Standby Database Management
Overview
This section describes procedurs to manage a standby database
environment.
Start the Standby Instance
The standby instance must be started and placed in
standby mount mode. The database should never be "opened" because this
will invalidate the "standby" status. In addition, 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.
Starting Recovery
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;
It is still possible to perform manual recovery
(this was required in 8i standby database) with the command:
SQL> RECOVER 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.
Canceling Recovery
Cancel managed recovery with the command:
SQL> ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE 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:
See the Oracle Data Guard Concepts and
Administration manual for more details.
Troubleshooting
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 retrospectively apply archived redo log files.
Archiver Errors
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. Once that is corrected, you must force the listener to begin
re-transmission with the command:
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.
This is the scenario used by the "stby_maint" script when implementing
the "restore" option as part of a 10g AS high availability environment
using DataGuard.
- SQL> connect / as
sysdba
- Connected.
- 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
- 1
- 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.
-
- SQL>
-
-
- Manual Recovery
-
- startup nomount;
- alter database
mount standby database;
- recover standby
database;
System Management
oratab File
The oratab file should be updated so that the
standby database entry has an "N" for the third field. This will insure
that the "dbstart" utility does not inadvertently start and open the
database and invalidate the standby status. An example is:
DBSID:/u01/app/oracle/product/9.2.0:N
Utilities
In order to facilitate the management of standby
databases the dbcontrol utility has been modified to be standby database
aware. The standby database's oratab entry must still be set with the
third field set to "N" in order to prevent the accidental opening of the
database at boot time. A new comment is supported in the oratab file to
identify the standby database to the dbcontrol utility. The format is
#STANDBY:<SID>:<OPTION>
where SID is the standby database SID and option is
NOSTART, MOUNT, MANAGED, or READ.
With this comment identifying a corresponding
standby database entry, dbcontrol will not open the standby database and
will only perform operations using the "standby" option. The following
has been added to the oratab file for database "stby":
#STANDBY:DBSID:MANAGED
orasetup
orasetup is a korn/bash shell script that replaces
the Oracle supplied script "oraenv". This file should be located in the
ORACLE_BASE/local/script directory. This script will set an Oracle
environment properly for all versions of the database. It also
recognizes the "Y" or “W” flag for databases and sets ORACLE_SID or the
"N" flag and sets TWO_TASK. This is also done for standby databases to
insure that the database is not accidentally damaged. Since the TWO_TASK
variable is set by default, the user must set the ORACLE_SID variable explicitly
in order to connect "/ as sysdba".
The orasetup utility can be run from any user with the
following syntax:
. orasetup <SID>
This assumes a korn or bash shell and that $ORACLE_BASE/local/script
is set in the PATH.
dbcontrol
dbcontrol is a korn/bash shell script that replaces
dbstart and dbshut and should be located in the ORACLE_BASE/local/script
directory. A "log" file directory should also exist at ORACLE_BASE/local/log
as dbcontrol will create a log file if invoked by another process (e.g.
if called by dbora at startup or shutdown). dbcontrol has been enhanced
to manage all database utilities as defined in the oratab file and can
be called during system startup or shutdown to start or stop all
database services.
dbcontrol has a help menu and can be used with a
single command line or interactively.
dbora
dbora is an enhanced version of the Oracle bourne shell script that is
called by the init process at startup and shutdown. The enhanced version calls dbcontrol with “START ALL” or “STOP ALL” parameters depending on whether
it’s being called during a startup or shutdown of the machine. The file
should be located in the /etc/init.d directory during database
installation.
stby_maint
stby_maint is a custom script to manage the
deletion of applied archived redo log files at the standby site. This
file should be located in the ORACLE_BASE/local/script directory and an
entry should be placed in the crontab file to run once a day.
