설명

  • 백업/복구를 관리자를 대신하여 대상 서버의 서버 프로세스에게 백업을 수행
  • 백업/복구 관련 정보는 Recovery catalog server가 있을경우 recovery catalog에
    없을 경우 백업서버의 controlfile에 저장
  • 백업 불가 대상 : online redologfile, init.ora, password file, listener.ora, tnsnames.ora
  • 단어 설명
    • 서버세션 : RMAN에 의해 호출된 서버 프로세스/스레드는 대상 데이터베이스에 접속되어 백업/복구 수행
    • 채널 : 백업/복구 작업을 수행 및 기록하기위해 필요한 대상 데이터베이스 링크
  • 하기 예시(테스트)는 단일 서버내 다중 인스턴스 구성을 통해 진행

 

 

 

recovery catalog server 구성

recovery catalog db 생성

스크립트

  • 테이블스페이스 및 계정 생성 (in rcServer)
create tablespace rv_tbs01 datafile '/data/temp/rc_tbs01.dbf' size 10M autoextend on;
create user rcuser identified by rcuser default tablespace rv_tbs01 temporary tablespace temp2;
grant connect,resource,recovery_catalog_owner to rcuser;
  • Client -> Server 연결 구성 및 db등록 
    • $TNS_ADMIN/tnsnames.ora 내 rcserver 등록 (in rcClient)
    • $TNS_ADMIN/listner.ora 내 rcserver 등록 (in rcServer)
    • recovery catalog 서버 접속 (in rcClient)
rman target / catalog rcuser/rcuser@rcserver
  • catalog 생성 및 database등록 (in RMAN) 
create catalog tablespace rv_tbs01;
register database;
  • 등록된 database 조회 (in rcServer /rcuser)
select * from rc_database;
select db_name,tablespace_name,name "file_name" from rc_datafile;

 

