Friday 22 March 2013

How to create physical standby using cold backup


------------------------ production ---------------------------

1. Enable ARCHIVE LOG MODE using
1. shutdown immediate;
2. startup mount
3. alter database archivelog;
4. alter database open;
5. select log_mode from v$database;
2. Set log_archive_dest_1 using
alter system set log_archive_dest_1='LOCATION=E:\oracle\ora92\database\archive' scope=both;
3. Create pfile from spfile using
create pfile from spfile;
4. Add following entries bellow *.processes=150 in initprod.ora
*.log_archive_start=true
*.log_archive_dest_1='LOCATION=E:\oracle\ora92\database\archive'
*.log_archive_dest_2='service=stdb'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%%ORACLE_SID%%T%TS%s.ARC'
*.standby_file_management='AUTO'
*.standby_archive_dest=E:\oracle\ora92\database\archive
5. Shutdown the database and startup from pfile using
startup pfile='E:\oracle\ora92\database\INITprod.ORA'
6. Now re-create pfile from spfile using
create spfile from pfile;
7. Shudown database and startup mount
8. Start the archive log using
alter system archive log start;
9. open the database.
10 Switch archive log and check the archive at archive location
alter system switch logfile;
11. create a control file for the standby using
alter database create standby controlfile as 'e:\stdb_ctrl.CTRL';
--------Note: Don't switch log file after control file creation

12. Shutdown database and take backup (copy) of
admin
oradata
tnsnames.ora
INITprod.ORA
stdb_ctrl.CTRL
13. Create a tns entry for the standby as follows
STDB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.170.1.112)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = PROD)
   )
 )
------------------------ standby ---------------------------

1. Copy the copied admin and oradata to the same location as on production
2. Create a database service using
oradim -new -sid prod -intpwd prod -startmode a -pfile 'E:\oracle\ora92\database\INITprod.ORA'
3. Create a new listener Net Configuration Assistant (cna)
4. Add entry in listener.ora through
start=> all programes=> oracle orahome92=> configuration and migration tools=> net manager=>
local=> listeners=> listener=> database services=> add database
global database name :prod
oracle home directory:D:\oracle\ora92
sid                  :prod
then file and click save network configuraion.
5. Restart lisnter from services.msc
6. Set ORACLE_SID using
set ORACLE_SID=PROD
7. Restart listener and database
8. Create spfile from pfile using
create spfile from pfile='E:\oracle\ora92\database\INITprod.ORA'
9. Copy control file created for standby at E:\oracle\oradata\PROD
and make its three copies renaming existing control files.
10. Set the log_archive_dest_2 using
alter system set log_archive_dest_2='service=stdb' scope=both;
alter system set log_archive_dest_2=enable scope=both;


Note:
1. if TNS protocol adopter error occur the use ip address instead host name in tnsnames file at production
for standby and at standby for production.
2. if standby is not recovering the recreate control file and replace it
at standby. shutdown database, restart and then recover.
3. it is better to create standby control at last and replace at standby.