Shulou(Shulou.com)05/31 Report--

This article shares with you the content of the sample analysis of PDB Snapshot rotation, a new feature of Oracle 18c. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

A new feature introduced by Oracle 18c is PDB snapshot rotation, which can create up to eight pdb snapshots, and a set of these eight snapshots is called snapshot rotation. When the eighth snapshot limit is reached, the new snapshot overwrites the oldest snapshot, just like redo log. The main function of PDB snapshot is to flash back to the snapshot point in time when there is a problem, and another function is to query historical data based on snapshots.

SQL > SELECT r.CON_ID, 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 LIKE 'MAX_PDB%' AND description like' maximum%' ORDER BY PROPERTY_NAME CON_ID PDB_NAME PROPERTY_NAME VALUE DESCRIPTION -3 ORCLPDB1 MAX_PDB_SNAPSHOTS maximum number of snapshots for a given PDB

By default, the snapshot is created manually, and we can change it to create it automatically.

SQL > select snapshot_mode,snapshot_interval/60 from dba_pdbs;SNAPSH SNAPSHOT_INTERVAL/60--MANUALSQL > alter pluggable database snapshot mode every 4 hours;Pluggable database altered.SQL > select snapshot_mode,snapshot_interval/60 from dba_pdbs;SNAPSH SNAPSHOT_INTERVAL/60--AUTO 4

To return to manual mode, type mode manual.

SQL > alter pluggable database snapshot mode manual;Pluggable database altered.SQL > alter pluggable database snapshot;Pluggable database altered.

Create a snapshot manually, you can specify your own name, or you can use a name automatically generated by the system. By looking at the DBA_PDB_SNAPSHOTS view, you can find the path where the snapshot is stored and the SCN number that created the snapshot.

SQL > SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH -3 ORCLPDB1 SNAP_2953839490_989014667 2821702 / opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3 ORCLPDB1 PDB_SNAP 2823303 / opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb 3 ORCLPDB1 SNAP_2953839490_989015970 2823679 / opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_ 2823679.pdb [oracle @ oracle-18c-vagrant ORCLPDB1] $ls-lshtotal 1.3G4.0K drwxr-x---. 8 oracle oinstall 4.0K Oct 8 22:39 ORCLCDB171M-rw-r--r--. 1 oracle oinstall 171m Oct 8 22:18 snap_2953839490_2821702.pdb171M-rw-r--r--. 1 oracle oinstall 171m Oct 8 22:38 snap_2953839490_2823303.pdb171M-rw-r--r--. 1 oracle oinstall 171m Oct 8 22:40 snap_2953839490_2823679.pdb371M-rw-r-. 1 oracle oinstall 371m Oct 8 22:39 sysaux01.dbf271M-rw-r-. 1 oracle oinstall 271m Oct 8 22:39 system01.dbf 56K-rw-r-. 1 oracle oinstall 63m Oct 1 23:21 temp01.dbf101M-rw-r-. 1 oracle oinstall 101m Oct 8 22:39 undotbs01.dbf5.1M-rw-r-. 1 oracle oinstall 5.1M Oct 8 22:39 users01.dbf

If you want to delete a snapshot, there are two ways, the first is to delete using alter pluggable database, and the second is to modify the max_pdb_snapshots parameter. When the max_pdb_snapshots parameter is set to 0, all snapshots will be deleted.

SQL > alter pluggable database drop snapshot SNAP_2953839490_989015970;Pluggable database altered.SQL > SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH-- -3 ORCLPDB1 SNAP_2953839490_989014667 2821702 1539008272 / opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3 ORCLPDB1 PDB_SNAP 2823303 1539009503 / opt/oracle/oradata / ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdbSQL > alter pluggable database set max_pdb_snapshots=0 Pluggable database altered.SQL > SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;no rows selected

After demonstrating the above functionality, there is another feature that we can create a pdb from the snapshot.

