Skip to content

Converting Standby Database to Snapshot (4/4) - With Flashback Tablespace Disabled

  • by
  • March 26, 2013August 5, 2020
Converting Standby Database to Snapshot (3/4) - With Flashback Database Disabled
Some standby conversions could be more complicated than usual, especially converting back to physical standby. Here is a case: some of flashback are disabled on tablespace-level even though it's enabled on database-level.

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 successfully

Since 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> startup

Convert 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:
ERROR

There 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.

Go Back to Overview Page -Converting Standby Database to Snapshot (0/4) - An Overview

Leave a Reply

Your email address will not be published.