Step to configure 11g Physical standby database.
********************************************
Oracle binaries installed with one stand alone database created.
IP: 192.168.56.2
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=prod
on Physical standby, I have installed only oracle binaries.
IP: 192.168.56.3
Primary DB_NAME=prod
Primary DB_UNIQUE_NAME=stdby
Required Parameters:
******************
DB_NAME - Must be same on primary & on all standby
DB_UNIQUE_NAME - Must be UNIQUE on all database respectively
LOG_ARCHIVE_CONFIG - This parameter includes DB_UNIQUE_NAME which are the part of DATAGUARD configuration
LOG_ARCHIVE_DEST_n - Define Local & Remote archive log file
location
LOG_ARCHIVE_DEST_STATE_n - Define state of archiving (ENABLE or DEFER)
REMOTE_LOGIN_PASSWORDFILE - Must be in EXCLUSIVE mode
FAL_SERVER - Use for archivelog gap resolution (required only in physical standby server)
DB_FILE_NAME_CONVERT - Required when directory structure is different FOR datafiles
LOG_FILE_NAME_CONVERT - Required when directory structure is different FOR logfiles
STANDBY_FILE_MANAGEMENT - Keep AUTO to create file automatically on standby
STEPS:
******
on PRIMARY database:
1) ENABLE ARCHIVELOG MODE
SELECT LOG_MODE FROM V$DATABASE;
IF ARCHIVELOG MODE is DISABLE, follow below steps to enable it.
SHUT IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2) ENABLE FORCE LOGGING MODE
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
3) VERIFY DB_NAME & DB_UNIQUE_NAME OF Primary Database
SHOW PARAMETER DB_NAME;
SHOW PARAMETER DB_UNIQUE_NAME;
4) CONFIGURE LOG_ARCHIVE_CONFIG PARAMETER
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stdby)';
5) SET ARCHIVELOG DESTINATION FOR STANDBY
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
6) SET REMOTE LOGIN PASSWORD TO EXCLUSIVE
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
7) SET FAL_SERVER PARAMETER
ALTER SYSTEM SET FAL_SERVER=stdby;
8) ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
9) CONFIGURE REQUIRED NETWORK SERVICES using "NETMGR"
10) TAKE FULL BACKUP OF PRIMARY DATABASE USING RMAN.
$ rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
11) CREATE STANDBY CONTROLFILE & PFILE
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stdcontrol.ctl';
CREATE PFILE='/tmp/initstdby.ora' FROM SPFILE;
12) MODIFY initstdby.ora file
i) change DB_UNIQUE_NAME
ii) change FAL_SERVER
iii) change log_archive_dest_n
iv) change location of controlfile
To achieve switchover & switchback (DR) activity, modify same parameters as in production into standby pfile.
13) CREATE APPROPRIATE DIRECTORY STRUCTURE AND MOVE BACKUP, PASSWORD FILE, CONTROLFILE & PFILE
NOTE:- If you fail to move password file to standby destination, you will face below error, while transporting logs to standby site.
ORA-01031: insufficient privileges
In order to rectify this error, move password file to $ORACLE_HOME/dbs location and rename with ora$ORACLE_SID.
ON PHYSICAL SERVER:
*******************
1) CREATE NETWORK SERVICES & UPDATE /etc/oratab FILE
2) START LISTENER on BOTH database
3) CREATE SPFILE FROM STANDBY PFILE
sqlplus / as sysdba
create spfile from pfile='/tmp/initstdby.ora';
4) RESTORE BACKUP ON STANDBY
rman> startup nomount;
rman> restore standby controlfile ' ';
rman> catalog start with '/u01/prod/datafiles/prod/rman';
rman> restore database;
[oracle@stand prod]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 10 22:23:53 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore standby controlfile from '/u01/prod/datafiles/prod/stndcnt.ctl';
Starting restore at 10-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/prod/datafiles/prod/stndcnt.ctl
Finished restore at 10-JUN-17
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1
RMAN> catalog start with '/u01/prod/datafiles/prod/rman';
Starting implicit crosscheck backup at 10-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 10-JUN-17
Starting implicit crosscheck copy at 10-JUN-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 10-JUN-17
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/prod/datafiles/prod/rman
List of Files Unknown to the Database
=====================================
File Name: /u01/prod/datafiles/prod/rman/o1_mf_annnn_TAG20170610T205429_dmr3sfrx_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_ncsnf_TAG20170610T205312_dmr3sdc6_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_nnndf_TAG20170610T205312_dmr3q09d_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_annnn_TAG20170610T205308_dmr3px0o_.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/prod/datafiles/prod/rman/o1_mf_annnn_TAG20170610T205429_dmr3sfrx_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_ncsnf_TAG20170610T205312_dmr3sdc6_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_nnndf_TAG20170610T205312_dmr3q09d_.bkp
File Name: /u01/prod/datafiles/prod/rman/o1_mf_annnn_TAG20170610T205308_dmr3px0o_.bkp
RMAN> RESTORE DATABASE;
Starting restore at 10-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/prod/datafiles/prod/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/prod/datafiles/prod/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/prod/datafiles/prod/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/prod/datafiles/prod/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/prod/datafiles/prod/rmantbs.dbf
channel ORA_DISK_1: reading from backup piece /u01/prod/datafiles/prod/rman/o1_mf_nnndf_TAG20170610T205312_dmr3q09d_.bkp
channel ORA_DISK_1: piece handle=/u01/prod/datafiles/prod/rman/o1_mf_nnndf_TAG20170610T205312_dmr3q09d_.bkp tag=TAG20170610T205312
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 10-JUN-17
RMAN>
RMAN> exit
5) CREATE STANDBY REDOLOG FILES on PRIMARY & STANDBY DATABASE
ALTER DATABASE ADD STANDBY LOGFILE '/u01/prod/datafiles/prod/stand_redo01.log' size 25M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/prod/datafiles/prod/stand_redo02.log' size 25M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/prod/datafiles/prod/stand_redo03.log' size 25M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/prod/datafiles/prod/stand_redo04.log' size 25M;
col member for a45
select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
3 ONLINE /u01/prod/datafiles/prod/redo03.log NO
2 ONLINE /u01/prod/datafiles/prod/redo02.log NO
1 ONLINE /u01/prod/datafiles/prod/redo01.log NO
4 STANDBY /u01/prod/datafiles/prod/stand_redo01.log NO
5 STANDBY /u01/prod/datafiles/prod/stand_redo02.log NO
6 STANDBY /u01/prod/datafiles/prod/stand_redo03.log NO
7 STANDBY /u01/prod/datafiles/prod/stand_redo04.log NO
7 rows selected.
6) Start MRP process with real time apply.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
7) Check the MRP process status.
SQL> select RECOVERY_MODE from v$archive_dest_status;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
RFS 0 IDLE
RFS 13 IDLE
RFS 0 IDLE
RFS 0 IDLE
MRP0 13 WAIT_FOR_LOG
9 rows selected.
SQL> !ps -ef | grep mrp
oracle 5815 1 0 22:31 ? 00:00:00 ora_mrp0_stdby
oracle 6064 6020 0 22:55 pts/1 00:00:00 /bin/bash -c ps -ef | grep mrp
oracle 6066 6064 0 22:55 pts/1 00:00:00 grep mrp
8) Check whether standby is SYNC with production or not ?
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
12
9) After proper configuration, to check any errors to standby site, use below query.
SQL> select error from v$archive_dest_status;
Hope you like this blog :)
No comments:
Post a Comment