this Document
Goal |
Solution |
References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
GOAL
***Checked for relevance on 20-Oct-2016***
Perform a conversion of a Snapshot Standby database back to a Physical Standby Database.
Perform checks relevant to a successful conversion.
Monitor the conversion process to ensure that it has been executed correctly without error.
SOLUTION
from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Primary database name: rs1_stb
Standby/Snapshot database name : rs1
Diskgroup : +DATA ,+FRA
The current configuration
The Primary Site
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 11:58:26 2013
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------
rs1_stb PRIMARY READ WRITE
The Standby Site
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 11:50:31 2013
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select db_unique_name,database_role,open_mode from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- --------------------
rs1 SNAPSHOT STANDBY READ WRITE
Checks to perform prior to the conversion
1. Check from a Data Guard Broker perspective the configuration is healthy and the configuration status is reported as "SUCCESS".
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@rs1_stb
Password:
Connected.
DGMGRL> show configuration;
Configuration - rs1_dg
Protection Mode: MaxPerformance
Databases:
rs1_stb - Primary database
rs1 - Snapshot standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
2. Confirm log shipping is occurring between the sites
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 12:08:18 2013
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
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 297
Next log sequence to archive 299
Current log sequence 299
Primary Site Alert Log
Details the switching occurring and the shipping of the logs
Thu Apr 18 11:57:15 2013
SMCO started with pid=24, OS id=3796
Thu Apr 18 12:09:04 2013
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 298 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 298 (LGWR switch)
Current log# 3 seq# 298 mem# 0: +DATA/rs1_stb/onlinelog/group_3.278.801239719
Current log# 3 seq# 298 mem# 1: +FRA/rs1_stb/onlinelog/group_3.315.801239719
Thu Apr 18 12:09:07 2013
Archived Log entry 380 added for thread 1 sequence 297 ID 0x6cd69690 dest 1:
Thu Apr 18 12:09:19 2013
LGWR: Standby redo logfile selected for thread 1 sequence 299 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 299 (LGWR switch)
Current log# 1 seq# 299 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
Current log# 1 seq# 299 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Thu Apr 18 12:09:22 2013
Archived Log entry 382 added for thread 1 sequence 298 ID 0x6cd69690 dest 1:
Standby Site Alert Log
Details the receiving of the logs in the Snapshot Standby
Thu Apr 18 11:52:11 2013
RFS[4]: Assigned to RFS process 3799
RFS[4]: Selected log 5 for thread 1 sequence 296 dbid 1823795963 branch 800115647
Archived Log entry 528 added for thread 1 sequence 296 ID 0x6cd69690 dest 3:
Thu Apr 18 11:53:30 2013
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=1521))))' SCOPE=MEMORY SID='rs1';
Thu Apr 18 11:54:09 2013
Starting background process SMCO
Thu Apr 18 11:54:09 2013
SMCO started with pid=23, OS id=3812
Thu Apr 18 12:09:03 2013
RFS[2]: Selected log 5 for thread 1 sequence 298 dbid 1823795963 branch 800115647
Thu Apr 18 12:09:04 2013
Archived Log entry 529 added for thread 1 sequence 297 ID 0x6cd69690 dest 3:
Thu Apr 18 12:09:18 2013
RFS[2]: Selected log 4 for thread 1 sequence 299 dbid 1823795963 branch 800115647
Thu Apr 18 12:09:19 2013
Archived Log entry 530 added for thread 1 sequence 298 ID 0x6cd69690 dest 3:
Perform the conversion from a Snapshot Standby to a Physical Standby
Given the shipping is successful and the broker configuration is begin reported as healthy the conversion back to a physical standby can be performed.
1. Start the broker command line and ensure a sys@TNSALIAS connection is made to ensure a successful shutdown and startup of the standby site required throughout this process.
Password:
Connected.
2. Issue the convert command from the broker
The convert command will perform the following steps
- Shutdown snapshot standby down and restart it to mount
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rs1" on database "rs1"
Starting instance "rs1"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
- Another shutdown of the standby is initiated once the flashback operation has been performed. This flashback will set the standby site back to a time prior to the conversion to a snapshot standby.
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
- The Standby site which has now been converted back to a Physical Standby site will now be restarted.
Starting instance "rs1"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database "rs1" converted successfully
Alert Log Extract from the Standby Site detailing the change from Snapshot Standby to Physical Standby
..
.
Fri Apr 19 11:41:46 2013
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
..
.
Fri Apr 19 11:41:55 2013
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 12
Completed: alter database CLOSE NORMAL
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
..
.
Fri Apr 19 11:42:03 2013
Instance shutdown complete
Fri Apr 19 11:42:04 2013
Starting ORACLE instance (normal)
..
.
Fri Apr 19 11:42:33 2013
RSM0 started with pid=41, OS id=13962
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (rs1)
krsv_proc_kill: Killing 4 processes (all RFS)
Flashback Restore Start
..
.
Fri Apr 19 11:42:50 2013
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped
Deleted Oracle managed file +FRA/rs1/flashback/log_6.412.802083919
Deleted Oracle managed file +FRA/rs1/flashback/log_4.265.800203569
Deleted Oracle managed file +FRA/rs1/flashback/log_3.308.801298337
Clearing standby activation ID 1836586315 (0x6d78154b)
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;
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Fri Apr 19 11:42:50 2013
ARC2: Becoming the active heartbeat ARCH
Clearing online redo logfile 1 +DATA/rs1/onlinelog/group_1.261.800115647
Clearing online log 1 of thread 1 sequence number 7
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/rs1/onlinelog/group_2.262.800115651
Clearing online log 2 of thread 1 sequence number 8
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/rs1/onlinelog/group_3.263.800115655
Clearing online log 3 of thread 1 sequence number 6
Clearing online redo logfile 3 complete
Completed: alter database convert to physical standby
Fri Apr 19 11:43:01 2013
Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 13967
RFS[4]: Selected log 4 for thread 1 sequence 304 dbid 1823795963 branch 800115647
Fri Apr 19 11:43:03 2013
Shutting down instance (immediate)
Shutting down instance: further logons disabled
..
.
Fri Apr 19 11:43:04 2013
NOTE: Deferred communication with ASM instance
All dispatchers and shared servers shutdown
alter database CLOSE NORMAL
ORA-1109 signalled during: alter database CLOSE NORMAL...
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
..
.
Completed: alter database DISMOUNT
..
.
Fri Apr 19 11:43:16 2013
Instance shutdown complete
Fri Apr 19 11:43:17 2013
Starting ORACLE instance (normal)
..
.
Fri Apr 19 11:43:31 2013
ARC3 started with pid=32, OS id=14084
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Physical Standby Database mounted.
Lost write protection disabled
ARC2: Becoming the active heartbeat ARCH
Completed: alter database mount
..
.
Fri Apr 19 11:43:44 2013
RSM0 started with pid=39, OS id=14112
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rs1';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rs1';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (rs1)
Fri Apr 19 11:43:48 2013
MRP0 started with pid=40, OS id=14114
MRP0: Background Managed Standby Recovery process started (rs1)
started logmerger process
Fri Apr 19 11:43:54 2013
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /tmp/1_284_800115647.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_285_800115647.dbf
Fri Apr 19 11:43:56 2013
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_286_800115647.dbf
Media Recovery Log /tmp/1_287_800115647.dbf
..
.
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_302_800115647.dbf
Media Recovery Log +FRA/rs1/archivelog/2013_04_19/thread_1_seq_303.308.813152615
Media Recovery Log +FRA/rs1/archivelog/2013_04_19/thread_1_seq_304.265.813152621
Media Recovery Waiting for thread 1 sequence 305 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 305 Reading mem 0
Mem# 0: +DATA/rs1/onlinelog/group_5.269.800367453
Mem# 1: +FRA/rs1/onlinelog/group_5.316.800367455
Standby Data Guard Broker Log detailing the change from Snapshot Standby to Physical Standby
04/18/2013 13:53:50
Creating process RSM0
04/18/2013 13:53:54
Process RSM0 re-created with PID = 7625
04/18/2013 13:56:39
Process RSM0, PID = 7625, will be killed
04/18/2013 13:56:46
Creating process RSM0
04/18/2013 13:56:50
Process RSM0 re-created with PID = 7635
04/19/2013 11:41:37
Data Guard notifying Oracle Clusterware of database role change
conversion to physical standby requires this database be restarted to the mounted state
04/19/2013 11:41:52
Notifying DMON of db close
Notifying RSM0 of db close
04/19/2013 11:41:56
Data Guard Broker shutting down
posting shutdown message to primary database 0x02001000
RSM0 successfully terminated
04/19/2013 11:41:58
Command CONVERT DATABASE rs1 completed with warning ORA-16570
>> DMON Process Shutdown <<
04/19/2013 11:42:22
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
dg_broker_config_file1 = "+DATA/rs1/dr1rs1.dat"
dg_broker_config_file2 = "+FRA/rs1/dr2rs1.dat"
04/19/2013 11:42:27
DMON Registering service rs1_DGB with listener(s)
Broker Configuration: "rs1_dg"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=3
Primary Database: rs1_stb (0x02010000)
Standby Database: rs1, Enabled Snapshot Standby (0x01010000)
04/19/2013 11:42:28
Data Guard notifying Oracle Clusterware of database role change
04/19/2013 11:42:32
Creating process RSM0
04/19/2013 11:43:00
CONVERT to PHYSICAL succeeded. Restart required.
04/19/2013 11:43:09
Data Guard Broker shutting down
posting shutdown message to primary database 0x02001000
RSM0 successfully terminated
04/19/2013 11:43:12
>> DMON Process Shutdown <<
04/19/2013 11:43:35
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
dg_broker_config_file1 = "+DATA/rs1/dr1rs1.dat"
dg_broker_config_file2 = "+FRA/rs1/dr2rs1.dat"
04/19/2013 11:43:39
DMON Registering service rs1_DGB with listener(s)
Broker Configuration: "rs1_dg"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=3
Primary Database: rs1_stb (0x02010000)
Standby Database: rs1, Enabled Physical Standby (0x01010000)
Physical standby bootstrap requires Oracle Clusterware buildup
04/19/2013 11:43:44
rs1 version check successfully completed
rs1 has current configuration metadata,
completing bootstrap
Creating process RSM0
04/19/2013 11:43:54
Notifying Oracle Clusterware that database is open
Data Guard notifying Oracle Clusterware to start services and other instances change
REFERENCES
NOTE:443720.1 - Using Snapshot Standby Database.NOTE:1623815.1 - Data Guard Snapshot Standby Archive Redo Log Management and Configuration