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.
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.
|
|||
Create scripts to push or pull archived redo logs if errors occur or if performance is degraded. See Adding Tablespaces or Datafiles to the Primary Database. |
|||
Thread events are automatically propagated through archived logs, so no extra action is necessary. |
|||
Redo log changes do not affect standby database unless a redo log is cleared or lost. In these cases, you must rebuild the standby database. Pre-clear the logs on the standby database with the ALTER DATABASE CLEAR LOGFILE statement. See Clearing Online Redo Logs. |
|||
Database functions normally until it encounters redo depending on any parameter changes. |
Re-create the standby control file (see Refreshing the Standby Database Control File). Re-create the standby database if the primary database is opened RESETLOGS. |
||
Re-create the standby database if the RESETLOGS option is utilized. |
|||
Tablespace status changes (made read/write or read-only, placed online or offline) |
Status changes are automatically propagated, so no response is necessary. Datafiles remain online. |
||
Manually create datafile and restart recovery. See Adding Tablespaces or Datafiles to the Primary Database. |
|||
Tablespace or datafile taken offline, or datafile is dropped offline |
The tablespace or datafile requires recovery when you attempt to bring it online. |
Datafiles remain online. The tablespace or datafile is fine after standby database activation. |
|
alert.log. File blocks are invalidated unless they are in the future of redo, in which case they are not touched. |
Unlogged changes are not propagated to the standby database. If you want to apply these changes, see Performing Direct Path Operations. |
||
Make sure the standby database is not following behind the primary database. |
|||
alert.log |
May cause operation to fail on standby database because it lacks disk space. |
Ensure that there is enough disk space for the expanded datafile. |
|
Issue OPEN RESETLOGS or CLEAR UNARCHIVED LOGFILES statements |
|||
May cause failure because of redo depending on the changed parameter. |
Dynamically change the standby parameter or shut down the standby database and edit the initialization parameter file. |
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
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
Typically, physical changes to the primary database require a manual response on the standby database. This section contains the following topics:
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:
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.
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.
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.
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.
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.
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.
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):
The standby database recovery process continues to sequentially read and apply the redo information generated by the unrecoverable direct load.
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).
For more details, see Taking Datafiles in the Standby Database Offline |
If you have performed UNRECOVERABLE
operations on your primary database, determine whether a new backup is
required.
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.
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.
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.
Start a SQL*Plus session on the standby instance and issue the CANCEL statement on the standby database to halt its recovery process.
Start a SQL*Plus session on the production instance and create the control file for the standby database:
Transfer the
standby control file and archived log files to the standby site using
an operating system utility appropriate for binary files.
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:
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.
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:
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
% 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.
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.