A new feature with Oracle 18c is the PDB snapshot carousel. As indicated by its name a PDB snapshot is a copy of a PDB at a specific point in time. You have the possibility to create up to eight snapshots, when you reach the maximum number of snapshots, the last snapshot is over written. The snapshot carousel is obviously the name of all your PDB snapshots.<\/p>\n
We have the possibility to create automatic snapshots using the \u201csnapshot mode every\u201d clause when you create or alter a PDB. For example you can change the snapshot mode from a PDB to every\u00a0 3 hours:<\/p>\n
SQL> alter session set container=pdb;\n\nSession altered.\n\nSQL> select snapshot_mode,snapshot_interval\/60 from dba_pdbs;\n\nSNAPSH SNAPSHOT_INTERVAL\/60\n------ --------------------\nMANUAL\n\nSQL> alter pluggable database snapshot mode every 3 hours;\n\nPluggable database altered.\n\nSQL> select snapshot_mode,snapshot_interval\/60 from dba_pdbs;\n\nSNAPSH SNAPSHOT_INTERVAL\/60\n------ --------------------\nAUTO\t\t\t 3\n\n<\/pre>\nTo return to manual mode, just type:<\/p>\n
SQL> alter pluggable database snapshot mode manual;\n\nPluggable database altered.\n\n<\/pre>\nWe can create PDB snapshots manually, you can use a specific name or not:<\/p>\n
SQL> alter pluggable database snapshot pdb_snap;\n\nPluggable database altered.\n\nSQL> alter pluggable database snapshot;\n\nPluggable database altered.\n\n<\/pre>\nWe can query the dba_pdb_snapshots view to display the PDB snapshots location:<\/p>\n
SQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, \nSNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH \nFROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;\n\nCON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN\n\nFULL_SNAPSHOT_PATH\n\n3 PDB\t PDB_SNAP 1155557\n\/home\/oracle\/oradata\/DB18\/pdb\/snap_2263384607_1155557.pdb\n\n3 PDB\t SNAP_2263384607_987432172 1155823\n\/home\/oracle\/oradata\/DB18\/pdb\/snap_2263384607_1155823.pdb\n\n<\/pre>\nIf you want to drop a snapshot, you have two methods:<\/p>\n
You delete the snapshot with the following alter pluggable statement:<\/p>\n
SQL> alter pluggable database drop snapshot SNAP_2263384607_987432172;\n\nPluggable database altered.\n\n<\/pre>\nOtherwise you set the MAX_PDB_SNAPSHOTS property to zero in the PDB:<\/p>\n
You can query the CDB_PROPERTIES and CDB_PDBS to display the parameter value:<\/p>\n
SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME,\n \tPROPERTY_VALUE AS value, DESCRIPTION\n \tFROM CDB_PROPERTIES r, CDB_PDBS p\n \tWHERE r.CON_ID = p.CON_ID\n \tAND PROPERTY_NAME LIKE 'MAX_PDB%'\n \tAND description like 'maximum%'\n \tORDER BY PROPERTY_NAME\n\nCON_ID\tPDB_NAME\tPROPERTY_NAME\tVALUE\t DESCRIPTION\n 3\t\t PDB MAX_PDB_SNAPSHOTS 8 maximum number of snapshots for a given PDB\n\n<\/pre>\nAnd if you set it to zero all your PDB snapshots will be dropped:<\/p>\n
SQL> alter session set container=pdb;\n\nSession altered.\n\nSQL> alter pluggable database set max_pdb_snapshots = 0;\n\nPluggable database altered.\n\nSQL> SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, \nSNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH \nFROM DBA_PDB_SNAPSHOTS\nORDER BY SNAP_SCN;\n\nno rows selected\n\n<\/pre>\nBut the main interest of the snapshot PDBS is to create new PDBS from a productive environment based on a point in time of the production PDB.<\/p>\n
So we create a PDB snapshot named PDB_SNAP:<\/p>\n
SQL> alter pluggable database snapshot pdb_snap;\n\nPluggable database altered.<\/pre>\nAnd now we create a PDB from the PDB_SNAP snapshot:<\/p>\n
SQL> create pluggable database PDB2 from PDB using snapshot PDB_SNAP create_file_dest='\/home\/oracle\/oradata\/DB18\/pdb2';\n\nPluggable database created.\n\nSQL> show pdbs\n\n CON_ID CON_NAME\t\t\t OPEN MODE RESTRICTED\n---------- ------------------------------ ---------- ----------\n\t 2 PDB$SEED\t\t\t READ ONLY NO\n\t 3 PDB\t\t READ WRITE NO\n\t 4 APPPSI\t\t\t READ WRITE NO\n\t 5 PDB2 \t\t\t READ WRITE NO\n\n<\/pre>\nWe have also the possibility to change the snapshot mode:<\/p>\n
SQL> alter session set container=pdb;\n\nSession altered.\n\nSQL> SELECT SNAPSHOT_MODE \"S_MODE\", SNAPSHOT_INTERVAL\/60 \"SNAP_INT_HRS\" \n FROM DBA_PDBS;\n\nS_MODE SNAP_INT_HRS\n------ ------------\nMANUAL\n\n\nSQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 1 HOURS;\n\nPluggable database altered.\n\nSQL> SELECT SNAPSHOT_MODE \"S_MODE\", SNAPSHOT_INTERVAL\/60 \"SNAP_INT_HRS\" \n FROM DBA_PDBS;\n\nS_MODE SNAP_INT_HRS\n------ ------------\nAUTO\t\t 1<\/pre>\nWe have the possibility to create a PDB that creates snapshots every 15 minutes :<\/p>\n
SQL> create pluggable database pdb_new from pdb\n 2 file_name_convert=('pdb','pdb_new')\n 3 snapshot mode every 15 minutes;\n\nPluggable database created.\n\n<\/pre>\nThere is a pre requisite for configuring automatic PDB snapshots: the CDB must be in local undo mode.<\/p>\n
Finally the snapshots are correctly created in my environment every 15 minutes:<\/p>\n
oracle@localhost:\/home\/oracle\/oradata\/DB183\/pdb\/ [DB183] ls -lrt snap*\n-rw-r--r--. 1 oracle dba 65690276 Oct 1 15:04 snap_3893567541_798493.pdb\n-rw-r--r--. 1 oracle dba 65740202 Oct 1 15:19 snap_3893567541_801189.pdb\n-rw-r--r--. 1 oracle dba 65823279 Oct 1 15:34 snap_3893567541_803706.pdb<\/pre>\nAnd to verify if it is correct , I had created in my pdb_new environment a location table in my psi schema with two records at 15H20:<\/p>\n
SQL> create table psi.location (name varchar2(10));\n\nTable created.\n\nSQL> insert into psi.location values ('London');\n\n1 row created.\n\nSQL> insert into psi.location values('Paris');\n\n1 row created.\n\nSQL> commit;\n\n<\/pre>\nAnd we create a new pdb from the snap to verify if the data are correct:<\/p>\n
SQL> create pluggable database pdb_psi from pdb_new \n using snapshot SNAP_45745043_988386045 \n create_file_dest='\/home\/oracle\/oradata\/DB183\/pdb_psi';\n\nPluggable database created.\n\n<\/pre>\nWe open pdb_psi and we check:<\/p>\n
SQL> alter session set container=pdb_psi;\n\nSession altered.\n\nSQL> select * from psi.location;\n\nNAME\n----------\nLondon\nParis\n\n<\/pre>\nThis feature might be very useful for testing purposes, imagine you have a production PDB, you only have to create a refreshable clone named PDB_MASTER and configure it to create daily snapshots. If you need a PDB for testing you only have to create a clone from any snapshot.<\/p>\n
Conclusion<\/p>\n
All those tests have been realized on an Linux x86-64 server, with Oracle 18.3 Enterprise Edition. My DB183 database has been initialized with the \u201c_exadata_feature_on\u201d \u00a0hidden parameter to avoid the “ORA-12754 Feature PDB Snapshot Carousel is disabled due to missing capability” error message.<\/p>\n
If you have a look at the Database Licensing User Manual:<\/p>\n
Feature \/ Option \/ Pack<\/strong><\/td>\n| SE2<\/strong><\/td>\n | EE<\/strong><\/td>\n | EE-ES<\/strong><\/td>\n | DBCS SE<\/strong><\/td>\n | DBCS EE<\/strong><\/td>\n | DBCS EE-HP<\/strong><\/td>\n | DBCS EE-EP<\/strong><\/td>\n | ExaCS<\/strong><\/td>\n | Notes<\/strong><\/td>\n<\/tr>\n<\/thead>\n\n | PDB Snapshot Carousel<\/td>\n | N<\/td>\n | N<\/td>\n | Y<\/td>\n | N<\/td>\n | Y<\/td>\n | Y<\/td>\n | Y<\/td>\n | Y<\/td>\n | <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n | <\/p>\n You will see that PDB Carousel (and a lot of interesting new features in Oracle 18.3) are only available for Engineered System or in Cloud and not for Enterprise Edition for third party hardware. I really hope Oracle will change this behavior in the future releases.<\/p>\n","protected":false},"excerpt":{"rendered":" A new feature with Oracle 18c is the PDB snapshot carousel. As indicated by its name a PDB snapshot is a copy of a PDB at a specific point in time. You have the possibility to create up to eight snapshots, when you reach the maximum number of snapshots, the last snapshot is over written. […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1184,66,237],"type_dbi":[],"class_list":["post-11746","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle-18c","tag-pdb","tag-snapshot"],"acf":[],"yoast_head":"\n |