Oracle Tips/Questions and Answers:

Question: 

Data Guard: How to create a physical Oracle standby database?

Answer:

Purpose:

This tutorial shows how to:

1-      Duplicate a database,

2-      Configure to maximize performance standby mode,

3-      Test the data on the physical standby database,

4-      Monitor the primary and physical standby databases.

Notes/Assumptions:

Using the following:

Primary database:  o4guard - $ORACLE_HOME=/u01/oradata/ o4guard

Physical Standby database: o4stby - $ORACLE_HOME=/u01/oradata/ o4stby

Alert logs:            /u01/diag/rdbms/o4guard/trace/ alert_o4guard.log,

/u01/diag/rdbms/o4stby/trace/ alert_o4stby

The “adrci” command – to check the alert log, oracle home, base, etc.

Install Oracle binaries at the physical standby database’s destination.

Create all the data files, redo logs, and controlfiles directories.

Topics:

It will cover the following topics:

1-      Data Guard,

2-      Primary database,

3-        Physical standby database,

4-      ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY,

5-      Configuring Data Guard environment

Subject:

How to create a Physical Oracle Standby database?

There are some many ways that you can configure the data guard databases.  You can utilize RMAN by using DUPLICATE command as “DUPLICATE FROM ACTIVE DATABASE” or manually. In this tutorial we do manually, so you can see exactly how the Data Guard works.

1-      On the primary database, alter the database to force logging.

Check to see if the database is at force logging.

SQL> SELECT force_logging FROM v$database;

If not, then alter the database.

SQL> ALTER DATABASE FORCE LOGGING;

2-      Add or make sure you have the following parameter at the primary initial parameter (pfile).

Then create SPFILE from PFILE. (You must use SPFILE)

DB_NAME=o4guard

DB_UNIQUE_NAME=o4guard
LOG_ARCHIVE_CONFIG=’DG_CONFIG= (o4guard,o4stby)’
LOG_ARCHIVE_DEST_1=’LOCATION=/ u01/oradata/o4guard/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ ROLES) DB_UNIQUE_NAME=o4guard’
LOG_ARCHIVE_DEST_2=’SERVICE= o4stby ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=o4stby’
LOG_ARCHIVE_DEST_STATE_1= ENABLE
LOG_ARCHIVE_DEST_STATE_2= ENABLE
REMOTE_LOGIN_PASSWORDFILE= EXCLUSIVE
LOG_ARCHIVE_FORMAT=’o4guard_% t_%s_%r.arc’
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=’o4stby’
FAL_CLIENT=’o4guard’
DB_FILE_NAME_CONVERT=’/u03/ oradata/o4stby/’,'/u03/ oradata/o4guard/’
LOG_FILE_NAME_CONVERT=’ /u05/oradata/o4stby/’,'/u05/ oradata/o4guard/’
STANDBY_FILE_MANAGEMENT=’AUTO’

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP PFILE=$ORACLE_HOME/rdbms/ INITo4guard.ini

SQL> CONNECT / AS SYSBA

SQL> CREATE spfile FROM pfile;

3-      Make sure the primary database is in archive log mode.

Check the primary database is on the archive log mode.

SQL> ARCHIVE LOG LIST;

If it is not, then enable archive log process.

SQL > SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

4-      Shutdown the primary database and create a cold copy of the database.

SQL> SHUTDOWN IMMEDIATE;

SQL> HOST cp /u03/oradata/o4guard/*.dbf /u03/oradata/o4stby/.  – Assuming all the data files are in the same location

SQL> HOST cp /u05/oradata/o4guard/*.log /u03/oradata/o4stby/.  – Assuming all the log files are in the same location

SQL> HOST cp $ORACLE_HOME/database/ PWDo4guard.ora $ORACLE_HOME/database/ PWDo4stby.ora /.  – At standby destination

5-      Create a standby controlfile on the primary database.

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE  CREATE STANDBY CONTROLFILE AS ‘/u01/o4stby.ctl’;

SQL> ALTER DATABASE OPEN;

Then, copy the controlfile to the standby database destinations:

SQL> HOST cp /u01/o4stby.ctl /u02/oradata/o4stby/ controlfile_01.ctl

SQL> HOST cp /u01/o4stby.ctl /u03/oradata/o4stby/ controlfile_02.ctl

6-      Create the initialization file for your physical standby database and then, copy the primary database’s PFILE to the standby destination and modify the parameters.

DB_UNIQUE_NAME=o4stby
CONTROL_FILES=/u02/oradata/ o4stby/controlfile_01.ctl’, ‘/u03/oradata/o4stby/ controlfile_02.ctl’

DB_FILE_NAME_CONVERT='/u03/ oradata/o4guard/’, ’/u03/oradata/o4stby/’
LOG_FILE_NAME_CONVERT='/u05/ oradata/o4guard/’,’ /u05/oradata/o4stby/’
LOG_ARCHIVE_DEST_1=’LOCATION=/ u03/o4stby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ ROLES) DB_UNIQUE_NAME=o4stby’
LOG_ARCHIVE_DEST_2=’SERVICE= o4guard ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=o4guard1g’
FAL_SERVER=’o4guard’
FAL_CLIENT=’o4stby’

7-      ** if you use window, run the following command on DOS to create service for your physical standby database and setup standby environment.

WINNT> oradim –NEW –SID o4stby –STARTMODE manual

8-      Configure listeners for the primary and standby databases and bounce listeners using NET Manager.

9-      Now, you are ready to start your physical standby database. Go to the standby database destination and start the database. Make sure before starting the database you have set your ORACLE_HOME and ORACLE_SID parameters. Once you start your database add the standby logfiles.

SQL> CREATE SPFILE FROM PFILE=$ORACLE_HOME/rdbms/ INITo4guard.ini

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4 (‘/u02/oradata/o4stby/redo04. log’) SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 5 (‘/u03/oradata/o4stby/redo05. log’) SIZE 50M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 6 (‘/u04/oradata/o4stby/redo06. log’) SIZE 50M;

10-   Open the database at recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11-   Now, you should be able to check and confirm that all the transaction on the primary database will be copy to the physical standby database.

On the primary database, check the archived sequence number and make a note of the last sequence number. Then create a table and insert records into your created table. At the end archive the current log file (ALTER SYSTEM ARCHIVE LOG CURRENT).

SQL> SELECT SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY 1;

On the standby database, check the applied archive log files. If the “APPLIED” value is “NO,” then run the query again until you get the “YES” value. It may take a while.

SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY 1;

Now, you should be able to see your created table with all the data.

How to stop and start the standby database:

At the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;   --to stop applying the redo log

SQL> ALTER DATABASE OPEN;

To start applying redo log file again, do the following:

SQL> STARTUP;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFLILE DISCONNECT;

How to change the physical standby database to the read-write mod:

--On the standby database

First stop redo apply and also make sure that you are on the MOUNT status.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;   --to stop applying the redo log

SQL> SELECT status FROM v$instance;

Now, change the read-only state to the read-write state. In order to do this first you need to convert the standby database to snapshot and then shutdown immediately to alter database with flashback on and open the database.

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; -- it will dismount the database and must be restarted

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

Now, you should be able to create tables and insert data. Notice that all the changes will not be applied to the primary database, unless you convert it back to physical standby at recovery mode;

How to change the standby database back to physical standby:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; -- it will dismount the database and must be restarted

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFLILE DISCONNECT;

Good Luck!