> rman backup manage common sql -- by firsouler

- [備份資訊檢查](#備份資訊檢查)
  - [RMAN 列出需要備份的資訊](#rman-列出需要備份的資訊)
  - [RMAN命令跟蹤](#rman命令跟蹤)
  - [檢查備份集資訊](#檢查備份集資訊)
  - [檢視某個備份集中歸檔日誌](#檢視某個備份集中歸檔日誌)
  - [檢視某個備份集SPFILE](#檢視某個備份集spfile)
  - [檢視rman配置資訊](#檢視rman配置資訊)
- [恢復前檢查](#恢復前檢查)
- [Data Recovery Advisor](#data-recovery-advisor)
- [RMAN備份指令碼](#rman備份指令碼)

### 備份資訊檢查

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

【RMAN】Oracle rman 常用命令參考

相關文章