Tag Archives: snapshot database

Convert Snapshot database to Physical Standby database

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
___________________________________________________________________________________________________


0. Overview

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.


1. Environment

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>


6. Verify Archive log GAP

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: +65-94693551