Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 20 May 2012

Manually Creating Physical Standby Database in 10g R2

Here in below example we will see Manually step by step procedure how to create standby database in oracle 10g R2. Consider the following point while creating your standby using this documents:
– Database forcing is already enabled.
– Database is already in Archivelog mode.
– Listener is already configure for Primary site.
– You have already prepared the full database backup.
– Password file is created for Primary site and copies to the standby site.
ON PRIMARY DATABASE:
In primary database we should define some initialization parameter, which controls redo transport service while the database is in primary role.
Step # 1: On PRIMARY site create a service and make the entry in TNSnames.ora file through which the PRIMARY site will be connected to the Standby machine.

STANDBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = Name of Standby Machine)(PORT = 1521)) )
   (CONNECT_DATA =
     (SERVICE_NAME = sadstby)
   ) )
Step # 2: Check connectivity
SQL> connect sys/oracle@sadstby as sysdba
Connected.
Step # 3: Reconnect Create Pfile
SQL> connect sys/oracle as sysdba
Connected.
SQL> Create pfile=’d:\pfile_primary.ora’ from spfile
File Created. 
Step # 4: Modify this Pfile
db_unique_name='PRIMARY'
FAL_Client='primary'
FAL_Server='standby'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1='Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary'
Log_archive_dest_2='Service=to_standby lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby'
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names='orcl'
Standby_File_Management='AUTO'

Step # 5: Create required folder on Drive
C:\oracle\backup à for archive_log_dest_1 parameter 
Step # 6: shutdown the primary database 
Shutdown immediate;
Step # 7: 
startup the primary database with the pfile
Startup mount pfile=d:\pfile_primary.ora;
Check weather database is archive or not. If not Convert to archive mode
SQL> Archive Log list;
Step # 8  (OPTIONAL): Add Standby Redolog file for Primary
SQL> Select Gruop#, Thread#, Members, Bytes from V$log;
SQL> Select group#, member from V$logfile;
Alter database add standby logfile(E:\oracle\product\10.2.0\oradata\stby_redo01.log’) size 150m;
Alter database add standby logfile(E:\oracle\product\10.2.0\oradata\stby_redo02.log’) size 150m;
Alter database add standby logfile(E:\oracle\product\10.2.0\oradata\stby_redo03.log’) size 150m;
SQL> Select Group#, Bytes from v$standby_log;
Step # 9: Shutdown DB and Copy all datafile and standby redo log
Shutdown immediate
Copy all datafiles and standby redologs from primary database to standby database. 
Step # 10: Start Primary DB with Pfile
Startup mount pfile=d:\pfile_primary.ora
Step # 11: Create Standby Controlfile for Primary and copy to Standby site
Alter database create standby controlfile as 'C:\oracle\backup\stby_control.ctl';
Now copy this standby control file to the standby site where other database file are located. Then delete the previous control files and paste this controlfile by renaming these files to control01.ctl, control02.ctl, control03.ctl. 
Step # 12: Now Create spfile from Pfile and Restart DB 
Create spfile from pfile=‘D:\pfile_primary.ora’;
Shutdown immediate; 
Startup;
ON SECONDARY DATABASE:
Step # 1: Create Physical Folder on Standby site
Create physical folder on C:\ drive  C:\oracle\backup 
Step # 2 : Create Pfile for Standby DB
SQL> Create pfile='d:\pfile_standby.ora' from spfile 
Step # 3 : Shutdown DB and Modify the Pfile
Shutdown the database; 
db_unique_name='STANDBY'
FAL_Client='standby'
FAL_Server='primary'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1=‘Location=c:\oracle\backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby’
Log_archive_dest_2=‘Service=to_primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names=‘ORCL’
Standby_File_Management=‘AUTO’
lock_name_space=standby
# db_file_name_convert: do not need for same directory structure
db_file_name_convert 'Oracle_home\Old_path' to 'Oracle_Home\New Path';
# log_file_name_convert: do not need for same directory structure
Log_file_name_convert 'Oracle_home\Old_log_path' to 'Oracle_Home\New_log_path';
Step # 4 : Startup the Standby with this Pfile and Create spfile
SQL>STARTUP MOUNT PFILE=‘D:\pfile_standby.ora’
SQL> create spfile from pfile=‘D:\pfile_standby.ora’;File created.
Step # 5: Restart the Standby DB 
SQL>Shutdown immediate
SQL>Startup mount;
ON PRIMARY DATABASE AGAIN:
SQL> show parameter log_archive_dest_satate_2
SQL>Alter system set log_archive_dest_satate_2=enable scope=both;
System altered
For checking log sequence number 
SQL>Archive log list; 
SQL>Alter system switch logfile ;
SQL>Select status, error from v$archive_dest where dest_id=2
ON STANDBY DATABASE AGAIN:
To apply logs start the MPR background process by executing the following statement
SQL>alter database recover managed standby database disconnect;
Database altered
SQL> select name, applied, archived from v$archived_log;
SQL> select database_role from v$database;
Manual Switchover: on Primary Database
SQL>connect sys/oracle@to_primary as sysdba
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN
ON Standby Database
SQL>connect /@sadstby as sysdba
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ON previous primary:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> alter database recover managed standby database disconnect
New Primary (previous stand by)
SQL> ALTER DATABASE OPEN;
FAILOVER:
failover is when the primary database is unavailable. Failover is performed only in the event of a catastrophic failure of the primary database, and the failover results in a transition of a standby database to the primary role. The database administrator can configure Data Guard to ensure no data loss. We performed a failover in our Disaster Recovery scenario. Where the primary database was taken offline (as unavailable), and standby database was activated as Primary Database.
The Standby database will now be activated as the new Primary database.
SQL> conn sys/oracle@orcl3 as sysdba
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
Checking whether Standby Database Working Properly:
First check primary database weather any job is still running.
SQL>Desc  v$database 
SQL>Select switchover_status from v$database ;
SQL>select username, program from v$session Where sid in (select
distinct sid from v$MYSTAT) AND TYPE-‘user’;
Sql>Alter System Set Job_Queue_Processes=0;

0 comments:

Post a Comment