8
Managing a Physical Standby Database

This chapter describes how to manage a physical standby database. Data Guard provides the means to easily manage, manipulate, and change a physical standby database in many ways.

This chapter contains the following topics:

8.1 Starting Up and Shutting Down a Physical Standby Database

This section describes the procedures for starting up and shutting down a physical standby database.

8.1.1 Starting Up a Physical Standby Database

To start up a physical standby database, use SQL*Plus to connect to the database with administrator privileges, and then use the SQL*Plus STARTUP command with the NOMOUNT option. (You must use the NOMOUNT option with a standby database.)

If both the primary and standby databases are offline, then always (if possible) start the standby database before starting the primary database.

After the database is started, mount the database as a standby database. Once it is mounted, the database can receive archived redo data from the primary database.

You then have the option of either starting a managed recovery operation or opening the database for read-only access. Typically, you start a managed recovery operation. The following example shows how to start a standby database:

Start the database:
```
SQL> STARTUP NOMOUNT;
```

Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Start the managed recovery operation:

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

Once the database is performing managed recovery, log apply services apply the archived redo logs to the standby database.

See Also:

Section 6.2.2 for information about managed recovery and Section 8.2 for information on opening a standby database for read-only access

8.1.2 Shutting Down a Physical Standby Database

To shut down a physical standby database, use the SQL*Plus SHUTDOWN command. If the database is performing managed recovery, you must cancel managed recovery operations before issuing the SHUTDOWN command. Control is not returned to the session that initiates a database shutdown until shutdown is complete.

If the primary database is up and running, defer the archive log destination on the primary database and perform a log switch operation (to make the defer operation take effect) before shutting down the standby database. Otherwise, log transport services will not be able to transmit redo data to this standby site.

The following steps show you how to shut down a standby database:

Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery.
```
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
```

Cancel managed recovery operations.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database.
```
SQL> SHUTDOWN IMMEDIATE;
```

8.2 Using a Standby Database That Is Open for Read-Only Access

When a standby database is open for read-only access, users can query the standby database without the potential for online data modifications. This reduces 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 ensure that log apply services are updating the standby database correctly.

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

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

Text description of sbr81099.gif follows.

Text description of the illustration sbr81099.gif

This section contains the following topics:

8.2.1 Assessing Whether to Open a Standby Database for Read-Only Access

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

Having a physical standby database open for read-only access makes it unavailable for managed recovery operations. The archived redo data is received by the standby database, but the redo logs are not applied. Therefore, a standby database that is open for read-only access is not transactionally current with the primary database. At some point, you need to resume managed recovery on the standby database, and apply the archived redo logs to resynchronize the standby database with the primary database. Having a standby database open for read-only access might prolong a failover or switchover operation if one is required for disaster recovery.

If you need a standby database for protection against disaster and for reporting, then you can maintain multiple standby databases: some open for read-only access and some performing managed recovery (which will automatically apply the archived redo logs to the standby database). However, you will need to perform managed recovery on all the standby databases periodically to ensure that the latest changes from the primary database are applied to the standby database. A physical standby database performing managed recovery gives you immediate protection against disaster.

Note:

Consider using a logical standby database if your business requires that the standby database be used for queries and reporting purposes concurrent with fulfilling disaster recovery requirements.

8.2.2 Opening a Standby Database for Read-Only Access

You can alternate between having a standby database open for read-only access and having a standby database perform managed recovery using the following procedures.

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

Start the Oracle instance for the standby database without mounting it:
```
SQL> STARTUP NOMOUNT;
```

Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Open the database for read-only access:
```
SQL> ALTER DATABASE OPEN READ ONLY;
```

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

Cancel log apply services:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Open the database for read-only access:
```
SQL> ALTER DATABASE OPEN READ ONLY;
```

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

Terminate all active user sessions on the standby database.

Restart log apply services:

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

8.2.3 Sorting Considerations For Standby Databases Open for Read-Only Access

Before you open your standby database for read-only access, consider the following topics regarding sorting operations:

8.2.3.1 Sorting Operations While the Database Is Open for Read-Only Access

To perform queries that sort a large amount of data on a standby database that is open for read-only access, the Oracle database server must be able to perform on-disk sorting operations. You cannot allocate space for sorting operations in tablespaces that cause Oracle software to write to the data dictionary.

