Multiple Dataguard Scenarios 6x2u2s

###Multiple Standby Databases Dataguard Oracle We will create 2 standby databases from a single RAC primary database. 11gR2 all ows upto 30 standby databases. The most important part is to consider the datag uard parameters, the rest is a normal dataguard creation. The important paramete rs are as below : db_file_name_convert log_archive_config log_archive_dest_1 log_archive_dest_2 log_archive_dest_3 LOG_ARCHIVE_DEST_STATE_1 LOG_ARCHIVE_DEST_STATE_2 LOG_ARCHIVE_DEST_STATE_3 log_file_name_convert remote__file STANDBY_FILE_MANAGEMENT fal_client fal_server sec_case_sensitive_logon (if on 11gR2) All database are running on Grid + ASM 11.2.0.3.4 with RAC primary and RAC stand by’s. Please note the procedure is identical to a single instance setup. In Rac th e only special thing you do is the standby database and instances to th e OCR via srvctl command. We all call our primary database SWX and our PHYSICAL STANDBY databases as SWXST ANDBY, SWXSTANDYBY2. We will use RMAN Duplicate Active Standby database feature of 11gR2. If your standby databases are in different data centesr and the bandwi dht isn’t suffcient you can use the tradionatl RMAN duplicate command. 1. Create tnsnames.ora entry on Primary database. Use RDBMS owner for the tnsnam es.ora not Grid infrastructure owner SWX = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.213)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swx1)(UR=A) ) ) SWXSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.219)(PORT = 1521)) (CONNECT_DATA =

(SERVER = DEDICATED) (SERVICE_NAME = swxstandby)(UR=A) ) ) SWXSTANDBY2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.224)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swxstandby2)(UR=A) ) 2. Add an SID entry on the listener.ora as Grid infrastructure SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=s wx)(ORACLE_HOME=/grid/app/11.2.0/grid/))) 3. Create a pfile and the below datagaurd parameters to the pfile

#########################STANDBY PARAMETERS######################## *.DB_UNIQUE_NAME=’swx’ *.db_file_name_convert=’+DATA/SWXSTANDBY2/’,’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY /’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swx’ *.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIM ARY_ROLE) db_unique_name=swxstandby’ *.log_archive_dest_3=’service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRI MARY_ROLE) db_unique_name=swxstandby2′ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.log_file_name_convert=’+DATA/SWXSTANDBY2/’,’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDB 2/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.remote__file=’exclusive’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.fal_client=’SWX’ *.fal_server=’SWXSTANDBY2′,’SWXSTANDBY’ *.sec_case_sensitive_logon=FALSE ######################################## 4. Restart the database with the new pfile and create spfile from it and bounce the database again 5. to the First DR database server as Oracle and create tnsnames.ora entry SWX = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.213)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swx)(UR=A) ) ) SWXSTANDBY =

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.218)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swxstandby)(UR=A) ) ) SWXSTANDBY2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.224)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swxstandby2)(UR=A) ) ) 6. Add SID listener entry in listener.ora file as Grid infrastructure . SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=s wx)(ORACLE_HOME=/grid/app/11.2.0/grid))) 7. Create a pfile with the dataguard parameters from the pfile created before on primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS######################## *.DB_UNIQUE_NAME=’SWXSTANDBY’ *.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FR STANDBY/’,’+FRA/SWXSTANDBY2/’ *.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swxstandby’ *.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROL E) db_unique_name=swx’ *.log_archive_dest_3=’service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRI MARY_ROLE) db_unique_name=swxstandby2′ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+F XSTANDBY/’,’+FRA/SWXSTANDBY2/’ *.remote__file=’exclusive’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.fal_client=’SWXSTANDBY’ *.fal_server=’SWX’,’SWXSTANDBY2′ *.sec_case_sensitive_logon=FALSE ######################################## 8. Start the database with the pfile and create spfile from it 9. Clone the database from RMAN using active option rman target sys/***@swx auxiliary sys/***@swxstandby RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

10. the database and instance with srvctl command srvctl add database -d SWXSTANDBY -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -a DATA,FRA srvctl add instance -d SWXSTANDBY -i swx1 -n csftest-2 srvctl stop database -d SWXSTANDBY sqlplus “/as sysdba” srvctl start database -d swxstandby srvctl modify database -d swxstandby -s mount srvctl config database -d swxstandby ## TO ACTIVATE ACTIVE STANDBY DATABASE ### srvctl start database -d swxstandby -o open 11. to the Second DR database server as Oracle and create tnsnames.or a entry SWX = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.213)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = swx)(UR=A) ) ) SWXSTANDBY2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.15)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SWXSTANDBY2)(UR=A) ) ) SWXSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = *.*.*.218)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SWXSTANDBY)(UR=A) ) ) 12. Create a listener.ora entry as grid SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=s wx)(ORACLE_HOME=/grid/app/11.2.0/grid))) 13. Create a pfile with the dataguard parameters from the pfile created before o n primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS######################## *.DB_UNIQUE_NAME=’SWXSTANDBY2′ *.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FR STANDBY/’,’+FRA/SWXSTANDBY2/’ *.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swxstandby2′ *.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROL

E) db_unique_name=swx’ *.log_archive_dest_3=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIM ARY_ROLE) db_unique_name=swxstandby’ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+F XSTANDBY/’,’+FRA/SWXSTANDBY2/’ *.remote__file=’exclusive’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.fal_client=’SWXSTANDBY2′ *.fal_server=’SWX’,’SWXSTANDBY’ *.sec_case_sensitive_logon=FALSE ######################################## 14. Start the database with the pfile and create spfile from it 15. Clone the database from RMAN using active option rman target sys/***@swx auxiliary sys/***@swxstandby RMAN> duplicate target database for standby from active database nofilenamecheck dorecover; 16. the database and instance with srvctl command srvctl add database -d swxstandby2 -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -n swx -a DATA,FRA srvctl add instance -d swxstandby2 -i swx1 -n suncsftst Now that we have a fully functional RAC PRIMARY with 2 PHYSICAL STANDBYs now l et us consider the 2 most important things in Dataguard: Switchover and Failover ————– ###Switchover### —————No extra steps are necessary when performing a switchover. All bystander physica l standby databases automatically apply redo data received from the new primary database. ————– ###Failover### —————The steps for performing a failover to a physical standby database depend on the Redo Apply progress of the new primary database and any bystander physical standby databases at the time of the failover. • If the new primary database has applied more redo than all of the bystander physical standby databases, no additional steps are required. Only the original primary database needs to be reinstated, using the steps documented in Oracle Data Guard Concepts and istration[5], Section 12.4.1 “Flashing Back a Failed Primary Database into a Physical Standby Database.”. • If any bystander physical standby database has applied more redo than the new

