12 Data Guard Scenarios

12.1.1 Configuring a Primary Database and a Physical Standby Database

Figure 12-1 shows the chicago primary database, the boston physical standby database, and the initialization parameters for each system.

Figure 12-1 Primary and Physical Standby Databases Before a Role Transition

Description of "Figure 12-1 Primary and Physical Standby Databases Before a Role Transition"

Table 12-2 shows the initialization parameters for the configuration in Figure 12-1.

DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST=/arch1/chicago/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/boston/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=chicago
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST=/arch1/boston/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

The following table describes the archival processing shown in Figure 12-1:

Figure 12-2 shows the same configuration after a switchover.

Figure 12-2 Primary and Physical Standby Databases After a Role Transition

Description of "Figure 12-2 Primary and Physical Standby Databases After a Role Transition"

The following table describes the archival processing shown in Figure 12-2:

12.1.2 Configuring a Primary Database and a Logical Standby Database

Figure 12-3 shows the chicago database running in the primary role, the denver database running in the logical standby role, and the initialization parameters for each system. Inactive components are grayed out.

Figure 12-3 Configuring Destinations for a Primary Database and a Logical Standby Database

Description of "Figure 12-3 Configuring Destinations for a Primary Database and a Logical Standby Database"

Table 12-3 shows the initialization parameters for the configuration in Figure 12-3.

DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,denver)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'LOCATION=/arch2/chicago/
  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_3=
 'SERVICE=denver   
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
STANDBY_ARCHIVE_DEST=/arch2/chicago/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

DB_UNIQUE_NAME=denver
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,denver)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/denver/
  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_2=
 'LOCATION=/arch2/denver/
  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_3=
 'SERVICE=chicago  
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
STANDBY_ARCHIVE_DEST=/arch2/denver/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

The following table describes the archival processing shown in Figure 12-3:

Unlike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files (online redo log files, archived redo log files, and standby redo log files). It is good practice to specify separate local destinations for:

• Archived redo log files that store redo data generated by the logical standby database. In Figure 12-3, this is configured as the LOG_ARCHIVE_DEST_1=LOCATION=/arch1/denver destination.

• Archived redo log files that store redo data received from the primary database. In Figure 12-3, this is configured as the LOG_ARCHIVE_DEST_2=LOCATION=/arch2/denver destination.

  In Figure 12-3, the STANDBY_ARCHIVE_DEST parameter is configured to the same location for these purposes:

  • If the standby redo log files fill up, redo data received from the primary database is archived directly to the archived redo log files in this location (described in Section 5.7.1).

  • If there is an archive gap, archived redo log files retrieved from other databases are copied to this location (described in Section 5.8).

Because the example configurations shown in Figure 12-3 (and Figure 12-4) do not include a physical standby database, the configuration sets up the LOG_ARCHIVE_DEST_3 destination for switchover with the logical standby database. Figure 12-4 shows the same configuration after a switchover.

Figure 12-4 Primary and Logical Standby Databases After a Role Transition

Description of "Figure 12-4 Primary and Logical Standby Databases After a Role Transition"

The following table describes the archival processing shown in Figure 12-4:

12.1.3 Configuring Both Physical and Logical Standby Databases

Figure 12-5 shows the chicago database running in the primary role, the boston database running in the physical standby role, and the denver database running in the logical standby database role. The initialization parameters are shown under each system. Components that are grayed out are inactive for the database's current role. This example assumes that a switchover would occur only between chicago and boston. In this configuration, the denver logical standby database is intended to be a reporting database only; denver will never be the target of a switchover or run in the primary database role.

Figure 12-5 Configuring a Primary Database with Physical and Logical Standby Databases

Description of "Figure 12-5 Configuring a Primary Database with Physical and Logical Standby Databases"

Table 12-4 shows the initialization parameters for the databases in Figure 12-5.

DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,boston,denver)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/boston/
  VALID_FOR=(ONLINE_
LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=denver
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_3=
  'SERVICE=chicago
  VALID_FOR=
  (ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
STANDBY_ARCHIVE_DEST=/arch1/boston/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,boston,denver)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ONLINE_
LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=denver
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_3=
  'SERVICE=boston
  VALID_FOR=
  (ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
STANDBY_ARCHIVE_DEST=/arch1/chicago/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

DB_UNIQUE_NAME=denver
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(chicago,boston,denver)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/denver/
  VALID_FOR=(ONLINE_
LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_2=
 'LOCATION=/arch2/denver/
  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
  DB_UNIQUE_NAME=denver'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST=/arch2/denver/
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

The following table describes the archival processing shown in Figure 12-5:

Figure 12-6 shows the same configuration after a switchover changes the chicago database to the standby role and the boston database to the primary role.

Figure 12-6 Primary, Physical, and Logical Standby Databases After a Role Transition

Description of "Figure 12-6 Primary, Physical, and Logical Standby Databases After a Role Transition"

The following table describes the archival processing shown in Figure 12-6:

12.1.4 Verifying the Current VALID_FOR Attribute Settings for Each Destination

To see whether or not the current VALID_FOR attribute settings are valid right now for each destination in the Data Guard configuration, query the V$ARCHIVE_DEST view, as shown in Example 12-1.

SQL> SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW FROM V$ARCHIVE_DEST;
DEST_ID  VALID_TYPE      VALID_ROLE   VALID_NOW
-------  --------------- ------------ ----------------
1        ALL_LOGFILES    ALL_ROLES    YES
2        STANDBY_LOGFILE STANDBY_ROLE WRONG VALID_TYPE
3        ONLINE_LOGFILE  STANDBY_ROLE WRONG VALID_ROLE
4        ALL_LOGFILES    ALL_ROLES    UNKNOWN
5        ALL_LOGFILES    ALL_ROLES    UNKNOWN
6        ALL_LOGFILES    ALL_ROLES    UNKNOWN
7        ALL_LOGFILES    ALL_ROLES    UNKNOWN
8        ALL_LOGFILES    ALL_ROLES    UNKNOWN
9        ALL_LOGFILES    ALL_ROLES    UNKNOWN
10       ALL_LOGFILES    ALL_ROLES    UNKNOWN
 10 rows selected.

• LOG_ARCHIVE_DEST_2 is set to (STANDBY_LOGFILES,STANDBY_ROLE), but the WRONG VALID_TYPE is returned because this standby destination does not have a standby redo log implemented.

• LOG_ARCHIVE_DEST_3 is set to (ONLINE_LOGFILES,STANDBY_ROLE), but the WRONG VALID_ROLE is returned because this destination is currently running in the primary database role.

All of the other destinations are shown as UNKNOWN, which indicates the destinations are either undefined or the database is started and mounted but archiving is not currently taking place. See the V$ARCHIVE_DEST view in the Oracle Database Reference for information about these and other columns.

12.2.1 Example: Best Physical Standby Database for a Failover

In a disaster, the most critical task for the DBA is to determine if it is quicker and safer to repair the primary database or fail over to a standby database. When deciding that a failover is necessary and multiple physical standby databases are configured, the DBA must choose which physical standby database is the best target for the failover. While there are many environmental factors that can affect which standby database represents the best choice, this scenario assumes these things to be equal for emphasizing data loss assessment.

This scenario begins with a Data Guard configuration consisting of the HQ primary database and two physical standby databases, SAT and NYC. The HQ database is operating in maximum availability protection mode, and the standby databases are each configured with three standby redo log files. See Section 1.4 for more information about the maximum availability protection mode for physical standby databases.

Table 12-5 provides information about the databases used in this scenario.

Assume that an event occurs in San Francisco where the primary site is located, and the primary site is damaged in such a way that it cannot be repaired in a timely manner. You must fail over to one of the standby databases. You cannot assume that the DBA who set up the multiple standby database configuration is available to decide to which standby database to fail over. Therefore, it is imperative to have a disaster recovery plan at each standby site, as well as at the primary site. Each member of the disaster recovery team needs to know about the disaster recovery plan and be aware of the procedures to follow. This scenario identifies the information you need when deciding which standby database should be the target of the failover.

One method of conveying information to the disaster recovery team is to include a ReadMe file at each standby site. This ReadMe file is created and maintained by the DBA and should describe how to:

• Log on to the local Oracle database as a DBA

• Log on to each system where the standby databases are located

• Get instructions for going through firewalls, because there might be firewalls between systems

• Log on to other Oracle databases as a DBA

• Identify the most up-to-date standby database

• Perform the standby database failover

• Configure network settings to ensure client applications access the new primary database, instead of the original primary database

When choosing a standby database, there are two critical considerations: which standby database received the most recent redo data and which standby database has applied the most redo.

Follow these steps to determine which standby database is the best candidate for failover when only physical standby databases are in the configuration. Always start with the standby database providing the highest protection level. In this scenario, the Seattle standby database provides the highest protection level because it is operating in maximum availability protection mode.

Step 1 Connect to the SAT physical standby database.

Issue a SQL statement such as the following:

SQL> CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

Step 2 Determine how much current redo data is available in the archived redo log file.

Query the columns in the V$MANAGED_STANDBY view, as shown:

SQL> SELECT THREAD#, SEQUENCE#, BLOCK#, BLOCKS
  2> FROM V$MANAGED_STANDBY WHERE STATUS='RECEIVING';

   THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- ---------- ---------- ----------
         1         14        234         16

This standby database received 249 blocks of redo data from the primary database. To compute the number of blocks received, add the BLOCKS column value to the BLOCK# column value, and subtract 1 (because block number 234 is included in the 16 blocks received).

Step 3 Obtain a list of the archived redo log files that were applied or are currently pending application to the SAT database.

Query the V$ARCHIVED_LOG view:

SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
  2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;
FILE_NAME                  SEQUENCE# APP
------------------------- ---------- ---
/oracle/dbs/hq_sat_2.log           2 YES 
/oracle/dbs/hq_sat_3.log           3 YES 
/oracle/dbs/hq_sat_4.log           4 YES
/oracle/dbs/hq_sat_5.log           5 YES 
/oracle/dbs/hq_sat_6.log           6 YES 
/oracle/dbs/hq_sat_7.log           7 YES 
/oracle/dbs/hq_sat_8.log           8 YES 
/oracle/dbs/hq_sat_9.log           9 YES
/oracle/dbs/hq_sat_10.log         10 YES
/oracle/dbs/hq_sat_11.log         11 YES
/oracle/dbs/hq_sat_13.log         13  NO

This output indicates that archived redo log file 11 was completely applied to the standby database. (The line for log file 11 in the example output is in bold typeface to assist you in reading the output. The actual output will not display bolding.)

Also, notice the gap in the sequence numbers in the SEQUENCE# column. In the example, the gap indicates the SAT standby database is missing archived redo log file number 12.

Step 4 Connect to the NYC database to determine if it is more recent than the SAT standby database.

Issue a SQL statement such as the following:

SQL> CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

Step 5 Determine how much current redo data is available in the archived redo log file.

Query the columns in the V$MANAGED_STANDBY view as shown:

SQL> SELECT THREAD#, SEQUENCE#, BLOCK#, BLOCKS
  2> FROM V$MANAGED_STANDBY WHERE STATUS='RECEIVING';

   THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- ---------- ---------- ----------
         1         14        157         93

This standby database has also received 249 blocks of redo information from the primary database. To compute the number of blocks received, add the BLOCKS column value to the BLOCK# column value, and subtract 1 (because block number 157 is included in the 93 blocks received).

Step 6 Obtain a list of the archived redo log files that were applied or are currently pending application to the NYC database.

Query the V$ARCHIVED_LOG view:

SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
  2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;

FILE_NAME                  SEQUENCE# APP
------------------------- ---------- ---
/oracle/dbs/hq_nyc_2.log           2 YES 
/oracle/dbs/hq_nyc_3.log           3 YES 
/oracle/dbs/hq_nyc_4.log           4 YES
/oracle/dbs/hq_nyc_5.log           5 YES 
/oracle/dbs/hq_nyc_6.log           6 YES 
/oracle/dbs/hq_nyc_7.log           7 YES 
/oracle/dbs/hq_nyc_8.log           8  NO 
/oracle/dbs/hq_nyc_9.log           9  NO
/oracle/dbs/hq_nyc_10.log         10  NO
/oracle/dbs/hq_nyc_11.log         11  NO
/oracle/dbs/hq_nyc_12.log         12  NO
/oracle/dbs/hq_nyc_13.log         13  NO

This output indicates that archived redo log file 7 was completely applied to the standby database. (The line for log file 7 in the example output is in bold typeface to assist you in reading the output. The actual output will not display bolding.)

More redo data was received at this location, but less was applied to the standby database.

Step 7 Choose the best target standby database.

In most cases, the physical standby database you choose as a failover target should provide a balance between risk of data loss and time required to perform the role transition. As you analyze this information to make a decision about the best failover candidate in this scenario, consider the following:

• For minimal risk of data loss during a failover, you should choose the NYC database as the best target standby database because Steps 5 and 6 revealed that the NYC site has the most recoverable redo.

• For minimal primary database downtime during the failover operation, you should choose the SAT database as the best target standby database. This database is a more appropriate candidate because the queries in Steps 2 through 6 reveal that the SAT database applied 5 archived redo log files more than the NYC database. However, if it is not possible to obtain and apply a copy of the missing archived redo log file (log 12 in the example), then you will not be able to make the SAT database as current as you can the NYC database. Therefore, you will lose the unapplied data (log files 12, 13, and part of log file 14 in the example).

Based on your business requirements, choose the best target standby database.

Step 8 Bring the selected standby database to its most current state.

• If you chose the SAT database as the best target based on your business requirements, perform the following steps:

  1. Retrieve any missing archived redo log files using an operating system copy utility. (This example uses the UNIX cp command). In this case, the SAT database is missing archived redo log file 12. Because the NYC database received this archived redo log file, you can copy it from the NYC database to the SAT database, as follows:

     % cp /net/nyc/oracle/dbs/hq_nyc_12.log /net/sat/oracle/dbs/hq_sat_12.log
     
     

  2. Determine if a partial archived redo log file exists for the next sequence number. In this example, the next sequence number should be 14. The following UNIX command searches the directory on the SAT database for the presence of an archived redo log file named hq_sat_14.log:

     % ls -l /net/sat/oracle/dbs/hq_sat_14.log 
     /net/sat/oracle/dbs/hq_sat_14.log: No such file or directory
     
     

     Because the SAT standby database is using standby redo log files, there should not be any partial archived redo log files.

  3. Register the retrieved archived redo log file. (There is no need to stop log apply services).

     SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/oracle/dbs/hq_sat_12.log';
     
     

  4. Query the V$ARCHIVED_LOG view again to make sure the archived redo log files were successfully applied:

     SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
       2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;
     
     FILE_NAME                  SEQUENCE# APP
     ------------------------- ---------- ---
     /oracle/dbs/hq_sat_2.log           2 YES 
     /oracle/dbs/hq_sat_3.log           3 YES 
     /oracle/dbs/hq_sat_4.log           4 YES
     /oracle/dbs/hq_sat_5.log           5 YES 
     /oracle/dbs/hq_sat_6.log           6 YES 
     /oracle/dbs/hq_sat_7.log           7 YES 
     /oracle/dbs/hq_sat_8.log           8 YES 
     /oracle/dbs/hq_sat_9.log           9 YES
     /oracle/dbs/hq_sat_10.log         10 YES
     /oracle/dbs/hq_sat_11.log         11 YES
     /oracle/dbs/hq_sat_12.log         12 YES
     /oracle/dbs/hq_sat_13.log         13 YES
     
     

• If you chose the NYC database as the best target based on your business requirements, perform the following steps:

  1. Determine if a partial archived redo log file exists for the next sequence number. The following UNIX command searches the directory on the NYC database for the presence of an archived redo log file named with the next sequence (hq_nyc_14):

     % ls -l /net/nyc/oracle/dbs/hq_nyc_14.log
     /net/nyc/oracle/dbs/hq_nyc_14.log: No such file or directory
     
     

     Because the NYC standby database is using standby redo log files, there should not be any partial archived redo log files.

  2. Start log apply services to apply the most current log file:

     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
       2> DISCONNECT FROM SESSION;
     
     

  3. Query the V$ARCHIVED_LOG view again to make sure the archived redo log files were successfully applied:

     SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
       2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;
     
     FILE_NAME                  SEQUENCE# APP
     ------------------------- ---------- ---
     /oracle/dbs/hq_nyc_2.log           2 YES 
     /oracle/dbs/hq_nyc_3.log           3 YES 
     /oracle/dbs/hq_nyc_4.log           4 YES
     /oracle/dbs/hq_nyc_5.log           5 YES 
     /oracle/dbs/hq_nyc_6.log           6 YES 
     /oracle/dbs/hq_nyc_7.log           7 YES 
     /oracle/dbs/hq_nyc_8.log           8 YES 
     /oracle/dbs/hq_nyc_9.log           9 YES
     /oracle/dbs/hq_nyc_10.log         10 YES
     /oracle/dbs/hq_nyc_11.log         11 YES
     /oracle/dbs/hq_nyc_12.log         12  NO
     /oracle/dbs/hq_nyc_13.log         13  NO
     
     

     Applying the archived redo log files might take some time to complete. Therefore, you must wait until all archived redo log files are designated as applied, as shown:

     SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
       2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;
     
     FILE_NAME                  SEQUENCE# APP
     ------------------------- ---------- ---
     /oracle/dbs/hq_nyc_2.log           2 YES 
     /oracle/dbs/hq_nyc_3.log           3 YES 
     /oracle/dbs/hq_nyc_4.log           4 YES
     /oracle/dbs/hq_nyc_5.log           5 YES 
     /oracle/dbs/hq_nyc_6.log           6 YES 
     /oracle/dbs/hq_nyc_7.log           7 YES 
     /oracle/dbs/hq_nyc_8.log           8 YES 
     /oracle/dbs/hq_nyc_9.log           9 YES
     /oracle/dbs/hq_nyc_10.log         10 YES
     /oracle/dbs/hq_nyc_11.log         11 YES
     /oracle/dbs/hq_nyc_12.log         12 YES
     /oracle/dbs/hq_nyc_13.log         13 YES
     

Step 9 Perform the failover.

You are now ready to stop log apply services and fail over the selected physical standby database to the primary role.

See Section 7.2.2 for additional information about how to fail over to a physical standby database.

12.2.2 Example: Best Logical Standby Database for a Failover

In a disaster when only logical standby databases are available, the critical task is to determine which logical standby database is the best target for the failover. While there are many environmental factors that can affect which is the best target standby database, this scenario assumes these things to be equal for emphasizing data loss assessment. See Section 1.4 for more information about the maximum availability protection mode for logical standby databases.

This scenario starts out with a Data Guard configuration consisting of the HQ primary database and two logical standby databases, SAT and NYC. Table 12-6 provides information about each of these databases.

Follow these steps to determine which standby database is the best candidate for failover when only logical standby databases are in the configuration:

Step 1 Connect to the SAT logical standby database.

Issue a SQL statement such as the following:

SQL> CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

Step 2 Determine the highest applied SCN and highest (newest) applicable SCN on the SAT database.

Query the following columns in the V$LOGSTDBY_PROGRESS view:

SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN
----------- ----------
     144059     144059

Step 3 Obtain a list of the archived redo log files that were applied or are currently pending application to the SAT database.

Query the DBA_LOGSTDBY_LOG view:

SQL> SELECT SUBSTR(FILE_NAME,1,25) FILE_NAME, SUBSTR(SEQUENCE#,1,4) "SEQ#",
  2> FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, 'HH:MI:SS') TIMESTAMP,
  3> DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) "THR#"
  4> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

FILE_NAME                 SEQ# FIRST_CHANGE# NEXT_CHANGE# TIMESTAM BEG END THR#
------------------------- ---- ------------- ------------ -------- --- --- ----
/oracle/dbs/hq_sat_2.log  2           101579       101588 11:02:57 NO  NO  1
/oracle/dbs/hq_sat_3.log  3           101588       142065 11:02:01 NO  NO  1
/oracle/dbs/hq_sat_4.log  4           142065       142307 11:02:09 NO  NO  1
/oracle/dbs/hq_sat_5.log  5           142307       142739 11:02:47 YES YES 1
/oracle/dbs/hq_sat_6.log  6           142739       143973 12:02:09 NO  NO  1
/oracle/dbs/hq_sat_7.log  7           143973       144042 01:02:00 NO  NO  1
/oracle/dbs/hq_sat_8.log  8           144042       144051 01:02:00 NO  NO  1
/oracle/dbs/hq_sat_9.log  9           144051       144054 01:02:15 NO  NO  1
/oracle/dbs/hq_sat_10.log 10          144054       144057 01:02:20 NO  NO  1
/oracle/dbs/hq_sat_11.log 11          144057       144060 01:02:25 NO  NO  1
/oracle/dbs/hq_sat_13.log 13          144089       144147 01:02:40 NO  NO  1

Notice that for log file 11, the SCN of 144059 (recorded in Step 2) is between the FIRST_CHANGE# column value of 144057 and the NEXT_CHANGE# column value of 144060. This indicates log file 11 is currently being applied. Also, notice the gap in the sequence numbers in the SEQ# column; in the example, the gap indicates that SAT database is missing archived redo log file 12.

Step 4 Connect to the NYC database.

Issue a SQL statement such as the following:

SQL> CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA;

Step 5 Determine the highest applied SCN and highest applicable SCN on the NYC database.

Query the following columns in the V$LOGSTDBY_PROGRESS view:

SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN LATEST_SCN
----------- ----------
     143970     144146

Step 6 Obtain a list of the log files that were processed or are currently pending processing on the NYC database.

Issue a SQL statement such as the following:

SQL> SELECT SUBSTR(FILE_NAME,1,25) FILE_NAME, SUBSTR(SEQUENCE#,1,4) "SEQ#", 
  2> FIRST_CHANGE#, NEXT_CHANGE#, TO_CHAR(TIMESTAMP, 'HH:MI:SS')  TIMESTAMP, 
  3> DICT_BEGIN BEG, DICT_END END, SUBSTR(THREAD#,1,4) "THR#" 
  4> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

FILE_NAME                 SEQ# FIRST_CHANGE# NEXT_CHANGE# TIMESTAM BEG END THR#
------------------------- ---- ------------- ------------ -------- --- --- ----
/oracle/dbs/hq_nyc_2.log  2           101579       101588 11:02:58 NO  NO  1
/oracle/dbs/hq_nyc_3.log  3           101588       142065 11:02:02 NO  NO  1
/oracle/dbs/hq_nyc_4.log  4           142065       142307 11:02:10 NO  NO  1
/oracle/dbs/hq_nyc_5.log  5           142307       142739 11:02:48 YES YES 1
/oracle/dbs/hq_nyc_6.log  6           142739       143973 12:02:10 NO  NO  1
/oracle/dbs/hq_nyc_7.log  7           143973       144042 01:02:11 NO  NO  1
/oracle/dbs/hq_nyc_8.log  8           144042       144051 01:02:01 NO  NO  1
/oracle/dbs/hq_nyc_9.log  9           144051       144054 01:02:16 NO  NO  1
/oracle/dbs/hq_nyc_10.log 10          144054       144057 01:02:21 NO  NO  1
/oracle/dbs/hq_nyc_11.log 11          144057       144060 01:02:26 NO  NO  1
/oracle/dbs/hq_nyc_12.log 12          144060       144089 01:02:30 NO  NO  1
/oracle/dbs/hq_nyc_13.log 13          144089       144147 01:02:41 NO  NO  1

Notice that for log file 6, the SCN of 143970 (recorded in Step 5) is between the FIRST_CHANGE# column value of 142739 and the NEXT_CHANGE# column value of 143973. This indicates that log file 6 is currently being applied. Also, notice that there are no gaps in the sequence of log files that remain to be processed.

Step 7 Choose the best target standby database.

In most cases, the logical standby database you choose as a failover target should provide a balance between risk of data loss and time required to perform the role transition. As you analyze this information to make a decision about the best failover candidate in this scenario, consider the following:

• For minimal risk of data loss during a failover, you should choose the NYC database as the best target standby database because Steps 5 and 6 revealed that the NYC site has the most recoverable archived redo log files.

• For minimal primary database downtime during the failover, you should choose the SAT database as the best target standby database. This database is a more appropriate candidate because the queries in Steps 2 through 6 reveal that the SAT database applied 5 archived redo log files more than the NYC database (even though there was only a 1-second delay (lag) in the receipt of archived redo log files by the NYC database). However, if it is not possible to obtain and apply a copy of the missing archived redo log file (log file 12 in the example), then you will not be able to make the SAT database as current as you can the NYC database. Therefore, you will lose the unrecovered data (log files 12, 13, and part of log file 14 in the example).

Based on your business requirements, choose the best target standby database.

Step 8 Bring the selected standby database to its most current state.

If you chose the SAT database as the best target based on your business requirements, perform the following steps:

1. Manually retrieve any missing archived redo log files using an operating system utility. (This example uses the UNIX cp command.) In this case, the SAT database is missing archived redo log file 12. Because the NYC database received this archived redo log file, you can copy it from the NYC database to the SAT database, as follows:

   %cp /net/nyc/oracle/dbs/hq_nyc_12.log
   /net/sat/oracle/dbs/hq_sat_12.log
   
   

2. Determine if a partial archived redo log file exists for the next sequence number. In this example, the next sequence number should be 14. The following UNIX command shows the directory on the SAT database, looking for the presence of an archived redo log file named hq_sat_14.log:

   %ls -l /net/sat/oracle/dbs/hq_sat_14.log
   -rw-rw----   1 oracle    dbs  333280 Feb 12  1:03 hq_sat_14.log
   
   

3. Stop log apply services and register both the retrieved archived redo log file and the partial archived redo log file:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/oracle/dbs/hq_sat_12.log';
   SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/oracle/dbs/hq_sat_14.log';
   
   

4. Start log apply services to apply the most current log file:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
   
   

5. Determine the highest applied SCN on the SAT database by querying the V$LOGSTDBY_PROGRESS view to see if the value of the APPLIED_SCN column is equal to the value of the LATEST_SCN column:

   SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
   
   APPLIED_SCN LATEST_SCN
   ----------- ----------
        144205     144205
   
   

   Because the SCN values match, you can be assured that there is no longer a delay (lag) between the current log file on the primary database and the last log file applied to the SAT database.

If you chose the NYC database as the best target based on your business requirements, perform the following steps:

1. Determine if a partial archived redo log file exists for the next sequence number. In this example, the next sequence number should be 14. The following UNIX command shows the directory on the NYC database, looking for the presence of an archived redo log file named hq_nyc_14:

   %ls -l /net/nyc/oracle/dbs/hq_nyc_14.log
   -rw-rw----   1 oracle    dbs  333330 Feb 12  1:03 hq_nyc_14.log
   
   

2. Register the partial archived redo log file on the NYC database:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE '/oracle/dbs/hq_nyc_14.log';
   
   

3. Start log apply services to apply the most current log file:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
   
   

4. Determine the highest applied SCN on the NYC database by querying the V$LOGSTDBY_PROGRESS view to see if the value of the APPLIED_SCN column is equal to the value of the LATEST_SCN column:

   SQL> SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
   
   APPLIED_SCN LATEST_SCN
   ----------- ----------
        144205     144205
   
   

   Because the SCN values match, you can be sure there is no longer a delay (lag) between the current log file on the primary database and the last log file received and applied by the NYC database.

Step 9 Perform the failover.

You are now ready to stop log apply services and fail over the selected logical standby database to the primary role.

See Section 7.3.2 for additional information on how to perform the failover.

12.3.1 When the New Primary Database Was Formerly a Physical Standby Database

This scenario demonstrates how to configure the SAT logical standby database with the failed over NYC database. This scenario is a continuation of the example described in Section 12.2.2. However, in this example, the NYC database was formerly a physical standby database.

Perform the following steps to configure the logical standby database with the new primary database:

Step 1 Disable archiving from the primary database.

On the NYC database, issue the following statements (assuming LOG_ARCHIVE_DEST_4 is configured to archive to the SAT database):

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=DEFER;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Step 2 Verify the logical standby database is capable of serving as a standby database to 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');

Step 3 Enable archiving on the primary database.

On the NYC database, issue the following statements (assume LOG_ARCHIVE_DEST_4 is configured to archive to the SAT database):

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Step 4 Query the new primary database to determine the SCN at which real-time apply can be enabled on the logical standby database

On the NYC database, issue the following query to determine the SCN of interest:

SQL> SELECT MAX(NEXT_CHANGE#) -1 AS WAIT_FOR_SCN FROM V$ARCHIVED_LOG;

Step 5 Start SQL Apply.

On the SAT database, issue the following statement:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Note that you must always issue this statement without the real-time apply option. You need to wait for SQL Apply to apply past WAIT_FOR_SCN returned in Step 4, before you can enable real-time apply. To determine when it is safe to resume real-time apply on the logical standby database, monitor the V$LOGSTDBY_PROGRESS view:

SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

When the value returned is greater than or equal to the WAIT_FOR_SCN value returned in Step 4, you can stop SQL Apply and restart it with real-time apply option:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

12.3.2 When the New Primary Database Was Formerly a Logical Standby Database

This scenario demonstrates how to configure the SAT logical standby database with the failed over NYC database. This scenario is a continuation of the example described in Section 12.2.2. However, in this example, the NYC database was formerly a logical standby database.

Perform the following steps to configure the logical standby database with the new primary database:

Step 1 Ensure the new primary database is ready to support logical standby databases.

On the NYC database, ensure the following query returns a value of READY. Otherwise, the LSP1 background process has not completed its work and the configuration of this logical must wait. For example:

SQL> SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS WHERE
  2> NAME = 'REINSTATEMENT_STATUS';

VALUE
--------------------
READY

Step 2 Disable archiving from the primary database.

On the NYC database, issue the following statements (assume LOG_ARCHIVE_DEST_4 is configured to archive to the SAT database):

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=DEFER;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Step 3 Verify the logical standby database is capable of being a standby to the new primary.

On the SAT database, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY(-
     former_standby_type => 'LOGICAL' -
     dblink => 'nyc_link');

Step 4 Determine the log files that must be copied to the local system.

On the SAT database, look for the output from the DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY procedure that identifies the log files that must be copied to the local system. If Step 3 identified the failover as a no-data-loss failover, then the displayed log files must be copied from the new primary database and should not be obtained from other logical standby databases or the former primary database. For example, on a Linux system, you would enter the grep command:

%grep 'LOGSTDBY: Terminal log' alert_sat.log
LOGSTDBY: Terminal log: [/oracle/dbs/hq_nyc_13.log]

Step 5 Copy the log files to the local system.

On the SAT database, copy the terminal log files to the local system. The following example shows how to do this using Linux commands:

%cp /net/nyc/oracle/dbs/hq_nyc_13.log 
/net/sat/oracle/dbs/hq_sat_13.log

Step 6 Register the terminal log with logical standby database.

On the SAT database, issue the following statement:

SQL> ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE -
     '/net/sat/oracle/dbs/hq_sat_13.log';

Step 7 Start SQL Apply.

On the SAT database, issue the following statements:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY nyc_link;

Note that you must always issue this statement without the real-time apply option. If you want 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;

Step 8 Enable archiving on the primary database to the logical standby database.

On the NYC database, issue the following statements (assuming LOG_ARCHIVE_DEST_4 is configured to archive to the SAT database):

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

12.4.1 Flashing Back a Failed Primary Database into a Physical Standby Database

The following steps assume the user has already performed a failover involving a physical standby database and Flashback Database has been enabled on the old primary database. This procedure brings the old primary database back into the Data Guard configuration as a new physical standby database.

Step 1 Determine the SCN at which the old standby database became the primary database.

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;

Step 2 Flash back the failed primary database.

To create a new physical standby 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 Step 1:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

1. Issue the following statement on the old primary database:

   SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
   
   

   This statement will dismount the database after successfully converting the control file to a standby control file.

2. Shut down and restart the database:

   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP MOUNT;
   

Step 4 Restart transporting redo to the new physical standby database.

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:

1. 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
     2> FROM V$ARCHIVE_DEST_STATUS;
   

2. If necessary, enable the destination:

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
   
   

3. Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. 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
     2> 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 the VALID_FOR attribute in one server parameter file (SPFILE). By doing this, the Data Guard configuration operates properly after a role transition.

Step 5 Start Redo Apply.

Start Redo Apply or real-time apply on the new physical standby database:

• To start Redo Apply:

  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
  
  

• To start real-time apply:

  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 
    2> USING CURRENT LOGFILE 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 Section 7.2.1, "Switchovers Involving a Physical Standby Database" for more information.

12.4.2 Flashing Back a Failed Primary Database into a Logical Standby Database

The following steps assume that the Data Guard configuration has already completed a failover involving a logical standby database and Flashback Database has been enabled on the old primary database. This procedure brings the old primary database back into the Data Guard configuration as a new logical standby database, without having to formally reinstantiate it from the new primary database.

Step 1 Determine the SCN to which to flash back the failed primary database.

On the new primary database, issue the following query to determine the SCN to which you want to flash back the failed primary database:

SQL> SELECT APPLIED_SCN AS FLASHBACK_SCN FROM V$LOGSTDBY_PROGRESS;

Step 2 Determine the log files that must be copied to the failed primary database for Flashback Database.

On the new primary database, issue the following query to determine the log files that must be copied to the failed primary database for Flashback Database to reach a consistent state

SQL> SELECT NAME FROM DBA_LOGSDTBY_LOG 
2>   WHERE NEXT_CHANGE# >  
3>           (SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS 
4>   WHERE NAME = 'STANDBY_BECAME_PRIMARY_SCN')
5>           AND FIRST_CHANGE <= (FLASHBACK_SCN from step 1);

Step 3 Flash back the failed primary database.

To create a new logical standby database, shut down the database (if necessary), mount the failed primary database, flash it back to the FLASHBACK_SCN determined in step 1, and enable the database guard.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN became_primary_scn;
SQL> ALTER DATABASE GUARD ALL;

Step 4 Open the database with the RESETLOGS option.

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 5 Create a database link to the new primary database and start SQL Apply.

SQL> CREATE PUBLIC DATABASE LINK mylink

2> CONNECT TO system IDENTIFIED BY password 
  3> USING 'service_name_of_new_primary_database';

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY mylink;

The role reversal is now complete.

Once the failed primary database has been 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 Section 7.3.1, "Switchovers Involving a Logical Standby Database" for more information.

12.4.3 Flashing Back a Logical Standby Database to a Specific Applied SCN

One of the benefits of a standby database is that Flashback Database can be performed on the standby database without affecting the primary database service. Flashing back a database to a specific point in time is a straightforward task, however on a logical standby database, you may want to flash back to a time just before a known transaction was committed. Such a need can arise when configuring a logical standby database with a new primary database after a failover.

The following steps describe how to use Flashback Database and SQL Apply to recover to a known applied SCN.

Step 1 Identify the log files that contain the Apply SCN.

On the logical standby database, issue the following query to identify the log files that contain the Apply_SCN:

SQL> SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG
5>   WHERE FIRST_CHANGE# <= APPLY_SCN
6>   AND NEXT_CHANGE# > APPLY_SCN
7>   ORDER BY FIRST_CHANGE# ASCENDING;

FILE_NAME
----------------------------------------------------------------
/net/sat/oracle/dbs/hq_sat_13.log

Step 2 Locate the timestamp associated with the SQL Apply initial reading of the first log file.

Locate the timestamp in the alert.log file associated with the SQL Apply initial reading of the first log file displayed in Step 1. For example:

%grep -B 1 '^LOGMINER: Begin mining logfile' alert_gap2.log | grep -B 1 hq_sat_13.logTue Jun  7 02:38:18 2005LOGMINER: Begin mining logfile: /net/sat/oracle/dbs/hq_sat_13.log 

Step 3 Flash back the database to the timestamp.

Flash back the database to the timestamp identified in Step 2.

SQL> SHUTDOWN;SQL> STARTUP MOUNT EXCLUSIVE;SQL> FLASHBACK DATABASE TO TIMESTAMP -
     TO_TIMESTAMP('07-Jun-05 02:38:18', 'DD-Mon-RR HH24:MI:SS');SQL> ALTER DATABASE OPEN RESETLOGS;

Step 4 Confirm SQL Apply has applied less than or up to the APPLY_SCN

Issue the following query:

SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

12.5.1 Flashing Back a Physical Standby Database to a Specific Point-in-Time

The following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLOGS statement on the primary database.

Step 1 Determine the SCN before the RESETLOGS operation occurred.

On the primary database, use the following query to obtain the value of the system change number (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;

Step 2 Obtain the current SCN on the standby database.

On the standby database, obtain the current SCN with the following query:

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

Step 3 Determine if it is necessary to flash back the database.

If the value of CURRENT_SCN is larger than the value of resetlogs_change# - 2, issue the following statement to flash back the standby database.

SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2;

• If the value of CURRENT_SCN is less than the value of the resetlogs_change# - 2, skip to Step 4.

• If the standby database's SCN is far enough behind the primary database's SCN, log apply services will be able to continue through the OPEN RESETLOGS statement without stopping. In this case, flashing back the database is unnecessary because log apply services do not stop upon reaching the OPEN RESETLOGS statement in the redo data.

Step 4 Restart Redo Apply.

To start Redo Apply on the physical standby database, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

The standby database is now ready to receive and apply redo from the primary database.

12.5.2 Flash Back a Logical Standby Database After Flashing Back the Primary

The following steps describe how to avoid re-creating a logical standby database after you have flashed back the primary database and opened it by issuing OPEN RESETLOGS statement.

Step 1 Determine the SCN at the primary database.

On the primary database, use the following query to obtain the value of the system change number (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) AS FLASHBACK_SCN FROM V$DATABASE;

Step 2 Stop SQL Apply.

On the logical standby database, stop SQL Apply:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

If the APPLIED_SCN is less than the value of the resetlogs_change#-2, you do not need to flash back the standby database and can proceed to Step 6. This may happen if SQL Apply is running with a delay. Otherwise, continue with Step 5.

Step 3 Determine which archived redo log file contains the FLASHBACK_SCN.

On the logical standby database, determine which archived redo log file contains the FLASHBACK_SCN determined in Step 1

SQL> SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG
  2> WHERE FIRST_CHANGE# <= FLASHBACK_SCN 
  3> AND NEXT_CHANGE# > FLASHBACK_SCN
  4> ORDER BY FIRST_CHANGE# ASCENDING;

FILE_NAME
----------------------------------------------------------------
/net/sat/oracle/dbs/hq_sat_146.log

Step 4 Locate the timestamp in the alert.log file.

Locate the timestamp in the alert.log file associated with the SQL Apply initial reading of the first log file displayed in Step 1. For example:

%grep -B 1 '^LOGMINER: Begin mining logfile' alert.log |  grep -B 1 hq_sat_146.log

Tue Mar  7 12:38:18 2005
LOGMINER: Begin mining logfile: /net/sat/oracle/dbs/hq_sat_146.log

Step 5 Flash back the logical standby database to the timestamp.

Issue the following SQL statements to flash back the logical standby database to the time identified in step 4, and open the logical standby database with the RESETLOGS option:

SQL> SHUTDOWN;
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> FLASHBACK DATABASE TO TIMESTAMP('07-Mar-05 12:38:18', 'DD-Mon-RR HH24:MI:SS');
SQL> ALTER DATABASE OPEN RESETLOGS;

Step 6 Confirm SQL Apply has applied less than or up to the Apply SCN.

On the logical standby database, issue the following query:

SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

Step 7 Start SQL Apply.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;