Use SQL*Plus to Convert to Snapshot Standby
Summary
This procedure is more complex, and therefore more error-prone than either using OEM or DGMGRL.
It also requires one (1) terminal session to the standby systems.
References
Oracle Manuals
Preparation
Since I have been using a data guard system controlled by the Data Guard Broker, I need to do some preparation.
Set Up
I am using the following systems:
Disable Data Guard Broker
Firstly, I need to disable Data Guard Broker.I did this by removing the data guard broker configuration as follows (on PADSTOW):
[oracle@padstow ~]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - ocm12.yaocm.id.au Protection Mode: MaxPerformance Members: ocm12 - Primary database ocm12_botany - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 65 seconds ago) DGMGRL> remove configuration Removed configuration DGMGRL> show configuration ORA-16532: Oracle Data Guard broker configuration does not exist Configuration details cannot be determined by DGMGRL DGMGRL> exit [oracle@padstow ~]$
Procedure
The procedure is in 10.7.1 Converting a Physical Standby Database into a Snapshot Standby Database.
Status Before Conversion
I ran the following SQL*Plus command on BOTANY:
select OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL from v$database;
The output is:
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- -------------------- READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
It looks like the instance is in Active Data Guard mode.
Startup in MOUNT Mode
On the physical standby database instance, I shutdown the instance on BOTANY and mounted as follows:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1258291200 bytes Fixed Size 2923920 bytes Variable Size 956301936 bytes Database Buffers 285212672 bytes Redo Buffers 13852672 bytes Database mounted.
The status of the standby instance is now:
SQL> select OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL from v$database; OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- -------------------- MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
Convert to Snapshot Standby
On the physical standby database instance, I converted the instance on BOTANY to SNAPSHOT STANDBY as follows:
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
The expected output is simply:
Database altered.
To complete the conversion, I have to open the database as READ/WRITE:
ALTER DATABASE OPEN READ WRITE;
The expected response is:
Database altered.
Verify Status
The status of the standby instance is verified through the following command:
select OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL from v$database;
The expected response is:
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- -------------------- READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
Verify Role
Verify that the instance is in the correct role:
select database_role from v$database;
The expected response is:
DATABASE_ROLE ---------------- SNAPSHOT STANDBY
Verify Restore Point
The restore point is created automatically. This can be verfied as follows:
select name, time from v$restore_point;
A sample response is:
NAME TIME --------------------------------------------- ------------------------------- SNAPSHOT_STANDBY_REQUIRED_04/23/2019 19:55:57 23-APR-19 07.55.57.000000000 PM