rman backup manage common sql — by firsouler

• 备份信息检查
  • RMAN 列出需要备份的信息
  • RMAN命令跟踪
  • 检查备份集信息
  • 查看某个备份集中归档日志
  • 查看某个备份集SPFILE
  • 查看rman配置信息
• 恢复前检查
• Data Recovery Advisor
• RMAN备份脚本

备份信息检查

RMAN 列出需要备份的信息

--根据配置策略，列出需要备份的
rman target / 
report need backup;

RMAN命令跟踪

#跟踪
rman target / debug trace=/tmp/rman.trc

检查备份集信息

--rman 检查备份文件情况，类似list backup summary
--LV=L:redo log  A:archivelog F:full  0/1 incremental
--S= A:AVAILABLE D:delete  E:expired
col path for a40
col LV for a2
col D_Type for a6
col Tag for a18
col "Size(G)" for 9999
col E_Hours for 999
col S for a2
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
SELECT distinct A.RECID "BACKUP SET",A.SET_STAMP,
        DECODE (B.INCREMENTAL_LEVEL,
                '', DECODE (BACKUP_TYPE, 'L', 'A', 'F'),
                1, '1',
                0, '0',
                B.INCREMENTAL_LEVEL)
           "LV",
--        B.CONTROLFILE_INCLUDED "INcL_CTL", --备份默认会备份控制文件，可不写
        DECODE (A.STATUS,
                'A', 'A',
                'D', 'D',
                'X', 'E',
                'ERROR')
           "S",
        A.DEVICE_TYPE "D_Type",
        A.START_TIME "Start Time",
        A.COMPLETION_TIME "Completion Time",
       round(A.ELAPSED_SECONDS/60/60,2) "E_Hours",
        round(A.BYTES/1024/1024/1024,2) "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' and a.COMPLETION_TIME>=sysdate-30 
  ORDER BY A.COMPLETION_TIME;

查找某个备份集中包含数据文件

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 c.set_stamp=&set_stamp
 ORDER BY C.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;

查看某个备份集中归档日志

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#;

查看某个备份集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;

查看rman配置信息

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

恢复前检查

 alter session set nls_date_format='YYYY/mm/dd HH24:MI:SS';
 select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ;
 SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME,name FROM V$ARCHIVED_LOG   WHERE '2019/04/08 14:05:20' BETWEEN FIRST_TIME AND NEXT_TIME;
 select max(first_time),max(next_time),name,status,COMPLETION_TIME from v$archived_log;
set lines 2000
col name for a50
alter session set nls_date_format='YYYY/mm/dd HH24:MI:SS';
select first_time,next_time,name,status,COMPLETION_TIME from v$archived_log where completion_time=(select max(completion_time) from v$archived_log);
 select first_time,next_time,name,status,COMPLETION_TIME from v$archived_log where sequence#=66;
 catalog archivelog '/backup/archivelog/1_67_964451570.dbf';
 catalog archivelog '/backup/archivelog/1_68_964451570.dbf';
 catalog archivelog '/backup/archivelog/1_69_964451570.dbf';
recover database until time "to_date('2019/04/08 14:05:20','YYYY/MM/DD HH24:MI:SS')";

Data Recovery Advisor

--列出需要修复的问题，rac中不支持
list failure detail;
list failure 142 detail;
advise failure;
repair failure preview;
repair failure;
validate database;
validate tablespace users;
validate datafile 1;
validate datafile 4 block 56;

RMAN备份脚本

--status
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select sid,start_time,status,end_time from V$RMAN_STATUS where STATUS='RUNNING';
backup as compressed backupset database format '/u02/backup/full_%U_%s.dat'
 include current controlfile spfile plus archivelog delete all input;
backup as backupset database format '/home/oracle/full_%U_%s.dat' 
include current controlfile spfile plus archivelog delete all input;
--To restore a datafile, either mount the database, or keep it open and take the datafile to be restored offline. 
--When RMAN performs a restore, it creates the restored files as datafile image copies and records them in the repository
--开启块跟踪
 alter database enable block change tracking using file 'd:\oradata\block_change_track';
--rman备份
mkdir -p /home/oracle/scripts/log
vi  /home/oracle/scripts/rmanbackup0.sh
#!/bin/sh
source ~/.bash_profile
DATE=`date +%Y-%m-%d`
rman cmdfile /home/oracle/scripts/backup0.rcv log=/home/oracle/scripts/log/rman0_$DATE.log
vi  /home/oracle/scripts/backup0.rcv
connect target  /
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup incremental level 0 format '/backup/orcl_incr0_%T_%s_%p' database include current controlfile;
backup format '/backup/orcl_controlfile_%T_%s_%p' current controlfile;
backup format '/backup/orcl_spfile_%T_%s_%p' spfile;
delete noprompt obsolete redundancy=2;
backup  format '/backup/orcl_arch_full_%T_%s_%p' archivelog all;
crosscheck archivelog all;
--delete noprompt archivelog until time 'sysdate-7';
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
vi /home/oracle/scripts/rmanbackup1.sh
#!/bin/sh
source ~/.bash_profile
DATE=`date +%Y-%m-%d`
rman cmdfile /home/oracle/scripts/backup1.rcv log=/home/oracle/scripts/log/rman1_$DATE.log
vi /home/oracle/scripts/backup1.rcv
connect target  /
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup incremental level 1 format '/backup/xckydg_incr1_%T_%s_%p' database;
backup format '/backup/orcl_controlfile_%T_%s_%p' current controlfile;
delete noprompt obsolete redundancy=2;
backup format '/backup/xckydg_arch_inc_%T_%s_%p' archivelog all;
crosscheck archivelog all;
delete noprompt archivelog until time 'sysdate-7';
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
release channel d1;
release channel d2;
}
chmod u+x /home/oracle/scripts/rmanbackup1.sh
chmod u+x /home/oracle/scripts/rmanbackup0.sh
chmod u+x /home/oracle/scripts/rmanbackup_arch.sh
0 23 * * 6 /home/oracle/scripts/rmanbackup0.sh
0 23 * * 0,1,2,3,4,5 /home/oracle/scripts/rmanbackup1.sh
0 12,23 * * * /home/oracle/scripts/rmanbackup_arch.sh
--backup archived_log
#!/usr/bin/ksh
#back_arch.sh
t1=`date +'%y-%m-%d %H:%M'`
#2013-06-20 14:58
rman target / << EOF
run{
allocate channel ch1;
allocate channel ch2;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
backup archivelog util time "to_date('2013-11-01 12:30','yyyy-mm-dd hh24:mi:ss')" format '/u02/backup/arch%s_%u.bk';
release channel ch1;
release channel ch2;
}
EOF
t2=`date +'%y-%m-%d %H:%M'`
echo "start time is :  "$t1;
echo "end time is :  "$t2;