Opening a Standby Database for Read-Only or Read/Write Access

When a standby database is open for read-only access, users can query the standby database but cannot update it. Thus, you can reduce the load on the primary database by using the standby database for reporting purposes. You can periodically open the standby database for read-only access and perform ad hoc queries to verify Redo

Note: When you first start Redo Apply on a newly created physical standby database that has not yet received any redo data from the primary database, an ORA-01112 message may be returned. This indicates that Redo Apply is unable to determine the starting sequence number for media recovery. If this occurs, you must either manually retrieve and register an archived redo log file on the standby database, or wait for the automatic archiving to occur before restarting Redo Apply.

Opening a Standby Database for Read-Only or Read/Write Access

Apply is updating the standby database correctly. (Note that for distributed queries, you must first issue the ALTER DATABASE SET TRANSACTION READ ONLY statement before you can issue a query on the read-only database.)

Figure 8–1 shows a standby database open for read-only access.

Figure 8–1 Standby Database Open for Read-Only Access

A physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

8.2.1 Assessing Whether or Not to Open a Standby Database

As you decide whether or not to open a physical standby database for read-only or read/write access, consider the following:

■ Opening the physical standby database read-only may lengthen the time it takes See Also:

■ Assessing Whether or Not to Open a Standby Database

■ Opening a Physical Standby Database for Read-Only Access

See Also: Section 12.6 for a scenario that describes activating a physical standby database as a read/write reporting database, and then resynchronizing the database with the primary database

Primary

Opening a Standby Database for Read-Only or Read/Write Access

As long as the physical standby database has not been opened read-only since the last time it was started, a restart is unnecessary after failover, thus increasing system availability.

■ While a standby database is open for read-only or read/write access, it does not apply redo data received from the primary database, thus it is not kept

transactionally consistent with the primary database.

When a physical standby database is open, redo data from the primary database is received by the standby database, but the log files are not applied. At some point, you need to resume Redo Apply on the standby database, and apply the archived redo log files to resynchronize the standby database with the primary database.

Because of the additional time required to apply any accumulated archived redo log files, having a standby database open for read-only access can increase the time required to complete failovers or switchovers.

You can use a physical standby database for reporting purposes or as a clone database while also maintaining the ability to complete a failover or switchover quickly if you configure more than one standby database on the standby system.

For example, based on your business requirements, you might:

■ Configure two physical standby databases with one standby database always performing Redo Apply to be as current as possible with the primary database and the other standby database open in read-only mode during business hours for reporting purposes.

■ Configure a physical standby database to maintain a copy of the primary database for disaster recovery purposes and also configure a logical standby database to off-load reporting tasks that require access to the latest data from the primary database.

When configuring more than one standby database on the same system, consider using the DEPENDENCY attribute of the LOG_ARCHIVE_DEST_n initialization

parameter to define one archival destination to receive redo data on behalf of all of the destinations, rather than transmitting redo data to each individual destination. See Section 5.7.5 for more information.

8.2.2 Opening a Physical Standby Database for Read-Only Access

You can alternate between having a physical standby database open for read-only access and performing Redo Apply using the following procedures.

To open a standby database for read-only access when it is currently shut down:

Start, mount, and open the database for read-only access using the following statement:

SQL> STARTUP;

To open a standby database for read-only access when it is currently performing Redo Apply:

1. Cancel Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Open the database for read-only access:

SQL> ALTER DATABASE OPEN;

Managing Primary Database Events That Affect the Standby Database

You do not need to shut down the instance to open it for read-only access.

To change the standby database from being open for read-only access to performing Redo Apply:

1. Terminate all active user sessions on the standby database.

2. Restart Redo Apply. To start Redo Apply, issue the following statement:

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

To enable real-time apply, include the USING CURRENT LOGFILE clause:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE 2> USING CURRENT LOGFILE;

You do not need to shut down the instance to start either of these apply modes.