Description:
In this blog, we are going to see data guard switch over by using data guard broker DGMGRL.
Data Guard Switchover:
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss.
This is typically done for planned maintenance of the primary system.
During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
The transition occurs without having to re-enable either database.
Overall steps:
1. Connect the primary database with DGMGRL Data Guard broker.
2. Switch over primary to standby database.
3. Connect Standby database.
4. Switch over standby to primary.
Step 1: Connect the primary database with the DGMGRL Data Guard broker.
[oratest@oracle admin]$ dgmgrl sys/oracle@orclDGMGRL for Linux: Release 19.0.0.0.0 - Production on
Fri Nov 12 13:19:44 2021 Version 19.9.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.Welcome to DGMGRL, type "help" for information.
Connected to "orcl"
Connected as SYSDBA.
Step 2: Switch over primary to the standby database.
DGMGRL> switchover to orcl_stby;Performing switchover NOW, please wait...Operation requires a connection to instance "orcl" on database
"orcl_stby"Connecting to instance "orcl"...Connected as SYSDBA.New primary database "orcl_stby" is opening...Operation requires start up of instance "orcl" on database "orcl"Starting instance "orcl"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "orcl_stby"DGMGRL>Let's switch back to the original primary. Connect to the
new primary (orcl_stby) and switchover to the new standby database (orcl).
Step 3: Connect the Standby database.
[oratest@oracle admin]$ dgmgrl sys/oracle@orcl_stbyDGMGRL for Linux: Release 19.0.0.0.0 - Production on
Fri Nov 12 13:31:56 2021Version 19.9.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.
Welcome to DGMGRL, type "help" for information.Connected to "orcl_stby"
Connected as SYSDBA.
Step 4: Switch over standby to primary
DGMGRL> switchover to orcl ;Performing switchover NOW, please wait...Operation requires a connection to instance "orcl" on
database "orcl"Connecting to instance "orcl"...Connected as SYSDBA.New primary database "orcl" is opening...Operation requires start up of instance "orcl" on database
"orcl_stby"Starting instance "orcl"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "orcl"DGMGRL>
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:
Description: In this blog, we are going to see step by step process of oracle data guard manual switchover process physical standby to primary.
Switch Over: A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.
Overall Steps:
1. Check Primary and Standby database role and open_mode.2. Change nls_date_format and check current log sync status.3. Check switch over status both primary and standby servers.4. Primary side switch over process.5. Standby side switch over process.6. Check the log sync status both sides.7. New primary database parameter configuration.8. Switch log files and check the log sync status.
Step 1:- Check primary and standby database role and open_mode.
SQL> select name,open_mode,database_role from v$database; Step 2:- Change nls_date_format and check current log sync status.
check the log sequence on both primary and standby sides. SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; Session altered. SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; Step 3:- Check switch over status both primary and standby servers. SQL> select switchover_status from v$database;
Step 4:- Primary side switch over process.
Switch over to standby. SQL> alter database commit to switchover to standby; Database altered. Bounce the database. SQL> shut immediate SQL> startup nomount Mount standby database. SQL> alter database mount standby database; Start MRP process. SQL> alter database recover managed standby database disconnect from session; Database altered. check the database role. SQL> select name,open_mode,database_role from v$database; Step 5:- Standby side switchover process:
Stop the MRP process. SQL> alter database recover managed standby database cancel; Database altered. Switch Over to Primary. SQL> alter database commit to switchover to primary; Database altered. Check database role changed or not and open_mode. SQL> select name,open_mode,database_role from v$database; Open the database. SQL> alter database open; Database altered. Check the database role and open_mode. SQL> select name,open_mode,database_role from v$database; Step 6:- Check the log sync status both sides. SQL> archive log list
Check both side log sync status.
Step 7:- New primary database parameter configuration for log sync. SQL> alter system set log_archive_dest_1 =’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=local’ scope=spfile; System altered.
SQL> alter system set log_archive_dest_2 =’service=cloud async valid_for=(online_logfiles,primary_role) db_unique_name=cloud’ scope=spfile; System altered.
SQL> alter system set db_file_name_convert=’/u01/app/oracle/oradata/LOCAL/datafile/’,’/u01/app/oracle/oradata/CLOUD/datafile/’ scope=spfile; System altered.
SQL> alter system set log_file_name_convert=’/u01/app/oracle/oradata/LOCAL/onlinelog/’,’/u01/app/oracle/oradata/CLOUD/onlinelog/’ scope=spfile; System altered.
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(local,cloud)’ SCOPE=both; System altered.
SQL> ALTER SYSTEM SET fal_server=’cloud’ SCOPE=both; System altered.
SQL> ALTER SYSTEM SET fal_client=’local’ SCOPE=both; System altered.
SQL> select DEST_NAME,STATUS,ERROR from v$ARCHIVE_DEST where status!=’INACTIVE’; DEST_NAME ——————————————————————————– STATUS ERROR ——— —————————————————————– LOG_ARCHIVE_DEST_1 VALID Step 8:-Switch log files and check the log sync status. Primary side switch logfiles: enable log_archive_dest_state_2. SQL> alter system switch logfile; SQL> archive log list Check log file sync status on standby side:- Restart the MRP process. SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database disconnect nodelay; Database altered. check log sync status. 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;
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:
Description: In this blog, we are going to see step by step process on the oracle data guard manual failover process.
Failover: A failover is a role transition in which one of the standby databases is transitioned to the primary role after the primary database fails or has become unreachable. A failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.
Data Guard Configuration Details:- Overall Steps:-
1. Check the database role,open_mode in standby server.2. Cancel MRP process.3. Change Standby to Primary Database.4. Bounce the database.
Step 1:- Check the database role,open_mode in the standby server. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- CLOUD MOUNTED PHYSICAL STANDBY
Cancel the MRP process: SQL> recover managed standby database cancel; Media recovery complete.
Finish the MRP process: SQL> alter database recover managed standby database finish; Database altered. Step 3:- Change Standby to Primary Database. SQL> alter database activate standby database; Database altered.
Check the role changed or not: SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- CLOUD MOUNTED PRIMARY Step 4:- Bounce the Database. SQL> shut immediate SQL> startup Check the database role and status. SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- CLOUD READ WRITE PRIMARY
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:
Description :-
In this Blog we are going to see standby log sync failure and sync the standby database using incremental scn backup.
Refresh Using RMAN SCN Backup:
In case of any log sync gap has been occur in standby database or archivelogs received but not applied in standby database we can refresh the standby database using SCN based RMAN backup.
Overall Steps:
1. Check the archivelog GAP both primary and standby database.
2. Check the SCN number in standby.
3. Take RMAN incremental SCN backup in primary database.
4. Create Standby Control file in primary database.
5. Transfer Backup and control file to standby.
6. Replace the standby control file.
7. Catalog backuppiece on standby database.
8. cancel MRP process.
9. recover database using backup piece.
Step 1:- Check the archive log GAP for both primary and standby database. Primary Database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
SYSTEM READ WRITE PRIMARY
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
34
Standby database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
SYSTEM MOUNTED PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
30
Step 2:- Check the SCN number in Standby.
SQL> select current_scn from v$database;
CURRENT_SCN
———–
2058645
Step 3:-Take RMAN incremental SCN backup in the primary database.
RMAN> backup incremental from SCN 2058645 database format '/u02/oracle/backup/DELL_BACKUP/database_%d_%u_%s';
Starting backup at 24-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_system_jwbgjd16_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_sysaux_jwbgktdq_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/SYSTEM/datafile/o1_mf_users_jwbglnno_.dbf
channel ORA_DISK_1: starting piece 1 at 24-DEC-21
channel ORA_DISK_1: finished piece 1 at 24-DEC-21
piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-DEC-21
channel ORA_DISK_1: finished piece 1 at 24-DEC-21
piece handle=/u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14 tag=TAG20211224T230253 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-DEC-21
RMAN> exit
Step 4:-Create Standby Control file in primary database
SQL> alter database create standby controlfile as ‘/u02/oracle/backup/DELL_BACKUP/std_control.ctl’;
Database altered.
Step 5:- Transfer Backup and control file to standby
RMAN> list backup ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 10.20M DISK 00:00:00 24-DEC-21
BP Key: 1 Status: EXPIRED Compressed: NO Tag: TAG20211224T092140
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092129700_jwbjydcj_.bkp
SPFILE Included: Modification time: 24-DEC-21
SPFILE db_unique_name: SYSTEM
Control File Included: Ckp SCN: 2032248 Ckp time: 24-DEC-21
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2 173.98M DISK 00:00:02 24-DEC-21
BP Key: 2 Status: EXPIRED Compressed: NO Tag: TAG20211224T115303
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115303_jwbst7c2_.bkp
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 2008822 24-DEC-21 2032572 24-DEC-21
1 6 2032572 24-DEC-21 2037484 24-DEC-21
1 7 2037484 24-DEC-21 2037797 24-DEC-21
1 8 2037797 24-DEC-21 2040754 24-DEC-21
1 9 2040754 24-DEC-21 2040789 24-DEC-21
1 10 2040789 24-DEC-21 2040796 24-DEC-21
1 11 2040796 24-DEC-21 2041066 24-DEC-21
1 12 2041066 24-DEC-21 2041524 24-DEC-21
1 13 2041524 24-DEC-21 2041533 24-DEC-21
1 14 2041533 24-DEC-21 2041659 24-DEC-21
1 15 2041659 24-DEC-21 2042175 24-DEC-21
1 16 2042175 24-DEC-21 2054365 24-DEC-21
1 17 2054365 24-DEC-21 2054426 24-DEC-21
1 18 2054426 24-DEC-21 2054437 24-DEC-21
1 19 2054437 24-DEC-21 2054442 24-DEC-21
1 20 2054442 24-DEC-21 2054630 24-DEC-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 0 1.14G DISK 00:00:48 24-DEC-21
BP Key: 3 Status: EXPIRED Compressed: NO Tag: TAG20211224T115310
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_nnnd0_TAG20211224T115310_jwbstgsy_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
3 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
4 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
7 0 Incr 2054665 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 26.50K DISK 00:00:00 24-DEC-21
BP Key: 4 Status: EXPIRED Compressed: NO Tag: TAG20211224T115407
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/backupset/2021_12_24/o1_mf_annnn_TAG20211224T115407_jwbsw7qd_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 21 2054630 24-DEC-21 2054749 24-DEC-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 10.20M DISK 00:00:01 24-DEC-21
BP Key: 5 Status: EXPIRED Compressed: NO Tag: TAG20211224T115408
Piece Name: /u01/app/oracle/fast_recovery_area/SYSTEM/autobackup/2021_12_24/o1_mf_s_1092138848_jwbsw9gv_.bkp
SPFILE Included: Modification time: 24-DEC-21
SPFILE db_unique_name: SYSTEM
Control File Included: Ckp SCN: 2054766 Ckp time: 24-DEC-21
BS Key Type LV Size
------- ---- -- ----------
6 Incr 74.02M
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
3 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
4 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
7 Incr 2100466 24-DEC-21 NO /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
Backup Set Copy #1 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 24-DEC-21 NO TAG20211224T230253
List of Backup Pieces for backup set 6 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
6 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0d0hil0t_13
Backup Set Copy #2 of backup set 6
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:28 24-DEC-21 NO TAG20211224T230253
List of Backup Pieces for backup set 6 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
8 1 AVAILABLE /u02/backup/database_SYSTEM_0d0hil0t_13
BS Key Type LV Size
------- ---- -- ----------
7 Incr 10.45M
Control File Included: Ckp SCN: 2100521 Ckp time: 24-DEC-21
Backup Set Copy #1 of backup set 7
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 24-DEC-21 NO TAG20211224T230253
List of Backup Pieces for backup set 7 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
7 1 EXPIRED /u02/oracle/backup/DELL_BACKUP/database_SYSTEM_0e0hil21_14
Backup Set Copy #2 of backup set 7
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:01 24-DEC-21 NO TAG20211224T230253
List of Backup Pieces for backup set 7 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
9 1 AVAILABLE /u02/backup/database_SYSTEM_0e0hil21_14
Step 8:- Cancel MRP Process.
SQL> recover managed standby database cancel;
Media recovery complete.
Step 9:- Recover standby database using the backup piece.
RMAN> RECOVER DATABASE;
Starting recover at 24-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_system_jwbgjd16_.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_sysaux_jwbgktdq_.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_undotbs1_jwbglmk3_.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/MONITOR/datafile/o1_mf_users_jwbglnno_.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/database_SYSTEM_0d0hil0t_13
channel ORA_DISK_1: piece handle=/u02/backup/database_SYSTEM_0d0hil0t_13 tag=TAG20211224T230253
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc
archived log for thread 1 with sequence 34 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc
archived log for thread 1 with sequence 35 is already on disk as file /u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_33_jwbwmzy2_.arc thread=1 sequence=33
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_34_jwbwn2p4_.arc thread=1 sequence=34
archived log file name=/u01/app/oracle/fast_recovery_area/MONITOR/archivelog/2021_12_24/o1_mf_1_35_jwbwwc6o_.arc thread=1 sequence=35
media recovery complete, elapsed time: 00:00:02
Finished recover at 24-DEC-21
RMAN> exit
Step 10:- enable the MRP process.
SQL> alter database recover managed standby database disconnect from session ;
Step 11:- Defer and enable dest 2 in primary database
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
Step 12:- Check the log sync primary and standby.
Primary side:
SQL> select max(sequence#) from v$archived_log where archived=’YES’;
MAX(SEQUENCE#)
————–
37
Standby side:
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; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference
1 37 37 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:
Backup Based Physical Standby Configuration Manually
Description: In this blog we are going to see step by step configuration and demo of oracle database primary to physical standby.
Backup Based Standby Configuration: Mostly we have configure standby database in active data guard method. using level 0 backup we configure physical standby database.
Overall Steps:
1. check the role in primary database.2. Take level 0 backup using RMAN.3. Primary database parameter configuration.4. Control and Pfile creation for standby database.5. Create directory in standby database.6. Copy archivelogs,control file,pfile, backupsets to standby database.7. Standby configuration.8. Bounce database and set the standby control file.9. Connect RMAN and restore ,recover the database.10. Check the database can be created.11. Start the MRP process.12. Check the Log sync status.
Step 1:- Check the database role in primary database. SQL> select name,open_mode,database_role from v$database; Step 2:- Take level 0 backup using RMAN. RMAN> backup incremental level 0 database plus archivelog; Step 3:- Primary database parameter configuration.
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(cloud,local)’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=local async valid_for=(online_logfiles,primary_role) db_unique_name=local’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=’local’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_client=’c’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;
System altered.
Step 4:- Create control file and Pfile for standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/app/oracle/oradata/CLOUD/standby.ctl’;
Database altered.
SQL> CREATE PFILE=’$ORACLE_HOME/dbs/initcompany.ora’ FROM SPFILE;
[oracle@local dbs]$ export ORACLE_SID=company [oracle@local dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Dec 17 08:17:06 2021 Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> CREATE SPFILE FROM PFILE=’initcompany.ora’;
File created.
Step 8:- Bounce database and set the standby control file.
SQL> startup mount; ORACLE instance started.
Total System Global Area 1191181696 bytes Fixed Size 8895872 bytes Variable Size 318767104 bytes Database Buffers 855638016 bytes Redo Buffers 7880704 bytes Database mounted. SQL> exit
SQL> alter system set control_files=’/u01/app/oracle/oradata/COMPANY/standby.ctl’ scope=spfile;
System altered.
Step 9:-Connect RMAN and restore ,recover the database.
RMAN> restore database;
Starting restore at 17-DEC-21Starting implicit crosscheck backup at 17-DEC-21using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=46 device type=DISKCrosschecked 18 objectsFinished implicit crosscheck backup at 17-DEC-21Starting implicit crosscheck copy at 17-DEC-21using channel ORA_DISK_1Finished implicit crosscheck copy at 17-DEC-21searching for all files in the recovery areacataloging files...cataloging doneList of Cataloged Files=======================File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkpFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkpFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkpFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkpFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkpFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_2_jvqw80t4_.arcFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_3_jvqwcpbc_.arcFile Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arcusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_system_jvbmzxk8_.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_sysaux_jvbn1c3g_.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_undotbs1_jvbn24dz_.dbfchannel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_users_jvbn25hj_.dbfchannel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkpchannel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp tag=TAG20211217T074035channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:45Finished restore at 17-DEC-21
RMAN> recover database;
Starting recover at 17-DEC-21 using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc archived log file name=/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc thread=1 sequence=4 media recovery complete, elapsed time: 00:00:00 Finished recover at 17-DEC-21
RMAN> exit
Step 10:- Check the database can be created.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE ——— ——————– CLOUD MOUNTED
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE ——— ——————– —————- CLOUD MOUNTED PHYSICAL STANDBY
Step 11:- Start the MRP process.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
Step 12:- Log sync status.
switch logs in primary, defer and enable the dest.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 8 Next log sequence to archive 10 Current log sequence 10 SQL>
check the status in standby.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
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; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference ———- ———————- ——————— ———- 1 9 9 0
SQL>
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:
Description: In this blog, we are going to see step by step manual data guard reinstate process using the flashback database method.
Data Guard Reinstate: After done failover to your Standby database so it becomes the new Primary. the old primary database can be reinstated as a standby database. this convert happens only in the database in flashback mode.
Overall Steps:
Check the flashback enabled.
Get the SCN number from the new primary database.
Bounce the old primary database open in the mount stage.
Flashback the database in SCN number of the new primary database.
Convert the database to physical standby.
Bounce the database and check the database role.
Step 1:- Check the flashback enabled after failover. SELECT FLASHBACK_ON FROM V$DATABASE;
Step 2:- Get SCN number from the new primary database. SQL> select to_char(standby_became_primary_scn) from v$database;
Step 3:- Bounce the Old primary database and open as mount stage.
Data Guard Broker: An Oracle Data Guard Broker configuration is a logical definition of an Oracle Data Guard configuration allowing for centralized management and configuration of the physical resources involved in the configuration.
Step 1: Connect to both Databases (primary and standby) and issue the following command:-
SQL>alter system set dg_broker_start=true; System altered.
Step 2: On the primary server, issue the following command to Register the Primary Server with the Broker.
[oratest@oracle dbs]$ dgmgrl sys/oracle@orcl DGMGRL for Linux: Release 19.0.0.0.0 – Production on Mon Nov 8 17:14:30 2021 Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information. Connected to “orcl” Connected as SYSDBA.
DGMGRL> create configuration 'dg_my_config' as primary database is 'orcl' connect
identifier is orcl;
Configuration “dg_my_config” created with primary database “orcl”
Step 3: Now add the standby database:-
DGMGRL> add database stand as connect identifier is stand maintained as physical;Database "stand" added
Step 4: Now we enable the new configuration;-
DGMGRL> enable configuration;Enabled.DGMGRL>
Step 5 ; The following commands show how to check the configuration and status of the databases from the broker:-
DGMGRL> show database stand; Database - stand Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 5.00 KByte/s Real Time Query: OFF Instance(s):
orcl
Database Status: SUCCESS
Step 6: Stop/Start Managed Recovery:-
Stop managed recovery.SQL> alter database recover managed standby database cancel;Start managed recovery.SQL> alter database recover managed standby database disconnect;
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:
step -3: – Ping Listener And Tnsnames Both Server……like output below,,
[oratest@oracle admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-OCT-2021 14:53:31
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain)))
OK (0 msec)
[oratest@oracle admin]$ tnsping stand
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-OCT-2021 14:53:36
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.20)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = oracle)))
OK (10 msec)
step 4: – In the primary Database server (192.168.1.24)
SQL>ALTER SYSTEM SET log_archive_config=’dg_config=(orcl,stand)’ SCOPE=both;
System altered.
SQL>ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles) db_unique_name=orcl' SCOPE=both;
System altered.
SQL>alter system set log_archive_dest_2='service=stand async
valid_for=(online_logfiles,primary_role) db_unique_name=stand' scope=both;
System altered.
SQL>ALTER SYSTEM SET fal_server='stand' SCOPE=both;
System altered.
SQL>ALTER SYSTEM SET fal_client='orcl' SCOPE=both;
System altered.
SQL>ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;
System altered.
step -5: In Standby Server Side
1.. Copy password file in primary Database
in primary server,
[oratest@oracle dbs]$ scp orapworcl
[email protected]:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[email protected]'s password:
orapworcl
100% 2048 2.0KB/s 00:00
After completed scp in standby server side change the password file name, like name below,
[oracle@oracletest dbs]$ mv orapworcl orapwstand
2..Changing Parameters in Standby Database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization
parameter file named initstand.ora
Containing a single parameter: DB_NAME=orcl
[oracle@oracletest dbs]$ cat initstand.ora
db_name=orcl
[oracle@oracletest dbs]$
3:- Create Directory Structure in Standby Database
[oracle@oracletest dbs]$ cd $ORACLE_BASE/admin/
[oracle@oracletest admin]$ mkdir stand
[oracle@oracletest admin]$ cd stand/
[oracle@oracletest stand]$ mkdir adump
[oracle@oracletest stand]$ mkdir -p /u01/app/oracle/oradata/stand
4:- Start the Standby Database using Pfile
[oracle@oracletest TEST]$ export ORACLE_SID=stand
[oracle@oracletest TEST]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 25 10:05:00 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='$ORACLE_HOME/dbs/initstand.ora' nomount;
ORACLE instance started.
Total System Global Area 243268216 bytes
Fixed Size 8895096 bytes
Variable Size 180355072 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
5-–> Connect RMAN for Primary Database in Standby server
[oracle@oracletest stand]$ export ORACLE_SID=orcl
[oracle@oracletest stand]$ rman target sys/oracle@orcl auxiliary sys/oracle@stand
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 8 16:29:37 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1613336523)
connected to auxiliary database: ORCL (not mounted)
6—–> Run the below command,
RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'orcl','stand'
set db_name='orcl'
set db_unique_name='stand'
set
db_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradat
a/stand/'
set
log_file_name_convert='/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/orada
ta/stand/'
set control_files='/u01/app/oracle/oradata/stand/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='stand'
set fal_server='orcl'
set standby_file_management='auto'
set log_archive_config='dg_config=(orcl,stand)'
set compatible='19.0.0'
set memory_target='1200m'
nofilenamecheck;
}
Output like below that,
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=62 device type=DISK
allocated channel: p2
channel p2: SID=24 device type=DISK
allocated channel: p3
channel p3: SID=78 device type=DISK
allocated channel: p4
channel p4: SID=61 device type=DISK
allocated channel: s1
channel s1: SID=37 device type=DISK
Starting Duplicate Db at 08-NOV-21
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwstand'
targetfile
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileorcl.ora' auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora''";
}
executing Memory Script
Starting backup at 08-NOV-21
Finished backup at 08-NOV-21
sql statement: alter system set spfile=
''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfilestand.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/stand/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=standXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_name =
''orcl'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stand'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/u01/app/oracle/oradata/ORCL/'', ''/u01/app/oracle/oradata/stand/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u01/app/oracle/oradata/ORCL/'', ''/u01/app/oracle/oradata/stand/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/stand/standby1.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''stand'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''orcl'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''auto'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(orcl,stand)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible =
''19.0.0'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
1200m comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/stand/adump'' comment= ''''
scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=standXDB)'' comment= ''''
scope=spfile
sql statement: alter system set db_name = ''orcl'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stand'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/ORCL/'',
''/u01/app/oracle/oradata/stand/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/ORCL/'',
''/u01/app/oracle/oradata/stand/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/stand/standby1.ctl''
comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stand'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''orcl'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''auto'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(orcl,stand)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''19.0.0'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 1200m comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
allocated channel: s1
channel s1: SID=35 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format
'/u01/app/oracle/oradata/stand/standby1.ctl';
}
executing Memory Script
Starting backup at 08-NOV-21
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_orcl.f
tag=TAG20211108T163937
channel p1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 08-NOV-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/stand/temp01.dbf";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/stand/temp02.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/stand/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/stand/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/stand/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/stand/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/stand/system01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/stand/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/stand/undotbs01.dbf" datafile
7 auxiliary format
"/u01/app/oracle/oradata/stand/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/stand/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/stand/temp02.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 08-NOV-21
channel p1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel p2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
output file name=/u01/app/oracle/oradata/stand/users01.dbf tag=TAG20211108T163951
channel p4: datafile copy complete, elapsed time: 00:00:56
output file name=/u01/app/oracle/oradata/stand/undotbs01.dbf tag=TAG20211108T163951
channel p3: datafile copy complete, elapsed time: 00:06:33
output file name=/u01/app/oracle/oradata/stand/system01.dbf tag=TAG20211108T163951
channel p2: datafile copy complete, elapsed time: 00:13:49
output file name=/u01/app/oracle/oradata/stand/sysaux01.dbf tag=TAG20211108T163951
channel p1: datafile copy complete, elapsed time: 00:14:10
Finished backup at 08-NOV-21
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1088096063 file
name=/u01/app/oracle/oradata/stand/users01.dbf
Finished Duplicate Db at 08-NOV-21
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN>
step -6 : connect to the standby Database
[oracle@oracletest stand]$ export ORACLE_SID=stand
[oracle@oracletest stand]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 8 17:03:57 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
SQL> select NAME,OPEN_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PHYSICAL STANDBY
SQL> select DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME
------------------------------
stand
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:
Description: In this blog, we are going to see oracle data guard protection modes and how to change one mode to another mode.
What is protection mode: Production mode is based on the RTO(recovery time objective) and RPO(recovery point objective values and to select the most appropriate model for their data guard configuration. The primary database always syncs with the standby database, the primary database is not active for the end-users for some natural disasters, hardware errors occur the standby can be changed as primary.
Why it is Required: In that situation some of the applications required maximum database performance at all times, so we can configure the standby database with appropriate protection mode.
Types of protection modes:
MAXIMUM PERFORMANCEMAXIMUM PROTECTIONMAXIMUM AVAILABILITY
MAXIMUM AVAILABILITY: Maximum availability protection mode provides the highest level of protection, which is possible without compromising the availability of the primary database. Whatever changes happen in the primary database it can be reflected in the standby database, the standby database sends acknowledgment then it will send again the logs, otherwise, it ignores the availability mode move on to the maximum performance mode. This mode ensures ZERO data loss in the case of certain double faults.
MAXIMUM PERFORMANCE: Maximum performance is a default protection mode. It is a high-level protection mode without affecting the primary database. The primary database sends logs to the standby database it’s not waiting for an acknowledgment it will perform asynchronous behavior. Less amount of data loss happens in maximum performance mode.
MAXIMUM PROTECTION: Maximum protection mode ensures that no data loss occurs if the primary database fails. The primary database sends logs to standby the RFS acknowledges to LNS at least one redo log can be transferred or it will shut down the primary database.
SUMMARY OF PROTECTION MODES:
How to view and change protection mode: View protection mode: SQL> select protection_mode from v$database; Data sync and async configuration: SQL> ALTER SYSTEM SET log_archive_dest_2=’service=data sync valid_for=(online_logfiles,primary_role) db_unique_name=stand’ SCOPE=both; System altered.
How to change protection mode:
SQL> alter database set standby database to maximize PERFORMANCE; SQL> alter database set standby database to maximize PROTECTION; SQL> alter database set standby database to maximize AVAILABILITY; SQL> select name,protection_mode from v$database;
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: