Querying V$ Views Before Backup&Recovery

（一）Querying V$ Views to Obtain Backup Information

The following table lists the techniques used to access metadata from the RMAN repository.

1. RMAN LIST and REPORT commands：用于RMAN备份恢复
2. V$ views：用于手动备份恢复

When the database is open, several V$ views provide direct access to RMAN repository records in the control file of each target database. Example: V$BACKUP_PIECE, V$BACKUP_SET

In some cases, V$ views supply information that is not available through use of the LIST and REPORT commands. Some V$ views such as V$DATAFILE_HEADER, V$PROCESS, and V$SESSION contain information not found in the recovery catalog views.

1. RC_ views：用于使用catalog

If your database is registered in a recovery catalog, then RC_ views provide direct access to the RMAN repository data stored in the recovery catalog. The RC_ views mostly correspond to the V$ views.

1. Report Schema

手动备份同RMAN备份只需要备份三种文件：数据文件、控制文件及归档日志

To list data files and control files:

查看数据文件名及对应表空间:

SELECT   t.NAME "Tablespace", f.NAME "Data File"

FROM     V$TABLESPACE t, V$DATAFILE f

WHERE    t.TS# = f.TS#

ORDER BY t.NAME;

查看控制文件名:

SELECT NAME FROM V$CONTROLFILE;

查看归档日志：

SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;

Note: 如果数据库归档到多个目录你只需要备份each log sequence number的日志即可，手动备份的方式就是直接复制，如：

