configuration of
physical standby database in oracle 11g
Pre-requisite
1)create 2 vm machine having OEL5 and oracle 11g install
2)let the machine name,ip and database is like below
Machine name:- lnx01.oracle.com (Primary) IP:-
192.168.100.101
Machine name:- lnx02.oracle.com(Standby) IP:-
192.168.100.102
Database name in primary side:- hrms
Primary side
==========
$export ORACLE_SID=hrms
$sqlplus / as sysdba
sql>select name,open_mode,log_mode,force_logging from
v$database;
sql>alter database force logging;
sql>create pfile from spfile;
sql>shut immediate;
sql>exit
$cd $ORACLE_HOME/dbs
$mv spfilehrms.ora spfilehrms.ora.bkp
$cd
$export ORACLE_SID=hrms
$sqlplus / as sysdba
sql>startup
sql>show parameter spfile
sql>exit
$export ORACLE_SID=hrms
$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
$cd $ORACLE_HOME/dbs
$vi inithrms.ora
go to the last and add the below parameter
###########DATAGURAD
PARAMTERS 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_hrms_stdby
DB_UNIQUE_NAME=hrms_stdby'
log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'
db_unique_name=hrms
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
########Standby
Role Initialization Parameters
fal_server=to_hrms_stdby
standby_file_management=auto
:wq
CONFIGURE THE LISTENER IN PRIMARY SIDE
$cd $ORACLE_HOME/network/admin
$vi listener.ora
[oracle@lnx01 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))
#(ADDRESS =
(PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC=
(SID_NAME=hrms)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
:wq
$vi tnsnames.ora
[oracle@lnx01 admin]$ cat tnsnames.ora
TO_HRMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))
)
(CONNECT_DATA =
(SERVICE_NAME =
hrms)
)
)
TO_HRMS_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))
)
(CONNECT_DATA =
(SERVICE_NAME =
hrms_stdby)
)
)
[oracle@lnx01 admin]$
:wq
CONFIGURE NETWORK SERVICE IN STANDBY SIDE
[oracle@lnx02 admin]$ cat listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_home/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =
/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME =
hrms_stdby)
)
)
ADR_BASE_LISTENER1 = /u01/app/oracle
[oracle@lnx02 admin]$
:wq
[oracle@lnx02 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_home/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TO_HRMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 7777))
)
(CONNECT_DATA =
(SERVICE_NAME =
hrms)
)
)
TO_HRMS_STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 7777))
)
(CONNECT_DATA =
(SERVICE_NAME =
hrms_stdby)
)
)
[oracle@lnx02 admin]$
:wq
Primary side
======
$cd $ORACLE_HOME/dbs
$scp inithrms.ora lnx02:/home/oracle
password:oracle
Standby side
=========
$cp inithrms.ora inithrms_stdby.ora
$mv inithrms_stdby.ora $ORACLE_HOME/dbs
$cd $ORACLE_HOME/dbs
$vi inithrms_stdby.ora
[oracle@lnx02 dbs]$ cat inithrms_stdby.ora
hrms.__db_cache_size=520093696
hrms.__java_pool_size=16777216
hrms.__large_pool_size=16777216
hrms.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from
environment
hrms.__pga_aggregate_target=520093696
hrms.__sga_target=771751936
hrms.__shared_io_pool_size=0
hrms.__shared_pool_size=201326592
hrms.__streams_pool_size=0
*.audit_file_dest='/u02/app/oracle/admin/hrms/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/hrms/control01.ctl','/u02/app/oracle/fast_recovery_area/hrms/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='hrms'
*.db_recovery_file_dest='/u02/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hrmsXDB)'
*.memory_target=1284505600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
################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_hrms_stdby
DB_UNIQUE_NAME=hrms_stdby'
log_archive_config='DG_CONFIG=(hrms,hrms_stdby)'
db_unique_name=hrms_stdby
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
###############STANDBY ROLE
PARAMETER############################
fal_server=to_hrms_stdby
standby_file_management=auto
db_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')
log_file_name_convert=('/u01/app/oracle/hrms/','/u02/app/oracle/hrms/')
:wq
lnx02#
mkdir -p /u02/app/oracle
lnx02#
mkdir -p /u02/app/oracle/hrms
lnx02#
mkdir -p /u02/app/oracle/fast_recovery_area
lnx02#
mkdir -p /u02/app/oracle/fast_recovery_area/hrms
lnx02#
chown -R oracle:oinstall /u02/app/oracle/
lnx02#
chown -R oracle:oinstall /u02/app/oracle/hrms/
lnx02#
chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/
lnx02#
chown -R oracle:oinstall /u02/app/oracle/fast_recovery_area/hrms/
lnx02#
chmod -R 775 /u02/app/oracle/
lnx02#
chmod -R 775 /u02/app/oracle/hrms/
lnx02#
chmod -R 775 /u02/app/oracle/fast_recovery_area/
lnx02#
chmod -R 775 /u02/app/oracle/fast_recovery_area/hrms/
lnx02#
/lnx02$
cd /u02/app/oracle/hrms/
lnx02$
mkdir adump
##############CONFIGURE
PASSWORD FILE BOTH PRIMARY AND STANDBY
[oracle@lnx02
dbs]$ orapwd file=orapwhrms_stdby password=rac entries=3 force=y
[oracle@lnx02
dbs]$
[root@lnx01
~]# su - oracle
[oracle@lnx01
~]$ cd $ORACLE_HOME/dbs
[oracle@lnx01
dbs]$ orapwd file=orapwhrms password=rac entries=3 force=y
[oracle@lnx01
dbs]$
###########START
THE PRIMARY DATABASE
[oracle@lnx01
~]$ export ORACLE_SID=hrms
[oracle@lnx01
~]$ sqlplus / as sysdba
SYS>startup
##########START
THE STANDBY DATABASE
[oracle@lnx02
~]$ export ORACLE_SID=hrms_stdby
[oracle@lnx02
~]$ sqlplus / as sysdba
SQL>
startup nomount
SYS>select
instance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------
------------
hrms_stdby STARTED
[oracle@lnx01
~]$ export ORACLE_SID=hrms
[oracle@lnx01
~]$ rman target sys/rac auxiliary sys/rac@to_hrms_stdby
Recovery
Manager: Release 11.2.0.2.0 - Production on Wed Dec 19 18:04:35 2012
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: HRMS (DBID=850606497)
connected
to auxiliary database: HRMS (not mounted)
RMAN>
RMAN>
duplicate target database for standby from active database;
CREATION OF STANDBY REDO LOG FILE IN BOTH THE SIDE
PRIMARY SIDE
==========
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo1.log') size 50m;
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo2.log') size 50m;
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo3.log') size 50m;
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo4.log') size 50m;
sql>select member from v$logfile where type = 'STANDBY';
sql>select member from v$logfile;
STANDBY SIDE
============
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo1.log') size 50m;
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo2.log') size 50m;
sql>alter database add standby logfile ('/u01/app/oracle/hrms/standby_redo3.log')
size 50m;
sql>alter database add standby logfile
('/u01/app/oracle/hrms/standby_redo4.log') size 50m;
sql>select member from v$logfile where type = 'STANDBY';
sql>select member from v$logfile;
Standby side
sql>select name,open_mode from v$database;
sql>archive log list
sql>alter database recover managed standby database
disconnect from session;
sql>select sequence#,first_time,next_time,applied from
v$archived_log order by sequence#;
Primary side
sql>select name,open_mode from v$database;
sql>archive log list
sql>select sequence#,first_time,next_time,applied from
v$archived_log order by sequence#;
THE SEQUENCE AND APPLIED COLUMN SHOULD BE MATCHED IN BOTH
THE SIDE OF THE SERVER.
No comments:
Post a Comment