This chapter describes how to manage physical and snapshot standby databases. The following topics are discussed:
Primary Database Changes That Require Manual Intervention at a Physical Standby
Monitoring Primary, Physical Standby, and Snapshot Standby Databases
See Oracle Data Guard Broker to learn how the Oracle Data Guard broker simplifies the management of physical and snapshot standby databases.
This section describes how to start up and shut down a physical standby database.
Use the SQL*Plus STARTUP command to start a physical standby database. The SQL*Plus STARTUP command starts, mounts, and opens a physical standby database in read-only mode when it is invoked without any arguments.
Once mounted or opened, a physical standby database can receive redo data from the primary database.
See Section 8.3 for information about Redo Apply and Section 10.2 for information about opening a physical standby database in read-only mode.
Note:
When Redo Apply is started on a physical standby database that has not yet received redo data from the primary database, anORA-01112 message may be returned. This indicates that Redo Apply is unable to determine the starting sequence number for media recovery. If this occurs, manually retrieve an archived redo log file from the primary database and register it on the standby database, or wait for redo transport to begin before starting Redo Apply.Use the SQL*Plus SHUTDOWN command to stop Redo Apply and shut down a physical standby database. Control is not returned to the session that initiates a database shutdown until shutdown is complete.
If the primary database is up and running, defer the standby destination on the primary database and perform a log switch before shutting down the physical standby database.
A physical standby database can be opened for read-only access and used to offload queries from a primary database.
Note:
A physical standby database that is opened in read-only mode is subject to the same restrictions as any other Oracle database opened in read-only mode. For more information, see Oracle Database Administrator's Guide.If a license for the Oracle Active Data Guard option has been purchased, Redo Apply can be active while the physical standby database is open, thus allowing queries to return results that are identical to what would be returned from the primary database. This capability is known as the real-time query feature. See Section 10.2.1 for more details.
If a license for the Oracle Active Data Guard option has not been purchased, a physical standby database cannot be open while Redo Apply is active, so the following rules must be observed when opening a physical standby database instance or starting Redo Apply:
Redo Apply must be stopped before any physical standby database instance is opened.
If one or more physical standby instances are open, those instances must be stopped or restarted in a mounted state before starting Redo Apply.
See Also:
Oracle Database Licensing Information for more information about Oracle Active Data Guard
The COMPATIBLE database initialization parameter must be set to 11.0 or higher to use the real-time query feature of the Oracle Active Data Guard option.
A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Note:
If Redo Apply is active on an open instance, additional instances can be opened without having to stop Redo Apply.Redo Apply cannot be started on a mounted physical standby instance if any instance of that database is open. The instance must be opened before starting Redo Apply on it.
Example: Querying V$DATABASE to Check the Standby's Open Mode
This example shows how the value of the V$DATABASE.OPEN_MODE column changes when a physical standby is open in real-time query mode.
Start up and open a physical standby instance, and perform the following SQL query to show that the database is open in read-only mode:
SQL> SELECT open_mode FROM V$DATABASE; OPEN_MODE -------------------- READ ONLY
Issue the following SQL statement to start Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Database altered.
Now that the standby is in real-time query mode (that is, the standby is open in read-only mode and Redo Apply is active), the V$DATABASE.OPEN_MODE column changes to indicate the following:
SQL> SELECT open_mode FROM V$DATABASE; OPEN_MODE -------------------- READ ONLY WITH APPLY
If you are using real-time query to offload queries from a primary database to a physical standby database, you may want to monitor the apply lag to ensure that it is within acceptable limits.
The current apply lag is the difference, in elapsed time, between when the last applied change became visible on the standby and when that same change was first visible on the primary. This metric is computed to the nearest second.
To obtain the apply lag, query the V$DATAGUARD_STATS view. For example:
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS -
> WHERE name like 'apply lag';
NAME VALUE DATUM_TIME TIME_COMPUTED
--------- ------------- ------------------- -------------------
apply lag +00 00:00:00 05/27/2009 08:54:16 05/27/2009 08:54:17
The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated. The difference between the values in these columns should be less than 30 seconds. If the difference is larger than this, the apply lag metric may not be accurate.
To obtain a histogram that shows the history of apply lag values since the standby instance was last started, query the V$STANDBY_EVENT_HISTOGRAM view. For example:
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' - > AND COUNT > 0; NAME TIME UNIT COUNT LAST_TIME_UPDATED --------- --------- -------- ----------- ------------------------ apply lag 0 seconds 79681 06/18/2009 10:05:00 apply lag 1 seconds 1006 06/18/2009 10:03:56 apply lag 2 seconds 96 06/18/2009 09:51:06 apply lag 3 seconds 4 06/18/2009 04:12:32 apply lag 4 seconds 1 06/17/2009 11:43:51 apply lag 5 seconds 1 06/17/2009 11:43:52 6 rows selected
To evaluate the apply lag over a time period, take a snapshot of V$STANDBY_EVENT_HISTOGRAM at the beginning of the time period and compare that snapshot with one taken at the end of the time period.
The STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.
If STANDBY_MAX_DATA_DELAY is set to the default value of NONE, queries issued to a physical standby database will be executed regardless of the apply lag on that database.
If STANDBY_MAX_DATA_DELAY is set to a non-zero value, a query issued to a physical standby database will be executed only if the apply lag is less than or equal to STANDBY_MAX_DATA_DELAY. Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.
If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.
Use the ALTER SESSION SQL statement to set STANDBY_MAX_DATA_DELAY. For example:
SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2
Issue the following SQL statement on a physical standby database to ensure that all redo data received from the primary database has been applied to a physical standby database:
SQL> ALTER SESSION SYNC WITH PRIMARY;
This statement will block until all redo data received by the standby database at the time that this command is issued has been applied to the physical standby database. An ORA-3173 error is returned immediately, and synchronization will not occur, if the redo transport status at the standby database is not SYNCHRONIZED or if Redo Apply is not active.
You can ensure that Redo Apply synchronization occurs in specific cases by using the SYS_CONTEXT('USERENV','DATABASE_ROLE') function to create a standby-only trigger (that is, a trigger that is enabled on the primary but that only takes certain actions if it is running on a standby). For example, you could create the following trigger that would execute the ALTER SESSION SYNC WITH PRIMARY statement for a specific user connection at logon:
CREATE TRIGGER adg_logon_sync_trigger
AFTER LOGON ON user.schema
begin
if (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) then
execute immediate 'alter session sync with primary';
end if;
end;
The apply lag control and Redo Apply synchronization mechanisms described above require that the client be connected and issuing queries to a physical standby database that is in real-time query mode.
The following additional restrictions apply if STANDBY_MAX_DATA_DELAY is set to 0 or if the ALTER SESSION SYNC WITH PRIMARY SQL statement is used:
The standby database must receive redo data via the SYNC transport.
The redo transport status at the standby database must be SYNCHRONIZED and the primary database must be running in either maximum protection mode or maximum availability mode.
Real-time apply must be enabled.
Oracle Active Data Guard achieves high performance of real-time queries in an Oracle RAC environment through the use of cache fusion. This allows the Oracle Data Guard apply instance and queries to work out of cache and not be slowed down by disk I/O limitations. A consequence of this is that an unexpected failure of the apply instance leaves buffers in inconsistent states across all the open Oracle RAC instances. To ensure data consistency and integrity, Oracle Data Guard closes all the other open instances in the Oracle RAC configuration, and brings them to a mounted state. You must manually reopen the instances - at which time the data is automatically made consistent, followed by restarting redo apply on one of the instances. Note that in an Oracle Data Guard broker configuration, the instances are automatically reopened and redo apply is automatically restarted on one of the instances.
See Also:
Oracle Data Guard Broker for more information about how the broker handles apply instance failures
The My Oracle Support note 1357597.1 at http://support.oracle.com for additional information about apply instance failures in an Oracle Active Data Guard Oracle RAC standby
If corrupt data blocks are encountered when a database is accessed, they can be automatically replaced with uncorrupted copies of those blocks. This requires the following conditions:
The physical standby database must be operating in real-time query mode, which requires an Oracle Active Data Guard license.
The physical standby database must be running real-time apply.
Automatic block media recovery works in two directions depending on whether the corrupted blocks are encountered on the primary or on the standby.
Corrupted Blocks On the Primary
If corrupt data blocks are encountered at a primary database, then the primary automatically searches for good copies of those blocks on a standby and, if they are found, has them shipped back to the primary.
Note that the primary requires a LOG_ARCHIVE_DEST_n to the standby only (a physical standby, a cascading physical standby, or a far sync instance). The primary does not require a LOG_ARCHIVE_DEST_n to any terminal destinations; it is able to automatically ascertain their service names.
If corrupt data blocks are encountered at a standby, then the standby automatically initiates communication with the primary and requests uncorrupted copies of those blocks. For the primary to be able to ship the uncorrupted blocks to the standby, the following database initialization parameters must be configured on the standby. This is true even if the primary does not directly service the standby (for example, in cascading configurations).
The LOG_ARCHIVE_CONFIG parameter is configured with a DG_CONFIG list and a LOG_ARCHIVE_DEST_n parameter is configured for the primary database.
or
The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database.
Additional Automatic Block Media Repair Considerations
Automatic repair is supported with any Oracle Data Guard protection mode. However, the effectiveness of repairing a corrupt block at the primary using the non-corrupt version of the block from the standby depends on how closely the standby apply is synchronized with the redo generated by the primary.
When an automatic block repair has been performed, a message is written to the database alert log.
If automatic block repair is not possible, an ORA-1578 error is returned.
The RMAN RECOVER BLOCK command is used to manually repair a corrupted data block. This command searches several locations for an uncorrupted copy of the data block. By default, one of the locations is any available physical standby database operating in real-time query mode. The EXCLUDE STANDBY option of the RMAN RECOVER BLOCK command can be used to exclude physical standby databases as a source for replacement blocks.
See Also:
Oracle Database Backup and Recovery Reference for more information about the RMANRECOVER BLOCK commandThe Active Data Guard Best Practices white paper describes how to tune queries for optimal performance on a physical standby database. This paper is available on the Oracle Maximum Availability Architecture (MAA) home page at:
http://www.oracle.com/goto/maa
Force Full Database Caching Mode
The use of force full database caching mode can potentially improve performance because queries are executed faster.
The enabling and disabling of force full database caching mode is not recorded in redo, so the status of in-memory caching is not necessarily the same on all members of a Data Guard configuration. The Force Full Database In-Memory Caching feature is available in Oracle Database 12c Release 1 (12.1.0.2) and later.
For more information about the Force Full Database In-Memory Caching feature, including guidelines on how and when to enable and disable it, see Oracle Database Performance Tuning Guide.
If you are using a standby to offload queries from the primary database, and the nature of the workload requires more temp table space than is automatically created when the standby is first created, then you may need to manually add additional space.
To add temporary files to the physical standby database, perform the following tasks:
Identify the tablespaces that should contain temporary files. Do this by entering the following command on the standby database:
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES 2> WHERE CONTENTS = 'TEMPORARY'; TABLESPACE_NAME -------------------------------- TEMP1 TEMP2
For each tablespace identified in the previous query, add a new temporary file to the standby database. The following example adds a new temporary file called TEMP1 with size and reuse characteristics that match the primary database temporary files:
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE 2> '/arch1/boston/temp01.dbf' 3> SIZE 40M REUSE;
Redo generation on a read-only database is not allowed. When a data manipulation language (DML) operation makes a change to a global temporary table, the change itself does not generate redo since it is only a temporary table. However, the undo generated for the change does in turn generate redo. Prior to Oracle Database 12c Release 1 (12.1), this meant that global temporary tables could not be used on Oracle Active Data Guard standbys, which are read-only.
However, as of Oracle Database 12c Release 1 (12.1), the temporary undo feature allows the undo for changes to a global temporary table to be stored in the temporary tablespace as opposed to the undo tablespace. Undo stored in the temporary tablespace does not generate redo, thus enabling redo-less changes to global temporary tables. This allows DML operations on global temporary tables on Oracle Active Data Guard standbys. This feature benefits Oracle Data Guard in the following ways:
Read-mostly reporting applications that use global temporary tables for storing temporary data can be offloaded to an Oracle Active Data Guard instance.
When temporary undo is enabled on the primary database, undo for changes to a global temporary table are not logged in the redo and thus, the primary database generates less redo. Therefore, the amount of redo that Oracle Data Guard must ship to the standby is also reduced, thereby reducing network bandwidth consumption and storage consumption.
To enable temporary undo on the primary database, use the TEMP_UNDO_ENABLED initialization parameter. On an Oracle Active Data Guard standby, temporary undo is always enabled by default so the TEMP_UNDO_ENABLED parameter has no effect.
Note:
Data definition language (DDL) operations on global temporary tables (for example,CREATE and DROP) must still be issued from the primary database. DDL changes will be visible on the standby when it catches up with the primary database.The temporary undo feature requires that the database initialization parameter COMPATIBLE be set to 12.0.0 or higher.
The temporary undo feature on Oracle Active Data Guard instances does not support temporary BLOBs or temporary CLOBs.
Distributed transactions on an Oracle Active Data Guard instance are not permitted if they involve changes to local objects. For example, you cannot commit a transaction that modifies a global temporary table on the Oracle Active Data Guard instance and also updates a remote table on another database using a database link. You must commit or roll back any outstanding DML operations to global temporary tables on the Active Data Guard instance before issuing a remote DML operation, or vice versa. This also includes implicit writes to global temporary tables made by operations such as EXPLAIN PLAN statements.
See Also:
Oracle Database Administrator's Guide for more information about temporary undo
Oracle Database Reference for more information about the TEMP_UNDO_ENABLED initialization parameter
In an Oracle Active Data Guard environment, sequences created by the primary database with the default CACHE and NOORDER options can be accessed from standby databases as well. When a standby database accesses such a sequence for the first time, it requests that the primary database allocate a range of sequence numbers. The range is based on the cache size and other sequence properties specified when the sequence was created. Then the primary database allocates those sequence numbers to the requesting standby database by adjusting the corresponding sequence entry in the data dictionary. When the standby has used all the numbers in the range, it requests another range of numbers.
The primary database ensures that each range request from a standby database gets a range of sequence numbers that do not overlap with the ones previously allocated for both the primary and standby databases. This generates a unique stream of sequence numbers across the entire Oracle Data Guard configuration.
Because the standby's requests for a range of sequences involve a round-trip to the primary, be sure to specify a large enough value for the CACHE keyword when you create a sequence that will be used on an Oracle Active Data Guard standby. Otherwise, performance could suffer.
Also, the terminal standby should have a LOG_ARCHIVE_DEST_n parameter defined that points back to the primary.
Example: Assigning a Range of Sequence Values In a Multi-standby Configuration
This example shows how a range of sequence values can be assigned to a database when it references NEXTVAL on the sequence either for the first time or after it uses up all of the previously assigned sequence values. In this example, there are two standby databases.
On the primary database, issue the following SQL statements to create a global temporary table named gtt, and a sequence named g with a cache size of 10:
SQL> CREATE GLOBAL TEMPORARY TABLE gtt (a int); Table created. SQL> CREATE SEQUENCE g CACHE 10; Sequence created.
On the first standby database, issue the following SQL statements:
SQL> INSERT INTO gtt VALUES (g.NEXTVAL);
1 row created.
SQL> INSERT INTO gtt VALUES (g.NEXTVAL);
1 row created.
SQL> SELECT * FROM gtt;
A
----------
1
2
Because the sequence cache size was set to 10 (in Step 1) and because this is the first time the sequence was accessed, the results of the SELECT statement show that the first standby database is assigned sequence numbers 1 to 10.
On the primary database, issue the following SQL statements:
SQL> SELECT g.NEXTVAL FROM dual;
NEXTVAL
----------
11
SQL> SELECT g.NEXTVAL FROM dual;
NEXTVAL
----------
12
The results of the SELECT statements show that the primary database is assigned the next range of sequence values, 11-20.
On the second standby database, issue the following SQL statements:
SQL> INSERT INTO gtt VALUES (g.NEXTVAL);
1 row created.
SQL> INSERT INTO gtt VALUES (g.NEXTVAL);
1 row created.
SQL> SELECT * FROM gtt;
A
----------
21
22
The results of the SELECT statement show that the second standby is assigned the next range of sequence values, 21-30.
Sequences created with the ORDER or NOCACHE options cannot be accessed on an Oracle Active Data Guard standby.
See Also:
Oracle Database SQL Language Reference for more information about creating sequences
Oracle Database Administrator's Guide for more information about managing sequences
A session sequence is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. Unlike the existing regular sequences (referred to as "global" sequences for the sake of comparison), a session sequence returns a unique range of sequence numbers only within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.
Session sequences support most of the sequence properties that are specified when the sequence is defined. However, the CACHE/NOCACHE and ORDER/NOORDER options are not relevant to session sequences and are ignored.
Session sequences must be created by a read/write database but can be accessed on any read/write or read-only databases (either a regular database temporarily open read-only or a standby database).
Creating and Altering Session Sequences
To create a session sequence, issue the following SQL statement:
SQL> CREATE SEQUENCE … SESSION;
To alter an existing session sequence to be a regular ("global") sequence, issue the following SQL statement:
SQL> ALTER SEQUENCE … GLOBAL;
To alter a regular sequence to be a session sequence, issue the following SQL statement:
SQL> ALTER SEQUENCE … SESSION;
Example: Using Session Sequences
This example shows how session sequence values are unique to each database session.
On the primary database, issue the following SQL statements to create a global temporary table named gtt and a session sequence named s:
SQL> CREATE GLOBAL TEMPORARY TABLE gtt (a int); Table created. SQL> CREATE SEQUENCE s SESSION; Sequence created.
On the standby database, issue the following SQL statements:
SQL> INSERT INTO gtt VALUES (s.NEXTVAL);
1 row created.
SQL> INSERT INTO gtt VALUES (s.NEXTVAL);
1 row created.
SQL> SELECT * FROM gtt;
A
----------
1
2
From another session of the same standby database, issue the following SQL statements:
SQL> INSERT INTO gtt VALUES (s.NEXTVAL);
1 row created.
SQL> INSERT INTO gtt VALUES (s.NEXTVAL);
1 row created.
SQL> SELECT * FROM gtt;
A
----------
1
2
The results of the SELECT statement show that the sequence values assigned are the same as those assigned for the first session in the previous step. This is because sequence values are unique to each database session.
Most structural changes made to a primary database are automatically propagated through redo data to a physical standby database. Table 10-1 lists primary database structural and configuration changes which require manual intervention at a physical standby database.
Table 10-1 Primary Database Changes That Require Manual Intervention at a Physical Standby
| Reference | Primary Database Change | Action Required on Physical Standby Database |
|---|---|---|
|
Add a data file or create a tablespace |
No action is required if the |
|
|
Drop or delete a tablespace or data file |
Delete data file from primary and physical standby database after the redo data containing the |
|
|
Use transportable tablespaces |
Move tablespace between the primary and the physical standby database. |
|
|
Rename a data file |
Rename the data file on the physical standby database. |
|
|
Add or drop a redo log file group |
Evaluate the configuration of the redo log and standby redo log on the physical standby database and adjust as necessary. |
|
|
Perform a DML or DDL operation using the |
Copy the data file containing the unlogged changes to the physical standby database. |
|
|
Grant or revoke administrative privileges or change the password of a user who has administrative privileges |
If the |
|
|
Reset the TDE master encryption key |
Replace the database encryption wallet on the physical standby database with a fresh copy of the database encryption wallet from the primary database. |
|
|
Change initialization parameters |
Evaluate whether a corresponding change must be made to the initialization parameters on the physical standby database. |
The STANDBY_FILE_MANAGEMENT database initialization parameter controls whether the addition of a data file to the primary database is automatically propagated to a physical standby databases.
If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to AUTO, any new data files created on the primary database are automatically created on the physical standby database.
If the STANDBY_FILE_MANAGEMENT database parameter on the physical standby database is set to MANUAL, a new data file must be manually copied from the primary database to the physical standby databases after it is added to the primary database.
Note:
On a physical standby for which the Oracle Active Data Guard option has been enabled, you cannot use the manual copy method. Instead, you must execute the following SQL statement on the standby to create an empty data file:
SQL> ALTER DATABASE CREATE DATAFILE [filename | filenumber] -
AS [NEW | new_filename];
You must specify which one to rename: the filename or the filenumber.
You should also specify either the new filename or NEW. The NEW keyword lets Oracle automatically choose a name, if Oracle Managed Files (OMF) is enabled.
Note that if an existing data file from another database is copied to a primary database, it must also be copied to the standby database and the standby control file must be re-created, regardless of the setting of STANDBY_FILE_MANAGEMENT parameter.
When a tablespace is dropped or a data file is deleted from a primary database, the corresponding data file(s) must be deleted from the physical standby database. The following example shows how to drop a tablespace:
SQL> DROP TABLESPACE tbs_4; SQL> ALTER SYSTEM SWITCH LOGFILE;
To verify that deleted data files are no longer part of the database, query the V$DATAFILE view.
Delete the corresponding data file on the standby system after the redo data that contains the previous changes is applied to the standby database. For example:
% rm /disk1/oracle/oradata/payroll/s2tbs_4.dbf
On the primary database, after ensuring the standby database applied the redo information for the dropped tablespace, you can remove the data file for the tablespace. For example:
% rm /disk1/oracle/oradata/payroll/tbs_4.dbf
You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the data files on both the primary and standby databases. To use this statement, the STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO. For example, to drop the tablespace at the primary site:
SQL> DROP TABLESPACE tbs_4 INCLUDING CONTENTS AND DATAFILES; SQL> ALTER SYSTEM SWITCH LOGFILE;
You can use the Oracle transportable tablespaces feature to move a subset of an Oracle database and plug it in to another Oracle database, essentially moving tablespaces between the databases.
To move or copy a set of tablespaces into a primary database when a physical standby is being used, perform the following steps:
Generate a transportable tablespace set that consists of data files for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.
Transport the tablespace set:
Copy the data files and the export file to the primary database.
Copy the data files to the standby database.
The data files must have the same path name on the primary and standby databases unless the DB_FILE_NAME_CONVERT database initialization parameter has been configured. If DB_FILE_NAME_CONVERT has not been configured and the path names of the data files are not the same on the primary and standby databases, issue the ALTER DATABASE RENAME FILE statement to rename the data files. Do this after Redo Apply has failed to apply the redo generated by plugging the tablespace into the primary database. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL before renaming the data files, and should be reset to the previous value after renaming the data files.
Plug in the tablespace.
Invoke the Data Pump utility to plug the set of tablespaces into the primary database. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database.
For more information about transportable tablespaces, see Oracle Database Administrator's Guide.
When you rename one or more data files in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same data files on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
The following steps describe how to rename a data file in the primary database and manually propagate the changes to the standby database.
To rename the data file in the primary database, take the tablespace offline:
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the data file on the primary system:
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
Rename the data file in the primary database and bring the tablespace back online:
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE - > '/disk1/oracle/oradata/payroll/tbs_4.dbf' - > TO '/disk1/oracle/oradata/payroll/tbs_x.dbf'; SQL> ALTER TABLESPACE tbs_4 ONLINE;
Note:
An alternative to these first three steps is to use theALTER DATABASE MOVE DATAFILE command to rename a datafile. This command lets you rename a datafile while allowing read/write access to the datafile. Adequate storage area is a prerequisite for moving a datafile because during the execution of the MOVE DATAFILE command, the database maintains both the original and the renamed datafiles as two separate files. See Section 2.5, "Moving the Location of Online Data Files" for more information.Connect to the standby database and stop Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Shut down the standby database:
SQL> SHUTDOWN;
Rename the data file at the standby site using an operating system command, such as the UNIX mv command:
% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
Start and mount the standby database:
SQL> STARTUP MOUNT;
Rename the data file in the standby control file. Note that the STANDBY_FILE_MANAGEMENT database initialization parameter must be set to MANUAL in order to rename a data file. This parameter can be reset to its previous value after renaming a data file.
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' - > TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
On the standby database, restart Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE - > DISCONNECT FROM SESSION;
If you do not rename the corresponding data file at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed data file, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:
ORA-00283: recovery session canceled due to errors ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'
Note:
An alternative to steps 4-9 is to use theALTER DATABASE MOVE DATAFILE command to rename a datafile at the standby. See Section 2.5, "Moving the Location of Online Data Files" for more information.The configuration of the redo log and standby redo log on a physical standby database should be reevaluated and adjusted as necessary after adding or dropping a log file group on the primary database.
Take the following steps to add or drop a log file group or standby log file group on a physical standby database:
Stop Redo Apply.
If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.
Add or drop a log file group.
Note:
An online logfile group must always be manually cleared before it can be dropped from a physical standby database. For example:ALTER DATABASE CLEAR LOGFILE GROUP 3;
An online logfile group that has a status of CURRENT or CLEARING_CURRENT cannot be dropped from a physical standby database. An online logfile group that has this status can be dropped after a role transition.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.
Restart Redo Apply.
In Oracle RAC environments, keep the following in mind:
When an online redo log group is added to a primary database, you must manually add an online redo log group to the standby database. It is not done automatically.
When a new redo thread is added to a primary database, a new redo thread is automatically added to the standby. By default, the new thread is configured with 2 log groups of 100 MB each. This cannot be changed or overridden.
When a new log group is added to an existing redo thread, a new log group is not automatically added to its existing thread.
When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and may require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCE LOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.
See Section 15.4 for information about recovering after the NOLOGGING clause is used.
If the REMOTE_LOGIN_PASSWORDFILE database initialization parameter is set to SHARED or EXCLUSIVE, the password file on a physical standby database must be replaced with a fresh copy from the primary database after granting or revoking administrative privileges or changing the password of a user with administrative privileges.
Failure to refresh the password file on the physical standby database may cause authentication of redo transport sessions or connections as SYSDG, SYSDBA, or SYSOPER to the physical standby database to fail.
If you have stored the password file in an Oracle ASM disk group at the standby database, then you must copy the updated password file from the primary database to the Oracle ASM location at the standby database. See Oracle Automatic Storage Management Administrator's Guide for information about the ASMCMD pwcopy command used to copy an Oracle ASM or database instance password file to a specified location. See Oracle Real Application Clusters Administration and Deployment Guide for information about using the srvctl utility to modify a database configuration.
The database encryption wallet on a physical standby database must be replaced with a fresh copy of the database encryption wallet from the primary database whenever the TDE master encryption key is reset on the primary database.
Failure to refresh the database encryption wallet on the physical standby database will prevent access to encrypted columns on the physical standby database that are modified after the master encryption key is reset on the primary database.
Oracle Data Guard allows recovery on a physical standby database to continue after the primary database has been opened with the RESETLOGS option. When an ALTER DATABASE OPEN RESETLOGS statement is issued on the primary database, the incarnation of the database changes, creating a new branch of redo data.
When a physical standby database receives a new branch of redo data, Redo Apply automatically takes the new branch of redo data. For physical standby databases, no manual intervention is required if the standby database did not apply redo data past the new resetlogs SCN (past the start of the new branch of redo data). The following table describes how to resynchronize the standby database with the primary database branch.
| If the standby database. . . | Then. . . | Perform these steps. . . |
|---|---|---|
Has not applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) and the new redo branch from OPEN RESETLOGS has been registered at the standby |
Redo Apply automatically takes the new branch of redo. | No manual intervention is necessary. The managed redo process (MRP) automatically resynchronizes the standby database with the new branch of redo data.
Note: To check whether the new redo branch has been registered at the standby, perform the following query at the primary and standby and verify that the results match: SELECT resetlogs_id, resetlogs_change# FROM V$DATABASE_INCARNATION WHERE status='CURRENT' |
| Has applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) and Flashback Database is enabled on the standby database | The standby database is recovered in the future of the new branch of redo data. |
The managed redo process (MRP) automatically resynchronizes the standby database with the new branch. |
| Has applied redo data past the new resetlogs SCN (past the start of the new branch of redo data) and Flashback Database is not enabled on the standby database | The primary database has diverged from the standby on the indicated primary database branch. | Re-create the physical standby database following the procedures in Chapter 3. |
| Is missing intervening archived redo log files from the new branch of redo data | The MRP cannot continue until the missing log files are retrieved. | Locate and register missing archived redo log files from each branch. |
| Is missing archived redo log files from the end of the previous branch of redo data. | The MRP cannot continue until the missing log files are retrieved. | Locate and register missing archived redo log files from the previous branch. |
See Oracle Database Backup and Recovery User's Guide for more information about database incarnations, recovering through an OPEN RESETLOGS operation, and Flashback Database.
This section describes where to find useful information for monitoring primary and standby databases.
Table 10-2 summarizes common primary database management actions and where to find information related to these actions.
Table 10-2 Sources of Information About Common Primary Database Management Actions
This section shows how to use dynamic performance views to monitor primary, physical standby, and snapshot standby databases.
The following dynamic performance views are discussed:
See Also:
Oracle Database Reference for complete reference information about viewsThe following query displays the data protection mode, data protection level, database role, and switchover status for a primary, physical standby or snapshot standby database:
SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, – > DATABASE_ROLE ROLE, SWITCHOVER_STATUS – > FROM V$DATABASE;
The following query displays fast-start failover status:
SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS", - > FS_FAILOVER_CURRENT_TARGET TARGET, - > FS_FAILOVER_THRESHOLD THRESHOLD, - > FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" – > FROM V$DATABASE;
The following query displays Redo Apply and redo transport status on a physical standby database:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,- > BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ------- ------------ ---------- ---------- ---------- ---------- RFS ATTACHED 1 947 72 72 MRP0 APPLYING_LOG 1 946 10 72
The sample output shows that a remote file server (RFS) process completed archiving a redo log file with a sequence number of 947 and that Redo Apply is actively applying an archived redo log file with a sequence number of 946. Redo Apply is currently recovering block number 10 of the 72-block archived redo log file.
The following query displays information about archived redo log files that have been received by a physical or snapshot standby database from a primary database:
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, - > NEXT_CHANGE# FROM V$ARCHIVED_LOG; THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ------------- ------------ 1 945 74651 74739 1 946 74739 74772 1 947 74772 74795
The sample output shows that three archived redo log files have been received from the primary database.
The following query displays archived log history information:
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, - > NEXT_CHANGE# FROM V$LOG_HISTORY;
The following query displays messages generated by Oracle Data Guard events that caused a message to be written to the alert log or to a server process trace file:
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
The following query shows the status of each redo transport destination, and for redo transport destinations that are standby databases, the SCN of the last primary database redo applied at that standby database:
SQL> SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY'; DEST_ID STATUS APPLIED_SCN ---------- --------- ----------- 2 VALID 439054 3 VALID 439054
The Active Data Guard 11g Best Practices (includes best practices for Redo Apply) white paper describes how to optimize Redo Apply and media recovery performance. This paper is available on the Oracle Maximum Availability Architecture (MAA) home page at:
http://www.oracle.com/goto/maa
See Also:
My Oracle Support note 454848.1 athttp://support.oracle.com for information about the installation and use of the Standby Statspack, which can be used to collect Redo Apply performance data from a physical standby databaseA snapshot standby database is a fully updatable standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.
A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.
A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies increased time to recover from primary database failures.
Perform the following steps to convert a physical standby database into a snapshot standby database:
Stop Redo Apply, if it is active.
Ensure that the database is mounted, but not open.
Ensure that a fast recovery area has been configured. It is not necessary for flashback database to be enabled.
Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Open the snapshot standby in read/write mode by issuing the following SQL statement:
SQL> ALTER DATABASE OPEN READ WRITE;
Note:
A physical standby database that is managed by the Oracle Data Guard broker can be converted into a snapshot standby database using either DGMGRL or Oracle Enterprise Manager Cloud Control. See Oracle Data Guard Broker for more details.A snapshot standby database can be opened in read-write mode and is fully updatable.
A snapshot standby database has the following characteristics:
A snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
A snapshot standby database cannot be the only standby database in a Maximum Protection Oracle Data Guard configuration.
Note:
Flashback Database is used to convert a snapshot standby database back into a physical standby database. Any operation that cannot be reversed using Flashback Database technology will prevent a snapshot standby from being converted back to a physical standby.For information about some of the limitations of Flashback Database, see Oracle Database Backup and Recovery User's Guide.
Perform the following steps to convert a snapshot standby database into a physical standby database:
On an Oracle Real Applications Cluster (Oracle RAC) database, shut down all but one instance.
Ensure that the database is mounted, but not open.
Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.
Note:
A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.