예시 

  • 더보기
    --1)테이블스페이스 및 계정 생성
    [rcServer]
    [oracle@rcServer~] export ORACLE_SID=rcServer
    [oracle@rcServer~] sqlplus /as sysdba;
    SQL> create tablespace rv_tbs01 datafile '/data/temp/rc_tbs01.dbf' size 10M autoextend on;
    SQL> create user rcuser identified by rcuser default tablespace rv_tbs01 temporary tablespace temp2;
    SQL> grant connect,resource,recovery_catalog_owner to rcuser;
     
    --2) Client -> Server 연결 구성 및 db등록
    [rcClient]
    [oracle@rcclient~]$ vi $TNS_ADMIN/tnsnames.ora
               rcserver=
               (DESCRIPTION =
               (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.109.133)(PORT = 1521))
               (CONNECT_DATA =
               (SERVER=DEDICATED)
               (SERVICE_NAME = rcServer)
               (SID=rcServer)
               )
               )
    :wq!
     
    [oracle@rcclient~]$ rman target / catalog rcuser/rcuser@rcserver
               Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 15 22:23:07 2022 
               Copyright (c) 1982, 2007, Oracle. All rights reserved. 
               connected to target database: EHR (DBID=4096122365)
               connected to recovery catalog database
     
    RMAN> create catalog tablespace rv_tbs01; 
               recovery catalog created 
    RMAN> register database;
               database registered in recovery catalog
               starting full resync of recovery catalog
               full resync complete
     
    [rcServer]
    [oracle@rcserver~]sqlplus rcuser/rcuser
    SQL> select * from rc_database; 
               DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS_TI
               ---------- ---------- ---------- -------- ----------------- ------------
               1 2 4096122365 EHR 1 15-MAY-21 
    SQL> select db_name,tablespace_name,name "file_name" from rc_datafile;
               DB_NAME TABLESPACE file_name
               -------- ---------- --------------------------------------------------
               EHR SYSTEM /oradata/EHR/system01.dbf
               EHR SYSAUX /oradata/EHR/sysaux01.dbf
               EHR USERS /oradata/EHR/users01.dbf
               EHR UNDO01 /oradata/EHR/undo01.dbf
               EHR HAKSA /oradata/EHR/haksa01.dbf
               EHR TEST01 /oradata/EHR/test01.dbf
     
    --3) 작동 테스트 (백업 -> 모든 controlfile 삭제 --> 복원)
    [rcClient]
    [oracle@rcclient~]$ rman target / catalog rcuser/rcuser@rcserver
               Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 15 22:23:07 2022 
               Copyright (c) 1982, 2007, Oracle. All rights reserved. 
               connected to target database: EHR (DBID=4096122365)
               connected to recovery catalog database
     
    RMAN> backup database;
               Starting backup at 15-MAR-22
               allocated channel: ORA_DISK_1
               channel ORA_DISK_1: sid=151 devtype=DISK
               channel ORA_DISK_1: starting full datafile backupset
               channel ORA_DISK_1: specifying datafile(s) in backupset
               input datafile fno=00001 name=/oradata/EHR/system01.dbf
               input datafile fno=00003 name=/oradata/EHR/sysaux01.dbf
               input datafile fno=00002 name=/oradata/EHR/test01.dbf
               input datafile fno=00007 name=/oradata/EHR/undo01.dbf
               input datafile fno=00004 name=/oradata/EHR/users01.dbf
               input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
               channel ORA_DISK_1: starting piece 1 at 15-MAR-22
               channel ORA_DISK_1: finished piece 1 at 15-MAR-22
               piece                       handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15/.. tag=TAG20220315T225144 comment=NONE
               channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
               channel ORA_DISK_1: starting full datafile backupset
               channel ORA_DISK_1: specifying datafile(s) in backupset
               including current control file in backupset
               channel ORA_DISK_1: starting piece 1 at 15-MAR-22
               channel ORA_DISK_1: finished piece 1 at 15-MAR-22
               piece            handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
               channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
               Finished backup at 15-MAR-22
     
     
    [oracle@rcclient~]$ sqlplus / as sysdba;
    SQL> !rm -fr /oradata/EHR/control*.ctl
    SQL> shutdown abort;
               ORACLE instance shut down.
    SQL> startup
               ORACLE instance started.
               ...중략...
               ORA-00205: error in identifying control file, check alert log for more info
    SQL> quit
    [oracle@rcclient~]$ rman target / catalog rcuser/rcuser@rcserver
               Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 15 23:01:37 2022
               Copyright (c) 1982, 2007, Oracle. All rights reserved.
               connected to target database: EHR (not mounted)
               connected to recovery catalog database
     
    RMAN> restore controlfile;
               Starting restore at 15-MAR-22
               allocated channel: ORA_DISK_1
               channel ORA_DISK_1: sid=156 devtype=DISK
     
               channel ORA_DISK_1: starting datafile backupset restore
               channel ORA_DISK_1: restoring control file
               channel ORA_DISK_1: reading from backup piece            /app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
               channel ORA_DISK_1: restored backup piece 1
               piece            handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
               channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
               output filename=/oradata/EHR/control01.ctl
               output filename=/oradata/EHR/control02.ctl
               output filename=/oradata/EHR/control03.ctl
               Finished restore at 15-MAR-22
     
    RMAN> alter database mount;
               database mounted
               released channel: ORA_DISK_1
     
    RMAN> alter database open resetlogs;
               RMAN-00571: ============================================
               RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
               RMAN-00571: ===========================================
               RMAN-03002: failure of alter db command at 03/15/2022 23:02:45
               ORA-01194: file 1 needs more recovery to be consistent
               ORA-01110: data file 1: '/oradata/EHR/system01.dbf'
     
    RMAN> recover database;
               Starting recover at 15-MAR-22
               Starting implicit crosscheck backup at 15-MAR-22
               allocated channel: ORA_DISK_1
               channel ORA_DISK_1: sid=156 devtype=DISK
               Crosschecked 1 objects
               Finished implicit crosscheck backup at 15-MAR-22 
               Starting implicit crosscheck copy at 15-MAR-22
               using channel ORA_DISK_1
               Crosschecked 1 objects
               Finished implicit crosscheck copy at 15-MAR-22
     
               searching for all files in the recovery area
               cataloging files...
               cataloging done
     
               List of Cataloged Files
               =======================
               File Name:            /app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
     
               using channel ORA_DISK_1
     
               starting media recovery
     
               archive log thread 1 sequence 57 is already on disk as file /oradata/EHR/redo03_a.log
               archive log filename=/oradata/EHR/redo03_a.log thread=1 sequence=57
               media recovery complete, elapsed time: 00:00:00
               Finished recover at 15-MAR-22
     
    RMAN> alter database open resetlogs;
               database opened
               new incarnation of database registered in recovery catalog
               starting full resync of recovery catalog
               full resync complete

Recovery Catalog DB 관리

스크립트

  • crosscheck : target과 recovery catalog간 정보 동기화
crosscheck backupset;
  • backupset 조회
list backupset;
  •  DELETE
delete backupset 102;
  • catalog (수동백업받은 파일을 RMAN catalog추가/삭제)
catalog datafilecopy '/data/backup/open/haksa01.dbf';
list copy;
change datafilecopy '/data/backup/open/haksa01.dbf' uncatalog;

예시

  • 더보기
    --1)crosscheck : target과 recovery catalog간 정보 동기화
    RMAN> crosscheck backupset; 
              using channel ORA_DISK_1
              crosschecked backup piece: found to be 'AVAILABLE'
              backup piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
              crosschecked backup piece: found to be 'AVAILABLE'
              backup piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15..
              Crosschecked 2 objects
     
    --2)DELETE
    RMAN> list backupset;
              List of Backup Sets
              ===================  
              BS Key Type LV Size Device Type Elapsed Time Completion Time
              ------- ---- -- ---------- ----------- ------------ ---------------
              101 Full 494.37M DISK 00:00:48 15-MAR-22
              BP Key: 103 Status: AVAILABLE Compressed: NO Tag: TAG20220315T225144
              Piece Name:           /app/oracle/flash_recovery_area/EHR/backupset/2022_03_15/..
              List of Datafiles in backup set 101
              File LV Type Ckp SCN Ckp Time Name
              ---- -- ---- ---------- --------- ----
              1 Full 614331 15-MAR-22 /oradata/EHR/system01.dbf
              2 Full 614331 15-MAR-22 /oradata/EHR/test01.dbf
              3 Full 614331 15-MAR-22 /oradata/EHR/sysaux01.dbf
              4 Full 614331 15-MAR-22 /oradata/EHR/users01.dbf
              6 Full 614331 15-MAR-22 /oradata/EHR/haksa01.dbf
              7 Full 614331 15-MAR-22 /oradata/EHR/undo01.dbf
     
              BS Key Type LV Size Device Type Elapsed Time Completion Time
              ------- ---- -- ---------- ----------- ------------ ---------------
              102 Full 6.77M DISK 00:00:01 15-MAR-22
              BP Key: 129 Status: AVAILABLE Compressed: NO Tag: TAG20220315T225144
              Piece Name:           /app/oracle/flash_recovery_area/EHR/backupset/2022_03_15/...
              Control File Included: Ckp SCN: 614349 Ckp time: 15-MAR-22
     
    RMAN> delete backupset 102;
              using channel ORA_DISK_1
     
              List of Backup Pieces
              BP Key BS Key Pc# Cp# Status Device Type Piece Name
              ------- ------- --- --- ----------- ----------- ----------
              129 102 1 1 AVAILABLE DISK           /app/oracle/flash_recovery_area/EHR/backupset/2022_03_15/..
     
    Do you really want to delete the above objects (enter YES or NO)? yes
              deleted backup piece
              backup piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_15/...
              Deleted 1 objects
     
    --3)catalog (수동백업받은 파일을 RMAN catalog추가 및 관리)
    [oracle@rcclient~]$ sqlplus / as sysdba;
    SQL> alter tablespace haksa begin backup;
              Tablespace altered.
    SQL> !cp /oradata/EHR/haksa01.dbf /data/backup/open/
    SQL> alter tablespace haksa end backup;
              Tablespace altered.
    SQL> quit
    [oracle@rcclient~]$ rman target / catalog rcuser/rcuser@rcserver
     
    RMAN> catalog datafilecopy '/data/backup/open/haksa01.dbf';
              cataloged datafile copy
              datafile copy filename=/data/backup/open/haksa01.dbf recid=3 stamp=1099439133
    RMAN> list copy;
              List of Datafile Copies
              Key File S Completion Time Ckp SCN Ckp Time Name
              ------- ---- - --------------- ---------- --------------- ----
              256 6 A 15-MAR-22 615915 15-MAR-22 /data/backup/open/haksa01.dbf
     
    RMAN> change datafilecopy '/data/backup/open/haksa01.dbf' uncatalog;
              uncataloged datafile copy
              datafile copy filename=/data/backup/open/haksa01.dbf recid=3 stamp=1099439133
              Uncataloged 1 objects

 

초기환경설정

  • 백업파일 보존기간 설정
    • CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
    • 만료 파일 일괄 정리시
DELETE OBSOLETE;
  • 백업본의 개수 지정 (Redundancy만큼 다중화하여 생성)
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
  • 백업 수행 프로세스의 병렬처리 수준 설정
CONFIGURE DEVICE TYPE disk parallelism 2;
  • 타 파일 백업시 컨트롤파일 자동 백업 설정
configure controlfile autobackup 
format for device type disk to '/data/backup/rman/cf_%F';
  • 단일 백업 파일의 최대 크기 지정
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 1G;
  • 설정내역확인
show all;

 

Channel 할당하기

자동채널할당

스크립트

  • parameterfile 내 db_recovery_file_dest로 설정시
configure default device type to disk;
  •  별도 경로 설정시 
configure channel device type disk   
format '/data/backup/rman/%U';

예시 

  • 더보기
    --parameterfile 내 db_recovery_file_dest로 설정시
    RMAN> configure default device type to disk;
              new RMAN configuration parameters:
              CONFIGURE DEFAULT DEVICE TYPE TO DISK;
              new RMAN configuration parameters are successfully stored
              starting full resync of recovery catalog
              full resync complete
     
    --별도 경로 설정시
    RMAN> configure channel device type disk
              format '/data/backup/rman/%U';
              new RMAN configuration parameters:
              CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/data/backup/rman/%U';
              new RMAN configuration parameters are successfully stored
              starting full resync of recovery catalog
              full resync complete
     
    RMAN> backup tablespace haksa;
              Starting backup at 16-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=143 devtype=DISK
              channel ORA_DISK_1: starting full datafile backupset
              channel ORA_DISK_1: specifying datafile(s) in backupset
              input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
              channel ORA_DISK_1: starting piece 1 at 16-MAR-22
              channel ORA_DISK_1: finished piece 1 at 16-MAR-22
              piece handle=/data/backup/rman/040oijjm_1_1 tag=TAG20220316T213238 comment=NONE
              channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
              Finished backup at 16-MAR-22
    RMAN> quit
              Recovery Manager complete.
    [oracle@rcclient~]$ ll
              -rw-r----- 1 oracle dba 163840 2022-03-16 21:32 040oijjm_1_1

수동채널할당 (작업형명령방법으로 테스트)

스크립트

run {
allocate channel c1 type disk
format '/data/backup/close/%U';
backup tablespace haksa;
}

 

예시 

  • 더보기
    RMAN> run {
    allocate channel c1 type disk
    format '/data/backup/close/%U';
    backup tablespace haksa;
    }
              allocated channel: c1
              channel c1: sid=156 devtype=DISK
     
              Starting backup at 16-MAR-22
              channel c1: starting full datafile backupset
              channel c1: specifying datafile(s) in backupset
              input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
              channel c1: starting piece 1 at 16-MAR-22
              channel c1: finished piece 1 at 16-MAR-22
              piece handle=/data/backup/close/050oijpp_1_1 tag=TAG20220316T213552 comment=NONE
              channel c1: backup set complete, elapsed time: 00:00:01
              Finished backup at 16-MAR-22
              released channel: c1
     
    RMAN> quit
              Recovery Manager complete.
    [oracle@rcclient~]$ ll
              -rw-r----- 1 oracle dba 163840 2022-03-16 21:35 050oijpp_1_1

 

RMAN 백업

전체백업

스크립트

run {
allocate channel c1 type disk maxpiecesize 10M;
allocate channel c2 type disk maxpiecesize 50M;
backup
tablespace haksa channel c1
format '/data/backup/close/%U'
tablespace test01 channel c2
format '/data/backup/rman/%U';
}

예시

  • 더보기
    RMAN> run {
    allocate channel c1 type disk maxpiecesize 10M;
    allocate channel c2 type disk maxpiecesize 50M;
    backup
    tablespace haksa channel c1
    format '/data/backup/close/%U'
    tablespace test01 channel c2
    format '/data/backup/rman/%U';
    } 
              allocated channel: c1
              channel c1: sid=145 devtype=DISK
     
              RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
              RMAN-06909: WARNING: parallelism require Enterprise Edition
              allocated channel: c2
              channel c2: sid=144 devtype=DISK
     
              Starting backup at 16-MAR-22
              channel c1: starting full datafile backupset
              channel c1: specifying datafile(s) in backupset
              input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
              channel c1: starting piece 1 at 16-MAR-22
              channel c2: starting full datafile backupset
              channel c2: specifying datafile(s) in backupset
              input datafile fno=00002 name=/oradata/EHR/test01.dbf
              channel c2: starting piece 1 at 16-MAR-22
              channel c1: finished piece 1 at 16-MAR-22
              piece handle=/data/backup/close/060oin4g_1_1 tag=TAG20220316T223247 comment=NONE
              channel c1: backup set complete, elapsed time: 00:00:01
              channel c2: finished piece 1 at 16-MAR-22
              piece handle=/data/backup/rman/070oin4g_1_1 tag=TAG20220316T223247 comment=NONE
              channel c2: backup set complete, elapsed time: 00:00:01
              Finished backup at 16-MAR-22
              released channel: c1
              released channel: c2

증분백업

설명

  • 차등증분백업 : 설정한 incremental level이 더 작거나 같은 날부터 현재까지 변경된 내용 모두 백업
  • 누적증분백업 : 설정한 incremental level이 더 작은 날 (같은 X)부터 현재까지 변경된 내용 모두 백업
  • block change tracking 기능 : 변경된 블록만 추적하는 기능으로 증분백업 속도 향상 (Ent. Edit. 가능)
    • block change tracking활성화
      • alter database enable block change tracking
        using file '/data/backup/rman/block_tracking.txt';
    • 체크
      • select status,filename,bytes/1024/1024 MB from v$block_change_tracking;

예시 

  • 더보기
    --1.1 level0 전체 데이터베이스백업
    RMAN> run {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    backup
    incremental level 0
    database
    format '/data/backup/rman/%U';
    }
     
              Starting backup at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=145 devtype=DISK
              channel ORA_DISK_1: starting incremental level 0 datafile backupset
              channel ORA_DISK_1: specifying datafile(s) in backupset
              input datafile fno=00001 name=/oradata/EHR/system01.dbf
              input datafile fno=00003 name=/oradata/EHR/sysaux01.dbf
              input datafile fno=00002 name=/oradata/EHR/test01.dbf
              input datafile fno=00007 name=/oradata/EHR/undo01.dbf
              input datafile fno=00004 name=/oradata/EHR/users01.dbf
              input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
              channel ORA_DISK_1: starting piece 1 at 20-MAR-22
              channel ORA_DISK_1: finished piece 1 at 20-MAR-22
              piece handle=/data/backup/rman/0e0ot1ut_1_1 tag=TAG20220320T203853 comment=NONE
              channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
              channel ORA_DISK_1: starting incremental level 0 datafile backupset
              channel ORA_DISK_1: specifying datafile(s) in backupset
              including current control file in backupset
              channel ORA_DISK_1: starting piece 1 at 20-MAR-22
              channel ORA_DISK_1: finished piece 1 at 20-MAR-22
              piece handle=/data/backup/rman/0f0ot1vd_1_1 tag=TAG20220320T203853 comment=NONE
              channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
              Finished backup at 20-MAR-22
     
     
    --1.2 lev3 특정(TEST01) tablespace만 차등증분백업
    RMAN> run {
    allocate channel c1 type disk;
    backup
    incremental level 3
    tablespace TEST01;
    }
     
              released channel: ORA_DISK_1
              allocated channel: c1
              channel c1: sid=145 devtype=DISK
     
              Starting backup at 20-MAR-22
              channel c1: starting incremental level 3 datafile backupset
              channel c1: specifying datafile(s) in backupset
              input datafile fno=00002 name=/oradata/EHR/test01.dbf
              channel c1: starting piece 1 at 20-MAR-22
              channel c1: finished piece 1 at 20-MAR-22
              piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_...bkp
              tag=TAG20220320T203921 comment=NONE
              channel c1: backup set complete, elapsed time: 00:00:01
              Finished backup at 20-MAR-22
              released channel: c1
     
     
    --1.3 lev3 특정(HAKSA) tablespace만 누적증분백업
    RMAN> run {
    allocate channel c1 type disk;
    backup
    incremental level 3 cumulative
    tablespace HAKSA;
    }
     
              Starting backup at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=145 devtype=DISK
              channel ORA_DISK_1: starting incremental level 3 datafile backupset
              channel ORA_DISK_1: specifying datafile(s) in backupset
              input datafile fno=00006 name=/oradata/EHR/haksa01.dbf
              channel ORA_DISK_1: starting piece 1 at 20-MAR-22
              channel ORA_DISK_1: finished piece 1 at 20-MAR-22
              piece handle=/data/backup/rman/0h0ot24o_1_1 tag=TAG20220320T204200 comment=NONE
              channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
              Finished backup at 20-MAR-22

RMAN 복구

데이터파일 복구

스크립트

run {
sql 'alter tablespace TEST01 offline immediate';
restore tablespace TEST01;
recover tablespace TEST01;
sql 'alter tablespace TEST01 online';
}

예시

  • 더보기
    RMAN> run {
    sql 'alter tablespace TEST01 offline immediate';
    restore tablespace TEST01;
    recover tablespace TEST01;
    sql 'alter tablespace TEST01 online';
    }
     
              sql statement: alter tablespace TEST01 offline immediate
     
              Starting restore at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=157 devtype=DISK
     
              channel ORA_DISK_1: starting datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              restoring datafile 00002 to /oradata/EHR/test01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0e0ot1ut_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0e0ot1ut_1_1 tag=TAG20220320T203853
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
              Finished restore at 20-MAR-22
     
              Starting recover at 20-MAR-22
              using channel ORA_DISK_1
              channel ORA_DISK_1: starting incremental datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              destination for restore of datafile 00002: /oradata/EHR/test01.dbf
              channel ORA_DISK_1: reading from backup piece      
               /app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_..3_.bkp 
              channel ORA_DISK_1: restored backup piece 1
              piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_..3_.bkp    
               tag=TAG20220320T203921
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
     
              starting media recovery
              media recovery complete, elapsed time: 00:00:01
     
              Finished recover at 20-MAR-22
     
              sql statement: alter tablespace TEST01 online

임시경로 복구

설명

  • 경로변경후 controlfile 내용 변경 필요
           switch datafile $number 의 $number는 report schema; 확인

 

 

 스크립트

