ArchiveMode 에서의 Backup & Recovery-RMAN03
RMAN
- RMAN의 추가적인 명령어들
- crosscheck 명령어
- rman에 등록된 정보와 OS 상에 실제 파일의 존재를 상호체크
- crosscheck archivelog all;
- 체크해서 fail 한 것을 'EXPIRED'상태로 만듬
- list expired archivelog all;
- 'EXPIRED' archivelog 목록 보기
- delete expired archivelog all;
- 'EXPIRED' 파일정보를 rman정보에서 삭제
- list expired archivelog all;
- 'EXPIRED' 가 지워졌는지 확인
- list archivelog all;
- archive 전체 목록 보기
- list expired backupset;
- delete expired backupset;
- 아카이브 로그 파일의 백업
- 벡업할 때 읽은 아카이브 파일 삭제
- backup as compressed backupset archivelog all delete input tag='archivelog backup';
- delete input : 실제로 읽은 파일만 삭제
- list backupset tag='archivelog backup';
RMAN> report obsolete redundancy 1;
-- report obsolete : configuration의 redundancy 의 정책을 따라서, 중복된 백업을 볼 수 있다.
RMAN> delete obsolete redundancy 1;
-- configuration의 redundancy 의 정책을 따라서, 중복된 백업을 삭제 한다.
-- 가장 최근 것만 남기고 백업셋들을 삭제한다.
RMAN> list backup of tablespace users;
-- 특정 대상의 백업을 확인
RMAN> backup as backupset
format '/home/oracle/backup/rman/df_%d_%s_%p.bus'
tablespace users;
-- 하나의 백업에 떨어지는 백업 피스가 여러개를 만든다.
-- format으로 경로와 이름을 지정한다.
[orcl2:rman]$ ls df*
df_ORCL2_19_1.bus
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 4.03M DISK 00:00:00 22-APR-19
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20190422T105909
Piece Name: /home/oracle/backup/rman/df_ORCL2_19_1.bus
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1235736 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
-- BS Key 와 BP Key가 같다 1:1로 백업이 진행되고 있다.
RMAN> BACKUP AS COPY tablespace users;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
output file name=/home/oracle/backup/rman/data_D-ORCL2_I-986068470_TS-USERS_FNO-4_0ntvkee9_20190422 tag=TAG20190422T110857 RECID=4 STAMP=1006254537
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190422-03 comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
-- 이미지 카피로 백업 (=cp)
-- 특정 테이블 스페이스 백업
================================================
RMAN> BACKUP AS COPY database; <== 데이터베이스 전체를 백업
RMAN> BACKUP AS COPY DATAFILE '/home/oracle/backup/rman/users_01_db01.dbf';
<== 데이터파일 백업
================================================
RMAN> show controlfile autobackup;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> list copy;
-- cp백업을 볼 수 있다.
RMAN 백업 유형
- 레벨0 Incremental backup
- 레벨 0으로 표시된 전체 백업
- Cumulative 레벨 1 Incremental backup
- 마지막 레벨 0 Incremental backup 이후 수정한 블록한 포함
- Differential 레벨 1 Incremental
- 마지막 Incremental backup 이후 수정한 블록만 포함
읽기 전용 테이블 스페이스 백업
- 단 한번만 백업을 수행한다.
- skip readonly를 이용하여 읽기 전용 테이블스페이스 또는 데이터파일을 건너 뛸 수 있다.
백업 관리 : Dynamin Performance View
- V$BACKUP_SET : 생성된 백업 셋
- V$BACKUP_PICE : 존재하는 백업 피스
- V$DATAFILE_COPY : 디스크의 데이터 파일 복사본
- V$BACKUP_FILES : 백업을 생성할 때 생성된 모든 파일에 대한 정보
filesperset
- bacupset 안에 포함할 수 있는 파일의 갯수를 지정
- default : 64
- 여기서 파일의 갯수는 백업대상이 구성된 파일의 갯수
maxpiece
- os상에 생성되는 파일의 갯수의 최대값을 설정한다.
참고 : data format
- %U : default
- %u_%p_%c (backup piece)
- data-D-%d_id-%I_TS-%N_FNO-%f_%u
- %u : 오라클 생성 8자 문자열
- %p : piece number within backup set
- %c : copy number of backup piece
- %d : database name
- %I : DBID
- %N : tablespace name
- %f : file number
- %u : 오라클 생성 8자 문자열 + 시간정보
- %T : YYYYMMDD
- %F : contolfile autobackup 시의 이름 지정
- c-(DBID)-YYYYMMDD-QQ
- YYYYMMDD는 백업 생성 날짜
- QQ는 00~FF인 16진수
백업실습
추가정보
@con
SYS@orcl2> select name from v$controlfile;
@log
select group#, sequence#, status, archived, members, first_change#, bytes/1024/1024 mb
from v$log;
@logfile
col status for a10
col member for a35
SELECT a.group# ,b.sequence#,a.member , b.bytes/1024/1024 MB , b.archived , b.status, b.first_change#
FROM v$logfile a , v$log b
WHERE a.group#=b.group#
ORDER BY 1;
@data
col name for a35
select name, status from v$datafile;
@datafile
col name1 for a15
col name2 for a35
select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
@switch
alter system switch logfile;
@check
alter system checkpoint;
@backup
col name for a30
col status for a15
select a.file#, a.name,a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
@recover_file
col name for a35
col error for a20
select r.file#, d.name, online_status, error, change#, time
from v$recover_file r, v$datafile d
where r.file# = d.file#;
@track
col filename format a50
select status,filename,bytes/1024/1024 mb from v$block_change_tracking;
--filesperset , maxpeice
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 277.67M DISK 00:00:33 22-APR-19
BP Key: 23 Status: AVAILABLE Compressed: YES Tag: TAG20190422T150818
Piece Name: /home/oracle/backup/rman/0vtvksf2_1_1_20190422
List of Datafiles in backup set 23
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1247606 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1247606 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1247606 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1247606 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1247606 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20190422T150853
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190422-05
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1247628 Ckp time: 22-APR-19
====================================================================================================
RMAN> backup database filesperset 2;
Starting backup at 22-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=143 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/11tvkt0u_1_1_20190422 tag=TAG20190422T151750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/12tvkt1d_1_1_20190422 tag=TAG20190422T151750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/13tvkt1s_1_1_20190422 tag=TAG20190422T151750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190422-06 comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
-- 1/2/2 로 생성된다.
-- 보기 편하게 아래의 포맷으로 backup을 하자.
====================================================================================================
RMAN> backup format '/home/oracle/backup/rman/df_%d_%s_%p.bus' database filesperset 2;
Starting backup at 22-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/df_ORCL2_39_1.bus tag=TAG20190422T152224 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/df_ORCL2_40_1.bus tag=TAG20190422T152224 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/df_ORCL2_41_1.bus tag=TAG20190422T152224 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190422-07 comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 587.34M DISK 00:00:15 22-APR-19
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20190422T152224
Piece Name: /home/oracle/backup/rman/df_ORCL2_39_1.bus
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1248643 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Full 396.88M DISK 00:00:10 22-APR-19
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20190422T152224
Piece Name: /home/oracle/backup/rman/df_ORCL2_40_1.bus
List of Datafiles in backup set 30
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1248658 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
4 Full 1248658 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 71.55M DISK 00:00:02 22-APR-19
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20190422T152224
Piece Name: /home/oracle/backup/rman/df_ORCL2_41_1.bus
List of Datafiles in backup set 31
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1248663 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
5 Full 1248663 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20190422T152307
Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-986068470-20190422-07
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1248670 Ckp time: 22-APR-19
RMAN> show all;
......
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
......
-- control file이 저장되는 위치가 현재 FRA가 아니다.
RMAN> configure controlfile autobackup format for device type disk clear;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
RMAN configuration parameters are successfully reset to default value
-- control file 의 autobackup 파일을 default 위치에 default 형식의 이름으로 만들어 달라는 뜻.
RMAN> show all;
......
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
......
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
RMAN> backup tablespace users;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/1btvku1e_1_1_20190422 tag=TAG20190422T153510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006270511_gctr4zs5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
-- control file의 저장 위치가 기본값(FRA)으로 변경되었음을 확인할 수 있다.
RMAN> backup format '/home/oracle/backup/rman/df_users_%s_%p.bus' tablespace users;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/df_users_45_1.bus tag=TAG20190422T153729 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006270650_gctr9bd9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
-- 백업의 위치와 이름을 포맷해서 작성할 수 있다.
RMAN> delete backupset 29, 30, 31;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
29 29 1 1 AVAILABLE DISK /home/oracle/backup/rman/df_ORCL2_39_1.bus
30 30 1 1 AVAILABLE DISK /home/oracle/backup/rman/df_ORCL2_40_1.bus
31 31 1 1 AVAILABLE DISK /home/oracle/backup/rman/df_ORCL2_41_1.bus
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/backup/rman/df_ORCL2_39_1.bus RECID=29 STAMP=1006269744
deleted backup piece
backup piece handle=/home/oracle/backup/rman/df_ORCL2_40_1.bus RECID=30 STAMP=1006269769
deleted backup piece
backup piece handle=/home/oracle/backup/rman/df_ORCL2_41_1.bus RECID=31 STAMP=1006269784
Deleted 3 objects
-- 원하는 backupset만 선택해서 삭제 가능
====================================================================================================
--MAXPIECESIZE 사용하여 piece 화일의 크기를 제한합니다.
--Default 로는 제한 없음
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 600M;
-- disk에다 쓸 프로세스를 할당하라는 뜻
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/oracle/backup/rman/%U_%T';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 600 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
CHANNEL : os에서 작업을 하는 프로세스(os 프로세스)
RMAN> run {
allocate channel c1 device type disk maxpiecesize 100m;
backup tablespace system;
}
-- allocate는 수동 할당을 진행.
-- os 작업을 할수 잇는 프로세스를 할당하는데 한조각에 100m씩만 써라라는 말
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=18 device type=DISK
Starting backup at 22-APR-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
channel c1: starting piece 1 at 22-APR-19
channel c1: finished piece 1 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttdy6q_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: starting piece 2 at 22-APR-19
channel c1: finished piece 2 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf16x_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: starting piece 3 at 22-APR-19
channel c1: finished piece 3 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf475_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: starting piece 4 at 22-APR-19
channel c1: finished piece 4 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf77d_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: starting piece 5 at 22-APR-19
channel c1: finished piece 5 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttfb7q_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: starting piece 6 at 22-APR-19
channel c1: finished piece 6 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttff7w_.bkp tag=TAG20190422T161334 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:18
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006272832_gcttfjcd_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
released channel: c1
RMAN> list backup of tablespace system;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 587.46M DISK 00:00:17 22-APR-19
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251433 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
Backup Set Copy #1 of backup set 43
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:17 22-APR-19 NO TAG20190422T161334
List of Backup Pieces for backup set 43 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
43 1 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttdy6q_.bkp
44 2 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf16x_.bkp
45 3 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf475_.bkp
46 4 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttf77d_.bkp
47 5 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttfb7q_.bkp
48 6 AVAILABLE /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161334_gcttff7w_.bkp
RMAN> delete backup;
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup as compressed backupset database;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161619_gcttl3x0_.bkp tag=TAG20190422T161619 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273024_gcttmk1z_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
-- autobackup +FRA
1. 현재 RMAN 설정을 확인한다.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
...........
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
...........
-- #defailt로 설정되어있어야 가능, 안되어있을경우
2. CONTROLFILE AUTOBACKUP ON 으로 설정하고 autobackup의 위치를 default로 설정한다.
-- RMAN> configure controlfile autobackup format for device type disk clear; 명령어 실행
3. 현재의 retention 정책에 맞지 않는 백업을 삭제하고 컨트롤 파일을 백업한다.
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN> backup current controlfile;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_ncnnf_TAG20190422T162316_gcttz5yp_.bkp tag=TAG20190422T162316 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273399_gcttz72q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
RMAN> list backup;
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 277.79M DISK 00:00:36 22-APR-19
BP Key: 50 Status: AVAILABLE Compressed: YES Tag: TAG20190422T161619
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161619_gcttl3x0_.bkp
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Full 9.73M DISK 00:00:01 22-APR-19
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20190422T161704
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273024_gcttmk1z_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251650 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 9.70M DISK 00:00:02 22-APR-19
BP Key: 52 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162316
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_ncnnf_TAG20190422T162316_gcttz5yp_.bkp
Control File Included: Ckp SCN: 1251854 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162319
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273399_gcttz72q_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251866 Ckp time: 22-APR-19
4. 전체 database 백업도 수행하고 컨트롤 파일 autobackup을 확인한다. 위치는 어디에 생성되는가?
RMAN> backup as compressed backupset database;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T162611_gctv4mv9_.bkp tag=TAG20190422T162611 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 22-APR-19
Starting Control File and SPFILE Autobackup at 22-APR-19
piece handle=/u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 277.79M DISK 00:00:36 22-APR-19
BP Key: 50 Status: AVAILABLE Compressed: YES Tag: TAG20190422T161619
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161619_gcttl3x0_.bkp
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Full 9.73M DISK 00:00:01 22-APR-19
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20190422T161704
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273024_gcttmk1z_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251650 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 9.70M DISK 00:00:02 22-APR-19
BP Key: 52 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162316
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_ncnnf_TAG20190422T162316_gcttz5yp_.bkp
Control File Included: Ckp SCN: 1251854 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162319
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273399_gcttz72q_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251866 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
49 Full 277.81M DISK 00:00:36 22-APR-19
BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20190422T162611
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T162611_gctv4mv9_.bkp
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.73M DISK 00:00:01 22-APR-19
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162656
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1252023 Ckp time: 22-APR-19
5. 컨트롤 파일을 삭제하여 장애 일으킨다.
SYS@orcl2> create table hr.t1 (a number);
Table created.
SYS@orcl2> insert into hr.t1 values (1);
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select count(*) from hr.t1;
COUNT(*)
----------
1
SYS@orcl2> @logfile;
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 19 /u01/app/oracle/orcl2/redo01.log 50 YES INACTIVE
2 20 /u01/app/oracle/orcl2/redo02.log 50 NO CURRENT
3 18 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
[orcl2:~]$ rm /u01/app/oracle/orcl2/control01.ctl
SYS@orcl2> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SYS@orcl2> shutdown abort;
ORACLE instance shut down.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
ORA-00205: error in identifying control file, check alert log for more info
=================================================================
alert.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Mon Apr 22 16:29:59 2019
Checker run found 1 new persistent data failures
=================================================================
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 22 16:33:20 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (not mounted)
6. RMAN SESSION 에서 복구
RMAN> restore controlfile from autobackup;
Starting restore at 22-APR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL2
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
-- DBID는 MOUNT가 안되었기 때문에 알수가 없으므로, AUTOBACKUP밑에서 찾았다는 말로 이해하자.
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/orcl2/control01.ctl
-- show parameter control_files와 동일
Finished restore at 22-APR-19
-- tartget은 parameter file이 알려준다. 소스를 읽어서 복원을 진행해야 하는데, 여기서 소스는 autobackup에서 읽는다. 그리고 autobackup은 flash_recovery_area에서 자동으로 찾는다.
-- restore는 A(source:backup되어있는 파일)를 B(target:controlfile)의 위치, 즉 instance의 parameter(show parameter control_files)의 value값 위치에 넣어준다.
-- $ORACLE_HOME/dbs/spfileorcl2.ora에 parameter값이 저장되어있다.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
-- 만들어진 controlfile은 bakcup controlfile이므로, 데이터베이스를 복구하여야한다.
RMAN> recover database;
Starting recover at 22-APR-19
Starting implicit crosscheck backup at 22-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 22-APR-19
Starting implicit crosscheck copy at 22-APR-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-APR-19
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/o1_mf_1_17_gbxn1vof_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2019_04_11/o1_mf_1_18_gbxn8k9s_.arc
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/orcl2/redo02.log
archived log file name=/u01/app/oracle/orcl2/redo02.log thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-APR-19
-- RMAN에서 controlfile을 restore을 하면 using backup controlfile이라는 것을 알고있다. 그러므로 따로 문장을 추가하지 않아도 된다.
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/22/2019 16:42:47
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
-- backup controlfile 을 이용한 복구이므로 에러 발생 (로그가 다르므로 실행 불가).
RMAN> alter database open resetlogs;
database opened
7. 복구 확인
SYS@orcl2> select * from hr.t1;
A
----------
1
8. resetlogs로 open 했으니 백업 받는다.
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
5 5 ORCL2 986068470 ORPHAN 754488 10-APR-19
1 1 ORCL2 986068470 PARENT 1114320 17-APR-19
2 2 ORCL2 986068470 PARENT 1137755 17-APR-19
3 3 ORCL2 986068470 PARENT 1138778 17-APR-19
4 4 ORCL2 986068470 PARENT 1140797 19-APR-19
6 6 ORCL2 986068470 CURRENT 1252155 22-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 277.79M DISK 00:00:36 22-APR-19
BP Key: 50 Status: AVAILABLE Compressed: YES Tag: TAG20190422T161619
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T161619_gcttl3x0_.bkp
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1251629 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Full 9.73M DISK 00:00:01 22-APR-19
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20190422T161704
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273024_gcttmk1z_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251650 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 9.70M DISK 00:00:02 22-APR-19
BP Key: 52 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162316
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_ncnnf_TAG20190422T162316_gcttz5yp_.bkp
Control File Included: Ckp SCN: 1251854 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 53 Status: AVAILABLE Compressed: NO Tag: TAG20190422T162319
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273399_gcttz72q_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1251866 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
49 Full 277.81M DISK 00:00:36 22-APR-19
BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20190422T162611
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T162611_gctv4mv9_.bkp
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1251957 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 9.73M DISK 00:00:00 22-APR-19
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20190422T164325
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006274605_gctw4x5p_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1252470 Ckp time: 22-APR-19
RMAN> delete backup;
YES
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup as compressed backupset database;
-- NO AUTOBACKUP + NO FRA
1. 현재 RMAN 설정을 확인한다.
1) DBA SESSION에서 현재 DB 상태 확인
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS
---------- ---------- ----------------------------------- ---------- --- ----------------
1 4 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT
2 2 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE
3 3 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1273326
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1273326
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1273326
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1273326
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1273326
SYS@orcl2> select sequence#, name from v$archived_log order by 1;
2) RMAN SESSION에서 정보확인
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** /u01/app/oracle/orcl2/system01.dbf
2 520 SYSAUX *** /u01/app/oracle/orcl2/sysaux01.dbf
3 105 UNDOTBS1 *** /u01/app/oracle/orcl2/undotbs01.dbf
4 6 USERS *** /u01/app/oracle/orcl2/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/orcl2/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2 20 TEMP 32767 /u01/app/oracle/orcl2/temp01.dbf
3. 현재 RMAN 설정을 확인한다.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
......
CONFIGURE CONTROLFILE AUTOBACKUP ON;
......
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/rman/%F';
RMAN configuration parameters are successfully reset to default value
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL2 are:
......
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
......
RMAN> backup as compressed backupset format '/home/oracle/backup/rman/%U_%T' database include current controlfile;
Starting backup at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/orcl2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/orcl2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/orcl2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/orcl2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/orcl2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/21tvl38v_1_1_20190422 tag=TAG20190422T170431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22-APR-19
channel ORA_DISK_1: finished piece 1 at 22-APR-19
piece handle=/home/oracle/backup/rman/22tvl3ac_1_1_20190422 tag=TAG20190422T170431 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-APR-19
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 280.79M DISK 00:00:37 22-APR-19
BP Key: 56 Status: AVAILABLE Compressed: YES Tag: TAG20190422T164801
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/backupset/2019_04_22/o1_mf_nnndf_TAG20190422T164801_gctwfkqb_.bkp
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1273015 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1273015 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1273015 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1273015 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1273015 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
52 Full 9.73M DISK 00:00:01 22-APR-19
BP Key: 57 Status: AVAILABLE Compressed: NO Tag: TAG20190422T164846
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006274926_gctwgywk_.bkp
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1273041 Ckp time: 22-APR-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 280.85M DISK 00:00:36 22-APR-19
BP Key: 58 Status: AVAILABLE Compressed: YES Tag: TAG20190422T170431
Piece Name: /home/oracle/backup/rman/21tvl38v_1_1_20190422
List of Datafiles in backup set 53
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1273949 22-APR-19 /u01/app/oracle/orcl2/system01.dbf
2 Full 1273949 22-APR-19 /u01/app/oracle/orcl2/sysaux01.dbf
3 Full 1273949 22-APR-19 /u01/app/oracle/orcl2/undotbs01.dbf
4 Full 1273949 22-APR-19 /u01/app/oracle/orcl2/users01.dbf
5 Full 1273949 22-APR-19 /u01/app/oracle/orcl2/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
54 Full 1.06M DISK 00:00:01 22-APR-19
BP Key: 59 Status: AVAILABLE Compressed: YES Tag: TAG20190422T170431
Piece Name: /home/oracle/backup/rman/22tvl3ac_1_1_20190422
SPFILE Included: Modification time: 22-APR-19
SPFILE db_unique_name: ORCL2
Control File Included: Ckp SCN: 1274011 Ckp time: 22-APR-19
4. DBA SESSION 에서 controlfile 장애 발생
SYS@orcl2> select dbid from v$database;
DBID
----------
986068470
SYS@orcl2> insert into hr.t1 values (1);
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> Select * from hr.t1;
A
----------
1
1
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
[orcl2:~]$ rm /u01/app/oracle/orcl2/control01.ctl
[orcl2:~]$ ls /u01/app/oracle/orcl2/control01.ctl
ls: /u01/app/oracle/orcl2/control01.ctl: No such file or directory
SYS@orcl2> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/orcl2/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> shutdown abort
ORACLE instance shut down.
5. RMAN SESSION 에서 복구
[orcl2:~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 22 17:14:11 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
RMAN> list backup;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 04/22/2019 17:14:37
ORA-01507: database not mounted
-- 백업에 대한 정보가 control file에 저장되어있기 때문에, backup 목록이 나오지 않는다.
-- control file은 mount단계에서 읽어진다.
RMAN> RESTORE CONTROLFILE;
Starting restore at 22-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/22/2019 17:14:43
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP
RMAN> restore controlfile from '/home/oracle/backup/rman/22tvl3ac_1_1_20190422';
Starting restore at 22-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/orcl2/control01.ctl
Finished restore at 22-APR-19
-- 파일명을 모를경우 해당 경로에 들어가서 가장 최근의 backupset을 찾아야한다.
-- data formate의 필요성.
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> recover database;
Starting recover at 22-APR-19
Starting implicit crosscheck backup at 22-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 22-APR-19
Starting implicit crosscheck copy at 22-APR-19
using channel ORA_DISK_1
Finished implicit crosscheck copy at 22-APR-19
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL2/autobackup/2019_04_22/o1_mf_s_1006273616_gctv6116_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/orcl2/redo02.log
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/orcl2/redo03.log
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/orcl2/redo01.log
archived log file name=/home/oracle/arch2/arch_1_4_1006274601.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/orcl2/redo02.log thread=1 sequence=5
archived log file name=/u01/app/oracle/orcl2/redo03.log thread=1 sequence=6
archived log file name=/u01/app/oracle/orcl2/redo01.log thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 22-APR-19
RMAN> alter database open resetlogs;
database opened
====================================================================================================
-- 여러 DB가 하나의 FRA(ASM DISK)에 저장되어있을 경우 DBID를 알아야 한다.
-- NOMOUNT 단계에서 실행한다.
RMAN> set dbid = 986068470
====================================================================================================
6. 복구 확인
SYS@orcl2> select * from hr.t1;
A
----------
1
1
7. resetlogs로 open 했으니 백업 받는다.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> delete backup;
YES
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup as compressed backupset database;
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN04 (0) | 2019.05.03 |
|---|---|
| [Oracle DBA]Clone DB (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-TEMP,UNDO (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN02 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN01 (0) | 2019.04.22 |