Create Oracle 10gR2 standby database using Real Time Apply ## ## Configuration Details ## PRIMARY site ------------- hostname=zephir ORACLE_SID=10GR2 DB_UNIQUE_NAME=leftcube ------------- $HOME/.profile ---------------- export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 export ORACLE_SID=10GR2 export ORACLE_HOSTNAME=zephir.domain.com umask 022 ##export DISPLAY="zephir:0.0" export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin PS1="`/usr/ucb/hostname`.$ORACLE_SID-> " set -o vi set history=50 ################################################################### # Oracle Alias' For Trace Files and Alert Files ################################################################### alias -x pfile="cd /u01/app/oracle/admin/10GR2/pfile" alias -x bdump="cd /u01/app/oracle/admin/10GR2/bdump" alias -x cdump="cd /u01/app/oracle/admin/10GR2/cdump" alias -x udump="cd /u01/app/oracle/admin/10GR2/udump" ################################################################### STANDBY site ------------- hostname=atlas ORACLE_SID=10GR2 DB_UNIQUE_NAME=rightcube ------------- $HOME/.profile ---------------- export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 export ORACLE_SID=10GR2 export ORACLE_HOSTNAME=atlas.domain.com umask 022 ##export DISPLAY="atlas:0.0" export PATH=$PATH:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin PS1="`/usr/ucb/hostname`.$ORACLE_SID-> " set -o vi set history=50 ################################################################### # Oracle Alias' For Trace Files and Alert Files ################################################################### alias -x pfile="cd /u01/app/oracle/admin/10GR2/pfile" alias -x bdump="cd /u01/app/oracle/admin/10GR2/bdump" alias -x cdump="cd /u01/app/oracle/admin/10GR2/cdump" alias -x udump="cd /u01/app/oracle/admin/10GR2/udump" ################################################################### ## ## CONFIGURE PRIMARY DATABASE ## ## Create a Password File (or verify you have one) ## ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID -rw-r----- 1 oracle dba 1536 Oct 1 15:00 /u01/app/oracle/product/10.2.0/db_1/dbs/orapw10GR2 ## Enable Forced Logging ## alter database force logging; ## Configure Standby Redo Logs ## ## NOTE: ## A standby redo log is required for the maximum protection and maximum ## availability modes and the LGWR ASYNC transport mode is recommended for ## all databases. Data Guard can recover and apply more redo data from a ## standby redo log than from archived redo log files alone. ## ## The size of the current standby redo log files must exactly match the ## size of the current primary database online redo log files. ## ## Minimally, the configuration should have one more standby redo log file ## group than the number of online redo log file groups on the primary ## database. ## ## the recommended number of standby redo log file groups is: ## ## (maximum number of logfiles for each thread + 1) * maximum number of threads ## ## For example, if the primary database has 2 log files for each thread and ## 2 threads, then 6 standby redo log file groups are needed on the standby ## database. ## ## Although the standby redo log is only used when the database is running ## in the standby role, Oracle recommends that you create a standby redo ## log on the primary database so that the primary database can switch over ## quickly to the standby role without the need for additional DBA ## intervention. SQL> col member format a35 SQL> set lines 132 SQL> set trims on SQL> SQL> r 1* select group#,type,member from v$logfile GROUP# TYPE MEMBER ---------- ------- ----------------------------------- 1 ONLINE /u01/oradata/10GR2/redo01.log 1 ONLINE /u02/oradata/10GR2/redo01.log 1 ONLINE /u03/oradata/10GR2/redo01.log 2 ONLINE /u01/oradata/10GR2/redo02.log 2 ONLINE /u02/oradata/10GR2/redo02.log 2 ONLINE /u03/oradata/10GR2/redo02.log 3 ONLINE /u01/oradata/10GR2/redo03.log 3 ONLINE /u02/oradata/10GR2/redo03.log 3 ONLINE /u03/oradata/10GR2/redo03.log 9 rows selected. SQL> select group#,THREAD#,BYTES,status from v$log; GROUP# THREAD# BYTES STATUS ---------- ---------- ---------- ---------------- 1 1 52428800 INACTIVE 2 1 52428800 INACTIVE 3 1 52428800 CURRENT ## based on the above information create 3+1 = 4 new standby groups ## each with 3 member - you could do 2 members or just 1 it's up to you ## the main thing is that there needs to be 4 standby groups, multiple ## members simply add redundancy to the setup they do not increase ## performance. ## ## Note that I name each group member the same this underscores that ## these files are multiplexed and are identical ## alter database add standby logfile group 4 ('/u01/oradata/10GR2/stby04.log', '/u02/oradata/10GR2/stby04.log', '/u03/oradata/10GR2/stby04.log') size 52428800; alter database add standby logfile group 5 ('/u01/oradata/10GR2/stby05.log', '/u02/oradata/10GR2/stby05.log', '/u03/oradata/10GR2/stby05.log') size 52428800; alter database add standby logfile group 6 ('/u01/oradata/10GR2/stby06.log', '/u02/oradata/10GR2/stby06.log', '/u03/oradata/10GR2/stby06.log') size 52428800; alter database add standby logfile group 7 ('/u01/oradata/10GR2/stby07.log', '/u02/oradata/10GR2/stby07.log', '/u03/oradata/10GR2/stby07.log') size 52428800; ## Verify the standby redo log file groups were created ## SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ----------------------------------- 1 ONLINE /u01/oradata/10GR2/redo01.log 1 ONLINE /u02/oradata/10GR2/redo01.log 1 ONLINE /u03/oradata/10GR2/redo01.log 2 ONLINE /u01/oradata/10GR2/redo02.log 2 ONLINE /u02/oradata/10GR2/redo02.log 2 ONLINE /u03/oradata/10GR2/redo02.log 3 ONLINE /u01/oradata/10GR2/redo03.log 3 ONLINE /u02/oradata/10GR2/redo03.log 3 ONLINE /u03/oradata/10GR2/redo03.log 4 STANDBY /u01/oradata/10GR2/stby04.log 4 STANDBY /u02/oradata/10GR2/stby04.log GROUP# TYPE MEMBER ---------- ------- ----------------------------------- 4 STANDBY /u03/oradata/10GR2/stby04.log 5 STANDBY /u01/oradata/10GR2/stby05.log 5 STANDBY /u02/oradata/10GR2/stby05.log 5 STANDBY /u03/oradata/10GR2/stby05.log 6 STANDBY /u01/oradata/10GR2/stby06.log 6 STANDBY /u02/oradata/10GR2/stby06.log 6 STANDBY /u03/oradata/10GR2/stby06.log 7 STANDBY /u01/oradata/10GR2/stby07.log 7 STANDBY /u02/oradata/10GR2/stby07.log 7 STANDBY /u03/oradata/10GR2/stby07.log 21 rows selected. SQL> select group#,thread#,sequence#,archived,status from v$standby_log; GROUP# THREAD# SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ---------- 4 0 0 YES UNASSIGNED 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED SQL> ## setup archive log ## ## NOTE: ## this is not the right setup for DataGuard but it's a typical ## setup on our production systems so I am simulating it here which ## will be changed in the next step ## zephir.10GR2-> mkdir /u02/oradata/10GR2/arch zephir.10GR2-> sqlplus /nolog SQL> connect / as sysdba Connected. SQL> alter database close; Database altered. SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfile10GR2.ora SQL> alter system set log_archive_dest_1='LOCATION=/u02/oradata/10GR2/arch' scope=both; System altered. SQL> alter database archivelog; Database altered. SQL> shutdown immediate; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 327155712 bytes Fixed Size 2029880 bytes Variable Size 171968200 bytes Database Buffers 150994944 bytes Redo Buffers 2162688 bytes Database mounted. Database opened. SQL> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/oradata/10GR2/arch Oldest online log sequence 28 Next log sequence to archive 30 Current log sequence 30 SQL> SQL> !ls -l /u02/oradata/10GR2/arch total 0 SQL> alter system switch logfile; System altered. SQL> r 1* alter system switch logfile System altered. SQL> r 1* alter system switch logfile System altered. SQL> !ls -l /u02/oradata/10GR2/arch total 64826 -rw-r----- 1 oracle dba 33161216 Oct 5 14:08 1_30_634834860.dbf -rw-r----- 1 oracle dba 1024 Oct 5 14:08 1_31_634834860.dbf -rw-r----- 1 oracle dba 3584 Oct 5 14:08 1_32_634834860.dbf SQL> ## Set Primary Database Initialization Parameters ## in this case there are two boxes both are in the ## same cubicle. Since DB_UNIQUE_NAME is normally tied ## to a physical location which removes any confusion ## when database's roles are switched I am setting ## DB_UNIQUE_NAME to: ## ## leftcube AND rightcube ## ## at this moment leftcube's role is PRIMARY ## and rightcube's role is STANDBY ## ## DB_NAME on both will be 10GR2 ## ## TWO tns-aliases will be setup for each of these names ## ## Note that the example specifies the LGWR process and asynchronous (ASYNC) ## network transmission to transmit redo data on the LOG_ARCHIVE_DEST_2 ## initialization parameter. These are the recommended settings and require ## standby redo log files ## alter system set db_unique_name=leftcube scope=spfile; alter system set log_archive_config='DG_CONFIG=(leftcube,rightcube)' scope=spfile; alter system set log_archive_dest_1= 'LOCATION=/u02/oradata/10GR2/arch VALID_FOR=(all_logfiles,all_roles) DB_UNIQUE_NAME=leftcube' scope=spfile; alter system set log_archive_dest_2= 'SERVICE=rightcube LGWR ASYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=rightcube' scope=spfile; alter system set log_archive_dest_state_1=ENABLE scope=spfile; alter system set log_archive_dest_state_2=ENABLE scope=spfile; ## NOTE: ## Specifying the initialization parameters shown in Example 3–4 sets up the ## primary database to resolve gaps, converts new datafile and log file path ## names from a new primary database, and archives the incoming redo data when ## this database is in the standby role. With the initialization parameters ## for both the primary and standby roles set as described, none of the ## parameters need to change after a role transition. ## ## I am not using DB_FILE_NAME_CONVERT because I always try to keep ## directory structure exactly the same ## alter system set fal_server=rightcube scope=spfile; alter system set fal_client=leftcube scope=spfile; alter system set standby_file_management=AUTO scope=spfile; ## this one is just a DUMMY setup to avoid ORA-19527 see: ## avoid ORA-19527 set dummy log_file_name_convert ## alter system set log_file_name_convert='junk','junk' scope=spfile; shutdown immediate; startup; ## AFTER you enable these setting and until you have setup ## the actual standby database you will be receiving the following ## errors in the alert log: ## ## ORA-12154: TNS:could not resolve the connect identifier specified ## LGWR: Error 12154 creating archivelog file 'rightcube' ## LNS: Failed to archive log 2 thread 1 sequence 35 (12154) ## ## this is due to LGWR not being able to send REDO over to rightcube ## IT'S OK since log_archive_dest_2 has a flag of ASYNC which is ## implicitly makes this destination OPTIONAL ## ## take a hotbackup of primary database ## ## I used a custom backup script called hotbk.sh zephir.10GR2-> pwd /u01/app/oracle/admin/10GR2/backup zephir.10GR2-> grep "Header" hotbk.sh # $Header$ hotbk.sh 05-OCT-2007 2.1 zephir.10GR2-> grep bkdir= hotbk.sh bkdir=/copy/db_backup/10GR2/dbfs zephir.10GR2-> mkdir -p /copy/db_backup/10GR2/dbfs zephir.10GR2-> ./hotbk.sh zephir.10GR2-> Fri Oct 5 16:48:03 PDT 2007: getting datafile list ... Fri Oct 5 16:48:04 PDT 2007: building backup script ... mv: cannot access /copy/db_backup/10GR2/dbfs/recover.sh Fri Oct 5 16:48:04 PDT 2007: running backup script (logfile=/tmp/13363.hotbk.sh.log) ... zephir.10GR2-> ls -l /copy/db_backup/10GR2/dbfs/ total 1831154 -rw-r----- 1 oracle dba 14270464 Oct 5 16:48 2007_10_05.backup.ctl -rw-r--r-- 1 oracle dba 212 Oct 5 16:48 recover.sh -rw-r----- 1 oracle dba 230694912 Oct 5 16:48 sysaux01.dbf -rw-r----- 1 oracle dba 471867392 Oct 5 16:48 system01.dbf -rw-r----- 1 oracle dba 214966272 Oct 5 16:48 undotbs01.dbf -rw-r----- 1 oracle dba 5251072 Oct 5 16:48 users01.dbf zephir.10GR2-> cat /copy/db_backup/10GR2/dbfs/recover.sh cp -p ./sysaux01.dbf /u03/oradata/10GR2/sysaux01.dbf cp -p ./system01.dbf /u02/oradata/10GR2/system01.dbf cp -p ./undotbs01.dbf /u03/oradata/10GR2/undotbs01.dbf cp -p ./users01.dbf /u02/oradata/10GR2/users01.dbf zephir.10GR2-> ## Create a Control File for the Standby Database ## alter database create standby controlfile as '/copy/db_backup/10GR2/dbfs/stby_control.ctl'; alter system archive log current; ## Prepare Initialization Parameter File for the Standby Database ## ## run this on PRIMARY create pfile='/copy/db_backup/10GR2/dbfs/init10GR2.ora' from spfile; ## Set initialization parameters on the physical standby database ## cp -p /copy/db_backup/10GR2/dbfs/init10GR2.ora \ /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora vi /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora --------- MODIFY AS FOLLOWS -------- *.db_unique_name='RIGHTCUBE' *.fal_client='RIGHTCUBE' *.fal_server='LEFTCUBE' *.log_archive_dest_1='LOCATION=/u02/oradata/10GR2/arch VALID_FOR=(all_logfiles,all_roles) DB_UNIQUE_NAME=rightcube' *.log_archive_dest_2='SERVICE=leftcube LGWR ASYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=leftcube' zephir.10GR2-> diff /copy/db_backup/10GR2/dbfs/init10GR2.ora \ > /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora 15c15 < *.db_unique_name='LEFTCUBE' --- > *.db_unique_name='RIGHTCUBE' 17,18c17,18 < *.fal_client='LEFTCUBE' < *.fal_server='RIGHTCUBE' --- > *.fal_client='RIGHTCUBE' > *.fal_server='LEFTCUBE' 23,25d22 < DB_UNIQUE_NAME=leftcube' < *.log_archive_dest_2='SERVICE=rightcube LGWR ASYNC < VALID_FOR=(online_logfiles,primary_role) 26a24,26 > *.log_archive_dest_2='SERVICE=leftcube LGWR ASYNC > VALID_FOR=(online_logfiles,primary_role) > DB_UNIQUE_NAME=leftcube' ## Copy Files from the Primary System to the Standby System ## I used rdist you can also use FTP or any other method ## ## on standby verify primary host is added to .rhosts atlas.10GR2-> grep zephir $HOME/.rhosts zephir.domain.com oracle ## rdist all files over to standby host zephir.10GR2-> rsh -l oracle -n atlas "mkdir -p /copy/db_backup/10GR2/dbfs" zephir.10GR2-> rdist -c /copy/db_backup/10GR2/dbfs/* oracle@atlas:/copy/db_backup/10GR2/dbfs/ updating host atlas installing: /copy/db_backup/10GR2/dbfs/2007_10_05.backup.ctl installing: /copy/db_backup/10GR2/dbfs/init10GR2.ora installing: /copy/db_backup/10GR2/dbfs/init10GR2_stby.ora installing: /copy/db_backup/10GR2/dbfs/recover.sh installing: /copy/db_backup/10GR2/dbfs/stby_control.ctl installing: /copy/db_backup/10GR2/dbfs/sysaux01.dbf installing: /copy/db_backup/10GR2/dbfs/system01.dbf installing: /copy/db_backup/10GR2/dbfs/undotbs01.dbf installing: /copy/db_backup/10GR2/dbfs/users01.dbf zephir.10GR2-> ## rdist archive logs zephir.10GR2-> rsh -l oracle -n atlas "mkdir -p /u02/oradata/10GR2/arch" zephir.10GR2-> rdist -c /u02/oradata/10GR2/arch/* oracle@atlas:/u02/oradata/10GR2/arch/ updating host atlas installing: /u02/oradata/10GR2/arch/1_30_634834860.dbf installing: /u02/oradata/10GR2/arch/1_31_634834860.dbf installing: /u02/oradata/10GR2/arch/1_32_634834860.dbf installing: /u02/oradata/10GR2/arch/1_33_634834860.dbf installing: /u02/oradata/10GR2/arch/1_34_634834860.dbf installing: /u02/oradata/10GR2/arch/1_35_634834860.dbf installing: /u02/oradata/10GR2/arch/1_36_634834860.dbf ## copy password file zephir.10GR2-> cd /u01/app/oracle/product/10.2.0/db_1/dbs/ zephir.10GR2-> rdist -c orapw10GR2 oracle@atlas:/u01/app/oracle/product/10.2.0/db_1/dbs/orapw10GR2 updating host atlas installing: orapw10GR2 ## Configure listeners for the primary and standby databases. ## ## primary (leftcube) zephir.10GR2-> cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = 10GR2.DOMAIN.COM) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = 10GR2) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521)) ) zephir.10GR2-> cat tnsnames.ora rightcube = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = 10GR2) ) ) 10GR2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = 10GR2.domain.com) ) ) ## standby (rightcube) atlas.10GR2-> cat listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = 10GR2.DOMAIN.COM) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (SID_NAME = 10GR2) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521)) ) atlas.10GR2-> cat tnsnames.ora leftcube = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = 10GR2) ) ) 10GR2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = 10GR2.domain.com) ) ) ## startup listener services on both standby and primary zephir.10GR2-> lsnrctl start atlas.10GR2-> lsnrctl start # test TNS aliases zephir.10GR2-> tnsping rightcube TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 05-OCT-2007 17:44:43 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = atlas.domain.com)(PORT = 1521))) (CONNECT_DATA = (SID = 10GR2))) OK (0 msec) atlas.10GR2-> tnsping leftcube TNS Ping Utility for Solaris: Version 10.2.0.3.0 - Production on 05-OCT-2007 17:45:09 Copyright (c) 1997, 2006, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = zephir.domain.com)(PORT = 1521))) (CONNECT_DATA = (SID = 10GR2))) OK (0 msec) ## ## Create PHYSICAL standby ## ## Create server parameter file on the standby database. ## atlas.10GR2-> ls -l /copy/db_backup/10GR2/dbfs/ total 1859050 -rw-r----- 1 oracle dba 14270464 Oct 5 16:48 2007_10_05.backup.ctl -rw-r--r-- 1 oracle dba 1391 Oct 5 16:56 init10GR2.ora -rw-r--r-- 1 oracle dba 1391 Oct 5 17:06 init10GR2_stby.ora -rw-r--r-- 1 oracle dba 212 Oct 5 16:48 recover.sh -rw-r----- 1 oracle dba 14270464 Oct 5 16:55 stby_control.ctl -rw-r----- 1 oracle dba 230694912 Oct 5 16:48 sysaux01.dbf -rw-r----- 1 oracle dba 471867392 Oct 5 16:48 system01.dbf -rw-r----- 1 oracle dba 214966272 Oct 5 16:48 undotbs01.dbf -rw-r----- 1 oracle dba 5251072 Oct 5 16:48 users01.dbf atlas.10GR2-> atlas.10GR2-> sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production on Fri Oct 5 17:46:49 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> SQL> SQL> !ls -l /u01/app/oracle/product/10.2.0/db_1/dbs total 48 -rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora -rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora -rw-r----- 1 oracle dba 1536 Oct 1 15:00 orapw10GR2 SQL> create spfile from pfile='/copy/db_backup/10GR2/dbfs/init10GR2_stby.ora'; File created. SQL> !ls -l /u01/app/oracle/product/10.2.0/db_1/dbs total 56 -rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora -rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora -rw-r----- 1 oracle dba 1536 Oct 1 15:00 orapw10GR2 -rw-r----- 1 oracle dba 3584 Oct 5 17:48 spfile10GR2.ora ## copy standby controlfile to it's proper location ## SQL> !grep control /u01/app/oracle/product/10.2.0/db_1/dbs/spfile10GR2.ora *.control_files='/u01/oradata/10GR2/control01.ctl', '/u02/oradata/10GR2/control02.ctl','/u03/oradata/10GR2/control03.ctl' SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u01/oradata/10GR2/control01.ctl SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u02/oradata/10GR2/control02.ctl SQL> !cp -p /copy/db_backup/10GR2/dbfs/stby_control.ctl /u03/oradata/10GR2/control03.ctl ## copy all datafiles to their locations ## SQL> !cat /copy/db_backup/10GR2/dbfs/recover.sh cp -p ./sysaux01.dbf /u03/oradata/10GR2/sysaux01.dbf cp -p ./system01.dbf /u02/oradata/10GR2/system01.dbf cp -p ./undotbs01.dbf /u03/oradata/10GR2/undotbs01.dbf cp -p ./users01.dbf /u02/oradata/10GR2/users01.dbf SQL> !cd /copy/db_backup/10GR2/dbfs/; sh ./recover.sh SQL> !find /*/oradata/10GR2 -type f -ls 95290 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u01/oradata/10GR2/control01.ctl 5829 32408 -rw-r----- 1 oracle dba 33161216 Oct 5 14:08 /u02/oradata/10GR2/arch/1_30_634834860.dbf 5830 1 -rw-r----- 1 oracle dba 1024 Oct 5 14:08 /u02/oradata/10GR2/arch/1_31_634834860.dbf 5831 4 -rw-r----- 1 oracle dba 3584 Oct 5 14:08 /u02/oradata/10GR2/arch/1_32_634834860.dbf 5832 192 -rw-r----- 1 oracle dba 187904 Oct 5 14:51 /u02/oradata/10GR2/arch/1_33_634834860.dbf 5833 144 -rw-r----- 1 oracle dba 134656 Oct 5 15:01 /u02/oradata/10GR2/arch/1_34_634834860.dbf 5834 1648 -rw-r----- 1 oracle dba 1678336 Oct 5 16:48 /u02/oradata/10GR2/arch/1_35_634834860.dbf 5835 12 -rw-r----- 1 oracle dba 11776 Oct 5 16:55 /u02/oradata/10GR2/arch/1_36_634834860.dbf 5836 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u02/oradata/10GR2/control02.ctl 5837 461048 -rw-r----- 1 oracle dba 471867392 Oct 5 16:48 /u02/oradata/10GR2/system01.dbf 5838 5136 -rw-r----- 1 oracle dba 5251072 Oct 5 16:48 /u02/oradata/10GR2/users01.dbf 5905 13944 -rw-r----- 1 oracle dba 14270464 Oct 5 18:01 /u03/oradata/10GR2/control03.ctl 5906 225408 -rw-r----- 1 oracle dba 230694912 Oct 5 16:48 /u03/oradata/10GR2/sysaux01.dbf 5907 210040 -rw-r----- 1 oracle dba 214966272 Oct 5 16:48 /u03/oradata/10GR2/undotbs01.dbf ## Start the physical standby database. ## SQL> startup mount; ORACLE instance started. Total System Global Area 327155712 bytes Fixed Size 2029880 bytes Variable Size 192939720 bytes Database Buffers 130023424 bytes Redo Buffers 2162688 bytes Database mounted. SQL> ## now create standby redo files (pre-req to enable "Real Time Apply") ## ## NOTE: ## 1) this should match exactly what was done on primary database (see ## above "Configure Standby Redo Logs") ## 2) this is optional in fact the first time around I forgot to create ## these logs on standby and recovery process was still working ## it just reports the following error: ## RFS[1]: Unable to open standby log 5: 313 ## and uses ARCHIVE logs instead ## alter database add standby logfile group 4 ('/u01/oradata/10GR2/stby04.log', '/u02/oradata/10GR2/stby04.log', '/u03/oradata/10GR2/stby04.log') size 52428800; alter database add standby logfile group 5 ('/u01/oradata/10GR2/stby05.log', '/u02/oradata/10GR2/stby05.log', '/u03/oradata/10GR2/stby05.log') size 52428800; alter database add standby logfile group 6 ('/u01/oradata/10GR2/stby06.log', '/u02/oradata/10GR2/stby06.log', '/u03/oradata/10GR2/stby06.log') size 52428800; alter database add standby logfile group 7 ('/u01/oradata/10GR2/stby07.log', '/u02/oradata/10GR2/stby07.log', '/u03/oradata/10GR2/stby07.log') size 52428800; ## Start Redo Apply ## ## NOTE: ## The statement includes the DISCONNECT FROM SESSION option so that Redo Apply ## runs in a background session. See Section 6.3, "Applying Redo Data to Physical ## Standby Databases" for more information. ## ## OPTION A is recommended ## ## OPTION A) [enables "Real Time Apply"] ## SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered. ## OPTION B) ["Real Time Apply" is disabled] SQL> alter database recover managed standby database disconnect from session; Database altered. ## on primary verify ARCHIVE seqs ## SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS'; Session altered. SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#; 2 SEQUENCE# FIRST_TIME NEXT_TIME ---------- -------------------- -------------------- 30 2007-OCT-03 19:35:08 2007-OCT-05 14:08:29 31 2007-OCT-05 14:08:29 2007-OCT-05 14:08:31 32 2007-OCT-05 14:08:31 2007-OCT-05 14:08:37 33 2007-OCT-05 14:08:37 2007-OCT-05 14:51:48 34 2007-OCT-05 14:51:48 2007-OCT-05 15:01:54 35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24 35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24 36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46 36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46 37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07 37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07 SEQUENCE# FIRST_TIME NEXT_TIME ---------- -------------------- -------------------- 38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42 38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42 13 rows selected. SQL> ## compare them to standby ## SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS'; Session altered. SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#; 2 SEQUENCE# FIRST_TIME NEXT_TIME ---------- -------------------- -------------------- 35 2007-OCT-05 15:01:54 2007-OCT-05 16:48:24 36 2007-OCT-05 16:48:24 2007-OCT-05 16:55:46 37 2007-OCT-05 16:55:46 2007-OCT-05 18:09:07 38 2007-OCT-05 18:09:07 2007-OCT-05 18:11:42 SQL> ## Verify new archived redo log files were applied ## SQL> select sequence#,applied from v$archived_log order by sequence#; SEQUENCE# APP ---------- --- 35 YES 36 YES 37 YES 38 YES SQL> SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u02/oradata/10GR2/arch Oldest online log sequence 34 Next log sequence to archive 0 Current log sequence 39 SQL> !ls -lta /u02/oradata/10GR2/arch total 71926 -rw-r----- 1 oracle dba 52429312 Oct 5 18:17 1_39_634834860.dbf -rw-r----- 1 oracle dba 1379328 Oct 5 18:11 1_37_634834860.dbf drwxr-xr-x 2 oracle dba 512 Oct 5 18:11 . -rw-r----- 1 oracle dba 11776 Oct 5 18:11 1_36_634834860.dbf -rw-r----- 1 oracle dba 1678336 Oct 5 18:11 1_35_634834860.dbf -rw-r----- 1 oracle dba 113152 Oct 5 18:11 1_38_634834860.dbf drwxr-xr-x 3 oracle dba 512 Oct 5 18:03 .. -rw-r----- 1 oracle dba 134656 Oct 5 15:01 1_34_634834860.dbf -rw-r----- 1 oracle dba 187904 Oct 5 14:51 1_33_634834860.dbf -rw-r----- 1 oracle dba 3584 Oct 5 14:08 1_32_634834860.dbf -rw-r----- 1 oracle dba 33161216 Oct 5 14:08 1_30_634834860.dbf -rw-r----- 1 oracle dba 1024 Oct 5 14:08 1_31_634834860.dbf SQL> ## ## Here's how Real Time Apply works ## ## Test 1) - switch a log on PRIMARY ## here's what you'll see in the primary alert log: LNS1 started with pid=18, OS id=13637 Fri Oct 5 18:48:38 2007 Thread 1 advanced to log sequence 41 Current log# 2 seq# 41 mem# 0: /u01/oradata/10GR2/redo02.log Current log# 2 seq# 41 mem# 1: /u02/oradata/10GR2/redo02.log Current log# 2 seq# 41 mem# 2: /u03/oradata/10GR2/redo02.log Fri Oct 5 18:48:39 2007 ARC0: Standby redo logfile selected for thread 1 sequence 40 for destination LOG_ARCHIVE_DEST_2 Fri Oct 5 18:48:39 2007 LNS: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2 here's what's reported in the STANDBY alert log: Fri Oct 5 18:48:38 2007 RFS[1]: Successfully opened standby log 4: '/u01/oradata/10GR2/stby04.log' Fri Oct 5 18:48:39 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[2]: Assigned to RFS process 3909 RFS[2]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Successfully opened standby log 4: '/u01/oradata/10GR2/stby04.log' Fri Oct 5 18:48:40 2007 Media Recovery Log /u02/oradata/10GR2/arch/1_40_634834860.dbf Media Recovery Waiting for thread 1 sequence 41 (in transit) Fri Oct 5 18:48:40 2007 Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0 Mem# 0: /u01/oradata/10GR2/stby04.log Mem# 1: /u02/oradata/10GR2/stby04.log Mem# 2: /u03/oradata/10GR2/stby04.log ## Test 2) - WITHOUT SWITCHING A LOG ## ## On STANDBY run the following SQL: SQL> select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby; PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 40 1 16 0 ARCH CLOSING 41 1 1611 0 MRP0 APPLYING_LOG 42 42 102400 0 RFS IDLE 0 0 0 0 RFS IDLE 42 45 1 0 SQL> ## On PRIMARY create a test table SQL> create table test(t number); Table created. SQL> insert into test values (1); 1 row created. SQL> insert into test values (2); 1 row created. SQL> insert into test values (3); 1 row created. SQL> commit; Commit complete. SQL> *** DO NOT SWITCH A LOG *** ## On STANDBY run the same SQL (note BLOCK# increasing): SQL> r 1* select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 40 1 16 0 ARCH CLOSING 41 1 1611 0 MRP0 APPLYING_LOG 42 76 102400 0 RFS IDLE 0 0 0 0 RFS IDLE 42 78 1 0 SQL> ## Cancel out of real time apply and open it in read only mode SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open read only; Database altered. SQL> select * from test; T ---------- 1 2 3 ## Place the standby back in managed recovery mode ## This will take the standby directly from read only mode ## and place it in managed recovery mode ## SQL> alter database recover managed standby database using current logfile disconnect; Database altered. ---------------------------------------------------
source:-http://kb.dbatoolz.com/ex/pwpkg.dp?p_key=11&p_what=detail&p_sr_id=2693&p_sc_id=19&p_debug=&p_search=&p_suser=&p_sdate=#top
Wednesday, May 18, 2011
Create Oracle 10gR2 standby database using Real Time Apply
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment