RMAN VIEWS (WHEN NO RECOVERY CATALOG AVAILABLE) . i have provide the example as per 11gR2.
To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the recover catalog. (Work in Mount stage)
SQL> select session_key, input_type, status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time, output_bytes_display,
time_taken_display from v$rman_backup_job_details order by session_key asc;
Below query will display the backup piece that hold the backup of archive log, datafile in the DISK or TAPE Drive.
Query to see Backed Up Archive Logs in the database (Work in Mount stage)
SQL> select recid,set_stamp,sequence#,first_change#,next_change#
from v$backup_redolog;
Query against the V$BACKUP_PIECE view to find backup details of a particular archive log (Work in Mount stage)
SQL> select r.sequence#, p.handle from v$backup_piece p, v$backup_redolog r
where r.set_stamp = p.set_stamp and r.set_count = p.set_count and r.sequence# = 63
Note: 63 is the sequence number.
Query against the V$BACKUP_PIECE view to find backup details of a particular datafiles (Work in Mount stage)
SQL> select d.file#, p.handle from v$backup_piece p, v$backup_datafile d where d.set_stamp = p.set_stamp and d.set_count = p.set_count and d.file# = 3
Note: 3 is the datafile number.
SQL> select table_name from dict where table_name like 'V$%RMAN%';
TABLE_NAME
------------------------------
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_TYPE
V$RMAN_COMPRESSION_ALGORITHM
V$RMAN_CONFIGURATION
V$RMAN_ENCRYPTION_ALGORITHMS
V$RMAN_OUTPUT
V$RMAN_STATUS
8 rows selected.
V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.
V$RMAN_BACKUP_SUBJOB_DETAILS è merges similar operations within an RMAN session into a single row. For example, if there are four BACKUP DATAFILE
V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.
SQL> select * from V$RMAN_BACKUP_TYPE;
WEIGHT INPUT_TYPE
---------- -------------
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL
V$RMAN_COMPRESSION_ALGORITHM è provides descriptions of supported compression algorithms. It is used by the RMAN client.
V$RMAN_CONFIGURATION è Information about RMAN persistent configuration settings.
V$RMAN_ENCRYPTION_ALGORITHMS è displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is AES128.
V$RMAN_OUTPUT è displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can hold 32768 rows.
V$RMAN_STATUS è displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs which are in progress are stored only in memory while the finished jobs are stored in the controlfile
+++++++++++++++++++++++++++++++++++++++++++++++++++
Recent Zero Level Backup
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t<br>
FROM v$backup_datafile b, v$tablespace ts, v$datafile f<br>
WHERE b.incremental_level = 0<br>
AND INCLUDED_IN_DATABASE_BACKUP='YES'<br>
AND f.file#=b.file#<br>
AND f.ts#=ts.ts#<br>
GROUP BY b.checkpoint_time<br>
ORDER BY 1;<br>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Check the Status of Backup
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from v$datafile),
(select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1),
(select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from v$backup_spfile
where completion_time > sysdate - 1);
To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the recover catalog. (Work in Mount stage)
SQL> select session_key, input_type, status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time, output_bytes_display,
time_taken_display from v$rman_backup_job_details order by session_key asc;
Below query will display the backup piece that hold the backup of archive log, datafile in the DISK or TAPE Drive.
Query to see Backed Up Archive Logs in the database (Work in Mount stage)
SQL> select recid,set_stamp,sequence#,first_change#,next_change#
from v$backup_redolog;
Query against the V$BACKUP_PIECE view to find backup details of a particular archive log (Work in Mount stage)
SQL> select r.sequence#, p.handle from v$backup_piece p, v$backup_redolog r
where r.set_stamp = p.set_stamp and r.set_count = p.set_count and r.sequence# = 63
Note: 63 is the sequence number.
Query against the V$BACKUP_PIECE view to find backup details of a particular datafiles (Work in Mount stage)
SQL> select d.file#, p.handle from v$backup_piece p, v$backup_datafile d where d.set_stamp = p.set_stamp and d.set_count = p.set_count and d.file# = 3
Note: 3 is the datafile number.
SQL> select table_name from dict where table_name like 'V$%RMAN%';
TABLE_NAME
------------------------------
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_TYPE
V$RMAN_COMPRESSION_ALGORITHM
V$RMAN_CONFIGURATION
V$RMAN_ENCRYPTION_ALGORITHMS
V$RMAN_OUTPUT
V$RMAN_STATUS
8 rows selected.
V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.
V$RMAN_BACKUP_SUBJOB_DETAILS è merges similar operations within an RMAN session into a single row. For example, if there are four BACKUP DATAFILE
V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.
SQL> select * from V$RMAN_BACKUP_TYPE;
WEIGHT INPUT_TYPE
---------- -------------
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL
V$RMAN_COMPRESSION_ALGORITHM è provides descriptions of supported compression algorithms. It is used by the RMAN client.
V$RMAN_CONFIGURATION è Information about RMAN persistent configuration settings.
V$RMAN_ENCRYPTION_ALGORITHMS è displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is AES128.
V$RMAN_OUTPUT è displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can hold 32768 rows.
V$RMAN_STATUS è displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs which are in progress are stored only in memory while the finished jobs are stored in the controlfile
+++++++++++++++++++++++++++++++++++++++++++++++++++
Recent Zero Level Backup
SELECT DISTINCT TO_CHAR((b.CHECKPOINT_TIME), 'YYYY-MM-DD HH:MI.SS') t<br>
FROM v$backup_datafile b, v$tablespace ts, v$datafile f<br>
WHERE b.incremental_level = 0<br>
AND INCLUDED_IN_DATABASE_BACKUP='YES'<br>
AND f.file#=b.file#<br>
AND f.ts#=ts.ts#<br>
GROUP BY b.checkpoint_time<br>
ORDER BY 1;<br>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Check the Status of Backup
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from v$datafile),
(select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1),
(select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from v$backup_spfile
where completion_time > sysdate - 1);
