Standby Database Management
This section describes procedures to manage a physical standby database environment.
Start the Standby Instance
The standby instance must be started and placed in standby mount mode. The database must be mounted in order to access the controlfile to allow file name changes and subsequent recovery. The following commands are used:
1. Use SQL*Plus to connect to the standby database instance. For example, enter:
SQL> CONNECT / AS SYSDBA
2. Start the Oracle instance at the standby database without mounting the database. For example, enter:
SQL> STARTUP NOMOUNT;
3. Mount the standby database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Modes and Recovery
Once the standby database has been recovered up to the last archived redo log file, it must be kept current by the application of log files once they have been archived from the primary database. This can be done in "manual recovery" mode or in "managed recovery" mode. The standby database can also be opened in "read only" mode which allows query access to the standby database. No recovery can take place while the database is open in read only mode through 10g Release 2, and so time must be arranged for periodic recoveries to take place in order to keep the standby in sync with the primary database.
For Oracle9i and above the standby database can be "caught up" and run in managed recovery mode. Managed recovery mode allows for the automatic application of archived redo log files to the standby database. Managed recovery mode should be initiated with a disconnected session. The following syntax will accomplish this:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Since Release 10.1 Oracle has supported real time apply, the following will enable real time apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
It is still possible to perform manual recovery (this was required in 8i standby database) with the command:
SQL> RECOVER STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
This will invoke standard cancel based recovery. Once all available archived log files have been applied then cancel the recovery. At this point managed recovery can be resumed.
Cancel managed recovery with the command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Cancel manual recovery with the command:
SQL> RECOVER CANCEL;
Startup and Shutdown
In order to maintain managed recovery of the standby database, the following guidelines must be followed:
"To avoid creating gap sequences, follow these rules:
- Start the standby databases and listeners before starting the primary database.
- Shut down the primary database before shutting down the standby database.
If you violate either of these two rules, then the standby database is down while the primary database is open and archiving. Consequently, Oracle can create a gap sequence. When you restart the standby database later, you must synchronize the standby database manually with the primary database before you can initiate managed recovery."
If Data Guard is being implemented then this is will not be a problem since Data Guard will take care of re-synchronization.
Managed Recovery Errors
In order to initiate managed recovery all archived redo log files must be applied to the standby database. Managed recovery will not retroactively apply archived redo log files.
If archived redo log files are not being transferred to the standby database then check the "state" of the archive destination in the v$archive_dest view on the primary database. If this state is in error then the standby listener service may have failed or the tns alias or connect string may not be defined correctly. Once the problem is corrected, you can force re-transmission with a command like the following (not the register at the end):
SQL> alter system set log_archive_dest_2='service=DBSID_DG2 ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register';
If Data Guard is enabled then this should be issued automatically.
If the primary archive destination has stopped, possibly due to a full disk, fix the problem and issue:
archive log start
Standby Recovery Commands
The following shows a case of "manually" recovering the standby database outside of "managed recovery mode". This might be used when performing manual time lag recovery of a standby database.
- SQL> connect / as sysdba
- SQL> alter database recover automatic standby database until cancel;
- alter database recover automatic standby database until cancel
- ERROR at line 1:
- ORA-00279: change 158556003 generated at 01/03/2006 11:42:22 needed for thread
- ORA-00289: suggestion : /u01/app/oracle/admin/DSID/arch/DBSID_1_10886.arc
- ORA-00280: change 158556003 for thread 1 is in sequence #10886
- ORA-00278: log file '/u01/app/oracle/admin/DBSID/arch/DBSID_1_10886.arc' no
- longer needed for this recovery
- ORA-16145: archival for thread# 1 sequence# 10886 in progress
- SQL> alter database recover cancel;
- Database altered.
- Manual Recovery
- startup nomount;
- alter database mount standby database;
- recover standby database;