OS: Windows 2003
Oracle: 11GR2
Primary : PROD
Standby: PRODDG
Tns Primary: PROD
Standby Tns: PRODDG
Primary and standby on same host.
1. Preparing the primary database for standby db.
1.1 Enable force logging
1.2 copy password file from primary site to standby site
1.3 Setup init file
1.4 Enable archive log ( if database is not in archive log mode)
2.Steps for physical standby database
2.1 Create backup
2.2 Create controlfile for standby database
2.3 create init file from primary database
2.3.1 modify init paramter file as per physical standby db
2.3.2 create spfile from modified pfile for physical standby db
2.4 set up environment to support physical standby
2.4.1 create standby db service via ORADIM utility
2.4.2 copy password file from primary site to standby site
2.4.3 configure listener via LSNRCTL utlity ( stop and start)
2.4.4 configure TNSNAMES.ora file in $ORACLE_HOME/network/admin
2.4.5 create spfile file from modified pfile
3. Start and verify physical standby db
3.1 start the physical standby db
3.2 verify the physical standby database is performing properly
4. Performing a Failover to a Physical Standby Database
4.1 Identify and resolve any redo gaps
4.2 stop redo apply
4.3 Finish applying all received redo data
4.4 Verify that the target standby database is ready to become a primary database
4.5 switch the physical standby database to the primary role
4.6 open the new primary database
4.7 backup the new primary database
SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 6 12:34:14 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --enable force logging
SQL> alter database force logging;
Database altered.
SQL> --copy password file from primary to standby site
SQL> --setup init parameter file
SQL> alter system set db_unique_name=prod scope=spfile;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(prod,proddg)' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=D:\arch\prod
2 valid_for=(all_logfiles,all_roles)
3 db_unique_name=prod' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=proddg ASYNC
2 valid_for=(online_logfiles,primary_role)
3 db_unique_name=proddg' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_2=enable scope=spfile;
System altered.
SQL> --setup parameter for standby db
SQL> alter system set fal_server=proddg scope=spfile;
System altered.
SQL> alter system set fal_client=prod scope=spfile;
System altered.
SQL> alter system set db_file_name_convert='D:\app\Administrator\oradata\PRODDG'
,'D:\app\Administrator\oradata\PROD' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='D:\app\Administrator\oradata\PRODDG
','D:\app\Administrator\oradata\PROD' scope=spfile;
System altered.
SQL> alter system set standby_file_management=auto scope=spfile;
System altered.
SQL> --enable archive log mode (if database already in archive log then also
SQL> --shutdown and startup the database to take affect above parameters;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1384752 bytes
Variable Size 234884816 bytes
Database Buffers 293601280 bytes
Redo Buffers 5791744 bytes
SQL> --if database successfully nomounted it does mean the above parameters is
SQL> --correctly set.
SQL> alter database mount;
Database altered.
SQL> --enable archive log mode
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\arch\prod
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> alter database open;
Database altered.
SQL> --create backup ( i am using COLDBACK method)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> --copy all files except CONTROLFILE
SQL> --create controlfile for standby db ( startup in MOUNT mode)
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1384752 bytes
Variable Size 234884816 bytes
Database Buffers 293601280 bytes
Redo Buffers 5791744 bytes
Database mounted.
SQL> alter database create standby controlfile as 'D:\app\Administrator\oradata\
proddg\CONTROL01.CTL';
Database altered.
SQL> --just copy the above control01.ctl and create one more files becuase in
SQL> --my primary db , TWO controls exist.
SQL> alter database open;
Database altered.
SQL> --create init file for physical standby db.
SQL> create pfile='c:\init_proddg.ora' from spfile;
File created.
SQL> --edit below parameters for standby db
proddg.__db_cache_size=293601280
proddg.__java_pool_size=4194304
proddg.__large_pool_size=4194304
proddg.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
proddg.__pga_aggregate_target=293601280
proddg.__sga_target=440401920
proddg.__shared_io_pool_size=0
proddg.__shared_pool_size=130023424
proddg.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\PRODdg\adump'
*.control_files='D:\app\Administrator\oradata\PRODdg\control01.ctl','D:\app\Administrator\oradata\PRODdg\control02.ctl'
*.db_file_name_convert='D:\app\Administrator\oradata\PROD','D:\app\Administrator\oradata\PRODdg'
*.db_name='PROD'
*.db_unique_name='PRODdg'
*.diagnostic_dest='D:\app\Administrator'
*.fal_client='PRODDG'
*.fal_server='PROD'
*.log_archive_config='DG_CONFIG=(prod,proddg)'
*.log_archive_dest_1='LOCATION=D:\arch\prodDG
valid_for=(all_logfiles,all_roles)
db_unique_name=proddg'
*.log_archive_dest_2='SERVICE=prod ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=prod'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='D:\app\Administrator\oradata\PROD','D:\app\Administrator\oradata\PRODdg'
C:\>rem create oracle standby service via ORADIM utility
C:\>oradim -NEW -SID proddg -STARTMODE manual
Instance created.
C:\>rem configure LISTENER ( stop and start)
C:\>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 06-AUG-2011 13:19
:59
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
C:\>lsnrctl start
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 06-AUG-2011 13:20
:04
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Log messages written to D:\app\Administrator\diag\tnslsnr\fakproora\listener\ale
rt\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakproora.fakhruddin.loc
al)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date 06-AUG-2011 13:20:06
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File D:\app\Administrator\diag\tnslsnr\fakproora\listener\a
lert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakproora.fakhruddin.local)(PORT=152
1)))
The listener supports no services
The command completed successfully
C:\>rem configure TNSNAMES.ORA file $ORACLE_HOME/network/admin
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.23)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
PRODdg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.23)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = proddg)
)
)
C:\>rem create spfile from motified pfile of standby db
C:\>set oracle_sid=proddg
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 6 13:23:38 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='c:\init_proddg.ora';
File created.
SQL> --start the physical standby db
SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1382112 bytes
Variable Size 92277024 bytes
Database Buffers 50331648 bytes
Redo Buffers 6676480 bytes
Database mounted.
SQL> --start redo apply on standby db
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL> --verify physical standby db perfoming properly
SQL> --connect standby db and query v$archvied_log
SQL> conn sys/oracle@proddg as sysdba
Connected.
SQL> select sequence#,first_time,next_time
2 from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
5 06-AUG-11 06-AUG-11
6 06-AUG-11 06-AUG-11
7 06-AUG-11 06-AUG-11
8 06-AUG-11 06-AUG-11
9 06-AUG-11 06-AUG-11
SQL> --connect to primary and force log switch
SQL> conn sys/oracle@prod as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> --connect to standby and verify new redo data was archived
SQL> conn sys/oracle@proddg as sysdba
Connected.
SQL> select sequence#,first_time,next_time
2 from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
5 06-AUG-11 06-AUG-11
6 06-AUG-11 06-AUG-11
7 06-AUG-11 06-AUG-11
8 06-AUG-11 06-AUG-11
9 06-AUG-11 06-AUG-11
10 06-AUG-11 06-AUG-11
6 rows selected.
SQL> --verify on standby db new redo data was applied
SQL> select sequence#,applied from v$archived_log
2 order by sequence#;
SEQUENCE# APPLIED
---------- ---------
5 NO
6 NO
7 NO
8 NO
9 NO
10 NO
6 rows selected.
SQL> --redo didnot apply on standby db
SQL> --start redo apply on standby db
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL> --verify on standby db new redo data was applied
SQL> select sequence#,applied from v$archived_log
2 order by sequence#;
SEQUENCE# APPLIED
---------- ---------
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
6 rows selected.
SQL> --all the redo apply on standby db.
C:\>set oracle_sid=PRODDG
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 6 14:24:38 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --primary site got crash and performing failover on this physical standby
SQL> --identify and resolve any log gap
SQL> select thread#, low_sequence#, high_sequence#
2 from v$archive_gap;
no rows selected
SQL> --there is no log gap between primary and standby
SQL> --stop redo apply
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> --finish applying all received redo data (if any)
SQL> alter database recover managed standby database finish;
Database altered.
SQL> --if above statement complete without any error then proceed as below
SQL> --verify that the target standby database is ready to become a primary data
base
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> --A value of either TO PRIMARY or SESSION ACTIVE indicate that standby
SQL> --ready for switch.
SQL> --switch the physical standby database to primary role
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> --open database (new primary db)
SQL> alter database open;
Database altered.
SQL> --take full database backup
No comments:
Post a Comment