How do you re-duplicate a broken physical standby database?

Question

first some data: we are using Oracle 11g databases. A primary database running on a dedicated server and a physical standby database on a separate dedicated server. We use the DataGuard feature to automatically replicate the primary database to the physical standby database in real time. The primary database is also backeed up through RMAN.

currently I am unable to cope a problem with our physical standby database. Somehow the transfer of the archive logs from the primary database to the physical standyby database has stopped, what it makes worse some of the archive logs already got deleted from some of our employees, now I can't issue a recovery by performing the following statement:

RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

since the required archive logs are gone.

So my thought was to re-duplicate the physical standby database. I shutdown the physical standby database and restarted it with STARTUP NOMOUNT. Then logged onto the server hosting the primary database and started a RMAN-session with:

RMAN target / auxiliary sys@PRIMARY_DB_DG
   RMAN> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
   RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER
         NOFILENAMECHECK;

but shortly after altering the the physical standby database to MOUNT status the process crashes due to either

1. RMAN-04006: error from auxiliary database: ORA-12537: TNS:connection closed,

2. RMAN-03009: failure of switch command on clone_default channel at 11/15/2011 11:13:58 ORA-03113: end-of-file on communication channel or

3. RMAN-06136: ORACLE error from auxiliary database: ORA-03113: end-of-file on communication channel

I have googled around for solutions, but only found guides to setup a physical standby database from scratch. So anybody knows howe to fix the physical database without setting it up completely new?

Greetings, CB

Answer 1

Here is my solution that worked for me:

1. Shutdown the physical standby database

   SQL> shutdown immediate;
   

2. (Optional, safer in case of failure) Backup all datafiles (*.dbf), redologs, archive logs, online logs, flashback logs and control files that are used by the shut down instance.

3. Delete all datafiles (*.dbf), redologs, archive logs, online logs, flashback logs and control files in their referenced locations, but make sure to keep the directories.

4. Start up your physical standyby database with NOMOUNT-Option

   SQL> startup nomount;
   

5. Now switch to your primary database environment.

6. Start your rman on you primary envoronment:

   $> rman target / auxiliary sys@Dataguard_instance
   

   Dataguard_instance must be substituted with your DataGuard instance name. After connect make sure your connected target database is your primary database

   connected to target database: PRIM_DB (DBID=4135917300)
   auxiliary database Password:
   connected to auxiliary database: PRIM_DB (not mounted)
   

   Note that your physical standby database is listed as not mounted primary database. If you see the same information as in target database, chances are that you are connected twice to your primary database. In that case we would create a 100% copy and not a physical standby database. So make sure you are using the right DataGuard instance.

7. So before we start we force a log switch:

   RMAN> sql 'alter system archive log current';
   

8. Now we are going to start the full replication of our physical standby database

   RMAN> duplicate target database for standby from active database dorecover  nofilenamecheck;
   

9. Now rman will perform a duplication of your physical standby database. Depending on your datafile size, this can take from a few hours to open end (I needed about 4 hours during night when the primary database was idle for about 1,5T of files) .

10. After rman is finished you can exit rman.

11. Reconnect to your physical standby database and shut it down:

    SQL> shutdown immediate;
    

12. If you use flashback option (else continnue with step 13):

    SQL> startup mount;
    SQL> alter database flashback on;
    SQL> alter database open;
    

13. Restart physical standby:

    SQL> startup;
    

14. Finished!

Hope that helps you to in case of need.