STEPS TO CONFIGURE DATAGUARD in 10G

STEPS TO CONFIGURE DATAGUARD in 10G

Assuming :

Primary instance is running and oracle software of same version is installed in the standby server.
Primary database name : colab
Standby database name : colabsby

1. Physical standby database creation :

a. Preparing the Primary Database for Standby Database Creation:

Before we create a standby database you must first ensure the primary database is properly configured.

1. Enable Force logging
2. Create a password File
3. Create standby Redo log file groups
4. Set Primary Database Initialization Parameters
5. Enable archiving

1.Enable Force logging :

Place the primary database in FORCE LOGGING mode after database creation using the following SQL
statement:

SQL> select force_logging from v$database;

FORCE_LOG
---------
NO

SQL> ALTER DATABASE FORCE LOGGING;

2.Create a password File :

Create a password file if one does not already exist.

3.Create standby Redo log file groups :

SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.

4.Set Primary Database Initialization Parameters :

SQL> alter system set log_archive_config='dg_config=(colab,colabsby)';
System altered.

Edit pfile and add the following parameters :

DB_UNIQUE_NAME=colab
LOG_ARCHIVE_CONFIG='DG_CONFIG=(colab,colabsby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/data3/oradata/colab/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=colab'
LOG_ARCHIVE_DEST_2=
'SERVICE=colabsby LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=colabsby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
*.fal_client='colab'
*.fal_server='colabsby'
*.DB_FILE_NAME_CONVERT='/data3/oradata/colabsby','/data3/oradata/colab'
*.LOG_FILE_NAME_CONVERT='/oracle/flash_recovery_area/COLABSBY/onlinelog','/oracle/flash
_recovery_area/COLAB/onlinelog'
*.standby_file_management='AUTO'

SQL> startup pfile
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
SQL> startup
SQL> alter system set standby_file_management = AUTO;
System altered.

5.Enable archiving :

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /data1/oracle/admin/colab/arch
Oldest online log sequence 16
Current log sequence 18
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list

Edit the tnsnames.ora & listener.ora in primary to add standby database details.

Edit the tnsnames.ora & listener.ora in standby to add standby database details or we can copy the files
from primary database.

b. Step-by-Step Instructions for Creating a Physical Standby Database :

1.Create a backup copy of the primary database Datafiles:

We can use any kind of primary database backup viz COLD,HOT.

2.Create a Control File for the standby database:

In primary :

SQL>SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT;

create the control file for the standby database, and open the primary database to user access.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/colabsby.ctl';
SQL> ALTER DATABASE OPEN;

3.Prepare an Initialization parameter File for the Standby Databases:

Create pfile from spfile used by the primary database.
Sql>create pfile='/tmp/initcolabsby.ora' from spfile;

Copy this pfile to standby database and modify the following parameters accordingly :

Ex:
db_name='colab'
DB_FILE_NAME_CONVERT='/data1/oracle/oradata/colab','/oracle/oradata/colabsby'
DB_UNIQUE_NAME='colabsby'
fal_client='colabsby'
fal_server='colab'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/admin/colabsby/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=colabsby'
LOG_ARCHIVE_DEST_2='SERVICE=colab LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=colab'
LOG_ARCHIVE_DEST_STATE_1='ENABLE'
LOG_ARCHIVE_DEST_STATE_2='ENABLE'
LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
LOG_FILE_NAME_CONVERT='/data1/oracle/flash_recovery_area/COLAB/onlinelog','/oracle/flash_r
ecovery_area/COLABSBY/onlinelog'

4.Copy files from the Primary system to the standby system:

Use an operating system copy utility to copy the following binary files from the primary system to the
standby system:

. Backup Datafiles
. Standby controlfile
. Initialization parameter file

5.Set Up the Environment to Support the Standby Database:

Create a password file if its required and configure the listeners for both primary and standby.

$ lsnrctl stop
$ lsnrctl start

Try to ping both the databases from both servers using TNSPING utility

ex: tnsping colab
tnsping colabsby

6.Start the Physical Standby Database:

On the standby database :

$sqlplus "/as sysdba"
SQL> startup nomount pfile=/oracle/product/10.2.0.1/dbs/initcolabsby.ora
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
SQL> startup mount

* To start Redo Apply :

SQL> alter database recover managed standby database disconnect from session;
Database altered.

7.Verify the Physical Standby Database Is Performing Properly:

