As you might know, on ExaCC, it is possible to create spare clones. This is based on snapshot technology. There is no physical copy and the modified blocks will be written to the sparse clone, and in our case these blocks will be part of the ASM spare disk group. And of course, the sparse clone then needs a master read only. This article is not intended to explain the whole functioning of this technology. For more information on the technology, I would recommend you to read following blog article :

https://blogs.oracle.com/exadata/post/exadata-sparse-clones-and-monitor-sparse-disk-groups-size

Intention of this article is to show what will happen to your sparse during a switchover. In multitenant environment, on the standby side the sparse clone PDB will be seen in mount status, but not accessible. The sparse is made locally, that is to say on the primary database side where it was created, and only accessible on this site. The sparse clone is storage dependant and primary and standby are not sharing same shelf storage. So after a switchover, the sparse clone is not accessible until switchover back to the initial primary is performed. Let’s see how that works!

Lab description

For the demonstration we will use:

  • an ExaCC cluster named exacc-cl01 (currently the primary side) on which we will have a CDB named SWIT001T_CHZ2
  • an ExaCC cluster named exacc-cl02 (currently the standby side) on which we will have the physical standby CDB named SWIT001T_CHZ3
  • The PDB will be SWTET_APP_001T
  • The Master Read Only PDB will be SWTET_TMR_001T
  • The sparce clone PDB will be SWTET_APP_002T

Master Read Only creation

The command used to create the Master Read Only on the primary CDB was:

SQL> CREATE PUBLIC DATABASE LINK SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2 CONNECT TO C##USER IDENTIFIED BY ".................." USING 'SWIT001T_CHZ2';

SQL> CREATE PLUGGABLE DATABASE SWTET_TMR_001T FROM SWTET_APP_001T@"SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2"
            KEYSTORE IDENTIFIED BY "..." INCLUDING SHARED KEY
            PARALLEL 8
            SERVICE_NAME_CONVERT=('SWTET_APP_001T','SWTET_TMR_001T')
            REFRESH MODE MANUAL
            STANDBYS=NONE;

SQL> ALTER PLUGGABLE DATABASE SWTET_TMR_001T OPEN READ ONLY INSTANCES=ALL;

SQL> DROP PUBLIC DATABASE LINK SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2;

On the primary side, this looks like:

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 14:46:17 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 READ ONLY  NO
SQL>

On the standby side, this looks like:

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 14:46:48 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
SQL>

Which is correct as the Master Read Only SWTET_TMR_001T was created with STANDBYS=NONE option.

Sparse clone creation

The command used to create the sparse clone on the primary CDB was:

SQL> CREATE PLUGGABLE DATABASE SWTET_APP_002T FROM SWTET_TMR_001T
                KEYSTORE IDENTIFIED BY "..." INCLUDING SHARED KEY
                PARALLEL 8
                CREATE_FILE_DEST='+SPRC2'
                SERVICE_NAME_CONVERT=('SWTET_TMR_001T','SWTET_APP_002T','SWTET_APP_001T','SWTET_APP_002T')
                SNAPSHOT COPY
                STANDBYS=NONE;

SQL> ALTER PLUGGABLE DATABASE SWTET_APP_002T OPEN INSTANCES=ALL;

On the primary side, this looks like:

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:03:18 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 READ ONLY  NO
         5 SWTET_APP_002T                 READ WRITE NO
SQL>

The sparse clone PDB is opened as expected in READ/WRITE mode.

On the standby side, this looks like:

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:37:11 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 MOUNTED
SQL>

Which is correct as we used the standby=no option to create the sparse clone and in any case the snapshot is only locally as storage dependant.

Create some data in the spare clone

Let’s create some data into the spare clone.

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:28:39 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 READ ONLY  NO
         5 SWTET_APP_002T                 READ WRITE NO

SQL> alter session set container=SWTET_APP_002T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> create table TEST_SWITCH (id int, name varchar(100), run_exec date);

