DBA Maestro - Helpful DBA tips

Here are the steps I used to recover my primary database by leveraging a Dataguard environment. ## on standby 1. stop managed recovery on standby. SQL> alter database recover managed standby database cancel; 2. Backup as a copy of the datafile from standby. RMAN> backup as copy datafile 2 format '/app/oracle/temp/sysaux_01.dbf'; 3. SCP the backup copy of the datafile to the primary database server. scp /app/oracle/temp/sysaux_01.dbf <target_server>:/app/oracle/temp ## on primary 4. Take corrupt datafile offline. SQL> alter database datafile 2 offline; 5. Catalog datafile to the primary database so the controlfile knows about it. RMAN> catalog datafilecopy '/app/oracle/temp/sysaux_01.dbf'; 6. Restore and recover the datafile in question. RMAN> restore datafile 2; RMAN> recover datafile 2; 7. Bring the datafile back online. SQL> alter database datafile 2 online; 8. Validate that the corruption is no longer pr...

RMAN-05001: auxiliary file name <file_name> conflicts with a file used by the target database I ran into an issue where I was trying to clone a new copy of non-production database (using production database as the source) by leveraging the RMAN clone via active database to save me some time and work.   I ran into the error where I couldn't successfully clone the database.  I made sure that my environment, pfile, RMAN script were configured correctly.  After much time of troubleshooting, I figured out the issue. After remembering that the source database had a DG environment, it occurred to me that was what was  causing  the error.  Reason being, the source had log_file_name_convert and db_file_name_convert configured which was being "carried over" onto my auxiliary instance hence causing the error mentioned above.  So all I needed to do was simply reset those two parameters within my RMAN script (as seen below) and I was able to suc...

Below are the steps I followed for patching OMS for OEM 12c R4.  I came across an issue with creating my property file.  I give an example of what it should look like upon completion. Creating the property file :  1. Run the following script to create the WebLogic encrypted configuration and key files. mkdir /app/oracle/Middleware/oms/oms_encr <OMS_HOME>/OPatch/wlskeys/createkeys.sh –oh <full path of platform OMS Oracle Home> -location <location to put the encrypted files> e.g. $OMS_HOME/OPatch/wlskeys/createkeys.sh -oh $OMS_HOME -location /app/oracle/Middleware/oms/oms_encr   Please enter weblogic admin server username: weblogic Please enter weblogic admin server password: Creating the key file can reduce the security of your system if it is not kept in a secured location after it is created. Creating new key... Trying to get configuration and key files for the given inputs... This operation will take some time. Please wait f...

I received the following error in a primary database's alert log that I was supporting recently.   ORA-00312: online log 5 thread 1: '+DATA/db_name/onlinelog/group_5.297.846947859' ORA-00338: log 5 of thread 1 is more recent than control file The database has a DataGuard environment as well and below are the steps that I took to resolve the issue. 1.  First, I checked the status of the redo logs and their current sizes and status. SQL> select group#,bytes/1024/1024,status from v$log;     GROUP# BYTES/1024/1024 STATUS ---------- --------------- ----------------          1              50 INACTIVE          2              50 INACTIVE          3              50 INACTIVE          4             250 INACTIVE   ...

Here are the majority of the steps for creating a clone 11gR2 database. 1.  Either configure your listener under the ORACLE_HOME for the standby (typically using NETCA) or if you are using 11gR2 RAC, you can use the Grid Infrastructure listener.ora to add your entries.  Configure listener.ora with your clone SID and reload listener. (example) SID_LIST_LISTENER =     (SID_DESC =       (GLOBAL_DBNAME = clone)       (SID_NAME = clone)       (ORACLE_HOME = <ORACLE_HOME>)     )   )     lsnrctl reload  LISTENER 3. Modify tnsnames.ora in $TNS_ADMIN on both primary and standby locations to include both your standby database TNS entry and the primary database TNS entry.   (example)     clone =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOS...

It's a been a while since my last post.  As you may know, I normally blog about solutions that are hard to find that haven't already been blogged about.  Here's my latest encounter... I was receiving this error when trying to login via "sqlplus / as sysdba" after completing an Oracle 11gR2 binary install using the "database home clone" methodology.  I checked that the binary permissions were set correctly, which they were.  Then finally I stumbled across someone else that had had this problem where the $ORACLE_HOME/rdbms/lib/config.c file wasn't configured properly.  Copyright (c) 1982, 2011, Oracle.  All rights reserved. ERROR: ORA-01031: insufficient privileges So I did the following: cd <ORACLE_HOME>/rdbms/lib cp -p config.c config.c.bak vi config.c (with below changes) relink oracle config.c before changes: /*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */ /*  Refer to ...

I was recently involved in DR testing involving two standby databases.  For the testing, I simply converted the 2 standby databases from physical standbys to snapshot standbys.   After the testing had ended from an application perspective, I started to convert the standby databases from snapshot standby back to physical standby.  In the process of doing so, I received the following ORA-0600 error. SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY * ERROR at line 1: ORA-00600: internal error code, arguments: [krhahws_02], [], [], [], [], [], [], [], [], [], [], [] Oh no!  Never fear, I thought...  I went to Oracle Support to research this issue and found the following resolution: 1. set disk_asynch_io=FALSE in the init.ora (this setting is just going to be temporary for repeating the "flashback database"). 2. shutdown then "startup mount" the database, to pick up the new parameter setting for disk_asynch_io 3. verify the disk_a...