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>
