Standby
Database
Monitoring
Events That Affect the Standby Database
To prevent possible problems, you should
be aware of events that affect a standby database and learn how to monitor
them. Most changes to a primary database are automatically propagated to
a standby database through archived redo logs and so require no user intervention.
Nevertheless, some changes to a primary database require manual intervention
at the standby site.
Monitoring
the Primary and Standby Databases
This table indicates whether a command is
normally propagated or requires extra administrative efforts to be fully
propagated. It also describes how to respond to these events.
Determining
Which Archived Logs Have Been Received by the Standby Site
The simplest way to determine the most recent
archived log received by the standby site is to query the V$ARCHIVED_LOG
view. This view is only useful after the standby site has started receiving
logs, because before that time the view is populated by old archived log
records generated from the primary control file. For example, you can execute
the following script (sample output included):
col name format a20
col thread# format 999
col sequence# format 999
col first_change# format 999999
col next_change# format 999999
SELECT thread#, sequence# AS "SEQ#", name, first_change# AS "FIRSTSCN",
next_change# AS "NEXTSCN",archived, deleted,completion_time AS "TIME"
FROM v$archived_log
/
SQL> @archived_script
THREAD# SEQ# NAME FIRSTSCN NEXTSCN ARC DEL TIME
------- ---------- -------------------- ---------- ---------- --- --- ---------
1 947 /arc_dest/arc_1_947 33113 33249 YES NO 23-JUN-99
Determining
Which Logs Have Been Applied to the Standby Database
Query the V$LOG_HISTORY view on the standby
database, which records the latest log sequence number that has been applied.
For example, issue the following query:
SQL> SELECT thread#, max(sequence#)
AS "LAST_APPLIED_LOG"
2> FROM v$log_history
3> GROUP BY thread#;
THREAD# LAST_APPLIED_LOG
------- ----------------
1
967
Responding
to Events That Affect the Standby Database
Typically, physical changes to the primary
database require a manual response on the standby database. This section
contains the following topics:
Adding
Tablespaces or Datafiles to the Primary Database
Adding a tablespace or datafile to the primary
database generates redo that, when applied at the standby database, automatically
adds the datafile name to the standby control file. If the standby database
locates the file with the filename specified in the control file, then
recovery continues. If the standby database is unable to locate a file
with the filename specified in the control file, then recovery terminates.
Perform one of the following procedures to
create a new datafile in the primary database and update the standby database.
Note that if you do not want the new datafile in the standby database,
you can take the datafile offline manually using the following syntax:
To add
a tablespace or datafile to the primary database and create the datafile
in the standby database:
-
Create a tablespace on the primary database as usual.
-
If the standby database is shut down, start the standby instance
without mounting it. Mount the standby database, then place it in managed
recovery mode:
-
Switch redo logs on the primary database to initiate redo
archival to the standby database. If the recovery process on the standby
database tries to apply the redo containing the CREATE TABLESPACE statement,
it stops because the new datafile does not exist on the standby site.
-
Either wait for the standby database to cancel recovery because
it cannot find the new datafile, or manually cancel managed recovery. (Note
that CREATE TABLESPACE redo adds the new filename to the standby control
file. The following alert.log entry is generated:)
-
Create the datafile on the standby database.
-
Place the standby database in managed recovery mode
Renaming
Datafiles on the Primary Database
Datafile renames on your primary database
do not take effect at the standby database until you refresh the standby
database control file. To keep the datafiles at the primary and standby
databases synchronized when you rename primary database datafiles, perform
analogous operations on the standby database.
Adding
or Dropping Redo Logs on the Primary Database
You can add redo log file groups or members
to the primary database without affecting the standby database. Similarly,
you can drop log file groups or members from the primary database without
affecting your standby database. Enabling and disabling of threads at the
primary database has no effect on the standby database.
Consider whether to keep the online redo
log configuration the same at the primary and standby databases. Although
differences in the online redo log configuration between the primary and
standby databases do not affect the standby database functionality, they
do affect the performance of the standby database after activation. For
example, if the primary database has 10 redo logs and the standby database
has 2, and you then activate the standby database so that it functions
as the new primary database, the new primary database is forced to archive
more frequently than the old primary database.
To prevent problems after standby activations,
Oracle Corporation recommends keeping the online redo log configuration
the same at the primary and standby databases. Note that when you enable
a log file thread with the ALTER DATABASE ENABLE THREAD statement at the
primary database, you must create a new control file for your standby database
before activating it. See Refreshing the Standby Database
Control File for procedures.
Resetting
or Clearing Unarchived Redo Logs on the Primary Database
If you clear log files at the primary database
by issuing the ALTER DATABASE CLEAR UNARCHIVED LOGFILE statement, or open
the primary database using the RESETLOGS option, you invalidate the standby
database. Because both of these operations reset the primary log sequence
number to 1, you MUST
re-create the standby database in order to be able to apply archived logs
generated by the primary database.
Altering
the Primary Database Control File
If you use the CREATE CONTROLFILE statement
at the primary database to perform any of the following operations, you
may invalidate the control file for the standby database:
-
Change the maximum number of redo log
file groups or members.
-
Change the maximum number of instances
that can concurrently mount and open the database.
Using the CREATE CONTROLFILE statement with
the RESETLOGS option on your primary database will force the next open
of the primary database to reset the online logs, thereby invalidating
the standby database.
If you have invalidated the control file for
the standby database, re-create the file using the procedures in Refreshing
the Standby Database Control File.
Taking
Datafiles in the Standby Database Offline
You can take standby database datafiles offline
as a means to support a subset of your primary database's datafiles. For
example, you may decide not to recover the primary database's temporary
tablespaces on the standby database.
If you execute this statement, then the tablespace
containing the offline files must be dropped after opening the standby
database.
Performing
Direct Path Operations
When you perform a direct load originating
from any of the following, the performance improvement applies only
to the primary database (there is no corresponding recovery process performance
improvement on the standby database):
-
Direct path load
-
CREATE TABLE through subquery
-
CREATE INDEX on the primary database
The standby database recovery process continues
to sequentially read and apply the redo information generated by the unrecoverable
direct load.
Propagating
UNRECOVERABLE Processes Manually
Primary database processes using the UNRECOVERABLE
option are not propagated to the standby database because these processes
do not appear in the archived redo logs. If you perform an UNRECOVERABLE
operation at the primary database and then recover the standby database,
you do not receive error messages during recovery; instead, Oracle writes
error messages in the standby database alert.log file. The following
error message is displayed:
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//* redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.
Although the error message recommends dropping
the object that contains the block, do not perform this operation.
Instead, perform any one of the following tasks:
-
Take the affected datafiles offline in
the standby database and drop the tablespace after activation (see Taking
Datafiles in the Standby Database Offline).
-
Re-create the standby database from a
new database backup
-
Back up the affected tablespace and archive
the current logs in the primary database, transfer the datafiles to the
standby database, and resume standby recovery. This is the same procedure
that you would perform to guarantee ordinary database recoverability after
an UNRECOVERABLE operation.
Determining
Whether a Backup Is Required After UNRECOVERABLE Operations
If you have performed UNRECOVERABLE operations
on your primary database, determine whether a new backup is required.
To determine
whether a new backup is necessary:
-
Query the V$DATAFILE view on the primary
database to determine the system change number (SCN) or time at which Oracle
generated the most recent invalidation redo data.
-
Issue the following SQL statement on
the primary database to determine whether you need to perform another backup:
SELECT unrecoverable_change#,
to_char(unrecoverable_time, 'mm-dd-yyyy hh:mi:ss')
FROM v$datafile;
-
If the query in the previous step reports
an unrecoverable time for a datafile that is more recent than the time
when the datafile was last backed up, then make another backup of the datafile
in question.
Refreshing
the Standby Database Control File
The following steps describe how to refresh,
or create a copy, of changes you have made to the primary database control
file. Refresh the standby database control file after making major structural
changes to the primary database, such as adding or dropping files.
To refresh
the standby database control file:
-
Start a SQL*Plus session on the standby
instance and issue the CANCEL statement on the standby database to halt
its recovery process.
SQL> RECOVER CANCEL # for manual recovery mode
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL # for managed recovery mode
Shut down the standby instances:
SQL> SHUTDOWN IMMEDIATE
-
Start a SQL*Plus session on the production
instance and create the control file for the standby database:
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'filename';
-
Transfer the standby control file and
archived log files to the standby site using an operating system utility
appropriate for binary files.
-
Connect to the standby instance and mount
(but do not open) the standby database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Restart the recovery process on the standby database:
SQL> RECOVER STANDBY DATABASE # recovers using location for logs
# specified in initialization parameter file
SQL> RECOVER FROM 'location' STANDBY DATABASE # recovers from specified
# location
Clearing
Online Redo Logs
After creating the standby database, you can
clear standby database online redo logs to optimize performance by issuing
the following statement, where integer refers to the number of the
log group:
ALTER DATABASE CLEAR LOGFILE GROUP integer;
This statement optimizes standby activation
because it is no longer necessary for Oracle to zero the logs at
activation. Zeroing involves writing zeros to the entire contents of the
redo log and then setting a new header to make the redo log look like it
was when it was created. Zeroing occurs during a RESETLOGS operation.
If you clear the logs manually, Oracle realizes
at activation that the logs already have zeros and skips the zeroing step.
This optimization is important because it can take a long time to write
zeros into all of the online logs. If you prefer not to perform this operation
during maintenance, Oracle clears the online logs automatically during
activation.
Backing
Up the Standby Database
If necessary, you can back up your standby
database, but not while the database is in manual or managed recovery mode.
You must take the standby database out of managed recovery mode, make the
backups, then resume managed recovery. You can make the backups when the
database is shut down or in read-only mode.
The following table lists some advantages
and disadvantages of these methods:
To back
up tablespaces on a standby database when it is in read-only mode:
-
Start a SQL*Plus session on the standby database and take
the database out of managed or manual recovery mode:
RECOVER MANAGED STANDBY DATABASE CANCEL
# for managed recovery
RECOVER CANCEL
# for manual recovery
-
Open the database in read-only mode:
ALTER DATABASE OPEN READ ONLY
-
Take backups of some tablespaces using operating system utilities.
You should not back up the standby control file.
-
Minimize the time that the database is down. For example,
to back up datafiles tbs11.f, tbs12.f, and
tbs13.fin
tablespace TBS_1 on UNIX you might enter:
% cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
% cp /disk1/oracle/dbs/tbs13.f /disk2/backup/tbs13.k
Terminate all active user sessions on the standby database.
-
Place the database in manual or managed recovery mode:
RECOVER MANAGED STANDBY DATABASE # for managed recovery
RECOVER STANDBY DATABASE # for manual recovery
-
Back up the control file on the primary database using an
operating system utility. You must back up the primary database control
file, not the standby database control file.
-
Repeat the preceding steps until you have backed up each
tablespace in the database.
To back
up tablespaces on a standby database when it is shut down:
-
Start a SQL*Plus session on the standby database and take
the database out of managed or manual recovery mode:
RECOVER MANAGED STANDBY DATABASE CANCEL # for managed recovery
RECOVER CANCEL # for manual recovery
-
Shut down the database:
SHUTDOWN IMMEDIATE
-
Make cold backups of some tablespaces using operating system
utilities. Minimize the time that the database is down. For example, to
back up datafiles tbs11.f, tbs12.f, and tbs13.f
in tablespace TBS_1 on UNIX you might enter:
% cp /disk1/oracle/dbs/tbs11.f /disk2/backup/tbs11.bk
% cp /disk1/oracle/dbs/tbs12.f /disk2/backup/tbs12.bk
% cp /disk1/oracle/dbs/tbs13.f /disk2/backup/tbs13.bk
-
Use SQL*Plus to start the Oracle instance at the standby
database without mounting it, specifying a parameter file if necessary:
STARTUP NOMOUNT pfile = initSTANDBY.ora
-
Mount the database:
ALTER DATABASE MOUNT STANDBY DATABASE
-
Place the database in manual or managed recovery mode:
RECOVER MANAGED STANDBY DATABASE # for managed recovery
RECOVER STANDBY DATABASE # for manual recovery
-
Repeat the preceding steps until you have backed up each
tablespace in the database.