# [OCI-26ai] Refreshable Clone PDBs *If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/oci-26ai-refreshable-clone-pdbs?showSharer=true)*.* 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). ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766234418615/7af4855c-a9fd-42fd-8227-5b195d359ea8.png align="center") And the model is as follows: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766234319392/ee0c4aa8-31b2-48bd-8f0b-05c4e91b44c3.png align="center") * **Step 1:**: Create the PDB **REFRESH\_PDB** on *TEST\_ORI*: ```plaintext 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*. ```plaintext 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. ```plaintext 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: ```plaintext 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: ```plaintext 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. ```plaintext 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. ```plaintext 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* : ```plaintext 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**. ```plaintext 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. ```plaintext 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. ```plaintext 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**. ```plaintext 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: ```plaintext 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: ```plaintext 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 :)