SNAPSHOT CAROUSEL
A PDB Snapshot is a point-in-time copy of a Pluggable Database.
We can create snapshots either manually using the SNAPSHOT clause of CREATE or ALTER PLUGGABLE DATABASE command, or automatically using the EVERY interval clause. When a PDB is enabled for snapshots, we can create multiple snapshots or point-in-time copies of the PDB. This library of snapshots is called a PDB snapshot carousel.
Using the snapshot carousel we can quickly clone a new PDB based on any snapshot in the carousel as well as have the ability to perform point-in-time recovery to any snapshot in the carousel.
Note that the CDB must be in local undo mode.
The MAX_PDB_SNAPSHOTS database property sets the maximum number of snapshots for every PDB in a PDB snapshot carousel. The default maximum is 8. You cannot set the property to a number greater than 8. The current setting is visible in the CDB_PROPERTIES view.
COL CON_ID FORMAT 99999
COL PROPERTY_NAME FORMAT a17
COL PDB_NAME FORMAT a9
COL VALUE FORMAT a3
COL DESCRIPTION FORMAT a43
SQL> SELECT p.PDB_NAME, PROPERTY_NAME,
PROPERTY_VALUE AS value, DESCRIPTION
FROM CDB_PROPERTIES r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID
AND PROPERTY_NAME='MAX_PDB_SNAPSHOTS';
PDB_NAME PROPERTY_NAME VAL DESCRIPTION
--------- ----------------- --- -------------------------------------------
PDB1 MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB
Let us see an example of this feature.
In pluggable database PDB1, we create the MYOBJECTS table in the HR schema. Note the number of rows in the table.
SQL> create table hr.myobjects as select * from all_objects;
Table created.
SQL> select count(*) from hr.myobjects;
COUNT(*)
----------
71720
Create the Pluggable Database Snapshots with a refresh interval of 5 minutes
SQL> create pluggable database pdb1_snap from pdb1
file_name_convert=('/u03/app/oracle/oradata/PDB1/','/u03/app/oracle/oradata/PDB1_SNAP/')
snapshot mode every 5 minutes;
Pluggable database created.
SQL> alter pluggable database pdb1_snap open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
6 PDB1_SNAP READ WRITE NO
SQL> col pdb_name format a10
SQL> SELECT PDB_NAME,SNAPSHOT_MODE "S_MODE", SNAPSHOT_INTERVAL "SNAP_INT_MINS" FROM DBA_PDBS;
PDB_NAME S_MODE SNAP_INT_MINS
---------- ------ -------------
PDB1_SNAP AUTO 5
PDB$SEED MANUAL
PDB1 MANUAL
After about 5 minutes we will start seeing the Pluggable Database snapshots being created
SQL> COL CON_NAME FORMAT a10 COL SNAPSHOT_NAME FORMAT a20 COL SNAP_SCN FORMAT 9999999 COL FULL_SNAPSHOT_PATH FORMAT a45 SQL> SELECT CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN; no rows selected SQL> / CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH ---------- -------------------- -------- --------------------------------------------- PDB1_SNAP SNAP_2931840591_9975 3040973 /u03/app/oracle/oradata/PDB1_SNAP/snap_293184 28888 0591_3040973.pdb SQL> / CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH ---------- -------------------- -------- --------------------------------------------- PDB1_SNAP SNAP_2931840591_9975 3040973 /u03/app/oracle/oradata/PDB1_SNAP/snap_293184 28888 0591_3040973.pdb PDB1_SNAP SNAP_2931840591_9975 3043025 /u03/app/oracle/oradata/PDB1_SNAP/snap_293184 29188 0591_3043025.pdb PDB1_SNAP SNAP_2931840591_9975 3043501 /u03/app/oracle/oradata/PDB1_SNAP/snap_293184 29488 0591_3043501.pdb PDB1_SNAP SNAP_2931840591_9975 3043958 /u03/app/oracle/oradata/PDB1_SNAP/snap_293184 29788 0591_3043958.pdb
In PDB1 pluggable database we now truncate the HR.MYOBJECTS table.
SQL> alter session set container=pdb1; Session altered. SQL> truncate table hr.myobjects; Table truncated.
We now create a Pluggable Database using one of the Pluggable Database Snapshots taken before the HR.MYOBJECTS table was truncated.
Note the number of rows in the HR.MYOBJECTS table.
SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb1_snap_copy FROM PDB1_SNAP USING SNAPSHOT SNAP_2931840591_997528888
create_file_dest='/u03/app/oracle/oradata/PDB1_SNAP_COPY';
Pluggable database created.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb1_snap_copy open;
Pluggable database altered.
SQL> alter session set container=pdb1_snap_copy;
Session altered.
SQL> select count(*) from hr.myobjects;
COUNT(*)
----------
71720
Note the size of the Pluggable Database archive (.pdb) files
SQL> conn / as sysdba
Connected.
SQL> SELECT CON_NAME, SNAPSHOT_NAME,
SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH
FROM DBA_PDB_SNAPSHOTS
ORDER BY SNAP_SCN;
CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
---------- --------------------------- -------- ----------------------------------------------------------------------
PDB1_SNAP SNAP_2931840591_997535488 3057328 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3057328.pdb
PDB1_SNAP SNAP_2931840591_997535788 3057826 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3057826.pdb
PDB1_SNAP SNAP_2931840591_997536088 3058298 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3058298.pdb
PDB1_SNAP SNAP_2931840591_997536388 3059155 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3059155.pdb
PDB1_SNAP SNAP_2931840591_997536688 3059631 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3059631.pdb
PDB1_SNAP SNAP_2931840591_997536988 3060098 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3060098.pdb
PDB1_SNAP SNAP_2931840591_997537288 3060795 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3060795.pdb
PDB1_SNAP SNAP_2931840591_997537588 3062481 /u03/app/oracle/oradata/PDB1_SNAP/snap_2931840591_3062481.pdb
[oracle@host01 oradata]$ cd PDB1_SNAP
[oracle@host01 PDB1_SNAP]$ du -hs *
484K ORCLCDB
207M snap_2931840591_3057328.pdb
207M snap_2931840591_3057826.pdb
207M snap_2931840591_3058298.pdb
207M snap_2931840591_3059155.pdb
207M snap_2931840591_3059631.pdb
207M snap_2931840591_3060098.pdb
207M snap_2931840591_3060795.pdb
207M snap_2931840591_3062481.pdb
CDB Fleet
A CDB fleet is a collection of different CDBs that can be managed as one logical CDB.
A CDB fleet provides the database infrastructure for monitoring and centralized management of many CDBs. Designate the lead CDB in a CDB fleet by setting the LEAD_CDB database property to true and designate a fleet member by setting the LEAD_CDB_URI database property to a database link that points to the lead CDB.
PDB information from the various CDBs is synchronized with the lead CDB. All PDBs in the CDBs are now “visible” in the lead CDB, enabling you to access the PDBs in the fleet as a single, logical CDB from the lead CDB.
In this example we will configure Container database CDB1 to be the Fleet Lead and CDB2 to be a Fleet Member.
In CDB1, set the LEAD_CDB parameter and create the user which will be used for the database link
CDB1 SQL> ALTER DATABASE SET LEAD_CDB = TRUE; Database altered. SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LEAD_CDB'; PROPERTY_VALUE -------------------------------------------------------------------------------- TRUE SQL> CREATE USER c##link_user IDENTIFIED BY oracle; User created. SQL> GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO c##link_user CONTAINER=ALL; Grant succeeded. SQL> GRANT CREATE PLUGGABLE DATABASE TO c##link_user CONTAINER=ALL; Grant succeeded. SQL> GRANT SYSOPER TO c##link_user CONTAINER=ALL; Grant succeeded.
In CDB2 which is a Fleet Member, set the parameter LEAD_CDB_URI and create a database link pointing to CDB1
CDB2 SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LEAD_CDB_URI'; no rows selected SQL> create database link cdb1_link connect to c##link_user identified by oracle using 'cdb1'; Database link created. SQL> select * from dual@cdb1_link; D - X SQL> ALTER DATABASE SET LEAD_CDB_URI = 'dblink:cdb1_link'; Database altered. SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='LEAD_CDB_URI'; PROPERTY_VALUE -------------------------------------------------------------------------------- dblink:cdb1_link
Note the pluggable databases which are in CDB2.
CDB2 SQL> col name format a12 SQL> select name,con_id,guid,open_mode from v$pdbs; NAME CON_ID GUID OPEN_MODE ------------ ---------- -------------------------------- ---------- PDB$SEED 2 7D3517326B31295FE053650AA8C06B17 READ ONLY PDB2 3 7D35460749422F94E053650AA8C0FFFE READ WRITE
From the Fleet Lead CDB1 we are able to view the pluggable databases present in a Fleet Member Container database (CDB2).
CDB1
SQL> col name format a12
SQL> select name,con_id,guid,open_mode from v$pdbs;
NAME CON_ID GUID OPEN_MODE
------------ ---------- -------------------------------- ----------
PDB$SEED 2 799177CD308D7401E053640AA8C0F460 READ ONLY
PDB1 3 7C91893723A617B9E053640AA8C0C023 READ WRITE
PDB2 5 7D35460749422F94E053650AA8C0FFFE MOUNTED
SQL> select con_id,name,member_cdb from v$containers;
CON_ID NAME MEM
---------- ------------ ---
1 CDB$ROOT NO
2 PDB$SEED NO
3 PDB1 NO
4 CDB2 YES
5 PDB2 NO
However we cannot directly connect to a pluggable database of a Fleet Member even though we are connected to the Fleet Lead.
SQL> alter session set container=pdb2; ERROR: ORA-65283: pluggable database PDB2 does not belong to the current container database
Container Maps
Container maps enable the partitioning of data at the application PDB level when the data is not physically partitioned at the table level.
A container map is set via a database property (CONTAINER_MAP) that specifies a partitioned map table defined in an application root.
In this example we create an Application Container database APPCON1 and two Application Pluggable databases PDB_AMER and PDB_EURO.
SQL> create pluggable database appcon1
as application container
admin user appadmin identified by oracle
file_name_convert=('/u01/app/oracle/oradata/CDB2/pdbseed/','/u01/app/oracle/oradata/CDB2/appcon1/');
Pluggable database created.
SQL> SQL> alter pluggable database appcon1 open;
Pluggable database altered.
SQL> !mkdir -p /u03/app/oracle/oradata/CDB2/appcon1/pdb_amer
SQL> alter session set container=appcon1;
Session altered.
SQL> create pluggable database pdb_amer
admin user pdb_amer_adm identified by oracle
file_name_convert=('/u01/app/oracle/oradata/CDB2/pdbseed/','/u03/app/oracle/oradata/CDB2/appcon1/pdb_amer/'); 2 3
Pluggable database created.
SQL> create pluggable database pdb_euro
admin user pdb_euro_adm identified by oracle
file_name_convert=('/u01/app/oracle/oradata/CDB2/pdbseed/','/u03/app/oracle/oradata/CDB2/appcon1/pdb_euro/'); 2 3
Pluggable database created.
SQL> create user app_own identified by oracle container=all;
User created.
SQL> grant dba to app_own container=all;
Grant succeeded.
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APPCON1 READ WRITE NO
4 PDB_AMER MOUNTED
5 PDB_EURO MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
Create a LIST Partitioned table and set the parameter CONTAINER_MAP.
SQL> CREATE TABLE app_own.conmap (country VARCHAR2(30) NOT NULL)
PARTITION BY LIST (country) (
PARTITION pdb_amer VALUES ('US','MEXICO','CANADA'),
PARTITION pdb_euro VALUES ('UK','FRANCE','GERMANY')
);
Table created.
SQL> alter pluggable database set container_map='app_own.conmap';
Pluggable database altered.
Create the application REG_SALES_APP including the table SALES_REVENUE_BY_REGION.
Note the ENABLE CONTAINER_MAP clause.
SQL> alter pluggable database application reg_sales_app begin install '1.0'; Pluggable database altered. SQL> create table app_own.sales_revenue_by_region sharing=metadata ( city_name varchar2(20), country varchar2(30), sales number); Table created. SQL> alter table app_own.sales_revenue_by_region enable container_map; Table altered. SQL> alter table app_own.sales_revenue_by_region enable containers_default; Table altered. SQL> alter pluggable database application reg_sales_app end install; Pluggable database altered. SQL> alter session set container=pdb_amer; Session altered. SQL> create user app_own identified by oracle; User created. SQL> grant dba to app_own; Grant succeeded. SQL> ALTER PLUGGABLE DATABASE APPLICATION reg_sales_app sync; Pluggable database altered. SQL> alter session set container=pdb_euro; Session altered. SQL> create user app_own identified by oracle; User created. SQL> grant dba to app_own; Grant succeeded. SQL> ALTER PLUGGABLE DATABASE APPLICATION reg_sales_app sync; Pluggable database altered.
Connect to PDB_AMER and insert data into the table SALES_REVENUE_BY_REGION.
SQL> alter session set container=pdb_amer ;
Session altered.
SQL> desc app_own.sales_revenue_by_region
Name Null? Type
----------------------------------------- -------- ----------------------------
CITY_NAME VARCHAR2(20)
COUNTRY VARCHAR2(30)
SALES NUMBER
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('New York','US',9000666);
1 row created.
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('Toronto','CANADA',888900);
1 row created.
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('Dallas','US',654321);
1 row created.
SQL> commit;
Commit complete.
Connect to PDB_EURO and insert data into the table SALES_REVENUE_BY_REGION.
SQL> alter session set container=pdb_euro;
Session altered.
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('London','UK',887777);
1 row created.
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('Munich','GERMANY',900000);
1 row created.
SQL> insert into app_own.sales_revenue_by_region
2 values
3 ('Paris','FRANCE',850000);
1 row created.
SQL> commit;
Commit complete.
Now connect to the Application Container database APPCON1 and run queries against the SALES_REVENUE_BY_REGION table. Based on the value being provided for COUNTRY, the appropriate Pluggable Database(s) is accessed using the Container Map table which had been earlier created.
SQL> alter session set container=appcon1;
Session altered.
SQL> select sales from app_own.sales_revenue_by_region
where country='US';
SALES
----------
9000666
654321
SQL> select city_name,sales from app_own.sales_revenue_by_region
where country='US';
CITY_NAME SALES
-------------------- ----------
New York 9000666
Dallas 654321
SQL> select city_name,sales from app_own.sales_revenue_by_region
where country='UK';
CITY_NAME SALES
-------------------- ----------
London 887777
SQL>
SQL> select sum(sales) from app_own.sales_revenue_by_region
where country in ('US','UK');
SUM(SALES)
----------
10542764
We can also dynamically update the Container Mapping table when a new Pluggable Database is created adding values which were not originally present in the Container Map table.
create pluggable database pdb_asia
admin user pdb_asia_adm identified by oracle
file_name_convert=('/u01/app/oracle/oradata/CDB2/pdbseed/','/u03/app/oracle/oradata/CDB2/appcon1/pdb_asia/')
container_map update (add partition pdb_asia values ('INDIA','CHINA'));
