11g R2 – Apply Services

Hi,

My name is Mark Tiger, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g).

Gathering information for some DBA tasks can be time-consuming, even although the commands that you need to issue eventually can be over quite quickly.  I have gone through this process over and over again, and have decided to help other Oracle DBA’s in the community.

In this blog, I will give you the details of how to carry out those tasks; that typically need a lot of research, before you can do them.  I will try to present the information in an easy to understand way.  My hope is that this will save you lots of time in research, and help to make you more productive as an Oracle DBA.  The illustrations are primarily meant for Linux, since this is a Platform; that enjoys preference from Oracle.  However they are easily adaptable for versions of UNIX/AIX and windows etc.

11g R2 – Apply Services

Apply services allows transactional consistent access to the data, by automatically applying redo to the standby database in order to maintain synchronization with the primary database.

By default, apply services will wait for a standby redo log file to be archived before applying the redo that it contains.  You can also enable real-time apply, which allows apply services to apply the redo in the current standby redo log file, as it is being filled.

Apply services use these methods to maintain physical and logical standby databases:

·         Redo Apply (this is for physical standby databases only)

Uses media recovery to keep the primary and physical standby databases synchronized.

·         SQL Apply (Logical standby databases only)

SQL apply, reconstitutes the SQL statements from the redo received from the primary database.  SQL apply then executes these statements against the logical standby database.

·         You also get real-time apply and delayed apply, which are important considerations.

Apply Services Configuration Options

Using Real-Time Apply to Apply Redo Data Immediately

If the real-time apply feature has been enabled; then apply services can apply data as it is received.  There is no need to wait for the current standby redo log file to be archived.  This will result in faster switchover and failover times.  It will be faster because the standby redo log files would have already been applied by the time the failover or switchover is initiated.

Real-time apply requires the standby database to be configured with a standby redo log configuration.  Real-Time apply requires the standby database to be in ARCHIVELOG mode.

Enable the real-time apply feature like this:

·         On a physical standby database.

SQL> alter database recover managed standby database using current logfile...;

·         ON a logical standby database.

SQL> alter database start logical standby apply immediate...;

In a Data Guard configuration, with a local destination and a standby destination.  As the Remote File Server(RFS) process writes the redo data to standby redo log files on the standby database; the apply services can recover redo from standby redo log file as they are being filled.

Specifying a Time Delay for the Application of Archived Redo Log Files

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

There are cases were you may want to create a time lag or delay between the time when the redo data is received from the primary site, and when it is applied to the standby database.  You would specify this time delay in minutes, typically to protect the standby database from corrupted or erroneous data application.  The DELAY interval specifies the delay from the time that the redo data is completely archived at the standby destination.

You the delay using the DELAY=<minutes> attribute of the LOG_ARCHIVE_DEST_n initialization parameter.  This will delay the applying of the archived redo log files to the standby database.  By default there is no time delay.  If you specify the DELAY attribute without specifying a value, the the default delay interval is 30 minutes.

You can also cancel a specified delay interval in the following way, both ways will result in the apply services immediately beginning to applay the archived redo log files to the standby database:

·         Physical Standby database: use the NODELAY keyword in the RECOVER MANAGED STANDBY DATABASE clause.

SQL> alter database recover managed standby database nodelay...;

·         Logical standby database:

SQL> alter database start logical standby apply nodelay...;

Using Flashback Database as an Alternative to Setting a Time Delay

Instead of setting up an apply delay, you can also use Flashback Database to recover from the application of corrupted or erroneous data to the standby database.  Flashback Database can quickly flashback the standby database to an arbitrary point in time.

Applying Redo Data to Physical Standby Databases

By default, the redo log data is applied from the archived redo log files.  When performing Redo Apply, a physical standby database can use the real-time apply feature to apply redo directly from the standby redo log files as they are being written by the RFS process.

Starting Redo Apply

To start the apply services on a physical standby database, ensure the physical standby database is started and mounted and the start the Redo Apply. 

·         To start Redo Apply and run it in the foreground:

SQL> alter database recover managed standby database;

In this case, control is not returned to the command prompt until recovery is cancelled by another session.

·         To start the Redo Apply in the background, you must include the DISCONNECT keyword in the statement:

SQL> alter database recover managed standby database disconnect;

This will create a detached server process and immediately return control to the user.  While the managed recovery process continues in the background, the SQL*Plus session can disconnect or continue performing other tasks.

·         To start the real-time apply, include the USING CURRENT LOGFILE clause in the statement:

SQL> alter database recover managed standby database using current logfile;

Stopping redo Apply

To stop the redo apply to the standby database:

SQL> alter database recover managed standby database cancel;

Monitoring Redo Apply on Physical Standby Databases

You can manually query the data dictionary, or use enterprise manager to monitor the progress of the redo apply.

·         V$database

You can get the data protection mode, the data protection level, the database role, and the switchover status for a primary, physical standby or snapshot standby database.

SQL> select protection_mode, protection_level, database_role role, switchover_status

From v$database; 

The following displays the fast-start failover status:

