[OCI-26ai] Refreshable Clone PDBs

If you prefer to read in Spanish Spanish version.

Today, we’re going to talk about how to make a copy a PDB and keep the copy up-to-date.

This kind of migration migth be local (same CDB) or remotely (another CDB).

Let’s start with the type of remotely.

Another important point , let’s use the external store in order to create PDBs (we need to set up SEPS). In the next article, we’ll set up SEPS.

In this lab, we will use the following:

• Source: 26ai on VM DB System in Oracle Cloud (test_ORI).

• Target: 26ai on VM DB System in Oracle Cloud (test_TARGET).

And the model is as follows:

• Step 1:: Create the PDB REFRESH_PDB on TEST_ORI:

SQL> CREATE PLUGGABLE DATABASE REFRESH_PDB ADMIN USER admin IDENTIFIED BY admin  keystore identified by external store; 
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE REFRESH_PDB OPEN;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE REFRESH_PDB SAVE STATE;
Pluggable database altered.
SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 REFRESH_PDB                    READ WRITE NO
SQL> ! hostname
source

• Step 2:: Let’s check that there aren’t other PDB, only PDB$SEED on TEST_TARGET.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL> ! hostname
target

• Step 3: Let’s create a common user, not a local user. The user must have the same name and password.

SQL> CREATE USER C##MANAGER IDENTIFIED BY "XXXXXX";
User created.
SQL> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO C##MANAGER CONTAINER=ALL;
Grant succeeded.
SQL> GRANT CREATE PLUGGABLE DATABASE TO C##MANAGER CONTAINER=ALL;
Grant succeeded.
SQL> GRANT SYSOPER TO C##MANAGER CONTAINER=ALL;
Grant succeeded.

• Step 4: Let’s create a DB Links. Before doing so, we need to modify both tnsnames files .

Source:

SQL> ! hostname
source

SQL> ! tail -8  $ORACLE_HOME/network/admin/tnsnames.ora
TARGET =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXXXXXXXXXXXXX)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XXXXXXXXXXXXXXXXXXXX)
    )
  )

Target:

SQL> ! hostname
target
SQL> ! tail -8  $ORACLE_HOME/network/admin/tnsnames.ora
SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXXXXXXXXXXXXX)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XXXXXXXXXXXXXXXXXXXX)
    )
  )

At this point, let’s create the DB Link on both.

SQL> CREATE DATABASE LINK DB_Source CONNECT TO C##MANAGER IDENTIFIED BY "XXXXXXXXX" USING 'SOURCE';
Database link created.
SQL> Select * From Dual@DB_Source;
D
-
X

Moreover, we need to verify that the DB Link is working.

Lastly, let’s create a table with some data since our pdb is empty.

SQL> alter session set container=REFRESH_PDB;
Session altered.
SQL> CREATE TABLE C##MANAGER.COPY_NUMERICOS AS 
     SELECT ROWNUM NUM 
       FROM DUAL 
    CONNECT BY ROWNUM <= 1000000;

Table created.

• Step 5: Let’s clone our PDB using the external store :

SQL> CREATE PLUGGABLE DATABASE REFRESH_PDB FROM REFRESH_PDB@DB_Source REFRESH MODE MANUAL keystore identified by external store;

Pluggable database created.

In this case, our PDB is the kind of REFRESH MODE MANUAL.

When we defined our PDB with REFRESH MODE MANUAL, it means the information isn’t automatically, but if we define it as REFRESH MODE EVERY <min>, the PDB will refresh automatically at the specified interval.

Currently, the PDB state is REFRESHING.

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='REFRESH_PDB';

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         3 REFRESH_PDB          REFRESHING           MANUAL

Let’s open the PDB in read-only mode and verify that the table with the data exists.

SQL> ALTER SESSION SET CONTAINER=REFRESH_PDB;
Session altered.
SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;
Pluggable database altered.
SQL> SELECT COUNT(*) FROM C##MANAGER.COPY_NUMERICOS;

  COUNT(*)
----------
   1000000

Next, we insert information into the table and refresh the PDB.

 SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE REFRESH;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Pluggable database altered.

SQL>  SELECT COUNT(*) FROM C##MANAGER.COPY_NUMERICOS;

  COUNT(*)
----------
   2000000

Great!, Both PDBs are synchronized.

At this point, we can either finished or switch the roles.

• Finished: Our PDB will change from a refreshable clone to a regular PDB.

SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
Pluggable database altered.
SQL>  alter pluggable database refresh mode none;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval 
     from dba_pdbs where pdb_name='REFRESH_PDB';

    CON_ID PDB_NAME             STATUS               REFRESH_MODE         REFRESH_INTERVAL
---------- -------------------- -------------------- -------------------- ----------------
         3 REFRESH_PDB          NORMAL               NONE

• Switch the roles: our source PDB will change from normal to refreshing, and target PDB will change from refreshing to normal mode:

Source:

SQL> col pdb_name format a15
SQL>  set linesize 1000
SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='REFRESH_PDB';

    CON_ID PDB_NAME        STATUS     REFRES REFRESH_INTERVAL
---------- --------------- ---------- ------ ----------------
         4 REFRESH_PDB     NORMAL     NONE

SQL> ALTER SESSION SET CONTAINER = REFRESH_PDB;                                                                 

Session altered.

SQL>  ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM REFRESH_PDB@DB_Target SWITCHOVER;

Pluggable database altered.

SQL> alter pluggable database open read only;

Pluggable database altered.

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='REFRESH_PDB';

    CON_ID PDB_NAME        STATUS     REFRES REFRESH_INTERVAL
---------- --------------- ---------- ------ ----------------
         4 REFRESH_PDB     REFRESHING MANUAL

Target:

SQL> r
  1* select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='REFRESH_PDB'

    CON_ID PDB_NAME        STATUS     REFRES REFRESH_INTERVAL
---------- --------------- ---------- ------ ----------------
         3 REFRESH_PDB     REFRESHING MANUAL

SQL> alter pluggable database refresh_pdb open read only;

Pluggable database altered.

SQL> select con_id, pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='REFRESH_PDB';

    CON_ID PDB_NAME        STATUS     REFRES REFRESH_INTERVAL
---------- --------------- ---------- ------ ----------------
         3 REFRESH_PDB     NORMAL     NONE

Looking forward to seeing you in the next article :)