Some key points before proceeding with the physical standby
setup.
§ Primary
database should be in archivelog mode.
§ Forced
Logging is on in Primary database (sql>alter database force logging;)
§ Initialization
parameter “db_name” should be same on both primary and standby database.
§ Initialization
parameter “db_unique_name” should be different on primary and standby
databases.
Primary Database : cwgdb
Standby Database : dupdb
1.
Check if the primary database is using the password file or not. If not, then
create one as below.
orapwd file=$ORACLE_HOME/dbs/orapwcwgdb password=rac123 force=y
2. Add the following parameters in the initialization parameter
file of the primary database.
initprim.ora
cwgdb.__db_cache_size=150994944
cwgdb.__java_pool_size=4194304
cwgdb.__large_pool_size=4194304
cwgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set
from environment
cwgdb.__pga_aggregate_target=171966464
cwgdb.__sga_target=251658240
cwgdb.__shared_io_pool_size=0
cwgdb.__shared_pool_size=83886080
cwgdb.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cwgdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/cwgdb/control01.ctl'
,'/u01/app/oracle/archivelog/cwgdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cwgdb'
*.db_recovery_file_dest='/u01/app/oracle/archivelog'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=cwgdbXDB)'
#*.local_listener='CWGDB'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#------------------------------------------------------------------------------
*.log_archive_config='DG_CONFIG=(cwgdb,dupdb)'
*.log_archive_dest_1='LOCATION=/u01/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cwgdb'
*.log_archive_dest_2='SERVICE=dupdb
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dupdb LGWR ASYNC'
#*.standby_archive_dest='/u01/app/oracle/archivelog/CWGDB/archivelog'
*.standby_file_management=auto
*.db_file_name_convert=('/oracle/app/oracle/datafiles','/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles','/u02/datafile')
*.log_file_name_convert=('/oracle/app/oracle/datafiles','/u01/app/oracle/oradata/cwgdb')
#*.remote_archive_enable=true
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=dupdb
*.fal_client=cwgdb
*.db_unique_name=cwgdb
3. Setup the connectivity
(listener.ora and tnsnames.ora) for the primary and standby databases.
Primary:
listener.ora
file
# listener.ora Network
Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
cwgdb=
# Generated by Oracle configuration tools.
cwgdb=
(DESCRIPTION_LIST =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.220)(PORT=1521))
)
)
)
SID_LIST_cwgdb=
(SID_LIST=
(SID_DESC=
# (SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
#(PROGRAM = extproc)
# (SID_DESC=
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME=cwgdb)
)
)
tnsnames.ora file
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
# Generated by Oracle configuration tools.
dupdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 172.16.1.221)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
dupdb)
)
)
cwgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 172.16.1.220)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
cwgdb)
)
)
Standby:
Listener.ora
file
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
# Generated by Oracle configuration tools.
dupdb=
(DESCRIPTION_LIST =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.221)(PORT=1521))
)
)
)
SID_LIST_dupdb=
(SID_LIST=
(SID_DESC=
# (SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
#(PROGRAM = extproc)
# (SID_DESC=
# (ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME=dupdb)
)
)
tnsnames.ora file
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
# Generated by Oracle configuration tools.
cwgdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 172.16.1.220)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
cwgdb)
)
)
dupdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL
= TCP)(HOST = 172.16.1.221)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
dupdb)
)
)
4. Add the following
parameters in the initialization parameter file of the standby database
initstnd.ora
dupdb.__db_cache_size=150994944
dupdb.__java_pool_size=4194304
dupdb.__large_pool_size=4194304
dupdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from
environment
dupdb.__pga_aggregate_target=171966464
dupdb.__sga_target=251658240
dupdb.__shared_io_pool_size=0
dupdb.__shared_pool_size=83886080
dupdb.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/dupdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/app/oracle/oradata/dupdb/stdby.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='cwgdb'
#*.db_recovery_file_dest='/oracle/app/oracle/archivelog'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/oracle/app/oracle'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=cwgdbXDB)'
#*.local_listener='CWGDB'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#------------------------------------------------------------------------------
*.log_archive_config='DG_CONFIG=(cwgdb,dupdb)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dupdb'
*.log_archive_dest_2='SERVICE=cwgdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)
DB_UNIQUE_NAME=cwgdb LGWR ASYNC'
#*.standby_archive_dest='/u01/app/oracle/archivelog/CWGDB/archivelog'
*.standby_file_management=auto
*.db_file_name_convert=(
'/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles','/u02/datafile','/oracle/app/oracle/datafiles')
*.log_file_name_convert=(
'/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles')
#*.remote_archive_enable=true
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=cwgdb
*.fal_client=dupdb
*.db_unique_name=dupdb
5. Now copy the password
file of the primary database “orapwcwgdb” located at $ORACLE_HOME/dbs to
the standby server location $ORACLE_HOME/dbs and rename the file as “orapwdupdb”.
Note that the format of the password file in Windows is as
pwd<SID>.ora (Example: pwdPRIM.ora)
6. assign a location to take the backup in primary database:
Rman >show all
Rman > CONFIGURE
CHANNEL DEVICE TYPE DISK FORMAT
'/u01/app/oracle/backup/sbi_%U';
7. Take the full backup from primary database:
Rman> backup
database plus archivelog ;
8.now go to mount stage and take the backup of standby
controlfile :
Sql>startup
mount
Sql> alter database
create standby controlfile as '/u01/app/oracle/backup/stdby.ctl;
9. Now connect to the
Primary database as target database and standby database as auxiliary instance
through RMAN. Make sure that the Primary database is open and the standby
database is in nomount stage (STARTED).
rman target sys/<password>@prim
auxiliary sys/<password>@stnd
Now perform the duplicate operation to create the standby
database.
rman>duplicate target database for standby
from active database nofilenamecheck;
10. Once the duplication is
completed, close the RMAN prompt and connect to the standby database through
SQL.
sqlplus sys/<password>@stnd as sysdba
Check the status of the standby database by making sure it is in
mount stage.
sql>select
status,instance_name,database_role from v$instance,v$database;
11. Now start the managed
recovery process on the standby database.
sql>alter database recover managed standby
database disconnect from session;
12. Now check if the managed
recovery process (MRP) has been started on the standby database or not.
sql>select process,status,sequence# from
v$managed_standby;
Example:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS
STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 39
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 39
Here, the MRP has been started and is waiting for the log
sequence# 39. If MRP is not started, then the above query would not show up the
MRP0 under the process column.
13. On the primary
database, perform a few log switches and check if the logs are applied to the
standby database.
sqlplus sys/<password>@prim as sysdba
sql>alter system switch logfile;
sql>select max(sequence#) from
v$archived_log;
Example:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
38
————–
38
sqlplus sys/<password>@stnd as sysdba
sql>select thread#,max(sequence#) from
v$archived_log where applied=’YES’ group by thread#;
Example:
SQL> select thread#,max(sequence#) from v$archived_log where
applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 38
———- ————–
1 38
Here, the maximum sequence# generated on the Primary database is
38 and the maximum sequence# applied on the standby database is also 38 which
means that the standby database is in sync with the primary database.
Here you go !!!
NOTE:
ORA-10456: cannot open
standby database; media recovery session may be in progress
รจ SQL> select * from hr.aa;
select * from hr.aa
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NOIDA MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
select * from hr.aa
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NOIDA MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.
PREPARED
BY ANAND.J
No comments:
Post a Comment