How to prepare Oracle Physical Standby DB Step by Step
Multiple Standby Databases Dataguard Oracle
Primary db_unique_name= KARDBPRD01
Standby01 db_unique_name= KARDBPRD02
Standby02 db_unique_name= KARDBPRD03
Preparation Task.
PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.
STEP 1:
create
spfile, if it does not exist.
create spfile from
pfile;
Enable archivelog, if this is not the case.
SQL>shutdown
immediate;
SQL>startup mount;
SQL>alter database
archivelog;
SQL> alter
database open;
SQL>archive log
list;
Enable Force Logging on primary.
SQL> select
FORCE_LOGGING from v$database;
SQL> ALTER
DATABASE FORCE LOGGING;
You can disable force logging as follow.
SQL> ALTER DATABASE no force logging;
STEP 2:
Configure a Standby
Redo Log on primary. The rule is to have n+1 groups for standby.
On PrimaryDB:
NOTE: Size of redo on
standby must be same as primary. You can find the size of redo log as follow. Create
the same number of SRLs for both production and standby databases. The SRLs
should exist on both production and standby databases.
Use the below query to
calculate the estimated size of the redo log file.
select TARGET_MTTR "trgt_mttr", ESTIMATED_MTTR "est_mttr", WRITES_MTTR
"wrt_mttr",
WRITES_LOGFILE_SIZE
"wrt_lg_size", OPTIMAL_LOGFILE_SIZE "opt_lg_size" from
v$instance_recovery ;
Size of redo log file
in bytes.
select bytes from
v$log;
The Size should match on both side of the ONLINE LOGFILES.
set linesize 200
col MEMBER format a50
select lg.group#,lg.thread#,sequence#,lg.bytes/1024/1024
"sizeMB",lg.archived,lg.status,le.type,le.member from v$log
lg,v$logfile le
where
lg.group#=le.group#
/
The Size should match both side of the STANDBY LOGFILES.
select
sg.group#,sg.thread#,sg.sequence#,sg.bytes/1024/1024 "sizeMB",sg.archived,sg.status,le.type,le.member
from V$STANDBY_LOG
sg,v$logfile le
where
le.group#=sg.group#
/
You can check status of the
logfile.
select group# ||' -
'|| type ||' - '|| member from v$logfile;
You can create standby redo log file on primary DB as follow.
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 40 '+DATA/redo40.log' size 2048m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 41 '+DATA/redo41.log' size 2048m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 42 '+DATA/redo42.log' size 2048m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 43 '+DATA/redo43.log' size 2048m;
ALTER DATABASE ADD
STANDBY LOGFILE GROUP 44 '+DATA/redo44.log' size 2048m;
STEP 3:
Modify the primary initialization parameter for dataguard on primary.
Definition of Important
parameters.
LOG_ARCHIVE_DEST_3= Is ignored; valid only when (primary) is running in
the standby role.
STANDBY_ARCHIVE_DEST= Is ignored; valid only when (primary) is running
in the standby role.
LOG_ARCHIVE_DEST_2= Directs transmission of redo data to the remote logical
standby destination.
LOG_ARCHIVE_CONFIG = specify all databases in data guard configuration.
it is list of all DB_UNIQUE_NAME separated by comma
LOG_ARCHIVE_DEST_n = specify where redo data are archived
LOG_ARCHIVE_DEST_1 = valid for both roles. Specify where to store redo
data generated by the primary database in local archived redo log files
LOG_ARCHIVE_DEST_2 = is valid only for the primary role. This
destination transmits redo data to the remote physical standby destination
FAL_SERVER= Add both standby server, that oracle knows from where to get
arch files in case of switchover.
FAL_SERVER=KARDBPRD2,KARDBPRD3
FAL_CLIENT= This is always the Current DB server. The parameter is ignored
when the DB is in Primary mode.
FAL_CLIENT=KARDBPRD1
Db_name=KARDBPRD # Same across
all DB’s
Db_unique_name=KARDBPRD1 # unique on each databases
Starting with the Standby setup.
On Primary DB:
alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(KARDBPRD01, KARDBPRD02, KARDBPRD03)';
LOG_ARCHIVE_DEST_1 is important,
if the DB get primary.
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=KARDBPRD1';
LOG_ARCHIVE_DEST_2 is related to first standby DB.
alter system set LOG_ARCHIVE_DEST_2='SERVICE=KARDBPRD2 LGWR async
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=KARDBPRD2';
LOG_ARCHIVE_DEST_3 is for the second standby DB
alter system set
LOG_ARCHIVE_DEST_3='SERVICE=KARDBPRD3 LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=KARDBPRD3';
You can set the parameter to defer. You need to set it later to enable, otherwise we see error in the alert logfile
alter system set
log_archive_dest_state_2=defer;
alter system set
log_archive_dest_state_3=defer;
Modify parameter to convert to data file so that oracle knows where to create datafile.
Example:
On_Primary:
*.LOG_FILE_NAME_CONVERT='+DATA/<standby unique name>','+DATA/<primary unique name>'
On_standby:
*.LOG_FILE_NAME_CONVERT=’+DATA/<primary unique name>’,’DATA/<standby unique name>’
alter system set
DB_FILE_NAME_CONVERT='standby location','primary location' scope=spfile;
alter system set
DB_FILE_NAME_CONVERT=
,'/data/KARDBPRD2/01/','+DATA','/data/KARDBPRD3/02/','+DATA'
scope=spfile;
alter system set
LOG_FILE_NAME_CONVERT='/data/ KARDBPRD2/redo/','+DATA', '/data/KARDBPRD3/redo/','+DATA'
scope=spfile;
You may need to check
following parameter as well as.
Change the value
according to your environment.
SELECT name, value
FROM v$parameter WHERE name LIKE 'db_recovery%';
ALTER SYSTEM SET
db_recovery_file_dest='/db/dumps' SCOPE=BOTH;
ALTER SYSTEM SET
db_recovery_file_dest_size=300g SCOPE=BOTH;
alter system set FAL_SERVER=kardbprd2,kardbprd3;# standbydb‘s
alter system set FAL_CLIENT=kardbprd1; # it's a server
self
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
SCOPE=SPFILE;
Create parameter file from the primary and copy it to standby and make the necessary changes.
create
pfile='/tmp/initstdby.ora' from spfile;
You can create multiple service_name, if needed.
alter system set
service_names='kardbprd','kardbprd.th.com','kardbprd1','kardbprd1.th.com','kardbprd2','kardbprd3' scope = both;
STEP 4:
Establish the connectivity,
Create net services on
both primary and standby
On primary server:
KARDBPRD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb001.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd1)
(SERVER = DEDICATED)
)
)
KARDBPRD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb002.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd2)
(SERVER = DEDICATED)
)
)
KARDBPRD3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb03.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd3)
(SERVER = DEDICATED)
)
)
STEP 5:
On Standby DB create a PW file.
11g:
orapwd
file='$ORACLE_HOME/dbs/orapwKARDBPRD1' password=xxxxxx entries=10;
12c:
orapwd
file='$ORACLE_HOME/dbs/orapwKARDBPRD1' entries=10 force=y password='xxxxxx'
force=y format=12
On standby server.
Edit a tnanames.ora
file.
KARDBPRD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb001.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd1)
(SERVER = DEDICATED)
)
)
KARDBPRD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb002.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd2)
(SERVER = DEDICATED)
)
)
KARDBPRD3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
kardb03.us.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = kardbprd3)
(SERVER = DEDICATED)
)
)
Make the necessary
change on the copied initstdby.ora file on standby1.
On Standby 1.
LOG_ARCHIVE_DEST_STATE_2
and LOG_ARCHIVE_DEST_STATE_3=Is ignored; valid only when “kardbprd” is running
in a primary role.
db_name=kardbprd
db_unique_name=kardbprd2
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_file_management=AUTO
FAL_SERVER=kardbprd1,kardbprd3;
#
other server’s
FAL_CLIENT=kardbprd2; # it's
a server DB self
alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(KARDBPRD01, KARDBPRD02, KARDBPRD03)';
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=KARDBPRD2';
Related to standby
alter system set LOG_ARCHIVE_DEST_2='SERVICE=kardbprd1
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kardbprd1';
alter system set
log_archive_dest_state_2=defer/enable;
Related to standby
alter system set LOG_ARCHIVE_DEST_3='SERVICE=kardbprd3
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kardbprd3';
alter system set
log_archive_dest_state_3=defer/enable;
Example: alter system set DB_FILE_NAME_CONVERT='primary location','standby location' scope=spfile;
alter system set
DB_FILE_NAME_CONVERT='+DATA','/db/data/01/' scope=spfile;
alter system set
LOG_FILE_NAME_CONVERT='+DATA','/db/data/redo/' scope=spfile;
You may need set also the following parameter. Otherwise, you could see the following error.
ORACLE error from
target database:
ORA-38757: Database
must be mounted and not open to FLASHBACK.
-- check the value first:
SELECT name, value FROM v$parameter WHERE name
LIKE 'db_recovery%';
-- change the value
ALTER SYSTEM SET
db_recovery_file_dest='/db/dumps' SCOPE=BOTH;
ALTER SYSTEM SET
db_recovery_file_dest_size=500g SCOPE=BOTH;
create spfile, if it doesn't exist
create spfile from pfile='xxx/xxx';
startup nomount;
Before you start RMAN script make sure the connections works.
tnsping KARDBPRD1
tnsping KARDBPRD2
tnsping KARDBPRD3
tnsping RCATDB #this is catalog DB, if you use.
sqlplus sys/password@KARDBPRD1 as sysdba
sqlplus sys/password@KARDBPRD2 as sysdba
sqlplus sys/password@KARDBPRD3 as sysdba
If not make sure
orapwd file created.
orapwd file='$ORACLE_HOME/dbs/orapwKARDBPRD1' entries=10
force=y password='xxxxx' force=y format=12
NOTE: If you regularly backup your primary DB, then you need to just create controlfile for standby as follow and copy it to stanyby DB' in the same location.
run{
sql 'alter system archive log current';
backup current controlfile for standby format '/fast_recovery_area/KARDBPRD1/backup/STA_CONT%d.%p.%s.%T.%t';
}
You can restore controlfile as follow after restore the DB backup:
rman target /
RMAN> restore standby controlfile from '/fast_recovery_area/TSTDB/backup/full_backup/STA_CONTxxxxx'
Run the RMAN script.
The script need to start from Standby DB.
#!/bin/ksh
# ----------------------------------------------
export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
#
----------------------------------------------
rman TARGET sys/xxxxx@kardbprd1
AUXILIARY / log=/rman/duplicatedb.log cmdfile "/rman/duplicatedb.standby"
If you use catalog DB
to prepare standby DB. The command should looks like as follow. You need to make
sure to connect your target DB and catalog.
$ORACLE_HOME/bin/rman target / catalog
username/password@RCATDB auxiliary / log=/rman/duplicatedb.log cmdfile "/rman/duplicatedb.standby"
You can generate a script as follow.
select 'set newname for tempfile '||file_id||' to
'||file_name||' ;'from dba_temp_files order by file_id;
select 'set newname for datafile '||file_id||' to
'||file_name||' ;'from dba_data_files order by file_id;
The output looks like
as follow, If you want to change the dbf file name or destination you need to
add set newname commands. Example.
set newname for tempfile 1 to '+DATA/temp01.dbf';
…
…
Otherwise, you can use simple rman commands.
RUN
{
allocate auxiliary
channel c1 type disk;
allocate auxiliary
channel c2 type disk;
allocate auxiliary
channel c3 type disk;
allocate auxiliary
channel c4 type disk;
duplicate target
database for standby dorecover;
#duplicate target
database for standby dorecover nofilenamecheck;
switch datafile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
Start the script on standby DB.
nohup ./create_physical_standby.sh
>/logs/rman/duplicatedbStandby.log &
If you get such type
of error. You need to fix this as follow.
RMAN-06053: unable to
perform media recovery because of missing log
RMAN-06025: no backup
of log thread 1 seq 442 lowscn 35422758870 found to restore
RMAN-06025: no backup
of log thread 1 seq 441 lowscn 35422757955 found to restore
Do following on Standby DB.
alter database recover managed standby database
cancel;
rman target /
RMAN> recover database;
NOTE: make sure that you create password file on standby database otherwise primary DB can't ship log file to the standby DB.
orapwd file='$ORACLE_HOME/dbs/orapwKARDBPRD2'
entries=10 force=y password='xxxxxx' force=y format=12
NOTE: The SRLs should exist on both production and standby databases.
Example:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 40 '+DATA /redo40.log'
size 2048m;
STEP 6:
Execute the STEP 5
again for preparing second standby. I am going to just add few parameters not
all.
alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(KARDBPRD01, KARDBPRD02, KARDBPRD03)';
alter system set
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=KARDBPRD3';
Related to standby
alter system set LOG_ARCHIVE_DEST_2='SERVICE=kardbprd1
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kardbprd1';
alter system set
log_archive_dest_state_2=defer/enable;
Related to standby
alter system set LOG_ARCHIVE_DEST_3='SERVICE=kardbprd2
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=kardbprd2';
alter system set
log_archive_dest_state_3=defer/enable;
Enable the log_archive_dest_2 and log_archive_dest_3 on primary DB, which we have set as defer earlier.
alter system set
log_archive_dest_state_2=enable;
alter system set log_archive_dest_state_3=enable;
Check the status of remote archive destination on primary DB.
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST
WHERE DEST_ID=2;
Start the MRP (managed
recovery process) on standby.
select
name,db_unique_name, open_mode, database_role from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ --------------------
----------------
KARDBPRD KARDBPRD2 MOUNTED PHYSICAL STANDBY
This will apply immediate log.
alter database recover managed standby database using current logfile
disconnect;
Check whether the MRP0 process started or not. Execute the following
commands.
select
PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where
process = 'MRP0' or client_process='LGWR';
PROCESS
CLIENT_P THREAD# SEQUENCE#
BLOCK#
--------- -------- ---------- ---------- ----------
RFS LGWR 1 302862
2264115
MRP0
N/A 1 302831 0
SELECT PROCESS,
STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS
STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
--------- ------------ ---------- ----------
---------- ----------
DGRD
ALLOCATED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
DGRD
ALLOCATED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 1 302863
94985 4
MRP0
WAIT_FOR_LOG 1 302831 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
11 rows selected.
Followings are few useful commands to verify whether the primary and standby are in sync.
On Standby.
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last
Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME
) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))
ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME
) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
On both primary and
standby.
select max(sequence#)
from v$archived_log where applied='YES';
Check the v$archive
view on standby
SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Do logswitch on
primary.
ALTER SYSTEM SWITCH
LOGFILE;
Check then on Standby.
SELECT
THREAD#,MAX(SEQUENCE#) FROM V$ARCHIVED_LOG GROUP BY THREAD#;
SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
select
sequence#,applied from v$archived_log order by sequence#;
Check on primary to
see which sequences are applied.
SELECT SEQUENCE#,
FIRST_TIME, NEXT_TIME, applied from v$archived_log order by sequence#;
