After failover we have to reinstate the primary db to physical standby. For that we have to find out the scn in the new primary db- (standby_became_primary_scn parameter from v$database) where at the particular scn the standby database has became the primary database. Using this scn in our old primary db we have to flashback our db to this particular scn and then we have to convert our old primary to physical standby.
Note: To perform the reinstate process we have to ensure that the flashback must be enabled in the primary db which got crashed.
Overview Steps:
Step:1 Find out the standby_became_primary_scn value in the new primary db from v$database view.
Step:2 Flashback the old primary db to this particular scn value that is taken from the new primary db.
Step:3 Then convert the primary db to physical standby and bounce the database.
Step:4 Check for the name, open_mode, database_role it is changed to physical standby, we have performed the reinstate task successfully.
Step;5 verify whether the standby is in sync with the primary after reinstate.
Step:1Get the value of standby_became_primary_scn from the new primary db.
Step:2Flashback the old primary db to this scn value.
Step:3Convert the primary db into physical standby, then bounce the database.
Step:4Now the database_role has changed from primary to physical standby, reinstate performed successfully.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Oracle Dataguard physical Standby configuration in 19c:
Overview steps:
Step:1 we have to enable the archive log mode ,flashback on primary databases.
Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.
Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.
Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.
Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.
Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)
Step:7 create the directory structure in standby similar to the primary side.
Step:8 Start the standby side database in nomount using the pfile.
Step:9 Connect to the RMAN and duplicate the primary database using the command
duplicate target database for standby from active database dorecover nofilenamecheck;
Step:10 Now our standby database is ready for read only purpose.
Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.
Step:12 Verify the current log sequence in both primary and standby sides.
Do the following set of changes in primary side:
Oracle Active Data Guard:
Active Data Guard is a licensed option for Oracle Database Enterprise Edition. … Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo – the information used by every Oracle Database to protect transactions – and applying it to the standby database.
Difference between Oracle Data Guard and Active Data Guard?
Oracle Active Data Guard provides the best data protection and availability for Oracle Database. Active Data Guard enables the offloading of read-only operations, backups, and so on, to an up-to-date physical standby database while also providing disaster protection.
Step:1 Enable the archive log mode in the primary database.
SQL> alter database archivelog;
Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
Step:2 Enable the force logging.
SQL>alter database force logging;
Database altered
Step:3 Flashback should be enabled in primary db.
ALTER DATABASE FLASHBACK ON;
Database altered
Check the archive log,force_logging,flashback is enabled.
SQL> select name,force_logging,flashback_on,log_mode from v$database;
NAME FORCE_LOGGING FLASHBACK_ON LOG_MODE
--------- --------------------------- ------------ -------------
CANADA YES YES ARCHIVELOG
Step:4 Add the Standby redo log files for standby side.
SQL> alter database add standby logfile thread 1
group 4('/u01/app/oracle/oradata/redo04.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 5('/u01/app/oracle/oradata/redo05.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 6('/u01/app/oracle/oradata/redo06.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 7('/u01/app/oracle/oradata/redo07.log')size 50m;
Database altered.
Step:5 set the log archive config parameter.
SQL> alter system set log_archive_config='DG_config=(canada,standby)';
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
log_archive_config string DG_config=(canada,standby)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2 string SERVICE=standby NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
-------------------------------- ----------- -----------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
Step:6 Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
Step:7 configure the log_archive_process to 30 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES
SQL> alter system set log_archive_max_processes=30;
System altered.
Step:8 register the fal_server and fal_client as standby(standby) and physical(primary) databases respectively.
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=physical;
System altered.
Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.
SQL> alter system set standby_file_management=auto;
System altered.
Step:10 change the remote_login_password file as exclusive.
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile string EXCLUSIVE
Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a new datafile and logfile respectively on the primary database to a filename on the standby database.
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;
System altered.
Step:12 Configure the tns and listener files on both primary and standby databases.
listener entry in the primary database.
tnsnames entry in the primary database.
listener entry in the standby database.
tnsnames entry in the standby database.
Step:13 ping your primary and standby databases from one to another.
Step:14 create the similar directory structures in standby side as of in primary.
Step:15 Transfer the password files,pfiles to the standby side through scp.
Step:16 create the initcanada.ora file containing the single parameter db_name=canada in the standby side it is useful to start our database in no mount stage.
Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.
Step:18 Duplicate the target database using the following command,
duplicate target database for standby from active database
dorecover nofilenamecheck;
[oracle@localhost admin]$
rman target sys/Pass#1234@canada auxiliary sys/Pass#1234@standby
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 17 07:28:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CANADA (DBID=215384084)
connected to auxiliary database: CANADA (not mounted)
RMAN>
duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 17-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwcanada';
}
executing Memory Script
Starting backup at 17-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
Finished backup at 17-FEB-21
contents of Memory Script:
{
restore clone from service 'canada' standby controlfile;
}
executing Memory Script
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/cntrlcanada.dbf
Finished restore at 17-FEB-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp
conflicts with a file used by the target database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf";
restore
from nonsparse from service
'canada' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'canada'
archivelog from scn 2091702;
switch clone datafile all;
}
executing Memory Script
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1064734612 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
contents of Memory Script:
{
set until scn 2092999;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-FEB-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
archived log for thread 1 with sequence 11 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
thread=1 sequence=10
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-21
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_10_1064697431.dbf RECID=1 STAMP=1064734609
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_11_1064697431.dbf RECID=2 STAMP=1064734611
Deleted 2 objects
Finished duplicate at 17-FEB-21
Yes,we have created the physical standby database successfully.
Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby.
Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
11
Step:21 Apply the log files using MRP process.
SQL> alter database recover managed standby database using current
logfile disconnect;
Database altered.
Step:22 check for the sequence no which is applied in standby.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log
ORDER BY sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
10 17-FEB-21 17-FEB-21 YES
11 17-FEB-21 17-FEB-21 YES
Step:23 We can check the difference in sequence using the following query.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 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;
SQL>
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 11 11 0
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us:
