Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the method of SQLPLUS using view to view RMAN backup set information". In daily operation, I believe that many people have doubts about the way SQLPLUS uses view to view RMAN backup set information. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubt that "SQLPLUS uses view to view RMAN backup set information". Next, please follow the editor to study!

With regard to the RMAN backup of the Oracle database, is it possible to check the success and failure of the RMAN backup in other ways than mail? In fact, we can check the record of backup failure for a certain period of time through the following SQL script:

SELECT * FROM V$RMAN_STATUSWHERE START_TIME > = TO_DATE (& START_TIME,'YYYY-MM-DD HH24:MI:SS') AND END_TIME = TO_DATE (& START_TIME,'YYYY-MM-DD HH24:MI:SS') AND END_TIME

In V$RMAN_STATUS, you will see that a record has been inserted that STATUS is RUNNING status

SQL > COL ROW_TYPE FOR A10 FROM V$RMAN_STATUS > COL OPERATION FOR A10 > COL COMMAND_ID FOR A20 > COL STATUS FOR A30 > COL OBJECT_TYPE FOR A16 > SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE 2 FROM V$RMAN_STATUS 3 WHERE START_TIME > = TO_DATE ('2016-07-19 10 FROM V$RMAN_STATUS 52 WHERE START_TIME,' YYYY-MM-DD HH24:MI:SS')

ROW_TYPE COMMAND_ID OPERATION STATUS OBJECT_TYPE--SESSION 2016-07-19T10:52:13 RMAN RUNNING

At this point, if you randomly execute an error command in RMAN, as follows

[oracle@MyDB ~] $rman target /

Recovery Manager: Release 10.2.0.5.0-Production on Tue Jul 19 10:52:13 2016

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to target database: SCM2 (DBID=3990839260)

RMAN > /

RMAN-00571: = = RMAN-00569: = ERROR MESSAGE STACK FOLLOWS = RMAN-00571: = = RMAN-00558: error encountered while parsing input commandsRMAN-01006: error signalled during parseRMAN-02001: unrecognized punctuation symbol "/"

SQL > COL ROW_TYPE FOR A10 FROM V$RMAN_STATUS > COL OPERATION FOR A10 > COL COMMAND_ID FOR A20 > COL STATUS FOR A30 > COL OBJECT_TYPE FOR A16 > SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE 2 FROM V$RMAN_STATUS 3 WHERE START_TIME > = TO_DATE ('2016-07-19 10 FROM V$RMAN_STATUS 52 WHERE START_TIME,' YYYY-MM-DD HH24:MI:SS')

ROW_TYPE COMMAND_ID OPERATION STATUS OBJECT_TYPE--SESSION 2016-07-19T10:52:13 RMAN RUNNING WITH ERRORS

Exit in RMAN, and you will see that the STAUS record has changed from "RUNNING WITH ERRORS" to "COMPLETED WITH ERRORS"

In other words, you can view some operations performed in RMAN in this view, such as deleting archive logs, and so on. In addition, if you want to view the output of RMAN, you can see that the view records the information generated by RMAN, which is in memory and will not be recorded on the control file. There are no more than 37278 records.

Of course, you can also view more detailed information about RMAN backups from V$RMAN_BACKUP_JOB_DETAILS.

SELECT START_TIME, END_TIME, OUTPUT_DEVICE_TYPE, STATUS, ELAPSED_SECONDS, COMPRESSION_RATIO, INPUT_BYTES_DISPLAY, OUTPUT_BYTES_DISPLAYFROM V$RMAN_BACKUP_JOB_DETAILSORDER BY START_TIME DESC

In addition, if we need to check some detailed records of RMAN backups, we share some classic SQL statements in our sql query rman backup information blog. It's included here.

View all backup set details:

SELECT A.RECID "BACKUP SET", A.SET_STAMP, DECODE (B.INCREMENTAL_LEVEL,'', DECODE (BACKUP_TYPE, 'Incr-1,' Archivelog', 'Full'), 1,' Incr-1', 0, 'Incr-0', B.INCREMENTAL_LEVEL) "Type LV" B.CONTROLFILE_INCLUDED "contains CTL", DECODE (A.STATUS, 'Aids,' AVAILABLE', 'Downs,' DELETED', 'Xrays,' EXPIRED', 'ERROR') "STATUS", A.DEVICE_TYPE "Device Type", A.START_TIME "Start Time" A.COMPLETION_TIME "Completion Time", A.ELAPSED_SECONDS "Elapsed Seconds", A.BYTES/1024/1024/1024 "Size (G)", A.COMPRESSED, A.TAG "Tag", A.HANDLE "Path" FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'RDER BY A.COMPLETION_TIME DESC

Find a backup set that contains data files

SELECT DISTINCT c. Fileholders, A.SETOESTAMP, D.NAME, C.CHECKPOINTINTCHANGELES, C.CHECKPOINT_TIMEFROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE DWHERE A.SET_STAMP = C.SET_STAMP AND D.FILE# = C.FILE# AND A.DELETEDRONO 'AND c.set_stamp=&set_stamp ORDER BY C.FILE#

Query a backup centralized control file

SELECT DISTINCT A.SET_STAMP, D.NAME, C.CHECKPOINTCHANGENG, C.CHECKPOINT_TIME FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D WHERE A.SET_STAMP = C.SET_STAMP AND C.FILE# = 0 AND A.DELETED = 'NO' AND C.SET_STAMP = & SET_STAMP

View the archive logs in a backup set:

SELECT DISTINCT B.SET_STAMP, B.THREAD#, B.SEQUENCE#, B.FIRST_TIME, B.FIRST_CHANGE#, B.NEXT_TIME, B.NEXT_CHANGE# FROM V$BACKUP_REDOLOG B V$BACKUP_PIECE A WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' AND B.SET_STAMP = & SET_STAMP ORDER BY THREAD#, SEQUENCE#

View a backup set SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLEFROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' AND B.SET_STAMP = & SET_STAMP

View the configuration information of RMAN

SELECT NAME,VALUE FROM backup configuration; at this point, the study on "how SQLPLUS uses views to view RMAN backup set information" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.