15 Oracle Data Guard Scenarios
These scenarios present different situations you might encounter while administering your Oracle Data Guard configuration. Each of them can be adapted to your specific environment.
Note:
A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, "database" and "non-CDB" refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, "non-CDB" refers to a non-CDB from a previous release.
-
Converting a Failed Primary Into a Standby Database Using Flashback Database
-
Using Flashback Database After Issuing an Open Resetlogs Statement
-
Using the DBCOMP Procedure to Detect Lost Writes and Other Inconsistencies
-
Converting a Failed Primary into a Standby Database Using RMAN Backups
-
Changing the Character Set of a Primary Without Re-Creating Physical Standbys
-
Actions Needed On a Standby After a PDB PITR or PDB Flashback On a Primary
Configuring Logical Standby Databases After a Failover
These are the steps required on a logical standby database after the primary database has failed over to another standby database.
After a failover has occurred, a logical standby database cannot act as a standby database for the new primary database until it has applied the final redo from the original primary database. This is similar to the way the new primary database applied the final redo during the failover. The steps you must perform depend on whether the new primary database was a physical standby or a logical standby database prior to the failover:
When the New Primary Database Was Formerly a Physical Standby Database
These steps demonstrate how to configure a logical standby database to support a new primary database that was a physical standby database before it assumed the primary role.
In this scenario, SAT is the logical standby database and NYC is the primary database.
-
On the SAT database, issue the following statement to configure the FAL_SERVER parameter to enable automatic recovery of log files.
SQL> ALTER SYSTEM SET FAL_SERVER='<tns_name_to_new_primary>'; SQL> ALTER SYSTEM SET FAL_CLIENT='<tns_name_to_local_database>';
-
Call the PREPARE_FOR_NEW_PRIMARY routine to verify that the logical standby database is capable of serving as a standby database to the new primary database. During this step, local copies of log files that pose a risk for data divergence are deleted from the local database. These log files are then requested for re-archival directly from the new primary database.
On the SAT database, issue the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY( - > former_standby_type => 'PHYSICAL' - > dblink => 'nyc_link');
Note:
If the
ORA-16109
message is returned and the 'LOGSTDBY: prepare_for_new_primary failure -- applied too far, flashback required.' warning is written in the alert.log, perform the following steps:-
Flash back the database to the SCN as stated in the warning and then
-
Repeat this step before continuing.
See Flashing Back a Logical Standby Database to a Specific Applied SCN for an example of how to flash back a logical standby database to an Apply SCN.
-
-
On the SAT database, issue the following statement to start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
When the New Primary Database Was Formerly a Logical Standby Database
These steps demonstrate how to configure a logical standby database to support a new primary database that was a logical standby database before it assumed the primary role.
In this scenario, SAT is the logical standby database and NYC is the primary database.
-
Ensure the new primary database is ready to support logical standby databases. On the NYC database, ensure the following query returns a value of
NONE
. Otherwise the new primary database has not completed the work required to enable support for logical standby databases. For example:SQL> SELECT PENDING_ROLE_CHANGE_TASKS FROM V$DATABASE;
A value of
NONE
must be returned before you attempt to reinstate an old primary database. -
On the SAT database, issue the following statement to configure the FAL_SERVER parameter to enable automatic recover of log files:
SQL> ALTER SYSTEM SET FAL_SERVER='<tns_name_to_new_primary>'; SQL> ALTER SYSTEM SET FAL_CLIENT='<tns_name_to_local_database>';
-
Call the
PREPARE_FOR_NEW_PRIMARY
routine to verify the logical standby database is capable of being a standby to the new primary. During this step, local copies of log files which pose a risk for data divergence are deleted from the local database. These log files are then requested for re-archival directly from the new primary database.On the SAT database, issue the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY( - > former_standby_type => 'LOGICAL' - > dblink => 'nyc_link');
Note:
If the
ORA-16109
message is returned and theLOGSTDBY: prepare_for_new_primary failure -- applied too far, flashback required
warning is written in the alert.log file, perform the following steps:-
Flash back the database to the SCN as stated in the warning and then
-
Repeat this step before continuing.
See Flashing Back a Logical Standby Database to a Specific Applied SCN for an example of how to flash back a logical standby database to an Apply SCN.
-
-
On the SAT database, issue the following statements to start SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY nyc_link;
This statement must always be issued without the real-time apply option enabled. To enable real-time apply on the logical standby database, wait for the above statement to complete successfully, and then issue the following statements:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Converting a Failed Primary Into a Standby Database Using Flashback Database
After a failover occurs, the original primary database can no longer participate in the Oracle Data Guard configuration until it is repaired and established as a standby database in the new configuration.
To do this, you can use the Flashback Database feature to recover the failed primary database to a point in time before the failover occurred, and then convert it into a physical or logical standby database in the new configuration. The following sections describe:
-
Flashing Back a Failed Primary Database into a Physical Standby Database
-
Flashing Back a Failed Primary Database into a Logical Standby Database
Note:
You must have already enabled Flashback Database on the original primary database before the failover. See Oracle Database Backup and Recovery User's Guide for more information.
-
Flashing Back a Logical Standby Database to a Specific Applied SCN
See Also:
Oracle Data Guard Broker for information about automatic reinstatement of the failed primary database as a new standby database (as an alternative to using Flashback Database)
Flashing Back a Failed Primary Database into a Physical Standby Database
These steps bring the old primary database back into the Oracle Data Guard configuration as a physical standby database.
The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover.
-
On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
-
Shut down the old primary database (if necessary), mount it, and flash it back to the value for
STANDBY_BECAME_PRIMARY_SCN
that was determined in the previous step.SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;
-
To convert the database to a physical standby database, issue the following statement on the old primary database:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
-
To start transporting redo to the new physical standby database, perform the following steps on the new primary database:
-
Issue the following query to see the current state of the archive destinations:
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, - > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
-
If necessary, enable the destination:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
-
Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. Issue the following SQL statements on the new primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,- > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
On the new standby database, you may also need to change the
LOG_ARCHIVE_DEST_
n
initialization parameters so that redo transport services do not transmit redo data to other databases.
-
-
Start Redo Apply on the new physical standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply needs to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See "Performing a Switchover to a Physical Standby Database" for more information.
Flashing Back a Failed Primary Database into a Logical Standby Database
These steps bring the old primary database back into the Oracle Data Guard configuration as a new logical standby database without having to formally instantiate it from the new primary database.
These steps assume that the Oracle Data Guard configuration has already completed a failover involving a logical standby database and that Flashback Database has been enabled on the old primary database.
Flashing Back a Logical Standby Database to a Specific Applied SCN
Using Flashback Database After Issuing an Open Resetlogs Statement
If an error occurrs on the primary database in an Oracle Data Guard configuration in which the standby database is using real-time apply, then the same error is applied on the standby database. if Flashback Database is enabled, you can revert the primary and standby databases back to their pre-error condition.
To do so, issue the FLASHBACK DATABASE
and OPEN RESETLOGS
statements on the primary database, and then issuing a similar FLASHBACK STANDBY DATABASE
statement on the standby database before restarting apply services. (If Flashback Database is not enabled, you need to re-create the standby database, as described in Creating a Physical Standby Database and Creating a Logical Standby Database , after the point-in-time recovery was performed on the primary database.)
Flashing Back a Physical Standby Database to a Specific Point-in-Time
These steps describe how to avoid re-creating a physical standby database after you issue the OPEN RESETLOGS
statement on the primary database.
With Oracle Database Release 19c, if the standby is in mount mode and flashback database is enabled, you may not need to do anything. In the common case, if there is sufficient flashback data available, the standby is automatically flashed back so that the standby continues to follow the primary.
If the standby is open and flashback database is enabled, you can place the standby in mount mode and then start standby recovery. The MRP, in an attempt to let the standby follow the primary, triggers an automatic flashback of the standby. If this operation succeeds, as reported in the alert log, you need to follow up with opening the Active Data Guard instance.
If automatic flashback is not triggered, or automatic flashback did not result in the standby following the primary, you can perform the steps described in this section.
Flashing Back a Logical Standby Database to a Specific Point-in-Time
These steps describe how to avoid re-creating a logical standby database after you have flashed back the primary database and opened it by issuing an OPEN RESETLOGS
statement.
Note:
If SQL Apply detects the occurrence of a resetlogs operation at the primary database, it automatically mines the correct branch of redo, if it is possible to do so without having to flashback the logical standby database. Otherwise, SQL Apply stops with an error ORA-1346: LogMiner processed redo beyond specified reset log scn
. In this section, it is assumed that SQL Apply has already stopped with such an error.
Recovering After the NOLOGGING Clause Is Specified
Some SQL statements allow you to specify a NOLOGGING
clause so that the operation is not logged in the online redo log file.
In actuality, when you specify NOLOGGING
, a redo record is still
written to the online redo log file, but there is no data associated with the record.
This specification can result in log application or data access errors at the standby
site. Manual recovery might be required to resume applying log files. Depending on
whether you have a logical standby or physical standby, you can avoid these errors by
doing the following:
-
Logical standbys
Specify the
FORCE LOGGING
clause in theCREATE DATABASE
orALTER DATABASE
statements. -
Physical standbys
Specify a logging mode that is appropriate to the way in which you plan to use your Data Guard configuration.
You can see the current logging mode in the V$DATABASE.FORCE_LOGGING
column (for CDBs), or the DBA_PDBS.FORCE_LOGGING
column (for PDBs).
Recovery Steps for Logical Standby Databases
On logical standby databases, when SQL Apply encounters a redo record for an operation performed with the NOLOGGING
clause on a table that is not being skipped, it stops with the following error:
ORA-16211 unsupported record found in the archived redo log
To recover after the NOLOGGING
clause is specified, re-create one or more tables from the primary database, as described in Adding or Re-Creating Tables On a Logical Standby Database.
Note:
In general, use of the NOLOGGING
clause is not recommended. Therefore, if you know in advance that operations using the NOLOGGING
clause will be performed on certain tables in the primary database, then you might want to prevent the application of SQL statements associated with these tables to the logical standby database. You can do this by using the DBMS_LOGSTDBY.SKIP
procedure.
Recovery Steps for Physical Standby Databases
When the redo is applied to the physical standby database, a portion of the data file is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as UNRECOVERABLE
, you see error messages similar to the following:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521) ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option
When you use STANDBY NOLOGGING FOR LOAD PERFORMANCE
mode it is still possible for a query executed on a physical standby to report a corrupt block due to a nologging operation. However, when managed recovery is running on that standby as part of an Active Data Guard configuration, it automatically fetches a replacement block from the primary. (Managed recovery is started with the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
statement.) Managed recovery attempts to fetch all such blocks shortly after they are first created and continues attempting to fix them until it succeeds. Whenever managed recovery is started, it again begins fixing any outstanding blocks from earlier recovery sessions. This automatic recovery applies only to corrupt blocks caused by redo generated with the STANDBY NOLOGGING FOR LOAD PERFORMANCE
mode enabled. Blocks corrupted due to conventional nonlogged operations must use the following procedure. These steps describe a simple approach that recovers all nonlogged blocks. (See follow-on sections for other approaches such as determining whether a backup is required after unrecoverable operations, and recovering parts of a physical standby database.)
-
Stop recovery on the standby:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-
Recover the nonlogged blocks by connecting RMAN to the standby and issue the following command:
RMAN> RECOVER DATABASE NONLOGGED BLOCK;
If the presence of unrecoverable blocks is only found after a switchover, then you can use these same two steps, but the primary database must be just mounted ( not open) and, RMAN must be connected to the primary.
It is possible that the RMAN
RECOVER
command may not be able to recover all the nonlogged blocks. The reasons that this might happen are detailed in the alert log of the database from which the RMAN command was executed. The most common reason is that a block has been modified recently at the primary and not yet written to its corresponding data file. This may mean that the block sent to the standby is too old to replace the unrecoverable block on the standby. To resolve this, issue theRECOVER
command again at a later time after the block has been written out to the data file.
The following is an example of an alert log entry for an execution of the RECOVER
command which left some blocks unrecovered:
Started Nonlogged Block Replacement recovery on file 7 (ospid 13005 rcvid 11319003446180375696)
Finished Nonlogged Block Replacement recovery on file 7. 5 blocks remain
Statistics for replacement block source database (service=dg3tns)
(Use of it stopped due to error 12942 received from it)
Blocks requested 5, blocks received 0.
Reason replacement blocks accepted or rejected Blocks Last block
-------------------------------------------------------- ---------- ----------
Not received: Rejected by sender. Wrong state or SCN 5 21
In this case, the command was run on a standby and the primary did not send any blocks but instead reported the following Oracle error: .
ORA-12942: database incarnation at source does not match
An examination of the alert logs would reveal that the primary had performed FLASHBACK DATABASE
and OPEN RESETLOGS
commands, but the standby had not been flashed back. This means the standby would now be on an orphaned branch of redo and therefore the primary could not supply data blocks that would be known to be the correct version.
Note:
When a Data Guard configuration has more than one standby, an RMANRECOVER
command that is run at a standby attempts to fetch blocks from the primary only. When the RECOVER
command is run at the primary it attempts to fetch blocks from only the one standby that it determines to be most likely to yield good blocks, which typically means the standby that is closest to being in synch with the primary.
Determining If a Backup Is Required After Unrecoverable Operations
If you performed unrecoverable operations on your primary database, then you need to determine if a new backup operation is required.
To do so, take the following steps:
See Oracle Database Reference for more information about the V$DATAFILE
view.
Recovery Steps for Part of a Physical Standby Database
The RMAN RECOVER ... NONLOGGED BLOCK
command can be used to recover blocks that belong to a set of data files or a set of tablespaces or just a single pluggable database (PDB) as well as the multitenant container database (CDB).
This ability may be useful if it is acceptable to have nonlogged blocks remain in some data files, for example because it is explicitly known that the nonlogged blocks were created as a result of loading rows into an object that has now been dropped.
The view V$NONLOGGED_BLOCK
usually lists the ranges of known invalid blocks for each data file and the entries are maintained as part of media recovery. However, there are times when the information is not complete. Typically this is after upgrading from a release prior to Oracle Database 12c Release 1 (12.1) or after restoring an operating system backup of a data file. The next time media recovery is run, the stale entries are removed and any newly invalidated blocks are recorded but any prior invalid blocks do not have entries in V$NONLOGGED_BLOCK
. The FIRST_NONLOGGED_SCN
column in the V$DATAFILE
view can still be used to see that there is at least one invalid block in a data file even when there are no V$NONLOGGED_BLOCK
entries for a data file.
The RMAN command VALIDATE ... NONLOGGED BLOCK
can be used to bring the entries in V$NONLOGGED_BLOCK
back into synchronization with the data files. It does this by determining if the existing ranges are complete and if not, it scans the necessary data files to identify any invalid blocks and make sure they are captured by an entry in V$NONLOGGED_BLOCK
. The VALIDATE ... NONLOGGED BLOCK
command has the same options as the RECOVER ... NONLOGGED BLOCK
command to validate just a set of data files or a set of tablespaces or a PDB, as well as the CDB.
Note:
Entries in theV$NONLOGGED_BLOCK
view represent a superset of the invalid blocks, and some normal blocks that are close to an invalid block may be included. For example, if there is an entry for file 7 that starts at block 100 and has 50 blocks in it, then none, some, or all of those 50 blocks are invalid. After the VALIDATE
command is run, there are no ranges that have no invalid blocks in them and the first and last block of a range are also be invalid blocks. However, there are limits to the total number of ranges that can be held in the control file so sometimes it may be necessary to merge ranges for the same file, causing regular blocks to be included in a range.
If only offline data files are to be validated or recovered then the database to which they belong can be open at the time the RMAN command is run.
Creating a Standby Database That Uses OMF or Oracle ASM
When you create standby databases, there are additional steps that must be performed if the primary database uses Oracle Managed Files (OMF) or Oracle Automatic Storage Management (Oracle ASM).
The discussion in this section is presented at a level of detail that assumes you already know how to create a physical standby database and are an experienced user of the RMAN, OMF, and Oracle ASM features.
Perform the following tasks to prepare for standby database creation:
-
Enable forced logging on the primary database.
-
Enable archiving on the primary database.
-
Set all necessary initialization parameters on the primary database.
-
Create an initialization parameter file for the standby database.
-
If the primary database is configured to use OMF, then Oracle recommends that the standby database be configured to use OMF, too. To do this, set the
DB_CREATE_FILE_DEST
andDB_CREATE_ONLINE_LOG_DEST_
n
initialization parameters to appropriate values. Maintenance and future role transitions are simplified if the same disk group names are used for both the primary and standby databases.Note:
If OMF parameters are set on the standby, then new files on that standby are always created as OMF, regardless of how they were created on the primary. Therefore, if both the
DB_FILE_NAME_CONVERT
andDB_CREATE_FILE_DEST
parameters are set on the standby, theDB_CREATE_FILE_DEST
parameter takes precedence. -
Set the
STANDBY_FILE_MANAGEMENT
initialization parameter toAUTO
. -
Configure Oracle Net, as required, to allow connections to the standby database.
-
Configure redo transport authentication as described in Configure Redo Transport Authentication.
-
Start the standby database instance without mounting the control file.
Perform the following tasks to create the standby database:
-
If the standby database is going to use Oracle ASM, create an Oracle ASM instance if one does not already exist on the standby database system.
-
Use the RMAN
BACKUP
command to create a backup set that contains a copy of the primary database's data files, archived log files, and a standby control file. -
Use the RMAN
DUPLICATE FOR STANDBY
command to copy the data files, archived redo log files and standby control file in the backup set to the standby database's storage area.The
DUPLICATE FOR STANDBY
command performs the actual data movement at the standby instance. If the backup set is on tape, the media manager must be configured so that the standby instance can read the backup set. If the backup set is on disk, the backup pieces must be readable by the standby instance, either by making their primary path names available through Network File Storage (NFS), or by copying them to the standby system and using RMANCATALOG BACKUPPIECE
command to catalog the backup pieces before restoring them.
After you successfully complete these steps, continue with the steps in Verify the Physical Standby Database Is Performing Properly, to verify the configuration of the physical standby database.
To create a logical standby database, continue with the standby database creation process described in Creating a Logical Standby Database , but with the following modifications:
After you successfully complete these steps, continue with the steps in Open the Logical Standby Database to start, recover, and verify the logical standby database.
See Also:
-
Oracle Database Administrator's Guide for information about OMF
-
Oracle Automatic Storage Management Administrator's Guide for more information about Oracle ASM
-
Oracle Database Backup and Recovery User's Guide for information about RMAN
Recovering From Lost-Write Errors on a Primary Database
During media recovery in an Oracle Data Guard configuration, a physical standby database can be used to detect lost-write data corruption errors on the primary database.
This is done by comparing SCNs of blocks stored in the redo log on the primary database to SCNs of blocks on the physical standby database. If the SCN of the block on the primary database is lower than the SCN on the standby database, then there was a lost-write error on the primary database.
In such a situation, if lost write detection (set with the DB_LOST_WRITE_PROTECT
initialization parameter) is enabled at both the primary and standby, then a recovery attempt at the standby results in an ORA-752
error. If lost write detection is not enabled, then a recovery attempt results in an ORA-600
[3020]
error. However, not all ORA-600
[3020]
errors are due to lost writes at the primary. Therefore, before following the guidelines given in this section, work with your Oracle Support representative to determine whether the root cause for the ORA-600
[3020]
error was indeed a lost write that occurred on the primary. Also see "Resolving ORA-752 or ORA-600 [3020] During Standby Recovery" in the My Oracle Support Note 1265884.1 at http://support.oracle.com
.
Note:
Because lost-write errors are detected only when a block is read into the cache by a primary and the corresponding redo is later compared to the block on the standby, there may be undetected stale blocks on both the primary and the standby that have not yet been read and verified. These stale blocks do not affect operation of the current database because until those blocks are read, all blocks that have been used up to the SCN of the currently applied redo on the standby to do queries or updates were verified by the standby.
When a primary lost-write error is detected on the standby, one or more block error messages similar to the following for each stale block are printed in the alert file of the standby database:
Tue Dec 12 19:09:48 2006 STANDBY REDO APPLICATION HAS DETECTED THAT THE PRIMARY DATABASE LOST A DISK WRITE OF BLOCK 26, FILE 7 NO REDO AT OR AFTER SCN 389667 CAN BE USED FOR RECOVERY. . . .
The alert file then shows that an ORA-00752
error is raised on the standby database and the managed recovery is cancelled:
Slave exiting with ORA-752 exception Errors in file /oracle/log/diag/rdbms/dgstwrite2/stwrite2/trace/stwrite2_pr00_23532.trc: ORA-00752: recovery detected a lost write of a data block ORA-10567: Redo is inconsistent with data block (file# 7, block# 26) ORA-10564: tablespace TBS_2 ORA-01110: data file 7: '/oracle/dbs/btbs_21.f' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 57503 . . .
The standby database is then recovered to a consistent state, without any corruption to its data files caused by this error, at the SCN printed in the alert file:
Recovery interrupted! Recovered data files to a consistent state at change 389569
This last message may appear significantly later in the alert file and it may have a lower SCN than the block error messages. Also, the primary database may operate without visible errors even though its data files may already be corrupted.
The recommended procedure to recover from such errors is a failover to the physical standby, as described in the following steps.
Steps to Failover to a Physical Standby After Lost-Writes Are Detected on the Primary
-
Shut down the primary database. All data at or after the SCN printed in the block error messages is lost.
-
Issue the following SQL statement on the standby database to convert it to a primary:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. Tue Dec 12 19:15:23 2006 alter database activate standby database ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (stwrite2) RESETLOGS after incomplete recovery UNTIL CHANGE 389569 Resetting resetlogs activation ID 612657558 (0x24846996) Online log /oracle/dbs/bt_log1.f: Thread 1 Group 1 was previously cleared Online log /oracle/dbs/bt_log2.f: Thread 1 Group 2 was previously cleared Standby became primary SCN: 389567 Tue Dec 12 19:15:23 2006 Setting recovery target incarnation to 3 Converting standby mount to primary mount. ACTIVATE STANDBY: Complete - Database mounted as primary (stwrite2) Completed: alter database activate standby database
-
Back up the new primary. Performing a backup immediately is a necessary safety measure, because you cannot recover changes made after the failover without a complete backup copy of the database. As a result of the failover, the original primary database can no longer participate in the Oracle Data Guard configuration, and all other standby databases now receive and apply redo data from the new primary database.
-
Open the new primary database.
-
An optional step is to recreate the failed primary as a physical standby. You can do this using the database backup taken at the new primary in step 3. (You cannot use flashback database or the Oracle Data Guard broker to reinstantiate the old primary database in this situation.) Be aware that a physical standby created using the backup taken from the new primary has the same data files as the old standby. Therefore, any undetected lost writes that the old standby had before it was activated are not detected by the new standby, since the new standby compares the same blocks. Any new lost writes that happen on either the primary or the standby are detected.
See Also:
Oracle Database Backup and Recovery User's Guide for more information about enabling lost-write detection
Using the DBCOMP Procedure to Detect Lost Writes and Other Inconsistencies
You can use the PL/SQL procedure, DBMS_DBCOMP.DBCOMP
, to detect lost writes and also to detect inconsistencies between a primary database and physical standby databases.
DBMS_DBCOMP.DBCOMP
procedure compares the same data blocks on the primary and physical standby databases. The procedure can be executed at any time. (It does not require that the DB_LOST_WRITE_PROTECT
initialization parameter be enabled.)
You can monitor the progress of an on-going block comparison operation by querying the V$SESSION_LONGOPS
view.
Note:
Logical standby databases, far sync instances, and cascaded standbys cannot be the target database for theDBMS_DBCOMP.DBCOMP
procedure.
DBMS_DBCOMP.DBCOMP
procedure assumes that there is one primary database and one or more physical standby databases. The databases should be at least mounted before block comparison.
In the following example situations, assume that there is a primary database with a unique name of dgmain
, and that physical standby databases are named dgmainb
, dgmainc
, dgmaind
, and so on.
Example 15-1 Primary and All Standbys Are Mounted or Open and DBCOMP Is Executed From the Primary
In this situation, when the DBCOMP
procedure is executed from the primary database, the specified data files are compared block by block between the primary and every physical standby database. For example, suppose that you perform the following query:
SQL> exec sys.dbms_dbcomp.dbcomp(‘1’,’BlockCompare’,:retval);
The generated output files are BlockCompare_dgmainb_1
and BlockCompare_dgmainc_d_1
.
Example 15-2 Primary and All Standbys Are Mounted or Open and DBCOMP Is Executed From a Standby
In this situation, when the DBCOMP
procedure is executed from one of the standby databases (for example, dgmainb
), the specified data files are compared only between the primary and that particular standby database. Other standby databases are not considered. For example, suppose that you perform the following query:
SQL> exec sys.dbms_dbcomp.dbcomp(‘1’,’BlockCompare’,:retval);
The generated output file is BlockCompare_dgmain_1
.
Example 15-3 Primary Is Mounted or Open, But Not All Standbys Are, and DBCOMP is Executed From the Primary
In this situation, when theDBCOMP
procedure is executed on the primary, the specified data files are compared between the primary database and the mounted or open physical standby databases. For those standby databases that are neither mounted nor open, no action is taken.
Example 15-4 Primary Is Mounted or Open, But Not All Standbys Are, and DBCOMP is Executed From a Standby
In this situation, the specified data files are compared between the primary and the standby from which the DBCOMP
procedure is executed.
Example 15-5 Primary is Not Mounted, But Multiple Standbys Are Mounted or Open
Because the primary database is neither mounted nor open, theDBCOMP
procedure cannot find an appropriate pair of primary and physical standby databases to compare. An ORA
error message is not returned, but a message similar to the following is printed out in the corresponding output file: Remote database is not in the primary role
.
Example 15-6 Primary Is Mounted or Open, But No Standbys Are Mounted or Open
Because no appropriate pair of primary and physical standby databases are found, a message is printed out in the corresponding output file, but no ORA
error is returned.
Related Topics
Converting a Failed Primary into a Standby Database Using RMAN Backups
To convert a failed primary database, Oracle recommends that you enable the Flashback Database feature on the primary and follow one of these procedures, as appropriate.
-
Flashing Back a Failed Primary Database into a Physical Standby Database
-
Flashing Back a Failed Primary Database into a Logical Standby Database
The procedures in these sections describe the fastest ways to convert a failed primary into either a physical or logical standby. However, if Flashback Database was not enabled on the failed primary, you can still convert the failed primary into either a physical or logical standby using a local backup of the failed primary, as described in the following topics:
Converting a Failed Primary into a Physical Standby Using RMAN Backups
These steps describe how to convert a failed primary into a physical standby by using RMAN backups.
This procedure requires that the COMPATIBLE
initialization parameter of the old primary be set to at least 11.0.0.
-
On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
-
Restore the database with a backup taken before the old primary had reached the SCN at which the standby became the new primary (
standby_became_primary_scn)
. Then, perform a point-in-time recovery to recover the old primary to that same point.Issue the following RMAN commands:
RMAN> RUN { SET UNTIL SCN <standby_became_primary_scn + 1>; RESTORE DATABASE; RECOVER DATABASE; }
With user-managed recovery, you can first restore the database manually. Typically, a backup taken a couple of hours before the failover would be old enough. You can then recover the failed primary using the following command:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CHANGE - > <standby_became_primary_scn + 1>;
Unlike a reinstantiation that uses Flashback Database, this procedure adds one to
standby_became_primary_scn
. For data files, flashing back to an SCN is equivalent to recovering up until that SCN plus one. -
Perform the following steps on the old primary database:
-
Issue the following statement on the old primary database:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
-
Shut down and restart the database:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;
-
-
Issue the following command:
SQL> ALTER DATABASE OPEN READ ONLY;
The goal of this step is to synchronize the control file with the database by using a dictionary check. After this command, check the alert log for any actions suggested by the dictionary check. Typically, no user action is needed if the old primary was not in the middle of adding or dropping data files during the failover.
-
If you have purchased a license for the Oracle Active Data Guard option and would like to operate your physical standby database in active query mode, skip this step. Otherwise, bring your standby database to the mount state.
For example:
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;
-
Before the new standby database was created, the new primary database probably stopped transmitting redo to the remote destination. To restart redo transport services, perform the following steps on the new primary database:
-
Issue the following query to see the current state of the archive destinations:
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, - > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
-
If necessary, enable the destination:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
-
Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully.
Note:
This is an important step in order for the old primary to become a new standby following the new primary. If this step is not done, the old primary may recover to an incorrect database branch. The only way to correct the problem then is to convert the old primary again.
At the SQL prompt, enter the following statements:
SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, - > ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
On the new standby database, you may also need to change the
LOG_ARCHIVE_DEST_
n
initialization parameters so that redo transport services do not transmit redo data to other databases. This step can be skipped if both the primary and standby database roles were set up with theVALID_FOR
attribute in one server parameter file (SPFILE). By doing this, the Oracle Data Guard configuration operates properly after a role transition.
-
-
Start Redo Apply on the new physical standby database, as follows:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See "Performing a Switchover to a Physical Standby Database" for more information.
Converting a Failed Primary into a Logical Standby Using RMAN Backups
These steps describe how to convert a failed primary into a logical standby using RMAN backups.
-
On the new primary database, issue the following query to determine the SCN to which you want to recover the failed primary database:
SQL> SELECT APPLIED_SCN RECOVERY_SCN FROM V$LOGSTDBY_PROGRESS;
Also on the new primary database, determine the SCN to use in dealing with archive logs, as follows:
-
Ensure all standby redo logs have been archived. Issue the following query, looking for a value of
NONE
to be returned. Depending on the size of the database and the number of logs needing to be archived, it could take some time before a status ofNONE
is returned.SQL> SELECT PENDING_ROLE_CHANGE_TASKS FROM V$DATABASE;
-
After a status of
NONE
has been returned, run the following query to retrieve the SCN for dealing with archive logs as part of this recovery:SQL> SELECT VALUE ARCHIVE_SCN FROM SYSTEM.LOGSTDBY$PARAMETERS - > WHERE NAME='STANDBY_BECAME_PRIMARY_SCN';
-
-
Remove any archive logs created at the time of, or after the failover operation, from the failed primary database. If the failed primary database was isolated from the standby, it could have divergent archive logs that are not consistent with the current primary database. To ensure these divergent archive logs are never applied, they must be deleted from backups and the fast recovery area. You can use the following RMAN command to delete the relevant archive logs from the fast recovery area:
RMAN> DELETE FORCE ARCHIVELOG FROM SCN ARCHIVE_SCN;
Once deleted, these divergent logs and subsequent transactions can never be recovered.
-
On the new primary database, issue the following query to determine the minimum set of log files that must be copied to the failed primary database before recovering from a backup:
SQL> SELECT file_name FROM DBA_LOGSTDBY_LOG WHERE next_change# > ARCHIVE_SCN;
Retrieve the required standby logs, copy the backup set to the new standby and restore it to the new standby fast recovery area. Because these logs are coming from standby redo logs, they are not part of the standby's standard archives. The RMAN utility is able to use a partial file name to retrieve the files from the correct location.
The following is a sample use of the RMAN
BACKUP
command:RMAN> BACKUP AS COPY DEVICE TYPE DISK FORMAT '/tmp/test/%U' > ARCHIVELOG LIKE '<partial file names from above>%';
The following is a sample use of the RMAN
RESTORE
command:RMAN> CATALOG START WITH '/tmp/test'; RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 33 UNTIL SEQUENCE 35;
-
Restore a backup of all the original primary's data files and recover to
RECOVERY_SCN + 1
. Oracle recommends that you leverage the current control file.-
Start up the database in restricted mode to protect it from rogue transactions until the
GUARD ALL
command can be issued after the database has been opened. -
Use the backup to restore the data files of the failed primary database.
-
Turn off flashback database, if it is enabled (necessary for the
USING BACKUP CONTROLFILE
clause). -
Perform point-in-time recovery to
RECOVERY_SCN +1
in SQL*Plus.
Whether you are using a current control file or a backup control file, you must specify the
USING BACKUP CONTROLFILE
clause to allow you to point to the archive logs being restored. Otherwise, the recovery process could attempt to access online redo logs instead of the logs retrieved in Step 3. When prompted for the sequences retrieved in Step 3, ensure you specify the file names of the restored archive log copies, as follows:SQL> RECOVER DATABASE UNTIL CHANGE RECOVERY_SCN + 1 USING BACKUP CONTROLFILE;
-
-
Open the database with the RESETLOGS option:
SQL> ALTER DATABASE OPEN RESETLOGS;
-
Enable Database Guard
SQL> ALTER DATABASE GUARD ALL;
-
Create a database link to the new primary database and start SQL Apply:
SQL> CREATE PUBLIC DATABASE LINK myLink - > CONNECT TO SYSTEM IDENTIFIED BY password - > USING 'service name of new primary database';
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY myLink;
At this point, you can disable restricted session (
ALTER SYSTEM DISABLE RESTRICTED SESSION
) or, if you need to restart the database to re-enable Flashback from Step 4c, let this restart turn offRESTRICTED SESSION
.
Changing the Character Set of a Primary Without Re-Creating Physical Standbys
Oracle Data Guard allows you to change both the database character set and the national character set of a primary database without requiring you to recreate any physical standby databases in the configuration.
You can continue to use your physical standby database with minimal disruption while performing character set conversion of a primary database.
The character set migration process consists of preparatory steps such as scanning for possible issues and identifying methods to solve them. During the execution of these preparatory steps the Oracle Data Guard configuration can operate unchanged and no extra steps are required to maintain the physical standby. After the preparatory steps are completed, the actual conversion is performed which may involve changes to both system data (metadata) and user data. Several procedures specific to Oracle Data Guard must be run as part of the conversion. The steps to run these procedures are interspersed with the steps performed by the Database Migration Assistant for Unicode (DMU) or other appropriate character set migration tool.
For a detailed description of the steps involved in this process, see My Oracle Support note 1124165.1 at http://support.oracle.com
.
Actions Needed On a Standby After a PDB PITR or PDB Flashback On a Primary
After you perform a PDB PITR or PDB Flashback on a primary, you can either restore the PDB or flashback the PDB on the standby to let the standby follow the primary.
With Oracle Database Release 19c, if the standby is in mount mode and flashback database is enabled, you may not need to do anything. In the common case, if there is sufficient flashback data, the standby is automatically flashed back so that the standby continues to follow the primary.
In all other cases, that is if the standby is open, refer to the "Recovery After PDB PITR or PDB Flashback in an Active Data Guard Environment" section.
Recovery After PDB PITR or PDB Flashback in an Active Data Guard Environment
In an Active Data Guard environment, PDB recovery isolation ensures that media recovery of a CDB on the standby is not impacted when one or more PDBs are not consistent with the rest of the CDB.
About PDB Recovery Isolation
Note:
PDB recovery isolation is enabled only in an Active Data Guard environment.When Active Data Guard media recovery discovers that a hot cloning, point-in-time recovery, or flashback database operation was performed on a PDB, it performs the following steps:
-
Temporarily marks the PDB as disabled and continues with CDB media recovery.
-
Automatically recovers the PDB in a separate background session. This is called PDB recovery isolation.
-
Enables the PDB after the PDB recovery isolation completes.
-
Merges the PDB recovery isolation session with the Active Data Guard media recovery session.
For PDB recovery isolation to be performed, the CDB must be open. Multiple PDB recovery isolation sessions can work in parallel, one for each PDB. When Active Data Guard media recovery is stopped and then restarted, the PDB recovery isolation is automatically restarted.
Although PDB recovery isolation is an automatic process, you can manually stop and start PDB recovery isolation. If you manually stop PDB recovery isolation, you must subsequently restart it. Else PDB recovery isolation remains disabled.
To manually stop PDB recovery isolation:
-
Use the
ALTER PLUGGABLE DATABASE
command with theRECOVER MANAGED STANDBY CANCEL
clause.For example, to stop PDB recovery isolation for the PDB
my_pdb
:ALTER PLUGGABLE DATABASE my_pdb RECOVER MANAGED STANDBY DATABASE CANCEL;
You can also use the
ALTER DATABASE
command if you are connected to the PDB or if the current container is set to the PDB.
When PDB recovery isolation is stopped, the value is the
RECOVERY_STATUS
column in V$PDBS
or
V$CONTAINER
is DISABLED
.
To manually start PDB recovery isolation: