Export / Import - Clone DB로 삭제된 데이터 복구하는 방법

< 전체 순서 요약 >

1. DB를 Archive log mode로 설정

2. 실제 DB: testdb, Clone DB: testdb2

3. 이 방법은 장애 시점 이전의 Backup file과 Archive log file로 복구를 해내므로 반드시 장애시점 이전의 Backup file과 Archive log file이 존재해야 한다.

< 작업 순서 요약 >

1. Tablespace 생성 후 전체 Tablespace Backup (hot / cold 상관 없음)

2. test Table 생성 후 장애발생 - 실습할 Tablespace에 생성할 것

3. 복구 서버(testdb2)용 Parameter file 생성 (기존 Parameter file 복사 후 몇 개 항목만 변경)

4. 복구 대상 파일을 복구 위치로 복사

5. 복구 서버를 위한 Control file 재 생성 (기존 서버에서 사용중인 Control file을 Trace해서 재 생성에 사용함)

6. mount 상태에서 recover database until time 명령어로 복구

7. exp로 해당 Table을 export 받고 imp로 원본 서버에 import 한다.

< 실제 작업 수행 >

1단계 : Tablespace 생성 후 전체 Backup 받기

SQL> create tablespace clone datafile '/home/oracle/oradata/testdb/clone01.dbf' size 10m;

Tablespace created.

SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

TABLESPACE_NAME              MB FILE_NAME

-------------------- ---------- --------------------------------------------------

USERS                         5 /home/oracle/oradata/testdb/users01.dbf

SYSAUX                      240 /home/oracle/oradata/testdb/sysaux01.dbf

UNDOTBS1                     35 /home/oracle/oradata/testdb/undotbs01.dbf

SYSTEM                      480 /home/oracle/oradata/testdb/system01.dbf

EXAMPLE                     100 /home/oracle/oradata/testdb/example01.dbf

CLONE                        10 /home/oracle/oradata/testdb/clone01.dbf

6 rows selected.

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/system01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/system01.dbf' -> `/data/backup/open/system01.dbf'

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/sysaux01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/sysaux01.dbf' -> `/data/backup/open/sysaux01.dbf'

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/undotbs01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/undotbs01.dbf' -> `/data/backup/open/undotbs01.dbf'

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/users01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/users01.dbf' -> `/data/backup/open/users01.dbf'

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace example begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/example01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/example01.dbf' -> `/data/backup/open/example01.dbf'

SQL> alter tablespace example end backup;

Tablespace altered.

SQL> alter tablespace clone begin backup;

Tablespace altered.

SQL> !cp -av /home/oracle/oradata/testdb/clone01.dbf /data/backup/open/

`/home/oracle/oradata/testdb/clone01.dbf' -> `/data/backup/open/clone01.dbf'

SQL> alter tablespace clone end backup;

Tablespace altered.

SQL> 

2단계 : 실습용 Tablespace 생성 후 Data를 입력하고 장애를 발생시킨다.

SQL> conn scott/tiger;

Connected.

SQL> create table test (no number) tablespace clone;

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "SYSDATE" from dual;

SYSDATE

-------------------

2010-03-16:11:20:17

SQL> drop table test purge;

Table dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                           TABLE

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

SQL> conn / as sysdba

Connected.

#log switch를 수 차례 발생시켜 해당 작업 내용이 아카이빙 되도록 한다.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> 

3단계 : 복구 서버용 Parameter file 생성하기 - 운영 서버에서 Parameter file 복사해서 복구 서버용으로 편집하는 작업

SQL> create pfile from spfile;

File created.

SQL> !cp -av $ORACLE_HOME/dbs/inittestdb.ora $ORACLE_HOME/dbs/inittestdb2.ora

`/home/oracle/product/10g/dbs/inittestdb.ora' -> `/home/oracle/product/10g/dbs/inittestdb2.ora'

SQL> !vi $ORACLE_HOME/dbs/inittestdb2.ora

testdb.__db_cache_size=213909504

testdb.__java_pool_size=4194304

testdb.__large_pool_size=4194304

testdb.__shared_pool_size=58720256

testdb.__streams_pool_size=0

*.audit_file_dest='/data/clone/admin/adump'

*.background_dump_dest='data/clone/admin/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/data/clone/testdb2/control01.ctl'

*.core_dump_dest='/data/clone/admin/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testdb2'

*.db_recovery_file_dest='/home/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.job_queue_processes=10

*.local_listener='LISTENER_TESTDB'

