Please send your Questions & Answers or Feedback to "dvmadhavreddy@javabook.org"

Please send your Questions & Answers or Feedback to "mohan@javabook.org"

Data Guard

oracle@venu1> export ORACLE_SID=venu
oracle@venu1> sqlplus / as sysdba
SQL> startup
SQL> select name.open_mode,log_mode,force_logging from v$database;
SQL> alter database force logging;
SQL> show parameter spfile;
SQL> create pfile from spfile;
SQL> shut immediate
SQL> exit
oracle@venu1> cd $ORACLE_HOME/dbs
oracle@venu1 dbs> mv spfilehrms.ora spfilehrms.ora.bkp
oracle@venu1 dbs> cd
oracle@venu1> export ORACLE_SID=hrms
oracle@venu1> sqlplus / as sysdba
SQL> startup
SQL> show parameter spfile
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> archive log list
SQL> shut immediate
SQL> exit
Always sender is tnsnames.ora
"db_unique_name" is primary
"DB_UNIQUE_NAME" is standby
oracle@venu1> cd $ORACLE_HOME/dbs
oracle@venu1 dbs> vi initvenu.ora
############data guard parameter for primary role initialization parameters############
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
log_archive_dest_2='SERVICE=to_venu_stdby DB_UNIQUE_NAME=venu_stdby'
log_archive_config='DG_CONFIG=(venu,venu_stdby)'
db_unique_name=venu
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
###########standby role initialization parameters####################
fal_server=to_venu_stdby
standby_file_management=auto

if manual, it creates the structural objects with different name and in different location i.e. in dbs directory.

#################configure the listener at primary side#######################
oracle@venu1> cd $ORACLE_HOME/network/admin
oracle@venu1 admin> vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = venu1.oracle.com)(PORT = 7777))
#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=venu)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
)
)
ADR_BASE_LISTENER = /u01/app/oracle

oracle@venu1 admin> cd
oracle@venu1>
################configuring tnsnames.ora at primary side################
oracle@venu1> cd $ORACLE_HOME/network/admin
oracle@venu1 admin> vi tnsnames.ora
#####FOR LOCAL
TO_VENU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = venu1.oracle.com) (PORT=7777))
)
(CONNECT_DATA =
(SERVICE_NAME = venu)
)
)

######FOR SWITCH OVER
TO_VENU_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = venu2.oracle.com) (PORT=7777))
)
(CONNECT_DATA =
(SERVICE_NAME = venu_stdby)
)
)
oracle@venu1 admin> cd
oracle@venu1>
root@venu1> su - oracle
oracle@venu1>  cd $ORACLE_HOME/dbs
oracle@venu1 dbs> scp initvenu.ora venu2:/home/oracle/
oracle@venu2's password :
oracle@venu1 dbs>

############standby side###############
root@venu1> ssh venu2
root@venu2> su - oracle
oracle@venu2> cp initvenu.ora initvenu_stdby.ora
oracle@venu2> mv initvenu_stdby.ora $ORACLE_HOME/dbs
oracle@venu2> cd $ORACLE_HOME/dbs
oracle@venu2 dbs> vi initvenu_stdby.ora
##########dataguard parameters for primary role##################
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID FOR=(ALL_LOGFILES,ALL_ROLES)'
log_archive_dest_2='SERVICE=to_venu DB_UNIQUE_NAME=venu'
log_archive_config='DG_CONFIG=(venu,venu_stdby)'
db_unique_name=venu_stdby
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
##########standby role initialzation parameters#############
fal_server=to_venu
standby_file_management=auto
db_file_name_convert=('/u02/app/oracle/venu','/u03/app/oracle/venu/')
log_file_name_convert=('/u02/app/oracle/venu','/u03/app/oracle/venu/')

oracle@venu2 dbs> cd
oracle@venu2>
root@venu2> mkdir -p /u03/app/oracle
root@venu2> mkdir -p /u03/app/oracle/venu
root@venu2> mkdir -p /u03/app/oracle/fast_recovery_area
root@venu2> mkdir -p /u03/app/oracle/fast_recovery_area/venu

