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.
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:
-
Log in to the CDB that has the name shown in the results, for example,
CDB001
. -
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.
Parent topic: Managing Exadata Snapshots