Querying V$ Views to Obtain Backup Information

Querying V$ Views to Obtain Backup Information

Before making a backup, you must identify all the files in your database and decide what to back up. Several V$ views can provide the necessary information.

Listing Database Files Before a Backup


select name from v$datafile
union all
select member from v$logfile
union all
select name from v$controlfile;


Determining Datafile Status for Online Tablespace Backups

You can check V$BACKUP views to determine datafile is part of backup or not.
it is also usefull when you database startup after instance failure through this view you can know datafile is part of online backup when database is open.

V$backup view is not useful when current controlfile is created from restored backup or RE-CREATE statment becuase it is not content information about backup datafile.

Through below query we can know status of datafile backup status

SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE'