Physical standby using HOT BACKUP

Here are the steps to create physical standby database using HOTBACKUP. The primary database name is "SRIPROD" and the standby databse is "SRITEST".

Step 1: On the primary database side, enable the FORCELOGGING.

a) Connect to the primary database.           sqlplus "/as sysdba"      b) SQL> alter database force logging;

Step 2: Create the password file on secondary database if it doesn’t exist. The password file will exist in $ORACLE_HOME/dbs

a) SQL> orapwd file=orapwSRIPROD password=xxx force=y

Step 3: Create the standby Redo logs.

a) The size of the standby redo log files should match the size of current primary database online redo log files. First we have find the size of the primary database online redo log files.           SQL> select bytes from v$log;                BYTES               ----------                104857600                104857600                104857600 Here the size of my online redo log files is 100M. b) Now use the following query to find the details of the log files in the primary database. (The following command has to execute on primary database.) SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------------ 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1a.dbf 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1b.dbf 2 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log2a.dbf 2 ONLINE /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log2b.dbf 3 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log3a.dbf 3 ONLINE /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log3b.dbf c) Now create the standby redo log files in the primary database. 1) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 4 ('/u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4a.dbf','/u01/app/oracle/data/db2/data/ SRIPROD/SRIPROD_log4b.dbf') SIZE 100M; 2) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 5 ('/u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5a.dbf','/u01/app/oracle/data/db3/data/ SRIPROD/SRIPROD_log5b.dbf') SIZE 100M; 3) ALTER DATABASE ADD SRITESTDBY LOGFILE GROUP 6 ('/u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6a.dbf','/u01/app/oracle/data/db4/data/ SRIPROD/SRIPROD_log6b.dbf') SIZE 100M; d) To verify the results of the standby redo log groups creation, run the following query on the primary database: SQL> select group#,type,member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ------------------------------------------------------------ 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1a.dbf 1 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log1b.dbf 2 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log2a.dbf 2 ONLINE /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log2b.dbf 3 ONLINE /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_log3a.dbf 3 ONLINE /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log3b.dbf 4 STANDBY /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4a.dbf 4 STANDBY /u01/app/oracle/data/db2/data/SRIPROD/SRIPROD_log4b.dbf 5 STANDBY /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5a.dbf 5 STANDBY /u01/app/oracle/data/db3/data/SRIPROD/SRIPROD_log5b.dbf 6 STANDBY /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6a.dbf 6 STANDBY /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log6b.dbf

Step 4: Enable Archiving on primary database.

a) First check the archiving is enabled or not?           SQL> archive log list;      b) If arching is not enabled then do the following steps to enable the archive log mode.            SQL>shutdown immediate;            SQL>startup mount;            SQL>alter database archivelog;            SQL>alter database open;            SQL>archive log list;

Step 5: Now we have to set the initialization parameters on primary database.

a)First create the pfile from spfile if the database is mounted using spfile.           cd $ORACLE_HOME/dbs           sqlplus “/ as sysdba”           create pfile from spfile; It will create initSRIPROD.ora file in $ORACLE_HOME/dbs folder. b) Now edit the initSRIPROD.ora file to add the new primary and standby role parameters. *.db_name=SRIPROD *.db_unique_name=SRIPROD *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SRIPROD,SRITEST)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/SRIPROD/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SRIPROD' *.LOG_ARCHIVE_DEST_2='SERVICE=SRITEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRITEST' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' *.FAL_CLIENT='SRIPROD' *.FAL_SERVER='SRITEST' *.STANDBY_FILE_MANAGEMENT='AUTO' *.DB_FILE_NAME_CONVERT='SRITEST','SRIPROD' *.LOG_FILE_NAME_CONVERT='SRITEST','SRIPROD'

Step 6: Now shutdown the primary database and mount the database with pfile.

On Primary database.           SQL> sqlplus “/ as sysdba”           SQL> shutdown immediate;           SQL> exit           UNIX PROMPT> cd $ORACLE_HOME/dbs           UNIX PROMPT> rm –rf spfileSRIPROD.ora (remove/move the spfile since we should mount the database using pfile.)           UNIX PROMPT> sqlplus “/as sysdba”           SQL> startup

Step 7: Now create the spfile from pfile(new pfile after adding the stand by parameters)

SQL> create spfile from pfile;      SQL> shutdown immediate;      SQL> startup; Now it will mount using the spfile.      SQL> Check whether the database is mounted using spfile or not? SQL> select nvl(value,'NO') from v$parameter where name='spfile'; NVL(VALUE,'NO') ---------------------------------------------------------------------------- /u01/app/oracle/product/10.2.0.1/dbs/spfileSRIPROD.ora

Step 8: Now we need to take a backup of primary database using cold backup or hot backup or using RMAN.
Here I am taking hot backup since there is no need for me to shut down the primary database.

a) Test whether primary database is in Archivelog mode or not? If it is not enable the archive log mode as described in Step 4. b) Use this sql command to check to find whether all the table spaces are in online mode or not on primary database?        $ select tablespace_name, online_status from dba_data_files; c) Use the following command to startup the actual database backup using hotbackup by placing all tablespaces in begin backup mode.      SQL> select 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_tablespaces where tablespace_name not like 'TEMP%'; 'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;' ------------------------------------------------------------- alter tablespace SYSTEM begin backup; alter tablespace UNDOTBS1 begin backup; alter tablespace SYSAUX begin backup; alter tablespace USERS begin backup; alter tablespace TEST begin backup; d) execute the above result to place all the table spaces in begin backup mode. e) Now copy the data files from primary database to stand by database locations. In primary use the following query to find the datafiles. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_system1a.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_undotbs1a.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_sysaux1a.dbf /u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log1d.dbf /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_test1a.dbf f) Copy all these datafiles to stand by database locations. My stand by database file structure is as follows. /u01/app/oracle/data/db1/data/SRITEST/ So use the following copy commands to copy the datafiles from primary to stand by. Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_system1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_system1a.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_undotbs1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_undotbs1a.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_sysaux1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_sysaux1a.dbf Cp u01/app/oracle/data/db4/data/SRIPROD/SRIPROD_log1d.dbf u01/app/oracle/data/db4/data/SRITEST/SRITEST_log1d.dbf Cp /u01/app/oracle/data/db1/data/SRIPROD/SRIPROD_test1a.dbf /u01/app/oracle/data/db1/data/SRITEST/SRITEST_test1a.dbf NOTE: It is very important to change the name of the data file in stand by database to the corresponding sid name as we are giving the *.DB_FILE_NAME_CONVERT='SRIPROD',' SRITEST '. That means it will replace all the SRIPROD name with SRITEST. g) After copying all data files it is needed to put all the table spaces in end backup mode. For that use the following… SQL> select 'alter tablespace ' || tablespace_name || ' end backup;' from dba_tablespaces where tablespace_name not like 'TEMP%'; 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;' ----------------------------------------------------------- alter tablespace SYSTEM end backup; alter tablespace UNDOTBS1 end backup; alter tablespace SYSAUX end backup; alter tablespace USERS end backup; alter tablespace TEST end backup; h) Now execute the above sql “alter tablespace” statements to place the table spaces in end backup mode. SQL> alter tablespace SYSTEM end backup; SQL> alter tablespace UNDOTBS1 end backup; SQL> alter tablespace SYSAUX end backup; SQL> alter tablespace USERS end backup; SQL> alter tablespace TEST end backup; i) With this hot backup is completed.

Step 9: Create the control file to the stand by database.
On the primary database create the controlfile for the standby to use as follows.

For us the primary database is in open mode. So we need to shut down the primary database and have to open in mount state.           On Primary:           SQL> shutdown immediate;           SQL> startup mount;           SQL> alter database create standby controlfile as ‘/u01/app/oracle/data/db1/data/SRITEST/SRITEST_control01.ctl’;           SQL> ALTER DATABASE OPEN;

Step 10: Copy the primary database pfile to stand by server $ORACLE_HOME/dbs/ and rename the pfile with appropriate oracle SID name.

setinst SRIPROD; si;      On primary database host:      UNIX PROMPT> cd $ORACLE_HOME/dbs      UNIX PROMPT> cp initSRIPROD.ora initSRITEST.ora (If both primary and stand by databases are on same host)           OR      UNIX PROMOT> scp –r –p initSRIPROD.ora oracle@hostname:$ORACLE_HOME/dbs/initSRITEST.ora

Step 11: Edit the init parameter file on standby database host.

On stand by database host change the following parameters on stand by database init parameter file.      Cd $ORACLE_HOME/dbs      View initSRITEST.ora *.audit_file_dest='/u01/app/oracle/admin/SRITEST/audit' *.background_dump_dest='/u01/app/oracle/admin/SRITEST/bdump' *.core_dump_dest='/u01/app/oracle/admin/SRITEST/cdump' *.user_dump_dest='/u01/app/oracle/admin/SRITEST/udump' *.compatible='10.2.0.3.0' *.control_files='/u01/app/oracle/data/db1/data/SRITEST/SRITEST_control01.ctl' *.db_name='SRIPROD' *.DB_UNIQUE_NAME='SRITEST' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(SRIPR)D,SRITEST)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/admin/SRITEST/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SRITEST' *.LOG_ARCHIVE_DEST_2='SERVICE=SRIPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SRIPROD' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' *.FAL_CLIENT='SRITEST' *.FAL_SERVER='SRIPROD' *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' *.DB_FILE_NAME_CONVERT='SRIPROD','SRITEST' *.LOG_FILE_NAME_CONVERT='SRIPROD','SRITEST'

Step 12: On Standby server, create all required directories for dump and archived log destination. Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

Note: We should copy the controlfile which is created in step 9 in appropriate folder as *.control_files parameter in init parameter file of standby database look for that file.

Step 13: Copy the standby control file “SRITEST_control01.ctl” which is created in Step 9 from primary to standby destinations.

Step 14: Copy the primary database password file to stand by database $ORACLE_HOME/dbs location and rename that file to “orapwSRITEST”

Step 15: Configure the listeners on primary database and on standby database.

a) View listener.ora LISTENER_SRIPROD= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = SRIPROD)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.srikanth.com) (PORT = 1521)) ) ) ) SID_LIST_LISTENER_SRIPROD= (SID_LIST = (SID_DESC = (SID_NAME = SRIPROD) (ORACLE_HOME=/u01/app/oracle/product/10.2.0.1) ) ) b) Now add tns entry for SRIPROD in tnsnames.ora file of primary database. On primary database: Cd $ORACLE_HOME/network/admin/ View tnsnames.ora SRIPROD = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.srikanth.com)(PORT=1521))(CONNECT_DATA=(SID=SRIPROD))) c) On Secondary database host: cd $ORACLE_HOME/network/admin Add the following entry. LISTENER_SRITEST= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = SRITEST)) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.srikanth.com) (PORT = 1527)) ) ) ) SID_LIST_LISTENER_SRITEST= (SID_LIST = (SID_DESC = (SID_NAME = SRITEST) (ORACLE_HOME=/u01/app/oracle/product/10.2.0.1) ) ) d) Now add tns entry for SRITEST in tnsnames.ora file of secondary database host. On secondary database’s host: Cd $ORACLE_HOME/network/admin/ View tnsnames.ora SRITEST = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.srikanth.com)(PORT=1521))(CONNECT_DATA=(SID=SRITEST)))

Step 16: Now start the listeners on both databases.

a) On primary database host:           lsnrctl start LISTENER_SRIPROD      b) On secondary database host:           lsnrctl start LISTENER_SRITEST      c) On primary database host:           Check tnsping to both services:             $tnsping SRIPROD             $tnsping STITEST      d) On secondary database host:           Check tnsping to both services:             $tnsping SRIPROD             $tnsping STITEST

Step 17: On Standby database host start the instance using newly created init parameter file and generate spfile.

On Secondary database      $ cd $ORACLE_HOME/dbs      $ sqlplus “/as sysdba”      SQL> startup nomount -> The database will start using init file. It should start in nomount state only.      SQL> create spfile from pfile;      SQL> shutdown immediate;

Step 18: Do the recovery for the stand by database as we did hot backup.

On standby database: a)      SQL> startup nomount -> Now it will start using spfile.      SQL> alter database mount standby database ;      SQL> recover standby database ;      Specify log: {=suggested | filename | AUTO | CANCEL} NOTE: Apply all the archive log files as you find them in primary database archive directory. After applying all the archive log files give “CANCEL”. b) Start Redo apply      On the standby database, to start redo apply:      SQL>alter database recover managed standby database disconnect from session; c) If you ever need to stop log apply services:      SQL> alter database recover managed standby database cancel;

Step 19: Verify the standby database is performing properly.

a) On Standby perform a query:           SQL>select sequence#, first_time, next_time from v$archived_log;      b) On Primary, force a logfile switch:           SQL>alter system switch logfile;      c) On Standby, verify the archived redo log files were applied:           SQL>select sequence#, applied from v$archived_log order by sequence#;      d) If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.      e) To start real-time apply.           SQL> alter database recover managed standby database using current logfile disconnect;