How to turn on Data Guard Oracle Database Tips by Donald Burleson

Turning on Data Guard

The next step is to turn the Data Guard on and create the temporary tablespace datafiles. The temporary tablespace is required because the log apply service uses the temporary tablespace during the SQL apply mode. The Data Guard can be enabled using the following statement:

ALTER DATABASE GUARD ALL;

The guard status can be verified from the v$database view:

Select
   Name,
   Guard_Status
From
   V$DATABASE;


NAME      GUARD_S
--------- -------
LAPPSDB   ALL

Starting the Data Guard Logical Standby Database

At this point, the logical Oracle instance is ready and the automatic log apply service can be started. Register the archived redo log, which is generated after the LogMiner dictionary creation on the primary database, and start the SQL apply service. To register the archived redo log use the following statement:

ALTER DATABASE REGISTER LOGICAL LOGFILE

'/oracle/appsdb/arch/appsdb_1_568.dbf';

Use the following statement to start applying the log files:

ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

At this point, Oracle will start a process called LSP0.  This process will facilitate the recovery process using log miner on the standby site. This step can be verified in the alert log file:

ALTER DATABASE START LOGICAL STANDBY APPLY

with optional part
INITIAL
LSP0 started with pid=11
Fri Oct  3 16:14:47 2003
Attempt to start background Logical Standby process
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL
Fri Oct  3 16:14:48 2003
LOGSTDBY event: ORA-16111: log mining and apply setting up

The last line in the alert log file shows an Oracle error 16111.  This is normal and it indicates that the log apply service is waiting for the redo from the primary database.

Since the primary database was running before the logical Oracle instance was created and the init.ora file of primary database has log_archive_dest_state_2 = ENABLE, it will try to transfer the log files to the standby site.

However, at that time, the listener on the standby site was not running, and as a result the log transfer service on the primary site will throw error [TNS: No Listener] and invalidate the log_archive_destination. The v$archive_dest view can be queried on the primary database to check this.

Once the listener is started on the standby site and the tnsnames on both the standby and the primary site is configured to establish connection between the primary and the standby database, the log_archive_dest can be validated again using the following statement executed on the primary site:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

Starting the Logical Standby Database

Query the v$archive_dest view again to verify that the archive destination pointing to standby site is valid:

Select
   DEST_NAME,
   STATUS,
   ERROR
From
   V$ARCHIVE_DEST;

Switch a logfile on the primary database to initiate the log transfer. After a log file on the primary database is switched, the log transfer service will transmit all the archived log files created after the LogMiner dictionary was built. For example, all the log files created after logfile will be transmitted. Check the standby_archive_dest location on the standby database to verify this. If any log is missing, a gap sequence will be created.

Verify the Logical Standby Database

A couple of SQL statements should be run on the primary database and the Oracle instance to verify that the data on both databases are in sync. Alternatively, a sample table can be created on the primary database. Populate a few rows in it, commit the changes, and do a log switch. After few moments, the new tables and rows should appear in the standby database. This will prove that the logical Oracle instance is running and is being updated with the changes on the primary database.