primary database, then perform the following steps to reinstate the bystander physical standby SQL*Plus Physical/Physical Failover with Physical Bystander Ahead 1. Determine STANDBY_BECAME_PRIMARY_SCN from the new primary. SQL> select STANDBY_BECAME_PRIMARY_SCN from v$database; 2. On the bystander physical standby, flash back to STANDBY_BECAME_PRIMARY_SCN from the new primary database. SQL> flashback database to scn <STANDBY_BECAME_PRIMARY_SCN>; 3. On the bystander physical standby, delete divergent archived redo logs created at the time of, or after, the failover. RMAN> delete archivelog from scn <STANDBY_BECAME_PRIMARY_SCN>; 4. On the new primary database, enable the redo transport destination for this bystander physical standby and archive the current redo log. SQL> alter system set log_archive_destination_2=enable; SQL> alter system archive log current; 5. After the logs have been received by the bystander physical standby, start Redo Apply on the bystander physical standby. SQL> alter database recover managed standby database using current logfile through all switchover disconnect; The bystander standby database is now reinstated. For more information refer to this excellent Oracle document. http://www.oracle.com/technetwork/database/features/availability/maa10gr2multipl estandbybp-1-131937.pdf Interesting Case-Study on Multiple Standby Databases. Apparently Apple uses it f or one of their Large databases. http://www.oracle.com/technetwork/database/features/availability/311400-134359.p df

###RAC to Single Instance Standby Database Setup (Oracle 11gR2 Physical Standby) Primary is a 2 node-rac cluster with instances swx1 and swx2 running on ASM. A s ervice called swxscan is created for client sides. We will use this service name for the tnsnames.ora. The standby instance will be called swxstandby and it will be running single ins tance on ASM as well. This is a standard dataguard configuration but the only tricky part is the liste ners on primary and DR, since in 11gR2 we have the Grid Infrastructure running u nder Grid and RDBMS under oracle (provided you followed Oracle’s OFA, which b y the way is the best way to do things imho) Also take care for the permissions on DR server for the raw devices. Since grid owns the raw devices the group which grid is part of should also have

oracle as its member. Do we will begin the procedure now. First we will do steps on Production RAC and then do the remaining on Standby server. I am not going to use active duplicate over the network. The reason for this is that in a production environment where size of your database is genereally above 500gb. If your bandwidth is not good enough it might take days to duplicate the database over network. Its better to use the old duplicate command and copy the rman backups over using a tape or any restore method at your disposal. What I do is backup it up to tape and then res tore on DR site. ———————Environment ——————– Primary Database 2 Node RAC 1 Node Names: swxracnode1, swxracnode2 2 DB Name: SWX 3 DB Unique Name: SWX 4 DB Version: 11.2.0.3 5 Grid Infrastructure (CRS + ASM). 6 SCAN settings in /etc/hosts file, so SCAN listener only running on one node (S wxracnode2 for this demo) 7 ASM Diskgroups: +DATA, +FRA 8 CRS installed with ‘Grid’ , RDBMS installed with ‘Oracle’ Standby Database Single Instance: 1 ASM Diskgroup : +DATA, +FRA (For datafiles and FRA) 2 DB Unique Name: SWXSTANDBY 3 DB Name: SWX 4 CRS(for single instance) installed with ‘Grid’ , RDBMS installed with ‘Oracle’ use r ————————— PREPARATIONS AT PRIMARY SITE —————————GI_HOME= /grid/11.2.0/grid ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1 CRS = Grid RDBMS = Oracle 1. Force logging on primary. to any one node and run below sql command alter database force logging; 2. Check for Online Redo Logfiles(ORL) on nodes and Create Standby Redo Logs (SR L) on the Primary RAC. select * from gv$logfile; select * from gv$log; — ORL ADDITION — Add atleast four groups per thread of size 100M; ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 (‘+DATA/swx/onlinelog/redo-01-01.log’,’+DATA/swx/onlinelog/redo01-02.log’) SIZE

100m, GROUP 2 (‘+DATA/swx/onlinelog/redo-02-01.log’,’+DATA/swx/onlinelog/redo-02-02.log’) SIZE 100m; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 (‘+DATA/swx/onlinelog/redo-03-01.log’,’+DATA/swx/onlinelog/redo-03-02.log’) SIZE 100m, GROUP 4 (‘+DATA/swx/onlinelog/redo-04-01.log’,’+DATA/swx/onlinelog/redo-04-02.log’) SIZE 100m; ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (‘+DATA/swx/onlinelog/redo-05-01.log’,’+DATA/swx/onlinelog/redo05-02.log’) SIZE 100m, GROUP 6 (‘+DATA/swx/onlinelog/redo-06-01.log’,’+DATA/swx/onlinelog/redo-06-02.log’) SIZE 100m; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 (‘+DATA/swx/onlinelog/redo-07-01.log’,’+DATA/swx/onlinelog/redo-07-02.log’) SIZE 100m, GROUP 8 (‘+DATA/swx/onlinelog/redo-08-01.log’,’+DATA/swx/onlinelog/redo-08-02.log’) SIZE 100m; ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9 (‘+DATA/swx/onlinelog/redo-09-01.log’,’+DATA/swx/onlinelog/redo-09-02.log’) SIZE 100m, GROUP 10 (‘+DATA/swx/onlinelog/redo-10-01.log’,’+DATA/swx/onlinelog/redo-10-02.log’) SIZ E 100m; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 (‘+DATA/swx/onlinelog/redo-11-01.log’,’+DATA/swx/onlinelog/redo-11-02.log’) SIZ E 100m, GROUP 12 (‘+DATA/swx/onlinelog/redo-12-01.log’,’+DATA/swx/onlinelog/redo-12-02.log’) SIZ E 100m; Create Standby Redo Logs (SRL) related to the online redo log (ORL) files in the database. As there are three redo log groups with two each we will crea te four for each thread. Note that OMF (db_create_file_dest=’+DATA’) are use d and the SRL are created with the same size as ORL. (maximum # of logfiles +1) * maximum # of threads For example in setup using two online log files for each thread. Thus, the numbe r of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread. 3. Set DB_RECOVERY_FILE_DEST_SIZE, DB_RECOVERY_FILE_DEST and ADD the SRL’s SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=400G scope=both sid=’*’; SQL> alter system set DB_RECOVERY_FILE_DEST=’+FRA’ scope=both sid=’*’; alter database add standby logfile thread 1 group 7 size 104857600; alter database add standby logfile thread 1 group 8 size 104857600; alter database add standby logfile thread 1 group 9 size 104857600; alter database add standby logfile thread 1 group 10 size 104857600; alter database add standby logfile thread 2 group 11 size 104857600;

alter database add standby logfile thread 2 group 12 size 104857600; alter database add standby logfile thread 2 group 13 size 104857600; alter database add standby logfile thread 2 group 14 size 104857600; 4. Bring Database in Archivelog mode alter system set log_archive_dest_1=’location=+fra’ scope=both sid=’*’; srvctl stop database -d swx ### to both nodes sqlplus “/as sysdba” startup mount alter database archivelog; SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +FRA Oldest online log sequence 1625 Next log sequence to archive 1626 Current log sequence 1626 SQL> alter database open; 5. Configure Listener.ora (GRID ) and Tnsnames.ora (ORACLE ) for both no des on Primary Side Configure tnsnames alias and a static registration with the GI listeners for bot h primary and standby database and compliant with Data Guard Broker. With Oracle 11gR2 since the SCAN Listener was introduced the modification is made to both l isteners in GI $OH and to the tnsnames.ora in RDBMS $OH. Reload the listeners. — Listener.ora on both Nodes of Primary RAC as Grid – LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S CAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S CAN2)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S CAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SWXSTANDBY = (DESCRIPTION_LIST = (DESCRIPTION =

(ADDRESS = (PROTOCOL = T)(HOST = swxracdr)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swx) (SID_NAME = swx) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swxstandby) (SID_NAME = swxstandby) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swx_DGMGRL) (SID_NAME = swx) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swxstandby_DGMGRL) (SID_NAME = swxstandby) ) ) ## As grid in $ORACLE_HOME (which is the Grid Home) do the below on both no des.Start and Stop all scan listeners on both nodes lsnrctl stop LISTENER_SCAN1 lsnrctl stop LISTENER_SCAN2 lsnrctl stop LISTENER_SCAN3 lsnrctl start LISTENER_SCAN1 lsnrctl start LISTENER_SCAN2 lsnrctl start LISTENER_SCAN3 — TNSNAMES.ora on both Nodes of Primary RAC as Oracle — SWXSTANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = swxracdr)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swxstandby) ) ) SWX = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = swxscan)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swx) ) )

