Using a Physical Standby Database with a Time Lag-CSDN博客

12.8 Using a Physical Standby Database with a Time Lag

By default, when log apply services are running on the standby database, the redo data is either written to archived log files and applied, or when real-time apply is enabled, the redo is written to the standby database as it arrives from the primary database. But in some cases, you may want to create a time lag between the archiving of an online redo log file at the primary site and the application of the archived redo log file at the standby site. A time lag can protect against the transfer of corrupted or erroneous data from the primary site to the standby site. When you set a time delay, it does not delay the transport of the redo data to the standby database. Instead, the time lag you specify begins when the redo data is completely archived at the standby destination.

For example, suppose you run a batch job every night on the primary database. Unfortunately, you accidently ran the batch job twice, and you did not realize the mistake until the batch job completed for the second time. Ideally, you need to roll back the database to the point in time before the batch job began. A primary database that has a standby database with a time lag could help you to recover. You could fail over the standby database with the time lag and use it as the new primary database.

To create a standby database with a time lag, use the DELAY attribute of the LOG_ARCHIVE_DEST_n initialization parameter in the primary database initialization parameter file.

Note:

If you define a delay for a destination that has real-time apply enabled, the delay is ignored

Although the redo data is still automatically transmitted from the primary database to the standby database and written to archived redo log files (and standby redo log files, if implemented), the log files are not immediately applied to the standby database. The log files are applied when the specified time interval expires.

This scenario uses a 4-hour time lag and covers the following topics:

Establishing a Time Lag on a Physical Standby Database
Failing Over to a Physical Standby Database with a Time Lag
Switching Over to a Physical Standby Database with a Time Lag

Readers of this scenario are assumed to be familiar with the procedures for creating a typical standby database. The details were omitted from the steps outlined in this scenario. See Chapter 3 for details about creating physical standby databases.

12.8.1 Establishing a Time Lag on a Physical Standby Database

To create a physical standby database with a time lag, modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database. The following is an example of how to add a 4-hour delay to the LOG_ARCHIVE_DEST_n initialization parameter:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby DELAY=240';

The DELAY attribute indicates that the archived redo log files at the standby site are not available for recovery until the 4-hour time interval has expired. The time interval (expressed in minutes) starts when the archived redo log files are successfully archived at the standby site. The redo information is still sent to the standby database and written to the disk as normal.

See Section 6.2.2 for a more information about establishing a time lag on physical and logical standby databases.

12.8.2 Failing Over to a Physical Standby Database with a Time Lag

A standby database configured to delay application of archived redo log files can be used to recover from user errors or data corruptions on the primary database. In most cases, you can query the time-delayed standby database to retrieve the data needed to repair the primary database (for example, to recover the contents of a mistakenly dropped table). In cases where the damage to the primary database is unknown or when the time required to repair the primary database is prohibitive, you can also consider failing over to a time-delayed standby database.

Assume that a backup file was inadvertently applied twice to the primary database and that the time required to repair the primary database is prohibitive. You choose to fail over to a physical standby database for which the application of archived redo log files is delayed. By doing so, you transition the standby database to the primary role at a point before the problem occurred, but you will likely incur some data loss. The following steps illustrate the process:

Initiate the failover by issuing the appropriate SQL statements on the time-delayed physical standby database:
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP
```
The ACTIVATE statement immediately transitions the standby database to the primary role and makes no attempt to apply any additional redo data that might exist at the standby location. When using this statement, you must carefully balance the cost of data loss at the standby location against the potentially extended period of downtime required to fully repair the primary database.
Re-create all other standby databases in the configuration from a copy of this new primary database.

12.8.3 Switching Over to a Physical Standby Database with a Time Lag

All of the redo data is transmitted to the standby site as it becomes available. Therefore, even when a time delay is specified for a standby database, you can make the standby database current by overriding the delay using the SQL ALTER DATABASE RECOVER MANAGED STANDBY statement.

Note:

To recover from a logical error, you must perform. a failover instead of a switchover.

The following steps demonstrate how to perform. a switchover to a time-delayed physical standby database that bypasses a time lag. For the purposes of this example, assume that the primary database is located in New York, and the standby database is located in Boston.

Step 1 Apply all of the archived redo log files to the original (time-delayed) standby database bypassing the lag.

Switchover will not begin until the standby database applies all of the archived redo log files. By lifting the delay using the NODELAY keyword, you allow the standby database to proceed without waiting for the specified time interval to pass before applying the archived redo log files.

Issue the following SQL statement to lift the delay:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY 
  2> DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;

Step 2 Stop read or update activity on the primary and standby databases.

You must have exclusive database access before beginning a switchover. Ask users to log off the primary and standby databases, or query the V$SESSION view to identify users that are connected to the databases and close all open sessions except the SQL*Plus session from which you are going to execute the switchover statement. See Oracle Database Administrator's Guide for more information about managing users.

Step 3 Switch the primary database to the physical standby role.

On the primary database (in New York), execute the following statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY 
  2> WITH SESSION SHUTDOWN;

This statement does the following:

Closes the primary database, terminating any active sessions
Transmits any unarchived redo log files and applies them to the standby database (in Boston)
Adds an end-of-redo marker to the header of the last log file being archived
Creates a backup of the current control file
Converts the current control file into a standby control file

Step 4 Shut down and start up the former primary instance, and mount the database.

Execute the following statement on the former primary database (in New York):

SQL> SHUTDOWN NORMAL;
SQL> STARTUP MOUNT;

Step 5 Switch the original standby database to the primary role.

Issue the following SQL statement:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY DATABASE;

Step 6 Shut down and restart the new primary database instance.

Issue the following SQL statements:

SQL> SHUTDOWN;

12.9 Recovering From a Network Failure

The following steps describe how to recover after a network failure.

Step 1 Identify the network failure.

The V$ARCHIVE_DEST view contains the network error and identifies which standby database cannot be reached. On the primary database, execute the following SQL statement for the destination that experienced the network failure. For example:

SQL> SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID = 2;

DEST_ID    STATUS    ERROR
---------- --------- --------------------------------------------------------
        2  ERROR     ORA-12224: TNS:no listener

The query results show there are errors archiving to the standby database, and the cause of the error is TNS:no listener. You should check whether or not the listener on the standby site is started. If the listener is stopped, then start it.

Step 2 Prevent the primary database from stalling.

If you cannot solve the network problem quickly, and if the standby database is specified as a mandatory destination, try to prevent the database from stalling by doing one of the following:

Defer archiving to the mandatory destination:
```
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = DEFER;
```
When the network problem is resolved, you can enable the archive destination again:
```
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
```

Change the archive destination from mandatory to optional:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1
  2> OPTIONAL REOPEN=60';

When the network problem is resolved, you can change the archive destination from optional back to mandatory:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1   2> MANDATORY REOPEN=60';

Step 3 Archive the current online redo log file.

On the primary database, archive the current online redo log file:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

When the network is back up again, log apply services can detect and resolve the archive gaps automatically when the physical standby database resumes Redo Apply.

12.10 Recovering After the NOLOGGING Clause Is Specified

In some SQL statements, the user has the option of specifying the NOLOGGING clause, which indicates that the database operation is not logged in the online redo log file. Even though the user specifies the clause, a redo record is still written to the online redo log file. However, there is no data associated with this record. This can result in log application or data access errors at the standby site and manual recovery might be required to resume applying log files.

Note:

To avoid these problems, Oracle recommends that you always specify the FORCE LOGGING clause in the CREATE DATABASE or ALTER DATABASE statements. See the Oracle Database Administrator's Guide.

12.10.1 Recovery Steps for Logical Standby Databases

For l ogical standby databases, when SQL Apply encounters a redo record for an operation performed with the NOLOGGING clause, it skips over the record and continues applying changes from later records. Later, if an attempt is made to access one of the records that was updated with NOLOGGING in effect, the following error is returned: ORA-01403 no data found

To recover after the NOLOGGING clause is specified, re-create one or more tables from the primary database, as described in Section 9.4.6.

Note:

In general, use of the NOLOGGING clause is not recommended. Optionally, if you know in advance that operations using the NOLOGGING clause will be performed on certain tables in the primary database, you might want to prevent the application of SQL statements associated with these tables to the logical standby database by using the DBMS_LOGSTDBY.SKIP procedure.

12.10.2 Recovery Steps for Physical Standby Databases

When the archived redo log file is copied to the standby site and applied to the physical standby database, a portion of the datafile is unusable and 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 will 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

To recover after the NOLOGGING clause is specified, you need to copy the datafile that contains the missing redo data from the primary site to the physical standby site. Perform. the following steps:

Step 1 Determine which datafiles should be copied.

Follow these steps:

Query the primary database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME                                                  UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/dbs/tbs_1.dbf                                       5216
/oracle/dbs/tbs_2.dbf                                          0
/oracle/dbs/tbs_3.dbf                                          0
/oracle/dbs/tbs_4.dbf                                          0
4 rows selected.

Query the standby database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;
NAME                                                  UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/dbs/stdby/tbs_1.dbf                                 5186
/oracle/dbs/stdby/tbs_2.dbf                                    0
/oracle/dbs/stdby/tbs_3.dbf                                    0
/oracle/dbs/stdby/tbs_4.dbf                                    0
4 rows selected.

Compare the query results of the primary and standby databases.

Compare the value of the UNRECOVERABLE_CHANGE# column in both query results. If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.

In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs_1.dbf datafile is greater, so you need to copy the tbs_1.dbf datafile to the standby site.

Step 2 On the primary site, back up the datafile you need to copy to the standby site.

Issue the following SQL statements:

SQL> ALTER TABLESPACE system BEGIN BACKUP;
SQL> EXIT;
% cp tbs_1.dbf /backup
SQL> ALTER TABLESPACE system END BACKUP;

Step 3 Copy the datafile to the standby database.

Copy the datafile that contains the missing redo data from the primary site to location on the physical standby site where files related to recovery are stored.

Step 4 On the standby database, restart Redo Apply.

Issue the following SQL statement:

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

You might get the following error messages (possibly in the alert log) when you try to restart Redo Apply:

ORA-00308: cannot open archived log 'standby1'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'

If you get the ORA-00308 error and Redo Apply does not terminate automatically, you can cancel recovery by issuing the following statement from another terminal window:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

These error messages are returned when one or more log files in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps, and repeat Step 4. See Section 5.8.4 for information about manually resolving an archive gap.