Oracle RMAN 검토 내역

아래 내용은 제가 2015년에 검토했던 내용이나 RMAN을 처음 접하시는 분들이 참고할만할 거 같아서 공유해드립니다.

 

RMAN

1)  RMAM의 작동 원리

RMAN백업을 수행 하기 위해서는 최소 STARTUP MOUNT단계에서 진행이 가능하며 OPEN 된 상태에서 백업을 받으려면 ARCHIVE LOG MODE가 세팅이 되어야 백업이 수행된다.

온라인 리두로그는 백업이 불가 하며 노아카이브 모드에서는 Clean 백업만 사용가능하다.즉 오프라인 테이블스페이스나 읽기 전용 테이블 스페이스 백업 사용 가능하다.
잘 사용되지 않은 백업 방식이므로 쓸 일이 없다.

RMAN은 Begin backup 하는것도 동일하기 때문에 end backup이 되지 않은 상태에서는 오류가 발생할 수 있다.

 

n  RMAN의 접속

RMAN을 구동하게 괴면 서버쪽의 SID를 채크 하고 SYS사용자로 로그인 하게 됩니다.그리고 인스턴스에 접속하기 위해 Channel Server Processes를 생성하게 됩니다. 기본값은 1개이며 이 Server Processes의 메모리인 PGA가 할당됩니다.

 

n  SYS.DBMS_RCVMAN 패키지 호출

이 과정에서 대상 데이터베이스의 버전과 전체적인 물리적 구조를 알기 위해 호출합니다.

이 패키지는 Control File의 정보를 읽어서 데이터 베이스 전체의 파일들에 관한 정보와 체크포인트 정보, 생성시간 정보, 각 Datafile의 온라인/오프라인 정보 및 위치 정보를 모으게 됩니다.

그리고 백업을 수행되는 동안 변경되는 정보들로부터 현재 시점의 정보를 지키기 위해 Control file을 스냅숏 해서 보관하게 됩니다.

 

n  DBMS_BACKUP_RESTORE 패키지 호출

이 과정에서 RMAN은 정해진 위치에 백업 피스를 만들고 백업 파일을 읽어 들이기 위한 입력 버퍼를 생성합니다. 출력 버퍼는 디스크에 백업 피스를 생성하기 위해 메모리를 할당받는다.

 

 

위 단계에서 메모리 할당 작업이 끝난 후 RMAN은 백업 피스를 초기화합니다.그리고 백업 피스를 저장하기 위해 디스크의 백업 장치 위치에 기본값인 50MB의 공간을 우선 할당합니다.( 9i ~ 11 버전버전 공통) 그리고 출력 버퍼가 가득 차면 출력 버퍼의 내용을 백업 피스에 기록합니다. 만약 백업량이 많아서 디스크에 할당한 50MB를 다 사용하면 추가로 더 확보하고 백업이 끝나면 추가로 할당된 영역은 해제됩니다.

모든 백업 파일이 백업 피스에 저장 완료되면 RMAN은 다시 DBMS_BACKUP_RESOTRE 패키지를 호출해서 백업 피스의 이름과 시간, 마지막 체크포인트 정보 등을 Control file에에 기록합니다.

이 과정까지 끝나면 백업이 완료됩니다.

 

2)  Recovery Catalog (복구 카탈로그)란?

RMAN사용 시에 RMAN의 백업 보구 작업을 하고 관련 정보를 저장해 두는 저장소입니다 Catalog 서버가 없을 경우 Target Database Server(local DB)의 Contrilfile에 해당 정보를 저장한다.

Recovery Catalog에 저장되는 정보를 아래와 같습니다.

1.  Datafile 및 아카이브 리두로그 파일의 backupset과 Copy 된 이미지에 대한 정보

2.  백업 대상 서버의 물리적 구조

3.  자주 사용하는 백업 스크립트

Catalog Server 없이 Local DB에서 접속 방법 :  rman target /
Catalog Server 사용시 접속 방법 : rman target / catalog catuser/catuser@catserver

 

 

3)  Recover Catalog DB 생성하기

테스트 서버를 최소 2대 이상 구성하여 해당 테스트를 진행할 수 있습니다.
Catalog Server는 RMAN정보를 저장하기 위한 Server
Client Server는는 Backup Target Server이다 아래에서는 Catalog Server와 Client Server로 지칭하겠다.

①   테이블 스페이스 생성 및 유저를 생성한다.(명령어는 넣지 않겠다.)

②   생성한 USER에 권한을 부여한다.è grant connect , resource , recovery_catalog_owner to [생성한 유저];생성한유저];

③   Catalog Server에서 RMAN 스키마로 접속하여 관련 오브젝트가 조회되는지 확인한다.

$ sqlplus rman/rman SQL> select * from rc_database;

 

 

④   Client Server에서 Catalog Server로 접속하여 Client Server정보를 등록한다.

$ rman target / catalog rman/rman@CAT RMAN> register database;

 

⑤   해당 정보는 Catalog Server에서 생성한 스키마에서 등록된 정보를 확인할 수 있다.

set line 200 col db name for a10 col tablespace name for a20 . col file name for a50 select db_name ,tablespace_name ,name "file_name" from rc_datafile;

 

4)  Catalog Server 구성 테스트

Client Server에서 전체 백업을 수행합니다.아래 테스트는 RAC에서 진행된 것이며 Single DB에서는 별다른 설정 없이 수행 가능하다.
만약 ASM으로 구성되어 있다면 백업용 ASM DISK Group를 생성하여 백업을 수행하는 것이 제일 효율적이다. (백업솔루션이 없는 경우)

하지만 아래 테스트에서는 로컬 DISK에 백업되도록 설정되어 있어 백업 정보가 RAC1, RAC2에 분산 저장되는 현상이 발생하였다.

RAC 상태에서 추가적인 설정이 필요하다. 수행 하게 되면 RAC1, RAC2에 대한 접속 정보가 tnsnames.ora파일에 설정되어 있어야 하며 각노드에서 Catalog Server tns정보가 등록되어 있어야 한다.

 

[tnsnames.or] CAT =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = CAT)     )   )   RAC1 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SID = RAC1)     )   ) RAC2 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SID = RAC2)     )   )   Catalog Server 구성 후 최초 백업을 수행해본다. Client Server 에서 Catalog Server로 접속 하는 방식으로 아래와 같이 접속한다.   [oracle@rac1 admin]$ rman target / catalog rman/rman@cat Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 7 09:33:09 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: RAC (DBID=2474932785) connected to recovery catalog database   RMAN> backup database;  ç 제일 기본적인 명령어   Starting backup at 07-SEP-15 starting full resync of recovery catalog full resync complete configuration for SBT_TAPE channel 2 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=19 instance=rac2 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=00002 name=+DATA/rac/datafile/sysaux.270.886950681 input datafile file number=00007 name=+DATA/rac/datafile/ts_new.259.889350625 input datafile file number=00004 name=+DATA/rac/datafile/users.272.888075725 input datafile file number=00008 name=+DATA/rac/datafile/ts_new.256.889350759 channel ORA_DISK_1: starting piece 1 at 07-SEP-15 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/rac/datafile/system.269.888079437 input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.271.886950681 input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.278.886950995 channel ORA_DISK_2: starting piece 1 at 07-SEP-15 channel ORA_DISK_2: finished piece 1 at 07-SEP-15 piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/9jqghvb7_1_1 tag=TAG20150907T093325 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: finished piece 1 at 07-SEP-15 piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/9iqghvb6_1_1 tag=TAG20150907T093325 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06 Finished backup at 07-SEP-15   Starting Control File and SPFILE Autobackup at 07-SEP-15 piece handle=/home/oracle/RMAN_BACKUP/coldbk/controlfile/c-2474932785-20150907-00.ctl comment=NONE Finished Control File and SPFILE Autobackup at 07-SEP-15   RMAN>

 

BACKUP을 수행 하기전에 먼저 RMAN에서 사용하는 설정정보를 확인 해보자.

 

RMAN> show all; RMAN configuration parameters for database with db_unique_name RAC are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/RMAN_BACKUP/coldbk/controlfile/%F.ctl'; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' CONNECT '*'; CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' CONNECT '*'; CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*'; CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*'; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/RMAN_BACKUP/%U_%T'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/snapcf_rac.f';

위 정보는 지금까지 테스트 하면서 설정된 정보가 포함되어 있습니다.
위 설정 정보에 대한 설명을 아래에서 확인 해보자.

- CONFIGURE RETENTION POLICY TO REDUNDANCY 1; Backup 보관 주기나 backup본의 갯수를 설정합니다. - CONFIGURE BACKUP OPTIMIZATION OFF; 이미 backup 된 동일한(checkpoint SCN등) datafile, archived redolog, backup set이 있다면 skip 합니다. - CONFIGURE DEFAULT DEVICE TYPE TO DISK; default backup device를 설정합니다 - CONFIGURE CONTROLFILE AUTOBACKUP OFF; RMAN의 BACKUP이나 COPY 명령등의 수행후 자동으로 control file backup을 수행합니다. - CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; autobackup되는 control file의 기본 format을 변경합니다. - CONFIGURE DEVICE TYPE DISK PARALLELISM 1; 특정 device에 automatic channel allocation 될때 channel의 갯수를 지정합니다. - CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; DATAFILE, CONTROL FILE의 backup set의 copy본 갯수를 지정합니다. - CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; ARCHIVELOG FILE의 backup set의 copy본 갯수를 지정합니다. - CONFIGURE MAXSETSIZE TO UNLIMITED; backupset의 maximum size를 설정합니다. - CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; (10g only) flash recovery area의 archived redo log에 대한 삭제 여부를 설정합니다. - CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/ora10g/dbs/snapcf_db10g.f'; RMAN은 resync시 생성되는 임시 snapshot control file의 이름을 지정한다.

 

위 정보들을 잘 조정 하여 백업 정책 및 백업 경로 등을 수정하여 백업 및 복구 수행 시 활용 할수 있다.

 

5)  Recover Catalog DB 관리 하기.

n  CROSSCHECK

CATALOG SERVER의 전체 BACKUPSET과 TARGET 서버의 백업정보가 정상적으로 동기화 되어 있는지 확인 하는 정보이다.

RMAN> crosscheck backupset;   using channel ORA_DISK_1 using channel ORA_DISK_2 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/01qeqmsn_1_1 RECID=1STAMP=887970712 crosschcked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/02qeqmu5_1_1 RECID=2 STAMP=887970758 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/03qer32r_1_1 RECID=3 STAMP=887983195 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04qer34a_1_1 RECID=4 STAMP=887983242 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/05qer74m_1_1 RECID=6 STAMP=887987351 crosschecked backup piece: found to be 'AVAILABLE'   위에서 AVAILABLE라는 것은 정상이라는 의미입니다.

 

n  backupset을 os 명령어로 삭제 후 다시 crosscheck 수행

[oracle@rac1 dbs]$ ls -l total 4703276 -rw-r----- 1 oracle dba      1689698304 Aug 17 10:32 01qeqmsn_1_1 -rw-r----- 1 oracle dba        18710528 Aug 17 10:32 02qeqmu5_1_1 -rw-r----- 1 oracle dba      1080543232 Aug 17 14:00 03qer32r_1_1 -rw-r----- 1 oracle dba      1062958592 Aug 17 14:01 04qer34a_1_1 -rw-r----- 1 oracle dba       932593664 Aug 17 15:09 05qer74m_1_1 -rw-r----- 1 oracle dba         7600640 Aug 17 15:11 09qer78m_1_1 -rw-r----- 1 oracle dba          364544 Aug 17 15:13 0bqer7cc_1_1 -rw-r----- 1 oracle dba           36864 Aug 17 15:13 0dqer7cd_1_1 -rw-r--r-- 1 oracle oinstall         66 Aug 17 10:44 afiedt.buf -rw-rw---- 1 oracle dba            1544 Aug 17 13:40 hc_rac1.dat -rw-r--r-- 1 oracle oinstall       2851 May 15  2009 init.ora -rw-r--r-- 1 oracle oinstall       1397 Aug 17 13:39 initrac1.ora -rw-r----- 1 oracle oinstall       1341 Aug 17 13:34 initrac1.ora.bak.rac1 -rw-r----- 1 oracle oinstall         33 Aug  3 15:10 initrac1.ora_org -rw-r----- 1 oracle oinstall       1536 Aug  5 15:16 orapwrac1 -rw-r----- 1 oracle dba        18792448 Aug 17 14:19 snapcf_rac1.f -rw-r----- 1 oracle dba            4608 Aug 17 13:46 spfilerac1.ora [oracle@rac1 dbs]$ rm *_1_1   RMAN> crosscheck backupset;   using channel ORA_DISK_1 using channel ORA_DISK_2 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/RMAN_BACKUP/arch_bak_887987766_14_141 RECID=13 STAMP=887987766 ...... crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/oracle/RMAN_BACKUP/arch_bak_888055108_47_471 RECID=45 STAMP=888055108 Crosschecked 16 objects   crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/01qeqmsn_1_1 RECID=1 STAMP=887970712 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/02qeqmu5_1_1 RECID=2 STAMP=887970758 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/03qer32r_1_1 RECID=3 STAMP=887983195 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04qer34a_1_1 RECID=4 STAMP=887983242 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/06qer74n_1_1 RECID=5 STAMP=887987351 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/05qer74m_1_1 RECID=6 STAMP=887987351 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/08qer764_1_1 RECID=7 STAMP=887987396 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/09qer78m_1_1 RECID=8 STAMP=887987478 …. Crosschecked 30 objects

 

위 OS에서 삭제된 backupset은 CROSSCHECK를 통해서 EXPIRED된것을 확인 할 수 있다.
삭제된 backupset이기 때문에 필요가 없는 리스트 이다.
그렇기 때문에 RMAN에서도 해당 정보를 정리 한다.

RMAN> delete expired backupset;   using channel ORA_DISK_1 using channel ORA_DISK_2   List of Backup Pieces BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 380     378     1   1   EXPIRED     DISK        /RMAN_BACKUP/01qeqmsn_1_1 381     379     1   1   EXPIRED     DISK        /RMAN_BACKUP/02qeqmu5_1_1 562     558     1   1   EXPIRED     DISK        /RMAN_BACKUP/03qer32r_1_1 563     559     1   1   EXPIRED     DISK        /RMAN_BACKUP/04qer34a_1_1 938     894     1   1   EXPIRED     DISK        /RMAN_BACKUP/05qer74m_1_1 958     955     1   1   EXPIRED     DISK        /RMAN_BACKUP/09qer78m_1_1 1036    1028    1   1   EXPIRED     DISK        /RMAN_BACKUP/0bqer7cc_1_1 1038    1030    1   1   EXPIRED     DISK        /RMAN_BACKUP/0dqer7cd_1_1   Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=/RMAN_BACKUP/01qeqmsn_1_1 RECID=1 STAMP=887970712 deleted backup piece backup piece handle=/RMAN_BACKUP/02qeqmu5_1_1 RECID=2 STAMP=887970758 deleted backup piece backup piece handle=/RMAN_BACKUP/03qer32r_1_1 RECID=3 STAMP=887983195 deleted backup piece backup piece handle=/RMAN_BACKUP/04qer34a_1_1 RECID=4 STAMP=887983242 deleted backup piece backup piece handle=/RMAN_BACKUP/05qer74m_1_1 RECID=6 STAMP=887987351 deleted backup piece backup piece handle=/RMAN_BACKUP/09qer78m_1_1 RECID=8 STAMP=887987478 deleted backup piece backup piece handle=/RMAN_BACKUP/0bqer7cc_1_1 RECID=10 STAMP=887987596 deleted backup piece backup piece handle=/RMAN_BACKUP/0dqer7cd_1_1 RECID=12 STAMP=887987597 Deleted 8 EXPIRED objects

 

위와 같이 정리된 모습을 볼 수 있다.

그리고 특정 BACKUPSET을 목록에서 삭제 할 수 있다.

ORACLE에서는 OS삭제를 권고 하지 않으며 RMAN에서 삭제하는 것을 권고 하고 있다.
특정 BACKUP SET를 삭제 하는 방법은 아래 와 같다.

RMAN> list backupset; 위 명령으로 backupset list를 확인 하여 BSkey를 가지고 삭제를 한다.     List of Backup Sets ===================     BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 893     Full    726.77M    DISK        00:00:35     17-AUG-15              BP Key: 937   Status: AVAILABLE  Compressed: NO  Tag: TAG20150817T150910         Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/06qer74n_1_1   List of Datafiles in backup set 893   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   1       Full 3865133    17-AUG-15 +DATA/rac/datafile/system.269.886950681   5       Full 3865133    17-AUG-15 +DATA/rac/datafile/example.277.886950781   6       Full 3865133    17-AUG-15 +DATA/rac/datafile/undotbs2.278.886950995   BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 895     Full    80.00K     DISK        00:00:01     17-AUG-15               BP Key: 939   Status: AVAILABLE  Compressed: NO  Tag: TAG20150817T150910         Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/08qer764_1_1   SPFILE Included: Modification time: 17-AUG-15   SPFILE db_unique_name: RAC   BS Key  Size       Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 956     14.05M     DISK        00:00:00     17-AUG-15              BP Key: 959   Status: AVAILABLE  Compressed: NO  Tag: TAG20150817T151116         Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/0aqer78m_1_1     List of Archived Logs in backup set 956   Thrd Seq     Low SCN    Low Time  Next SCN   Next Time   ---- ------- ---------- --------- ---------- ---------   1    71      3854102    17-AUG-15 3854105    17-AUG-15   1    72      3854105    17-AUG-15 3854108    17-AUG-15   1    73      3854108    17-AUG-15 3854111    17-AUG-15   1    74      3854111    17-AUG-15 3865453    17-AUG-15 2    82      3854117    17-AUG-15 3865450    17-AUG-15 …………….. ………….   RMAN> delete backupset 1029;  ç 해당 명령으로 특정 Backupset를 삭제 할 수 있다.   using channel ORA_DISK_1 using channel ORA_DISK_2   List of Backup Pieces BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 1037    1029    1   1   AVAILABLE   DISK        /u01/app/oracle/product/11.2.0/db_1/dbs/0cqer7cc_1_1 Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/0cqer7cc_1_1 RECID=11 STAMP=887987596 Deleted 1 objects

 

n  관리자가 수동으로 Hotbackup을 수행 한 후 RMAN Catalog 에 추가 하고 관리 할수있다.

(SINGLE DB에서는 별다른 설정하지 않아도 되나 ASM에서 아래 테스트를 위해 ASMCMD를 통해서 CP명령으로 LOCAL FILESYSTEM에 백업을 수행했다.)

SQL> select name from v$datafile;   NAME -------------------------------------------------------------------------------- +DATA/rac/datafile/system.269.886950681 +DATA/rac/datafile/sysaux.270.886950681 +DATA/rac/datafile/undotbs1.271.886950681 +DATA/rac/datafile/users.272.886950681 +DATA/rac/datafile/example.277.886950781 +DATA/rac/datafile/undotbs2.278.886950995   6 rows selected.   SQL> alter tablesapce example begin backup; Tablespace altered.   [oracle@rac1 dbs]$ export $ORACLE_SID=+ASM1   çASM 인스턴스 환경에서 아래 명령을 수행한다. $ asmcmd cp +DATA/rac/datafile/example.277.886950781 /RMAN_BACKUP/example.dbf copying +DATA/rac/datafile/example.277.886950781 -> /home/oracle/RMAN_BACKUP/example.dbf   [oracle@rac1 dbs]$ export $ORACLE_SID=rac1 ç DB환경에서 end backup를 수행한다.   [oracle@rac1 dbs]$ sql SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 10:29:00 2015 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   SQL> alter tablespace example end backup; Tablespace altered.     (3) Catalog 명령어로 백업 받은 파일을 Recovery catalog에 추가합니다.   RMAN> catalog datafilecopy '/home/oracle/RMAN_BACKUP/example.dbf';   cataloged datafile copy datafile copy file name=/home/oracle/RMAN_BACKUP/example.dbf RECID=2 STAMP=888057007   RMAN> list copy;   specification does not match any control file copy in the repository specification does not match any archived log in the repository List of Datafile Copies =======================   Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 1570    5    A 18-AUG-15       4086871    18-AUG-15               Name: /home/oracle/RMAN_BACKUP/example.dbf

위와 같은 일은 없겠지만 알아 두는 게 좋을 거 같다.
위에 추가한 내용을 삭제 하고 싶을 땐 아래와 같이 수행하면 된다.

RMAN> change datafilecopy '/home/oracle/RMAN_BACKUP/example.dbf' uncatalog;   uncataloged datafile copy datafile copy file name=/home/oracle/RMAN_BACKUP/example.dbf RECID=2 STAMP=888057007 Uncataloged 1 objects     RMAN> list copy;   specification does not match any datafile copy in the repository specification does not match any control file copy in the repository specification does not match any archived log in the repository

 

 

6)  Checnnel 할당하기

n  자동 Channel 할당하기

자동 Channel이란 백업을 수행할 때 별도의 경로를 주지 않아도 정해진 위치로 백업을 받는 것을 말하며 Default Channel의 의미가 있습니다. 다음과 같이 설정을 할 수 있습니다.

RMAN> configure default device type to disk; new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 released channel: ORA_DISK_2 starting full resync of recovery catalog full resync complete   이렇게 설정 하면 default device가 파라미터 파일 의 db_recovery file_dest 파라미 터 경로로 설정됩 니다. 특정 한 경로를 default device로 설정해 보겠습니다.   SQL> show parameter db_recovery   NAME                               TYPE      VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest              string db_recovery_file_dest_size         big integer 0   위와 같이 설정되어 있지 않으면 $ORACLE_HOME/dbs/ 로 Backupset이 받아 진다.

n  수동 Channel 할당하기

아래와 같이 작업형 명령어레 작성하여  Channel 과 경로를 지정 할 수도 있다.

RMAN> run{ 2> allocate channel c1 type disk  ç Channel 할당 3> format '/home/oracle/RMAN_BACKUP/%U_%T'; ç 경로지정 4> backup tablespace example; 5> }   released channel: ORA_DISK_1 released channel: ORA_DISK_2 allocated channel: c1 channel c1: SID=187 instance=rac1 device type=DISK   Starting backup at 18-AUG-15 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 channel c1: starting piece 1 at 18-AUG-15 channel c1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/1rqetcjs_1_1_20150818 tag=TAG20150818T105452 comment=NONE channel c1: backup set complete, elapsed time: 00:00:03 Finished backup at 18-AUG-15 released channel: c1   è아래와 같이 독립 명령문으로 수행 하여도 된다. RMAN> backup tablespace users format '/home/oracle/RMAN_BACKUP/%U_%T';   Starting backup at 18-AUG-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=109 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=187 instance=rac2 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=00004 name=+DATA/rac/datafile/users.272.886950681 channel ORA_DISK_1: starting piece 1 at 18-AUG-15 channel ORA_DISK_1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/1sqetcn7_1_1_20150818 tag=TAG20150818T105639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-AUG-15

 

 

7)  백업의 종류

n  backupset으로 백업 수행

backup set이란 RMAN이 백업 파일을 만들때의 기본값이다. 이방법을 사용하게 되면 RMAN백업의 장정을 모두 사용할 수 있다.

Backup Set과 함께 사용 되는 용어는 backup piece라는 용어도 있다.

이는 backupset의 크기가 클 경우 분할해서 여러 개의 파일로 나누어서 백업 받을수 있는데 분할되는 하나의 백업 파일을 backup piece라고 부릅니다.

 

n  image copy로 백업 수행

image copy랑 OS명령어로 begin backup하는것과 가장 유사한 방법입니다. 이 방법을 사용할 경우 RMAN백업의 장점인 사용중인 블록만 백업 받는다거나 증분백업 받는 기능등 좋은 장점을 사용 할 수 없습니다.

RMAN> copy datafile '+DATA/rac/datafile/example.277.886950781' to '/home/oracle/RMAN_BACKUP/example_cp.dbf';   Starting backup at 18-AUG-15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 output file name=/home/oracle/RMAN_BACKUP/example_cp.dbf tag=TAG20150818T110635 RECID=3 STAMP=888059205 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 18-AUG-15

 

8)  백업 가능 대상

database (all data file과 현재 control file)
tablespace, datafile (current 또는 image copy)
archived redo log, control file (current 또는 image copy)
백업되지 않는 대상
online redo log file, init.ora, password file, listener.ora, tnsnames.ora

 

9)  증분 백 업 (Incremental backup)

n  일요일에 level0으로 데이터베이스를 전체 백업 받기

RMAN> run {

2> allocate channel c1 type disk;

3> allocate channel c2 type disk;

4> backup

5> incremental level 0

6> database format '/home/oracle/RMAN_BACKUP/INC/%U_%T';

7> }

 

n  일요일은 제외한 나머지는 누적 증분백업/차등증분 level3로 백업을 받는다.

RMAN> run{

2> allocate channel c2 type disk;

3> backup

4> incremental level 3 cumulative  or   incremental level 3

5> tablespace users;

6> }

1. 차등증분백업(incremental backup) 과거숫자=<현재숫자

 백업받을때 설정했던 숫자가 자기보다 작거나 같으면, 그 시점부터 지금까지 모든 변경된 데이터를 백업을 받는 것을 말함

 

2. 누적증분백업(cumulative incremental backup) 과거숫자 < 현재숫자

 백업받을때 설정했던 작은 숫자가 나온 시점부터 현재까지 모든 변경된 데이터를 백업받는 것

 

10)          Block change tracking 기능 활성화 후 증분 백업 수행

Block change tracking 기 능이 란 데이 터 블록들의 변경된 블록만 추적 하는 기 능 을 의미합니 다. 블록들의 변경 사항은 특정 파일 에 저장되어 관리됩니다. 이 기 능은 oracle10g Enterprise Edition에서 부터 지원됩니다.

SQL> alter database enable block change tracking using file '/home/oracle/RMAN_BACKUP/block_tracking.txt'; 시간이 다소 걸림 !!서비스에 영향이 있는지 확인 해봐야 할부분!!   Tue Aug 18 13:57:06 2015 alter database enable block change tracking using file '/home/oracle/RMAN_BACKUP/block_tracking.txt' Block change tracking file is current. Tue Aug 18 13:57:06 2015 Starting background process CTWR Tue Aug 18 13:57:06 2015 CTWR started with pid=62, OS id=11843 Block change tracking service is active.   파일 생성된것은 확인 되나 오래 걸려서 중간에 Ctrl+C로 중지 했는데..... PMON이 죽으면서 DB가 죽고 다시 살아남 ... [oracle@rac1 RMAN_BACKUP]$ ls -lart block_tracking.txt total 3908384 -rw-r-----  1 oracle dba       11600384 Aug 18 13:57 block_tracking.txt   ----------------------------------------- alert log--------------------------------------------------- Tue Aug 18 14:00:16 2015 ORA-1013 signalled during: alter database enable block change tracking using file '/home/oracle/RMAN_BACKUP/block_tracking.txt'... Tue Aug 18 14:00:16 2015 CHANGE TRACKING ERROR in another instance, disabling change tracking Block change tracking service stopping. Stopping background process CTWR Tue Aug 18 14:00:46 2015 Background process CTWR not dead after 30 seconds Killing background process CTWR Deleted file /home/oracle/RMAN_BACKUP/block_tracking.txt Tue Aug 18 14:00:49 2015 System state dump requested by (instance=1, osid=8801 (PMON)), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_diag_8814_20150818140049.trc Tue Aug 18 14:00:49 2015 PMON (ospid: 8801): terminating the instance due to error 487 Tue Aug 18 14:00:50 2015 ORA-1092 : opitsk aborting process Dumping diagnostic data in directory=[cdmp_20150818140049], requested by (instance=1, osid=8801 (PMON)), summary=[abnormal instance termination]. Instance terminated by PMON, pid = 8801 Tue Aug 18 14:01:01 2015 Starting ORACLE instance (normal) 위 상황은 싱글모드에서 진행 해야 하는 작업임.       RAC환경에서 클러스터 파일 시스템이나 ASM또는 공유 DISK에 저장 해야한다. SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA/block_change_tracking.dat' REUSE; Database altered.   이 파일의 내용을 확인해 보니 바이너리 파일이라는 것을 알 수 있습니다. 이 기능 을 비활성화하려면 SQL> alter database disable block change tracking; 하면 됩니다. Block change tracking 기능을 확인하려면 control file을 조회하면 됩니다.   set line 200 col status for a10 col filename for a50 col MB for 999999 select status , filename , bytes/1024/1024 MB from v$block_change_tracking;

 

 

11)          압축 하면서 백업 수행하기

RMAN> backup as compressed backupset database;

Starting backup at 18-AUG-15 configuration for SBT_TAPE channel 2 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=99 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=108 instance=rac1 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.278.886950995 input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.271.886950681 channel ORA_DISK_1: starting piece 1 at 18-AUG-15 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/rac/datafile/system.269.886950681 input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 input datafile file number=00004 name=+DATA/rac/datafile/users.272.886950681 channel ORA_DISK_2: starting piece 1 at 18-AUG-15 channel ORA_DISK_1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/2aqetppo_1_1_20150818 tag=TAG20150818T143952 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_2: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/2bqetppo_1_1_20150818 tag=TAG20150818T143952 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55 Finished backup at 18-AUG-15     압축하지 않은 Backupset -rw-r-----  1 oracle dba      764,428,288 Aug 18 14:43 2gqetq0c_1_1_20150818 -rw-r-----  1 oracle dba      960,659,456 Aug 18 14:44 2fqetq0b_1_1_20150818   압축 하면서 받은 Backupset -rw-r-----  1 oracle dba      169,304,064 Aug 18 14:40 2aqetppo_1_1_20150818 -rw-r-----  1 oracle dba      227,196,928 Aug 18 14:40 2bqetppo_1_1_20150818

 

12)          ARCHIVE LOG BAKCUPSET 압축하기

RMAN> run { 2> configure channel 1 device type disk connect 'SYS/oracle@rac1' format '/home/oracle/RMAN_BACKUP/arch_bak_%t_%s_%s%p';   configure channel 2 device type disk connect 'SYS/oracle@rac2' format '/home/oracle/RMAN_BACKUP/arch_bak_%t_%s_%s%p';3> 4> backup as compressed backupset archivelog all; 5> }   old RMAN configuration parameters: CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   '/home/oracle/RMAN_BACKUP/%U_%T'; new RMAN configuration parameters: CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT '*' FORMAT   '/home/oracle/RMAN_BACKUP/arch_bak_%t_%s_%s%p'; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 released channel: ORA_DISK_2 starting full resync of recovery catalog full resync complete   old RMAN configuration parameters: CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   '/home/oracle/RMAN_BACKUP/%U_%T'; new RMAN configuration parameters: CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT '*' FORMAT   '/home/oracle/RMAN_BACKUP/arch_bak_%t_%s_%s%p'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete   Starting backup at 18-AUG-15 current log archived configuration for SBT_TAPE channel 2 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=104 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=189 instance=rac2 device type=DISK channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=111 RECID=301 STAMP=888066936 input archived log thread=2 sequence=122 RECID=302 STAMP=888068701 channel ORA_DISK_1: starting piece 1 at 18-AUG-15 channel ORA_DISK_2: starting compressed archived log backup set channel ORA_DISK_2: specifying archived log(s) in backup set input archived log thread=1 sequence=112 RECID=306 STAMP=888069652 input archived log thread=2 sequence=123 RECID=305 STAMP=888068703 input archived log thread=2 sequence=124 RECID=312 STAMP=888072545 input archived log thread=1 sequence=113 RECID=309 STAMP=888069653 channel ORA_DISK_2: starting piece 1 at 18-AUG-15 channel ORA_DISK_1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/arch_bak_888072550_82_821 tag=TAG20150818T144909 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=114 RECID=310 STAMP=888072543 channel ORA_DISK_1: starting piece 1 at 18-AUG-15 channel ORA_DISK_2: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/arch_bak_888072550_83_831 tag=TAG20150818T144909 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/arch_bak_888072553_84_841 tag=TAG20150818T144909 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-AUG-15   압축 하지 않았을 때의 Backupset -rw-r-----  1 oracle dba       47466496 Aug 18 14:50 arch_bak_888072600_86_861 -rw-r-----  1 oracle dba        3111424 Aug 18 14:50 arch_bak_888072601_88_881    --압축 하였을 때의 Backupset -rw-r-----  1 oracle dba        1573888 Aug 18 14:49 arch_bak_888072553_84_841 -rw-r-----  1 oracle dba       15109632 Aug 18 14:49 arch_bak_888072550_82_821

 

 

13)          MultiSection Backup(11g New Feature)

앞에서 여러 개의 파일을 백업할 경우 Channel별로 설정해서 백업 받는 방법을 살펴보았습니다. 그러나 만약 한 개의 파일 이 아주 크다면 이것은 Channel별로 분할 해서 받을 수 없습니다. 그래서 등장한 기능이 MultiSection Backup 기능입니다.

 

RMAN> report schema;   Report of database schema for database with db_unique_name RAC   List of Permanent Datafiles =========================== File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    760      SYSTEM               YES     +DATA/rac/datafile/system.269.886950681 2    1080     SYSAUX               NO      +DATA/rac/datafile/sysaux.270.886950681 3    105      UNDOTBS1             YES     +DATA/rac/datafile/undotbs1.271.886950681 4    7        USERS                NO      +DATA/rac/datafile/users.272.886950681 5    346      EXAMPLE              NO      +DATA/rac/datafile/example.277.886950781 6    50       UNDOTBS2             YES     +DATA/rac/datafile/undotbs2.278.886950995   List of Temporary Files ======================= File Size(MB) Tablespace           Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1    52       TEMP                 32767       +DATA/rac/tempfile/temp.276.886950777     300메가씩 쪼개서 받아 지도록 한다.   RMAN> backup as compressed backupset section size 300m format  '/home/oracle/RMAN_BACKUP/%U_%T' datafile 2;     Starting backup at 18-AUG-15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 backing up blocks 1 through 38400 channel ORA_DISK_1: starting piece 1 at 18-AUG-15 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 backing up blocks 38401 through 76800 channel ORA_DISK_2: starting piece 2 at 18-AUG-15 channel ORA_DISK_1: finished piece 1 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/35qetrp3_1_1_20150818 tag=TAG20150818T151339 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 backing up blocks 76801 through 115200 channel ORA_DISK_1: starting piece 3 at 18-AUG-15 channel ORA_DISK_2: finished piece 2 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/35qetrp3_2_1_20150818 tag=TAG20150818T151339 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:16 channel ORA_DISK_2: starting compressed full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 backing up blocks 115201 through 138240 channel ORA_DISK_2: starting piece 4 at 18-AUG-15 channel ORA_DISK_2: finished piece 4 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/35qetrp3_4_1_20150818 tag=TAG20150818T151339 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: finished piece 3 at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/35qetrp3_3_1_20150818 tag=TAG20150818T151339 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16 Finished backup at 18-AUG-15   Starting Control File and SPFILE Autobackup at 18-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/coldbk/controlfile/c-2474932785-20150818-0a.ctl comment=NONE Finished Control File and SPFILE Autobackup at 18-AUG-15   RMAN>

 

 

14)          RMAN 백업 작업 진행 사항 확인하기

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK !=0;   백업이 수행 중인 세션에 대해서 수행률을 확인 할 수 있다.          SID    SERIAL#  CONTEXT      SOFAR  TOTALWORK %_COMPLETE ---------- ---------- ---------- ---------- ---------- ----------        104        81          1      67838    158080      42.91

 

 

15)          RMAN으로 복구하기

 

1) Data file 삭제 후 DB Open 상태에서 복구하기   [oracle@rac1 RMAN_BACKUP]$ asmcmd ASMCMD> ASMCMD> cd DATA/RAC/DATAFILE ASMCMD> pwd +DATA/RAC/DATAFILE ASMCMD> ls -l Type      Redund  Striped  Time             Sys  Name DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    EXAMPLE.264.886777531 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    EXAMPLE.277.886950781 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    SYSAUX.257.886777425 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    SYSAUX.270.886950681 DATAFILE  UNPROT  COARSE   AUG 05 13:00:00  Y    SYSTEM.256.886777425 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    SYSTEM.269.886950681 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    UNDOTBS1.258.886777427 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    UNDOTBS1.271.886950681 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    UNDOTBS2.265.886777745 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    UNDOTBS2.278.886950995 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    USERS.272.888075725   ASMCMD> rm -rf USERS.272.886950681 ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/RAC/DATAFILE/USERS.272.886950681' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)   SQL> alter tablespace users offline;   Tablespace altered.   [oracle@rac1 RMAN_BACKUP]$ asmcmd ASMCMD> cd DATA/RAC/DATAFILE ASMCMD> rm -rf USERS.272.886950681     SQL> alter tablespace users online; alter tablespace users online * ERROR at line 1: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '+DATA/rac/datafile/users.272.886950681'   >>복구를 위해 관련 테이블 스페이스 data를 restore한다.   RMAN> restore tablespace users;   Starting restore at 18-AUG-15 starting full resync of recovery catalog full resync complete using channel ORA_DISK_1 using channel ORA_DISK_2   channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00004 to +DATA/rac/datafile/users.272.886950681 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/product/11.2.0/db_1/dbs/3gqetsgs_1_1 channel ORA_DISK_2: piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/3gqetsgs_1_1 tag=TAG20150818T152619 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 Finished restore at 18-AUG-15 starting full resync of recovery catalog full resync complete   >> 복구 진행 RMAN> recover tablespace users;   Starting recover at 18-AUG-15 using channel ORA_DISK_1 using channel ORA_DISK_2   starting media recovery media recovery complete, elapsed time: 00:00:00   Finished recover at 18-AUG-15 starting full resync of recovery catalog full resync complete   >> 복구가 정상적으로 완료 되면 online수행한다.   RMAN> sql ' alter tablespace users online';   sql statement:  alter tablespace users online starting full resync of recovery catalog full resync complete   RMAN>     관련 Alert log내용   alter tablespace users online Tue Aug 18 15:41:11 2015 Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_dbw0_12978.trc: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '+DATA/rac/datafile/users.272.886950681' ORA-17503: ksfdopn:2 Failed to open file +DATA/rac/datafile/users.272.886950681 ORA-15012: ASM file '+DATA/rac/datafile/users.272.886950681' does not exist ORA-1157 signalled during: alter tablespace users online... Tue Aug 18 15:42:23 2015   alter database recover datafile list clear Completed: alter database recover datafile list clear   alter database recover if needed  tablespace USERS Media Recovery Start   Serial Media Recovery started Recovery of Online Redo Log: Thread 2 Group 4 Seq 126 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_4.280.886951099 Recovery of Online Redo Log: Thread 1 Group 2 Seq 116 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_2.275.886950773 Media Recovery Complete (rac1) Completed: alter database recover if needed  tablespace USERS    Tue Aug 18 15:42:53 2015  alter tablespace users online Completed:  alter tablespace users online

 

16)          Offline 안 되는 테이블스페이스 삭제 후 복구하기

