Data Guard Redo Log Apply Services tips Oracle Database Tips by Donald Burleson

Oracle Data Guard Prepare to Start the Log Apply Service

Before the log apply service can be started on a logical standby database, the logical Oracle instance must be recovered to bring it to a point where the SQL apply mode can start reading and applying the changes from the redo logs onto the database. This starting point for the SQL apply mode should be the next SCN after the LogMiner dictionary is created on the primary database.

In order to apply all the changes up to this SCN, the logical Oracle instance should be manually recovered using the archived logfile that was created just after building the LogMiner dictionary.

TIP - If a cold backup of the primary database is used to create a logical standby database, there is no need to manually recover and this step can be skipped.  Open the database using the resetlogs and continue from changing the dbname.

Prepare to Start the Log Apply Service

In this case, the archived redo logfile is appsdb_1_568.dbf. The following statement will start recovery using appsdb_1_568.dbf logfile:

ALTER DATABASE RECOVER LOGFILE
'/oracle/appsdb/arch/appsdb_1_568.dbf';

This may give an error that a previous archived redo log is required for recovery.  Copy this file to the standby site and continue recovery using this log:

ERROR at line 1:
ORA-00279: change 393493 generated at 10/03/2003 12:03:12 needed for thread 1
ORA-00289: suggestion : /oracle/appsdb/arch/appsdb_1_567.dbf
ORA-00280: change 393493 for thread 1 is in sequence #567
ORA-00278: log file '/oracle/appsdb/arch/appsdb_1_567.dbf' no longer needed
for this recovery
ORA-00308: cannot open archived log '/oracle/appsdb/arch/appsdb_1_567.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Copy the appsdb_1_567.dbf file and recover using this logfile followed by the recovery using next logfile:

Alter database recover logfile '/oracle/appsdb/arch/appsdb_1_567.dbf';

alter database recover logfile
*
ERROR at line 1:
ORA-00279: change 393816 generated at 10/03/2003 12:07:58 needed for thread
1
ORA-00289: suggestion : /oracle/appsdb/archlocal/appsdb_1_568.dbf
ORA-00280: change 393816 for thread 1 is in sequence #568
ORA-00278: log file '/oracle/appsdb/arch/appsdb_1_567.dbf' no longer needed for this recovery

And then the archived log file, which is created after log miner dictionary creation.

Alter database recover logfile '/oracle/appsdb/arch/appsdb_1_568.dbf';
alter database recover logfile
*
ERROR at line 1:
ORA-00279: change 393836 generated at 10/03/2003 12:07:59 needed for thread
1
ORA-00289: suggestion : /oracle/appsdb/archlocal/appsdb_1_569.dbf

ORA-00280: change 393836 for thread 1 is in sequence #569
ORA-00278: log file '/oracle/appsdb/arch/appsdb_1_568.dbf' no longer needed for this recovery

This will recover the logical Oracle instance to the point where the log miner dictionary was created. Archived logs generated after this point can be registered with the standby database to be recovered by the log apply service.  After applying the archived log, open the database using resetlogs.

ALTER DATABASE OPEN RESETLOGS;

Change the DBNAME of the Standby Database

The dbname of the Oracle instance can be changed using the nid utility. The database will need to be shutdown and mounted again:

nid target=sys/change_on_install dbname=lappsdb setname=yes

The following output will appear. The command will prompt for input on the following question:

"Change database name of database <old dbname> to  <new dbname>? (Y/[N]) => " 
Type Y and press enter.

----------------Output of nid command---------------
DBNEWID: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.