백업 & 복구 40 - ASM : RMAN으로 백업과 복구

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 시작
장애 1: 잘못된 update 후 commit 으로 인한 장애 복구하기
 
 

 
사전작업 ------->>>>>>>>>>>>>>
 
 
1. 현재 상태 확인
 

SQL> !vi dd.sql
set line 200
col tablespace_name for a10
col file_name for a50
select tablespace_name, round(bytes/1024/1024,1) MB, file_name
from dba_data_files
/
 
SQL> @dd
TABLESPACE   MB FILE_NAME
---------- ---- --------------------------------------------------
SYSTEM      300 +FRA/testdb/datafile/system.262.776456133
UNDOTBS1    200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX      120 +DATA/testdb/datafile/sysaux.262.776365365
USERS         5 +DATA/testdb/datafile/users.264.776365391
TS_NEW       10 +FRA/testdb/datafile/ts_new.260.776454711
TS_NEW        5 +FRA/testdb/datafile/ts_new.261.776454737

 
 
 

SQL> !vi log.sql
set line 200;
col member for a50
col mb for 999
col sequence# for 999
col satus for a8
col archived for a5
select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence#, b.archived, b.status, b.first_change#
from v$logfile a, v$log b
where a.group#=b.group#
order by 1,2
/
 
 
    GROUP# MEMBER                                               MB SEQUENCE# ARCHI STATUS           FIRST_CHANGE#
---------- -------------------------------------------------- ---- --------- ----- ---------------- -------------
         1 +DATA/testdb/onlinelog/group_1.257.776365295         50        13 NO    CURRENT                 230873
         1 +FRA/testdb/onlinelog/group_1.257.776365299          50        13 NO    CURRENT                 230873
         2 +DATA/testdb/onlinelog/group_2.258.776365301         50        11 NO    INACTIVE                172049
         2 +FRA/testdb/onlinelog/group_2.258.776365303          50        11 NO    INACTIVE                172049
         3 +DATA/testdb/onlinelog/group_3.259.776365307         50        12 NO    INACTIVE                206461
         3 +FRA/testdb/onlinelog/group_3.259.776365311          50        12 NO    INACTIVE                206461
 
6 rows selected.

 
 
 

SQL> archive log list;
 
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Current log sequence           13

  
▶ RMAN을 사용하기 위해 아카이브 로그 모드로 변경하여야 한다.
 

※ 참고 : Archive log mode 로 변경 방법
2012/01/30 - [Study/Oracle - 백업&복구] - 백업&복구 1 - No Archive log mode 와 Archive log mode

 
해당 디렉토리 먼저 생성
$ mkdir -p /data/arc1
$ mkdir -p /data/arc2

** spfile이면
SQL> alter system set log_archive_dest_1='location=/data/arc1';
SQL> alter system set log_archive_dest_2='location=/data/arc2';
 
** pfile이면 :
$ vi $ORACLE_HOME/dbs/inittestdb.ora
log_archive_dest_1='location=/data/arc1'
log_archive_dest_2='location=/data/arc2'
log_archive_format='%s_%t_%r.arc'
:wq!
  
SQL> shutdown immediate;
 
SQL> startup mount;
 
SQL> alter database archivelog;
 
SQL> alter database open;

 
 
 
 
 
2. 전체 백업 수행
 

RMAN>
backup as compressed backupset
format '/data/backup/rman/%U_%T'
database;
 
 
Starting backup at 29-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+FRA/testdb/datafile/system.262.776456133
input datafile fno=00002 name=+DATA/testdb/datafile/undotbs1.261.776365357
input datafile fno=00003 name=+DATA/testdb/datafile/sysaux.262.776365365
input datafile fno=00005 name=+FRA/testdb/datafile/ts_new.260.776454711
input datafile fno=00004 name=+DATA/testdb/datafile/users.264.776365391
input datafile fno=00006 name=+FRA/testdb/datafile/ts_new.261.776454737
channel ORA_DISK_1: starting piece 1 at 29-FEB-12
channel ORA_DISK_1: finished piece 1 at 29-FEB-12
piece handle=/data/backup/rman/04n4g8dj_1_1_20120229 tag=TAG20120229T003915 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 29-FEB-12
channel ORA_DISK_1: finished piece 1 at 29-FEB-12
piece handle=/data/backup/rman/05n4g8e2_1_1_20120229 tag=TAG20120229T003915 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-FEB-12

 
 
 
장애과정 ------->>>>>>>>>>>>>>
 
 
3. 테스트용 테이블(scott.tt100) 생성 후 데이터 입력
 