테스트를 위해 양쪽 노드다 shutdown abort로 내린다. 내리지않으면 아래와 같이 삭제가 안된다.   ASMCMD> rm -rf SYSTEM.269.886950681 ORA-15032: not all alterations performed ORA-15028: ASM file '+DATA/RAC/DATAFILE/SYSTEM.269.886950681' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)   ASMCMD> rm -rf SYSTEM.269.886950681   SMCMD> ls -l   Type      Redund  Striped  Time             Sys  Name DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    none => EXAMPLE.264.886777531 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    none => SYSAUX.257.886777425 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    none => UNDOTBS1.258.886777427 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    none => UNDOTBS2.265.886777745 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    none => EXAMPLE.277.886950781 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    none => SYSAUX.270.886950681 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    none => UNDOTBS1.271.886950681 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    none => UNDOTBS2.278.886950995 DATAFILE  UNPROT  COARSE   AUG 18 15:00:00  Y    none => USERS.272.888075725     SQL> startup mount; ORACLE instance started.   Total System Global Area  929046528 bytes Fixed Size                 1368464 bytes Variable Size            373296752 bytes Database Buffers         549453824 bytes Redo Buffers               4927488 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '+DATA/rac/datafile/system.269.886950681'   >> RMAN으로 접속 하여 복구를 수행한다.   [oracle@rac1 coldbk]$ rman target / catalog rman/rman@cat Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 18 15:56:49 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: RAC (DBID=2474932785, not open) connected to recovery catalog database   >> mount단계에서 모든 작업이 수행된다. node1은 RMAN에서 mount를 하고 node2는 sqlplus접속 하여 mount한다.   RMAN> startup mount;   Oracle instance started database mounted   Total System Global Area     929046528 bytes   Fixed Size                     1368464 bytes Variable Size                373296752 bytes Database Buffers             549453824 bytes Redo Buffers                   4927488 bytes starting full resync of recovery catalog full resync complete   >>SYSTEM이 날아 갔을땐 전체 백업본을 리스토어 한다.   RMAN> restore database;   Starting restore at 18-AUG-15 configuration for SBT_TAPE channel 2 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=176 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=177 instance=rac2 device type=DISK   channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to +DATA/rac/datafile/sysaux.270.886950681 channel ORA_DISK_1: restoring datafile 00003 to +DATA/rac/datafile/undotbs1.271.886950681 channel ORA_DISK_1: restoring datafile 00006 to +DATA/rac/datafile/undotbs2.278.886950995 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/db_1/dbs/3fqetsgs_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to +DATA/rac/datafile/system.269.886950681 channel ORA_DISK_2: restoring datafile 00004 to +DATA/rac/datafile/users.272.888075725 channel ORA_DISK_2: restoring datafile 00005 to +DATA/rac/datafile/example.277.886950781 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/product/11.2.0/db_1/dbs/3gqetsgs_1_1 channel ORA_DISK_2: piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/3gqetsgs_1_1 tag=TAG20150818T152619 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/3fqetsgs_1_1 tag=TAG20150818T152619 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 18-AUG-15 starting full resync of recovery catalog full resync complete   >> 복구를 수행한다.   RMAN> recover database;   Starting recover at 18-AUG-15 using channel ORA_DISK_1 using channel ORA_DISK_2   starting media recovery media recovery complete, elapsed time: 00:00:02   Finished recover at 18-AUG-15 starting full resync of recovery catalog full resync complete   >>DB를 오픈한다.   RMAN> alter database open;   database opened starting full resync of recovery catalog full resync complete   RMAN>   ASMCMD> ls -l Type      Redund  Striped  Time             Sys  Name DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    EXAMPLE.264.886777531 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    EXAMPLE.277.886950781 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    SYSAUX.257.886777425 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    SYSAUX.270.886950681 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    SYSTEM.269.888076699 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    UNDOTBS1.258.886777427 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    UNDOTBS1.271.886950681 DATAFILE  UNPROT  COARSE   AUG 05 14:00:00  Y    UNDOTBS2.265.886777745 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    UNDOTBS2.278.886950995 DATAFILE  UNPROT  COARSE   AUG 18 16:00:00  Y    USERS.272.888075725       >>>>>>>>>>>>>>>>>>>>>>>>>>>>    rac2 alertlog   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Tue Aug 18 15:55:57 2015 Starting ORACLE instance (normal) ....... Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name:    Linux Node name:      rac2.localdomain Release:        2.6.32-300.10.1.el5uek Version:        #1 SMP Wed Feb 22 17:22:40 EST 2012 Machine:        i686 Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac2.ora System parameters with non-default values:   processes                = 150   sga_target               = 888M   control_files            = "+DATA/rac/controlfile/current.273.886950769"   db_block_size            = 8192   compatible               = "11.2.0.4.0"   log_archive_dest_1       = "LOCATION=/home/oracle/ARC2"   log_archive_dest_2       = "LOCATION=/home/oracle/ARC1"   log_archive_format       = "%t_%s_%r.dbf"   cluster_database         = TRUE   db_create_file_dest      = "+DATA"   thread                   = 2   undo_tablespace          = "UNDOTBS2"   instance_number          = 2   remote_login_passwordfile= "EXCLUSIVE"   db_domain                = ""   dispatchers              = "(PROTOCOL=TCP) (SERVICE=racXDB)"   remote_listener          = "crs-scan:1521"   audit_file_dest          = "/u01/app/oracle/admin/rac/adump"   audit_trail              = "DB"   db_name                  = "rac"   open_cursors             = 300   pga_aggregate_target     = 295M   diagnostic_dest          = "/u01/app/oracle" Cluster communication is configured to use the following interface(s) for this instance   169.254.89.236 ...... Completed: ALTER DATABASE   MOUNT Tue Aug 18 15:57:03 2015 alter database open This instance was first to open Tue Aug 18 15:57:03 2015   >>> SYSTEM파일이 없기 때문에 오픈을 하지 못한다. Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_dbw0_10032.trc: ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '+DATA/rac/datafile/system.269.886950681' ORA-17503: ksfdopn:2 Failed to open file +DATA/rac/datafile/system.269.886950681 ORA-15012: ASM file '+DATA/rac/datafile/system.269.886950681' does not exist Block change tracking file is current. Abort recovery for domain 0 Errors in file /u01/app/oracle/diag/rdbms/rac/rac2/trace/rac2_ora_10100.trc: ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '+DATA/rac/datafile/system.269.886950681' ORA-1157 signalled during: alter database open...   >>rac1,2에서 Restore를 수행 하였습니다. 파일은 나눠서 노드별로 수행되었음.   Tue Aug 18 15:58:21 2015 Full restore complete of datafile 4 +DATA/rac/datafile/users.272.888075725.  Elapsed time: 0:00:00   checkpoint is 4170871   last deallocation scn is 3 Full restore complete of datafile 5 +DATA/rac/datafile/example.277.886950781.  Elapsed time: 0:00:07   checkpoint is 4170871   last deallocation scn is 3096016 Full restore complete of datafile 1 +DATA/rac/datafile/system.269.888076699.  Elapsed time: 0:00:10   checkpoint is 4170871   last deallocation scn is 706986   Undo Optimization current scn is 4156559 Tue Aug 18 15:59:51 2015 NOTE: Deferred communication with ASM instance NOTE: deferred map free for map id 10 Tue Aug 18 16:04:41 2015 alter database open Block change tracking file is current. Picked broadcast on commit scheme to generate SCNs Tue Aug 18 16:04:41 2015 LGWR: STARTING ARCH PROCESSES Tue Aug 18 16:04:41 2015      rac1 alertlog  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Tue Aug 18 15:48:43 2015 Shutting down instance (abort) License high water mark = 17 USER (ospid: 5102): terminating the instance Instance terminated by USER, pid = 5102 Tue Aug 18 15:48:44 2015 Instance shutdown complete Tue Aug 18 15:51:53 2015 Starting ORACLE instance (normal) ...... Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options. ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name:   Linux Node name:     rac1.localdomain Release:       2.6.32-300.10.1.el5uek Version:       #1 SMP Wed Feb 22 17:22:40 EST 2012 Machine:       i686 Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/db_1/dbs/spfilerac1.ora System parameters with non-default values:   processes                = 150   sga_target               = 888M   control_files            = "+DATA/rac/controlfile/current.273.886950769"   db_block_size            = 8192   compatible               = "11.2.0.4.0"   log_archive_dest_1       = "LOCATION=/home/oracle/ARC1"   log_archive_dest_2       = "LOCATION=/home/oracle/ARC2"   log_archive_format       = "%t_%s_%r.dbf"   cluster_database         = TRUE   db_create_file_dest      = "+DATA"   thread                   = 1   undo_tablespace          = "UNDOTBS1"    ....... ARC1: Becoming the heartbeat ARCH ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Tue Aug 18 15:52:55 2015 Decreasing number of real time LMS from 1 to 0 Tue Aug 18 15:55:59 2015 Reconfiguration started (old inc 2, new inc 4) List of instances:  1 2 (myinst: 1)  Global Resource Directory frozen  Communication channels reestablished  Master broadcasted resource hash value bitmaps  Non-local Process blocks cleaned out Tue Aug 18 15:55:59 2015  LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived  Set master node info  Submitted all remote-enqueue requests  Dwn-cvts replayed, VALBLKs dubious  All grantable enqueues granted  Post SMON to start 1st pass IR  Submitted all GCS remote-cache requests  Post SMON to start 1st pass IR  Fix write in gcs resources Reconfiguration complete Tue Aug 18 15:57:00 2015 Increasing number of real time LMS from 0 to 1 Tue Aug 18 15:58:21 2015   >> restore 수행함  Channel를 양쪽 노드로 설정하여 양쪽에서 한꺼번에 수행된다.   Full restore complete of datafile 6 +DATA/rac/datafile/undotbs2.278.886950995.  Elapsed time: 0:00:02   checkpoint is 4170868   last deallocation scn is 4167065   Undo Optimization current scn is 4156559 Full restore complete of datafile 3 +DATA/rac/datafile/undotbs1.271.886950681.  Elapsed time: 0:00:02   checkpoint is 4170868   last deallocation scn is 4098819   Undo Optimization current scn is 4156559 Tue Aug 18 15:58:53 2015 Full restore complete of datafile 2 +DATA/rac/datafile/sysaux.270.886950681.  Elapsed time: 0:00:34   checkpoint is 4170868   last deallocation scn is 4069296 Tue Aug 18 15:59:08 2015   >>복구를 수행하였다.   alter database recover datafile list clear Completed: alter database recover datafile list clear alter database recover datafile list  1 , 2 , 3 , 4 , 5 , 6 Completed: alter database recover datafile list  1 , 2 , 3 , 4 , 5 , 6 alter database recover if needed  start Media Recovery Start  started logmerger process Parallel Media Recovery started with 3 slaves Tue Aug 18 15:59:09 2015 Recovery of Online Redo Log: Thread 1 Group 2 Seq 116 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_2.275.886950773 Recovery of Online Redo Log: Thread 2 Group 4 Seq 126 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_4.280.886951099 Recovery of Online Redo Log: Thread 1 Group 1 Seq 117 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_1.274.886950771 Media Recovery Complete (rac1) Completed: alter database recover if needed  start Tue Aug 18 15:59:33 2015 alter database open This instance was first to open Block change tracking file is current. Beginning crash recovery of 1 threads  parallel recovery started with 2 processes Started redo scan kcrfr_rnenq: use log nab 32449 Completed redo scan  read 2129 KB redo, 0 data blocks need recovery Started redo application at  Thread 2: logseq 126, block 28190 Recovery of Online Redo Log: Thread 2 Group 4 Seq 126 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_4.280.886951099 Completed redo application of 0.00MB Completed crash recovery at  Thread 2: logseq 126, block 32449, scn 4215218  0 data blocks read, 0 data blocks written, 2129 redo k-bytes read Thread 2 advanced to log sequence 127 (thread recovery) Picked broadcast on commit scheme to generate SCNs Archived Log entry 322 added for thread 2 sequence 126 ID 0x9384812e dest 1: Archived Log entry 323 added for thread 2 sequence 126 ID 0x9384812e dest 2: Thread 2 advanced to log sequence 128 (before internal thread enable) Archived Log entry 324 added for thread 2 sequence 127 ID 0x9384812e dest 1: Archived Log entry 325 added for thread 2 sequence 127 ID 0x9384812e dest 2: Thread 2 advanced to log sequence 129 (after internal thread enable) Tue Aug 18 15:59:34 2015 Thread 1 opened at log sequence 118   Current log# 2 seq# 118 mem# 0: +DATA/rac/onlinelog/group_2.275.886950773 Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Starting background process CTWR Tue Aug 18 15:59:34 2015 CTWR started with pid=43, OS id=13652 Block change tracking service is active. Tue Aug 18 15:59:34 2015 SMON: enabling cache recovery [12854] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1231014464 end:1231015454 diff:990 (9 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 Tue Aug 18 15:59:37 2015 minact-scn: Inst 1 is now the master inc#:4 mmon proc-id:9986 status:0x7 minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000 minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:4 errcnt:0 No Resource Manager plan active ... NOTE: Deferred communication with ASM instance NOTE: deferred map free for map id 93 Tue Aug 18 16:04:40 2015 Starting background process SMCO Tue Aug 18 16:04:40 2015 SMCO started with pid=29, OS id=15138

 

 

17)          블럭손상을 발생 시켜 복구 하는법

블럭손상을 발생 시켜 복구 하는법 ASM으로 가능하나 테스트편의를 위해 FILESYSTEM으로 하였습니다.   테스트를 테에블 스페이스를 생성한다.   create smallfile tablespace bad_data datafile '/home/oracle/RMAN_BACKUP/bad_data_01.dbf' size 10M;   테스트를 위해 데이터를 생성한다..   Create table test (username varchar2(9), password varchar2(6)) tablespace bad_data;   DECLARE u  VARCHAR2(9); p  VARCHAR2(6); BEGIN FOR jump  IN 1 ..10000  LOOP u := 'TEST'||jump; p := 'P'||jump; insert into test values (u,p); END LOOP; commit; END; /   PL/SQL procedure successfully completed.     생성된 데이터를 확인한다.   select rowid , to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot from test where password='P7777'   ROWID                  OBJID   FILENUM   BLOCKNUM    ROWSLOT ------------------ ---------- ---------- ---------- ---------- AAAVs5AAHAAAACdAAr     88889          7       157         43       image backup을 수행한다. RMAN> copy datafile 7 to '/home/oracle/RMAN_BACKUP/bad_data_01.dbf_good';   Starting backup at 19-AUG-15 starting full resync of recovery catalog full resync complete using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/home/oracle/RMAN_BACKUP/bad_data_01.dbf output file name=/home/oracle/RMAN_BACKUP/bad_data_01.dbf_good tag=TAG20150819T101952 RECID=6 STAMP=888142793 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 19-AUG-15   Starting Control File and SPFILE Autobackup at 19-AUG-15 piece handle=/home/oracle/RMAN_BACKUP/coldbk/controlfile/c-2474932785-20150819-07.ctl comment=NONE Finished Control File and SPFILE Autobackup at 19-AUG-15   RMAN> exit   SQL> alter tablespace bad_data offline; Tablespace altered     >>dd 명령어로 corrupt생성하기 /home/oracle $dd if=/home/oracle/RMAN_BACKUP/bad_data_01.dbf bs=8k count=156 of=/home/oracle/RMAN_BACKUP/bad_data_01.dbf_new 156+0 records in 156+0 records out /home/oracle $dd if=/home/oracle/RMAN_BACKUP/bad_data_01.dbf bs=8k count=1 >> /home/oracle/RMAN_BACKUP/bad_data_01.dbf_new 1+0 records in 1+0 records out /home/oracle $dd if=/home/oracle/RMAN_BACKUP/bad_data_01.dbf bs=8k skip=157 >> /home/oracle/RMAN_BACKUP/bad_data_01.dbf_new 1124+0 records in 1124+0 records out /home/oracle $mv /home/oracle/RMAN_BACKUP/bad_data_01.dbf_new /home/oracle/RMAN_BACKUP/bad_data_01.dbf     SQL>alter tablespace bad_data online; Tablespace altered.   Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning option     SQL> select * from test where password='P7777'; select * from test where password='P7777'               * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 7, block # 156) ORA-01110: data file 7: '/home/oracle/RMAN_BACKUP/bad_data_01.dbf'   --- 블럭손상에 대한 alert log   Wed Aug 19 10:03:57 2015 Hex dump of (file 7, block 156) in trace file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_16954.trc Corrupt block relative dba: 0x01c0009c (file 7, block 156) Bad header found during multiblock buffer read Data in bad block:  type: 0 format: 2 rdba: 0xffc00000  last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00  spare1: 0x0 spare2: 0x0 spare3: 0x0  consistency value in tail: 0x00000000  check value in block header: 0xff66  block checksum disabled Reading datafile '/home/oracle/RMAN_BACKUP/bad_data_01.dbf' for corruption at rdba: 0x01c0009c (file 7, block 156) Reread (file 7, block 156) found same corrupt data (no logical check) Wed Aug 19 10:03:57 2015 Corrupt Block Found          TSN = 7, TSNAME = BAD_DATA          RFN = 7, BLK = 156, RDBA = 29360284          OBJN = 88888, OBJD = 88888, OBJECT = TEST, SUBOBJECT =          SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_16954.trc  (incident=18419): ORA-01578: ORACLE data block corrupted (file # 7, block # 156) ORA-01110: data file 7: '/home/oracle/RMAN_BACKUP/bad_data_01.dbf' Incident details in: /u01/app/oracle/diag/rdbms/rac/rac1/incident/incdir_18419/rac1_ora_16954_i18419.trc Wed Aug 19 10:03:59 2015 Dumping diagnostic data in directory=[cdmp_20150819100359], requested by (instance=1, osid=16954), summary=[incident=18419]. Errors in file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_16954.trc  (incident=18420): ORA-01578: ORACLE data block corrupted (file # 7, block # 156) ORA-01110: data file 7: '/home/oracle/RMAN_BACKUP/bad_data_01.dbf' Incident details in: /u01/app/oracle/diag/rdbms/rac/rac1/incident/incdir_18420/rac1_ora_16954_i18420.trc Dumping diagnostic data in directory=[cdmp_20150819100400], requested by (instance=1, osid=16954), summary=[incident=18420]. Wed Aug 19 10:04:01 2015 Sweep [inc][18420]: completed Sweep [inc][18419]: completed Sweep [inc2][18420]: completed Sweep [inc2][18419]: completed     블럭손상부분은 아래 명령어들로 확인 가능하다.   [oracle@rac1 RMAN_BACKUP]$ dbv file=/home/oracle/RMAN_BACKUP/bad_data_01.dbf  blocksize=8192   DBVERIFY: Release 11.2.0.4.0 - Production on Wed Aug 19 10:05:06 2015   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   DBVERIFY - Verification starting : FILE = /home/oracle/RMAN_BACKUP/bad_data_01.dbf Page 156 is marked corrupt Corrupt block relative dba: 0x01c0009c (file 7, block 156) Bad header found during dbv: Data in bad block:  type: 0 format: 2 rdba: 0xffc00000  last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00  spare1: 0x0 spare2: 0x0 spare3: 0x0  consistency value in tail: 0x00000000  check value in block header: 0xff66  block checksum disabled       DBVERIFY - Verification complete   Total Pages Examined         : 1280 Total Pages Processed (Data) : 27 Total Pages Failing   (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing   (Index): 0 Total Pages Processed (Other): 131 Total Pages Processed (Seg)  : 0 Total Pages Failing   (Seg)  : 0 Total Pages Empty            : 1121 Total Pages Marked Corrupt   : 1                  <<,----------- Total Pages Influx           : 0 Total Pages Encrypted        : 0 Highest block SCN            : 4459204 (0.4459204) [oracle@rac1 RMAN_BACKUP]$     RMAN> BACKUP VALIDATE DATABASE ;   Starting backup at 19-AUG-15 starting full resync of recovery catalog full resync complete using channel ORA_DISK_1 using channel ORA_DISK_2 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=+DATA/rac/datafile/sysaux.270.886950681 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/rac/datafile/system.269.888079437 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 2    OK     0              20671        140898          4468981     File Name: +DATA/rac/datafile/sysaux.270.886950681   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              42763             Index      0              40248             Other      0              37118             channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1    OK     0              14197        97286           4468934     File Name: +DATA/rac/datafile/system.269.888079437   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              65499             Index      0              13507             Other      0              4077              channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set RMAN-03009: failure of backup command on ORA_DISK_2 channel at 08/19/2015 10:06:10 ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/home/oracle/RMAN_BACKUP/bad_data_01.dbf' channel ORA_DISK_2 disabled, job failed on it will be run on another channel channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 5    OK     0              31371        44324           3340873     File Name: +DATA/rac/datafile/example.277.886950781   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              4547              Index      0              1149              Other      0              7253              channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/home/oracle/RMAN_BACKUP/bad_data_01.dbf input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.271.886950681 input datafile file number=00004 name=+DATA/rac/datafile/users.272.888075725 input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.278.886950995 channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3    OK     0              1            13445           4469039     File Name: +DATA/rac/datafile/undotbs1.271.886950681   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              0                 Index      0              0                 Other      0              13439             File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4    OK     0              206          1148            4329560     File Name: +DATA/rac/datafile/users.272.888075725   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              418               Index      0              40                Other      0              456               File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 6    OK     0              1            6403            4469051     File Name: +DATA/rac/datafile/undotbs2.278.886950995   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              0                 Index      0              0                  Other      0              6399              File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 7    FAILED 0              1121         1280            4459204     File Name: /home/oracle/RMAN_BACKUP/bad_data_01.dbf   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              27                Index      0              0                 Other      1              132               validate found one or more corrupt blocks See trace file /u01/app/oracle/diag/rdbms/rac/rac1/trace/rac1_ora_15789.trc for details 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: backup set complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type    Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- Control File OK     0              1172            channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type    Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- SPFILE       OK     0              2               Finished backup at 19-AUG-15   RMAN>       SQL> select * from  V$DATABASE_BLOCK_CORRUPTION;        FILE#     BLOCK#    BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ ---------          7        156          1                 0 CORRUPT       RMAN> run {blockrecover datafile 7 block 156;} 손상된 블록에 대한 복구를 시도한다.   Starting recover at 19-AUG-15 starting full resync of recovery catalog full resync complete using channel ORA_DISK_1 using channel ORA_DISK_2   channel ORA_DISK_1: restoring block(s) from datafile copy /home/oracle/RMAN_BACKUP/bad_data_01.dbf_good   starting media recovery media recovery complete, elapsed time: 00:00:07   Finished recover at 19-AUG-15   RMAN> ---alertlog---- 현재 데이터가 적어 온라인 redo로복구가 완료 되었다. Started Block Media Recovery Recovery of Online Redo Log: Thread 1 Group 1 Seq 125 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_1.274.886950771 Recovery of Online Redo Log: Thread 2 Group 3 Seq 139 Reading mem 0   Mem# 0: +DATA/rac/onlinelog/group_3.279.886951099 Completed Block Media Recovery     SQL> select * from test where password='P7777';   USERNAME  PASSWO --------- ------ TEST7777  P7777     [참고자료] Quick guide RMAN corrupt block recover steps (문서 ID 1428823.1) RMAN : Block-Level Media Recovery - Concept & Example (문서 ID 144911.1)

 

 

18)                  11gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning

n  RAC(ASM)에서 싱글DB(non ASM)으로 복제DB를 구성하다.

11gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning. Source = 2 node rac cluster with asm  SID=rac Target = single instance non asm      SID=dup(to be made)   Notes:   1) duplication기능을 사용 하여 운영과 같은 복제시스템을 구성 할수 있다.(11gR2 feature).   RMAN을 사용하여 복제 DB를 구성 할 수 있다.   2) Oracle Managed Files 로 설정 할 경우 아래 파라미터를 통해서 설정한다..      아래 DEST 정보를 파라미터에 세팅하여 이동할 정보를 작성한다.   2.1) db_create_file_dest---------------->> This directory will contain data,redo & control files. 2.2) db_recovery_file_dest-------------->> This will contain Flash recover Area.   3) OMF를 사용 하지 않을 경우 아래 파라미터를 추가 한다.   3.1) control_files 3.2) db_file_name_convert 3.3) log_file_name_convert     Source에서의 작업   1) rac database 는 archive log mode. 2) 패스워드 파일을 복제될 대상으로 COPY한다.. [oracle@rac1 dbs]$ scp orapwrac1 catalog:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup The authenticity of host 'catalog (192.168.0.2)' can't be established. RSA key fingerprint is 6d:8e:f5:6e:6f:38:2e:8e:be:6b:16:66:83:cc:23:68. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'catalog,192.168.0.2' (RSA) to the list of known hosts. oracle@catalog's password: orapwrac1       양쪽 노드에 추가 하여 한다.   [oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora.rac1 Network Configuration File: /u01/app/oracle/product/11.2.0/ dbhome_1/network/admin/tnsnames.ora.rac1 # Generated by Oracle configuration tools.   DUP =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1522))     )     (CONNECT_DATA =       (SERVICE_NAME = dup)     )   )     Copy the modified tnsnames.ora file to target.      >> tnsnames.ora 파일을 대상 서버로 복사한다.   [oracle@rac1 admin]$ scp tnsnames.ora 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/network/admin/ oracle@192.168.1.69 s password: tnsnames.ora                                                100%  533     0.5KB/s   00:00   Source에서의 작업   [oracle@rac1 admin]$ sql   SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 25 13:43:48 2015   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options   >> RAC DB의 파라미터를 수정하여 대상 서버를 구상 할것이다.   SQL> create pfile from spfile;   File created.     SOURCE :   [oracle@rac1 dbs]$ cat initrac1.ora rac2.__db_cache_size=390070272 rac1.__db_cache_size=478150656 rac2.__java_pool_size=4194304 rac1.__java_pool_size=4194304 rac2.__large_pool_size=16777216 rac1.__large_pool_size=16777216 rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment rac2.__pga_aggregate_target=310378496 rac1.__pga_aggregate_target=310378496 rac2.__sga_target=931135488 rac1.__sga_target=931135488 rac2.__shared_io_pool_size=0 rac1.__shared_io_pool_size=0 rac2.__shared_pool_size=511705088 rac1.__shared_pool_size=423624704 rac2.__streams_pool_size=0 rac1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/rac/adump' *.audit_trail='db' *.cluster_database=TRUE *.compatible='11.2.0.4.0' *.control_files='+DATA/rac/controlfile/current.273.886950769' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='rac' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)' rac1.instance_number=1 rac2.instance_number=2 *.log_archive_dest_1='LOCATION=/home/oracle/ARC' rac1.log_archive_dest_1='LOCATION=/home/oracle/ARC1' rac2.log_archive_dest_1='LOCATION=/home/oracle/ARC2' rac2.log_archive_dest_1='LOCATION=/home/oracle/ARC1' rac1.log_archive_dest_1='LOCATION=/home/oracle/ARC2' *.log_archive_dest_2='' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=309329920 *.processes=150 *.remote_listener='crs-scan:1521' *.remote_login_passwordfile='exclusive' *.sga_target=927989760 rac2.thread=2 rac1.thread=1 rac1.undo_tablespace='UNDOTBS1' rac2.undo_tablespace='UNDOTBS2'     TARGET :RAC관련된 파라미터는 제거 한다.   dup.__db_cache_size=29360128 dup.__java_pool_size=4194304 dup.__large_pool_size=4194304 dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment dup.__pga_aggregate_target=109051904 dup.__sga_target=155189248 dup.__shared_io_pool_size=0 dup.__shared_pool_size=113246208 dup.__streams_pool_size=0 *.audit_file_dest='/u02/app/oracle/admin/dup/adump' *.audit_trail='DB' *.compatible='11.2.0.0.0' *.db_block_size=8192 *.db_create_file_dest='/u02/app/oracle/oradata' *.db_domain='example.com' *.db_name='dup' *.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)' *.memory_target=262144000 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' *.undo_tablespace='UNDOTBS1'       TARGET에서 작업한다.   1) .profile를 설정한다.   ----------------------------------snip------------------------------------ #ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=dup; export ORACLE_SID PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH ------------------------------------snip------------------------------------   2) 리스너를 생성하고시작한다..   [oracle@dup admin]$ cat listener.ora   # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0 /db_1/network/admin/listener.ora # Generated by Oracle configuration tools.   SID_LIST_DUP =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = dup)       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)       (SID_NAME = dup)     )   )   DUP =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1522))   )   ADR_BASE_LISTENER = /u01/app/oracle       3) 관련 디렉토리를 구성한다.   [oracle@dup ~]$ mkdir -p /u02/app/oracle/admin/dup/adump [oracle@dup ~]$ mkdir -p /u02/app/oracle/oradata [oracle@dup ~]$ mkdir -p /u02/app/oracle/flash_recovery_area       4) tnsping 을 통해서 양쪽 노드에서 확인한다..   tnsping rac1 tnsping dup   5) 수정된 파라미터로 복제될 서버에서 nmount한다..   [oracle@dup ~]$ echo $ORACLE_SID dup   [oracle@dup ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.1.0 Production on Tue May 28 01:24:39 2013   Copyright (c) 1982, 2009, Oracle.  All rights reserved.   Connected to an idle instance.   SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdup.ora';   File created.   SQL> startup nomount ORACLE instance started.   Total System Global Area  263639040 bytes Fixed Size                  1335892 bytes Variable Size             230690220 bytes Database Buffers           29360128 bytes Redo Buffers                2252800 bytes     6) 복제될 서버에서 RMAN을 특정 옵션으로 원격지 서버로 접속한다.   [oracle@dup ~]$ echo $ORACLE_SID dup [oracle@catalog ~]$ rman target sys/oracle@rac auxiliary sys/oracle@dup catalog rman/rman@cat   Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 25 14:31:32 2015   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.   connected to target database: RAC (DBID=2474932785) connected to recovery catalog database connected to auxiliary database: DUP (not mounted)   RMAN> duplicate database to dup from active database nofilenamecheck;   ------------------------------------------------ 로그 정보 -------------------------------------------- Starting Duplicate Db at 25-AUG-15 co-nfiguration for SBT_TAPE channel 2 is ignored allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=134 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=10 device type=DISK   contents of Memory Script: {    sql clone "alter system set  control_files =   ''/u02/app/oracle/oradata/DUP/controlfile/o1_mf_bxqzg06b_.ctl'' comment=  ''Set by RMAN'' scope=spfile";    sql clone "alter system set  db_name =  ''RAC'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    sql clone "alter system set  db_unique_name =  ''DUP'' comment=  ''Modified by RMAN duplicate'' scope=spfile";    shutdown clone immediate;    startup clone force nomount    backup as copy current controlfile auxiliary format  '/u02/app/oracle/oradata/DUP/controlfile/o1_mf_bxqzg06h_.ctl';    sql clone "alter system set  control_files =   ''/u02/app/oracle/oradata/DUP/controlfile/o1_mf_bxqzg06h_.ctl'' comment=  ''Set by RMAN'' scope=spfile";    shutdown clone immediate;    startup clone nomount;    alter clone database mount; } executing Memory Script   sql statement: alter system set  control_files =   ''/u02/app/oracle/oradata/DUP/controlfile/o1_mf_bxqzg06b_.ctl'' comment= ''Set by RMAN'' scope=spfile   sql statement: alter system set  db_name =  ''RAC'' comment= ''Modified by RMAN duplicate'' scope=spfile   sql statement: alter system set  db_unique_name =  ''DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile   Oracle instance shut down   Oracle instance started   Total System Global Area     263639040 bytes   Fixed Size                     1364000 bytes Variable Size                218107872 bytes Database Buffers              37748736 bytes Redo Buffers                   6418432 bytes   Starting backup at 25-AUG-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 instance=rac1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=110 instance=rac2 device type=DISK channel ORA_DISK_1: starting datafile copy copying current control file output file name=+DATA/snapcf_rac.f tag=TAG20150825T143152 RECID=10 STAMP=888676314 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 25-AUG-15   sql statement: alter system set  control_files =   ''/u02/app/oracle/oradata/DUP/controlfile/o1_mf_bxqzg06h_.ctl'' comment= ''Set by RMAN'' scope=spfile   Oracle instance shut down   connected to auxiliary database (not started) Oracle instance started   Total System Global Area     263639040 bytes   Fixed Size                     1364000 bytes Variable Size                218107872 bytes Database Buffers              37748736 bytes Redo Buffers                   6418432 bytes   database mounted   contents of Memory Script: {    set newname for clone datafile  1 to new;    set newname for clone datafile  2 to new;    set newname for clone datafile  3 to new;    set newname for clone datafile  4 to new;    set newname for clone datafile  5 to new;    set newname for clone datafile  6 to new;    backup as copy reuse    datafile  1 auxiliary format new    datafile  2 auxiliary format new    datafile  3 auxiliary format new    datafile  4 auxiliary format new    datafile  5 auxiliary format new    datafile  6 auxiliary format new    ;    sql 'alter system archive log current'; } executing Memory Script   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   executing command: SET NEWNAME   Starting backup at 25-AUG-15 using channel ORA_DISK_2 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/rac/datafile/sysaux.270.886950681 channel ORA_DISK_2: starting datafile copy input datafile file number=00001 name=+DATA/rac/datafile/system.269.888079437 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_system_8mqfg7vp_.dbf tag=TAG20150825T143224 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_2: starting datafile copy input datafile file number=00005 name=+DATA/rac/datafile/example.277.886950781 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf tag=TAG20150825T143224 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:00 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/rac/datafile/undotbs1.271.886950681 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf tag=TAG20150825T143224 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:29 channel ORA_DISK_2: starting datafile copy input datafile file number=00006 name=+DATA/rac/datafile/undotbs2.278.886950995 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf tag=TAG20150825T143224 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:11 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/rac/datafile/users.272.888075725 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_8pqfg81p_.dbf tag=TAG20150825T143224 channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:07 output file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf tag=TAG20150825T143224 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 25-AUG-15   sql statement: alter system archive log current   contents of Memory Script: {    backup as copy reuse    archivelog like  "/home/oracle/ARC1/1_154_886950771.dbf" auxiliary format  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf"   archivelog like  "/home/oracle/ARC1/1_153_886950771.dbf" auxiliary format  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_153_886950771.dbf"   archivelog like  "/home/oracle/ARC2/2_186_886950771.dbf" auxiliary format  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf"   ;    catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf";    catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_153_886950771.dbf";    catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf";    switch clone datafile all; } executing Memory Script   Starting backup at 25-AUG-15 using channel ORA_DISK_2 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=154 RECID=499 STAMP=888676421 channel ORA_DISK_2: starting archived log copy input archived log thread=1 sequence=153 RECID=498 STAMP=888659844 output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting archived log copy input archived log thread=2 sequence=186 RECID=500 STAMP=888676422 output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_153_886950771.dbf RECID=0 STAMP=0 channel ORA_DISK_2: archived log copy complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:07 Finished backup at 25-AUG-15   cataloged archived log archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf RECID=499 STAMP=888676434   cataloged archived log archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_153_886950771.dbf RECID=500 STAMP=888676434   cataloged archived log archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf RECID=501 STAMP=888676434   datafile 1 switched to datafile copy input datafile copy RECID=10 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_system_8mqfg7vp_.dbf datafile 2 switched to datafile copy input datafile copy RECID=11 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf datafile 3 switched to datafile copy input datafile copy RECID=12 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf datafile 4 switched to datafile copy input datafile copy RECID=13 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf datafile 5 switched to datafile copy input datafile copy RECID=14 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf datafile 6 switched to datafile copy input datafile copy RECID=15 STAMP=888676435 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_8pqfg81p_.dbf   contents of Memory Script: {    set until scn  6054841;    recover    clone database     delete archivelog    ; } executing Memory Script   executing command: SET until clause   Starting recover at 25-AUG-15 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=135 device type=DISK allocated channel: ORA_AUX_DISK_2 channel ORA_AUX_DISK_2: SID=10 device type=DISK   starting media recovery   archived log for thread 1 with sequence 154 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf archived log for thread 2 with sequence 186 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_154_886950771.dbf thread=1 sequence=154 archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_186_886950771.dbf thread=2 sequence=186 media recovery complete, elapsed time: 00:00:01 Finished recover at 25-AUG-15 Oracle instance started   Total System Global Area     263639040 bytes   Fixed Size                     1364000 bytes Variable Size                218107872 bytes Database Buffers              37748736 bytes Redo Buffers                   6418432 bytes   contents of Memory Script: {    sql clone "alter system set  db_name =  ''DUP'' comment=  ''Reset to original value by RMAN'' scope=spfile";    sql clone "alter system reset  db_unique_name scope=spfile";    shutdown clone immediate;    startup clone nomount; } executing Memory Script   sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile   sql statement: alter system reset  db_unique_name scope=spfile   Oracle instance shut down   connected to auxiliary database (not started) Oracle instance started   Total System Global Area     263639040 bytes   Fixed Size                     1364000 bytes Variable Size                218107872 bytes Database Buffers              37748736 bytes Redo Buffers                   6418432 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG   MAXLOGFILES    192   MAXLOGMEMBERS      3   MAXDATAFILES     1024   MAXINSTANCES    32   MAXLOGHISTORY      292  LOGFILE   GROUP   1  SIZE 50 M ,   GROUP   2  SIZE 50 M  DATAFILE   '/u02/app/oracle/oradata/DUP/datafile/o1_mf_system_8mqfg7vp_.dbf'  CHARACTER SET AL32UTF8   sql statement: ALTER DATABASE ADD LOGFILE     INSTANCE 'i2'   GROUP   3  SIZE 50 M ,   GROUP   4  SIZE 50 M   contents of Memory Script: {    set newname for clone tempfile  1 to new;    switch clone tempfile all;    catalog clone datafilecopy  "/u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf",  "/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf",  "/u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf",  "/u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf",  "/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_8pqfg81p_.dbf";    switch clone datafile all; } executing Memory Script   executing command: SET NEWNAME   renamed tempfile 1 to /u02/app/oracle/oradata/DUP/datafile/o1_mf_temp_%u_.tmp in control file   cataloged datafile copy datafile copy file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf RECID=1 STAMP=888676469 cataloged datafile copy datafile copy file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf RECID=2 STAMP=888676469 cataloged datafile copy datafile copy file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf RECID=3 STAMP=888676469 cataloged datafile copy datafile copy file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf RECID=4 STAMP=888676469 cataloged datafile copy datafile copy file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_8pqfg81p_.dbf RECID=5 STAMP=888676469   datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=888676469 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=888676469 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=888676469 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=888676469 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf datafile 6 switched to datafile copy input datafile copy RECID=5 STAMP=888676469 file name=/u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_8pqfg81p_.dbf Reenabling controlfile options for auxiliary database Executing: alter database enable block change tracking   contents of Memory Script: {    Alter clone database open resetlogs; } executing Memory Script   database opened Finished Duplicate Db at 25-AUG-15 ----------------------------------------------------복제완료-------------------------------------------- RMAN>     7) Single 로 된 서버이므로 rac 환경을 disable 한다..       [oracle@catalog ~]$ sql   SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 25 14:45:53 2015   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning and Real Application Testing options   SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning and Real Application Testing options [oracle@catalog ~]$ echo $ORACLE_SID dup [oracle@catalog ~]$ sql   SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 25 14:46:00 2015   Copyright (c) 1982, 2013, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning and Real Application Testing options   SQL> select thread#,status,enabled from v$thread;      THREAD# STATUS ENABLED ---------- ------ --------             1 OPEN   PUBLIC             2 CLOSED PUBLIC   SQL> select group# from v$log where thread#=2;       GROUP# ----------             3             4   SQL> alter database disable thread 2;   Database altered.   SQL> alter database drop logfile group 3;   Database altered.   SQL> alter database drop logfile group 4;   Database altered.   SQL> select thread#, status, enabled from v$thread;      THREAD# STATUS ENABLED ---------- ------ --------             1 OPEN   PUBLIC   SQL> show parameter undo   NAME                                        TYPE          VALUE ------------------------------------ ----------- ------------------------------ undo_management                        string        AUTO undo_retention                             integer       900 undo_tablespace                           string        UNDOTBS1 SQL> select tablespace_name from dba_tablespaces;   TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS UNDOTBS2 EXAMPLE   7 rows selected.   SQL> drop tablespace UNDOTBS2 including contents and datafiles;   Tablespace dropped.   col file_name format a80 col tablespace_name format a10 SQL> set linesize 150   SQL> select tablespace_name,file_name,status from dba_temp_files;   TABLESPACE FILE_NAME                                                                                           STATUS ---------- -------------------------------------------------------------------------------- --------- SYSTEM    /u02/app/oracle/oradata/DUP/datafile/o1_mf_system_8mqfg7vp_.dbf                         AVAILABLE SYSAUX    /u02/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_8lqfg7vp_.dbf                AVAILABLE UNDOTBS1   /u02/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_8oqfg81l_.dbf                    AVAILABLE USERS      /u02/app/oracle/oradata/DUP/datafile/o1_mf_users_8qqfg820_.dbf                AVAILABLE EXAMPLE    /u02/app/oracle/oradata/DUP/datafile/o1_mf_example_8nqfg80s_.dbf                      AVAILABLE