OracleDataGuard _ slave database read-only or read/write access

8.2 Opening a Standby Database for Read-Only or Read/Write Access8.2

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 Apply is updating the standby database correctly. (Note that for distributed queries, you must first issue theALTER database set transaction read only statement before you can issue a query on the read-only database .)

When the standby database is opened in read-only mode, you can only query the standby database but cannot update it. Then, you can generate reports on the slave database to reduce the load on the master database. You can regularly open the slave database in read-only mode and check whether the redo application is correctly updated to the slave database. For distributed queries, you must issue the alter database set transaction read only statement before the READ-only database query.

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

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

Description of "Figure 8-1 Standby Database Open for Read-Only Access"

See Also:

Assessing Whether or Not to Open a Standby Database

Opening a Physical Standby Database 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.

The physical standby database can be temporarily enabled for development, reporting, or testing in read/write mode, and then returns to the previous point to restore to a physical standby database. After the database flash back, Data Guard automatically synchronizes Data between the master database and the slave database, instead of re-creating the physical backup database from the backup copy of the master database.

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 Section 12.6 scenario, the physical standby database that describes the activity is the read/write report database, and then re-Synchronize the database with the master database. 8.2.1 Assessing Whether or Not to Open a Standby Database8.2.1 assess Whether to Open the Standby Database

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

To determine whether to open the physical standby database in read-only or read/write mode, consider the following:

Opening the physical standby database read-only may lengthen the time it takes to recover from a failure or outage, because the database must be restarted after a failover.

Opening the physical standby database in read-only mode can prolong the time for failed or interrupted recovery, because the database must be restarted after failover.

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.

As long as the physical standby database is not opened in read-only mode since the last time it was started, restart is required after the Failover, and the system availability will be increased.

While a standby database is open for read-only or read/write access, it does not apply redo data stored ed from the primary database, thus it is not kept transactionally consistent with the primary database.

When the standby database is opened in read-only or read/write mode, the redo data from the primary database will not be applied, and transactions that are consistent with the primary database will not be applied.

When a physical standby database is open, redo data from the primary database is stored 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.

When the physical standby database is opened, the redo data from the primary database is received by the standby database but not applied. It is a little hurt. You need to re-apply the redo data on the slave database, and apply archive logs to synchronize the data between the slave database and the master database. Because you need to apply for any accumulated archived redo log files, opening a standby database with read-only access will increase the additional time required for failover or dressing change.

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.

If multiple backup databases are configured on your backup system, you can use a physical backup database for report purposes or clone a database, while maintaining a complete failover or normal failover.

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

For example, based on your business needs, you can:

Configure two physical standby databases with one standby database always faster Ming 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 two physical standby databases, one of which is used to redo the logs of the application master database as often as possible, and the other is read-only to open the logs to generate reports during the business period.

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.

Configure the physical standby database to maintain copies of the master database for disaster recovery. Configure the logical standby database to uninstall the report task, which requires access to the latest data of the master database.

When authentication ing more than one standby database on the same system, consider using the DEPENDENCY attribute of the LOG_ARCHIVE_DEST_ninitialization 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 locking Ming Redo Apply using the following procedures.

You can use the following process to enable access and execute redo applications in the read-only mode of the physical standby database to switch in turn.

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

When the standby database is closed, use the STARTUP command to open the standby database in read-only mode:

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 executing Ming Redo Apply:

When a redo application is being executed, open the standby database in read-only mode to access the application: (cancel the redo application and then open the database)

Cancel Redo Apply:

SQL> Alter database recover managed standby database cancel;

Open the database for read-only access:

SQL> Alter database open;
######################################## ######################################## ###### SYS @ PRODSTD> alter database open;
Database altered.
SYS @ PRODSTD> select OPEN_MODE from v $ database;
OPEN_MODE ---------- READ ONLY
SYS @ PRODSTD> explain Sat Mar 29 13:22:29 2014 alter database open Sat Mar 29 13:22:29 2014 SMON: enabling cache recovery Sat Mar 29 13:22:30 2014 Database Characterset is AL32UTF8 replication_dependency_tracking turned off (no async multimaster replication found) Physical standby database opened Read onlyAccess. Completed: alter database open
######################################## ######################################## ########

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

Note:

By default, the alter database open statement opens physical standby databases in read-only mode. the Oracle database determines if this is a physical standby database based on information in the control file. by default, the statement alter database open is read-only when the physical standby DATABASE is opened. The oracle Database determines whether the information in the control file is a physical standby database.

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

Switch slave database from read-only access to redo application:

Terminate all active user sessions on the standby database.

1. Terminate all active user sessions in the slave database.

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

2. Restart the redo application and issue the following statement to enable the redo application:

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

To enable real-time apply, include the using current logfile clause:

Real-time application, which must contain 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.

You do not need to disable the database and then enable these application modes.