SQL> create table scott.tt100 (no number) tablespace ts_new;
 
SQl> insert into scott.tt100 values (1);
SQL> insert into scott.tt100 values (2);
SQL> commit;
 
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YY
-------------------
2012-02-29:00:45:50
 
 
SQL> select * from scott.tt100;
 
        NO
----------
         1
         2

 
 

※ scott 유저가 없다면? 만들기!!
SQL> create user scott identified by tiger;
User created.
 
SQL> grant dba to scott;
Grant succeeded.

 
 
 
 
 
4. 장애 발생 - 잘못된 update 후 commit
 

SQL> update scott.tt100 set no=1000;
 
2 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from scott.tt100;
 
        NO
----------
      1000
      1000

 
 
 
복구과정 ------->>>>>>>>>>>>>>
 
5. 임시 경로를 활용하여 복구하기
 

- 복구경로 : /data/temp
- 복구순서
  1) 현재 장애를 저장하고 있는 리두로그를 아카이브 로그로 만들어서 저장
  2) 현재 SCN 정보를 저장하고 있는 Current control file 을 백업받아서 복구 경로로 복원
  3) 파라미터 파일에서 control file 경로 변경 후 DB 종료
  4) 백업된 데이터 파일을 복구 경로로 복원 후 control file 재생성
  5) Recover 수행 (시간기반 복구)
  6) 복구된 테이블 exp 수행해서 운영 DB Open 후 import

 
 
 
1) 현재 장애를 저장하고 있는 리두로그를 아카이브 로그로 만들어서 저장
 : redo log 를 복사할 수 없기 떄문에 아카이브 로그로 만들어서 복구에 사용
 

SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.

 
 
 
2) 현재 SCN 정보를 저장하고 있는 Current control file 을 백업받아서 복구 경로로 복원

- 디렉토리 만들고
$ mkdir -p /data/temp/
 
SQL> alter database backup controlfile to '/data/temp/control01.ctl';
 
Database altered.

 
 
 
3) 파라미터 파일에서 control file 경로 변경 후 DB 종료
 
① 원래 컨트롤파일 조회

SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
+DATA/testdb/controlfile/current.256.776365293
+FRA/testdb/controlfile/current.256.776365293

 
 
② 파라미터 파일 수정

- spfile 경우
 
SQL> alter system set control_files='/data/temp/control01.ctl' scope=spfile;
 
- pfile 경우
$ vi $ORACLE_HOME/dbs/inittestdb.ora
기존부분 주석처리후 새경로 입력
#*.control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293'
*.control_files='/data/temp/control01.ctl'
 
 
SQL> shutdown immediate;
 
SQL> startup mount;
 
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
/data/temp/control01.ctl

 
 
 
 
4) 백업된 데이터 파일을 복구 경로로 복원 후 control file 재생성
 
 
① 백업된 데이터 파일을 복구 경로로 복원
 

RMAN> report schema;
 
Starting implicit crosscheck backup at 29-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 29-FEB-12
 
Starting implicit crosscheck copy at 29-FEB-12
using channel ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck copy at 29-FEB-12
 
searching for all files in the recovery area
cataloging files...
no files cataloged
 
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    300      SYSTEM               ***     +FRA/testdb/datafile/system.262.776456133
2    200      UNDOTBS1             ***     +DATA/testdb/datafile/undotbs1.261.776365357
3    120      SYSAUX               ***     +DATA/testdb/datafile/sysaux.262.776365365
4    5        USERS                ***     +DATA/testdb/datafile/users.264.776365391
5    10       TS_NEW               ***     +FRA/testdb/datafile/ts_new.260.776454711
6    5        TS_NEW               ***     +FRA/testdb/datafile/ts_new.261.776454737
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/testdb/tempfile/temp.263.776365373

 
 

RMAN>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
 
