Create a PDB snapshot carousel
From Robs_Wiki
Revision as of 09:53, 18 October 2020 by Qadmin wiki (talk | contribs) (→Example 2 create an automatic PDB snapshot carousel)
Contents
Introduction
This feature is introduced in 18c and allows you to make instant snapshot copies of the Pluggable Database (PDB) and keep them in an external binnacle. A PDB snapshot is an archive file (.pdb) compromising of a a copy of PDB snapshot content. You can have a maximum of 8 snapshots per PDB. On reaching the limit, older copies will be overwritten as new instant copies are required.
- Snapshots differ from copy PDB snapshots, which are sparse copies.
- Snapshots will always depend on the CDB where they are hosted.
- You cannot create the snapshot from the root container. You must be connected to the PDB itself
- The database does not HAVE to running in archive log mode.
Example 1 create a manual snapshot and create a new PDB from that snapshot
SQL> select property_value from database_properties where property_name = 'MAX_PDB_SNAPSHOTS'; PROPERTY_VALUE -------------------------------------------------------------------------------- 8 SQL> alter pluggable database pdb1 snapshot pdb1_first_snap; Pluggable database altered. SQL> select con_name, snapshot_name, snapshot_scn, snapshot_time, full_snapshot_path from cdb_pdb_snapshots; CON_NAME SNAPSHOT_NAME SNAPSHOT_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH ---------- ------------------------------ ------------- --------------------- PDB1 PDB1_FIRST_SNAP 15656372 1602932322 /u01/app/oracle/oradata/snap_3499944333_15656372.pdb SQL> show parameter db_create_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u01/app/oracle/oradata SQL> alter session set container=cdb$root; Session altered. SQL> create pluggable database snap1_pdb1 from pdb1 using snapshot pdb1_first_snap; Pluggable database created.
Example 2 create an automatic PDB snapshot carousel
SQL> create pluggable database pdbsnap admin user pdbadmin identified by pdbadmin roles=(DBA) snapshot mode every 30 minutes; SQL> alter pluggable databsase pdbsnap open; SQL> select pdb_name, snapshot_mode from cdb_pdbs where snapshot_mode = 'AUTO'; PDB_NAME SNAPSH -------------------- ------ PDBSNAP AUTO --you can view the snapshots as follows SQL> select con_name, snapshot_name, snapshot_scn, snapshot_time, full_snapshot_path from cdb_pdb_snapshots;
The PDB snapshots are implemented as dbms_scheduler jobs:
SQL> select job_name, state, to_char(last_start_date,'DD-MON-YYYY HH24:MI:SS') last_start_date, to_char(next_run_date,'DD-MON-YYYY HH24:MI:SS') next_run_date, comments from DBA_SCHEDULER_JOBS where comments like '%SNAP%' order by state desc; JOB_NAME STATE LAST_START_DATE NEXT_RUN_DATE COMMENTS --------------------------- --------------- ----------------------------- ----------------------- PDBSNAP_1114628458_SNAPSHOT SCHEDULED 17-OCT-2020 16:29:52 17-OCT-2020 16:59:52 PDBSNAP snapshot