Log Apply Services- Physical Standby

Log Apply Services This chapter describes how redo logs are applied to a standby database. It includes the following topics: • •

Introduction to Log Apply Services Applying Redo Data to Physical Standby Databases

6.1 Introduction to Log Apply Services Log apply services automatically apply archived redo logs to maintain synchronization with the primary database and allow transactionally consistent access to the data. Archived redo data is not available for log apply services until a log switch occurs on the primary database. The main difference between physical and logical standby databases is the manner in which log apply services apply the archived redo logs. For physical standby databases, log apply services maintain the standby database by performing managed recovery operations. For logical standby databases, log apply services maintain the standby database by executing SQL statements. The following list summarizes these operations: •

Managed recovery operations (physical standby databases only) In this mode, log transport services transmit redo data to the standby site, and log apply services automatically apply the redo logs. . Caution: You can also open a physical standby database for read-only operations to allow users to query the standby database for reporting purposes. However, while a standby database that is open for read-only access, it is not kept transactionally current with the primary database, resulting in prolonging a failover or switchover operation if one is required for disaster recovery. See Section 8.2, "Using a Standby Database That Is Open for Read-Only Access" for more information.

•

SQL apply operations (logical standby databases only) Log apply services manage logical standby databases by executing SQL statements. Logical standby databases can be opened in read/write mode, but the target tables being maintained by the logical standby database are opened in read-

only mode for reporting purposes. The SQL apply mode allows you to use the logical standby database for reporting activities even while SQL statements are being applied. The sections in this chapter describe the managed recovery and SQL apply operations, and log apply services in more detail.

6.2 Applying Redo Data to Physical Standby Databases The physical standby database uses several processes to automate archiving redo data and recovering redo logs on the standby database. On the standby database, log apply services use the following processes: •

Remote file server (RFS) The remote file server (RFS) process receives redo data from the primary database either in the form of archived redo logs or standby redo logs.

•

Archiver (ARCn) If standby redo logs are being used, the ARCn process archives the standby redo logs that are to be applied by the managed recovery process (MRP).

•

Managed recovery process (MRP) The managed recovery process (MRP) applies information from the archived redo logs to the standby database. When performing managed recovery operations, log apply services automatically apply archived redo logs to maintain transactional synchronization with the primary database.

Log apply services can apply logs to a physical standby database when the database is performing recovery, but not when it is open for read-only operations). A physical standby database can be performing one of the following: • •

Managed recovery operations Read-only operations

Table 6-1 summarizes the basic tasks for configuring and monitoring log apply services. Table 6-1 Task List: Configuring Log Apply Services for Physical Standby Databases

Step Task 1

See ...

Start the standby instance and mount the standby Section 6.2.1

Step Task

See ...

database. 2

Enable managed recovery or read-only operations.

Section 6.2.2.1 or Section 8.2, respectively

3

If performing managed recovery operations, set Section 6.4 and the Oracle9i Net initialization parameters to automatically resolve Services Administrator's Guide archive gaps.

4

Monitor log apply services.

Section 6.5

6.2.1 Starting the Physical Standby Instance After all necessary parameter and network files are configured, you can start the standby instance. If the standby instance is not started and mounted, the standby database cannot receive redo data from the primary database. To start the physical standby database instance, perform the following steps: 1. Start the physical standby instance without mounting the database: 2. SQL> STARTUP NOMOUNT;

2. Mount the physical standby database. For example: 3. SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

6.2.2 Starting Managed Recovery Operations Log apply services keep the standby database synchronized with the primary database by automatically applying archived redo logs to the standby database, as shown in Figure 61. Figure 6-1 Automatic Updating of a Physical Standby Database

Text description of the illustration redoapply.gif 6.2.2.1 Starting Log Apply Services You can specify that log apply services run as a foreground session or as a background process. • • •

To start a foreground session, issue the SQL statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

If you started a foreground session, by default, control is not returned to the command prompt. •

To start a background process, you must use the DISCONNECT keyword on the SQL statement. For example:

• •

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session. •

If you did not start log apply services as a detached server process, you can stop log apply services by the issuing the following SQL statement in another window:

•

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

•

See Also: Section 6.2.3 and Chapter 13 6.2.2.2 Monitor the Recovery Process You can query views to monitor log apply services as follows: 1. To verify that you have correctly initiated log apply services, query the V$MANAGED_STANDBY fixed view on the standby database. This view monitors the progress of a standby database in managed recovery mode. For example: 2. 3. 4. 5. 6. 7.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS ------- ------------ ---------- ---------- ---------- ---------MRP0 APPLYING_LOG 1 946 10 1001

If you did not start a detached server process, you need to execute this query from another SQL session. 2. To monitor activity on the standby database, query the V$ARCHIVE_DEST_STATUS fixed view. See Also: Section 6.5

6.2.3 Controlling Redo Apply Operations Although this SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement does not require any additional clauses, it provides many keywords to help you control the redo apply process. See Also: Section 13.12 and Oracle9i SQL Reference for complete information about the SQL statement syntax

6.2.4 Datafile Management To enable the automatic creation of new datafiles on a physical standby database when datafiles are created on the primary database, you must define the STANDBY_FILE_MANAGEMENT initialization parameter.

If the directory structures on the primary and standby databases are different, you must also set the DB_FILE_NAME_CONVERT initialization parameter to convert the filenames of one or more sets of datafiles on the primary database to filenames on the standby database. 6.2.4.1 Setting the STANDBY_FILE_MANAGEMENT Initialization Parameter When you set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, it automatically creates on the standby database any datafiles that were newly created on the primary database, using the same name that you specified on the primary database. The STANDBY_FILE_MANAGEMENT initialization parameter works with the DB_FILE_NAME_CONVERT parameter to convert the datafile locations from the primary site to standby site. 6.2.4.2 Setting the DB_FILE_NAME_CONVERT Initialization Parameter When a new datafile is added on the primary database, the same datafile is created on the standby database. The DB_FILE_NAME_CONVERT parameter is used to convert the datafile name on the primary database to a datafile name on the standby database. This parameter works the same if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO or MANUAL. The DB_FILE_NAME_CONVERT initialization parameter must specify paired strings. The first string is a sequence of characters to be looked for in a primary database filename. If that sequence of characters is matched, it is replaced by the second string to construct the standby database filename. You can specify multiple pairs of filenames. For example: DB_FILE_NAME_CONVERT= "/disk1/oracle/oradata/payroll/df1", \ "/disk1/oracle/oradata/payroll/standby/df1", \ "/disk1/oracle/oradata/payroll", "/disk1/oracle/oradata/payroll/standby/" STANDBY_FILE_MANAGEMENT=AUTO

Note: When you specify pairs of files, be sure to specify the most restrictive path names before the least restrictive, as shown in the example. 6.2.4.3 Restrictions on ALTER DATABASE Operations You cannot rename the datafile on the standby site when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO. When you set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed: • •

ALTER DATABASE RENAME ALTER DATABASE ADD/DROP LOGFILE

• •

ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER ALTER DATABASE CREATE DATAFILE AS

If you attempt to use any of these statements on the standby database, an error is returned. For example: SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy'; alter database rename file '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01270: RENAME operation is not allowed if STANDBY_FILE_MANAGEMENT is auto

See Also: Section 8.4.1 to learn how to add datafiles to a database