set newname for datafile 1 to '/data/temp/system01.dbf';
set newname for datafile 2 to '/data/temp/undotbs01.dbf';
set newname for datafile 3 to '/data/temp/sysaux01.dbf';
set newname for datafile 4 to '/data/temp/users01.dbf';
set newname for datafile 5 to '/data/temp/ts_new01.dbf';
set newname for datafile 6 to '/data/temp/ts_new02.dbf';
 
restore database;
 
switch datafile all;
}
 
 
 
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: sid=155 devtype=DISK
 
allocated channel: ch2
channel ch2: sid=154 devtype=DISK
 
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 restore at 29-FEB-12
 
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data/temp/system01.dbf
restoring datafile 00002 to /data/temp/undotbs01.dbf
restoring datafile 00003 to /data/temp/sysaux01.dbf
restoring datafile 00004 to /data/temp/users01.dbf
restoring datafile 00005 to /data/temp/ts_new01.dbf
restoring datafile 00006 to /data/temp/ts_new02.dbf
channel ch1: reading from backup piece /data/backup/rman/04n4g8dj_1_1_20120229
channel ch1: restored backup piece 1
piece handle=/data/backup/rman/04n4g8dj_1_1_20120229 tag=TAG20120229T003915
channel ch1: restore complete, elapsed time: 00:00:46
Finished restore at 29-FEB-12
 
datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=776480988 filename=/data/temp/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=776480988 filename=/data/temp/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=776480988 filename=/data/temp/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=776480988 filename=/data/temp/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=776480988 filename=/data/temp/ts_new01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=776480988 filename=/data/temp/ts_new02.dbf
released channel: ch1
released channel: ch2

▶ 데이터파일 복원완료
 
 
② 현재상태 조회

SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/data/temp/system01.dbf
/data/temp/undotbs01.dbf
/data/temp/sysaux01.dbf
/data/temp/users01.dbf
/data/temp/ts_new01.dbf
/data/temp/ts_new02.dbf
 
6 rows selected.
 
  
SQL> select member from v$logfile;
 
MEMBER
--------------------------------------------------------------------------------
+DATA/testdb/onlinelog/group_1.257.776365295
+FRA/testdb/onlinelog/group_1.257.776365299
+DATA/testdb/onlinelog/group_2.258.776365301
+FRA/testdb/onlinelog/group_2.258.776365303
+DATA/testdb/onlinelog/group_3.259.776365307
+FRA/testdb/onlinelog/group_3.259.776365311
 
6 rows selected.

▶ 조회결과
data file = 임시경로의 데이터파일 사용
redo log file = ASM기반의 현재 운영중인 것 사용

※ 참고
: 기존 복구의 경우 사용중인 리두로그파일도 shutdown 상태에서 cp로 복사후 rename해서 그대로 사용할수 있었다.
 
복구 후 resetlogs 옵션으로 open 해야 하는데, 그렇게 되면 사용중인 redo log file이 초기화 되어 현재 DB에 문제가 생긴다.
 
기존방법처럼 redo log file을 임시경로로 옮기는방법이 가장 좋으나, 10g ASM 까지는 cp 명령이 지원 안된다.
RMAN에서도 online redo log 를 백업이나 복사하지 못한다.
 
해결법 : 복구해야 하는 내용을 archive redo log file로 들어가게 만든후(위에서 로그스위치 일으켜서 작업완료),
           control file 을 재 생성해서 redo log file의 경로를 /data/temp/ 로 만든후 복구하고,
           resetlogs로 open

 
 
 
③ 컨트롤 파일 재생성

SQL> alter database backup controlfile to trace as '/data/temp/re.sql';
 
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
 
Database dismounted.
ORACLE instance shut down.

 
 

$ vi /data/temp/re.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/data/temp/redo01.log'
  ) SIZE 50M,
  GROUP 2 (
    '/data/temp/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/data/temp/redo03.log'
  ) SIZE 50M
DATAFILE
  '/data/temp/system01.dbf',
  '/data/temp/undotbs01.dbf',
  '/data/temp/sysaux01.dbf',
  '/data/temp/users01.dbf',
  '/data/temp/ts_new01.dbf',
  '/data/temp/ts_new02.dbf'
CHARACTER SET KO16MSWIN949

 
 

SQL> @/data/temp/re.sql
ORACLE instance started.
 
Total System Global Area  285212672 bytes
Fixed Size                  1273276 bytes
Variable Size              92275268 bytes
Database Buffers          188743680 bytes
Redo Buffers                2920448 bytes
 
Control file created.

 
 
 
 
5) Recover 수행 (시간기반 복구)
 

$ rman target /
 
RMAN>
run {
sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
set until time='2012-02-29:00:45:50';
recover database;
alter database open resetlogs;
}
 
 
 
using target database control file instead of recovery catalog
sql statement: alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"
 
executing command: SET until clause
 
Starting recover at 29-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
 
starting media recovery
 
archive log filename=/data/arc2/15_1_776365287.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 29-FEB-12
 
database opened

 
 
 

SQL> select status from v$instance;
 
STATUS
------------
OPEN
 
 
SQL> select * from scott.tt100;
 
        NO
----------
         1
         2
 
▶ 임시로 복구 완료되었음

 
 
 

 
6) 복구된 테이블 exp 수행해서 운영 DB Open 후 import
  
① 복구된 테이블 exp로 안전하게 빼두기
 

$ exp scott/tiger tables=tt100 file=tt100.dmp
 
Export: Release 10.2.0.5.0 - Production on Wed Feb 29 01:36:15 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
 
About to export specified tables via Conventional Path ...
. . exporting table                          TT100          2 rows exported
Export terminated successfully without warnings.

 
 
② 컨트롤파일 위치 원래대로 돌리기

- spfile 경우
 
SQL> alter system set control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293' scope=spfile;
 

- pfile 경우
 
$ vi $ORACLE_HOME/dbs/inittestdb.ora
기존내용 살리고 임시경로 지정해준것 지우거나 주석처리
*.control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293'
#*.control_files='/data/temp/control01.ctl'

 
 
 
③ 원래 컨트롤 파일로 바꾸고 재 open후 장애 테이블 확인
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  285212672 bytes
Fixed Size                  1273276 bytes
Variable Size              92275268 bytes
Database Buffers          188743680 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
 
 
SQL> select * from scott.tt100;
 
        NO
----------
      1000
      1000

※ 참고
 : 컨트롤 파일을 원래대로 돌리는 것 만으로 이전 장애 상태의 DB로 돌아갈 수 있다.
   현재 임시폴더 (/data/temp/)에는 복구용 파일들이 남아 있지만 못쓰게 된 상태.
   컨트롤 파일의 정보대로 DB가 돌아왔다.
 : 우리가 임시로 복구한 DB는 (백업DATA file /새로만든control file/아카이브로그 참조해서 자료 살리고
   redo log file은 새로 만들어짐) 의 상태였다.
   기존 control file / 기존 DATA file / 기존 redo log file 들은 정보가 변화없이 동결되어 있던 상태에서 그대로 열린것이다.
 
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
+FRA/testdb/datafile/system.262.776456133
+DATA/testdb/datafile/undotbs1.261.776365357
+DATA/testdb/datafile/sysaux.262.776365365
+DATA/testdb/datafile/users.264.776365391
+FRA/testdb/datafile/ts_new.260.776454711
+FRA/testdb/datafile/ts_new.261.776454737
 
6 rows selected.
 
 
SQL> select member from v$logfile;
 
MEMBER
--------------------------------------------------------------------------------
+DATA/testdb/onlinelog/group_1.257.776365295
+FRA/testdb/onlinelog/group_1.257.776365299
+DATA/testdb/onlinelog/group_2.258.776365301
+FRA/testdb/onlinelog/group_2.258.776365303
+DATA/testdb/onlinelog/group_3.259.776365307
+FRA/testdb/onlinelog/group_3.259.776365311
 
6 rows selected.

 
 
④ 장애난 데이터 삭제
 : 필요에 따라 drop / truncate 도 가능

SQL> drop table scott.tt100;
 
Table dropped.

 
  
 
⑤ imp 로 테이블 살리기
 

$ imp scott/tiger file=tt100.dmp ignore=y fromuser=scott touser=scott
 