Temporary tablespaces allow you to add tempfile entries when the database is open for read-only access for the purpose of making queries without affecting dictionary files or generating redo entries. Therefore, you can use temporary tablespaces as long as you follow these requirements for creating them:

The tablespaces must be temporary, locally managed, and contain only temporary files.
User-level allocations and permissions to use the locally managed temporary tablespaces must be in place on the primary database. You cannot change these settings on the standby database.
You must create and associate a temporary file for the temporary tablespace on the standby database.

To create a temporary tablespace for use on a read-only physical standby database

If you did not have a temporary tablespace on the primary database when you created the physical standby database, perform the following steps on the primary database:

Enter the following SQL statement:

SQL> CREATE TEMPORARY TABLESPACE temp1 
     TEMPFILE '/disk1/oracle/dbs/temp1.dbf'
     SIZE 20M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Switch the log to send the redo data to the standby database:
```
SQL> ALTER SYSTEM SWITCH LOGFILE;
```

To create and associate a temporary file with a temporary tablespace on a read-only physical standby database

The redo data that is generated on the primary database automatically creates the temporary tablespace in the standby control file after the archived redo log is applied to the physical standby database. However, even if the temporary tablespace existed on the primary database before you created the physical standby database, you must use the ADD TEMPFILE clause to actually create the disk file on the standby database.

On the physical standby database, perform the following steps:

Start managed recovery, if necessary, and apply the archived redo logs by entering the following SQL statement:
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
```
Cancel managed recovery and open the physical standby database for read-only access using the following SQL statements:
```
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
```
Opening the physical standby database for read-only access allows you to add a temporary file. Because adding a temporary file does not generate redo data, it is allowed for a database that is open for read-only access.
Create a temporary file for the temporary tablespace. The size and names for the files can differ from the primary database. For example:
```
SQL> ALTER TABLESPACE temp1 
       ADD TEMPFILE '/disk1/oracle/dbs/s_temp1.dbf'
       SIZE 10M REUSE;
