Loading....

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'));
Please follow and like us:

Last Update: June 22, 2020  

October 29, 2019 249 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , , , , , , , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?