sun-oracle-abhi: 2010

For all the DBA’s of MoonUp Infotech and All the DBA’s all along the world.
I named my Primary database as "primary" and its host as "abhi1", while my Physical Standby database as "standby" and its host as "abhi2".

Setting Up Oracle Data Guard (Physical Standby Database)

1. Turn on archiving on the Primary database

[root@abhi1]# su - oracle
[oracle@abhi1]$ export ORACLE_SID=primary
[oracle@abhi1]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 21 14:20:03 2010

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>startup mount
ORACLE instance started.

Total System Global Area 251658240 bytes
Fixed Size 1289964 bytes
Variable Size 125829396 bytes
Database Buffers 117440512 bytes
Redo Buffers 7098368 bytes

Database mounted.

SQL> alter database archivelog;

System altered.

SQL> alter database open;

System 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@abhi1 ~]$ mkdir -p /u0/stage
[oracle@abhi1 ~]$

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

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

[oracle@abhi1 ~]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Dec 21 14:54:14 2010

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

connected to target database: PRIMARY (DBID=3316445735)

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

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

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

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

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

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

PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = abhi1.seer-technologies.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = abhi2.seer-technologies.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)

[oracle@abhi2 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@abhi2 admin]$ cd $ORACLE_HOME/dbs
[oracle@abhi2 dbs]$ ls
hc_primary.dat initdw.ora init.ora isnapcf_primary.f
[oracle@abhi2 dbs]$
[oracle@abhi2 dbs]$ orapwd file=orapwStandby password=oracle

4.2 Copy and edit init.ora for standby
[oracle@abhi2 ~]$ cd /u0/stage/
[oracle@abhi2 stage]$ cp initprimary.ora $ORACLE_HOME/dbs/initstandby.ora

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

standby.__db_cache_size=171966464
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=96468992
standby.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/standby/adump'
*.background_dump_dest='/opt/oracle/admin/standby/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/oracle/oradata/standby/control01.ctl','/opt/oracle/oradata/standby/control02.ctl','/opt/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='primary'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u0/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='/opt/oracle/admin/standby/udump'

db_unique_name='standby'
instance_name='standby'

log_archive_config='dg_config=(standby,primary)'
log_archive_dest_2='service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary'
db_file_name_convert='/opt/oracle/oradata/primary/','/opt/oracle/oradata/standby/'
log_file_name_convert='/opt/oracle/oradata/primary/','/opt/oracle/oradata/standby/'
standby_file_management=auto
fal_server='primary'
fal_client='standby'
service_names='standby'

4.3 Create required directories
[oracle@abhi2 dbs]$ mkdir -p /opt/oracle/oradata/standby/
[oracle@abhi2 dbs]$ mkdir -p /opt/oracle/admin/standby/adump
[oracle@abhi2 dbs]$ mkdir -p /opt/oracle/admin/standby/bdump
[oracle@abhi2 dbs]$ mkdir -p /opt/oracle/admin/standby/cdump
[oracle@abhi2 dbs]$ mkdir -p /opt/oracle/admin/standby/udump
[oracle@abhi2 dbs]$

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

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 21 19:40:56 2010

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@abhi2 dbs]$ rman target sys/oracle@primary auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Dec 21 19:47:50 2010

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

connected to target database: PRIMARY (DBID=3316445735)
connected to auxiliary database: STANDBY (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/2010 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/2010 19:49:33
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01103: database name 'PRIMARY' in control file is not 'STANDBY'

To fix this error, I changed the db_name parameter of the standby database from "standby" to "primary". 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 ('/opt/oracle/oradata/standby/standbyredo01.log') size 50M,
3 group 5 ('/opt/oracle/oradata/standby/standbyredo02.log') size 50M,
4 group 6 ('/opt/oracle/oradata/standby/standbyredo03.log') size 50M,
5* group 7 ('/opt/oracle/oradata/standby/standbyredo04.log') size 50M
SQL> /

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /opt/oracle/oradata/standby/redo03.log NO
2 ONLINE /opt/oracle/oradata/standby/redo02.log NO
1 ONLINE /opt/oracle/oradata/standby/redo01.log NO
4 STANDBY /opt/oracle/oradata/standby/standbyredo01.log NO
5 STANDBY /opt/oracle/oradata/standby/standbyredo02.log NO
6 STANDBY /opt/oracle/oradata/standby/standbyredo03.log NO
7 STANDBY /opt/oracle/oradata/standby/standbyredo04.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=(standby,primary)' scope=both;

System altered.

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

System altered.

SQL> alter system set db_file_name_convert='/opt/oracle/oradata/standby/','/opt/oracle/oradata/primary/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/opt/oracle/oradata/standby/','/opt/oracle/oradata/primary/' scope=spfile;

System altered.

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

System altered.

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

System altered.

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

System altered.

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

System altered.

5.2 Add Standby redo logs
SQL> alter database add standby logfile group 4
2 ('/opt/oracle/oradata/primary/standbyredo01.log')
3 size 50M,
4 group 5 ('/opt/oracle/oradata/primary/standbyredo02.log')
5 size 50M,
6 group 6 ('/opt/oracle/oradata/primary/standbyredo03.log')
7 size 50M,
8 group 7 ('/opt/oracle/oradata/primary/standbyredo04.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.

sun-oracle-abhi

Tuesday, December 21, 2010

How to Configure DataGuard On Oracle10g

Monday, February 22, 2010

How to configure BSNL broadband on Solaris 10 x86 platform

Wednesday, February 10, 2010

Installation of Solaris 10 on x86 Platform

ORACLE 10G Installation on Solaris 10 x86 platform

Followers

Blog Archive

About Me