On the Primary & Standby run this to identify existing archived redo log files :

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

SEQUENCE# FIRST_TIME APPLIED
-------------- ------------------ -------------
25 06-MAY-10 NO
26 06-MAY-10 YES
27 06-MAY-10 YES
28 06-MAY-10 YES
29 06-MAY-10 YES

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

SQL>ALTER SYSTEM SWITCH LOGFILE; or ALTER SYSTEM ARCHIVE LOG CURRENT;

Now on the primary & standby database, query the V$ARCHIVED_LOG view to verify the redo data
was received and archived on the standby database:

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

SEQUENCE# FIRST_TIME APPLIED
---------------- --------------- --------------
25 06-MAY-10 NO
26 06-MAY-10 YES
27 06-MAY-10 YES
28 06-MAY-10 YES
29 06-MAY-10 YES
30 06-MAY-10 NO

Standby :

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

SEQUENCE# FIRST_TIME APPLIED
---------------- --------------- --------------
25 06-MAY-10 NO
26 06-MAY-10 YES
27 06-MAY-10 YES
28 06-MAY-10 YES
29 06-MAY-10 YES
30 06-MAY-10 NO

Now the standby database is configured properly and it is receiving archived logs from the primary database.

NEXT STEP is to configure Data Guard broker to perform switch over when primary or secondary fails.

1. IN PRIMARY :

SQL> alter system set dg_broker_config_file1='/data1/oracle/product/10.2.0.1/dbs/dr1colab.dat' scope=spfile;
SQL> alter system set dg_broker_config_file2='/data1/oracle/product/10.2.0.1/dbs/dr2colab.dat' scope=spfile;
SQL> alter system set dg_broker_start=true sid='*';
SQL> sho parameter dg_broker_start
SQL> sho parameter dg_broker_config

Check whether the DMON process is started or not :

-bash-3.00$ ps -ef|grep dmon|grep -v grep
oracle 1830 1 0 14:02:43 ? 0:00 ora_dmon_colab

2. In STANDBY :

SQL> alter system set dg_broker_config_file1='/oracle/product/10.2.0.1/dbs/dr1colabsby.dat' scope=spfile;
SQL> alter system set dg_broker_config_file2='/oracle/product/10.2.0.1/dbs/dr2colabsby.dat' scope=spfile;
SQL> alter system set dg_broker_start=true sid='*';
SQL> sho parameter dg_broker_start
SQL> sho parameter dg_broker_config.

Check whether the DMON process is started or not :

bash-2.05$ ps -ef|grep dmon|grep -v grep
oracle 11427 1 0 01:23:35 ? 0:00 ora_dmon_colabsby

IN PRIMARY :

-bash-3.00$ dgmgrl

DGMGRL for Solaris: Version 10.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.

DGMGRL> create configuration 'DGcolab1' as
> primary database is 'colab'
> connect identifier is colab;
Configuration "DGCOLAB1" created with primary database "colab"

DGMGRL> show configuration
Configuration
Name: DGCOLAB1
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ccollab - Primary database
Current status for "DGCOLAB1":
DISABLED

DGMGRL> add database 'colabsby' as
> connect identifier is colabsby
> maintained as physical;
Database "colabsby" added

DGMGRL> show configuration
Configuration
Name: DGcolab1
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
colab - Primary database
colabsby - Physical standby database
Current status for "DGcolab1":
DISABLED

DGMGRL>ENABLE CONFIGURATION;
ENABLED

DGMGRL> show configuration
Configuration
Name: DGcolab1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
colab - Primary database
colabsby - Physical standby database
Current status for "DGcolab1":
SUCCESS

The new Data Guard broker configuration will now be tested by performing a switchover operation :

DGMGRL> switchover to colabsby;

Performing switchover NOW, please wait .......
New primary database 'colabsby' is opening .......
Operation requires shutdown of instance “colab” on database “colab”
Shutting down instance “colab” ......
...........................................................................
...........................................................................
...........................................................................
...........................................................................
Switchover succeeded, new primary is 'colabsby'

DGMGRL> show configuration
Configuration
Name: DGcolab1
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
colabsby - Primary database
colab - Physical standby database
Current status for "DGcolab1":
SUCCESS

Following the switchover database have swapped the roles.

Login to standby database and check for confirmation :

SQL>select database_role from v$database;

DATABASE_ROLE
---------------------------
PRIMARY


/*END*/