run {
sql 'alter tablespace TEST01 offline immediate';
set newname for datafile '/oradata/EHR/test01.dbf' to '/oradata/tmp/test01.dbf';
 restore tablespace TEST01;
 switch datafile 4;
 recover tablespace TEST01;
sql 'alter tablespace TEST01 online';
}

오프라인 불가 테이블스페이스 삭제 후 복구

스크립트

run {
startup mount;
restore database;
recover database;
alter database open;
}

예시

  • 더보기
    RMAN> shutdown abort;
              Oracle instance shut down
    RMAN> run {
    startup mount;
    restore database;
    recover database;
    alter database open;
    }
              connected to target database (not started)
              Oracle instance started
              database mounted
     
              Total System Global Area 1610612736 bytes
     
              Fixed Size 2096632 bytes
              Variable Size 385876488 bytes
              Database Buffers 1207959552 bytes
              Redo Buffers 14680064 bytes
     
              Starting restore at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=157 devtype=DISK
     
              channel ORA_DISK_1: starting datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              restoring datafile 00001 to /oradata/EHR/system01.dbf
              restoring datafile 00002 to /oradata/EHR/test01.dbf
              restoring datafile 00003 to /oradata/EHR/sysaux01.dbf
              restoring datafile 00004 to /oradata/EHR/users01.dbf
              restoring datafile 00006 to /oradata/EHR/haksa01.dbf
              restoring datafile 00007 to /oradata/EHR/undo01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0e0ot1ut_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0e0ot1ut_1_1 tag=TAG20220320T203853
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
              Finished restore at 20-MAR-22
     
              Starting recover at 20-MAR-22
              using channel ORA_DISK_1
              channel ORA_DISK_1: starting incremental datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              destination for restore of datafile 00002: /oradata/EHR/test01.dbf
              channel ORA_DISK_1: reading from backup piece      
                  /app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_..3_.bkp
              channel ORA_DISK_1: restored backup piece 1
              piece           handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1.3_.bkp
               tag=TAG20220320T203921
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
              channel ORA_DISK_1: starting incremental datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              destination for restore of datafile 00006: /oradata/EHR/haksa01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0h0ot24o_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0h0ot24o_1_1 tag=TAG20220320T204200
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
     
              starting media recovery
              media recovery complete, elapsed time: 00:00:00
     
              Finished recover at 20-MAR-22
     
              database opened

불완전복구 (drop table/user, DML ..)

스크립트

 run{
startup mount;
sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
set until time ='2022-03-20 20:10:00';
restore database;
recover database;
alter database open resetlogs;
}

예시 

  • 더보기
    RMAN> shutdown immediate;
    RMAN> run{
    startup mount;
    sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
    set until time ='2022-03-20 20:10:00';
    restore database;
    recover database;
    alter database open resetlogs;
    }
     
              Starting restore at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=157 devtype=DISK
     
              channel ORA_DISK_1: starting datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              restoring datafile 00001 to /oradata/EHR/system01.dbf
              restoring datafile 00002 to /oradata/EHR/test01.dbf
              restoring datafile 00003 to /oradata/EHR/sysaux01.dbf
              restoring datafile 00004 to /oradata/EHR/users01.dbf
              restoring datafile 00006 to /oradata/EHR/haksa01.dbf
              restoring datafile 00007 to /oradata/EHR/undo01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0e0ot1ut_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0e0ot1ut_1_1 tag=TAG20220320T203853
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
              Finished restore at 20-MAR-22
              Starting recover at 20-MAR-22
              using channel ORA_DISK_1
              channel ORA_DISK_1: starting incremental datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              destination for restore of datafile 00002: /oradata/EHR/test01.dbf
              channel ORA_DISK_1: reading from backup piece    
          /app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_..3_.bkp
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/app/oracle/flash_recovery_area/EHR/backupset/2022_03_20/o1_.._.bkp
               tag=TAG20220320T203921
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
              channel ORA_DISK_1: starting incremental datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              destination for restore of datafile 00006: /oradata/EHR/haksa01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0h0ot24o_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0h0ot24o_1_1 tag=TAG20220320T204200
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
     
              starting media recovery
              media recovery complete, elapsed time: 00:00:01
     
              Finished recover at 20-MAR-22
              database opened

