Oracle 10g Data Guard Setup/Configuration

Since I've mentioned in my last post that I'm currently re-exploring Oracle Data Guard, here is my 1st entry for my Data Guard series.

First things first, below are the specs of my test environment:
- HP G60-445DX (w/ 4GB RAM)
- Ubuntu 9.04 64-bit (instead of Vista Home premium to maximize the RAM)
- VMWare 2.0

For the Oracle Data Guard Configuration I created 2 Virtual Machines, each with the ff. specs:
RAM: 1GB (have tried this with 512 MB before, but very slow)
OS : Oracle Enterprise Linux 4 (can be downloaded from e-delivery.oracle.com)
DB: Oracle 10g R2 (10.2.0.4)
HD: 30 GB (not pre-allocated)


I assumed that you guys already have background in database administration, so I will be skipping the fundamentals (e.g DB installation, patching etc.)
I named my Primary database as "prim10g" and its host as "oel41", while my Physical Standby database as "stby10g" and its host as "oel42".



Setting Up Oracle Data Guard (Physical Standby Database)

 
1. Turn on archiving on the Primary database

[root@oel41 ~]# su - oracle
[oracle@oel41 ~]$ export ORACLE_SID=prim10g
[oracle@oel41 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 1 14:20:03 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set log_archive_dest='/u02/oradata/archive/' scope=both;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1267044 bytes
Variable Size 104860316 bytes
Database Buffers 171966464 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open
2 /
Database altered.

2. Gather necessary files to create a physical standby database and perform an RMAN backup
 
2.1 Create a staging directory on Primary and Standby host
[oracle@oel41 ~]$ mkdir -p /u02/stage
[oracle@oel41 ~]$

2.2 Create a copy of the init.ora (pfile)
SQL> create pfile='/u02/stage/initprim10g.ora' from spfile;

2.3 Backup the database including archivelogs and create a backup of the current controlfile for standby database

[oracle@oel41 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 1 14:54:14 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: PRIM10G (DBID=3316445735)

RMAN> run { allocate channel c1 type disk;
2> backup database format '/u02/stage/db%U'
3> plus archivelog format '/u02/stage/arc%U';
4> backup current controlfile for standby format '/u02/stage/ctl%U';
5> }

2.4 Copy *.ora files from $ORACLE_HOME/network/admin to staging directory
[oracle@oel41 ~]$ cd $ORACLE_HOME/network/admin
[oracle@oel41 admin]$ cp *.ora /u02/stage/

2.5 Copy all the files in the Primary's staging directory to Standby node
[oracle@oel41 ~]$ scp /u02/stage/* oracle@oel42:/u02/stage/


3. Configure Net Services on the Standby
[oracle@oel42 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel42.seer-technologies.com)(PORT = 1521))
)

[oracle@oel42 admin]$
[oracle@oel42 admin]$
[oracle@oel42 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIM10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel41.seer-technologies.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim10g)
)
)

STBY10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel42.seer-technologies.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby10g)
)
)

[oracle@oel42 admin]$

4. Create a Physical Standby database

4.1 Create password file.  Ensure that the password is the same the Primary database SYS password.

[oracle@oel42 admin]$ cd $ORACLE_HOME/dbs
[oracle@oel42 dbs]$ ls
hc_prim10g.dat initdw.ora init.ora isnapcf_prim10g.f
[oracle@oel42 dbs]$
[oracle@oel42 dbs]$ orapwd file=orapwSTBY10g password=oracle10g

4.2 Copy and edit init.ora for standby
[oracle@oel42 ~]$ cd /u02/stage/
[oracle@oel42 stage]$ cp initprim10g.ora $ORACLE_HOME/dbs/initstby10g.ora

###########################
#below is the copy of my initstby10g.ora:
###########################

stby10g.__db_cache_size=171966464
stby10g.__java_pool_size=4194304
stby10g.__large_pool_size=4194304
stby10g.__shared_pool_size=96468992
stby10g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stby10g/adump'
*.background_dump_dest='/u01/app/oracle/admin/stby10g/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/stby10g/control01.ctl','/u01/app/oracle/oradata/stby10g/control02.ctl','/u01/app/oracle/oradata/stby10g/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/stby10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim10g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stby10gXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u02/oradata/archive/'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/stby10g/udump'

db_unique_name='stby10g'
instance_name='stby10g'

log_archive_config='dg_config=(stby10g,prim10g)'
log_archive_dest_2='service=prim10g valid_for=(online_logfiles,primary_role) db_unique_name=prim10g'
db_file_name_convert='/u01/app/oracle/oradata/prim10g/','/u01/app/oracle/oradata/stby10g/'
log_file_name_convert='/u01/app/oracle/oradata/prim10g/','/u01/app/oracle/oradata/stby10g/'
standby_file_management=auto
fal_server='prim10g'
fal_client='stby10g'
service_names='stby10g'

4.3 Create required directories
[oracle@oel42 dbs]$ mkdir -p /u01/app/oracle/oradata/stby10g/
[oracle@oel42 dbs]$ mkdir -p /u01/app/oracle/admin/stby10g/adump
[oracle@oel42 dbs]$ mkdir -p /u01/app/oracle/admin/stby10g/bdump
[oracle@oel42 dbs]$ mkdir -p /u01/app/oracle/admin/stby10g/cdump
[oracle@oel42 dbs]$ mkdir -p /u01/app/oracle/admin/stby10g/udump
[oracle@oel42 dbs]$

4.4 Create an spfile and start the database without mounting the controlfile
[oracle@oel42 dbs]$ export ORACLE_SID=stby10g
[oracle@oel42 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 1 19:40:56 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL>
SQL> startup nomount
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267044 bytes
Variable Size 104860316 bytes
Database Buffers 171966464 bytes
Redo Buffers 7118848 bytes
SQL>

4.5 Duplicate the Primary database using RMAN
[oracle@oel42 dbs]$ rman target sys/oracle10g@prim10g auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 1 19:47:50 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: PRIM10G (DBID=3316445735)
connected to auxiliary database: STBY10G (not mounted)

RMAN> duplicate target database for standby;


NOTE: During my RMAN restoration, I encountered these errors:

sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/01/2009 19:49:33
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 09/01/2009 19:49:33
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'PRIM10G' in control file is not 'STBY10G'

To fix this error, I changed the db_name parameter of the standby database from "stby10g" to "prim10g". As per Dataguard documentation, db_name parameter must be the same on the primary and standby databases.

4.6 Add Standby redo logs
SQL> alter database add standby logfile
2 group 4 ('/u01/app/oracle/oradata/stby10g/stbyredo01.log') size 50M,
3 group 5 ('/u01/app/oracle/oradata/stby10g/stbyredo02.log') size 50M,
4 group 6 ('/u01/app/oracle/oradata/stby10g/stbyredo03.log') size 50M,
5* group 7 ('/u01/app/oracle/oradata/stby10g/stbyredo04.log') size 50M
SQL> /


SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/stby10g/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/stby10g/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/stby10g/redo01.log NO
4 STANDBY /u01/app/oracle/oradata/stby10g/stbyredo01.log NO
5 STANDBY /u01/app/oracle/oradata/stby10g/stbyredo02.log NO
6 STANDBY /u01/app/oracle/oradata/stby10g/stbyredo03.log NO
7 STANDBY /u01/app/oracle/oradata/stby10g/stbyredo04.log NO

7 rows selected.

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
4 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
5 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
6 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0
7 UNASSIGNED 0 0 52428800 512 YES UNASSIGNED 0 0


4.7 Start managed recovery and Real-time apply on the standby database
SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


5. Configure Primary Database for Dataguard
5.1 Modify initialization parameters
SQL>
SQL> alter system set log_archive_config='dg_config=(stby10g,prim10g)' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='service=stby10g valid_for=online_logfiles,primary_role)
  2  db_unique_name=stby10g' scope=both;

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/stby10g/','/u01/app/oracle/oradata/prim10g/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/stby10g/','/u01/app/oracle/oradata/prim10g/' scope=spfile;

System altered.

SQL> alter system set standby_file_management=auto scope=both;

System altered.

SQL> alter system set fal_server='stby10g' scope=both;

System altered.

SQL> alter system set fal_client='prim10g' scope=both;

System altered.

SQL> alter system set service_names=prim10g scope=both;

System altered.


5.2 Add Standby redo logs
SQL> alter database add standby logfile group 4
2 ('/u01/app/oracle/oradata/prim10g/stbyredo01.log')
3 size 50M,
4 group 5 ('/u01/app/oracle/oradata/prim10g/stbyredo02.log')
5 size 50M,
6 group 6 ('/u01/app/oracle/oradata/prim10g/stbyredo03.log')
7 size 50M,
8 group 7 ('/u01/app/oracle/oradata/prim10g/stbyredo04.log');




6. Verify Data Guard Configuration
6.1 Query v$archived_log view to identify existing files in the archived redo log


SQL> select sequence#, first_time, next_time
2 from v$archived_log
3 order by sequence#;


6.2 Force a redo log switch on the Primary database
SQL> alter system switch logfile;


6.3 Re-query v$archived_log view on the Physical Standby database to verify if the redo data was received and archived on the standby database
SQL> select sequence#, first_time, next_time, applied

2 from v$archived_log
3 order by sequence#;

Note: you must see that archived logs are being applied on the physical standby, this indicates a successful dataguard configuration.


Tips:

 Checking database alert log helps a lot when configuring and troubleshooting Oracle Data Guard.  Below are the errors I found in my alert logs, and how I resolved them:

Error: ORA-12541: TNS:no listener

Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: /u01/app/oracle/oradata/prim10g/redo02.log
Wed Sep  2 15:02:06 2009
Error 12541 received logging on to the standby
Check whether the listener is up and running.
Wed Sep  2 15:02:06 2009
Errors in file /u01/app/oracle/admin/prim10g/bdump/prim10g_arc0_7262.trc:
ORA-12541: TNS:no listener

Fix:

Quite easy indeed, just start the listener on the physical standby database

Error: ORA-01031: insufficient privileges

Errors in file /u01/app/oracle/admin/prim10g/bdump/prim10g_arc0_7262.trc:
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'stby10g'. Error is 1031.
Wed Sep  2 15:18:24 2009

 
Fix:

SYS password on the Physical Standby Database might be different from the Primary database.   Alter SYS password by recreating the password file.  (note: "alter user command cannot be used since the database is in mount mode")