## tnsping ping swx, swxstandby as Oracle to check ### If not pingabe check if DNS is configured on your server 6. Create files for the Nodes as Oracle . format as orapwswx1 for node 1 format as orapwswx2 for node 2 Create the file in folder $ORACLE_HOME/dbs on all nodes. orapwd file=$ORACLE_HOME/dbs/orapwsswx1 ignorecase=y =xxxxxxxx force=y orapwd file=$ORACLE_HOME/dbs/orapwsswx2 ignorecase=y =xxxxxxxx force=y –ignore case is important parameter here since from 11gR2 onwards s are ca se-sensitive PING[ARC2]: Heartbeat failed to connect to standby ‘swx’. Error is 16191. Error 1017 received logging on to the standby These are common errors associated with files. If you encounter the abo ve errors, recreate the files. 7. Create initialization parameter file and add Standby database parameters to i t. We will not use Active duplication over the network. Since in a production envir onment it is sometimes impractical; where size of database can easily exceed 1TB . The method to duplicate via RMAN and copy the backup to Standby database serve r is a tried and tested method and works beautifully. to sqlplus on one of the nodes. show parameter spfile; create pfile=’/backup/swxPRIMARY.ora’ from spfile; Ensure you remote_listener parameter is set to your scan listener *.remote_listener=’swxscan.domain.com:1521′ Add the below parameters to the new swxPRIMARY.ora file #########################STANDBY PARAMETERS######################## *.DB_UNIQUE_NAME=’swx’ *.db_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.log_archive_config=’dg_config=(swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swx’ *.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIM ARY_ROLE) db_unique_name=swxstandby’ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.log_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.remote__file=’exclusive’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.fal_client=’SWX’ *.fal_server=’SWXSTANDBY’ *.sec_case_sensitive_logon=FALSE

######################################## FULL PARAMETER FILE OF THE PRIMARY DATABASE swx2.__db_cache_size=4294967296 swx1.__db_cache_size=3892314112 swx2.__java_pool_size=16777216 swx1.__java_pool_size=33554432 swx2.__large_pool_size=33554432 swx1.__large_pool_size=50331648 swx2.__pga_aggregate_target=2147483648 swx1.__pga_aggregate_target=2147483648 swx2.__sga_target=6442450944 swx1.__sga_target=6442450944 swx2.__shared_io_pool_size=536870912 swx1.__shared_io_pool_size=536870912 swx2.__shared_pool_size=1526726656 swx1.__shared_pool_size=1862270976 swx2.__streams_pool_size=0 swx1.__streams_pool_size=33554432 *.audit_file_dest=’/oracle/app/oracle//swx/adump’ *.audit_trail=’db’ *.cluster_database=true *.compatible=’11.2.0.0.0′ *.control_files=’+DATA/swx/controlfile/current.265.773009443′ *.db_block_size=8192 *.db_create_file_dest=’+DATA’ *.db_domain=” *.db_name=’swx’ *.db_recovery_file_dest_size=429496729600 *.db_recovery_file_dest=’+FRA’ *.diagnostic_dest=’/oracle/app/oracle’ *.dispatchers= (PROTOCOL=T) (SERVICE=swxXDB)’ *.swx1.instance_number=1 *.swx2.instance_number=2 *.job_queue_processes=1000 *.log_archive_config=’dg_config=(swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swx’ *.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIM ARY_ROLE) db_unique_name=swxstandby’ *.open_cursors=300 *.pga_aggregate_target=2147483648 *.processes=1000 *.remote_listener=’swxscan.qiibonline.com:1521′ *.remote__file=’exclusive’ *.sessions=1105 *.sga_target=6442450944 *.standby_file_management=’AUTO’ *.swx2.thread=2 *.swx1.thread=1 *.swx2.undo_tablespace=’UNDOTBS2′ *.swx1.undo_tablespace=’UNDOTBS1′ *.db_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.log_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’ *.standby_file_management=auto *.fal_server=’SWXSTANDBY’ *.fal_client=’SWX’ Bounce the database with the new parameter file

sqlplus “/as sysdba” shutdown immediate; — shutdown 2nd node also, you can use ‘srvctl stop database -d swx’ for this also startup pfile=’/backup/swxPRIMARY.ora’; create spfile from pfile=’/backup/swxPRIMARY.ora’; shutdown immediate; startup; show parameter spfile; Now we have completed the steps at Primary Side, only the duplicate command from RMAN is remaining. But let us first ensure we prepare the standby database befo re we duplicate and restore the database. ————————— PREPARATIONS AT STANDBY SITE —————————GI_HOME= /grid/11.2.0/grid ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1 CRS = Grid RDBMS = Oracle The installation for database is grid infrastrcuture utilizing ASM for the datab ase. We have already created the HAS services and the RDBMS software was also in stalled. In ASM 2 diskgroups called +DATA, +FRA was created, same like productio n. We will create a new database using the intialization parameter file we created before but we will add new parameters for the standby database. If your are doin g this setup on windows then you will have to create a windows service first usi ng ‘oradim’ 1. Create listener.ora with Grid LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = T)(HOST = swxracdr)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /grid/app ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SWXSTANDBY = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = swxracdr)(PORT = 1521)) )

) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swx) (SID_NAME = swx) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swxstandby) (SID_NAME = swxstandby) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swx_DGMGRL) (SID_NAME = swx) ) (SID_DESC = (ORACLE_HOME= /grid/11.2.0/grid) (GLOBAL_DBNAME = swxstandby_DGMGRL) (SID_NAME = swxstandby) ) ) Reload the listeners using lsnrctl reload command 2. Create tnsnames.ora file using Oracle SWX = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = swxscan.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swx) ) ) SWXSTANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = swxracdr.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = swxstandby) ) ) 3. Create Directory for adump audit_file_dest, also create directories in the AS M diskgroup using ASMCMD — as oracle — mkdir -p /oracle/app/oracle//swx/adump asmcmd> asmcmd> mkdir +DATA/SWXSTANDBY/ asmcmd> mkdir +DATA/SWX/

asmcmd> mkdir +FRA/SWXSTANDBY/ asmcmd> mkdir +FRA/SWX/ Also create any other directories you think you have missed in ASM or on the loc al filesystem. 4. Change intilization parameters in swxPRIMARY.ora file and name file as swxSTA NDBY.ora Since it is RAC to single instance dataguard the parameter cluster_database need s to be set to false. This is the primary difference between a single instance a nd a rac database. #########################STANDBY DATABASE PARAMETERS######################## *.DB_UNIQUE_NAME=’SWXSTANDBY’ *.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’ *.log_archive_config=’dg_config=(swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swxstandby’ *.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROL E) db_unique_name=swx’ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’ *.remote__file=’exclusive’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.fal_client=’SWXSTANDBY’ *.fal_server=’SWX’ *.cluster_database=false *.sec_case_sensitive_logon=FALSE ######################################## FULL PARAMETER FILE swxSTANDBY.ora swxstandby.__db_cache_size=3724541952 swxstandby.__java_pool_size=16777216 swxstandby.__large_pool_size=50331648 swxstandby.__pga_aggregate_target=2147483648 swxstandby.__sga_target=6442450944 swxstandby.__shared_io_pool_size=536870912 swxstandby.__shared_pool_size=2063597568 swxstandby.__streams_pool_size=16777216 *.audit_file_dest=’/oracle/app/oracle//swx/adump’ *.cluster_database=false *.compatible=’11.2.0.0.0′ *.control_files=’+DATA/swxstandby/controlfile/current.272.792584487’#Restore Control file *.db_block_size=8192 *.db_create_file_dest=’+DATA’ *.db_create_online_log_dest_1=’+FRA’ *.db_domain=” *.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’ *.db_name=’swx’ *.db_recovery_file_dest_size=429496729600 *.db_recovery_file_dest=’+FRA’ *.db_unique_name=’SWXSTANDBY’ *.diagnostic_dest=’/oracle/app/oracle’ *.dispatchers= (PROTOCOL=T) (SERVICE=swxXDB)’ *.fal_client=’SWXSTANDBY’ *.fal_server=’SWX’ *.job_queue_processes=1000

*.log_archive_config=’dg_config=(swxstandby,swx)’ *.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_n ame=swxstandby’ *.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROL E) db_unique_name=swx’ *.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’ *.open_cursors=300 *.pga_aggregate_target=2147483648 *.processes=1000 *.remote_listener=’swxscan.qiibonline.com:1521′ *.remote__file=’exclusive’ *.sec_case_sensitive_logon=FALSE *.sessions=1105 *.sga_target=6442450944 *.standby_file_management=’AUTO’ *.undo_tablespace=’UNDOTBS1′ 5. Now startup the database in nomount mode using this pfile and create spfile f rom it. sqlplus “/as sysdba” startup nomount pfile=’/backup/swxSTANDBY.ora’; create spfile from pfile=’/backup/swxSTANDBY.ora’; shutdown immediate; startup nomount; 6. Create file for standby database Create the file in folder $ORACLE_HOME/dbs on all nodes and DR server a s Oracle format as orapwswxstandby for Standby database orapwd file=$ORACLE_HOME/dbs/orapwswxstandby force=y ignorecase=y =xxxxx x To check file use query from sql plus. select * from v$pwfile_s; NAME SYSDB SYSOP SYSAS —————————— —– —– —– SYS TRUE TRUE FALSE ————————— ###DUPLICATING THE DATABASE FOR STANDBY FROM RMAN### —————————1. Before duplicating ensure that the mountpoint /backup exists on the DR server . This is the location where we will copy the backups to and restore from there. RMAN > show all; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/rmanbkp/SWX_Lev0_%s_%D_%M_%Y’; RMAN> sql ‘alter system archive log current’;

