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

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.


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


 184 Total Views,  2 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × 5 =