9.7.9 Determining All Snapshots Associated with a Test Master

Use this query to discover all of the children associated with a test master.

Consider the following configuration for a test master with multiple children.

Description of the illustration snapshot-hierarchy-example.png

You can use a query of the associated SYSTEM data files for each database to list all the children within the same tree. The query selects just the SYSTEM data file for each database or PDB. The data file must exist for all clones and parents, and there should be only 1 data file for each. The START WITH clause provides a starting point of a file that is not a cloned file, which is the original test master parent.

Connect to the Oracle ASM instance and run this command as the SYSASM user.

SELECT clonefilename "Child", snapshotfilename "Parent"
FROM v$clonedfile
WHERE LOWER(snapshotfilename) LIKE '%system.%'  
START WITH snapshotfilename NOT IN (SELECT clonefilename FROM v$clonedfile)  
CONNECT BY LOWER(clonefilename) = PRIOR (snapshotfilename);

The results of this query for database-based snapshots would be similar to the following:

Child                                      
  Parent
-----------------------------------------------------------
  -----------------------------------------------------------------
+SPARSE/SNAP001/DATAFILE/SYSTEM.256.1011532891           
  +DATA/TESTMASTER/DATAFILE/system.270.1011530981 
+SPARSE/SNAP002/DATAFILE/SYSTEM.265.1011532969           
  +DATA/TESTMASTER/DATAFILE/system.270.1011530981
+SPARSE/SNAP1011/DATAFILE/SYSTEM.270.1011533005           
  +SPARSE/SNAP001/DATAFILE/system.256.1011532891
+SPARSE/SNAP1012/DATAFILE/SYSTEM.275.1011780925           
  +SPARSE/SNAP001/DATAFILE/system.256.1011532891
+SPARSE/SNAP2011/DATAFILE/SYSTEM.281.1011781103           
  +SPARSE/SNAP1011/DATAFILE/system.270.1011533005

If you created folders in Oracle ASM that contained the database name, as shown in the above result, then the database name in the CLONEFILENAME string is the snapshot, and the database name in the SNAPSHOTFILENAME string is the master for that snapshot.

The results of this query for PDB-based snapshots would be similar to the following:

CLONEFILENAME                                                           
  SNAPSHOTFILENAME
---------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------
+SPARSEC1/CDB001/8BDBC355D43721F5E053412E850AB5D1/DATAFILE/SYSTEM.256.1011532891  
  +DATAC1/CDB001/8BDBC355D42D21F5E053412E850AB5D1/DATAFILE/system.270.1011530981 
+SPARSEC1/CDB001/8BDBC355D43E21F5E053412E850AB5D1/DATAFILE/SYSTEM.265.1011532969  
  +DATAC1/CDB001/8BDBC355D42D21F5E053412E850AB5D1/DATAFILE/system.270.1011530981 
+SPARSEC1/CDB001/8BDBC355D44021F5E053412E850AB5D1/DATAFILE/SYSTEM.270.1011533005   
  +SPARSEC1/CDB001/8BDBC355D43721F5E053412E850AB5D1/DATAFILE/system.256.1011532891 
+SPARSEC1/CDB001/8BDBC355D44821F5E053412E850AB5D1/DATAFILE/SYSTEM.275.1011780925   
  +SPARSEC1/CDB001/8BDBC355D43721F5E053412E850AB5D1/DATAFILE/system.256.1011532891 
+SPARSEC1/CDB001/8BDBC355D44D21F5E053412E850AB5D1/DATAFILE/SYSTEM.281.1011781103   
  +SPARSEC1/CDB001/8BDBC355D44021F5E053412E850AB5D1/DATAFILE/system.270.1011533005 

In this case, the folder name in Oracle ASM is the GUID associated with the PDB. To determine the name of teach snapshot PDB and its master, you must do the following:

1. Log in to the CDB that has the name shown in the results, for example, CDB001.

2. Run a query against the CDB_PDBS view to translate the GUIDs into the PDB names, as shown below:

   SELECT pdb_name, guid FROM CDB_PDBS 
   WHERE guid IN ('8BDBC355D42D21F5E053412E850AB5D1','8BDBC355D43721F5E053412E850AB5D1'
   '8BDBC355D44821F5E053412E850AB5D1','8BDBC355D43E21F5E053412E850AB5D1', 
   '8BDBC355D44021F5E053412E850AB5D1','8BDBC355D44D21F5E053412E850AB5D1');
   
   PDB_NAME                GUID   
   ----------------------- -----------------------------------
   TESTMASTER              8BDBC355D42D21F5E053412E850AB5D1
   SNAP001                 8BDBC355D43721F5E053412E850AB5D1
   SNAP1012                8BDBC355D44821F5E053412E850AB5D1
   SNAP02                  8BDBC355D43E21F5E053412E850AB5D1
   SNAP1011                8BDBC355D44021F5E053412E850AB5D1
   SNAP2011                8BDBC355D44D21F5E053412E850AB5D1

Then use this information to determine the parent/child relationship among the PDBs in the original query results.