Search This Blog

Friday, August 5, 2011

Physical Standby Database with 10GR2

OS : WIN 2003
ORACLE: 10.2.0.1.0
PRIMARY DB: TAJ
STANDBY : TAJDG
PRIMARY TNS: TAJ
STANDBY TNS: TAJDG

Step by Step with LIVE example:
1. Prepare the primary database for standby
1.1 Enable force logging
1.2 create password file (if doesn't exist)
1.3 configure standby redo log file
1.4 set init parameter file
1.5 enable archive log mode

2. Preparation for standby database
2.1 Create a backup copy of the primary database datafiles
2.2 create control file for the standby database
2.3 prepare init file for the standby database
2.3.1 create init file from primary database
2.3.2 edit init file as per standby database

3. setup environment to support to standby database
3.1 set db services via ORADIM utility
3.2 create password file via ORAPWD utility
3.3 configure listener ( stop and start) via LSNRCTL utlity
3.4 create TNS entry in $ORACLE_HOME/network/admin/TNSNAMES.ORA file
3.5 create spfile from edited pfile of standby database


4. Start and verify physical standby database
4.1 start the physical standby database
4.2 start redo apply
4.3 verify the physical standby database is performing properly

5. How to start and stop redo apply on standby database
5.1 to stop redo apply on standby execute below command
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
5.2 to start redo apply on standby execute below command
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 5 16:14:19 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> --enable force logging
SQL> alter database force logging;
Database altered.
SQL> --create password file if doesn't exit in the $ORACLE_HOME/database/PWD{sid}.ora
SQL> --configure standby redolog file
SQL> --setup init file parameter
SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
db_name                              string      taj

SQL> alter system set db_unique_name=taj scope=spfile;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(taj,tajdg)' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=e:\arch\taj
  2  valid_for=(all_logfiles,all_roles)
  3  db_unique_name=taj' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2='SERVICE=tajdg ASYNC
  2  valid_for=(online_logfiles,primary_role)
  3  db_unique_name=tajdg' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_1=enable;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> show parameter remote_login_password
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> alter system set fal_server=tajdg scope=spfile;
System altered.
SQL> alter system set fal_client=taj scope=spfile;
System altered.
SQL> alter system set db_file_name_convert='E:\oracle\product\10.2.0\oradata\tajdg
','E:\oracle\product\10.2.0\oradata\taj' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='E:\oracle\product\10.2.0\oradata\ta
jdg','E:\oracle\product\10.2.0\oradata\taj' scope=spfile;
System altered.
SQL> alter system set standby_file_management=AUTO scope=spfile;
System altered.
SQL> --enable the archivelog mode (if database not in archivelog mode)
SQL> --if database in archive log mode then also need to restart db
SQL> --to take effect set above parameters.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            e:\arch\taj
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> alter database open;
Database altered.

SQL> --create init file from spfile for standby database
SQL> create pfile='c:\init_tajdg.ora' from spfile;
File created.
SQL> --edit below parameters in the init_tajdg.ora file
tajdg.__db_cache_size=436207616
tajdg.__java_pool_size=4194304
tajdg.__large_pool_size=4194304
tajdg.__shared_pool_size=159383552
tajdg.__streams_pool_size=0
*.audit_file_dest='E:\oracle\product\10.2.0/admin/tajdg/adump'
*.background_dump_dest='E:\oracle\product\10.2.0/admin/tajdg/bdump'
*.control_files='E:\oracle\product\10.2.0/oradata/tajdg/control01.ctl','E:\oracle\product\10.2.0/oradata/tajdg/control02.ctl','E:\oracle\product\10.2.0/oradata/tajdg/control03.ctl'
*.core_dump_dest='E:\oracle\product\10.2.0/admin/tajdg/cdump'
*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\taj','E:\oracle\product\10.2.0\oradata\tajdg'
*.db_name='taj'
*.db_unique_name='TAJDG'
*.fal_client='TAJDG'
*.fal_server='TAJ'
*.log_archive_config='DG_CONFIG=(taj,tajdg)'
*.log_archive_dest_1='LOCATION=e:\arch\tajDG
valid_for=(all_logfiles,all_roles)
db_unique_name=tajDG'
*.log_archive_dest_2='SERVICE=taj ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=taj'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\taj','E:\oracle\product\10.2.0\oradata\tajdg'
*.remote_login_passwordfile='EXCLUSIVE'
*.user_dump_dest='E:\oracle\product\10.2.0/admin/tajdg/udump'

C:\>rem setup environment to support standby database
C:\>rem create standby database service via ORADIM utility
C:\>oradim -new -sid tajdg -startmode manual
Instance created.
C:\>rem create password file for standby db via ORAPWD utility
C:\>orapwd file=E:\oracle\product\10.2.0\db_2\database\pwdtajdg.ora password=oracle entries=5
C:\>rem configure listener
C:\>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-AUG-2011 16:47
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
C:\>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 05-AUG-2011 16:47
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is E:\oracle\product\10.2.0\db_2\network\admin\listener.ora
Log messages written to E:\oracle\product\10.2.0\db_2\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakorion.fakhruddin.loca
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
Start Date                05-AUG-2011 16:47:43
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\product\10.2.0\db_2\network\admin\listener.o
Listener Log File         E:\oracle\product\10.2.0\db_2\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fakorion.fakhruddin.local)(PORT=1522
)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
C:\>rem configure TNSNAMES.ORA file in $ORACLE_HOME/network/admin folder
C:\>rem set oracle_sid for standby and connect with standby db
C:\>set oracle_sid=tajdg
C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 5 16:49:18 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> --create spfile from edited pfile for standby database
SQL> create spfile from pfile='c:\init_tajdg.ora' ;
File created.
SQL> --start and verify the physical standby database.
SQL> startup mount
ORACLE instance started.
Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             167775108 bytes
Database Buffers          436207616 bytes
Redo Buffers                7135232 bytes
Database mounted.
SQL> --start redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> --verify physical standby database performing properly
SQL> --identify the existing archived log files on standby db
SQL> select sequence#, first_time, next_time
  2  from v$archived_log order by sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 05-AUG-11 05-AUG-11
SQL> --on the primary database force archivelog switch
SQL> conn sys/oracle@taj as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> --verify on the standby database the new redo was archived
SQL> conn sys/oracle@tajdg as sysdba
Connected.
SQL> select sequence#, first_time, next_time
  2  from v$archived_log order by sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 05-AUG-11 05-AUG-11
         2 05-AUG-11 05-AUG-11
SQL> --as you can see seq# 2 is transffered ... :)
SQL> --now verify new archived log was applied on standby successfully
SQL> --on standby server
SQL> select sequence#,applied from v$archived_log
  2  order by sequence#;
 SEQUENCE# APP
---------- ---
         1 YES
         2 YES



No comments: