> rman backup manage common sql -- by firsouler <!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} --> <!-- code_chunk_output --> - [备份信息检查](#备份信息检查) - [RMAN 列出需要备份的信息](#rman-列出需要备份的信息) - [RMAN命令跟踪](#rman命令跟踪) - [检查备份集信息](#检查备份集信息) - [查看某个备份集中归档日志](#查看某个备份集中归档日志) - [查看某个备份集SPFILE](#查看某个备份集spfile) - [查看rman配置信息](#查看rman配置信息) - [恢复前检查](#恢复前检查) - [Data Recovery Advisor](#data-recovery-advisor) - [RMAN备份脚本](#rman备份脚本) <!-- /code_chunk_output --> ### 备份信息检查 #### RMAN 列出需要备份的信息 ``` sql --根据配置策略，列出需要备份的 rman target / report need backup; ``` #### RMAN命令跟踪 ``` #跟踪 rman target / debug trace=/tmp/rman.trc ``` #### 检查备份集信息 ``` sql --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; ``` #### 查找某个备份集中包含数据文件 ``` 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 c.set_stamp=&set_stamp ORDER BY C.FILE#; ``` #### 查询某个备份集中控制文件 ``` 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 C.SET_STAMP = &SET_STAMP; ``` #### 查看某个备份集中归档日志 ``` 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 B.SET_STAMP = &SET_STAMP ORDER BY THREAD#, SEQUENCE#; ``` #### 查看某个备份集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 B.SET_STAMP = &SET_STAMP; ``` #### 查看rman配置信息 ``` sql SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION; ``` ### 恢复前检查 ``` sql 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 ``` sql --列出需要修复的问题，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备份脚本 ``` sql --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; ```