*.log_archive_dest_1='location=/data/arc1'

*.log_archive_dest_2='location=/data/arc2'

*.log_archive_format='%s_%t_%r.arc'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/data/clone/admin/udump'

"~/product/10g/dbs/inittestdb2.ora" 30L, 1025C written

SQL> !mkdir -p /data/clone/testdb2

SQL> !mkdir -p /data/clone/admin/adump

SQL> !mkdir -p /data/clone/admin/bdump

SQL> !mkdir -p /data/clone/admin/cdump

SQL> !mkdir -p /data/clone/admin/udump

SQL> 

4단계 : 임시 복구 경로에 복구 대상 파일 복원하기

SQL> !cp -av /data/backup/open/system01.dbf /data/clone/testdb2/

`/data/backup/open/system01.dbf' -> `/data/clone/testdb2/system01.dbf'

SQL> !cp -av /data/backup/open/sysaux01.dbf /data/clone/testdb2/

`/data/backup/open/sysaux01.dbf' -> `/data/clone/testdb2/sysaux01.dbf'

SQL> !cp -av /data/backup/open/undotbs01.dbf /data/clone/testdb2/

`/data/backup/open/undotbs01.dbf' -> `/data/clone/testdb2/undotbs01.dbf'

SQL> !cp -av /data/backup/open/clone01.dbf /data/clone/testdb2/

`/data/backup/open/clone01.dbf' -> `/data/clone/testdb2/clone01.dbf'

SQL> 

5단계 : 복구 DB를 위한 Control file 재 생성 및 DB 이름 변경, DB 시작 운영 서버에서 Control file을 Backup 받아서 복구 서버용으로 만드는 작업

SQL> alter database backup controlfile to trace as '/data/clone/recon_testdb2.sql';

Database altered.

SQL> !vi /data/clone/recon_testdb2.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TESTDB2" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/data/clone/testdb2/redo01_a.log',

    '/data/clone/testdb2/redo01_b.log'

  ) SIZE 5M,

  GROUP 2 (

    '/data/clone/testdb2/redo02_a.log',

    '/data/clone/testdb2/redo02_b.log'

  ) SIZE 5M,

  GROUP 3 (

    '/data/clone/testdb2/redo03_a.log',

    '/data/clone/testdb2/redo03_b.log'

  ) SIZE 5M

DATAFILE

  '/data/clone/testdb2/system01.dbf',

  '/data/clone/testdb2/sysaux01.dbf',

  '/data/clone/testdb2/undotbs01.dbf',

  '/data/clone/testdb2/clone01.dbf'

CHARACTER SET KO16KSC5601

;

"/data/clone/recon_testdb2.sql" 27L, 688C written

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@ghost ~]$ export ORACLE_SID=testdb2

[oracle@ghost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 16 11:56:18 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @/data/clone/recon_testdb2.sql

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                2973696 bytes

Control file created.

SQL> 

6단계 : 복구 (복구 서버에서 하는 작업)

SQL> recover database using backup controlfile until time '2010-03-16:11:20:17';

ORA-00279: change 526254 generated at 03/16/2010 11:16:57 needed for thread 1

ORA-00289: suggestion : /data/arc2/6_1_713363612.arc

ORA-00280: change 526254 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/tiger;

Connected.

SQL> select * from test;

        NO

----------

         1

         2

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@ghost ~]$ mkdir -p /data/backup/exp

[oracle@ghost ~]$ export ORACLE_SID=testdb2

[oracle@ghost ~]$ exp userid=scott/tiger file=/data/backup/exp/scott.dmp tables=test;

Export: Release 10.2.0.1.0 - Production on Tue Mar 16 12:05:43 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                           TEST          2 rows exported

Export terminated successfully without warnings.

[oracle@ghost ~]$ 

7단계 : 데이터 Import 후 확인

< 실제 서버에서 작업 >

[oracle@ghost ~]$ export ORACLE_SID=testdb

[oracle@ghost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 16 12:08:24 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from scott.test;

select * from scott.test

                    *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> exit 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

[oracle@ghost ~]$ imp userid=scott/tiger file=/data/backup/exp/scott.dmp ignore=y

Import: Release 10.2.0.1.0 - Production on Tue Mar 16 12:09:15 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in KO16KSC5601 character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing table                         "TEST"          2 rows imported

Import terminated successfully without warnings.

[oracle@ghost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 16 12:09:41 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> conn scott/tiger;

Connected.

SQL> select * from test;

        NO

----------

         1

         2

SQL>