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...

I encountered an issue where standby fell out of sync with primary.  When trying to place the standby in auto recovery again, I received the following error in the alert log: ORA-00600: internal error code, arguments: [3020], [41], [41955], [172008419], [], [], [], [], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 41, block# 41955, file offset is 343695360 bytes) ORA-10564: tablespace SYSTEM ORA-01110: data file 41: '+DATA/spl1adg/datafile/system.317.766442357' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 15 After researching this issue in metalink, the proposed solution was to rebuild the standby.  After pondering over this for a bit, I did the following which allowed me to remedy the issue: 1. Took the datafile offline on standby. SQL> alter database datafile 41 offline drop; 2. Took fresh backup of the datafile in question on primary. RMAN> backup as backupset datafile 41; 3. Copie...

A cascaded standby database is a standby database that receives primary database redo indirectly, from a cascading physical standby database, rather than directly from a primary database. Cascading can reduce network bandwidth consumption by eliminating duplicate redo transmission over network links that are shared by more than one standby database. I used this method after I successfully created a physical standby from a backup that was running against another physical standby; all RMAN backups are configured to run against the physical standby.  I needed to get some archivelogs to synch up primary with new standby; the archivelogs were no longer present on primary but were still located at the physical standby location. Therefore, to configure it so that I could successfully synchronize the new standby with primary, I did the following: Physical standby Database: db_unique_name = prmy_stb alter system set log_archive_config='DG_CONFIG=(prmy,prmy_stb,cscd_stb)'...

When receiving this error, this is more than likely due to db_file_name_convert not being configured correctly in the standby database.  DB_FILE_NAME_CONVERT is used to convert a filename of a new datafile on the primary database to a filename on the standby database. Here is how I fixed the issue on standby: 1. Set standby_file_management to MANUAL on the standby database. ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 2. Create the datafile manually in the standby database. ALTER DATABASE CREATE DATAFILE '/opt/oracle/app/db/11.2.0.1/dbs/UNNAMED00057' as '/opt/data/poc2adg/datafile/deal_data.257.767990655';   3. Set standby_file_management to AUTO on the standby database. ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 4. Configure DB_FILE_NAME_CONVERT with the correct settings for datafile conversion. ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' primary_db_datafile_location ',' standby_db_datafile_location ' SCOPE=SPFILE;...

After reviewing this error in the alert log, I found that this was pertaining to one particular datafile.  What I did to resolve this issue was the following: 1. Go to the primary database and backup the datafile in question.  In this case, it was datafile #56. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 20 21:52:01 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: POC2 (DBID=1234567890) RMAN> backup as compressed backupset datafile 56; Starting backup at 20-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1492 instance=poc22 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00056 name=+DATA/poc2/datafile/tg_ods.256.767919427 channel ORA_DISK_1: starting piece 1 at 20-DEC-11 channel ORA_D...

Before building out your standby, ensure that the database has force logging enabled in your primary database.       ALTER DATABASE FORCE LOGGING; 1. Ensure that your TNS_ADMIN is pointing to the proper location (typically this will be the Oracle Home where your database will utilize).       echo $TNS_ADMIN       export TNS_ADMIN=$ORACLE_HOME/network/admin 2.  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)     stbydb =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1525))     )     (CONNECT_DATA =       (SERVICE_NAME = stbydb)     )   ) prmrydb =   (DESCRIPTION =    ...

I found a great blog entry around cloning a database from an 11g physical standby.  I decided to complete the steps myself. The method basically entails turning your physical standby into a snapshot standby database via dgmgrl (11g only). For 11gR2, flashback logging is not a requirement.  Otherwise, flashback logging being enabled is a requirement as converting physical standby into snapshot standby requires a guaranteed restore point to be created; this allows the snapshot standby to be converted back into physical standby. Setting db_recovery_file_dest and db_recovery_file_dest_size are configured is the only caveat. 1. Startup clone instance with startup nomount. SQL*Plus: Release 11.2.0.1.0 Production on Wed May 11 13:06:12 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area  217157632 bytes Fixed Size        ...