Oracle 常用指令 & SQL - RMAN 篇 (RMAN section)

RMAN 相關指令 & SQL

-- 查詢歷史備份記錄

COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select INPUT_TYPE,
STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY;

-- 開啟debug模式

$ rman target sys/password debug trace=/tmp/rman.log

-- Monitor Backup (Single Database)

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'
/

REM RMAN Progress
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
       sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

REM RMAN wiats
set lines 120
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
SELECT SID, SPID, CLIENT_INFO, event, seconds_in_wait secs, p1, p2, p3
  FROM V$PROCESS p, V$SESSION s
  WHERE p.ADDR = s.PADDR
  and CLIENT_INFO like 'rman channel=%'
/

--OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK
/

-- Monitor restore (RAC mode)

TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
  FROM gv$session_longops
 WHERE opname LIKE 'RMAN%'
   AND totalwork != 0
   AND sofar <> totalwork
 ORDER BY 1;

TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
  FROM gv$process p, gv$session s
 WHERE p.addr = s.paddr
   AND client_info LIKE 'rman%';

TTITLE LEFT 'Files currently being written to:'
COL filename FORMAT a50
SELECT filename, bytes, io_count
  FROM v$backup_async_io
 WHERE status='IN PROGRESS'
/

TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
       ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||
       ROUND(SUM(v.value     /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
            FROM v$session_longops
            WHERE opname          LIKE 'RMAN: aggregate input'
              AND sofar           != TOTALWORK
              AND elapsed_seconds IS NOT NULL
       ),SUM(v.value     /1024/1024)),2) || ' Meg/sec'
 FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
  AND n.name       = 'physical write total bytes'
  AND v.sid        = s.sid
  AND v.inst_id    = s.inst_id
  AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON

-- 備份資料庫 (含壓縮)

RMAN> run {
allocate channel c1 type disk;
backup as compressed backupset database format '/backup/%U';
release channel c1;
}

-- 備份archivelog (含壓縮)

RMAN> run {
allocate channel c1 type disk;
backup archivelog all format '/backup/backup_archivelog_%d_%T_%s_%p';
release channel c1;
}

-- Restore Archivelog File from NBU

RMAN> run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=xxxx,NB_ORA_CLIENT=xxxx,NB_ORA_POLICY=xxxx';
set archivelog destination to "+FRADG/orcl/archivelog";
restore archivelog from logseq=38830 until logseq=38834 thread=1;
restore archivelog from logseq=9544 until logseq=9548 thread=2;
release channel c00;
release channel c01;
}

-- Restore Database to Special time from NBU

RMAN> RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
SEND 'NB_ORA_SERV=xxxx,NB_ORA_CLIENT=xxxx,NB_ORA_POLICY=xxxx';
alter database mount;
set until time "to_date('202220503 10:10:00','yyyymmdd hh24:mi:ss')";
restore database;
recover database;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}

Ref：

Script to monitor RMAN progress

Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2)