检查某个时间段备份情況
[oracle@DB01 ~]$ sql / as sysdba
SQL> SELECT t.START_TIME,t.END_TIME,t.STATUS,t.OBJECT_TYPE,t.RECID
FROM V$RMAN_STATUS t where t.START_TIME between trunc(sysdate)-2 and sysdate
and t.OPERATION='BACKUP';
START_TIM END_TIME STATUS OBJECT_TYPE RECID
--------- --------- ----------------------- ------------- ----------
06-FEB-19 06-FEB-19 COMPLETED DB FULL 403
06-FEB-19 06-FEB-19 COMPLETED ARCHIVELOG 405
07-FEB-19 07-FEB-19 COMPLETED ARCHIVELOG 415
08-FEB-19 08-FEB-19 COMPLETED ARCHIVELOG 425
07-FEB-19 07-FEB-19 COMPLETED DB FULL 413
08-FEB-19 08-FEB-19 COMPLETED DB FULL 423
6 rows selected.
SQL> SELECT ROW_TYPE,COMMAND_ID,OPERATION,STATUS,OBJECT_TYPE FROM V$RMAN_STATUS WHERE START_TIME >= trunc(sysdate);
ROW_TYPE COMMAND_ID OPERATION STATUS OBJECT_TYPE
------------------- --------------------------------- --------------------------------- ----------------------- -------------
RECURSIVE OPERATION 2019-02-08T01:00:01 CONTROL FILE AND SPFILE AUTOBACK COMPLETED
SESSION 2019-02-08T01:00:01 RMAN COMPLETED
COMMAND 2019-02-08T01:00:01 BACKUP COMPLETED ARCHIVELOG
COMMAND 2019-02-08T01:00:01 DELETE OBSOLETE COMPLETED
COMMAND 2019-02-08T01:00:01 DELETE COMPLETED DB FULL
COMMAND 2019-02-08T01:00:01 CROSSCHECK COMPLETED ARCHIVELOG
COMMAND 2019-02-08T01:00:01 CROSSCHECK COMPLETED DB FULL
RECURSIVE OPERATION 2019-02-08T01:00:01 CONTROL FILE AND SPFILE AUTOBACK COMPLETED
COMMAND 2019-02-08T01:00:01 DELETE COMPLETED ARCHIVELOG
COMMAND 2019-02-08T01:00:01 BACKUP COMPLETED DB FULL
10 rows selected.
从V$RMAN_BACKUP_JOB_DETAILS中查看RMAN备份更详细的信息
SQL> set sqlformat ansiconsole
SQL> 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 where START_TIME>=trunc(sysdate)-1 ORDER BY START_TIME DESC;
START_TIME END_TIME OUTPUT_DEVICE_TYPE STATUS ELAPSED_SECONDS COMPRESSION_RATIO INPUT_BYTES_DISPLAY OUTPUT_BYTES_DISPLAY
08-FEB-19 08-FEB-19 DISK COMPLETED 199 11.4289011154252405915030122838588529849 17.83G 1.56G
07-FEB-19 07-FEB-19 DISK COMPLETED 196 11.11955723199367356069664120684631374387 17.94G 1.61G
SQL> SELECT t.COMMAND_ID as "备份名",t.STATUS as "狀態",t.START_TIME as "開始時間",t.TIME_TAKEN_DISPLAY as "所用時間",t.END_TIME as "結束時間"
,t.INPUT_TYPE as "類型",t.OUTPUT_DEVICE_TYPE as "輸出設備",t.INPUT_BYTES_DISPLAY as "輸入大小",t.OUTPUT_BYTES_DISPLAY as "輸出大小"
,t.OUTPUT_BYTES_PER_SEC_DISPLAY as "輸出速率(每秒)"
FROM V$RMAN_BACKUP_JOB_DETAILS t where START_TIME>=trunc(sysdate)-7 ORDER BY START_TIME DESC;
备份名 狀態 開始時間 所用時間 結束時間 類型 輸出設備 輸入大小 輸出大小 輸出速率(每秒)
2019-07-25T01:00:01 COMPLETED 25-JUL-19 00:09:16 25-JUL-19 DB FULL DISK 28.44G 5.18G 9.53M
2019-07-24T09:40:13 COMPLETED 24-JUL-19 00:06:37 24-JUL-19 DB FULL DISK 32.20G 6.17G 15.92M
2019-07-24T01:00:01 COMPLETED 24-JUL-19 00:11:53 24-JUL-19 DB FULL DISK 29.62G 5.53G 7.94M
2019-07-23T01:00:01 COMPLETED 23-JUL-19 00:12:07 23-JUL-19 DB FULL DISK 29.32G 5.45G 7.68M
2019-07-22T01:00:01 COMPLETED 22-JUL-19 00:13:50 22-JUL-19 DB FULL DISK 32.02G 6.34G 7.82M
2019-07-21T01:00:01 COMPLETED 21-JUL-19 00:13:35 21-JUL-19 DB FULL DISK 33.79G 6.73G 8.46M
2019-07-20T01:00:01 COMPLETED 20-JUL-19 00:10:59 20-JUL-19 DB FULL DISK 29.49G 5.25G 8.16M
2019-07-19T01:00:01 COMPLETED 19-JUL-19 00:10:19 19-JUL-19 DB FULL DISK 27.61G 4.77G 7.89M
2019-07-18T01:00:01 COMPLETED 18-JUL-19 00:10:35 18-JUL-19 DB FULL DISK 28.32G 5.14G 8.29M
查看所有备份集详细信息
SQL>
SELECT A.RECID "BACKUP SET",A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1级',
0, 'Incr-0级',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', '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'
ORDER BY A.COMPLETION_TIME DESC;
BACKUP SET SET_STAMP Type LV 包含CTL STATUS Device Type Start Time Completion Time Elapsed Seconds Size(G) COMPRESSED Tag Path
544 999651798 Full YES AVAILABLE DISK 08-FEB-19 08-FEB-19 0 0.020233154296875 NO TAG20190208T010318 /u03/fra/MPCDB/MPCDB01/autobackup/2019_02_08/o1_mf_s_999651798_g5rs6q5c_.bkp
543 999651783 Archivelog NO AVAILABLE DISK 08-FEB-19 08-FEB-19 14.99999999999999999999999999999999999999 0.12280368804931640625 YES ARC_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_annnn_ARC_20190208_0100_g5rs67nq_.bkp
542 999651783 Archivelog NO AVAILABLE DISK 08-FEB-19 08-FEB-19 14.99999999999999999999999999999999999999 0.11543941497802734375 YES ARC_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_annnn_ARC_20190208_0100_g5rs67pc_.bkp
543 999651783 Archivelog NO AVAILABLE DISK 08-FEB-19 08-FEB-19 14.99999999999999999999999999999999999999 0.12280368804931640625 YES ARC_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_annnn_ARC_20190208_0100_g5rs67nq_.bkp
542 999651783 Archivelog NO AVAILABLE DISK 08-FEB-19 08-FEB-19 14.99999999999999999999999999999999999999 0.11543941497802734375 YES ARC_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_annnn_ARC_20190208_0100_g5rs67pc_.bkp
541 999651780 Full YES AVAILABLE DISK 08-FEB-19 08-FEB-19 0 0.020233154296875 NO TAG20190208T010300 /u03/fra/MPCDB/MPCDB01/autobackup/2019_02_08/o1_mf_s_999651780_g5rs64tb_.bkp
540 999651730 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 45.99999999999999999999999999999999999996 0.31397247314453125 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs4n2x_.bkp
539 999651772 Full YES AVAILABLE DISK 08-FEB-19 08-FEB-19 0 0.0013275146484375 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_ncnnf_DAT_20190208_0100_g5rs5xcr_.bkp
538 999651747 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 16.99999999999999999999999999999999999998 0.10214996337890625 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A24C159DF592318E0530102000AED4B/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs5372_.bkp
537 999651731 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 7 0.05745697021484375 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A24C159DF592318E0530102000AED4B/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs4n24_.bkp
536 999651695 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 30.99999999999999999999999999999999999997 0.20328521728515625 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7F688CA50FBDF6F4E0530102000AC1AC/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs3j6g_.bkp
536 999651695 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 30.99999999999999999999999999999999999997 0.20328521728515625 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7F688CA50FBDF6F4E0530102000AC1AC/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs3j6g_.bkp
535 999651695 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 26.00000000000000000000000000000000000001 0.2084197998046875 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs3j5p_.bkp
535 999651695 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 26.00000000000000000000000000000000000001 0.2084197998046875 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs3j5p_.bkp
534 999651604 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 88.00000000000000000000000000000000000004 0.096832275390625 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A74EDADBCA427C7E0530102000AFEEF/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs0nkr_.bkp
534 999651604 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 88.00000000000000000000000000000000000004 0.096832275390625 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A74EDADBCA427C7E0530102000AFEEF/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs0nkr_.bkp
533 999651669 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 15.99999999999999999999999999999999999998 0.00323486328125 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7F688CA50FBDF6F4E0530102000AC1AC/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs2oxt_.bkp
532 999651604 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 54.99999999999999999999999999999999999999 0.29479217529296875 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A74EDADBCA427C7E0530102000AFEEF/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs0nlo_.bkp
532 999651604 Full NO AVAILABLE DISK 08-FEB-19 08-FEB-19 54.99999999999999999999999999999999999999 0.29479217529296875 YES DAT_20190208_0100 /u03/fra/MPCDB/MPCDB01/7A74EDADBCA427C7E0530102000AFEEF/backupset/2019_02_08/o1_mf_nnndf_DAT_20190208_0100_g5rs0nlo_.bkp
19 rows selected.
查找某个备份集中包含数据文件
SQL> 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.DELETED='NO'
and a.START_TIME>trunc(sysdate) AND c.set_stamp='999651695'
ORDER BY C.FILE#;
FILE# SET_STAMP NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME
1 999651695 /u02/oradata/MPCDB/system01.dbf 57245427286 08-FEB-19
4 999651695 /u02/oradata/MPCDB/undotbs01.dbf 57245427286 08-FEB-19
48 999651695 /u02/oradata/MPCDB/MPBUS/system01.dbf 57245427285 08-FEB-19
49 999651695 /u02/oradata/MPCDB/MPBUS/sysaux01.dbf 57245427285 08-FEB-19
51 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_UD_1.dbf 57245427285 08-FEB-19
52 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_UD_2.dbf 57245427285 08-FEB-19
53 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_UD_1.dbf 57245427285 08-FEB-19
54 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_UD_2.dbf 57245427285 08-FEB-19
55 999651695 /u02/oradata/MPCDB/MPBUS/mpbus_1901.dbf 57245427285 08-FEB-19
57 999651695 /u02/oradata/MPCDB/MPBUS/mpbus_1902.dbf 57245427285 08-FEB-19
59 999651695 /u02/oradata/MPCDB/MPBUS/mpbus_1903.dbf 57245427285 08-FEB-19
61 999651695 /u02/oradata/MPCDB/MPBUS/mpbus_1904.dbf 57245427285 08-FEB-19
63 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1901_A1.dbf 57245427285 08-FEB-19
65 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1901_B1.dbf 57245427285 08-FEB-19
67 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1902_A1.dbf 57245427285 08-FEB-19
69 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1902_B1.dbf 57245427285 08-FEB-19
71 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1903_A01.dbf 57245427285 08-FEB-19
73 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1903_B01.dbf 57245427285 08-FEB-19
75 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1904_A01.dbf 57245427285 08-FEB-19
77 999651695 /u02/oradata/MPCDB/MPBUS/MPBUS_1904_B01.dbf 57245427285 08-FEB-19
79 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1901_A1.dbf 57245427285 08-FEB-19
81 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1901_B1.dbf 57245427285 08-FEB-19
83 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1902_A1.dbf 57245427285 08-FEB-19
85 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1902_B1.dbf 57245427285 08-FEB-19
87 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1903_A01.dbf 57245427285 08-FEB-19
89 999651695 /u02/oradata/MPCDB/MPBUS/I_MPBUS_1903_B01.dbf 57245427285 08-FEB-19
26 rows selected.
查询某个备份集中控制文件
SQL> 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 a."START_TIME">trunc(sysdate) AND C.SET_STAMP = '999651780';
SET_STAMP NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME
999651780 /u02/oradata/MPCDB/control01.ctl 57245428541 08-FEB-19
999651780 /u02/oradata/MPCDB/control02.ctl 57245428541 08-FEB-19
查看某个备份集中归档日志
SQL> 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 a.START_TIME>trunc(sysdate) AND B.SET_STAMP ='999651783'
ORDER BY THREAD#, SEQUENCE#;
SET_STAMP THREAD# SEQUENCE# FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
999651783 1 829 06-FEB-19 57243994584 07-FEB-19 57244037592
999651783 1 830 07-FEB-19 57244037592 07-FEB-19 57244061653
999651783 1 831 07-FEB-19 57244061653 07-FEB-19 57244061928
999651783 1 832 07-FEB-19 57244061928 07-FEB-19 57245173624
999651783 1 833 07-FEB-19 57245173624 07-FEB-19 57245230837
999651783 1 834 07-FEB-19 57245230837 07-FEB-19 57245353308
999651783 1 835 07-FEB-19 57245353308 07-FEB-19 57245363606
999651783 1 836 07-FEB-19 57245363606 08-FEB-19 57245408036
999651783 1 837 08-FEB-19 57245408036 08-FEB-19 57245428563
查看某个备份集SPFILE
SQL> 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 a."START_TIME">trunc(sysdate) and B.SET_STAMP ='999651798';
SET_STAMP COMPLETION_TIME HANDLE
999651798 08-FEB-19 /u03/fra/MPCDB/MPCDB01/autobackup/2019_02_08/o1_mf_s_999651798_g5rs6q5c_.bkp
查看RMAN的配置信息
SQL> SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
NAME VALUE
RETENTION POLICY TO REDUNDANCY 3
ARCHIVELOG DELETION POLICY TO NONE
DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET
CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G MAXOPENFILES 8 RATE 100 M
CONTROLFILE AUTOBACKUP ON