Import: Release 10.2.0.5.0 - Production on Wed Feb 29 01:54:06 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                        "TT100"          2 rows imported
Import terminated successfully without warnings.

 
 
 
⑥ 복구 확인
 

SQL> select * from scott.tt100;
 
        NO
----------
         1
         2
▶ 복구완료

 
 
 
 
 
6. 정리
 : ASM 기반에서 RMAN을 사용하여 잘못된 update 장애를 Flashback을 쓰지않고 복구완료
 : 잘못된 delete 장애도 동일한 방법으로 복구 할 수 있다.
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 끝
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 시작
장애 2: 잘못된 drop table 장애 복구하기
 
 
 
사전작업 ------->>>>>>>>>>>>>>
 
 
1. 현재 상태 확인 후 전체 백업 수행
 

SQL> @dd
 
TABLESPACE_NAME         MB FILE_NAME
--------------- ---------- ------------------------------------------------------------
SYSTEM                 300 +FRA/testdb/datafile/system.262.776456133
UNDOTBS1               200 +DATA/testdb/datafile/undotbs1.261.776365357
SYSAUX                 120 +DATA/testdb/datafile/sysaux.262.776365365
USERS                    5 +DATA/testdb/datafile/users.264.776365391
TS_NEW                  10 +FRA/testdb/datafile/ts_new.260.776454711
TS_NEW                   5 +FRA/testdb/datafile/ts_new.261.776454737
 
6 rows selected.
 
SQL> @log
 
    GROUP# MEMBER                                                     MB       SEQ# ARC STATUS           1st#
---------- -------------------------------------------------- ---------- ---------- --- ---------- ----------
         1 +DATA/testdb/onlinelog/group_1.257.776365295               50         19 YES INACTIVE       244289
         1 +FRA/testdb/onlinelog/group_1.257.776365299                50         19 YES INACTIVE       244289
         2 +DATA/testdb/onlinelog/group_2.258.776365301               50         20 NO  CURRENT        244291
         2 +FRA/testdb/onlinelog/group_2.258.776365303                50         20 NO  CURRENT        244291
         3 +DATA/testdb/onlinelog/group_3.259.776365307               50         18 YES INACTIVE       244287
         3 +FRA/testdb/onlinelog/group_3.259.776365311                50         18 YES INACTIVE       244287
 
6 rows selected.
 
SQL> col name for a50
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------
+DATA/testdb/controlfile/current.256.776365293
+FRA/testdb/controlfile/current.256.776365293
▶ 복구후 다시 사용 할것임

 
 

RMAN>
backup as compressed backupset
format '/data/backup/rman/%U_%T'
database;
 
 
Starting backup at 29-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=140 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+FRA/testdb/datafile/system.262.776456133
input datafile fno=00002 name=+DATA/testdb/datafile/undotbs1.261.776365357
input datafile fno=00003 name=+DATA/testdb/datafile/sysaux.262.776365365
input datafile fno=00005 name=+FRA/testdb/datafile/ts_new.260.776454711
input datafile fno=00004 name=+DATA/testdb/datafile/users.264.776365391
input datafile fno=00006 name=+FRA/testdb/datafile/ts_new.261.776454737
channel ORA_DISK_1: starting piece 1 at 29-FEB-12
channel ORA_DISK_1: finished piece 1 at 29-FEB-12
piece handle=/data/backup/rman/06n4gdpl_1_1_20120229 tag=TAG20120229T021100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 29-FEB-12
channel ORA_DISK_1: finished piece 1 at 29-FEB-12
piece handle=/data/backup/rman/07n4gdqe_1_1_20120229 tag=TAG20120229T021100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 29-FEB-12

 
 
 
 
장애과정 ------->>>>>>>>>>>>>>
 
 
2. 테스트용 테이블(scott.tt100) 생성 후 데이터 입력
 

SQL> create table scott.tt200 (no number) tablespace ts_new;
 
SQl> insert into scott.tt200 values (1);
SQL> insert into scott.tt200 values (2);
SQL> commit;
 
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YY
-------------------
2012-02-29:02:13:27
 
 
SQL> select * from scott.tt200;
 
        NO
----------
         1
         2

 
 
 
 
 
3. drop table 장애 발생
 

SQL> drop table scott.tt200 purge;
 
Table dropped.
 
SQL> select * from scott.tt200;
select * from scott.tt200
                    *
ERROR at line 1:
ORA-00942: table or view does not exist
 

- 아카이브 로그 파일을 사용하기 위해 로그스위치 강제로 발생시켜서 아카이빙 한다.
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.

 
 

 
 
 
복구과정 ------->>>>>>>>>>>>>>
 
 
4. 임시 경로를 활용하여 복구하기

복구경로 : /data/temp2

  

$ mkdir -p /data/temp2

 
 
1) 현재 SCN 정보를 저장하고 있는 Current control file 을 백업받아서 복구 경로로 복원

SQL> alter database backup controlfile to '/data/temp2/control01.ctl';
 
Database altered.

 
 
 
2) 파라미터 파일에서 control file 경로 변경 후 DB 종료

- spfile 경우
SQL> alter system set control_files='/data/temp2/control01.ctl' scope=spfile;
 
- pfile 경우
$ vi $ORACLE_HOME/dbs/inittestdb.ora
기존부분 주석처리후 새경로 입력
#*.control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293'
*.control_files='/data/temp2/control01.ctl'

 
 
 
 
5. MOUNT 까지 재 시작 후 데이터 파일 경로 변경 후 복원하기
 
 

SQL> shutdown immediate;
 
SQL> startup mount;
 
SQL> select name from v$controlfile;
 
NAME
--------------------------------------------------------------------------------
/data/temp2/control01.ctl

 
 
 

RMAN> report schema;
 
Starting implicit crosscheck backup at 29-FEB-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 29-FEB-12
 
Starting implicit crosscheck copy at 29-FEB-12
using channel ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck copy at 29-FEB-12
 
searching for all files in the recovery area
cataloging files...
no files cataloged
 
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    300      SYSTEM               ***     +FRA/testdb/datafile/system.262.776456133
2    200      UNDOTBS1             ***     +DATA/testdb/datafile/undotbs1.261.776365357
3    120      SYSAUX               ***     +DATA/testdb/datafile/sysaux.262.776365365
4    5        USERS                ***     +DATA/testdb/datafile/users.264.776365391
5    10       TS_NEW               ***     +FRA/testdb/datafile/ts_new.260.776454711
6    5        TS_NEW               ***     +FRA/testdb/datafile/ts_new.261.776454737
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/testdb/tempfile/temp.263.776365373
 
 
RMAN>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
 
set newname for datafile 1 to '/data/temp2/system01.dbf';
set newname for datafile 2 to '/data/temp2/undotbs01.dbf';
set newname for datafile 3 to '/data/temp2/sysaux01.dbf';
set newname for datafile 4 to '/data/temp2/users01.dbf';
set newname for datafile 5 to '/data/temp2/ts_new01.dbf';
set newname for datafile 6 to '/data/temp2/ts_new02.dbf';
 
restore database;
 
switch datafile all;
}
 
 
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: sid=155 devtype=DISK
 
allocated channel: ch2
channel ch2: sid=154 devtype=DISK
 
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 restore at 29-FEB-12
 
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data/temp2/system01.dbf
restoring datafile 00002 to /data/temp2/undotbs01.dbf
restoring datafile 00003 to /data/temp2/sysaux01.dbf
restoring datafile 00004 to /data/temp2/users01.dbf
restoring datafile 00005 to /data/temp2/ts_new01.dbf
restoring datafile 00006 to /data/temp2/ts_new02.dbf
channel ch1: reading from backup piece /data/backup/rman/04n4g8dj_1_1_20120229
channel ch1: restored backup piece 1
piece handle=/data/backup/rman/04n4g8dj_1_1_20120229 tag=TAG20120229T003915
channel ch1: restore complete, elapsed time: 00:00:46
Finished restore at 29-FEB-12
 
datafile 1 switched to datafile copy
input datafile copy recid=10 stamp=776480988 filename=/data/temp2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=11 stamp=776480988 filename=/data/temp2/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=12 stamp=776480988 filename=/data/temp2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=13 stamp=776480988 filename=/data/temp2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=14 stamp=776480988 filename=/data/temp2/ts_new01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=15 stamp=776480988 filename=/data/temp2/ts_new02.dbf
released channel: ch1
released channel: ch2
▶ 데이터파일 복원완료

 
 
 
6. redo log 경로 변경을 위해 현재 상태의 control file 을 재생성
 

SQL> alter database backup controlfile to trace as '/data/temp2/re.sql';
 
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
 
Database dismounted.
ORACLE instance shut down.

  

$ vi /data/temp2/re.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/data/temp2/redo01.log'
  ) SIZE 50M,
  GROUP 2 (
    '/data/temp2/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/data/temp2/redo03.log'
  ) SIZE 50M
DATAFILE
  '/data/temp2/system01.dbf',
  '/data/temp2/undotbs01.dbf',
  '/data/temp2/sysaux01.dbf',
  '/data/temp2/users01.dbf',
  '/data/temp2/ts_new01.dbf',
  '/data/temp2/ts_new02.dbf'
CHARACTER SET KO16MSWIN949

  

SQL> @/data/temp2/re.sql
ORACLE instance started.
 
Total System Global Area  285212672 bytes
Fixed Size                  1273276 bytes
Variable Size              92275268 bytes
Database Buffers          188743680 bytes
Redo Buffers                2920448 bytes
 
Control file created.

 
 
 
7. Recover 수행 (시간기반 복구)
 

$ rman target /
 
RMAN>
run {
sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';
set until time='2012-02-29:02:13:27';
recover database;
alter database open resetlogs;
}
 
 
using target database control file instead of recovery catalog
sql statement: alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"
 
executing command: SET until clause
 
Starting recover at 29-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
 
starting media recovery
 
archive log filename=/data/arc2/15_1_776365287.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:03
Finished recover at 29-FEB-12
 
database opened

  
 

SQL> select status from v$instance;
 
STATUS
------------
OPEN
 
 
SQL> select * from scott.tt100;
 
        NO
----------
         1
         2
 
▶ 임시로 복구 완료되었음

 
 
 
 
 
8. 복구된 테이블 exp 수행해서 운영 DB Open 후 import
 
① 복구된 테이블 exp로 안전하게 빼두기
 

$ exp scott/tiger tables=tt200 file=tt200.dmp
 
Export: Release 10.2.0.5.0 - Production on Wed Feb 29 01:36:15 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
 
About to export specified tables via Conventional Path ...
. . exporting table                          TT200          2 rows exported
Export terminated successfully without warnings.

 
 
 
② 컨트롤파일 위치 원래대로 돌리기

- spfile 경우
 
SQL> alter system set control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293' scope=spfile;
 
- pfile 경우
 
$ vi $ORACLE_HOME/dbs/inittestdb.ora
기존내용 살리고 임시경로 지정해준것 지우거나 주석처리
*.control_files='+DATA/testdb/controlfile/current.256.776365293','+FRA/testdb/controlfile/current.256.776365293'
#*.control_files='/data/temp2/control01.ctl'

 
 
 
③ 원래 컨트롤 파일로 바꾸고 재 open후 장애 테이블 확인
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
 
 
SQL> select * from scott.tt200;
select * from scott.tt200
                    *
ERROR at line 1:
ORA-00942: table or view does not exist
복구 전이라 에러 발생

 
 
 
④ imp 로 테이블 살리기
 

$ imp scott/tiger file=tt200.dmp ignore=y fromuser=scott touser=scott
 
Import: Release 10.2.0.5.0 - Production on Wed Feb 29 01:54:06 2012
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Export file created by EXPORT:V10.02.01 via conventional path
import done in KO16MSWIN949 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                        "TT200"          2 rows imported
Import terminated successfully without warnings.

 
 
 
⑤ 복구 확인
 

SQL> select * from scott.tt200;
 
        NO
----------
         1
         2
▶ 복구완료

 
 
 
 
 
9. 정리
 : ASM 기반에서 장애가 발생했을 경우 복구 방법 완료
 : ASM 기반이 파일 시스템 기반과 조금 다르지만 복구 원리는 동일함
 : truncate table 장애, drop user 장애, drop tablespace 장애 등도 모두 원리 동일
 : RAC 기반에서 ASM을 사용할 경우 복구 원리 동일함
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 끝