How to Find all Physicals Files in Multinanent Architecture CDB and PDB DATABASE in Oracle

Below is the command, how to find all physical files in Oracle in one short (control files, datafiles & redolog files) in the multitenant architecture CDB and PDB in the database.

Option 1: Connect to container database CDB$ROOT and fetch all physical files in one short which includes CDB (Root Container) & all PDBs (Pluggable DBs). Click here to get a sample output of it.

SHOW CON_NAME

COL NAME FOR A60
SET PAGES 999

SELECT NAME FROM V$CONTROLFILE
UNION
SELECT FILE_NAME FROM CDB_DATA_FILES
UNION
SELECT FILE_NAME FROM  CDB_TEMP_FILES
UNION
SELECT MEMBER FROM V$LOGFILE;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Option 2: Connect to any specific pluggable database (PDB) or root container (CBD) and fetch all physical files. Click here to get a sample output of it.

SHOW PDBS

ALTER SESSION SET CONTAINER = DBSGURU_PDB;

SHOW CON_NAME

COL NAME FOR A60
SET PAGES 999

SELECT NAME FROM V$CONTROLFILE
UNION
SELECT FILE_NAME FROM DBA_DATA_FILES
UNION
SELECT FILE_NAME FROM DBA_TEMP_FILES
UNION
SELECT MEMBER FROM V$LOGFILE;
prompt $$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

NOTE: Above option will fetch physical files from connected container DB only.

Question: Why do we need all physical files together?

Answer: Whenever we do decommission / clone / migration / restore etc.

We always encourage the technical person to visit section SCRIPTS to get more daily usage SQL commands.

https://dbsguru.com/how-to-find-all-physicals-files-in-cdb-pdb-in-oracle/

Hope so you like this Script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

CDB, DATABASE, MULTITENANT, ORACLE, PDB, SCRIPTS