Convert RAC Physical Standby To Snapshot Standby

Note: For snapshot standby, you do not need Flashback enabled at database level

Convert physical standby to snapshot standby

On Standby Database

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- -----------
TESTDB    MOUNTED

SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_02/28/2025
21:40:27'.
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database convert to snapshot standby;

Database altered.

SQL>  alter database open;

Database altered.

SQL>  select name, guarantee_flashback_database from v$restore_point;

NAME                                            GURA
----------------------------------------------  -------------

SNAPSHOT_STANDBY_REQUIRED_02/28/2025 21:41:04   YES


SQL> create table student(id number(2), name varchar2(10));

Table created.

SQL>  insert into student values(1,'John');

1 row created.

SQL> insert into student values (2,'James');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from student;

       ID  NAME
---------- ----------
         1 John
         2 James

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

SQL>  alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             MOUNTED
PDB1                 MOUNTED

Have to restart database for all PDB open.

$ srvctl stop database -d TESTDB -o immediate

$ srvctl start database -d TESTDB -o open

SQL>  col NAME format a20
SQL>  select name, open_mode from gv$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB1                 READ WRITE
PDB$SEED             READ ONLY
PDB1                 READ WRITE

SQL> select name, open_mode, database_role from v$database;

NAME                 OPEN_MODE            DATABASE_ROLE
-------------------- -------------------- ----------------
TESTDB               READ WRITE           SNAPSHOT STANDBY

Revert back snapshot standby to physical standby

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ETSTDB    MOUNTED              SNAPSHOT STANDBY

SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance

$ srvctl stop  instance  -d TESTDB -i TESTDB2 -o immediate

SQL>  alter database convert to physical standby;

Database altered.

SQL>  select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

$ srvctl start  database -d TESTDB -o mount

SQL> alter database recover managed standby database disconnect;

Database altered.

Oracle ASM Thin Provisioned Attribute and Reclaim Storage Space

ASMFD Installation & Configuration

Verify the ASM Filer Driver (AFD) Kernel module has been loaded.

$ lsmod | grep afd
oracleafd             226702  1

To confirm ASM Filter Driver has already been installed, loaded, is supported and versions, use afddriverstate installed | loaded | supported | version.

$ afddriverstate installed
AFD-9203: AFD device driver installed status: 'true'

$ afddriverstate loaded
AFD-9205: AFD device driver loaded status: 'true'

$ afddriverstate supported
AFD-9200: Supported

$ afddriverstate version
AFD-9325:     Driver OS kernel version = 3.10.0-862.el7.x86_64.
AFD-9326:     Driver build number = RELEASE.
AFD-9231:     Driver build version = 19.0.0.0.0 (19.24.0.0.0).
AFD-9547:     Driver available build number = RELEASE.
AFD-9232:     Driver available build version = 19.0.0.0.0 (19.24.0.0.0).

To check the ASMFD filtering status:

$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'RACNODE1'

Check and list ASMFD disks.

$ asmcmd afd_lsdsk

Oracle ASM Thin Provisioned Attribute

The ASM Thin Provisioned attribute enables or disables the functionality to discard unused storage space after a diskgroup rebalance is completed.

Note: the ASM Thin Provisioned attribute only works with the ASM Filter Driver, setting it on non-ASMFD diskgroups has no effect.

The attribute value can be true to enable or false to disable the functionality. The default value is false.

Oracle ASM informs the storage array which space is no longer used and can be repurposed. If a rebalance is not running, a rebalance can be triggered manually with the ALTER DISKGROUP … REBALANCE SQL statement.

How to check ASM ‘thin_provisioned’ attribute ?

set linesize 250
 
column diskgroup heading 'Diskgroup' format a15
column name heading 'Attribute Name' format a25
column value heading 'Value' format a15
column read_only heading 'Read Only' format a15
 
SELECT
  SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) AS name,
  SUBSTR(a.value,1,24) AS value, read_only 
FROM
  V$ASM_DISKGROUP dg, 
  V$ASM_ATTRIBUTE a 
WHERE
  dg.group_number = a.group_number AND
  a.name NOT LIKE '%template%' AND
  a.name LIKE '%thin%'
/ 

Diskgroup       Attribute Name            Value           Read Only
--------------- ------------------------- --------------- ---------------
DATA2           thin_provisioned          true            N
FRA             thin_provisioned          FALSE           N
OCR             thin_provisioned          FALSE           N
REDO            thin_provisioned          true            N

$ asmcmd lsattr -G FRA | grep thin
thin_provisioned

We can easily change the ‘thin_provisioned’ attribute using ASMCA or using ‘alter disk group <DISK_GROUP> set attribute ‘thin_provisioned’=’TRUE’;’

SQL> alter diskgroup FRA set attribute 'thin_provisioned'='TRUE';
Diskgroup altered.

Since Oracle 18c the phase option includes the keywords WITH or WITHOUT, removing the need to use the underscore parameters, check your version documentation for limitations.

SQL> ALTER DISKGROUP DATA REBALANCE WITHOUT COMPACT;
Diskgroup altered.

SQL > alter diskgroup data2 rebalance with balance compact;
Diskgroup altered.

SQL> ALTER DISKGROUP DATA REBALANCE WITH BALANCE COMPACT POWER 1024 WAIT;
Diskgroup altered.

SQL>select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from gv$asm_operation;

INST_ID OPERA STAT POWER  SOFAR   EST_WORK   EST_RATE EST_MINUTES
------- ----- ---- ----- ------ ---------- ---------- -----------
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      2 REBAL WAIT     1
      1 REBAL RUN      1   1951    8634894       2475        3488
      1 REBAL DONE     1   1017       1017          0           0
      1 REBAL DONE     1      0          0          0           0

6 rows selected.

ORA-01157 When Standby Database Open Read Only

The following errors occur when trying to open a standby database read only:

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA2/TESTDB/DATAFILE/system.3203.1186181325'

When checking ASM file for SYSTEM tablespace, the file exists but with different name:

ASMCMD> ls -lt +DATA2/TESTDB/DATAFILE/system*
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   NOV 30 10:00:00  Y    SYSTEM.577.1186394617

It seems incantation issue from database control file.

SOLUTION

List incarnation:

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TESTDB   2969509608       PARENT  1          17-APR-19
2       2       TESTDB   2969509608       PARENT  1920977    27-NOV-24
3       3       TESTDB   2969509608       CURRENT 2848922    29-NOV-24

Rest incarnation:

RMAN> RESET DATABASE TO INCARNATION 2;

database reset to incarnation 2

RMAN>  list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TESTDB   2969509608       PARENT  1          17-APR-19
2       2       TESTDB   2969509608       CURRENT 1920977    27-NOV-24
3       3       TESTDB   2969509608       ORPHAN  2848922    29-NOV-24

Then standby database open read only successfully.

SQL>  alter database open read only;

Database altered.