Description:
In this Blog, we are going to see what is snapshot standby database and how to convert a physical standby database to a snapshot standby database with Demo.
Snapshot Standby Database:
Snapshot standby allows doing a read-write operation on the standby database.
We can convert the physical standby database to snapshot standby.
On that, we can do all types of testing or can be used as a development database.
Once the testing is over we can again convert the snapshot database to physical standby.
Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
Data Guard Configuration Environment:
Overall Steps:
1. Check primary and standby database role and open mode.2. Check the archived sequence value in standby.3. Disable the MRP process.4. Check the flashback status if it not enabled, enable the flashback.5. Check the db_recovery_file_set location and size.6. Bounce the database and start the db as mount stage.7. Convert physical standby to snapshot standby database.8. Open standby database and check open mode READ/WRITE.9. Test the snapshot standby database now we can create user and tables
for testing purpose.10. verify the log sequence primary and standby.11. Bounce database again open in mount stage.12. Convert snapshot standby to physical standby.13. Bounce the database.14. Check the database role has been changed as physical standby and the
mode as read only.15. After bounce enable the MRP process and check table available or not.
Step 1- Check primary and standby database role and open mode. Primary database: [oracle@agent ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:16:48 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> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN agent PRIMARY READ WRITE SQL>
Standby database: [oracle@data ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:18:41 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> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN data PHYSICAL STANDBY READ ONLY WITH APPLY SQL>
Step 2:- Check the archived sequence value in standby: SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ———- ————– 1 14
Step 3:- Disable the MRP process in the standby database: SQL> alter database recover managed standby database cancel; Database altered.
Step 4:- Check the flashback status if it is not enabled enable the flashback. SQL> select flashback_on from v$database;
FLASHBACK_ON —————— NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database;
FLASHBACK_ON —————— YES
Step 5:- Check the db_recovery_file_set location and size. SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 8256M
Step 6:- Bounce the database and start the DB as mount stage: SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount 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 Database mounted.
Step 7:- Convert physical standby to snapshot standby database: SQL> alter database convert to snapshot standby; Database altered.
Step 8:- Open standby database check read/write mode, and database role: SQL> alter database open; Database altered. SQL> select status, instance_name, database_role,open_mode from v$database, v$instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– MOUNTED data SNAPSHOT STANDBY MOUNTED
Step 9:- Test the snapshot standby database now we can create user and tables for testing purposes. SQL> create user agent identified by vbt default tablespace users quota unlimited on users; User created SQL> grant connect,resource to agent; Grant succeeded. SQL> conn agent/vbt Connected. SQL> create table test(id number,name varchar2(20)); Table created. SQL> insert into test values(101,’ram’); 1 row created. SQL> insert into test values(102,’raj’); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME ———- ——————– 101 ram 102 raj
Step 10:- verify the log sequence primary and standby. Primary side SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ———- ————– 1 14 Standby side SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
Step 11:- Bounce database again open in mount stage. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 1258290752 bytes Fixed Size 8896064 bytes Variable Size 754974720 bytes Database Buffers 486539264 bytes Redo Buffers 7880704 bytes Database mounted.
Step 12:- convert snapshot standby to physical standby: SQL> alter database convert to physical standby; Database altered.
Step 13- Bounce the database SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1258290752 bytes Fixed Size 8896064 bytes Variable Size 754974720 bytes Database Buffers 486539264 bytes Redo Buffers 7880704 bytes Database mounted. Database opened.
Step 14:- check the database role has been changed as physical standby and the mode as read-only SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN data PHYSICAL STANDBY READ ONLY
Step 15:- After bounce enable the MRP process and check table available or not. SQL> alter database recover managed standby database disconnect from session; Database altered.
SQL> select * from agent.test; select * from agent.test ERROR at line 1: ORA-00942: table or view does not exist 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 the step by step process of data Guard configuration, and primary to physical standby database demo.
what Is Data Guard:
Oracle data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
Data Guard maintains these standby databases as transactional consistent copies of the production database.
if the production database becomes unavailable, Data Guard can switch any standby database to the production role.
Physical Standby
Physical Standby is the exact block-for-block copy of primary database.
REDO logs apply primary to physical standby, its always sync with primary database
Physical Standby database only opened as READ ONLY mode.
Most of the environment used the physical standby database.
Environment Details: Overall Steps:
1.Check Archive log mode enable and force logging enabled.2.Add Standby log file group.3.check listener and TNS entry both primary and standby severs.4.Change parameters in primary database.5.copy password file primary to standby.6.Create PFILE in standby database.7.Create directory Structure in Standby database.8.Connect RMAN in standby database and run the script9.Check database can be configured or not.10.Connect standby database execute MRP process.11.Switch Logs in primary database, Execute Defer Enable.12.Check standby database log files can be switched.13.Crosscheck Alert Log files.
Primary Server Configuration:
Step 1: Check Archive log mode enable and force logging enabled.
In the primary database check whether the archive log mode is enabled or not, also check the force logging option to enable if is not enabled please enable it.
[oracle@agent ~]$ export ORACLE_SID=agent [oracle@agent ~]$ sqlplus / as sysdba Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
Connect database as mount stage
SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered. SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING LOG_MODE
————————- ————–
YES ARCHIVELOG
Step 2: Add Standby logfile group.
Add logfile group in the primary database mentioned as that file creation as standby log group.
SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/AGENT/redo04.log’ size 50m; Database altered.
SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/AGENT/redo05.log’ size 50m; Database altered.
SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/AGENT/redo06.log’ size 50m; Database altered.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
The output of tns ping statements connects both sides primary to standby and standby to primary.
[oracle@agent admin]$ tnsping data TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:44 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = data) (UR=A))) OK (20 msec)
[oracle@agent admin]$ tnsping agent TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:55 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = agent))) OK (0 msec) [oracle@agent admin]$
Step 4:Change parameters in primary database.
1. dg_config – Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and standby database. SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(agent,data)’ SCOPE=both; System altered.
2. log_archive_dest_1 – The LOG_ARCHIVE_DEST parameter is used to specify the directory to which Oracle archive logs are written. SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=agent’ SCOPE=both; System altered.
3. log_archive_dest_2 – handle the transmission of the standby site’s archived redo logs back to the original primary database. SQL> ALTER SYSTEM SET log_archive_dest_2=’service=data async valid_for=(online_logfiles,primary_role) db_unique_name=data’ SCOPE=both; System altered.
4. FAL_SERVER – It is used to fetch an archive log server for a standby database. SQL> ALTER SYSTEM SET fal_server=’data’ SCOPE=both; System altered.
5. FAL_CLIENT – It is configured for point the FAL Client. SQL> ALTER SYSTEM SET fal_client=’agent’ SCOPE=both; System altered.
6. standby_file_management –Check the redo changes done in Primary and sync those changes in Standby by Stopping the recovery and making STANDBY_FILE_MANAGEMENT = MANUAL value. Then sync the primary changes with standby changes and again change the parameter to AUTO and start the recovery on Standby database. SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both; System altered.
SQL> show parameter log_archive_config;
NAME TYPE VALUE ———————————— ———– —————————— log_archive_config string dg_config=(agent,data) SQL>
Move the file in new name db_unique_name(standby) oracle@data ~]$ cd $ORACLE_HOME/dbs [oracle@data dbs]$ ls hc_data.dat init.ora lkDATA orapwdata orapwagent spfiledata.ora [oracle@data dbs]$ mv orapwagent orapwdata
Step 6 :- create PFILE in standby database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initdata.ora Containing a single parameter: DB_NAME=agent [oracle@data dbs]$ cat initdata.ora db_name=agent [oracle@data dbs]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
Step 7 :- Create directory Structure in Standby database.
[oracle@data dbs]$ cd $ORACLE_BASE/admin/ [oracle@data dbs]$ mkdir data [oracle@data dbs]$ cd data [oracle@data dbs]$ mkdir adump [oracle@data dbs]$ mkdir -p /u01/app/oracle/oradata/DATA
Step 8 :- Connect RMAN clone Database
method 1: copy pfile primary database to standby edit the parameters after that connect RMAN and clone the database.
parameter_value_convert ‘agent’,’data’ set db_name=’agent’ set db_unique_name=’data’ set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’ set log_archive_max_processes=’5′ set fal_client=’data’ set fal_server=’agent’ set standby_file_management=’AUTO’ set log_archive_config=’dg_config=(agent,data)’ set compatible=’19.3.0.0′ set memory_target=’1200m’
method 2:
craete a RMAN script mention the parameters changes in spfile to execute the script.
Target database as primary – Agent Auxiliary database as a standby – Data
[oracle@data admin]$ export ORACLE_SID=agent [oracle@data admin]$ rman target sys/oracle@agent auxiliary sys/oracle@data Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 18 08:00:22 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGENT (DBID=396457310) connected to auxiliary database: AGENT (not mounted)
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 ‘agent’,’data’ set db_name=’agent’ set db_unique_name=’data’ set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’ set log_archive_max_processes=’5′ set fal_client=’data’ set fal_server=’agent’ set standby_file_management=’AUTO’ set log_archive_config=’dg_config=(agent,data)’ set compatible=’19.3.0.0′ set memory_target=’1200m’ nofilenamecheck; }
the output of the script:
using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=82 device type=DISK
contents of Memory Script: { backup as copy reuse passwordfile auxiliary format ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdata’ targetfile ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileagent.ora’ auxiliary format ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora’ ; sql clone “alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora””; } executing Memory Script
Starting backup at 18-NOV-21 Finished backup at 18-NOV-21
sql statement: alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora”
contents of Memory Script: { sql clone “alter system set audit_file_dest = ”/u01/app/oracle/admin/data/adump” comment= ”” scope=spfile”; sql clone “alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment= ”” scope=spfile”; sql clone “alter system set db_name = ”agent” comment= ”” scope=spfile”; sql clone “alter system set db_unique_name = ”data” comment= ”” scope=spfile”; sql clone “alter system set db_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile”; sql clone “alter system set log_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile”; sql clone “alter system set control_files = ”/u01/app/oracle/oradata/DATA/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 = ”data” comment= ”” scope=spfile”; sql clone “alter system set fal_server = ”agent” 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=(agent,data)” comment= ”” scope=spfile”; sql clone “alter system set compatible = ”19.3.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/data/adump” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment= ”” scope=spfile
sql statement: alter system set db_name = ”agent” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”data” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile
sql statement: alter system set control_files = ”/u01/app/oracle/oradata/DATA/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 = ”data” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”agent” 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=(agent,data)” comment= ”” scope=spfile
sql statement: alter system set compatible = ”19.3.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
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/DATA/standby1.ctl’; } executing Memory Script
Starting backup at 18-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_agent.f tag=TAG20211118T080552 channel p1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 18-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/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp”; switch clone tempfile all; set newname for datafile 1 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf”; set newname for datafile 3 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf”; set newname for datafile 4 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf”; set newname for datafile 7 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf”; backup as copy reuse datafile 1 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf” datafile 3 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf” datafile 4 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf” datafile 7 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf” ; sql ‘alter system archive log current’; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp in control file
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
Step 11:- Switch Logs in the primary database, Execute Defer Enable.
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9
Set log_archive_dest_state_2 as defer SQL> alter system set log_archive_dest_state_2=defer;
System altered.
Enable log_archive_dest_state_2 location SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 12: – check standby database log files can be switched
SQL> select error, status from v$archive_dest;
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;
Check MRP0 process status: SQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS=’MRP0′;
STATUS ———— WAIT_FOR_LOG
Archived log applied time and status:
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SQL> select name,open_mode from v$database; NAME OPEN_MODE ——— ——————– AGENT MOUNTED
Step 13:- Monitor the process in the alert log The best method to monitor the data guard process in the alert log shows as exact data transfer reports and errors we can using the logs easily find out the mistakes.
oracle@data trace]$ pwd /u01/app/oracle/diag/rdbms/data/data/trace [oracle@data trace]$ tail -100f alert_data.log MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_11_jsclfmk7_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-12 (in transit) 2021-11-18T09:05:44.267442+05:30 rfs (PID:29555): Archived Log entry 7 added for B-1088922080.T-1.S-12 ID 0x17a1415e LAD:2 rfs (PID:29555): No SRLs created for T-1 2021-11-18T09:05:44.297904+05:30 rfs (PID:29555): Opened log for T-1.S-13 dbid 396457310 branch 1088922080 2021-11-18T09:05:44.346775+05:30 MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_12_jsclfnxx_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-13 (in transit) 2021-11-18T09:05:45.670215+05:30 rfs (PID:29555): Archived Log entry 8 added for B-1088922080.T-1.S-13 ID 0x17a1415e LAD:2 rfs (PID:29555): No SRLs created for T-1 2021-11-18T09:05:45.699219+05:30 rfs (PID:29555): Opened log for T-1.S-14 dbid 396457310 branch 1088922080 2021-11-18T09:05:46.482970+05:30 MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_13_jscljj95_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-14 (in transit) 2021-11-18T09:16:20.023804+05:30
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:
Oracle Data Guard concept make sure high availability, disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.
Data Guard can be of two types:
Physical Stand By Database-
Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.
Logical Stand by Database-
Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.
Query to monitor the data guard status:
Run in Production to get the database role and thread and sequence of archived_log
a. select name, database_role from v$database;
b. select thread#,max(sequence#) from v$archived_log group by thread#;
Run in DR Database:
a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;
Command to see MRP & RFS services are running or not
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
Take current sync status using below query:
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;
If there is any lag between the Production Database versus Standby Database then you have to check the following:
Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.
In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.
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:
Oracle Data Guard concept make sure high availability, disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.
Data Guard can be of two types:
Physical Stand By Database-
Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.
Logical Stand by Database-
Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.
Query to monitor the data guard status:
Run in Production to get the database role and thread and sequence of archived_log
a. select name, database_role from v$database;
b. select thread#,max(sequence#) from v$archived_log group by thread#;
Run in DR Database:
a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;
Command to see MRP & RFS services are running or not
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
Take current sync status using below query:
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;
If there is any lag between the Production Database versus Standby Database then you have to check the following:
Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.
In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.
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:
In this article we are going to see about how to refresh the standby database using RMAN Incremental Backups.
My scenario is , I have faced the log difference between the primary and standby databases and then I sync the primary and standby databases by taking the incremental backup. Let us see in brief about here.
Overview steps:
Step:1 I have verified the log sequence on both the primary and standby databases then i found that there is a log difference between them.
Step:2 we can resolve this issue by taking the incremental backup on primary database and then restoring it in the standby database.
Step:3First of all stop the redo transfer from primary to standby database by setting the log_archive_dest_2=DEFER.
Step:4 Find out the current_scn from v$database view in standby database.
Step:5 Then on primary, connect to the RMAN and take the incremental backup from the scn (which we noted in the standby database)
Step:6 Create the standby control file in primary database.
Step:7 By using server copy transfer the backup files, standby control file to the standby database.
Step:8In standby database, replace the control file from backup.
Step:9 Stop the MRP(Managed Recovery Process) in standby database and shut down the database.
Step:10Mount the standby database and connect to the RMAN, then catalog the backup pieces.
Step:11 Recover the standby database by using the incremental backup taken from the primary database.
Step:12 Enable log_archive_dest_state_2 parameter on primary and enable the MRP process on standby database.
Step:13 Then verify the log sequence sync between primary and standby databases.
Step:14 Finally we have refreshed/sync the standby database by using the incremental backup.
Step:1 Verify the log sequence on both primary and secondary database and found that there is a log difference between them.
on primary:
on standby:
Step:2 set the log_archive_dest_2=defer in primary database.
Step:3 Look for the current scn from the standby database.
Step:4 Take the incremental backup starting from that scn because after this scn only our primary and standby databases are not in sync.
Step:5 Create the standby controlfile in primary database.
Step:6 Move the incremental backup, standby controlfile from primary database to standby database through scp(server copy) and in standby database replace the controlfile from backup .
Step:7 Stop the MRP process in standby database.
Step:8 Recover the standby database by using the incremental backup taken from the primary database.
Step:9 Set the log_archive_dest_2=enable on primary database and start the MRP process in standby database.
Step:10 Start the MRP process on standby database.
Step:11 Verify the log sequence on both primary database and standby database, now both are in sync.
On primary:
On standby:
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
It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.
Overview steps:
Step:1 Check the db name, open mode, database role of the primary and standby databases.
Step:2 select switchover status on primary & standby db.
Step:3 Check that there is no active users connected to the databases.
Step:4Switch the current online redo log file on primary database and verify that it has been applied in the standby database.
Step:5Connect with primary database and initiate the switchover.
Step:6 Bounce the primary db and check the switchover status.
Step:7 Then convert the physical standby into primary db.(stop the MRP process)
Step:8 Open the new standby db in read only mode.
Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.
Step:1 Check the db name, open mode, database role of the primary and standby databases.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ WRITE PRIMARY
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ ONLY PHYSICAL STANDBY
Step:2 Check that there is no active users connected to the databases.
Step:4 Check the log sequence number in primary and standby db(before switchover).
Step:3 In this step primary db is converted into standby by giving the following command.
SQL>alter database commit to switchover to physical standby with session shutdown;
Database altered
SQL>alter database mount standby database;
Database altered
Step:5 Check name, open_mode, database_role of new standby database.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ ONLY PHYSICAL STANDBY
Step:6 Then start to apply the redo log (MRP process) on primary(current standby).
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
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
Step:1 verify the name, open mode, roles in primary and standby databases.
Step:2 Look into the max(sequence#) value from v$thread view on both sides.
Step:3 Check for unsupported datatypes in logical standby in primary db.
Step:4Find the tables without unique identifier in primary database.
Step;5 At Primary Database, Build a Log Miner in redo data So that sql apply can Interpret the redo changes.
Step:6 Enable the log _archive_dest_3 parameter for primary db.
Step:7 logical supplemental is automatically enabled by executing this, if not already enabled.
Step:8 Cancel recovery at Physical Standby Database.
Step:9 Create a spfile and then mount the database. In order to create logical standby at the mount stage.
Step:10 Enable the log _archive_dest_3 parameter for standby db.
Step:11 Open the database with reset log option.
Step:12 Then start the logical recovery.
Step:13verify the database has been converted to logical standby databases.
Step:1 Check name, open_mode, database_role of primary and standby databases.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ ONLY PHYSICAL STANDBY
Step:2 Verify the max(sequence#) from v$thread view on both the sides, it should have same max(sequence#) value.
on primary side
SQL> select max(sequence#) from v$thread;
MAX(SEQUENCE#)
--------------
24
on standby side
SQL> select max(sequence#) from v$thread;
MAX(SEQUENCE#)
--------------
24
Step:3 Check for unsupported datatypes in logical standby in primary db and Find the tables without unique identifier in primary database.
Step:4 execute dbms_logstdby.build,While executing this,logical supplemental is automatically enabled,if not already enabled and also enable the log_archive_dest_3 parameter.
Step:9 check whether database role of db has changed from physical standby to logical standby.once it is altered it is clear that we have successfully converted our physical standby into logical standby.
SQL> select name, open_mode, database_role from v$database;
NAME DATABASE_ROLE
--------- --------------------
CANADA LOGICAL STANDBY
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
It allows to perform read-write operation on the standby database.ie converting the physical standby to snapshot standby database. On that, we can do all types of testing or it can be used as a development database. After our testing process is over we can convert our snapshot standby database in to physical standby database and changes done to the snapshot standby will be reverted.
A snapshot standby database receives and archives redo data but it does not apply the redo data from the primary database.
NOTE: FRA (Fast Recovery Area) must be configured in physical standby database but it is not necessary to have flashback enabled.
Overview steps:
Step:1 verify open_mode, database_role of the standby db it should be in read only with apply, physical standby
Step:2 cancel the recovery process(MRP)
Step:3Enable the flashback_mode in standby, it can be enabled after specifying the location of the db_recovery_file_dest (db recovery area)
Step:4 Then we can convert our physical standby to snapshot standby database.
Step:1 check the open_mode, database_role of the database.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
Step:2 we must stop the redo apply process using the following command.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
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
Database mounted.
Step:3 Enable the flashback to convert it into snapshot standby database.
SQL> alter database flashback on;
Database altered.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
-------------------------------- ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/
db_recovery_file_dest_size big integer 8256M
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step:5 Convert the physical standby to snapshot standby using below command.
SQL> alter database convert to snapshot standby;
Database altered
Step:6 Mount the db and open it.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
Step:7 select the name of the restore point, gurantee_flashback_databaseinformation’s from v$restore_point view, where gurantee_flashback_database parameter tells that whether flashback log files will be kept to ensure a flashback to this point.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
NAME GUA
---------------------------------------------------- ----------------
SNAPSHOT_STANDBY_REQUIRED_02/18/2021 01:27:55 YES
Step:8 check the role of the db whether it has converted into snapshot standby.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
SNAPSHOT STANDBY
Step:9 Now I am performing some DML operations, creating a table student and adding 100000 rows in it.
SQL> create table student(id number(5));
Table created.
SQL> begin
for i in 1 .. 100000 loop
insert into student values(1);
end loop;
end;
PL/SQL procedure successfully completed.
SQL> select count(*) from student;
COUNT(*)
----------
100000
SQL> commit;
Commit complete.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
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
Database mounted.
Step:10 Converting snapshot standby db to physical standby.
SQL> alter database convert to physical standby;
Database altered.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
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
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
Step:11 Hence we converted our snapshot standby to physical standby, the DML operations that we have done in snapshot standby cannot be retrieved here.
SQL> select * from student;
select * from student
*
ERROR at line 1:
ORA-00942: table or view does not exist
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
