DataGuard

Data Guard

Data Guard is a mechanism for having a copy of one database mirror the activities happening on the main site. This mechanism is particularly applicable where recovery from a backup is unacceptable with regards to time and effort. This solution allows a quick 'failover' from machine to machine with the minimum of effort.

There are various levels of data transfer between live and data guard sites -
-
-
-

Types of Data Guard.

Physical standby - The standby database is mounted in constant recovery and is unavailable for query.

Logical standby - The database is open (read only) and available for query. Extra indexes can be added to improve query performance turning the standby database into a reporting database as well. There are limitations to the types of data supported by this type of standby database. Please see the documentation for your particular database version and requirements.

The background.

Data Guard continuously copies archive logs from one machine to 'another' and then applies them to a 'backup' database, which is in a constant state of recovery.

If the main database / machine should fail then the backup machine / database can take over.

All directories and filenames are the same as on the live machine. This allows the elimination of filename conversion and any other associated conversion and the inevitable confusion that arises.

How to Put the Standby Live.

1. Shut down the live database.

2. Copy all redo logs to the backup machine (if possible).

On Athena (current Data Guard backup machine) issue the following command when connected as / as sysdba.

Identify and resolve any archived log gaps.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
This will show all gaps of log files and what may need to be copied across from the original machine.

Register any unapplied log files just copied across.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'arcxxxx.log';

Repeat the last two steps until the query returns nothing.

3. Copy in the redo logs to bring the database up to the most recent transaction level.

4. Finish managed recovery.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

5. Switch the backup to take over from the primary database.
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

After issuing this SQL statement, you can no longer use this database as a standby database and subsequent redo logs from the original primary database cannot be applied.

6. SQL> SHUTDOWN IMMEDIATE

7. SQL>STARTUP

To resume the original machine / database combination the original database should be recreated as a standby database and then you should perform a switchover.
Stopping / Starting the Standby Database.

The data guard database is started and stopped in a slightly different manner to a normal database.

Stopping the database.
Connect / as sysdba
à shutdown immediate.

Starting the database
Connect / as sysdba
à STARTUP NOMOUNT;
à ALTER DATABASE MOUNT STANDBY DATABASE;
àALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The 'disconnect from session' allows the recover command to return you to the command line and run recovery in the background. (Else you would have to leave a SQL window open.)


Basic Commands

To see what activity is taking place on the standby site:
SQL> SELECT * FROM V$MANAGED_STANDBY;

Determine how much current redo data is available in the redo log.

SQL>SELECT THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2> FROM V$MANAGED_STANDBY WHERE STATUS='RECEIVING';

Obtain a list of the archived redo logs that were applied or are currently pending application to the SAT database.

SQL> SELECT SUBSTR(NAME,1,25) FILE_NAME, SEQUENCE#, APPLIED
2> FROM V$ARCVHIVED_LOG ORDER BY SEQUENCE#;