root@venu2> chown -R oracle:oinstall /u03/app/oracle
root@venu2> chown -R oracle:oinstall /u03/app/oracle/venu
root@venu2> chown -R oracle:oinstall /u03/app/oracle/fast_recovery_area
root@venu2> chown -R oracle:oinstall /u03/app/oracle/fast_recovery_area/venu

root@venu2> chmod -R 775 /u03/app/oracle
root@venu2> chmod -R 775 /u03/app/oracle/venu
root@venu2> chmod -R 775 /u03/app/oracle/fast_recovery_area
root@venu2> chmod -775 /u03/app/oracle/fast_recovery_area/venu

root@venu2> su - oracle
oracle@venu2> cd /u03/app/oracle/venu/
oracle@venu2 venu> mkdir adump
oracle@venu2 venu>

##################configuring listener and tnsnames########################
oracle@venu2> netca
oracle@venu2> cd $ORACLE_HOME/network/admin
oracle@venu2 admin> vi listener.ora

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = venu2.oracle.com)(PORT = 7777))
#(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME=venu_stdby)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home)
)
)
ADR_BASE_LISTENER1 = /u01/app/oracle


oracle@venu2> cd $ORACLE_HOME/network/admin
oracle@venu2 admin> vi tnsnames.ora

TO_VENU_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = venu2.oracle.com) (PORT=7777))
)
(CONNECT_DATA =
(SERVICE_NAME = venu_stdby)
)
)


TO_VENU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = venu1.oracle.com) (PORT=7777))
)
(CONNECT_DATA =
(SERVICE_NAME = venu)
)
)

oracle@venu2 admin> cd
oracle@venu2>
########configure password file both primary and standby############
oracle@venu2 dbs> orapwd file=orapwvenu_stdby password=rac entries=3 force=y
oracle@venu1 dbs> orapwd file=orapwvenu password=rac entries=3 force=y

#########start the primary database#############
oracle@venu1> export ORACLE_SID=venu
oracle@venu1> sqlplus / as sysdba
SYS> startup
#########start the standby database###########
oracle@venu2> export ORACLE_SID=venu_stdby
oracle@venu2> sqlplus / as sysdba
SYS> startup nomount
SYS> select instance_name,status from v$instance;

oracle@venu1> export ORACLE_SID=venu
oracle@venu1> rman target sys/rac auxiliary sys/rac@to_venu_stdby
RMAN>duplicate target database for standby for active datbase;
###open standby alert log#########

@@@@SNAPSHOT STANDBY@@@@@
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>alter database recover managed standby database cancel;
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>alter database convert to snapshot standby;
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>select name,open_mode from v$database;
SYS>alter database open;
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>create user u1 identified by u1;
SYS>grant connect,resource to u1;
SYS>conn u1/u1;
u1>create table a(a number);
u1>insert into a values(1);
u1>commit;
u1>conn / as sysdba
SYS>select * from all_users;
SYS>shut immediate
SYS>startup mount
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>alter database convert to physical standby;
SYS>shut immediate
SYS>startup mount
SYS>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
SYS>archive log list
SYS>alter database open;
SYS>conn u1/u1

@@@switchover@@@
venu1>alter database commit to switchover to physical standby;
venu1>shut immediate
venu2>alter database commit to switchover to primary standby;
venu2>shut immediate
venu2>startup
venu1>export ORACLE_SID=venu
venu1>sqlplus / as sysdba
venu1>startup mount
venu1>select open_mode,name,database_role,protection_mode,flashback_on from v$database;

@@@maximum protection to maximum availability@@@
venu1>shut immediate
venu1>startup mount
venu1>alter database set standby database to maximise availability;
venu1>alter database open;
venu1>select open_mode,name,database_role,protection_mode,flashback_on,controlfile_type from v$database;

@@@maximum  availability to maximum performance@@@
venu1>shut immediate
venu1>startup mount
venu1>alter database set standby database to maximise performace;
venu1>select open_mode,name,database_role,protection_mode,flashback_on,controlfile_type from v$database;

venu2>select open_mode,name,database_role,protection_mode,flashback_on from v$database;
In 10g, it takes last destination as FRA.
In 11g, it takes first destination as FRA.

Related Posts Plugin for WordPress, Blogger...

Flag Counter