```
See Also:
Oracle9i SQL Reference for information about the CREATE TEMPORARY TABLESPACE syntax

8.2.3.2 Sorting Operations Without Temporary Tablespaces

If a temporary file does not exist on the standby database, or if the standby database is not open and you attempt to sort a large amount of data, an error is returned, as shown in the following example.

SQL> SELECT * FROM V$PARAMETER;

select * from v$parameter

              *

ERROR at line 1:

ORA-01220: file based sort illegal before database is open

Note that you can, however, sort small amounts of data if the SORT_AREA_SIZE parameter is set to a sufficient value in your server parameter file. (The SORT_AREA_SIZE parameter is a static parameter.)

8.3 Creating Primary Database Back Up Files Using a Physical Standby Database

You can use the physical standby database to off-load the database backup operation from the primary database because a physical standby database is a copy of the primary database. Using RMAN at the standby site, you can back up the datafiles and the archived redo logs while the standby database is performing managed recovery. Later, you can restore these backups to the primary database using RMAN.

Note:

You cannot use a logical standby database to back up the primary database.

See Also:

Oracle9i Recovery Manager User's Guide for more details about RMAN backup and recovery of a primary database using a standby database

8.4 Managing Primary Database Events That Affect the Standby Database

To prevent possible problems, you should be aware of events in the primary database that affect a standby database and learn how to respond to them. This section describes these events and the recommended responses to these events.

In some cases, the events or changes that occur on a primary database are automatically propagated through archived redo logs to the standby database and thus require no extra action on the standby database. In other cases, you might need to perform maintenance tasks on the standby database.

Table 8-1 indicates whether or not a change made on the primary database requires additional intervention by the database administrator (DBA) to be propagated to the standby database. It also briefly describes how to respond to these events. Detailed descriptions of the responses are described in the section references provided.

Caution:

If you clear logs at the primary database by issuing the ALTER DATABASE CLEAR UNARCHIVED LOGFILE statement, or open the primary database using the RESETLOGS option, you invalidate the standby database. Because both of these operations reset the primary log sequence number to 1, you must re-create the standby database to be able to apply archived redo logs generated by the primary database.

The following events are automatically administered by log transport services and log apply services, and therefore require no intervention by the database administrator:

A SQL ALTER DATABASE statement is issued with the ENABLE THREAD or DISABLE THREAD clause.
The status of a tablespace changes (changes to read/write or read-only, placed online or taken offline).

A datafile is added or tablespace is created when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

Table 8-1 Actions Required on a Standby Database After Changes to a Primary Database

Reference	Change Made on Primary Database	Action Required on Standby Database
Section 8.4.1	Add a datafile or create a tablespace	If you did not set the `STANDBY_FILE_MANAGEMENT` initialization parameter to `AUTO`, you must copy the new datafile to the standby database.
Section 8.4.2	Drop or delete a tablespace or datafile	Delete the corresponding datafile after the archived redo log was applied.
Section 8.4.3	Rename a datafile	Rename the datafile on the standby database.
Section 8.4.4	Add or drop online redo logs	Synchronize changes on the standby database.
Section 8.4.5	Alter the primary database control file (using the SQL `ALTER DATABASE CREATE CONTROLFILE` statement)	Re-create the standby control file or re-create the standby database, depending on the alteration made.
Section 8.4.6	Perform a DML or DDL operation using the `NOLOGGING` or `UNRECOVERABLE` clause	Send the datafile containing the unlogged changes to the standby database.
Chapter 11	Change initialization parameter	Dynamically change the standby parameter or shut down the standby database and update the initialization parameter file.

8.4.1 Adding a Datafile or Creating a Tablespace

The initialization parameter, STANDBY_FILE_MANAGEMENT, allows you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:

If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.
If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.

Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.

The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.

8.4.1.1 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO

The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

Add a new tablespace to the primary database:

SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'
  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

Archive the current redo log so it will get copied to the standby database:
```
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
```

Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------
/disk1/oracle/dbs/t_db1.dbf
/disk1/oracle/dbs/t_db2.dbf

Verify that the new datafile was added to the standby database:

SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------
/disk1/oracle/dbs/s2t_db1.dbf
/disk1/oracle/dbs/s2t_db2.dbf

8.4.1.2 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

The following example shows the steps required to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices.

Add a new tablespace to the primary database:

SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'
  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;

Verify that the new datafile was added to the primary database:

SQL> SELECT NAME FROM V$DATAFILE;
NAME

----------------------------------------------------------------------
/disk1/oracle/dbs/t_db1.dbf
/disk1/oracle/dbs/t_db2.dbf

Perform the following steps to copy the tablespace to a remote standby location:
1. Place the new tablespace offline:
```
SQL> ALTER TABLESPACE new_ts OFFLINE;
```
2. Copy the new tablespace to a local temporary location using an operating system utility copy command. Copying the files to a temporary location will reduce the amount of time that the tablespace must remain offline. The following example copies the tablespace using the UNIX cp command:
```
% cp t_db2.dbf s2t_db2.dbf
```
3. Place the new tablespace back online:
```
SQL> ALTER TABLESPACE new_ts ONLINE;
```
4. Copy the local copy of the tablespace to a remote standby location using an operating system utility command. The following example uses the UNIX rcp command:
```
%rcp s2t_db2.dbf standby_location
```
Archive the current redo log on the primary database so it will get copied to the standby database:
```
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
```
Use the following query to make sure that managed recovery is running. If the MRP or MRP0 process is returned, managed recovery is being performed.
```
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
```

Verify that the datafile was added to the standby database after the redo log was applied to the standby database.

SQL> SELECT NAME FROM V$DATAFILE;
NAME

----------------------------------------------------------------------
/disk1/oracle/dbs/s2t_db1.dbf
/disk1/oracle/dbs/s2t_db2.dbf

8.4.2 Dropping a Tablespace in the Primary Database

When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles in the standby database, as follows:

Drop the tablespace at the primary site:

SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
% rm tbs_4.dbf

Make sure that managed recovery is on (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, managed recovery is on.
```
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
```
Delete the corresponding datafile on the standby site after the archived redo log was applied to the standby database. For example:
```
% rm tbs_4.dbf
```
On the primary database, after ensuring that the standby database has applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:
```
% rm tbs_4.dbf
```

8.4.3 Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. If you do not want the standby database to have the same physical structure as the primary database, then these steps are not required.

To rename the datafile in the primary database, take the tablespace offline:
```
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
```
Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:
```
% mv tbs_4.dbf tbs_x.dbf
```

Rename the datafile in the primary database and bring the tablespace back online:

SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf' 
  2>                                     TO 'tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;

Connect to the standby database and make sure that all the logs are applied; then stop managed recovery operations:

SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED 
  2> FROM V$ARCHIVED_LOG;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database:
```
SQL> SHUTDOWN;
```
Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:
```
% mv tbs_4.dbf tbs_x.dbf
```

Start and mount the standby database with the new control file:

SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Rename the datafile in the standby controlfile. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.
```
SQL> ALTER DATABASE RENAME FILE 'tbs_4.dbf' 
  2>                         TO 'tbs_x.dbf';
```

On the standby database, restart managed recovery operations:

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

If you do not rename the corresponding datafile at the standby site, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/disk1/oracle/dbs/tbs_x.dbf'

8.4.4 Adding or Dropping Online Redo Logs

Changing the size and number of the online redo logs is sometimes done to tune the database. You can add redo log file groups or members to the primary database without affecting the standby database. Similarly, you can drop log file groups or members from the primary database without affecting your standby database. However, these changes do affect the performance of the standby database after switchover.

For example, if the primary database has 10 redo logs and the standby database has 2, and then you switch over to the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database.

Consequently, when you add or drop an online redo log at the primary site, it is important that you synchronize the changes in the standby database by following these steps:

If managed recovery is on, you must cancel it before you can change the logs.
If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.
Add or drop an online redo log:
- To add an online redo log, use a SQL statement such as this:
```
SQL> ALTER DATABASE ADD STANDBY LOGFILE 'prmy3.log' SIZE 100K;
```
- To drop an online redo log, use a SQL statement such as this:
```
SQL> ALTER DATABASE DROP STANDBY LOGFILE 'prmy3.log';
```
Repeat the statement you used in step 3 on each standby database.
Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the managed recovery options to their original states.

8.4.5 Altering the Primary Database Control File

Using the SQL CREATE CONTROLFILE statement with the RESETLOGS option on your primary database will force the primary database to reset the online logs the next time the primary database is opened, thereby invalidating the standby database.

If you invalidated the control file for the standby database, re-create the file using the procedure provided in Section 3.2.3.

If you invalidated the standby database, you must re-create the standby database using the procedures in Chapter 3.

8.4.6 NOLOGGING or Unrecoverable Operations

When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and might require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCELOGGING clause to override the NOLOGGING setting. However, this statement will not repair an invalidated database.

If you perform an unrecoverable operation (such as a direct path load), you will see a performance improvement on the primary database; but there is no corresponding recovery process performance improvement on the standby database, and you will have to move the data manually to the standby database.

See Also:

Section 10.5 for information about recovering after the NOLOGGING clause is used

8.5 Monitoring the Primary and Standby Databases

This section gives you a general overview on where to find information for monitoring the primary and standby databases in a Data Guard environment.

This section contains the following topics:

Table 8-2 summarizes common events that occur on the primary database and pointers to the files and views where you can monitor these events on the primary and standby sites.

Table 8-2 Location Where Common Actions on the Primary Database Can Be Monitored

Primary Database Event	Primary Site Information	Standby Site Information
A SQL `ALTER DATABASE` statement is issued with the `ENABLE THREAD` or `DISABLE THREAD` clause specified	Alert log `V$THREAD` view	Alert log
Redo log changed	Alert log `V$LOG` view `STATUS` column of `V$LOGFILE` view	Alert log
`CREATE CONTROLFILE` statement issued	Alert log	Alert log^{Foot 1}
Managed recovery performed	Alert log	Alert log
Tablespace status changes made (made read/write or read-only, placed online or offline)	`DBA_TABLESPACES` view Alert log	`V$RECOVER_FILE` view
Datafile added or tablespace created	`DBA_DATA_FILES` view Alert log	`V$DATAFILE` view Alert log
Tablespace dropped	`DBA_DATA_FILES` view Alert log	`V$DATAFILE` view Alert log
Tablespace or datafile taken offline, or datafile is deleted offline	`V$RECOVER_FILE` view Alert log	`V$RECOVER_FILE` view
Rename datafile	`V$DATAFILE` Alert log	`V$DATAFILE` Alert log
Unlogged or unrecoverable operations	`V$DATAFILE` view `V$DATABASE` view	Alert log
Recovery progress	`V$ARCHIVE_DEST_STATUS` view Alert log	`V$ARCHIVED_LOG` view `V$LOG_HISTORY` view `V$MANAGED_STANDBY` view Alert log
Autoextend a datafile	Alert log	Alert log
Issue `OPEN RESETLOGS` or `CLEAR UNARCHIVED LOGFILES` statements	Alert log	Alert log
Change initialization parameter	Alert log	Alert log

¹ When you issue a CREATE CONTROLFILE statement on the primary database, the standby database functions normally until it encounters redo data that depends on initialization parameters.

8.5.1 Alert Log

The database alert log is a chronological record of messages and errors. Besides providing information about the Oracle database, it also includes information about operations specific to Data Guard, including the following:

Messages related to administrative operations such as the following SQL statements: ALTER DATABASE RECOVER MANAGED STANDBY, STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
Errors related to administrative operations that are reported by background processes, such as ARC0, MRP0, RFS, LGWR
The completion time stamp for administrative operations

The alert log also provides pointers to the trace or dump files generated by a specific process.

8.5.2 Dynamic Performance Views (Fixed Views)

The Oracle database server contains a set of underlying views that are maintained by the server. These views are often called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. These views are also called fixed views because they cannot be altered or removed by the database administrator.

These view names are prefixed with either V$ or GV$, for example, V$ARCHIVE_DEST or GV$ARCHIVE_DEST.

Standard dynamic performance views (V$ fixed views) store information on the local instance. In contrast, global dynamic performance views (GV$ fixed views), store information on all open instances. Each V$ fixed view has a corresponding GV$ fixed view.

See Also:

Chapter 14, "Views" and the Oracle9i Database Reference for additional information on view columns

8.5.3 Monitoring Recovery Progress

This section shows some samples of the types of views discussed in Section 8.5.2 for monitoring recovery progress in a Data Guard environment. It contains the following examples:

8.5.3.1 Monitoring the Process Activities

You can obtain information about managed recovery operations on a standby database by monitoring the activities performed by the following processes:

ARC0
MRP/MRP0
RFS

The V$MANAGED_STANDBY view on the standby database site shows you the activities performed by both log transport and log apply processes in a Data Guard environment. The CLIENT_P column in the output of the following query identifies the corresponding primary database process.

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P  SEQUENCE# STATUS
------- -------- ---------- ------------
ARCH    ARCH              0 CONNECTED
ARCH    ARCH              0 CONNECTED
MRP0    N/A             204 WAIT_FOR_LOG
RFS     LGWR            204 WRITING
RFS     N/A               0 RECEIVING

8.5.3.2 Determining the Progress of Managed Recovery Operations

The V$ARCHIVE_DEST_STATUS view on either a primary or standby database site provides you information such as the redo logs that are archived, the archived redo logs that are applied, and the log sequence numbers of each. The following query output shows the standby database is two archived logs behind in applying the redo logs received from the primary database.

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
  2> FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1                947           1               945

8.5.3.3 Determining the Location and Creator of Archived Redo Logs

You can also query the V$ARCHIVED_LOG view on the standby database to find additional information about archived redo logs. Some information you can get includes the location of the archived redo log, which process created the archived redo log, redo log sequence number of each archived redo log, when the log was archived, and whether or not the archived redo log was applied. For example:

SQL> SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME 
  2> FROM V$ARCHIVED_LOG;

NAME                                            CREATOR SEQUENCE# APP COMPLETIO
----------------------------------------------  ------- --------- --- ---------
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00198.001  FGRD          198 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00199.001  FGRD          199 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00200.001  FGRD          200 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00201.001  LGWR          201 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00202.001  FGRD          202 YES 30-MAY-02
H:\ORACLE\ORADATA\PAYROLL\STANDBY\ARC00203.001  LGWR          203 YES 30-MAY-02

6 rows selected.

8.5.3.4 Viewing the Archive Log History

The V$LOG_HISTORY on the physical standby site shows you a complete history of the archived log, including information such as the time of the first entry, the lowest SCN in the log, the highest SCN in the log, and the sequence number of the archived log.

SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_
HISTORY;

FIRST_TIM FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
--------- ------------- ------------ ----------
13-MAY-02        190578       214480          1
13-MAY-02        214480       234595          2
13-MAY-02        234595       254713          3
.
.
.
30-MAY-02       3418615      3418874        201
30-MAY-02       3418874      3419280        202
30-MAY-02       3419280      3421165        203

203 rows selected.

8.5.3.5 Determining Which Logs Were Applied to the Standby Database

Query the V$LOG_HISTORY view on the standby database, which records the latest log sequence number that was applied. For example, issue the following query:

SQL> SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
  2> FROM V$LOG_HISTORY
  3> GROUP BY THREAD#;

THREAD# LAST_APPLIED_LOG
------- ----------------
      1              967

In this example, the archived redo log with log sequence number 967 is the most recently applied log.

You can also use the APPLIED column in the V$ARCHIVED_LOG fixed view on the standby database to find out which log is applied on the standby database. The column displays YES for the log that was applied. For example:

SQL> SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG; 

   THREAD#  SEQUENCE# APP 
---------- ---------- --- 
         1          2 YES 
         1          3 YES 
         1          4 YES 
         1          5 YES 
         1          6 YES 
         1          7 YES 
         1          8 YES 
         1          9 YES 
         1         10 YES 
         1         11 NO 

10 rows selected.

8.5.3.6 Determining Which Logs Were Not Received by the Standby Site

Each archive destination has a destination ID assigned to it. You can query the DEST_ID column in the V$ARCHIVE_DEST fixed view to find out your destination ID. You can then use this destination ID in a query on the primary database to discover logs that were not sent to a particular standby site.

For example, assume the current local archive destination ID on your primary database is 1, and the destination ID of one of your remote standby databases is 2. To find out which logs were not received by this standby destination, issue the following query on the primary database:

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM 
  2> (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL 
  3>  WHERE 
  4> LOCAL.SEQUENCE# NOT IN 
  5> (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND 
  6> THREAD# = LOCAL.THREAD#); 

   THREAD#  SEQUENCE# 
---------- ---------- 
  1        12 
  1        13 
  1        14

The preceding example shows the logs that were not received by standby destination 2.

8 Managing a Physical Standby Database

8.1 Starting Up and Shutting Down a Physical Standby Database

8.1.1 Starting Up a Physical Standby Database

8.1.2 Shutting Down a Physical Standby Database

8.2 Using a Standby Database That Is Open for Read-Only Access

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

8.2.1 Assessing Whether to Open a Standby Database for Read-Only Access

8.2.2 Opening a Standby Database for Read-Only Access

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

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

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

8.2.3 Sorting Considerations For Standby Databases Open for Read-Only Access

8.2.3.1 Sorting Operations While the Database Is Open for Read-Only Access

To create a temporary tablespace for use on a read-only physical standby database

To create and associate a temporary file with a temporary tablespace on a read-only physical standby database

8.2.3.2 Sorting Operations Without Temporary Tablespaces

8.3 Creating Primary Database Back Up Files Using a Physical Standby Database

8.4 Managing Primary Database Events That Affect the Standby Database

Table 8-1 Actions Required on a Standby Database After Changes to a Primary Database

8.4.1 Adding a Datafile or Creating a Tablespace

8.4.1.1 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO

8.4.1.2 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

8.4.2 Dropping a Tablespace in the Primary Database

8.4.3 Renaming a Datafile in the Primary Database

8.4.4 Adding or Dropping Online Redo Logs

8.4.5 Altering the Primary Database Control File

8.4.6 NOLOGGING or Unrecoverable Operations

8.5 Monitoring the Primary and Standby Databases

Table 8-2 Location Where Common Actions on the Primary Database Can Be Monitored

8.5.1 Alert Log

8.5.2 Dynamic Performance Views (Fixed Views)

8.5.3 Monitoring Recovery Progress

8.5.3.1 Monitoring the Process Activities

8.5.3.2 Determining the Progress of Managed Recovery Operations

8.5.3.3 Determining the Location and Creator of Archived Redo Logs

8.5.3.4 Viewing the Archive Log History

8.5.3.5 Determining Which Logs Were Applied to the Standby Database

8.5.3.6 Determining Which Logs Were Not Received by the Standby Site

8
Managing a Physical Standby Database