RMAN> backup database include current controlfile for standby plus archivelog; Copy the database to the DR server on folder ‘/backup/rmanbkp’ and now we will begin to duplicate to RMAN from DR server from Oracle like below rman target sys/***@swx auxiliary sys/*** or rman target sys/***@swx auxiliary / or rman target sys/***@swx auxiliary sys/****@swxstandby $ rman target sys/*****@swx auxiliary / Recovery Manager: Release 11.2.0.3.0 – Production on Tue Nov 20 15:39:01 2012 Copyright (c) 1982, 2011, Oracle and/or its s. All rights reserved. connected to target database: SWX (DBID=xxxxxx) connected to auxiliary database: SWX (DBID=xxxxxx) If you get some error use (UR=A) in tnsnames.ora file like (SERVICE_NAME = swx) (UR=A) Go to primary server and crosscheck archivelogs and backupsets and delete expire d or obsolete objects and re to auxiliary. RMAN> duplicate target database for standby nofilenamecheck dorecover; 2. Put the Standby into Managed standby mode: ON standby server shutdown immediate; alter database mount standby database; alter database recover managed standby database using current logfile disconnect from session; Go to the Primary Server: SQL> alter system switch logfile; SQL> alter system archive log current; Check if the logs are coming here and properly applying. 3. the sync between primary and standby From primary sql > select max(sequence#) as “PRIMARY” from v$log_history; From Standby

SQL > select max(sequence#) as “STANDBY”,applied from v$archived_log group by applie d ; SQL> Select process,status from v$managed_standby; If there is error like below , it is generally due to files and for 11g R2 the parameter sec_case_sensitive_logon PING[ARC2]: Heartbeat failed to connect to standby ” Error is 16191. Error 1017 received logging on to the standby Recreate the files and alter sec_case_sensitive_logon to false Invoking a DR Standby Database Overview There are different methods you can use to invoke a standby database depending o n the typical DR scenario you are facing. Scenario for usage. 1. Database Switchover Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) wi thout having to rebuild either environment. This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover. 2. Activating a Standby Database This method will activate the Standby Database as the primary database recoverin g up to the point of the last log shipment. After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard env ironments). This scenario can be used if the primary server is not available and you need to force the standby database to become the primary. 3. Opening the standby Database in Read-Only Mode This method involves stopping data-guard replication and opening the standby dat abase in read-only mode for querying. The database can then be shutdown and reop ened in standby mode so that data-guard replication can resume (assuming all req uired archive logs are still available) This scenario can be used if you want to open the standby database temporarily f or querying data and then wish to put the database back into standby mode. The following section describes how to perform each of the above methods of invo king a standby database: 1. Database Switchover Using this method you can switch backwards and forwards between the primary and

DR servers (e.g. so that the primary can become DR and DR can become primary) wi thout having to rebuild either environment: On Primary Server: SQL> alter database commit to switchover to standby; This may cause the following error to be generated: ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected If this does occur then restart the database, as below, before retrying the abov e command: SQL> shutdown immediate SQL> startup SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect; The primary server is now configured as a DR standby database. On DR Server: SQL> alter database recover managed standby database cancel; SQL> alter database commit to switchover to primary; SQL> shutdown immediate SQL> startup The DR server is now configured as the primary database. To switch back you just need to repeat the above process but the other way aroun d (e.g. convert the DR database back to a standby and the primary database back to primary). 2. Activating a Standby Database If the primary database is not available the standby database can be converted i nto the primary database as follows: SQL> alter database recover managed standby database cancel; SQL> alter database activate standby database; SQL> shutdown immediate SQL> startup The original primary database is now obsolete and can be rebuilt as a standby da

tabase once it is available again. 3. Opening the Standby Database in Read Only Mode (Active Dataguard) The standby database can be opened in read only mode for querying and then conve rted back into a standby database without affecting the primary. On standby server: SQL> alter database recover managed standby database cancel; SQL> alter database open read only; The standby database is now open and available for querying in read only mode. To put the standby database back into standby mode: SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect; ================================================================================ ===== ###2 Node RAC to Single Instance Dataguard Switchover (Oracle 11gR2 Physical Sta ndby ) Many a times to save cost in building a DR solution for a certain application; y ou have to create a single instance database for your RAC primary. So you have g one ahead and build the 2-node to single instance setup and now comes the time t o test it. Well it is not much different than any other switchover scneario but the difference is that the switchover will be done with one node active in the p rimary (I prefer to use the OCR master node, check for OCR master node using “ocrc onfig -showbackup” command) This procedure was done on Oracle 11gR2 11.2.0.3 on Solaris 64-bit SPARC servers . Now let us begin our planned switchover. But before we begin I hope you have che cked all the necessary parameters on both sides. Viz. Log_archive_dest_1 Log_archive_dest_2 Log_archive_dest_state_1 Log_archive_dest_state_2 Fal_client Fal_server Local_listener Remote_listener Standby_archive_Dest Standby_archive_management service_names db_unique_name instance_name db_file_name_convert log_file_name_convert

the logs on both sites are in sync and logs are properly applied on the s tandby database. On Primary, Select thread#,max(sequence#) from v$archived_log group by thread#; On Standby, Select thread#,max(sequence#) from v$log_history group by thread#; Now we begin the actual switch over procedure 1. Shutdown RAC DB and STARTUP AGAIN TO CLEAR SESSIONS. I always prefer this met hod after shutting down the application srvctl stop database -d primary srvctl start database -d primary — Shutdown any service if you have defined one — srvctl stop service -d swx -s primaryservice 2. Shutdown Standby database and Restart Recovery shutdown immediate; startup mount; alter database recover managed standby database using current logfile disconnect from session; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; — Create a Guaranteed Restore point on Standby Database in case of a rollback if s omething goes wrong — CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ; 3. Shutdown Node 2 in cluster srvctl stop instance -d primary -n primarynode2 4. Take Guaranteed Flashback Restore points on Primary CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE; 5. that the primary database can be switched to the standby role SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS —————– TO STANDBY ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM ARCHIVE LOG CURRENT;

— Actual SWAPOVER Procedure — 6. Initiate Shutdown on Primary( Ensure 2nd Instance is shutdown) alter database commit to switchover to physical standby with session shutdown; Shutdown immediate; Startup mount; select name,db_unique_name, log_mode,open_mode,controlfile_type,switchover_statu s,database_role from v$database; Make log_Archive_Dest_state_2 to DEFER alter system set log_archive_dest_state_2=’DEFER’ sid=’*’; 7. On the (old) standby database. select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database; On the (old) standby database switch to new primary role: alter database commit to switchover to primary WITH SESSION SHUTDOWN; shutdown immediate; startup; 8. On new Primary database select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database; Make log_Archive_Dest_state_2 to ENABLE alter system set log_archive_dest_state_2=’ENABLE’ sid=’*’; alter system switch logfile; On new primary, select error from v$archive_Dest_status; select max(sequence#) from v$archived_log; 9. On new Standby, Start Redo Apply alter database recover managed standby database using current logfile disconnect ; Select max(sequence#) from v$log_history; (should be matching with Primary) 10. Now Start RAC databases services (both Primary – in open & Standby – in mount) srvctl start instance –d primary -n primarynode2 -o mount Check if the mrp0 process is saying “APPLYING_LOG” on the new standby database. Chec k with below query.

select process,status from v$managed_standby; If the logs are shipping and applying if not check the alert logs for any errors . The general error in an assymetrical rac is due to files. If the logs not applying and you seeing like below error in the alert log of new primary. PING[ARC2]: HEARTBEAT FAILED TO CONNECT TO STANDBY ‘SWX’. ERROR IS 16191. ERROR 1017 RECEIVED LOGGING ON TO THE STANDBY Solution : RECREATE THE FILES FOR NODE1 if failover is happening from N ODE1 format as orapw$ORACLE_SID for Standby database eg: orapwstandbydatabase format as orapw$ORACLE_SID for node 1 eg: orapwprimarynode1 format as orapw$ORACLE_SID for node 2 eg: orapwprimarynode2 Create the file in folder $ORACLE_HOME/dbs on all nodes and DR server a s Oracle(Database) ###ORA-10567: Redo is inconsistent with data block Recovery interrupted! Recovered data files to a consistent state at change 13507372 Errors in file e:\oracle\product\10.2.0\\ecc\bdump\eccstandby_mrp0_2724.trc : ORA-12801: error signaled in parallel query server P011 ORA-00600: internal error code, arguments: [3020], [1], [56204], [4250508], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 1, block# 56204) ORA-10564: I had an interesting scenario lately. In a dataguard configuration my primary da tabase had block corruption. But instead of failing over I decided to repair the database by doing point-in-time recovery by rolling back 5 minutes before the bloxk corruption. I used RMAN command like below on Primary run { set until time to_date(’12-May-2012 02:15:00′, ‘DD-MON-YYYY HH24:MI:SS’); restore database; recover database; } Now this recovered the primary and I was able to connect the Application. Now i wanted to check what was happeining on the standby. The alert log on standby gave below error ORA-10567: Redo is inconsistent with data block (file# 1, block# 56204)

I immediately knew my PITR on primary was the reason for this inconistency. This is called a ‘STUCK RECOVERY’.There is an inconsistency between the information stored in the redo and the information sto red in a database block being recovered. The best way to overcome is to restore from a backup and doing a point-in-time r ecovery to a time PRIOR to the one implied by the ORA-600[3020] error. So all I had to do is rewind the standby database to one minute before the Prima ry restore time and let dataguard do its magic. run { set until time to_date(’12-May-2012 02:14:00′, ‘DD-MON-YYYY HH24:MI:SS’); restore database; recover database; } Once I did this I checked the v$managed_standby view for MRP0 process and it had started applying the logs by then. Dataguard brought the standby in sync with primary. Magical ! By doing it this way; I recovered my primary database quickly and avoided failov er to standby site and rebuilding the standby database from scratch. Please note this procedure was done on Oracle 10. 2.0.5 database running on Windows Server 2003. Also I had a full backup from 2 days before both on Primary and Standby si te. Also all archivelogs after that day existed on disks of both Primary and Standby. ###RMAN database restore until time I had a block corruption of my primary database, though we had a standby build w e wanted to recover the Primary instead of failover to the Standby. This recovery is possible when you have say a full backup made of the database t ill 9th May and all archivelogs from 9th May till 12th MAY are present. Put the database in mount mode and restore it till the time before the corruptio n happened. run { set until time to_date(’12-May-2012 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’); restore database; recover database; }

###Resync Standby Database when Archivelogs at Primary are deleted. In this scenario,the standby database lost (connection) with primary data base for few days and hundreds of redo log was switched and unfortunately archiv elogs were deleted from primary database, network problem is resolved , and now

you want to resync standby db with minimun time / no downtime. server 1 (primary database) os: linux 64bit db: 10gR2 db_size: 700GB server 2 (standby database (DR)) os: linux 64bit db: 10gR2 1. Find the current SCN of standby database. select current_scn from v$database; CURRENT_SCN ———– 4793543 2. On the primary database – create the needed incremental backup from the above S CN rman target / RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 4793543 DATABASE FORMAT ‘C:\tem p\bkup_%U’; 3. create a new standby controlfile from production sqlplus> alter database create standby crontrolfile ‘blah’; 4.cancel managed recovery on standby sqlplus> recover managed standby database cancel; 5. Move your incremental backup from (2) to the standby server (empty folder) an d catalog it rman target / rman> catalog start with ‘c:\temp\backup\’; 6. recover your standby from the incremental backup rman> recover database noredo; 7. shutdown the standby and replace the controlfile with the one you backup in ( 3) 8.startup the standby and put it back into managed recovery mode sqlplus> startup mount sqlplus> recover managed standby database disconnect; ###Oracle 11gR2 Dataguard using Duplicate Active Standby In this activity we will create Oracle 11gR2 Datagaurd using active duplication. In active duplication, we don’t need to take rman backup and copy the backup to t he Standby Server. The database can be duplicated over the network. Its an amazi ng feature which saves you much time and effort. The only thing you have to be c areful while duplicating using this active feature is to make sure the LOG_FILE_ NAME_CONVERT and DB_FILE_NAME_CONVERT parameters are set properly. And all the r equisite directories are created on standby server. Primary SID : ecc Standby SID: eccstandby

DB Unique name : ecc In regards to Oracle 11gR2 take take and set the parameter sec_case_sensitive_lo gon is set to false. Let us begin now the excercise. ORACLE 11GR2 DATAGUARD USING DUPLICATE ACTIVE STANDBY —————————————————————————————————————————Primary Database Steps: —————————————————————————————————————————-: 1. Put database in archive log mode Archive log list; alter system set log_archive_dest_1=’LOCATION=E:\archivelogs’; shutdown immediate startup mount alter database archivelog; #Enable force logging alter database force logging; 2. Create file select * from v$pwfile_s; If file doesnt exist then make the file If it doesn’t exist, use the following command to create one: – On Windows: $cd %ORACLE_HOME%\database $orapwd file=’F:/ECWD.ora’ =xxxxxxxx ignorecase=y force=y (Note: Replace xxxxxxxxx with the for the SYS .) 3. Create standby redo logs; ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘E:\oradata\ecc\redo04.log’ size 50m; ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘E:\oradata\ecc\redo05.log’ size 50m; ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘E:\oradata\ecc\redo06.log’ size 50m; select * from v$logfile; select * from v$standby_log; you have to create these standby logs on Standby Server as well ADD TNS entry to tnsnames.ora ====================================

eccstandby = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = hrdev.qiibonline.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ecc)(UR=A) ) ) ===================================== 4. Edit the newly created pfile and add following parameters to the pfile show parameter spfile; create pfile=’F:/ecRIM.ora’ from spfile; #########################STANDBY PARAMETERS######################## *.DB_UNIQUE_NAME=’ecc’ *.INSTANCE_NAME=’ecc’ *.DB_FILE_NAME_CONVERT=’eccstandby’,’ecc’*.INSTANCE_NAME=’ecc’ *.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ecc,eccstandby)’ *.LOG_ARCHIVE_DEST_1=’LOCATION=E:\archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) D B_UNIQUE_NAME=ecc’ *.LOG_ARCHIVE_DEST_2=’SERVICE=eccstandby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=eccstandby’ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.LOG_FILE_NAME_CONVERT=’ecc’,’eccstandby’ *.REMOTE__FILE=’EXCLUSIVE’ *.SERVICE_NAMES=’ecc’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.sec_case_sensitive_logon=’FALSE’ *.FAL_CLIENT=’ecc’ *.FAL_SERVER=’eccstandby’ ######################################## 4. Start database using this pfile and then open database from spfile. shutdown immediate startup pfile=’F:\ecRIM.ora’ create spfile from pfile=’F:\ecRIM.ora’; shutdown immediate startup 5. Backup ECC database using RMAN rman target / connected to target database: ECC (DBID=997720117) show all; CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘E:\rmanbkp\%F’; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘E:\rmanbkp\%d_%s_%p.rman’; show all; ##############RMAN configuration parameters are ###################### CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘E:\ORA_BACKUPS\ %F’; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘E:\ORA_BACKUPS\%d_%s_%p.rman’; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S NCFECC.ORA’; # default ######################################################################### sql “alter system switch logfile”; sql “alter system archive log current”; —————————————————————————————————————————Standby Database Steps: —————————————————————————————————————————create folders of adump, diag Create archive log folder ‘F:\archivelogs’ copy the previously create pfile called ecRIM.ora to standby server and rename it to eccSTANDBY.ora Add the following parameters to the pfile called eccSTANDBY.ora ################ Standby Parameters ##### *.DB_FILE_NAME_CONVERT=’ecc’,’eccstandby’ *.DB_NAME=’ecc’ *.DB_UNIQUE_NAME=’eccstandby’ *.INSTANCE_NAME=’eccstandby’ *.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(ecc,eccstandby)’ *.LOG_ARCHIVE_DEST_1=’LOCATION=F:\archivelogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) D B_UNIQUE_NAME=eccstandby’ *.LOG_ARCHIVE_DEST_2=’SERVICE=ecc VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQ UE_NAME=ecc’ *.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ *.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ *.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’ *.LOG_FILE_NAME_CONVERT=’ecc’,’eccstandby’ *.REMOTE__FILE=’EXCLUSIVE’ *.SERVICE_NAMES=’eccstandby’ *.STANDBY_FILE_MANAGEMENT=’AUTO’ *.sec_case_sensitive_logon=’FALSE’ *.FAL_CLIENT=’eccstandby’ *.FAL_SERVER=’ecc’

############################################### 2. Create listener entires and tns entries on both ECC and ECCSTANDBY server ================== On Primary Server ================== ###Listener Entry### Take backup of previous listener file. Change remove old entry and add this new entry ECC = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = eccdbtest.qiibonline.com)(PORT = 1521)) ) SID_LIST_ECC = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ecc_DGMGRL) (ORACLE_HOME = E:\oracle\product\10.2.0\db_1) (SID_NAME = ecc) ) ) lsnrctl start lsnrctl status connect a toad session from your pc and check if listener is able to establish t he connection. ### TNS entry ### ECC = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = eccdbtest.qiibonline.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ecc)(UR=A) ) ) eccstandby = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = hrdev.qiibonline.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = eccstandby)(UR=A) ) ) ================== On Standby Server ================== ### Listener entry ### ECCSTANDBY =

(DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = hrdev.qiibonline.com)(PORT = 1521)) ) SID_LIST_ECCSTANDBY = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = eccstandby_DGMGRL) (ORACLE_HOME = E:\oracle\product\10.2.0\db_1) (SID_NAME = eccstandby) ) ) ### TNS Entries #### ECCSTANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = hrdev.qiibonline.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = eccstandby)(UR=A) ) ) ECC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = T)(HOST = eccdbtest.qiibonline.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ecc)(UR=A) ) ) Stop and Start the Listeners on both servers using command “LSNRCTL STOP” “LSNRCTL START” wait for few seconds to check services on listener on the primary se rver 3. Create file for the Standby server orapwd file=F:\ECWD.ora =sys ignorecase=y force=y note: sys must be identical for both primary and standby database 4. Create Service on Windows Since we are creating data guard on windows we need to manually create a service using oradim utility. On unix you can safely skip this step ## FORMAT ## oradim -new -sid %ORACLE_SID% -intpwd MYSECRET -startmode M ANUAL -pfile c:\>oradim -new -sid ECCSTANDBY -intpwd sys -startmode AUTO -pfile ‘F:\eccSTANDBY. ora’ #pfile here is the pfile you created for the Standby server previously called ec cSTANDBY.ora’ is the that is used to connect / as sysdba. MODE indicates how the service is started. It must either be auto or manual. aut

o tells Windows to automatically start the service when Windows is booted. manua l, on the other hand, requires someone to manually start the service. set ORACLE_SID=ECCSTANDBY sqlplus “/as sysdba” startup nomount pfile=’F:\eccSTANDBY.ora’ create spfile from pfile=’F:\eccSTANDBY.ora’ show parameter fal; shutdown immediate startup nomount show parameter fal; 5. Creating Standby using RMAN rman target sys/sys@ECC auxiliary sys/sys@eccstandby connected to target database: ECC (DBID=997720117) connected to auxiliary database: ECC (not mounted) confirm from primary the DBID of ECC target (DBID=997720117) is same go to primary server and crosscheck archivelogs and backupsets and delete expire d or obsolete objects and re to auxiliary Also if you get some errors make sure you create the subfolders “eccstandby” and “ecc” i n following path : E:\oracle\product\10.2.0\oradata DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMEC HECK; ################################################## starting media recovery archive log thread 1 sequence 9 is already on disk as file F:\ARCHIVELOGS\LOG1_9 _737729399.ARC archive log thread 1 sequence 10 is already on disk as file F:\ARCHIVELOGS\LOG1_ 10_737729399.ARC archive log thread 1 sequence 11 is already on disk as file F:\ARCHIVELOGS\LOG1_ 11_737729399.ARC archive log filename=F:\ARCHIVELOGS\LOG1_9_737729399.ARC thread=1 sequence=9 archive log filename=F:\ARCHIVELOGS\LOG1_10_737729399.ARC thread=1 sequence=10 archive log filename=F:\ARCHIVELOGS\LOG1_11_737729399.ARC thread=1 sequence=11 media recovery complete, elapsed time: 00:00:00 Finished recover at 15-DEC-10 Finished Duplicate Db at 15-DEC-10 #################################################### 7. Put the Standby into Managed standby mode: ON standby server shutdown immediate; STARTUP NOMOUNT

alter database mount standby database; alter database recover managed standby database disconnect from session; Go to the Primary Server: SQL> alter system switch logfile; SQL> alter system archive log current; —————————————————————————————————————————- the sync between primary and standby: —————————————————————————————————————————From primary sql > select max(sequence#) as “PRIMARY” from v$log_history; From Standby SQL > select max(sequence#) as “STANDBY”,applied from v$archived_log group by applie d ; #####Creating any tablespace on primary server will automatically create those s ame tablespace datafiles on new server. But this is not applicable to temporary files and undo files. It is good practise to make temporary file size same on st andby server manually. Also resize your undo tablespace accordingly. Also Restart the MRP process on Standby using below command if datafile on prima ry didnt transfer to secondary. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT ; ================================================================================ ===== Invoking a DR Standby Database Overview There are different methods you can use to invoke a standby database depending o n the typical DR scenario you are facing. Scenario for usage. 1. Database Switchover Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) wi thout having to rebuild either environment. This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover. 2. Activating a Standby Database This method will activate the Standby Database as the primary database recoverin g up to the point of the last log shipment. After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard en vironments).

This scenario can be used if the primary server is not available and you need to force the standby database to become the primary. 3. Opening the standby Database in Read-Only Mode This method involves stopping data-guard replication and opening the standby dat abase in read-only mode for querying. The database can then be shutdown and reo pened in standby mode so that data-guard replication can resume (assuming all re quired archive logs are still available) This scenario can be used if you want to open the standby database temporarily f or querying data and then wish to put the database back into standby mode. The following section describes how to perform each of the above methods of invo king a standby database: 1. Database Switchover Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) wi thout having to rebuild either environment: On Primary Server: SQL> alter database commit to switchover to standby; This may cause the following error to be generated: ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected If this does occur then restart the database, as below, before retrying the abov e command: SQL> shutdown immediate SQL> startup SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect; The primary server is now configured as a DR standby database. On DR Server: SQL> alter database recover managed standby database cancel; SQL> alter database commit to switchover to primary; SQL> shutdown immediate SQL> startup

The DR server is now configured as the primary database. To switch back you just need to repeat the above process but the other way aroun d (e.g. convert the DR database back to a standby and the primary database back to primary). 2. Activating a Standby Database If the primary database is not available the standby database can be converted i nto the primary database as follows: SQL> alter database recover managed standby database cancel; SQL> alter database activate standby database; SQL> shutdown immediate SQL> startup The original primary database is now obsolete and can be rebuilt as a standby da tabase once it is available again. 3. Opening the Standby Database in Read Only Mode The standby database can be opened in read only mode for querying and then conve rted back into a standby database without affecting the primary. On standby server: SQL> alter database recover managed standby database cancel; SQL> alter database open read only; The standby database is now open and available for querying in read only mode. To put the standby database back into standby mode: SQL> shutdown immediate SQL> startup nomount SQL> alter database mount standby database; SQL> alter database recover managed standby database disconnect; ================================================================================ ===== How to check whether the Standby Database is in Sync On the primary server: SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history; On the standby server: SQL> SELECT max(sequence#) AS “STANDBY”, applied FROM v$archived_log GROUP BY appli ed; The standby database is in sync with the primary database if the above PRIMARY v

alue matches the above STANDBY value where applied = ‘YES’. ###Oracle 11g Snapshot Standby Database (Read- Write mode for standby db) Oracle 11g Dataguard introduced powerful new features viz. Active Data Guard and Snapshot Standby Database. Snapshot standby database specially is an amazing fe ature. Where you can convert your standby database into read-write mode while th e log shipping is happening. Once you finish your testing (feel free to drop tab les, run rogue update statements or anything under the sun) you can tell the dat abase to apply the already received logs. You never lose a single transaction. Because in the background the archivelogs are being shipped, just not being app lied. I will demonstrate how to convert you physical standby database into snaps hot database and refresh it again. 1. Connect to the Standby database Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> conn sys as sysdba Enter : Connected. SQL> select database_role,switchover_status from v$database; DATABASE_ROLE SWITCHOVER_STATUS —————- ——————– PHYSICAL STANDBY NOT ALLOWED SQL> alter database recover managed standby database cancel; Database altered. SQL> show parameter recover NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string db_recovery_file_dest_size big integer 0 db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 SQL> alter system set db_recovery_file_dest=’E:\recovery_dest’ scope=spfile; System altered. SQL> alter system set db_recovery_file_dest_size=5g; System altered. SQL> show parameter recover NAME ———————————— ———– —————————— db_recovery_file_dest db_recovery_file_dest_size db_unrecoverable_scn_tracking recovery_parallelism

TYPE

VALUE

string big integer 5G boolean TRUE integer 0

SQL> alter system set db_recovery_file_dest=’location=E:\recovery_dest’ scope=spfile ; System altered.

SQL> show parameter recover NAME TYPE ———————————— ———– —————————— db_recovery_file_dest string db_recovery_file_dest_size big integer db_unrecoverable_scn_tracking boolean recovery_parallelism integer SQL> alter database mount standby database;

VALUE E:\recovery_dest 5G TRUE 0

Database altered. SQL> alter database convert to snapshot standby; Database altered. SQL> select open_mode,database_role from v$database; OPEN_MODE ——————– —————MOUNTED

DATABASE_ROLE SNAPSHOT STANDBY

SQL> alter database open; Database altered. SQL> select open_mode,database_role from v$database; OPEN_MODE ——————– —————READ WRITE

DATABASE_ROLE SNAPSHOT STANDBY

After you finish your testing, convert the snapshot database back to physical st andby again. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 778387456 bytes Fixed Size 1383768 bytes Variable Size 234881704 bytes Database Buffers 536870912 bytes Redo Buffers 5251072 bytes SQL> alter database mount; Database altered. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted

ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 778387456 bytes Fixed Size 1383768 bytes Variable Size 234881704 bytes Database Buffers 536870912 bytes Redo Buffers 5251072 bytes SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select open_mode,database_role from v$database; OPEN_MODE ——————– —————MOUNTED

DATABASE_ROLE PHYSICAL STANDBY

Helpful Link: http://repettas.wordpress.com/2008/10/24/rman-11g-snapshot-standby-database/ ###Apple’s Oracle Dataguard 11g architecture Apple use’s Oracle Active Dataguard 11g for its AppStore. In Oracle 11g data guar d you can have upto 30 active data guard instances. What Apple most likely does is, cascade its Production DB into many Standby instances and uses different app lications to interface with each standby database.

For more details read on : http://www.oracledatabase12g.com/archives/oracle-acti ve-data-guard-technical-overview.html ###Defer Log Shipping Oracle Data Guard Sometimes it becomes necessary to stop the log shipping procedure without doing a shutdown of the standby database or server. For eg: IF there is a network rela ted activity in you organization and there will be a network outage. You can def er the log shipping. Or if you are relocating your standby database server. Plea se note : Log shipping has to be enabled explicitly later for the logs to start applying on Standby server again. On Primary database server SQL> alter system set log_archive_destination_state_2=’DEFER”; this defers(delays the log shipping)

Now do a switch of logfile to confirm the logfile didnt come on the standby data base. Alternately you can check from SQL command using. On the primary server: SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history; On the standby server: SQL> SELECT max(sequence#) AS “STANDBY”, applied FROM v$archived_log GROUP BY applied;