SQL > alter pluggable database set max_pdb_snapshots=8;Pluggable database alteredSQL > alter pluggable database snapshot pdb_snap;Pluggable database altered.SQL > SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH-- -3 ORCLPDB1 PDB_SNAP 2825283 1539010328 / opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2825283.pdbSQL >! [oracle@oracle-18c-vagrant ~] $mkdir-p / opt/oracle/ Oradata/ORCLCDB/ORCLPDB2SQL > create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2' Create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'*ERROR at line 1:ORA-65040: operation not allowed from within a pluggable database SQL > alter session set container=CDB$ROOT;Session altered.SQL > create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'; Pluggable database created.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO 5 ORCLPDB2 MOUNTEDSQL > alter pluggable database ORCLPDB2 open; Pluggable database altered.

In this way, we create a new PDB from the snapshot, and assuming that there is a data overwrite in the current PDB, we can use the PDB of this historical snapshot to recover the data.

Of course, another useful feature is that during the process of creating pdb, we set it to 15 minutes to produce a snapshot. Of course, there is a prerequisite for configuring this feature: CDB must be in local undo mode.

SQL > create pluggable database ORCLPDB3 from ORCLPDB1 file_name_convert= ('ORCLPDB1','ORCLPDB3') snapshot mode every 5 minutes;Pluggable database created.

You can see that a snapshot is automatically created every 5 minutes

[oracle@oracle-18c-vagrant ORCLPDB3] $ls-lrt snap*-rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb-rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb-rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb-rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb-rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb-rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb-rw-r--r--. 1 oracle oinstall 115724288 Oct 8 23:43 snap_2091710291_2837222.pdb

We can do an experiment and test it. Create a table insertion point data before taking a snapshot, where the last snapshot was 23:43.

SQL > alter session set container=ORCLPDB3;Session altered.SQL > create table A1 as select * from dba_objects;Table created.SQL > select count (1) from A1; COUNT (1)-72897 [oracle@oracle-18c-vagrant ORCLPDB3] $ls-lrt snap*-rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb-rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb-rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb-rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb-rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb-rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb-rw-r--r--. 1 oracle oinstall 179353508 Oct 8 23:43 snap_2091710291_2837222.pdb-rw-r--r--. 1 oracle oinstall 5767168 Oct 8 23:48 snap_2091710291_2839217.pdbSQL > SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH-- -7 ORCLPDB3 SNAP_2091710291_989017984 2830531 1539011592 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2830531.pdb 7 ORCLPDB3 SNAP_2091710291_989018284 2832545 1539011887 / opt/oracle/oradata/ORCLCDB/ ORCLPDB3/snap_2091710291_2832545.pdb 7 ORCLPDB3 SNAP_2091710291_989018584 2833104 1539012188 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833104.pdb 7 ORCLPDB3 SNAP_2091710291_989018884 2833687 1539012489 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833687.pdb 7 ORCLPDB3 SNAP_2091710291_989019184 2834965 1539012787 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2834965.pdb 7 ORCLPDB3 SNAP_2091710291_989019484 2836695 1539013087 / Opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2836695.pdb 7 ORCLPDB3 SNAP_2091710291_989019784 2837222 1539013386 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2837222.pdb 7 ORCLPDB3 SNAP_2091710291_989020084 2839217 1539013686 / opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2839217.pdb

A new snapshot is taken at 23:48, and we use the new snapshot to make a clone. After logging in to the ORCLPDB4 database, we will find that there is data.

SQL > alter session set container=CDB$ROOT;Session altered.SQL > create pluggable database ORCLPDB4 from ORCLPDB3 using snapshot SNAP_2091710291_989020084 create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB4';Pluggable database createdSQL > alter pluggable database ORCLPDB4 open;Pluggable database altered.SQL > alter session set container=ORCLPDB4;Session altered.SQL > select count (1) from A1; COUNT (1)-72897

This feature is very useful for testers. For example, Mobile Telecom now has a BCV environment dedicated to decimation and testing, and this BCV environment is a clone of the underlying synchronization. Using the 18c database, we can create a snapshot directly, and then use the snapshot to clone a new database to form a BCV environment for use by decimation and testing.

Thank you for reading! This is the end of this article on "the new feature of Oracle 18c-sample analysis of PDB snapshot rotation". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.