***************************************************************************************************************
This documentation is successfully applied on Oracle 9.2.0.1.0 on windows platform. This documentation is valid for both the cold and hot backup of primary database. If you are using a, remote standby, the files will need to be backed up and then transfer using tool such as FTP or mapped network drive. While using FTP make sure that you are using binary mode to transfer file.
For configuring the standby database your Primary database should be archive mode
Here In this document I am keeping for primary-
Instance_name/service_names = primary
And for standby
Instance_name/service_names = standby
Taking Backup of primary database
Step--1
Shut down the database and take a Cold Backup of all logfiles, datafiles and archive log files;
Parameter initialization task:
Step --2
Copy the init.ora, logfiles, datafile and archive log files of primary to Standby location.
Make changes in parameter file of both Standby and Primary database.
Change the parameter in Primary Init.ora.
· remote_archive_enable=true
· fal_server=primary
· fal_client=standby
· standby_file_mangement=auto
Change the parameter in Standby init.ora
· remote_archive_enable=true
· control_files='standby_controlfile.ctl'
· fal_sever=primary
· fal_client=standby
· standby_archive_dest='standby_archive_destination'
· standby_file_mangement=auto
· instance_name=standby
· service_names=standby
· log_archive_dest='standby_archive_destination'
· background_dump_dest='background dump destination'
· user_dump_dest='user dump destination'
· core_dump_dest='core dump destination'
· Keep db_name as same as Primary
If you are using different directory structure on standby then you have to set two parameter-
· db_file_name_convert=("Primary directory path","standby directory path")
· log_file_name_convert=("Primary directory path","standby directory path")
If you are configuring standby on same host of primary then set this parameter-
· lock_name_space=standby
Varify local_listner parameter set properly in init.ora.
Configuring Network files:
Primary Tnsnames.ora:
-------------------------------------------------------------------------------------------------------------------------
Primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP address of primary server )(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary instance_name)
)
)
Standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP adress of standby server )(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby instance name)
)
)
----------------------------------------------------------------------------------------------------------------------------
Primary Listner.ora:
----------------------------------------------------------------------------------------------------------------------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP adress of primary server)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = c:\oracle\ora92)
(SID_NAME = primary)
)
)
----------------------------------------------------------------------------------------------------------------------------
Standby Tnsnames.ora:
----------------------------------------------------------------------------------------------------------------------------
Primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP address of primary server )(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary instance_name)
)
)
Standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP adress of standby server )(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby instance name)
)
)
----------------------------------------------------------------------------------------------------------------------------
Standby Listner.ora
----------------------------------------------------------------------------------------------------------------------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP adress of standby server)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = c:\oracle\ora92)
(SID_NAME = standby)
)
)
----------------------------------------------------------------------------------------------------------------------------
Creating standby controlfile:
Step--4
Startup Primary database
sql>startup pfile='init.ora'
sql> create spfile from pfile;
sql> alter database create standby controlfile as 'standby controlfile path and name'
Or you can create the standby controlfile, when your are configuring the standby database. But remember to switch logfile before create standby controlfile. Otherwise ‘ORA-01152: file %s was not restored from a sufficiently old backup’ may occur.
Step--5
Copy standby controlfile to standby location.
Mounting standby database:
Step--6
If you are using windows then create windows service using oradim as follows---
oradim -new -sid standby -intpwd
If your using unix based then
$export oracle_sid= standby
Connect to sqlplus as sysdba on standby
sql>Startup nomount pfile= 'standby_init.ora'
sql>alter database mount standby database;
Step--7
Copy all archive log file from primary which have generated after backup you have taken to standby location.
Step-8
sql>recover standby database;
apply one by one archive as it is cancel base recovery by pressing ENTER.
You can apply logs untill an application of suggested log file yields this massage:
ora-00308:cannot open archived log '/standby archived destination path'
ora-27037 unable to obtain file status
SVR Error: 2: No such file or directory.
Dont panic. No harm is done.
If you know last archive log copied, you can type cancel after that file is applied to avoid this massage.
Enable managed Recovery:
sql>alter database recover managed standby database disconnect from session;
Go to primary database prompt
sql> alter system set log_archive_dest_2='MANDATORY service=STANDBY reopen=30';
sql> alter system set log_archive_dest_state_2=ENABLE;
Confirm Managed Recovery:
1. Check last archive log file in standby location.
2. Go to primary sql prompt
sql> alter system switch log file;
Check latest generated archive log file;
3. Go to standby archive log destination.
Check the latest archive log has received from primary or not. If it received, check alert.log of standby.
You will get this massage.
Media recovery Log 'path and name for that archive'
Media Recovery Waiting for thread 1 seq# "Next sequence no"
4. Also you can check from v$log_history
sql> select max(sequence#) from V$log_history;
This is stage where u can say you have successfully configured standby database.
Open the standby database in Read-only-mode
Go to primary database
sql>alter system switch logfile;
This time be sure that a new archive log file is created and propagated on the standby database;
Go to standby database prompt
sql>recover managed standby database cancel;
sql>alter database open read only;
Return the standby database to recovery mode
sql> shutdown immediate;
sql> startup nomount;
sql> alter database mount standby database;
sql> alter database recover managed standby database disconnect from session;
Activate the Standby Database
Stop Recovery Of Standby Database
sql> recover managed standby database cancel;
Activate the Standby Database
sql> Alter database activate standby database;
sql> shutdown;
sql> startup
Recommendations:
· Many physical changes to the primary database require a manual response on the standby database. e.g. If you will create datafile on primary at that time u have to add datafile on standby database also.
· Direct load operation on primary database logged differently in redo logs, which will invalidate the affected data blocks on the standby database.
No comments:
Post a Comment