Check the status before conversion.
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;
INST_ID OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
2 MOUNTED PHYSICAL STANDBY NOT ALLOWED
1 MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> select name, flashback_on from v$tablespace;
NAME FLA
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS NO
TEMP YES
EXAMPLE NO
UNDOTBS2 YES
7 rows selected.
SQL>You can see flashback are disabled in two of them. We may expect that it could be troublesome when the snapshot standby convert back to a physical one.
Let's convert the standby to snapshot.
DGMGRL> show database standb
Database - standb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
standb1 (apply instance)
standb2
Database Status:
SUCCESS
DGMGRL> convert database standb to snapshot standby;
Converting database "standb" to a Snapshot Standby database, please wait...
Database "standb" converted successfullySince we need only one instance of the cluster database, we restart only one instance.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> startupConvert back to physical standby at the first attempt.
DGMGRL> show database standb
Database - standb
Role: SNAPSHOT STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 27 minutes 2 seconds
Instance(s):
standb1 (apply instance)
standb2
Database Status:
SUCCESS
DGMGRL> convert database standb to physical standby;
Converting database "standb" to a Physical Standby database, please wait...
Operation requires shutdown of instance "standb1" on database "standb"
Shutting down instance "standb1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standb1" on database "standb"
Starting instance "standb1"...
ORACLE instance started.
Database mounted.
Continuing to convert database "standb" ...
Error: ORA-38753: Cannot flashback data file ; no flashback log data.
Failed.
Failed to convert database "standb"It failed due to no flashback log data on some tablespaces.
Let's correct the errors by offlining their datafiles and enabling flashback the two tablespaces. But I think it's too late to do it.
SQL> select vd.ts#, vt.name, vd.file#, vd.status from v$datafile vd, v$tablespace vt where vd.ts# = vt.ts# and vt.flashback_on = 'NO';
TS# NAME FILE# STATUS
---------- ------------------------------ ---------- -------
4 USERS 4 ONLINE
6 EXAMPLE 5 ONLINE
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter tablespace users flashback on;
Tablespace altered.
SQL> alter tablespace example flashback on;
Tablespace altered.
SQL> select vd.ts#, vt.name, vt.flashback_on, vd.file#, vd.status from v$datafile vd, v$tablespace vt where vd.ts# = vt.ts#;
TS# NAME FLA FILE# STATUS
---------- ------------------------------ --- ---------- -------
0 SYSTEM YES 1 SYSTEM
1 SYSAUX YES 2 ONLINE
2 UNDOTBS1 YES 3 ONLINE
4 USERS YES 4 OFFLINE
5 UNDOTBS2 YES 6 ONLINE
6 EXAMPLE YES 5 OFFLINE
6 rows selected.
SQL>Convert back to physical standby at the second attempt.
DGMGRL> convert database standb to physical standby;
Converting database "standb" to a Physical Standby database, please wait...
Error: ORA-19926: Database cannot be converted at this time
Failed.
Failed to convert database "standb"
DGMGRL> Oh, I forgot to restart the instance to interrupt the broken converting process.
Restart the instance.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 792725496 bytes
Database Buffers 41943040 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.Convert back to physical standby at the last attempt.
DGMGRL> convert database standb to physical standby;
Converting database "standb" to a Physical Standby database, please wait...
Operation requires shutdown of instance "standb1" on database "standb"
Shutting down instance "standb1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standb1" on database "standb"
Starting instance "standb1"...
ORACLE instance started.
Database mounted.
Continuing to convert database "standb" ...
Operation requires shutdown of instance "standb1" on database "standb"
Shutting down instance "standb1"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standb1" on database "standb"
Starting instance "standb1"...
ORACLE instance started.
Database mounted.
Database "standb" converted successfully
DGMGRL>We succeed finally. But we have lots of jobs to do.
Check the status of data guard broker.
DGMGRL> show database standb
Database - standb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
standb1 (apply instance)
standb2
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Status:
ERRORThere is an error to initiate apply server.
Start the apply service manually.
SQL> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 4 belongs to an orphan incarnation
ORA-01110: data file 4: '+DATA/standb/datafile/users.268.799012479'It doesn't work and shows the datafile 4 belongs to an orphan incarnation. Is that true? If it's true, we need to use RMAN to restore the datafiles.
That is, if some tablespaces disabled the flashback before the converting, the only way to go back is to use RMAN to restore data files (for minimal efforts), tablespace or database. This will force the data files to abandon the orphan incarnation.
Check incarnation and restore datafile 4 and 5.
[oracle@standby01 ~]$ rman target /
...
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- ------- ---------- ----------
1 1 COMPDB 841830157 PARENT 1 15-AUG-09
2 2 COMPDB 841830157 PARENT 945184 10-SEP-11
3 3 COMPDB 841830157 CURRENT 2524114 29-OCT-12
4 4 COMPDB 841830157 ORPHAN 3674313 22-MAR-13
RMAN> restore datafile 4,5;
Starting restore at 22-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=standb1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA/standb/datafile/users.268.799012479
channel ORA_DISK_1: restoring datafile 00005 to +DATA/standb/datafile/example.270.799012469
channel ORA_DISK_1: reading from backup piece +DATA/standb/backupset/2012_12_17/nnndf0_tag20121217t110610_0.1079.802263975
channel ORA_DISK_1: piece handle=+DATA/standb/backupset/2012_12_17/nnndf0_tag20121217t110610_0.1079.802263975 tag=TAG20121217T110610
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 22-MAR-13
RMAN>Thereafter, we start the apply service instead of recovering by RMAN.
DGMGRL> enable database standb
Enabled.
DGMGRL> show database standb
Database - standb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 7 days 15 hours 12 minutes 21 seconds
Real Time Query: OFF
Instance(s):
standb1 (apply instance)
standb2
Database Status:
SUCCESS
DGMGRL>Now, all are back to normal. Don't worry, the apply lag will be back to normal soon or later.