SQL> SELECT FS_FAILOVER_STATUS "FSFO STATUS",

                FS_FAILOVER_CURRENT_TARGET TARGET,

                 FS_FAILOVER_THRESHOLD THRESHOLD,

                FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT"

                FROM V$DATABASE;

·         V$managed_standby:

To display the redo apply and redo transport status on a physical standby database.

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

·         V$archived_log

Information about archived redo log files, that have been received by a physical or snapshot standby database from a primary database:

SQL> select thread#, sequence#, first_change#, next_change# from v$archived_log;

·         V$log_history

Archived log history information, the sequence# is a useful diagnostic tool.

SQL> select thread#, sequence#, first_change#, next_change# from v$log_history;

·         V$dataguard_status

Messages generated by Data Guard, events that caused a message to be written to the alert log or to a server process trace file.

SQL> select message from v$dataguard_status;

·         V$archive_dest

Show te status of each redo transport destination, and for redo transport destinations that are standby databases, the SCN of the last primary database redo applied at that standby database.

SQL> select dest_id, status, applied_scn from v$archive_dest where target =’STANDBY’;

Applying Redo Data to Logical Standby Databases

SQL Apply converts the data from the archived redo log or standby redo log in to SQL statements and then executes these SQL statements on the logical standby database.   Because the logical standby database operates in open mode, you can use the standby database simultaneously for other tasks such as reporting, summations and queries.

Starting SQL Apply

The logical standby database needs to be open.

SQL> alter database start logical standby apply;

To start real-time apply on the logical standby database, in order to immediately apply redo data rom the standby redo log files on the logical standby database; you need to include the IMMEDIATE keyword in the statement.

SQL> alter database start logical standby apply immediate;

Stopping SQL Apply on a Logical Standby Database

To stop SQL Apply you can enter this statement.

SQL> alter database stop logical standby apply;

When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied.  So the command may not stop the SQL Apply process immediately, you may have to wait for it to finish.

Monitoring SQL Apply on Logical Standby Databases

You can monitor the logical standby database using enterprise manager, or by manual methods.

·         Dba_logstdby_events:  This view records interesting events that occurred during the operation of SQL Apply.  By default the view records the most recent 10,000 events.  You can change the number of events recorded by the PL/SQL procedure:

DBMS_LOGSTDBY.APPLY_SET().

Errors that cause SQL Apply to stop are recorded in this view.  Such events will also be recorded in the alert log.  You can search through the alert log with this search criteria “LOGSTDBY”, because this keyword will be recorded with any reference in the alert log.  When querying the view you should order by EVENT_TIME_STAMP, COMMIT_SCN, CURRENT_SCN.  This will ensure that the events are ordered in the proper way.

SQL> alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';

SQL> column status format a60

SQL> select event_time, status, event from dba_logstdby_events

Order by event_timestamp, commit_scn, current_scn;

·         DBA_LOGSTDBY_LOG

This view provides dynamic information about archived logs, being processed by SQL Apply.

SQL> select file_name, sequence# as SEQ#, first_change# as F_SCN#,

Next_change# as n_SCN#, timestamp,

Dict_begin as BEG, dict_end as END,

Thread# as thr#, applied from dba_logstdby_log

Order by sequence#;

·         V$dataguard_stats

This view provides stats related to the failover characteristics fo the logical standby database.

o   The time to failover

o   How current the commited data in the logical standby database is

o   What will the potential data loss be in the event of a disaster

SQL> select name, value, unit from v$dataguard_stats;

·         V$logstdby_process

Information about the current state of the various processes involved with SQL Apply

o   Identifying information (sid | serial# | spid)

o   SQL Apploy process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, OR APPLIER(type)

o   Status of the processes current activity (status_code | status)

o   Highest redo record processed by this process (high_scn)

SQL> select sid, serial#, spid, type, high_scn from v$logstdby_process;

·         V$logstdby_progress

Detailed information regarding the progress made by SQL Apply

o   SCN and time at which all transactions that have been committed on the primary database have been applied to the logical standby database.(applied_scn, applied_time)

o   SCN and time at which SQL Apply would begin reading redo records (restart_scn, restart_time)

o   SCN and time of the latest redo record received on the logical standby database (latest_scn, latest_time)

o   SCN and time fo the latest record processed by the BUILDER prcess (minig_scn, mining_time)

SQL> select applied_scn, latest_scn, mining_scn, restart_scn from v$logstdby_progress;

·         V$logstdby_state

This view provides a synopsis of the current state of SQL Apply, including:

o   The DBID of the primary database (primary_dbid)

o   The logminer session ID allocated to SQL Apply (session_id)

o   Whether or not SQL Apply is applying in real time (realtime_apply)

SQL> select * from v$logstdby_state;

·         V$logstdby_stats

This view displays statistics, current state, andstatus information related to the SQL Apply.  No rows are returned form this view when SQL Apply is not running.  This view is only meaningful in terms of a logical standby database.

SQL> select substr(name, 1,40) as name,

Substr(value,1,32) as value from v$logstdby_stats;

MarkTiger01@gmail.com

P.S. I am busy preparing Nine books on preparing for the Oracle 11g R2 OCM DBA exam.  Watch this spot for details on the books, as they start becoming available.