Table created.

SQL> insert into TEST_SWITCH values (0, 'Before switch', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from TEST_SWITCH;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Before switch                                                                                        06/06/2025 15:33:32

SQL>

Switchover to CHZ3

Let’s switchover from CHZ2 to CHZ3.

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] dgh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Jun 6 15:36:06 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@SWIT001T_CHZ2
Password:
Connected to "SWIT001T_CHZ2"
Connected as SYSDBA.

DGMGRL> show configuration lag

Configuration - swit001t_dgconf

  Protection Mode: MaxPerformance
  Members:
  SWIT001T_CHZ2 - Primary database
    SWIT001T_CHZ3 - Physical standby database
                    Transport Lag:      0 seconds (computed 0 seconds ago)
                    Apply Lag:          0 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL> validate database SWIT001T_CHZ3

  Database Role:     Physical standby database
  Primary Database:  SWIT001T_CHZ2

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    SWIT001T_CHZ2:  YES
    SWIT001T_CHZ3:  YES

  Temporary Tablespace File Information:
    SWIT001T_CHZ2 TEMP Files:  5
    SWIT001T_CHZ3 TEMP Files:  3

DGMGRL> switchover to SWIT001T_CHZ3;
Performing switchover NOW, please wait...
Operation requires a connection to database "SWIT001T_CHZ3"
Connecting ...
Connected to "SWIT001T_CHZ3"
Connected as SYSDBA.
New primary database "SWIT001T_CHZ3" is opening...
Oracle Clusterware is restarting database "SWIT001T_CHZ2" ...
Connected to "SWIT001T_CHZ2"
Switchover succeeded, new primary is "swit001t_chz3"
DGMGRL>

DGMGRL> show configuration lag

Configuration - swit001t_dgconf

  Protection Mode: MaxPerformance
  Members:
  SWIT001T_CHZ3 - Primary database
    SWIT001T_CHZ2 - Physical standby database
                    Transport Lag:      0 seconds (computed 1 second ago)
                    Apply Lag:          0 seconds (computed 1 second ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 7 seconds ago)

Create table in source pdb in new primary cl02

To check on the sparse clone database would react after the switchover, let’s add some data in the source PDB on the new primary (CHZ3).

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:40:16 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 MOUNTED

SQL> alter session set container=SWTET_APP_001T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> create table TEST_ON_STDBY (id int, name varchar(100), run_exec date);

Table created.

SQL> insert into TEST_ON_STDBY values (0, 'Cl08 is primary', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SWTET_APP_001T                 READ WRITE NO
		 
SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
SWIT001T1        exacc-cl02n1

SQL> select * from TEST_ON_STDBY;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Cl08 is primary                                                                                      06/06/2025 15:43:19

SQL>

Check table on new standby side cl01

We can check data on the standby side PDB on the cluster cl01.

oracle@exacc-cl01n1:~/ [grinf19] SWIT001T1

 ********************************************
 INSTANCE_NAME   : SWIT001T1
 DB_NAME         : SWIT001T
 DB_UNIQUE_NAME  : SWIT001T_CHZ2
 STATUS          : OPEN READ ONLY WITH APPLY
 LOG_MODE        : ARCHIVELOG
 USERS/SESSIONS  : 4/19
 DATABASE_ROLE   : PHYSICAL STANDBY
 FLASHBACK_ON    : YES
 FORCE_LOGGING   : YES
 VERSION         : 19.26.0.0.0
 CDB_ENABLED     : YES
 PDBs            : PDB$SEED  SWTET_APP_001T  SWTET_APP_002T  SWTET_TMR_001T
 ********************************************

 PDB color: pdbname=mount, pdbname=open read-write, pdbname=open read-only
 Statustime: 2025-06-06 15:45:25

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:45:27 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 READ ONLY  NO

The Master Read Only PDB is in MOUNT status, opening it in READ ONLY mode would not be possible. This would make sense as it is the reference for the sparse clone and we created it with the no standby option.

SQL> alter pluggable database SWTET_TMR_001T open read only instances=all;
alter pluggable database SWTET_TMR_001T open read only instances=all
*
ERROR at line 1:
ORA-01173: data dictionary indicates missing data file from system tablespace

Let’s check if the source PDB is having the new created data on the new standby side cl01.

SQL> alter session set container=SWTET_APP_001T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
SWIT001T1        exacc-cl01n1

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SWTET_APP_001T                 READ ONLY  NO

SQL> select * from TEST_ON_STDBY;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Cl08 is primary                                                                                      06/06/2025 15:43:19

SQL>

Yes, we have the new data.

Run switchover back

Let’s switch back to cl01.

DGMGRL> show configuration lag

Configuration - swit001t_dgconf

  Protection Mode: MaxPerformance
  Members:
  SWIT001T_CHZ3 - Primary database
    SWIT001T_CHZ2 - Physical standby database
                    Transport Lag:      0 seconds (computed 0 seconds ago)
                    Apply Lag:          0 seconds (computed 0 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 11 seconds ago)

DGMGRL> validate database SWIT001T_CHZ2;

  Database Role:     Physical standby database
  Primary Database:  SWIT001T_CHZ3

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    SWIT001T_CHZ3:  YES
    SWIT001T_CHZ2:  YES

  Temporary Tablespace File Information:
    SWIT001T_CHZ3 TEMP Files:  3
    SWIT001T_CHZ2 TEMP Files:  5

DGMGRL> switchover to SWIT001T_CHZ2;
Performing switchover NOW, please wait...
Operation requires a connection to database "SWIT001T_CHZ2"
Connecting ...
Connected to "SWIT001T_CHZ2"
Connected as SYSDBA.
New primary database "SWIT001T_CHZ2" is opening...
Oracle Clusterware is restarting database "SWIT001T_CHZ3" ...
Connected to "SWIT001T_CHZ3"
Switchover succeeded, new primary is "swit001t_chz2"

DGMGRL> show configuration lag

Configuration - swit001t_dgconf

  Protection Mode: MaxPerformance
  Members:
  SWIT001T_CHZ2 - Primary database
    SWIT001T_CHZ3 - Physical standby database
                    Transport Lag:      0 seconds (computed 5 seconds ago)
                    Apply Lag:          0 seconds (computed 5 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

On the standby side, this would look like:

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:51:33 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 MOUNTED
SQL>

On the primary side, this would look like:

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:52:24 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 READ WRITE NO

Reopening the Master Read Only would fail.

SQL> alter pluggable database SWTET_TMR_001T open read only instances=all;
alter pluggable database SWTET_TMR_001T open read only instances=all
*
ERROR at line 1:
ORA-01173: data dictionary indicates missing data file from system tablespace

Add data in source PDB

Adding new data in source PDB would be of course successful.

SQL> alter session set container=SWTET_APP_001T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
SWIT001T1        exacc-cl01n1

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SWTET_APP_001T                 READ WRITE NO

SQL> insert into TEST_ON_STDBY values (1,'primary back to cl07',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from TEST_ON_STDBY;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Cl08 is primary                                                                                      06/06/2025 15:43:19
         1 primary back to cl07                                                                                 06/06/2025 15:54:54

SQL>

And data would be also available in the source PDB on the standby side.

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:56:00 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 MOUNTED

SQL> alter session set container=SWTET_APP_001T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
SWIT001T1        exacc-cl02n1

SQL> select * from TEST_ON_STDBY;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Cl08 is primary                                                                                      06/06/2025 15:43:19
         1 primary back to cl07                                                                                 06/06/2025 15:54:54

SQL>

<h3>Add new data in spare clone</h3>

Let’s add new data in the sparse clone.

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 15:57:00 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 MOUNTED
         5 SWTET_APP_002T                 READ WRITE NO

SQL> alter session set container=SWTET_APP_002T;

Session altered.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> set lines 300

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ----------------------------------------------------------------
SWIT001T1        exacc-cl01n1

SQL> select * from TEST_SWITCH;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Before switch                                                                                        06/06/2025 15:33:32

SQL> insert into TEST_SWITCH values (1,'After switchover and back cl07', sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from TEST_SWITCH;

        ID NAME                                                                                                 RUN_EXEC
---------- ---------------------------------------------------------------------------------------------------- -------------------
         0 Before switch                                                                                        06/06/2025 15:33:32
         1 After switchover and back cl07                                                                       06/06/2025 15:59:01

SQL>

Adding new data into the sparse clone is possible.

Drop the sparse clone

Command to drop the sparse clone would be:

SQL> ALTER PLUGGABLE DATABASE SWTET_APP_002T CLOSE IMMEDIATE INSTANCES=ALL;

SQL> DROP PLUGGABLE DATABASE SWTET_APP_002T INCLUDING DATAFILES;

On the primary side, this would now look like:

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 17:25:32 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_TMR_001T                 MOUNTED
SQL>

On the standby side, this would now look like:

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 17:26:03 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_TMR_001T                 MOUNTED
SQL>

Refresh Master Read Only

We can refresh the Master Read Only with following command.

SQL> drop pluggable database SWTET_TMR_001T including datafiles;

SQL> CREATE PUBLIC DATABASE LINK SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2 CONNECT TO C##USER IDENTIFIED BY ".................." USING 'SWIT001T_CHZ2';

SQL> CREATE PLUGGABLE DATABASE SWTET_TMR_001T FROM SWTET_APP_001T@"SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2"
            KEYSTORE IDENTIFIED BY "..." INCLUDING SHARED KEY
            PARALLEL 8
            SERVICE_NAME_CONVERT=('SWTET_APP_001T','SWTET_TMR_001T')
            REFRESH MODE MANUAL
            STANDBYS=NONE;

SQL> ALTER PLUGGABLE DATABASE SWTET_TMR_001T OPEN READ ONLY INSTANCES=ALL;

SQL> DROP PUBLIC DATABASE LINK SWIT001T_e9085be389a239c888e5c0baf4f50405@SWIT001T_CHZ2;

Create spare clone again

And we can create the sparse clone again with the same command.

SQL> CREATE PLUGGABLE DATABASE SWTET_APP_002T FROM SWTET_TMR_001T
                KEYSTORE IDENTIFIED BY "..." INCLUDING SHARED KEY
                PARALLEL 8
                CREATE_FILE_DEST='+SPRC2'
                SERVICE_NAME_CONVERT=('SWTET_TMR_001T','SWTET_APP_002T','SWTET_APP_001T','SWTET_APP_002T')
                SNAPSHOT COPY
                STANDBYS=NONE;

SQL> ALTER PLUGGABLE DATABASE SWTET_APP_002T OPEN INSTANCES=ALL;

On the primary side, this looks like:

oracle@exacc-cl01n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 17:38:12 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ WRITE NO
         4 SWTET_APP_002T                 READ WRITE NO
         5 SWTET_TMR_001T                 READ ONLY  NO
SQL>

And on the standby side:

oracle@exacc-cl02n1:~/ [SWIT001T1 (CDB$ROOT)] sqh

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 6 17:38:38 2025
Version 19.26.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.26.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SWTET_APP_001T                 READ ONLY  NO
         4 SWTET_APP_002T                 MOUNTED
         5 SWTET_TMR_001T                 MOUNTED
SQL>

To wrap up…

Sparse clone works successfully in Data Guard environment. Sparse clone are only available locally, and so available only when the CDB hosting initially the sparse clone PDB has the primary role. Switching to the other site is not a problem. The sparse clone would be available again once switching back. At last, we can then easily refresh the master read only and create again the spare clone.

Post Views: 63
Thumbnail [60x60]
by
Marc Wagner