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

This article mainly introduces how to view the backup information script in the database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to know it.

View a backup set SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE

FROM 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 V$RMAN_CONFIGURATION

Check the record of failed backups in a certain period of time: SELECT * FROM V$RMAN_STATUS

WHERE 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 sysdate-3

RMAN progress query

Select case

When opname like'% aggregate%' then

'total'

Else

Opname

End opname

Trunc (sofar * 100 / totalwork, 2) | |'% 'progress

Units

From v$session_longops

Where opname like 'RMAN%'

And totalwork > sofar

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_STATUS

WHERE 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

SQL > COL OPERATION FOR A10

SQL > COL COMMAND_ID FOR A20

SQL > COL STATUS FOR A30

SQL > COL OBJECT_TYPE FOR A16

SQL > SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE

2 FROM V$RMAN_STATUS

3 WHERE START_TIME > = TO_DATE ('2016-07-19 10 YYYY-MM-DD HH24:MI:SS' 52 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 commands

RMAN-01006: error signalled during parse

RMAN-02001: unrecognized punctuation symbol "/"

Clip_image001

SQL > COL ROW_TYPE FOR A10

SQL > COL OPERATION FOR A10

SQL > COL COMMAND_ID FOR A20

SQL > COL STATUS FOR A30

SQL > COL OBJECT_TYPE FOR A16

SQL > SELECT ROW_TYPE, COMMAND_ID, OPERATION, STATUS,OBJECT_TYPE

2 FROM V$RMAN_STATUS

3 WHERE START_TIME > = TO_DATE ('2016-07-19 10 YYYY-MM-DD HH24:MI:SS' 52 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"

Clip_image002

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_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER 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, 'Archivelog',' Full')

1, 'Incr-1'

0, 'Incr-0 level'

B.INCREMENTAL_LEVEL)

"Type LV"

B.CONTROLFILE_INCLUDED "contains CTL"

DECODE (A.STATUS

'All, 'AVAILABLE'

'During, 'DELETED'

'Xantha, '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.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME

FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D

WHERE A.SET_STAMP = C.SET_STAMP

AND D.FILE# = C.FILE#

AND A.DELETEDIZATION no'

AND c.set_stamp=&set_stamp

ORDER BY C.FILE#

Query a backup centralized control file

SELECT DISTINCT A.SET_STAMP

D.NAME

C.CHECKPOINT_CHANGE#

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, HANDLE

FROM 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 V$RMAN_CONFIGURATION

Thank you for reading this article carefully. I hope the article "how to View backup Information script in Database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.