Hungry DBA .com for DBAs who like food

Example of how to configure DataGuard Standby Databases 

Basic configuration 

1. If relevant disable log shipping on the primary

SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;

2. If relevant shutdown the existing standby database and remove database files

3. From primary shutdown the database and copy files to standby location

-- this will remotely copy the files located in /pmprd/oradata and /pmprd/oraindex to the stbyserver standby server directories /stprd/oradata and /stprd/oratemp

scp -C -o CompressionLevel=4 /pmprd/oradata/* oracle@stbyserver:/stprd/oradata
scp -C -o CompressionLevel=4 /pmprd/oratemp/* oracle@stbyserver:/stprd/oratemp

4. On primary create standby controlfile and copy this to the standby location

alter database create standby controlfile as '/pmprd/oradata/stbycf.ctl';

scp /pmprd/oradata/stbycf.ctl oracle@stbyserver:/stprd/oradata

5. Setup oracle parameters

on primary:

-- ensure that in the tnsnames.ora file the TNS alias stprd.dbsupportservices.co.uk points to the DR database

*.log_archive_dest_2='SERVICE=stprd.dbsupportservices.co.uk REOPEN=60'
*.log_archive_dest_state_2='ENABLE'
*.standby_archive_dest='/pmprd/archive'
*.standby_file_management='AUTO'

on standby:

-- ensure that in the tnsnames.ora file the TNS alais stprd points to the DR database and pmprd_fal points to the primary database

*.fal_client='stprd'
*.fal_server='pmprd_fal'
*.standby_archive_dest='/stprd/archive/'
*.standby_file_management='auto'

-- ensure that the database file locations are converted to point to the correct location on the standby

*.db_file_name_convert='/pmprd/','/stprd/'
*.log_file_name_convert='/pmprd/','/stprd/'

-- ensure the control file is pointing to the standby controlfile:

*.control_files='/stprd/oradata/stbycf.ctl'

6. Bring the standby database up to date

-- startup the standby database in mount mode
SQL> startup nomount;

SQL> alter database mount standby database;

-- recover the database to same state as primary

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

-- check alert log to see status of files being applied.  Once the up to date archive logs have been applied cancel recovery as follows:


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

7. Enable log shipping on the primary server

SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;

8. Test that logs are shipping across

-- On primary server

alter system switch logfile;

-- arch log files should appear in the standby log destination (/emprd/archive/)

9. Place standby database in automatic restore mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Perform manual recovery of a standby database

Managed Recovery as provided in the above example is issed by the following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

By running Managed Recovery if a required log file cannot be found then Oracle will attempt to automatically retrieve the file from the primary server and will then apply it.

In some cases you might need to have a more controlled mechanism of recovering the standby database and can issue a manual recovery as follows:

-- Mount the standby database if not already mounted
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

Although the above statement says RECOVER AUTOMATIC; what it will actually do will restore up to the latest archive file which is held locally on the standby server; it will not try to retrieve files that cannot be found from the primary server.

Thus if you only wanted to recover up to a certain log you could rename the relevant logfile as follows:

mv /PROD/archive/0001_0000180425.arc /PROD/archive/0001_0000180425.arc.stop

Once the standby reaches the appropriate point using the AUTOMATIC recovery method you will receive an error similar to that below:

SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

ORA-00279: change 2205611386965 generated at 15/04/2007 19:11:01 needed for
thread 1
ORA-00289: suggestion : /PROD/archive/0001_0000180425.arc
ORA-00280: change 2205611386965 for thread 1 is in sequence #180425
ORA-00278: log file '/PROD/archive/0001_0000180425.arc' no longer needed for
this recovery
ORA-00308: cannot open archived log '/PROD/archive/0001_0000180425.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

-- Stop the recovery as follows:

SQL> CANCEL
Media recovery cancelled.

-- It is now safe to rename the archive log file back to the original source:

mv /PROD/archive/0001_0000180425.arc.stop /PROD/archive/0001_0000180425.arc

An example of a typical usage for manual recovery is if you wanted to create a clone from the standby database up to a point in time without affecting the primary server.

Activating a DataGuard Standby Database

-- If relevant stop managed recovery

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Activate the standby database

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

-- Restart and open the database for read write mode.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE OPEN READ WRITE;