테스트 시나리오 ( 복구 )
====================================================================
2. 아카이브 로그 모드 에서 복구.
2-1. 완전복구
1) offline 이 되는 일반 tablespace 복구
2) offline 이 되지 않는 tablespace 복구
3) 백업파일이 없는 경우 복구
4) 일부 데이터 파일이 없는 경우 복구 (임시 경로를 이용한 긴급 복구)
2-2. 불안전 복구 - 논리적 장애 복구
1) drop table로 삭제된 테이블 복구
2) drop table 복구 (백업파일x)
3) 잘못된 UPDATE 장애 복구
4) drop tablespace 로 잘못 삭제된 tablespace 복구(백업 ctl 있음)
5) drop tablespace 로 잘못 삭제된 tablespace 복구(백업 ctl 없음)
3. undo tablespace 복구
----------------------------------------------------
----------------------------------------------------
테스트를 위해 데이터 파일 백업. & 컨트롤 파일 백업
SYS@ORCL AS SYSDBA> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SELECT file_id,
tablespace_name,
file_name
FROM DBA_DATA_FILES
order by 1
1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
2 SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 USERS /u01/app/oracle/oradata/orcl/users01.dbf
5 EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
6 TBS_A /u01/app/oracle/oradata/orcl/tbs_a_01.dbf
7 TBS_A /u01/app/oracle/oradata/orcl/tbs_a_02.dbf
8 TBS_B /u01/app/oracle/oradata/orcl/tbs_b_01.dbf
9 UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02.dbf
10 UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02_2.dbf
11 TBS_C /u01/app/oracle/oradata/orcl/tbs_c_01.dbf
12 TBS_D /u01/app/oracle/oradata/orcl/tbs_d_01.dbf
13 TBS_RMAN /home/oracle/ts/TBS_RAMN01.dbf
14 PBJ_TEMP /home/oracle/pbj_temp01.dbf
15 TBS_TEST /home/oracle/ts/TBS_TEST02.dbf
17 DATA01 /home/oracle/ts/DATA01.dbf
21 TBS_SCOTT_IDX /home/oracle/scott_idx01.dbf
25 TBS_TEST /home/oracle/ts/TBS_TEST01.dbf
26 RC_TBS01 /u01/app/oracle/oradata/orcl/rc_tbs.dbf
** 데이터 파일 백업
아래의 쿼리를 통해
begin , cp, end 쿼리 추출
select 'alter tablespace '||b.name||' begin backup;' ,
'host cp '||a.name||' '|| '/home/oracle/backup/',
'alter tablespace '||b.name||' end backup;'
from v$datafile a,
v$tablespace b
where a.ts# = b.ts#;
alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
alter tablespace UNDOTBS2 begin backup;
alter tablespace UNDOTBS2 begin backup;
alter tablespace TBS_A begin backup;
alter tablespace TBS_A begin backup;
alter tablespace TBS_B begin backup;
alter tablespace TBS_C begin backup;
alter tablespace TBS_D begin backup;
alter tablespace PBJ_TEMP begin backup;
alter tablespace DATA01 begin backup;
alter tablespace TBS_SCOTT_IDX begin backup;
alter tablespace TBS_TEST begin backup;
alter tablespace TBS_TEST begin backup;
alter tablespace RC_TBS01 begin backup;
alter tablespace TBS_RMAN begin backup;
백업 상태 확인 ( begin 모드로 들어갔는지 확인)
select * from v$backup;
begin 백업 확인 = status -> active
SYS@ORCL AS SYSDBA> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 ACTIVE 1.4696E+13 23-MAR-16
2 ACTIVE 1.4696E+13 23-MAR-16
3 ACTIVE 1.4696E+13 23-MAR-16
4 ACTIVE 1.4696E+13 23-MAR-16
6 ACTIVE 1.4696E+13 23-MAR-16
7 ACTIVE 1.4696E+13 23-MAR-16
8 ACTIVE 1.4696E+13 23-MAR-16
9 ACTIVE 1.4696E+13 23-MAR-16
10 ACTIVE 1.4696E+13 23-MAR-16
11 ACTIVE 1.4696E+13 23-MAR-16
12 ACTIVE 1.4696E+13 23-MAR-16
13 ACTIVE 1.4696E+13 23-MAR-16
14 ACTIVE 1.4696E+13 23-MAR-16
15 ACTIVE 1.4696E+13 23-MAR-16
17 ACTIVE 1.4696E+13 23-MAR-16
21 ACTIVE 1.4696E+13 23-MAR-16
25 ACTIVE 1.4696E+13 23-MAR-16
26 ACTIVE 1.4696E+13 23-MAR-16
백업 시작
host cp /u01/app/oracle/oradata/orcl/system01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/users01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/undotbs02_2.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/undotbs02.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/tbs_a_02.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/tbs_a_01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/tbs_b_01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/tbs_c_01.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/tbs_d_01.dbf /home/oracle/backup/
host cp /home/oracle/pbj_temp01.dbf /home/oracle/backup/
host cp /home/oracle/ts/DATA01.dbf /home/oracle/backup/
host cp /home/oracle/scott_idx01.dbf /home/oracle/backup/
host cp /home/oracle/ts/TBS_TEST01.dbf /home/oracle/backup/
host cp /home/oracle/ts/TBS_TEST02.dbf /home/oracle/backup/
host cp /u01/app/oracle/oradata/orcl/rc_tbs.dbf /home/oracle/backup/
host cp /home/oracle/ts/TBS_RAMN01.dbf /home/oracle/backup/
cp 확인 후, end 백업.
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS end backup;
alter tablespace UNDOTBS2 end backup;
alter tablespace UNDOTBS2 end backup;
alter tablespace EXAMPLE end backup;
alter tablespace TBS_A end backup;
alter tablespace TBS_A end backup;
alter tablespace TBS_B end backup;
alter tablespace TBS_C end backup;
alter tablespace TBS_D end backup;
alter tablespace PBJ_TEMP end backup;
alter tablespace DATA01 end backup;
alter tablespace TBS_SCOTT_IDX end backup;
alter tablespace TBS_TEST end backup;
alter tablespace TBS_TEST end backup;
alter tablespace RC_TBS01 end backup;
alter tablespace TBS_RMAN end backup;
end 백업 확인 = status -> not active
SYS@ORCL AS SYSDBA> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 NOT ACTIVE 1.4696E+13 23-MAR-16
2 NOT ACTIVE 1.4696E+13 23-MAR-16
3 NOT ACTIVE 1.4696E+13 23-MAR-16
4 NOT ACTIVE 1.4696E+13 23-MAR-16
6 NOT ACTIVE 1.4696E+13 23-MAR-16
7 NOT ACTIVE 1.4696E+13 23-MAR-16
8 NOT ACTIVE 1.4696E+13 23-MAR-16
9 NOT ACTIVE 1.4696E+13 23-MAR-16
10 NOT ACTIVE 1.4696E+13 23-MAR-16
11 NOT ACTIVE 1.4696E+13 23-MAR-16
12 NOT ACTIVE 1.4696E+13 23-MAR-16
13 NOT ACTIVE 1.4696E+13 23-MAR-16
14 NOT ACTIVE 1.4696E+13 23-MAR-16
15 NOT ACTIVE 1.4696E+13 23-MAR-16
17 NOT ACTIVE 1.4696E+13 23-MAR-16
21 NOT ACTIVE 1.4696E+13 23-MAR-16
25 NOT ACTIVE 1.4696E+13 23-MAR-16
26 NOT ACTIVE 1.4696E+13 23-MAR-16
** 컨트롤 파일 백업
Wed Mar 23 14:56:02 2016
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_22379.trc
Completed: alter database backup controlfile to trace
host cp /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_22379.trc /home/oracle/backup/
** 아카이브 확인
[oracle@DBTEST orcl]$ ll *907073294*
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_10_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_11_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_12_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_13_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_14_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_15_907073294.arc
-rw-r----- 1 oracle oinstall 39866368 Mar 21 22:00 name_1_16_907073294.arc
-rw-r----- 1 oracle oinstall 31293440 Mar 22 01:00 name_1_17_907073294.arc
-rw-r----- 1 oracle oinstall 42190848 Mar 22 09:13 name_1_18_907073294.arc
-rw-r----- 1 oracle oinstall 42726912 Mar 22 09:13 name_1_19_907073294.arc
-rw-r----- 1 oracle oinstall 11162624 Mar 21 14:58 name_1_1_907073294.arc
-rw-r----- 1 oracle oinstall 32489984 Mar 22 17:01 name_1_20_907073294.arc
-rw-r----- 1 oracle oinstall 33788928 Mar 22 22:00 name_1_21_907073294.arc
-rw-r----- 1 oracle oinstall 33166848 Mar 23 03:00 name_1_22_907073294.arc
-rw-r----- 1 oracle oinstall 48494080 Mar 23 09:14 name_1_23_907073294.arc
-rw-r----- 1 oracle oinstall 34370560 Mar 23 09:14 name_1_24_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_2_907073294.arc
-rw-r----- 1 oracle oinstall 2560 Mar 21 14:58 name_1_3_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_4_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_5_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_6_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_7_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_8_907073294.arc
-rw-r----- 1 oracle oinstall 1024 Mar 21 14:58 name_1_9_907073294.arc
로그 확인.( scn 확인 : 현재 25번.) => 즉, 아카이브가 24번까지 있어야함.
SYS@ORCL AS SYSDBA> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- ------------ ------------ ------------
1 1 25 52428800 512 1 NO
CURRENT 1.4696E+13 23-MAR-16 2.8147E+14
3 1 24 52428800 512 1 YES
INACTIVE 1.4696E+13 23-MAR-16 1.4696E+13 23-MAR-16
2 1 23 52428800 512 1 YES
INACTIVE 1.4696E+13 23-MAR-16 1.4696E+13 23-MAR-16
GROUP# 별 redo 파일 확인.
SYS@ORCL AS SYSDBA> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO
2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO
1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO
=================================================================================================
1) Offline 이 되는 일반 Tablespace 복구하기 (대상 : 26 RC_TBS01 /u01/app/oracle/oradata/orcl/rc_tbs.db)
------------------------------------------
삭제.
------------------------------------------
SYS@ORCL AS SYSDBA> !rm -rf /u01/app/oracle/oradata/orcl/rc_tbs.dbf
SYS@ORCL AS SYSDBA> !ls /u01/app/oracle/oradata/orcl/rc_tbs.dbf
ls: cannot access /u01/app/oracle/oradata/orcl/rc_tbs.dbf: No such file or directory
------------------------------------------
RCUSER 로그인 후 INSERT
------------------------------------------
SYS@ORCL AS SYSDBA> conn RCUSER/RCUSER
Connected.
RCUSER@ORCL > create table test01 (no number) tablespace RC_TBS01
2 ;
Table created.
RCUSER@ORCL >
RCUSER@ORCL >
------------------------------------------
장애 확인.
------------------------------------------
RCUSER@ORCL > insert into test01 values(1);
insert into test01 values(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 26
ORA-01110: data file 26: '/u01/app/oracle/oradata/orcl/rc_tbs.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
------------------------------------------
백업 으로부터 restore 후 recover
------------------------------------------
SYS@ORCL AS SYSDBA> alter tablespace RC_TBS01 offline;
Tablespace altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> host cp /home/oracle/backup/rc_tbs.dbf /u01/app/oracle/oradata/orcl/rc_tbs.dbf
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> recover tablespace RC_TBS01;
Media recovery complete.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter tablespace RC_TBS01 online;
Tablespace altered.
------------------------------------------
RCUSER 로 다시 값 INSERT
------------------------------------------
RCUSER@ORCL > insert into test01 values(1);
1 row created.
RCUSER@ORCL >
RCUSER@ORCL > select * from test01;
NO
----------
1
RCUSER@ORCL > commit;
Commit complete.
=================================================================================================
2) Offline 이 안되는 Tablespace 복구하기 (대상 : 1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf)
------------------------------------------
삭제.
------------------------------------------
SYS@ORCL AS SYSDBA> create table test02(no number) tablespace SYSTEM;
Table created.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> insert into test02 values(1);
1 row created.
SYS@ORCL AS SYSDBA> commit;
Commit complete.
SYS@ORCL AS SYSDBA> !rm -rf /u01/app/oracle/oradata/orcl/system01.dbf
SYS@ORCL AS SYSDBA> !ls /u01/app/oracle/oradata/orcl/system01.dbf
ls: cannot access /u01/app/oracle/oradata/orcl/system01.dbf: No such file or directory
------------------------------------------
tablespace offline -> system tbs offline 안됨
------------------------------------------
SYS@ORCL AS SYSDBA> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
------------------------------------------
db 종료후 dbf 파일 카피
------------------------------------------
SYS@ORCL AS SYSDBA> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> !cp /home/oracle/backup/system01.dbf /u01/app/oracle/oradata/orcl/
------------------------------------------
startup 후 recover 실행 후 startup
------------------------------------------
SYS@ORCL AS SYSDBA> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3238004888 bytes
Database Buffers 33554432 bytes
Redo Buffers 16568320 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
SYS@ORCL AS SYSDBA> recover database;
Media recovery complete.
SYS@ORCL AS SYSDBA> alter database open;
Database altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select status from v$instance;
STATUS
------------
OPEN
=================================================================================================
3) 백업 파일이 없는 경우 복구하기 (대상 : 16 pbj /u01/app/oracle/oradata/orcl/pbj.dbf)
------------------------------------------
pbj tbs 생성
------------------------------------------
create tablespace pbj
datafile '/u01/app/oracle/oradata/orcl/pbj.dbf' size 5M;
SYS@ORCL AS SYSDBA> SELECT file_id,
tablespace_name,
file_name
FROM DBA_DATA_FILES
where tablespace_name='PBJ'
order by 1;
16 PBJ
/u01/app/oracle/oradata/orcl/pbj.dbf
------------------------------------------
pbj.dbf 삭제
------------------------------------------
SYS@ORCL AS SYSDBA> !rm -rf /u01/app/oracle/oradata/orcl/pbj.dbf
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> !ls /u01/app/oracle/oradata/orcl/pbj.dbf
ls: cannot access /u01/app/oracle/oradata/orcl/pbj.dbf: No such file or directory
------------------------------------------
pbj.dbf 삭제
------------------------------------------
SYS@ORCL AS SYSDBA> !rm -rf /u01/app/oracle/oradata/orcl/pbj.dbf
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> !ls /u01/app/oracle/oradata/orcl/pbj.dbf
ls: cannot access /u01/app/oracle/oradata/orcl/pbj.dbf: No such file or directory
------------------------------------------
pbj.dbf 삭제 후 table 생성 insert
------------------------------------------
SYS@ORCL AS SYSDBA> create table test03 (no number) tablespace pbj;
Table created.
SYS@ORCL AS SYSDBA> insert into test03 values (1);
1 row created.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> commit;
Commit complete.
SYS@ORCL AS SYSDBA> select * from test03;
NO
----------
1
------------------------------------------
장애 확인
------------------------------------------
SYS@ORCL AS SYSDBA> alter tablespace pbj offline;
Tablespace altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter tablespace pbj online;
alter tablespace pbj online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 16 - see DBWR trace file
ORA-01110: data file 16: '/u01/app/oracle/oradata/orcl/pbj.dbf'
---------------------------------------------
백업파일이 존재하지 않으므로 dadta file 생성하여 복구
----------------------------------------------
SYS@ORCL AS SYSDBA> alter database create datafile '/u01/app/oracle/oradata/orcl/pbj.dbf'
as '/u01/app/oracle/oradata/orcl/pbj_1.dbf';
SYS@ORCL AS SYSDBA> SELECT file_id,
2 tablespace_name,
3 file_name
4 FROM DBA_DATA_FILES
5 where tablespace_name='PBJ'
6 order by 1;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
16 PBJ
/u01/app/oracle/oradata/orcl/pbj_1.dbf
SYS@ORCL AS SYSDBA> recover tablespace pbj;
Media recovery complete.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter tablespace pbj online;
Tablespace altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select * from test03;
NO
----------
1
=================================================================================================
4) 일부 data file 이 없는 경우 복구(임시 경로를 이용한 긴급 복구)
(상황 : 특정 data file 만 제외(system, undo 등....)하고
모든 datafile 삭제됨 (/home/oracle/ts/*.dbf :
13 TBS_RMAN /home/oracle/ts/TBS_RAMN01.dbf
15 TBS_TEST /home/oracle/ts/TBS_TEST02.dbf
17 DATA01 /home/oracle/ts/DATA01.dbf
25 TBS_TEST /home/oracle/ts/TBS_TEST01.dbf
)
redo/archive log 백업된 datafile 존재
=>긴급히 db복구를 해야할 경우 임시 경로에 백업파일을 restore 하여 복구
-----------------------------------------------
/home/oracle/ts/ dbf 파일 삭제 후 강제 shutdown
------------------------------------------------------------------
SYS@ORCL AS SYSDBA> !rm -rf /home/oracle/ts/*.dbf
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> !ls /home/oracle/ts/*.dbf
ls: cannot access /home/oracle/ts/*.dbf: No such file or directory
SYS@ORCL AS SYSDBA> shutdown abort;
ORACLE instance shut down.
--------------------------------------------------
startup 장애 확인
/home/oracle/backup/ 백업되어있던 datafile restore 후 일부(DATA01.dbf) 복사하지 않는다.
---------------------------------------------------
SYS@ORCL AS SYSDBA> !cp /home/oracle/backup/TBS_RAMN01.dbf /home/oracle/temp/
SYS@ORCL AS SYSDBA> !cp /home/oracle/backup/TBS_TEST02.dbf /home/oracle/temp/
SYS@ORCL AS SYSDBA> !cp /home/oracle/backup/TBS_TEST01.dbf /home/oracle/temp/
!cp /home/oracle/backup/DATA01.dbf /home/oracle/temp/
--------------------------------------------------
data file 경로를 변경후 복구, 백업파일이 없는 DATA01.dbf 경우 데이터 파일 재생성
--------------------------------------------------
SYS@ORCL AS SYSDBA> alter database rename file '/home/oracle/ts/TBS_RAMN01.dbf'
2 to '/home/oracle/temp/TBS_RAMN01.dbf';
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/home/oracle/ts/TBS_TEST01.dbf'
2 to '/home/oracle/temp/TBS_TEST01.dbf'
3 ;
Database altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter database rename file '/home/oracle/ts/TBS_TEST02.dbf'
2 to '/home/oracle/temp/TBS_TEST02.dbf';
Database altered.
SYS@ORCL AS SYSDBA>alter database create datafile '/home/oracle/ts/DATA01.dbf'
2 as '/home/oracle/temp/DATA01.dbf';
Database altered.
SYS@ORCL AS SYSDBA> RECOVER database;
Media recovery complete.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter database open;
Database altered.
SYS@ORCL AS SYSDBA> select status from v$instance;
STATUS
------------
OPEN
==========================================================================================
==========================================================================================
2-2. 불안전 복구 - 논리적 장애 복구
--------------------------------------------------------------------
1) drop table로 삭제된 테이블 복구
--------------------------------------------------------------------
SYS@ORCL AS SYSDBA> select a.name "TS_NAME", b.name "FILE_NAME", b.bytes/1024/1024 MB, b.status
2 from v$tablespace a, v$datafile b
3 where a.ts#=b.ts#;
TS_NAME FILE_NAME MB STATUS
--------------- --------------------------------------------- ---------- -------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf 1000 SYSTEM
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf 1500 ONLINE
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 1000 ONLINE
USERS /u01/app/oracle/oradata/orcl/users01.dbf 365 ONLINE
UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02_2.dbf 85 ONLINE
UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02.dbf 85 ONLINE
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf 100 OFFLINE
TBS_A /u01/app/oracle/oradata/orcl/tbs_a_02.dbf 100 ONLINE
TBS_A /u01/app/oracle/oradata/orcl/tbs_a_01.dbf 100 ONLINE
TBS_B /u01/app/oracle/oradata/orcl/tbs_b_01.dbf 50 ONLINE
TBS_C /u01/app/oracle/oradata/orcl/tbs_c_01.dbf 100 ONLINE
TBS_D /u01/app/oracle/oradata/orcl/tbs_d_01.dbf 79 ONLINE
PBJ_TEMP /home/oracle/pbj_temp01.dbf 100 ONLINE
DATA01 /home/oracle/temp/DATA01.dbf 100 ONLINE
TBS_SCOTT_IDX /home/oracle/scott_idx01.dbf 200 ONLINE
TBS_TEST /home/oracle/temp/TBS_TEST02.dbf 5 ONLINE
TBS_TEST /home/oracle/temp/TBS_TEST01.dbf 100 ONLINE
RC_TBS01 /u01/app/oracle/oradata/orcl/rc_tbs.dbf 10 ONLINE
TBS_RMAN /home/oracle/temp/TBS_RAMN01.dbf 10 ONLINE
PBJ /u01/app/oracle/oradata/orcl/pbj_1.dbf 5 ONLINE
---------------------------------------------------------
현재 상태 확인
---------------------------------------------------------
SYS@ORCL AS SYSDBA> select name from v$datafile;
NAME
------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/tbs_a_01.dbf
/u01/app/oracle/oradata/orcl/tbs_a_02.dbf
/u01/app/oracle/oradata/orcl/tbs_b_01.dbf
/u01/app/oracle/oradata/orcl/undotbs02.dbf
/u01/app/oracle/oradata/orcl/undotbs02_2.dbf
/u01/app/oracle/oradata/orcl/tbs_c_01.dbf
/u01/app/oracle/oradata/orcl/tbs_d_01.dbf
/home/oracle/temp/TBS_RAMN01.dbf
/home/oracle/pbj_temp01.dbf
/home/oracle/temp/TBS_TEST02.dbf
/u01/app/oracle/oradata/orcl/pbj_1.dbf
/home/oracle/temp/DATA01.dbf
/home/oracle/scott_idx01.dbf
/home/oracle/temp/TBS_TEST01.dbf
/u01/app/oracle/oradata/orcl/rc_tbs.dbf
SYS@ORCL AS SYSDBA> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
SYS@ORCL AS SYSDBA> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
---------------------------------------------------------
전체 close 백업 (/home/oracle/temp 만)
---------------------------------------------------------
SYS@ORCL AS SYSDBA> !cp /home/oracle/temp/* /home/oracle/close/
SYS@ORCL AS SYSDBA> !ls /home/oracle/close
DATA01.dbf TBS_RAMN01.dbf TBS_TEST01.dbf TBS_TEST02.dbf
--------------------------------------------------------
table 생성후 data입력
-------------------------------------------------------
SYS@ORCL AS SYSDBA> conn TBS_USER/testuser
Connected.
TBS_USER@ORCL > create table test04 (no number) tablespace TBS_TEST;
Table created.
TBS_USER@ORCL > insert into test04 values (1);
1 row created.
TBS_USER@ORCL > insert into test04 values (2);
1 row created.
TBS_USER@ORCL > insert into test04 values (3);
1 row created.
TBS_USER@ORCL > select * from test04;
NO
----------
1
2
3
TBS_USER@ORCL > commit;
----------------------------------------------------------
복구 시 시점
----------------------------------------------------------
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016/03/24 10:16:34
TBS_USER@ORCL > select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "Time" from dual;
Time
-------------------
2016-03-24:10:17:23
----------------------------------------------------------
장애 ( 테이블 drop)
----------------------------------------------------------
TBS_USER@ORCL > drop table test04 purge;
Table dropped.
TBS_USER@ORCL > select * from test04;
select * from test04
*
ERROR at line 1:
ORA-00942: table or view does not exist
----------------------------------------------------------
복구
1) 임시경로 생성 후 필요한 파일 restore
2) 임시 경로 : /home/oracle/close_bak
3) 백업 file : system01.dbf , sysaux01.dbf, undotbs01.dbf
4) 삭제된 tbs.dbf : TBS_TEST
5) redo file , control file 현재것을 복원
----------------------------------------------------------
SYS@ORCL AS SYSDBA> !cp //u01/app/oracle/oradata/orcl/system01.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/undotbs02.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/undotbs02_2.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /home/oracle/temp/TBS_TEST02.dbf /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/*.ctl /home/oracle/close_bak
SYS@ORCL AS SYSDBA> !cp /u01/app/oracle/oradata/orcl/redo* /home/oracle/close_bak
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> !ls /home/oracle/close_bak
TBS_TEST02.dbf redo01.log redo03.log system01.dbf undotbs02.dbf
control01.ctl redo02.log sysaux01.dbf undotbs01.dbf undotbs02_2.dbf
[oracle@DBTEST dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbs
[oracle@DBTEST dbs]$ vi initORCL.ora
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
---> 변경 *.control_files='/home/oracle/close_bak/control01.ctl','/home/oracle/close_bak/control02.ctl'
SYS@ORCL AS SYSDBA> startup mount pfile='/u01/app/oracle/product/11.2.0/dbs/initORCL.ora';
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3204450456 bytes
Database Buffers 67108864 bytes
Redo Buffers 16568320 bytes
Database mounted.
--------------------------------------------------------------------
컨트롤파일 경로 확인 & 복구에 필요한 필수적인 dbf 파일 경로 변경
-------------------------------------------------------------------
SYS@ORCL AS SYSDBA> select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/home/oracle/close_bak/control01.ctl
NO 16384 614
/home/oracle/close_bak/control02.ctl
NO 16384 614
SYS@ORCL AS SYSDBA> alter database rename file '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
2 to '/home/oracle/close_bak/sysaux01.dbf';
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/u01/app/oracle/oradata/orcl/system01.dbf'
2 to '/home/oracle/close_bak/system01.dbf'
;
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
2 to '/home/oracle/close_bak/undotbs01.dbf' ;
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/u01/app/oracle/oradata/orcl/undotbs02.dbf'
2 to '/home/oracle/close_bak/undotbs02.dbf';
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/u01/app/oracle/oradata/orcl/undotbs02_2.dbf'
2 to '/home/oracle/close_bak/undotbs02_2.dbf';
Database altered.
SYS@ORCL AS SYSDBA> alter database rename file '/home/oracle/temp/TBS_TEST02.dbf'
2 to '/home/oracle/close_bak/TBS_TEST02.dbf';
Database altered.
-------------------------------------------------------
복구에 필요 없는 데이터 파일 offline
-----------------------------------------------------
alter database datafile '/u01/app/oracle/oradata/orcl/users01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/example01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/tbs_a_01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/tbs_a_02.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/tbs_b_01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/tbs_c_01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/tbs_d_01.dbf' offline drop;
alter database datafile '/home/oracle/temp/TBS_RAMN01.dbf' offline drop;
alter database datafile '/home/oracle/pbj_temp01.dbf' offline drop;
alter database datafile '/home/oracle/temp/DATA01.dbf' offline drop;
alter database datafile '/home/oracle/scott_idx01.dbf' offline drop;
alter database datafile '/home/oracle/temp/TBS_TEST01.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/pbj_1.dbf' offline drop;
alter database datafile '/u01/app/oracle/oradata/orcl/rc_tbs.dbf' offline drop;
SYS@ORCL AS SYSDBA> select name, status from v$datafile;
NAME STATUS
------------------------------------------- -------
/home/oracle/close_bak/system01.dbf SYSTEM
/home/oracle/close_bak/sysaux01.dbf ONLINE
/home/oracle/close_bak/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf OFFLINE
/u01/app/oracle/oradata/orcl/example01.dbf OFFLINE
/u01/app/oracle/oradata/orcl/tbs_a_01.dbf OFFLINE
/u01/app/oracle/oradata/orcl/tbs_a_02.dbf OFFLINE
/u01/app/oracle/oradata/orcl/tbs_b_01.dbf OFFLINE
/home/oracle/close_bak/undotbs02.dbf ONLINE
/home/oracle/close_bak/undotbs02_2.dbf ONLINE
/u01/app/oracle/oradata/orcl/tbs_c_01.dbf OFFLINE
/u01/app/oracle/oradata/orcl/tbs_d_01.dbf OFFLINE
/home/oracle/temp/TBS_RAMN01.dbf OFFLINE
/home/oracle/pbj_temp01.dbf OFFLINE
/home/oracle/close_bak/TBS_TEST02.dbf ONLINE
/u01/app/oracle/oradata/orcl/pbj_1.dbf OFFLINE
/home/oracle/temp/DATA01.dbf OFFLINE
/home/oracle/scott_idx01.dbf OFFLINE
/home/oracle/temp/TBS_TEST01.dbf OFFLINE
/u01/app/oracle/oradata/orcl/rc_tbs.dbf OFFLINE
alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/home/oracle/close_bak/redo03.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/home/oracle/close_bak/redo02.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/home/oracle/close_bak/redo01.log';
SYS@ORCL AS SYSDBA> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/close_bak/redo03.log
/home/oracle/close_bak/redo02.log
/home/oracle/close_bak/redo01.log
SYS@ORCL AS SYSDBA> recover database until time '2016-03-24:10:17:23';
Media recovery complete.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter database open resetlogs;
Database altered.
SYS@ORCL AS SYSDBA> select status from v$instance;
STATUS
-------
OPEN
TBS_USER@ORCL > select * from test04;
NO
----------
1
2
3
---------------------------------------------
불안전 복구를 했으므로 아카이브 재생성.
----------------------------------------------
SYS@ORCL AS SYSDBA> alter system switch logfile;
System altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> /
System altered.
SYS@ORCL AS SYSDBA> /
System altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/orcl
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
-----------------------------------------------------------------------------------------------
2) 잘못된 파일 update 장애복구
- scott.test07 테이블에 잘봇된 update 를 수행하고 commit 한 상태
- table을 update 이전으로 복구
------------------------------------------
tbs 생성 및 테이블 생성, 데이터 입력, update
-----------------------------------------
SYS@ORCL AS SYSDBA> create tablespace tbs datafile '/u01/app/oracle/oradata/orcl/tbs.dbf' size 10m;
Tablespace created.
SYS@ORCL AS SYSDBA> create table scott.test07 (no number, name varchar2(10));
Table created.
SYS@ORCL AS SYSDBA> insert into scott.test07 values (1,'AA');
1 row created.
SYS@ORCL AS SYSDBA> insert into scott.test07 values (2,'BB');
1 row created.
SYS@ORCL AS SYSDBA> commit;
Commit complete.
SYS@ORCL AS SYSDBA> select * from scott.test07;
NO NAME
---------- ----------
1 AA
2 BB
SYS@ORCL AS SYSDBA> select to_char(sysdate, 'YYYY-MM-DD:HH24:MI:SS') "Time" from dual;
Time
-------------------
2016-03-24:15:29:21
SYS@ORCL AS SYSDBA> update scott.test07 set name='error';
2 rows updated.
SYS@ORCL AS SYSDBA> COMMIT;
Commit complete.
SYS@ORCL AS SYSDBA> SELECT * FROM SCOTT.TEST07;
NO NAME
---------- ----------
1 error
2 error
------------------------------------------------------------
복구 시작....
1) 컨트롤 파일 위치 변경
2) redo 위치 변경
3) recover until time
------------------------------------------------------------
*.control_files='/home/oralce/close_bak/control01.ctl','/home/oracle/close_bak/control02.ctl'
#*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
[oracle@DBTEST dbs]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /home/oracle/close_bak/control01.ctl
[oracle@DBTEST dbs]$ cp /u01/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/close_bak/
[oracle@DBTEST orcl]$ cp redo* /home/oracle/close_bak/
alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/home/oracle/close_bak/redo03.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/home/oracle/close_bak/redo02.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/home/oracle/close_bak/redo01.log';
alter database rename file '/home/oracle/close_bak/redo03.log' to '/u01/app/oracle/oradata/orcl/redo03.log';
alter database rename file '/home/oracle/close_bak/redo02.log' to '/u01/app/oracle/oradata/orcl/redo02.log';
alter database rename file '/home/oracle/close_bak/redo01.log' to '/u01/app/oracle/oradata/orcl/redo01.log';
SYS@ORCL AS SYSDBA> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/close_bak/redo03.log
/home/oracle/close_bak/redo02.log
/home/oracle/close_bak/redo01.log
SYS@ORCL AS SYSDBA> recover database until time '2016-03-24:15:29:21';
Media recovery complete.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> alter database open resetlogs;
Database altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select * from scott.test07;
NO NAME
---------- ----------
1 AA
2 BB
'Oracle > backup' 카테고리의 다른 글
| RMAN 완전복구/불완전복구 (0) | 2022.01.26 |
|---|---|
| Cold Backup && Hot Backup (0) | 2022.01.26 |
| RMAN (Recovery Manager) (0) | 2022.01.26 |
| BCT(Block change tracking) (0) | 2022.01.26 |