% cp $ORACLE_HOME/oracle/trgt/arch/* /disk2/backup/arch

1. 查看当前数据文件状态是否为BACKUP MODE

V$BACKUP用于查看当前数据文件是否处于BACKUP MODE，不能用于OFFLINE数据文件

如果还原了控制文件或重建了控制文件则无法使用V$BACKUP

V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information that the database needs to populate V$BACKUP accurately. Also, if you have restored a backup of a file, this file's STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view can contain misleading data about restored files.

查看数据文件是否处于backup mode(STATUS为ACTIVE则表示处理BACKUP MODE)

SELECT FILE#,STATUS,CHANGE# FROM V$BACKUP;

如果STATUS为INACTIVE则CHANGE#显示上次BEGIN BACKUP的时间，如果STATUS为ACTIVE则CHANGE#为本次BEGIN BACKUP的时间

（二）Querying V$ Views to Obtain Backup Information

1. Determining Which Data File Need Recovery

Query V$RECOVER_FILE to determine which data files must be recovered and why :

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#

FROM V$RECOVER_FILE;

The ERROR column identifies the problem for each file requiring recovery. 为空表示未知

Change#表示从哪个SCN开始恢复

使用备份的控制文件时无法使用V$RECOVER_FILE

You cannot use V$RECOVER_FILE with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the data files. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE accurately.

 V$RECOVER_FILE信息简单对于复杂恢复还是要查看V$DATAFILE与V$DATAFILE_HEADER

1. V$DATAFILE displays datafile information from the control file.

查看数据文件当前状态及CHECKPOINT SCN：

SELECT FILE#,STATUS,ENABLED,CHECKPOINT_CHANGE# FROM V$DATAFILE;

STATUS: offline|online|System(表示系统表空间)|sysoff(OFFLINE的系统表空间)                                  

ENABLED：disabled(SQL不可访问)|read only|read write|unknown(控制文件corrupted)                                  

查看数据文件上一次OFFLINE的时间段(注如果数据文件已OFFLINE表示上一次而非本次)：

SELECT FILE#,OFFLINE_CHANGE#, ONLINE_CHANGE# FROM V$DATAFILE;

查看处于OFFLINE状态的数据文件OFFLINE SCN(当前非OFFLINE显示空):

SELECT FILE#, LAST_CHANGE# FROM V$DATAFILE;

查数据文件最后一次NOLOGGING操作:

SELECT FILE#,UNRECOVERABLE_CHANGE# FROM V$DATAFILE;

1. V$DATAFILE_HEADER displays datafile information from the datafile headers.

V$datafile_header最重要的是ERROR及CHECKPOINT_CHANGE#列

查看坏文件：

Set linesize 200

Col error format a50

Col recover format a10

Col tablespace_name format a20

SELECT file#, tablespace_name,status, error, recover

FROM V$DATAFILE_HEADER;

如果ERROR非空有两种情况，如果无法读文件则记录其它信息也为空；如果可读但校验失败则记录其它列有数据但不可信。ERROR非空一般都是要RESTORE不能直接RECOVER恢复，ERROR为空但RECOVER为YES表示不需要RESTORE直接RECOVER即可

ERROR: NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used.

查看SCN：

SELECT FILE#, TABLESPACE_NAME,RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#

FROM V$DATAFILE_HEADER;

2. Determining Which Archived Redo Log files are Needed

V$RECOVERY_LOG列出数据文件完全恢复所需归档文件名

V$RECOVERY_LOG lists information about archived logs that are needed to complete media recovery. This information is derived from the log history view, V$LOG_HISTORY.

其中ARCHVIE_NAME列是通过 V$LOG_HISTORY以及LOG_ARCHIVE_FORMAT参数得出，所以实现可能归档文件并不存在，如果归档备份到其它位置需要先还原归档日志

V$RECOVERY_LOG view also includes the probable names of the files based on the naming convention specified by using the LOG_ARCHIVE_FORMAT parameter.

If some archived logs must be restored, and if sufficient space is available, then restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1. The database locates the correct log automatically when required during media recovery.

If sufficient space is not available, then restore some or all of the required archived redo log files to an alternative location.

恢复不外乎三个SCN: 控制文件中数据文件的SCN，数据文件SCN以及归档日志SCN，比较三个SCN即可分析需要从哪个SCN恢复到哪个SCN，使用什么归档日志等

查看控制文件中数据文件当前状态及CHECKPOINT SCN(没有RESETLOGS_CHANGE#)：

SELECT FILE#,STATUS,ENABLED,CHECKPOINT_CHANGE# FROM V$DATAFILE;

查看数据文件SCN：

SELECT FILE#, TABLESPACE_NAME,RESETLOGS_CHANGE#,CHECKPOINT_CHANGE#

FROM V$DATAFILE_HEADER;

查看归档日志SCN(NAME为空表示需要还原归档备份):

SELECT NAME, SEQUENCE#, RESETLOGS_CHANGE#,NEXT_CHANGE#

FROM V$ARCHIVED_LOG;

FIRST_CHANGE#为日志起始SCN, NEXT_CHANGE#为下个日志的起始SCN

V$ARCHIVED_LOG与V$LOG_HISTORY:

V$LOG_HISTORY是REDO LOG的历史记录，不开归档也会有，它没有归档文件信息，V$ARCHIVED_LOG是在归档成功后添加的记录。它们均存于控制文件，重建控制文件CREATE CONTORLFILE将重置视图数据，使用备份控制文件显示的是备份控制文件时数据库信息。

V$LOG_HISTORY displays log history information from the control file.

V$ARCHIVED_LOG displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN COPY command.

V$ARCHIVED_LOG受RMAN操作影响(手动备份或删除不会影响此视图需要在RMAN下CROSSCHECK才行)而V$LOG_HISTORY不受，在查看V$ARCHIVED_LOG前最好先执行CROSSCHECK ARCHIVELOG ALL; DELETE EXPIRED ARCHIVELOG ALL;

如在RMAN执行以下命令

BACKUP UP ARCHIVELOG ALL DELETE ALL INPUT;

此时V$ARCHIVED_LOG中NAME列全部为空，再执行以下命令

RESTORE ARCHIVELOG ALL;

此时V$ARCHIVED_LOG增加了新的记录，新记录NAME列为归档文件名，但之前NAME为空的记录并不删除。所以相同RESETLOGS_CHANGE#与SEQUENCE#下的NAME全为空才表示不存在实际文件

1. Determining Status during Recovery

After issuing the SQL*Plus RECOVER command, you can view all files that have been considered for recovery in the V$RECOVERY_FILE_STATUS view.

You can access status information for each file in the V$RECOVERY_STATUS view. These views are not accessible after you terminate the recovery session.