Dev's Weblog

Reading time: 8 - 13 minutes

Task 1: Gather Files and Perform BackUp..........................

Task 2: Configure Oracle Net SERVICES on the Standby.............

Task 3: Create the Standby Instances and Database................

Task 4: Configure The Primary Database For Data Guard............

Task 5: Verify Data Guard Configuration..........................

1. On the primary node, create a staging directory.

For example:

[oracle@primary oracle]$ mkdir -p /u01/stage

1. Create the same exact path on one of the standby hosts:

[oracle@quartz oracle]$ mkdir -p /u01/stage

2. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory.

For example:

SQL> CREATE PFILE='/u01/stage/initPRIMARY.ora' FROM SPFILE;

3. On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory.

For example:

[oracle@primary stage]$ rman target /

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/u01/stage/%U' DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/u01/stage/%U' CURRENT CONTROLFILE FOR STANDBY;

5. Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:

[oracle@primary oracle]$ cp $ORACLE_HOME/network/admin/*.ora /u01/stage

6. Copy the contents of the staging directory on primary node to the standby node on which the staging directory was created on in step 2. For example:

[oracle@primary oracle]$ scp /u01/stage/* oracle@quartz:/u01/stage

1. Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on all standby hosts.

2. Modify the listener.ora files of primary and standby host.

Listner.ora of primary host must look like following.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = primary)

(GLOBAL_DBNAME = primary_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

Listner.ora of standby host must look like following.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = standby)

(GLOBAL_DBNAME = standby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = quartz)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

1. Start the standby listeners on all standby hosts.

1. To enable secure transmission of redo data, make sure the primary and standby databases use a password file, and make sure the password for the SYS user is identical on every system.

For example:

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwSTANDBY password=oracle

2. Copy and rename the primary database PFILE from the staging area on all standby hosts to the $ORACLE_HOME/dbs directory on all standby hosts. For example:

[oracle@standby_host1 stage]$ cp initPRIMARY.ora $ORACLE_HOME/dbs/initSTANDBY.ora

2. Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table:

Data Guard Parameter

*.log_archive_config=

'dg_config=(primary,standby)'

*.log_archive_dest_2=

'service=standby valid_for=(online_logfiles,primary_role)

db_unique_name=standby'

*.db_file_name_convert='/u01/database/primary',

'/u01/database/standby'

*.log_file_name_convert='/u01/database/primary',

'/u01/database/standby'

*.standby_file_management=auto

*.fal_server='standby'

*.fal_client='primary'

*.service_names='primary'

--------------

*.background_dump_dest=

/u01/app/oracle/admin/standby/bdump

*.core_dump_dest=

/u01/app/oracle/admin/standby/cdump

*.user_dump_dest=

/u01/app/oracle/admin/standby/udump

*.audit_file_dest=

/u01/app/oracle/admin/standby/adump

*.db_recovery_dest='+RECOVERY'

1. Create the dump directories on all standby hosts as referenced in the standby initialization parameter file.

For example:

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/bdump

[oracle@standby_host1 oracle]$ mkdir -p

$ORACLE_BASE/admin/STANDBY/cdump

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/udump

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/adump

1. After setting up the appropriate environment variables on each standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the standby database instance on the standby host that has the staging directory, without mounting the control file.

SQL> STARTUP NOMOUNT

1. From the standby host where the standby instance was just started, duplicate the primary database as a standby.

For example:

$ rman target sys/oracle@primary auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

1. Connect to the standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs.

The recommended number of standby redo logs is:

(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be

(2 + 1) * 2 = 6.

That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter.

Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

On only one standby host (and this is your designated Redo Apply instance), start managed recovery and real-time apply on the standby database:

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

1. Configure the primary database initialization parameters to support both the primary and standby roles.

*.log_archive_config='dg_config=(STANDBY,PRIMARY)'

*.log_archive_dest_2='service=STANDBY

valid_for=(online_logfiles,primary_role)

db_unique_name=STANDBY'

*.db_file_name_convert='/u01/database/standby','/u01/database/primary' *.log_file_name_convert='/u01/database/standby','/u01/database/primary'

*.standby_file_management=auto

*.fal_server='STANDBY'

*.fal_client='PRIMARY'

*.service_names=PRIMARY

1. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread .

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

1. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2. On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. On the standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;