Convert Snapshot database to Physical Standby database
Table of Contents
___________________________________________________________________________________________________
0. Overview
1. Environment
2. Configure Snapshot Standby Database (DELL_DG)
3. Verify Primary Database archive logs really applying on snapshot database?
4. Covert to physical standby from snapshot standby
5. Verify snapshot database changes flushed out after converting to physical standby
6. Verify Archive log GAP
___________________________________________________________________________________________________
Primary database changes will not applied to snapshot standby database why because there is no MRP process running on snapshot database.
Whatever changes done on snapshot standby will be flushed out once convert back to physical standby database from snapshot standby.
PRIMARY: (DELL)
Platform : Linuxx86_64 Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version : Oracle 11.2.0.4 File system : Normal DB Name : DELL DB_UNIQUE_NAME : DELL Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/11.2.0.4
STANDBY: (DELL_DG)
Platform : Linuxx86_64 Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102 DB Version : Oracle 11.2.0.4 File system : Normal DB Name : DELL DB_UNIQUE_NAME : DELL_DG Flashback : Disabled Oracle Home Path : /u01/app/oracle/product/11.2.0.4
2. Configure Snapshot Standby Database (DELL_DG)
3. Verify Primary Database archive logs really applying on snapshot database?
On Primary
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL PRIMARY READ WRITE SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 62 <---- SQL>
On Standby
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG SNAPSHOT STANDBY READ WRITE SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 58 <---- new archive logs not applied on standby SQL> SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 61 ARCH CLOSING 58 ARCH CONNECTED 0 ARCH CLOSING 62 RFS IDLE 0 RFS IDLE 63 RFS IDLE 0 7 rows selected. SQL> SQL> !ps -ef | grep mrp oracle 28016 8812 0 16:12 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp oracle 28018 28016 0 16:12 pts/0 00:00:00 grep mrp SQL> Primary database changes will not applied to snapshot standby database why because there is no MRP process running on snapshot database.
4. Covert to physical standby from snapshot standby
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG SNAPSHOT STANDBY READ WRITE SQL> SQL> create user raj identified by raj default tablespace users temporary tablespace temp; User created. SQL> grant connect, resource to raj; Grant succeeded. SQL> conn raj/raj; Connected. SQL> create table test (a number); Table created. SQL> insert into test values (1); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> commit; Commit complete. SQL> select count(*) from test; COUNT(*) ---------- 3 <----- SQL> SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG SNAPSHOT STANDBY READ WRITE SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes Database mounted. SQL> SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- MOUNTED DELL_DG SNAPSHOT STANDBY MOUNTED SQL> alter database convert to physical standby; Database altered. SQL> SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; select status,instance_name,database_role,open_mode from v$database,v$Instance * ERROR at line 1: ORA-01507: database not mounted SQL> SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2252864 bytes Variable Size 805310400 bytes Database Buffers 452984832 bytes Redo Buffers 8818688 bytes Database mounted. Database opened. SQL> SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG PHYSICAL STANDBY READ ONLY SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG PHYSICAL STANDBY READ ONLY WITH APPLY SQL> SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CLOSING 64 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 65 MRP0 WAIT_FOR_LOG 65 8 rows selected. SQL> SQL> !ps -ef | grep mrp oracle 29573 1 4 16:47 ? 00:00:01 ora_mrp0_DELL_DG oracle 29577 8812 0 16:48 pts/0 00:00:00 /bin/bash -c ps -ef | grep mrp oracle 29579 29577 0 16:48 pts/0 00:00:00 grep mrp SQL> Alert log Completed: ALTER DATABASE MOUNT Sat Dec 29 16:43:35 2018 Using STANDBY_ARCHIVE_DEST parameter default value as /u02/oracle/archive/DELL_DG Sat Dec 29 16:43:37 2018 RFS[1]: Assigned to RFS process 29466 RFS[1]: Selected log 5 for thread 1 sequence 64 dbid -329878705 branch 994761301 Sat Dec 29 16:43:37 2018 RFS[2]: Assigned to RFS process 29468 RFS[2]: Selected log 4 for thread 1 sequence 63 dbid -329878705 branch 994761301 Starting Data Guard Broker (DMON) Archived Log entry 125 added for thread 1 sequence 63 ID 0xec5daac9 dest 1: Sat Dec 29 16:43:43 2018 INSV started with pid=29, OS id=29470 Sat Dec 29 16:43:43 2018 alter database convert to physical standby ALTER DATABASE CONVERT TO PHYSICAL STANDBY (DELL_DG) Killing 3 processes with pids 29464,29466,29468 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 29462 Flashback Restore Start Flashback Restore Complete Drop guaranteed restore point Stopping background process RVWR Deleted Oracle managed file /u02/oracle/oradata/fast_recovery_area/DELL_DG/flashback/o1_mf_g2h0cny8_.flb Deleted Oracle managed file /u02/oracle/oradata/fast_recovery_area/DELL_DG/flashback/o1_mf_g2h0cpxn_.flb Guaranteed restore point dropped Clearing standby activation ID 3966853654 (0xec716216) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Shutting down archive processes Archiving is disabled Sat Dec 29 16:43:48 2018 ARCH shutting down ARC3: Archival stopped Sat Dec 29 16:43:48 2018 ARCH shutting down ARC2: Archival stopped Sat Dec 29 16:43:48 2018 ARCH shutting down ARC1: Archival stopped Sat Dec 29 16:43:48 2018 ARCH shutting down ARC0: Archival stopped Sat Dec 29 16:43:58 2018 Completed: Data Guard Broker cleanup Restarting Data Guard Broker (DMON) Sat Dec 29 16:43:58 2018 Completed: alter database convert to physical standby Sat Dec 29 16:43:58 2018 DMON started with pid=21, OS id=29475 .. .. .. Shutting down instance (immediate) Shutting down instance: further logons disabled Stopping background process MMNL Stopping background process MMON License high water mark = 4 All dispatchers and shared servers shutdown ALTER DATABASE CLOSE NORMAL ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Shutting down Data Guard Broker processes Sat Dec 29 16:46:40 2018 Stopping background process VKTM ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Sat Dec 29 16:46:42 2018 Instance shutdown complete Sat Dec 29 16:46:45 2018 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Initial number of CPU is 1 Number of processor cores in the system is 1 Number of processor sockets in the system is 1 Picked latch-free SCN scheme 3 Autotune of undo retention is turned on. IMODE=BR ILAT =27 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options. ORACLE_HOME = /u01/app/oracle/product/11.2.0.4 System name: Linux Node name: rac2.rajasekhar.com Release: 4.1.12-37.5.1.el6uek.x86_64 Version: #2 SMP Thu Jun 9 15:56:37 PDT 2016 Machine: x86_64 Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0.4/dbs/spfileDELL_DG.ora System parameters with non-default values: processes = 150 memory_target = 1216M control_files = "/u02/oracle/oradata/DELL_DG/control01.ctl" control_files = "/u02/oracle/oradata/fast_recovery_area/DELL_DG/control02.ctl" db_file_name_convert = "/u02/oracle/oradata/DELL" db_file_name_convert = "/u02/oracle/oradata/DELL_DG" log_file_name_convert = "/u02/oracle/oradata/DELL" log_file_name_convert = "/u02/oracle/oradata/DELL_DG" db_block_size = 8192 compatible = "11.2.0.4.0" log_archive_dest_1 = "LOCATION=/u02/oracle/archive/DELL_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DELL_DG" log_archive_dest_2 = "SERVICE=DELL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DELL" log_archive_dest_state_1 = "ENABLE" log_archive_dest_state_2 = "ENABLE" fal_client = "DELL_DG" fal_server = "DELL" log_archive_config = "DG_CONFIG=(DELL,DELL_DG)" log_archive_format = "%t_%s_%r.dbf" db_recovery_file_dest = "/u02/oracle/oradata/fast_recovery_area" db_recovery_file_dest_size= 3G standby_file_management = "AUTO" undo_tablespace = "UNDOTBS1" remote_login_passwordfile= "EXCLUSIVE" db_domain = "" dispatchers = "(PROTOCOL=TCP) (SERVICE=DELL_DGXDB)" audit_file_dest = "/u01/app/oracle/admin/DELL_DG/adump" audit_trail = "DB" db_name = "DELL" db_unique_name = "DELL_DG" open_cursors = 300 dg_broker_start = TRUE diagnostic_dest = "/u01/app/oracle" Sat Dec 29 16:46:45 2018 PMON started with pid=2, OS id=29500 Sat Dec 29 16:46:45 2018 PSP0 started with pid=3, OS id=29502 Sat Dec 29 16:46:46 2018 VKTM started with pid=4, OS id=29505 at elevated priority VKTM running at (1)millisec precision with DBRM quantum (100)ms Sat Dec 29 16:46:46 2018 GEN0 started with pid=5, OS id=29509 Sat Dec 29 16:46:46 2018 DIAG started with pid=6, OS id=29511 Sat Dec 29 16:46:46 2018 DBRM started with pid=7, OS id=29513 Sat Dec 29 16:46:46 2018 DIA0 started with pid=8, OS id=29515 Sat Dec 29 16:46:46 2018 MMAN started with pid=9, OS id=29517 Sat Dec 29 16:46:46 2018 DBW0 started with pid=10, OS id=29519 Sat Dec 29 16:46:46 2018 LGWR started with pid=11, OS id=29521 Sat Dec 29 16:46:46 2018 CKPT started with pid=12, OS id=29523 Sat Dec 29 16:46:46 2018 SMON started with pid=13, OS id=29525 Sat Dec 29 16:46:46 2018 RECO started with pid=14, OS id=29527 Sat Dec 29 16:46:46 2018 MMON started with pid=15, OS id=29529 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Sat Dec 29 16:46:46 2018 MMNL started with pid=16, OS id=29531 starting up 1 shared server(s) ... ORACLE_BASE from environment = /u01/app/oracle Sat Dec 29 16:46:46 2018 DMON started with pid=19, OS id=29537 Sat Dec 29 16:46:46 2018 ALTER DATABASE MOUNT ARCH: STARTING ARCH PROCESSES Sat Dec 29 16:46:50 2018 ARC0 started with pid=21, OS id=29544 ARC0: Archival started ARCH: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Sat Dec 29 16:46:50 2018 ARC1 started with pid=22, OS id=29546 Successful mount of redo thread 1, with mount id 3966920310 Physical Standby Database mounted. Lost write protection disabled Sat Dec 29 16:46:50 2018 ARC2 started with pid=23, OS id=29548 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC2: Becoming the heartbeat ARCH ARC2: Becoming the active heartbeat ARCH Sat Dec 29 16:46:50 2018 ARC3 started with pid=24, OS id=29550 ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Completed: ALTER DATABASE MOUNT Sat Dec 29 16:46:51 2018 ALTER DATABASE OPEN Data Guard Broker initializing... Data Guard Broker initialization complete Sat Dec 29 16:46:52 2018 Using STANDBY_ARCHIVE_DEST parameter default value as /u02/oracle/archive/DELL_DG Sat Dec 29 16:46:52 2018 Primary database is in MAXIMUM PERFORMANCE mode Sat Dec 29 16:46:52 2018 RFS[1]: Assigned to RFS process 29555 RFS[1]: Selected log 5 for thread 1 sequence 64 dbid -329878705 branch 994761301 RFS[2]: Assigned to RFS process 29557 RFS[2]: Selected log 4 for thread 1 sequence 65 dbid -329878705 branch 994761301 Archived Log entry 126 added for thread 1 sequence 64 ID 0xec5daac9 dest 1: Starting Data Guard Broker (DMON) AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access Sat Dec 29 16:46:56 2018 INSV started with pid=28, OS id=29559 SMON: enabling cache recovery Dictionary check beginning Dictionary check complete Database Characterset is WE8MSWIN1252 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Physical standby database opened for read only access. Completed: ALTER DATABASE OPEN Sat Dec 29 16:46:56 2018 db_recovery_file_dest_size of 3072 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Sat Dec 29 16:47:32 2018 alter database recover managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (DELL_DG) Sat Dec 29 16:47:32 2018 MRP0 started with pid=29, OS id=29573 MRP0: Background Managed Standby Recovery process started (DELL_DG) Serial Media Recovery started Managed Standby Recovery not using Real Time Apply Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Clearing online redo logfile 1 /u02/oracle/oradata/DELL_DG/redo01.log Clearing online log 1 of thread 1 sequence number 65 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u02/oracle/oradata/DELL_DG/redo02.log Clearing online log 2 of thread 1 sequence number 2 Clearing online redo logfile 2 complete Media Recovery Log /u02/oracle/archive/DELL_DG/1_59_994761301.dbf Completed: alter database recover managed standby database disconnect from session Media Recovery Log /u02/oracle/archive/DELL_DG/1_60_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_61_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_62_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_63_994761301.dbf Media Recovery Log /u02/oracle/archive/DELL_DG/1_64_994761301.dbf Media Recovery Waiting for thread 1 sequence 65 (in transit)
5. Verify snapshot database changes flushed out after converting to physical standby
SQL> select username,account_status from dba_users where username='RAJ'; no rows selected <----- USER RAJ not exist... all snapshot data flushed out. SQL> select count(*) from raj.test; select count(*) from raj.test * ERROR at line 1: ORA-00942: table or view does not exist <----- SQL> SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN DELL_DG PHYSICAL STANDBY READ ONLY WITH APPLY SQL>
On PRIMARY SQL> select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 64 <----- SQL> On Standby SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 64 <--- SQL>
=================================== OR ===================================
How to do perform the same using Dataguard Broker
Using DG Broker Convert Snapshot standby to Physical standby
DGMGRL> SHOW CONFIGURATION; Configuration - DELL Protection Mode: MaxPerformance Databases: DELL - Primary database DELL_DG - Snapshot standby database <---- Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> DGMGRL> CONVERT DATABASE "DELL_DG" TO PHYSICAL STANDBY; Converting database "DELL_DG" to a Physical Standby database, please wait... Operation requires shutdown of instance "DELL_DG" on database "DELL_DG" Shutting down instance "DELL_DG"... Database closed. Database dismounted. ORACLE instance shut down. Operation requires startup of instance "DELL_DG" on database "DELL_DG" Starting instance "DELL_DG"... ORACLE instance started. Database mounted. Continuing to convert database "DELL_DG" ... Operation requires shutdown of instance "DELL_DG" on database "DELL_DG" Shutting down instance "DELL_DG"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "DELL_DG" on database "DELL_DG" Starting instance "DELL_DG"... ORACLE instance started. Database mounted. Database "DELL_DG" converted successfully DGMGRL> DGMGRL> SHOW CONFIGURATION; Configuration - DELL Protection Mode: MaxPerformance Databases: DELL - Primary database DELL_DG - Physical standby database <----- Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +