Chap.09 무정지 상태에서의 복구 (by nav012) [2015.11.28]
h1.09 무정지 상태에서의 복구
h3.09-1 개요
- 사용자의 논리적 에러 등으로 데이터 손실 및 장애가 발생 한 상황 가정
- 운영 서버의 중단 없이 백업본을 이용해 DB 를 복사하여 복구
- Clone DB란? - 백업본을 이용해 복제한 운영 DB 의 복사본
h3.09-2 복구 Case
- Case 1 - 마지막 Full 백업 이후 데이터의 삭제
- Case 2 - 마지막 Full 백업 이후 Tablespace 의 삭제
h3.09-3 전제조건
- 장애 상황 발생 전 Full backup 필요
- Archive mode 로 DB 운영 필요
h3.09-4 복구 시나리오
- 편의를 위해 Case 1 과 Case 2 상황을 동시에 처리 함
1. 백업/복구 환경 구성
2. 유저 데이터 생성 <- Case 1 에서 복구 할 데이터
3. 백업(Full backup)
4. 테이블 스페이스 생성 <- Case 2 에서 복구 할 테이블스페이스
5. 데이터 삭제(Case 1) 및 테이블스페이스 드롭(Case 2)
6. 시점 기반 복구(3-4 사이의 시점으로 복구)
09-5 복구 상세
h4.0 복구 DB 환경
- Single + ASM 사용 DB
- ORACLE_SID = RECO3, +ASM
- ORACLE_HOME = /app/oracle/product/11.2.0
- GRID_HOME = /app/grid/11.2.0
- Clone DB ORACLE_SID = RECLONE
- 백업 경로 = /aoo/oracle/backup
h4.1. 백업/복구 환경 구성
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/fb_area
Oldest online log sequence 81
Next log sequence to archive 83
Current log sequence 83
SQL> alter system switch logfile;
System altered.
SQL> create tablespace reco_table_tbs datafile '+DATA1' size 100m autoextend on next 100m maxsize 1000m;
Tablespace created.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TMP2
RECO_TABLE_TBS
7 rows selected.
SQL> create user reco identified by 1234 default tablespace reco_table_tbs;
User created.
SQL> grant create session, create table, unlimited tablespace to reco;
Grant succeeded.
h5.2. 유저 데이터 생성
SQL> create table reco.recover_table (a number) tablespace reco_table_tbs;
Table created.
SQL> insert into reco.recover_table select level from dual connect by level <= 10;
10 rows created.
SQL> commit;
Commit complete.
h4.3. 백업(Full backup)
SQL> alter database begin backup;
Database altered.
SQL> select 'cp '||file_name||' /app/oracle/backup' as cp_script from dba_data_files ;
CP_SCRIPT
---------------------------------------------------------------------------------------
cp +DATA1/reco3/datafile/system.283.855078459 /app/oracle/backup
cp +DATA1/reco3/datafile/sysaux.284.855078555 /app/oracle/backup
cp +DATA1/reco3/datafile/undotbs1.273.855078669 /app/oracle/backup
cp +DATA1/reco3/datafile/users.261.855078557 /app/oracle/backup
cp +DATA1/reco3/datafile/reco_table_tbs.257.896890415 /app/oracle/backup
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[RECO3]rac2:/app/oracle> export ORACLE_SID=+ASM
[+ASM]rac2:/app/oracle> export ORACLE_HOME=/app/grid/11.2.0
[+ASM]rac2:/app/oracle> asmcmd
ASMCMD> cp +DATA1/reco3/datafile/system.283.855078459 /app/oracle/backup/
ASMCMD> copying +DATA1/reco3/datafile/system.283.855078459 -> /app/oracle/backup//system.283.855078459
ASMCMD> cp +DATA1/reco3/datafile/sysaux.284.855078555 /app/oracle/backup/
ASMCMD> copying +DATA1/reco3/datafile/sysaux.284.855078555 -> /app/oracle/backup//sysaux.284.855078555
ASMCMD> cp +DATA1/reco3/datafile/undotbs1.273.855078669 /app/oracle/backup/
ASMCMD> copying +DATA1/reco3/datafile/undotbs1.273.855078669 -> /app/oracle/backup//undotbs1.273.855078669
ASMCMD> cp +DATA1/reco3/datafile/users.261.855078557 /app/oracle/backup/
ASMCMD> copying +DATA1/reco3/datafile/users.261.855078557 -> /app/oracle/backup//users.261.855078557
ASMCMD> cp +DATA1/reco3/datafile/reco_table_tbs.257.896890415 /app/oracle/backup/
ASMCMD> copying +DATA1/reco3/datafile/reco_table_tbs.257.896890415 -> /app/oracle/backup//reco_table_tbs.257.896890415
ASMCMD>
ASMCMD> exit
[+ASM]rac2:/app/oracle> export ORACLE_SID=RECO3
[RECO3]rac2:/app/oracle> export ORACLE_HOME=/app/oracle/product/11.2.0
[RECO3]rac2:/app/oracle> sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 27 16:27:23 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter database end backup;
Database altered.
SQL> alter system switch logfile;
System altered.
h4.4. 테이블 스페이스 생성
SQL> create tablespace recover_tbs datafile '+DATA1' size 100m autoextend on next 100m maxsize 300m;
Tablespace created.
SQL> create table reco.recover_table2 (b varchar2(30)) tablespace recover_tbs;
Table created.
SQL> insert into reco.recover_table2 (b) values ('test data');
1 row created.
SQL> commit;
Commit complete.
SQL> select b from reco.recover_table2 ;
B
------------------------------
test data
SQL> alter system switch logfile ;
System altered.
***** 복구 시점 확인 *****
SQL> select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------
27-NOV-15 04.28.25.234518 PM +09:00
h5.5. 데이터 삭제(Case 1) 및 테이블스페이스 드롭(Case 2)
SQL> delete from reco.recover_table;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> drop tablespace recover_tbs including contents and datafiles;
Tablespace dropped.
SQL> select * from reco.recover_table;
no rows selected
SQL> select * from reco.recover_table2;
select * from reco.recover_table2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM +DATA1/reco3/datafile/system.283.855078459
SYSAUX +DATA1/reco3/datafile/sysaux.284.855078555
UNDOTBS1 +DATA1/reco3/datafile/undotbs1.273.855078669
USERS +DATA1/reco3/datafile/users.261.855078557
RECO_TABLE_TBS +DATA1/reco3/datafile/reco_table_tbs.257.896890415
SQL> alter system switch logfile ;
System altered.
...
h4.6. 시점 기반 복구
*운영 DB 에서 pfile 복사하여 Clone DB 에 맞게 편집
*운영 DB 가 spfile 을 사용 중이므로 편의를 위해 create pfile 로 text 기반 parameter file 생성 후 일괄 수정
- 일반적으로 편집 해야 할 부분
1. db_name - Clone DB 에 새로 지정 할 DB 이름을 지정한다
2. control_files - 새로 생성 할 Controlfile 의 경로, 이름을 지정한다
3. *dump_dest 및 OS 의 실제 디렉토리 - background/user/core/audit dump dest 를 복구 서버 환경에 맞게 편집 후,실제 디렉토리 생성
4. 각종 memory 관련 파라메터 - 실제 복구 서버의 memory 환경에 맞게 잡아준다
5. RAC --> Single 일 경우 cluster_database = false
h5.6-1. Parameter file 생성 및 편집
SQL> create pfile='/app/oracle/backup/initRECLONE.ora' from spfile;
File created.
SQL> !
[RECO3]rac2:/app/oracle> cd backup
[RECO3]rac2:/app/oracle/backup> vi initRECLONE.ora
RECO3.__db_cache_size=20971520
RECO3.__java_pool_size=4194304
RECO3.__large_pool_size=4194304
RECO3.__pga_aggregate_target=41943040
RECO3.__sga_target=209715200
RECO3.__shared_io_pool_size=0
RECO3.__shared_pool_size=163577856
RECO3.__streams_pool_size=4194304
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._compression_compatibility='11.2.0.0.0'
*._connect_by_use_union_all='TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
:%s/RECO3/RECLONE/g --> vi editor 로 일괄 수정 가능한 부분
*.control_files='/app/oracle/backup/RECLONE_control.ctl'# Restore Controlfile
--> 새로 생성 할 콘트롤 파일 지정
*.core_dump_dest='/app/oracle/rman_restore/diag/rdbms/reco3/RECLONE/cdump'
*.background_dump_dest='/app/oracle/rman_restore/diag/rdbms/reco3/RECLONE/trace'#Deprecate parameter
*.user_dump_dest='/app/oracle/rman_restore/diag/rdbms/reco3/RECLONE/trace'#Deprecate parameter
--> 덤프 디렉토리 경로 수정
[RECO3]rac2:/app/oracle/product/11.2.0/dbs> cd /app/oracle/rman_restore/diag/rdbms/reco3
[RECO3]rac2:/app/oracle/rman_restore/diag/rdbms/reco3> ls
i_1.mif RECO3
[RECO3]rac2:/app/oracle/rman_restore/diag/rdbms/reco3> mkdir -p RECLONE/trace
[RECO3]rac2:/app/oracle/rman_restore/diag/rdbms/reco3> mkdir -p RECLONE/cdump
--> 덤프 디렉토리 생성
h5.6-2. Control file 생성 및 편집
SQL> alter database backup controlfile to trace as '/app/oracle/backup/RECLONE_control.sql';
Database altered.
SQL> !
[RECO3]rac2:/app/oracle/backup> vi /app/oracle/backup/RECLONE_control.sql
CREATE CONTROLFILE REUSE DATABASE "RECO3" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+REDO/reco/redo01a.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '+REDO/reco/redo02a.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '+REDO/reco/redo03a.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'+DATA1/reco3/datafile/system.283.855078459',
'+DATA1/reco3/datafile/sysaux.284.855078555',
'+DATA1/reco3/datafile/undotbs1.273.855078669',
'+DATA1/reco3/datafile/users.261.855078557',
'+DATA1/reco3/datafile/reco_table_tbs.257.896890415'
CHARACTER SET AL32UTF8
;
~
~
~
~
~
~
~
:%s/+DATA1\/reco3\/datafile\//\/app\/oracle\/backup\//g
:%s/+REDO\/reco\//\/app\/oracle\/backup\//g
--> vi editor 로 일괄 경로 수정
CREATE CONTROLFILE SET DATABASE RECLONE RESETLOGS ARCHIVELOG
-->DB 이름 수정
CREATE CONTROLFILE SET DATABASE RECLONE RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/app/oracle/backup/redo01a.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/app/oracle/backup/redo02a.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/app/oracle/backup/redo03a.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/app/oracle/backup/system.283.855078459',
'/app/oracle/backup/sysaux.284.855078555',
'/app/oracle/backup/undotbs1.273.855078669',
'/app/oracle/backup/users.261.855078557',
'/app/oracle/backup/reco_table_tbs.257.896890415'
CHARACTER SET AL32UTF8
;
--> 편집 후 결과
h5.6-3 Controlfile 편집 옵션
CREATE CONTROLFILE SET/REUSE DATABASE RECO3 NORESETLOGS/RESETLOGS ARCHIVELOG
- SET DATABASE NAME / REUSE DATABASE NAME
물리적으로 다른 서버에 복구 시 DB 이름 변경 없이 복제 가능 - 이 때 Controlfile 은 아래와 같이 수정
- RESETLOGS
Log seqeuence 를 부터 재 사용
Current redolog 는 복구에 사용 불가
DB Name 이 변경되었을 때
불완전 복구를 사용했을 때
'using backup controlfile' 절을 사용하여 복구했을 때
h5.6-4. 시점 기반 복구 수행
[RECO3]rac2:/app/oracle/backup> export ORACLE_SID=RECLONE
[RECLONE]rac2:/app/oracle/backup> sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 27 16:32:43 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount pfile='/app/oracle/backup/initRECLONE.ora';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: SQL_TRACE initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 1040188600 bytes
Database Buffers 20971520 bytes
Redo Buffers 5541888 bytes
SQL> @/app/oracle/backup/recoclone_control.sql
Control file created.
SQL>
SQL> recover database until time '2015-11-27:16:28:26' using backup controlfile;
ORA-00279: change 3178963 generated at 11/27/2015 16:14:16 needed for thread 1
ORA-00289: suggestion : /app/oracle/fb_area/1_84_860864072.dbf
ORA-00280: change 3178963 for thread 1 is in sequence #84
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3180322 generated at 11/27/2015 16:27:42 needed for thread 1
ORA-00289: suggestion : /app/oracle/fb_area/1_85_860864072.dbf
ORA-00280: change 3180322 for thread 1 is in sequence #85
ORA-00278: log file '/app/oracle/fb_area/1_84_860864072.dbf' no longer needed for this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '+DATA1/reco3/datafile/recover_tbs.264.896891269'
ORA-01112: media recovery not started
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------
/app/oracle/backup/system.283.855078459
/app/oracle/backup/sysaux.284.855078555
/app/oracle/backup/undotbs1.273.855078669
/app/oracle/backup/users.261.855078557
/app/oracle/backup/reco_table_tbs.257.896890415
/app/oracle/product/11.2.0/dbs/UNNAMED00006
6 rows selected.
SQL> alter database create datafile '/app/oracle/product/11.2.0/dbs/UNNAMED00006' as '/app/oracle/backup/recovered_file.dbg';
Database altered.
SQL> recover database until time '2015-11-27:16:28:26' using backup controlfile;
ORA-00279: change 3180334 generated at 11/27/2015 16:27:57 needed for thread 1
ORA-00289: suggestion : /app/oracle/fb_area/1_85_860864072.dbf
ORA-00280: change 3180334 for thread 1 is in sequence #85
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 3180629 generated at 11/27/2015 16:28:19 needed for thread 1
ORA-00289: suggestion : /app/oracle/fb_area/1_86_860864072.dbf
ORA-00280: change 3180629 for thread 1 is in sequence #86
ORA-00278: log file '/app/oracle/fb_area/1_85_860864072.dbf' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
h5.6-5. 복구 결과 확인
SQL> select tablespace_name , file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
RECO_TABLE_TBS /app/oracle/backup/reco_table_tbs.257.896890415
USERS /app/oracle/backup/users.261.855078557
UNDOTBS1 /app/oracle/backup/undotbs1.273.855078669
SYSAUX /app/oracle/backup/sysaux.284.855078555
SYSTEM /app/oracle/backup/system.283.855078459
RECOVER_TBS /app/oracle/backup/recovered_file.dbg
6 rows selected.
SQL> select * from reco.recover_table;
A
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from reco.recover_table2;
B
------------------------------
test data
SQL>