Yesterday I was asked by a colleague to look into a test system. There was data deleted through the application. He tried to restore it with from a database backup 2 days ago with no luck. He seemed to be struggling with recover -- either getting "ORA-16433: The database or pluggable database must be opened … Continue reading Oracle startup failure with ORA-00704 ORA-00604
Month: May 2020
Large size of trace files from MMON
While I was doing housekeeping working to prepare ODA upgrade, I noticed some large trace files for ASM and MGMTDB databases as showed below: [grid@joeoda001 trace]$ du -sh *gen* *mmon* 1.6G +ASM1_gen0_20481.trc 250M +ASM1_gen0_20481.trm 1.5G +ASM1_mmon_20571.trc 243M +ASM1_mmon_20571.trm [grid@joeoda001 grid]$ du -sh diag/rdbms/_mgmtdb/-MGMTDB/trace/*|grep -v K 22M diag/rdbms/_mgmtdb/-MGMTDB/trace/alert_-MGMTDB.log 161M diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_dbrm_23332.trc 16M diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_dbrm_23332.trm 1.7G diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_gen0_23224.trc 240M diag/rdbms/_mgmtdb/-MGMTDB/trace/-MGMTDB_gen0_23224.trm … Continue reading Large size of trace files from MMON
Which user to run ADRCI?
Since Oracle 11g, RDBMS diagnostic data has been reorganized and is stored inside a common directory structure, the Automatic Diagnostic Repository (ADR). An ADR is a centralized directory structure where one can find trace files, alert messages, incident dumps, core files, etc. And a comand line tool ADRCI can be used to mange this repository. … Continue reading Which user to run ADRCI?
A small trick to handle files prefixed with a specify character in BASH
From time to time, we might see some files on Linux which have strange leading characters. For example, a leading "-" dash or a leading "+" plus sign. Depending on which operations (which commands) you take, you may have different ways to handle those files -- putting file name in a single or double quotes, … Continue reading A small trick to handle files prefixed with a specify character in BASH
Purging flat (non-XML) text listener log file for ASM, SCAN etc.
In my last post, I've showed how to purge Audit Trail files from OS. Usually Audit Trail directories are places which you see lots of space used due to unmanaged audit files from Oracle. However, there are places you may want to take a look when you see a filesystem related to Oracle is getting … Continue reading Purging flat (non-XML) text listener log file for ASM, SCAN etc.
Purging Audit Trail in RAC & ODG with ASM environment
In Oracle Database Security Guide (12c), there is a section to describe how to purge Audit Trail records. Basically you have two options to do it using DBMS_AUDIT_MGMT: Schedule a job to do it regularly.Manually purge it as needed. However it's not obvious how to do it in a RAC with ODG on ASM environment. … Continue reading Purging Audit Trail in RAC & ODG with ASM environment
A subtle detail of outer join
When doing outer join on multiple tables or two tables on multiple conditions in Oracle, if you are using (+) operator, you must use it in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise … Continue reading A subtle detail of outer join