tablespace 복구

스크립트

  • 작업형명령어와 단일명령어가 혼합
    • nomount → nls_date_format변경 → restore controlfile → alter database mount → nls_date_format변경 →불완전복원 및 복구

예시

  • 더보기
    RMAN> shutdown immediate;
    RMAN> startup nomount;
    RMAN> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
    RMAN> run {
    restore controlfile from '/data/backup/rman/0j0ot4nb_1_1';
    }
              Starting restore at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=156 devtype=DISK
     
              channel ORA_DISK_1: restoring control file
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
              output filename=/oradata/EHR/control01.ctl
              output filename=/oradata/EHR/control02.ctl
              output filename=/oradata/EHR/control03.ctl
              Finished restore at 20-MAR-22
    RMAN> alter database mount;
              database mounted
              released channel: ORA_DISK_1
    RMAN> sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
    RMAN> run {
    set until time '2022-03-20 21:30:00';
    restore database;
    recover database;
    }
              executing command: SET until clause
     
              Starting restore at 20-MAR-22
              Starting implicit crosscheck backup at 20-MAR-22
              allocated channel: ORA_DISK_1
              channel ORA_DISK_1: sid=156 devtype=DISK
              Crosschecked 16 objects
              Finished implicit crosscheck backup at 20-MAR-22
              
              Starting implicit crosscheck copy at 20-MAR-22
              using channel ORA_DISK_1
              Crosschecked 1 objects
              Finished implicit crosscheck copy at 20-MAR-22
     
              searching for all files in the recovery area
              cataloging files...
              no files cataloged
     
              using channel ORA_DISK_1
     
              channel ORA_DISK_1: starting datafile backupset restore
              channel ORA_DISK_1: specifying datafile(s) to restore from backup set
              restoring datafile 00001 to /oradata/EHR/system01.dbf
              restoring datafile 00002 to /oradata/EHR/test01.dbf
              restoring datafile 00003 to /oradata/EHR/sysaux01.dbf
              restoring datafile 00004 to /oradata/EHR/users01.dbf
              restoring datafile 00006 to /oradata/EHR/haksa01.dbf
              restoring datafile 00007 to /oradata/EHR/undo01.dbf
              channel ORA_DISK_1: reading from backup piece /data/backup/rman/0i0ot4n4_1_1
              channel ORA_DISK_1: restored backup piece 1
              piece handle=/data/backup/rman/0i0ot4n4_1_1 tag=TAG20220320T212555
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
              Finished restore at 20-MAR-22
     
              Starting recover at 20-MAR-22
              using channel ORA_DISK_1
     
              starting media recovery
     
              archive log thread 1 sequence 6 is already on disk as file /oradata/EHR/redo03_a.log
              archive log filename=/oradata/EHR/redo03_a.log thread=1 sequence=6
              media recovery complete, elapsed time: 00:00:01
              Finished recover at 20-MAR-22

 

Block 복구 

설명

  • 쿼리수행시 ORA-01578 block corruption 발생시

스크립트

  • 논리/물리적 장애 체크 (RMAN)
run {
backup validate database;
}
  • corrupted block 확인
select * from v$backup_corruption;
  • 복원 (3.2 컬럼정보확인)
run {blockrecover datafile $FILE# block $BLOCK# ;}
저작자표시 비영리 변경금지

'Oracle > 백업과 복구' 카테고리의 다른 글

[논리적 백업/복구] IMPORT/EXPORT & DATAPUMP  (0) 2022.03.23
[논리적 백업/복구] Flashback  (0) 2022.03.23
[물리적 백업/복구] 복구 redo log file  (0) 2022.03.23
[물리적 백업/복구] 복구-data file  (0) 2022.03.23
[물리적 백업/복구] 백업  (0) 2022.03.23

+ Recent posts

티스토리툴바