19c Refreshable Clone - Alternate to Data Guard and High Availability Solution for Oracle Standard Edition
|
Source CDB (hostname) |
Target CDB (hostname) |
Source PDB |
Target PDB |
|
SRCCDB |
TRGCDB |
PROD |
PRODREF |
|
sourceserver |
targetserver |
|
|
Oracle Database 19c Standard Edition 2 Release
19.0.0.0.0 - Production
Version 19.10.0.0.0
Steps to create Refreshable PDB
1)
Create new CDB (Same OS, Same Patch
set level) on target host where you wanted to create refreshable PDB or you can
use any existing CDB database on any other server.
2)
Add tns entry in each other’s
tnsnames.ora
3) Creation of a refreshable clone PDB requires a database link in the source and target CDB root container, create db link in both sides.
4) Create a user in the source CDB SRCCDB (host-sourceserver) for database link purpose
sqlplus as sysdba
SQL> create user c##clone_user identified by <pass> temporary tablespace temp container=ALL;
User created.
SQL> grant create session, create pluggable database,
sysoper to c##clone_user container=ALL;
Grant succeeded.
5)
In the target server cdb – TRGCDB
(Host- targetserver) create database link pointing to source CDB using
c##clone_user created above in the source CDB as above
First add below entry in tnsnames.ora in target server
-->Refreshable PDB - CDB tns entry in target node
SRCCDB_ sourceserver =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = sourceserver.vij.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME = SRCCDB.vij.com)
)
)
àcreate database link , connect as sysdba to cdb
SQL> create database link clonesource connect to c##clone_user identified by <pass> using 'SRCCDB_sourceserver';
Database link
created.
SQL> select * from dual@clonesource;
D
-
X
6)
Create a refreshable clone on
target , connect to CDB as sysdba
--target - create below directory on target
/u01/oradata/PRODREF/datafiles
--Create Refreshable DB took 5 Min - DB size 8 GB
-bash-4.2$
sqlplus "/as sysdba"
SQL*Plus:
Release 19.0.0.0.0 - Production on Tue Sep 23 11:04:05 2022
Version
19.10.0.0.0
Copyright (c)
1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database
19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version
19.10.0.0.0
SQL> create pluggable database PRODREF from PROD@clonesource
refresh mode manual FILE_NAME_CONVERT=('/u01/oradata/PROD/datafiles/','/u01/oradata/PRODREF/datafiles/');
Pluggable
database created.
SQL> show
pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3
PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL> select
PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
REFRES
REFRESH_INTERVAL LAST_REFRESH_SCN
------
---------------- ----------------
PRODREF
MANUAL 142927690
PDB$SEED
NONE
PRODSTG
NONE
7)
We can open refreshable clone READ
ONLY to query from the tables to verify below as an example
--------on source
-bash-4.2$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23
12:30:18 2022
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release
19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ
ONLY NO
3 PROD READ WRITE NO
SQL> alter session set container=PROD;
Session altered.
SQL> show user
USER is "SYS"
SQL> desc test
ERROR:
ORA-04043: object test does not exist
SQL> create table test(id number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
-------------------------on target check the table values
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ ONLY
NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database PRODREF refresh;
Pluggable database altered.
SQL>
-----------------at the same time alert log in the
target
2022-09-23T11:29:39.739979-04:00
alter pluggable database PRODREF refresh
2022-09-23T11:29:40.040005-04:00
Applying media recovery for pdb-4099 from SCN 142927690
to SCN 142933068
Remote log information: count-1
thr-1,seq-3315,logfile-/u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc,los-142884167,nxs-18446744073709551615,maxblks-78629
PRODREF(3):Media Recovery Start
2022-09-23T11:29:40.040482-04:00
PRODREF(3):Serial Media Recovery started
PRODREF(3):max_pdb is 5
2022-09-23T11:29:40.067924-04:00
PRODREF(3):Media Recovery Log /u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc
2022-09-23T11:29:40.335010-04:00
PRODREF(3):Incomplete Recovery applied until change
142933068 time 08/23/2022 11:38:04
2022-09-23T11:29:40.339377-04:00
PRODREF(3):Media Recovery Complete (TRGCDB)
Completed: alter pluggable database PRODREF refresh
-----------------------------target
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ
ONLY NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL>
Let’s now open PRODREF in Read Only mode to verify the
refresh
SQL> alter pluggable database PRODREF open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ ONLY NO
3 PRODREF READ ONLY NO
4 PRODSTG READ WRITE NO
SQL>
SQL> alter session set container=PRODREF;
Session altered.
SQL> select * from test;
ID
----------
1
SQL>
SQL> alter pluggable database PRODREF close
immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2 PDB$SEED READ ONLY NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
8)
Set the refresh mode to refresh
at every 4 minutes
SQL> alter pluggable database PRODREF refresh mode
every 4 minutes;
Pluggable database altered.
SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PRODREF';
PDB_NAME
--------------------------------------------------------------------------------
REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
------ ---------------- ----------------
PRODREF
AUTO
4 142935544
SQL>
SQL> select name from v$active_services;
NAME
----------------------------------------------------------------
TRGCDB.vij.com
PRODSTG
TRGCDBXDB
PRODREF
SYS$BACKGROUND
SYS$USERS
9)
We can follow above step no 7
to open it in Read only and query the data, once done need to close it again for
refresh to start automatically every 4 minutes
10)
Note that the automatic refresh
will success only if the PDB clone is mounted.
11)
Note also that a manual refresh
can be done even if the auto refresh is configured.
12) We can open the refreshable PDB in Read Write as below steps (Not performed in testing phase) , but once opened in Read Write can not be put back to refreshable mode and need to rebuild the refreshable clone. Used dummy PDB name - PDBRW as an example to show the commands to avoid accidental open of PRODREF.
To open the
database in a read write mode, we have to set the refresh mode to none, below steps
taken from the doc, not tested in our environment, change the PDB name
accordingly.
SQL> alter
pluggable database PDBRW refresh mode
none;
alter pluggable
database PDBRW refresh mode none
*
ERROR at line 1:
ORA-65025:
Pluggable database PDBRW is not closed on all instances.
SQL> alter pluggable database PDBRW close immediate;
Pluggable database altered.
SQL> alter pluggable database PDBRW refresh mode none;
Pluggable database altered.
SQL> col pdb_name for a15
SQL> select
PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where
PDB_NAME='PDBRW';
PDB_NAME REFRES REFRESH_INTERVAL
LAST_REFRESH_SCN
---------------
------ ---------------- ----------------
PDBRW NONE 39272683
SQL> alter
pluggable database PDBRW open read write;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBRW READ WRITE NO
13)
One other usage of refreshable
PDB is that the clone can be used as a golden master for snapshots at PDB
level. And these snapshots can be used for cloning environments for developers.
14)
You can create another PDB from
the Refreshed PDB and open it Read Write , thus your refreshable PDB would be intact and would be in refreshable
mode only.
E.g – On target server where you have created PRODREF refreshable PDB. Create Read Write PDB.
Sqlplus “/as sysdba”
Create pluggable database PRODRW from PRODREF;
alter pluggable database PRODRW open read write;
If there is Disaster at the source – primary server this can be served as Standby database for High Availability.
-------------------------------------------------------
