Case 12. Drop table 삭제 복구하기
- 무정지 상태에서 즉시 복구하기 – clone db / exp / imp 사용함
* 사용자의 장애로 drop 된 특정 테이블을 삭제되기 전 상태로 복구해야만 합니다.
중요한 것은 현재 서비스는 중단되어서는 안 된다는 것입니다.
테이블 삭제 시간은 알고 있는 것으로 가정합니다.
* 실제 작업 순서
Step 1. 현재 상태 백업 (Begin backup)
Step 2. 테스트 테이블 생성 후 데이터 입력 – 최종 데이터 커밋 후 시간 확보할 것
Step 3. Drop table 장애 발생
Step 4. Rac1 노드에서 clone db 생성 작업
Step 5. Rac1 노드에서 clone db로 복구한 테이블을 exp 후 imp 해서 복구 완료함
Step 1. 현재 상태 백업 (Begin backup)
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ ------------------- --------- ---------------- ------------- -------------- --------
1 /dev/raw/raw13 49 4 CURRENT 1 NO
1 /dev/raw/raw14 49 4 CURRENT 1 NO
2 /dev/raw/raw15 49 2 ACTIVE 1 YES
2 /dev/raw/raw16 49 2 ACTIVE 1 YES
3 /dev/raw/raw17 49 3 ACTIVE 1 YES
3 /dev/raw/raw18 49 3 ACTIVE 1 YES
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 INACTIVE 2 YES
5 /dev/raw/raw22 49 2 INACTIVE 2 YES
6 /dev/raw/raw23 49 3 ACTIVE 2 YES
6 /dev/raw/raw24 49 3 ACTIVE 2 YES
12 rows selected.
SQL> !vi begin.sql
conn / as sysdba;
alter tablespace system begin backup;
!dd if=/dev/raw/raw6 of=/data/backup/open/raw6_system bs=8k
alter tablespace system end backup;
alter tablespace sysaux begin backup;
!dd if=/dev/raw/raw7 of=/data/backup/open/raw7_sysaux bs=8k
alter tablespace sysaux end backup;
alter tablespace undotbs1 begin backup;
!dd if=/dev/raw/raw8 of=/data/backup/open/raw8_undotbs1 bs=8k
alter tablespace undotbs1 end backup;
alter tablespace undotbs2 begin backup;
!dd if=/dev/raw/raw9 of=/data/backup/open/raw9_undotbs2 bs=8k
alter tablespace undotbs2 end backup;
alter tablespace users begin backup;
!dd if=/dev/raw/raw10 of=/data/backup/open/raw10_users bs=8k
alter tablespace users end backup;
:wq!
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Step 2. 테스트 테이블 생성 후 데이터 입력 – 최종 데이터 커밋 후 시간 확보할 것
SQL> create user scott identified by tiger
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> create table scott.tt10 (no number);
Table created.
SQL> insert into scott.tt10 values (1);
1 row created.
SQL> insert into scott.tt10 values (2);
1 row created.
SQL> insert into scott.tt10 values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,’YYYY-MM-DD:HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2010-12-08:02:13:17
SQL> save t.sql
Created file t.sql
SQL> select * from scott.tt10;
NO
---------------
1
2
3
Step 3. Drop table 장애 발생
SQL> drop table scott.tt10 purge;
Table dropped.
SQL> select * from scott.tt10;
select * from scott.tt10
*
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.
SQL> /
System altered.
Log switch 는 양쪽 노드 모두에서 충분히 일으켜야 합니다.
Step 4. Rac1 노드에서 clone db 생성 작업
1. Create pfile from spfile 로 파라미터 파일 생성
SQL> create pfile from spfile;
File created.
SQL> !
[oracle@rac1 ~]$ cd product/10g/db/dbs
[oracle@rac1 dbs]$ ls
hc_clone.dat init.ora initrac1.ora hc_rac1.dat initdw.ora orapwrac1
2. 새로 생성된 pfile 이름 변경 후 vi로 열어서 rac2 노드는 전부 주석처리, db_name ,control_files 경로를 변경한다.
[oracle@rac1 dbs]$ vi initrac1.ora
#rac1.__db_cache_size=92274688
#rac2.__db_cache_size=92274688
#rac1.__java_pool_size=4194304
#rac2.__java_pool_size=4194304
#rac1.__large_pool_size=4194304
#rac2.__large_pool_size=4194304
#rac1.__shared_pool_size=75497472
#rac2.__shared_pool_size=75497472
#rac1.__streams_pool_size=0
#rac2.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/rac/adump'
*.background_dump_dest='/home/oracle/admin/rac/bdump'
#*.cluster_database_instances=2
#*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='/data/temp/control01.ctl' <- 파일명 변경
*.core_dump_dest='/home/oracle/admin/rac/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='clone' <- db 명 변경
#rac2.instance_number=2
#rac1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest_1='location=/data/arc1'
*.log_archive_dest_2='location=/data/arc2'
*.nls_language='KOREAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=60817408
*.processes=150
#*.remote_listener='LISTENERS_RAC'
*.remote_login_passwordfile='exclusive'
*.sga_target=184549376
#rac2.thread=2
#rac1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#rac1.undo_tablespace='UNDOTBS1'
#rac2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/oracle/admin/rac/udump':wq!
[oracle@rac1 dbs]$ mv initrac1.ora initclone.ora
[oracle@rac1 dbs]$ ls
hc_clone.dat init.ora initdw.ora hc_rac1.dat initclone.ora orapwrac1
3. 운영 DB에서 control file 백업을 받아 재생성 준비
SQL> alter database backup controlfile to trace as '/home/oracle/re.sql';
Database altered.
4. 백업 받은 control file 내용에서 reuse->set , rac->clone , redo log file 경로 변경하고 데이터 파일 경로를 수정해 줍니다.
SQL> !
[oracle@rac1 ~]$ vi /home/oracle/re.sql
STARTUP NOMOUNT
2 CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
3 MAXLOGFILES 192
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 1024
6 MAXINSTANCES 32
7 MAXLOGHISTORY 292
8 LOGFILE
9 GROUP 1 (
10 '/data/temp/redo01.log'
11 ) SIZE 5M,
12 GROUP 2 (
13 '/data/temp/redo02.log'
14 ) SIZE 5M,
15 GROUP 3 (
16 '/data/temp/redo03.log'
17 ) SIZE 5M
18 DATAFILE
19 '/data/temp/raw6',
20 '/data/temp/raw8',
21 '/data/temp/raw7',
22 '/data/temp/raw9',
23 '/data/temp/raw10'
24 CHARACTER SET KO16MSWIN949
25 ;
5. 백업 받아 두었던 데이터 파일을 clone 경로로 복사한다.
SQL> !
[oracle@rac1 ~]$ cd /data/backup/close
[oracle@rac1 close]$ ls
raw10_users raw6_system raw7_sysaux raw8_undotbs1 raw9_undotbs2
[oracle@rac1 close]$ dd if=/data/backup/open/raw10_users of=/data/temp/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 close]$ dd if=/data/backup/open/raw6_system of=/data/temp/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
[oracle@rac1 close]$ dd if=/data/backup/open/raw7_sysaux of=/data/temp/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
[oracle@rac1 close]$ dd if=/data/backup/open/raw8_undotbs1 of=/data/temp/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 close]$ dd if=/data/backup/open/raw9_undotbs2 of=/data/temp/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
6. Export ORACLE_SID=clone 로 변경해 준 후 로그인해서 위 4번 과정에서 만든 control file을 실행해서 control file을 재생성 한다.
[oracle@rac1 ~]$ export ORACLE_SID=clone
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 6 23:05:08 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> @/home/oracle/re.sql
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 67112160 bytes
Database Buffers 109051904 bytes
Redo Buffers 7118848 bytes
Control file created.
7. RAC2 의 아카이브 파일을 RAC1으로 복사해온다
SQL> !scp rac2:/data/arc1/* /data/arc1/
2_12_737007054.dbf 100% 2865KB 2.8MB/s 00:01
2_13_737007054.dbf 100% 10MB 4.9MB/s 00:02
2_14_737007054.dbf 100% 953KB 953.0KB/s 00:00
SQL> !scp rac2:/data/arc2/* /data/arc2/
2_12_737007054.dbf 100% 2865KB 2.8MB/s 00:00
2_13_737007054.dbf 100% 10MB 4.9MB/s 00:02
2_14_737007054.dbf 100% 953KB 953.0KB/s 00:00
8. Recover database until time ‘ 시간 ‘ using backup controlfile 로 복구를 한다.
SQL> recover database until time '2010-12-08:02:13:17' using backup controlfile;
ORA-00279: change 604852 generated at 12/08/2010 01:55:17 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_39_737007054.dbf
ORA-00280: change 604852 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 604852 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_35_737007054.dbf
ORA-00326: log begins at change 607095, need earlier change 604852
ORA-00334: archived log: '/data/arc2/2_35_737007054.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/temp/raw6'
SQL> recover database until time '2010-12-08:02:13:17' using backup controlfile;
ORA-00279: change 604852 generated at 12/08/2010 01:55:17 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_39_737007054.dbf
ORA-00280: change 604852 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 604852 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_34_737007054.dbf
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
간혹 여기서 아래와 같은 에러메세지가 뜨면서 open 이 안되는 경우가 있습니다.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
위 에러를 해결하기 위해 파라미터 파일에 아래의 히든값을 추가해 주고 재 시작 시킵니다.
_no_recovery_through_resetlogs=true
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 67112160 bytes
Database Buffers 109051904 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
Step 5. Rac1 노드에서 clone db로 복구한 테이블을 exp 후 imp 해서 복구 완료함
SQL> select * from scott.tt10;
NO
---------------
1
2
3
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@rac1 ~]$ export ORACLE_SID=clone
[oracle@rac1 ~]$ exp scott/tiger file=/home/oracle/scott.dmp tables=tt10 ;
Export: Release 10.2.0.4.0 - Production on Wed Dec 8 02:48:19 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, 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 TT10 3 rows exported
Export terminated successfully without warnings.
[oracle@rac1 ~]$ export ORACLE_SID=rac1
[oracle@rac1 ~]$ imp scott/tiger file=/home/oracle/scott.dmp ignore=y
Import: Release 10.2.0.4.0 - Production on Wed Dec 8 02:48:58 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, 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 SCOTT's objects into SCOTT
. . importing table "TT10" 3 rows imported
Import terminated successfully without warnings.
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 02:49:12 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select * from scott.tt10;
NO
----------------
1
2
3
지금까지 사용자의 실수로 drop 된 테이블을 운영중인 RAC 서버를 중단하지 않고
clone db와 exp/imp를 이용하여 실시간으로 복구하는 방법을 배웠습니다.
Case 13. DML 장애 복구하기
- 무정지 상태에서 즉시 복구하기 – clone db / exp / imp 사용함
이번 장애는 RAC 환경에서 잘못된 update 후 commit 이 수행 된 경우 commit 이전 상태로 데이터를 복구하는 경우입니다.
Step 1. 전체 백업 수행 (begin backup)
Step 2. 테스트 테이블 생성 후 update 장애 발생시킴
Step 3. Rac1 노드에서 clone db 생성 작업
Step 4. Rac1 노드에서 clone db로 복구한 테이블을 exp 후 imp 해서 복구 완료함
Step 1. 전체 백업 수행 (begin backup)
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Step 2. 테스트 테이블 생성 후 update 장애 발생시킴
SQL> create table scott.tt20 (no number,name varchar2(10));
Table created.
SQL> insert into scott.tt20 values (1,'AAA');
1 row created.
SQL> insert into scott.tt20 values (2,'BBB');
1 row created.
SQL> insert into scott.tt20 values (3,'CCC');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from scott.tt20;
NO NAME
---------- ----------
1 AAA
2 BBB
3 CCC
SQL> @t
TO_CHAR(SYSDATE,'YY
---------------------------
2010-12-08:12:10:04 <- 복구 할 시간입니다.
SQL> update scott.tt20 set name='FFF' ;
3 rows updated.
SQL> select * from scott.tt20 ;
NO NAME
---------- ----------
1 FFF
2 FFF
3 FFF
SQL> commit; <- 잘못된 업데이트 후 커밋함.
Commit complete.
SQL> @t
TO_CHAR(SYSDATE,'YY
---------------------------2010-12-08:12:10:45
SQL> rollback;
Rollback complete.
SQL> select * from scott.tt20;
NO NAME
------------ ----------
1 FFF
2 FFF
3 FFF
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
양쪽 노드 모두 로그스위치를 수차례 발생시켜 아카이브 파일을 생성시킵니다.
Step 3. Rac1 노드에서 clone db 생성 작업
1. Create pfile from spfile 로 파라미터 파일 생성
SQL> create pfile from spfile;
File created.
2. 새로 생성된 pfile 이름 변경 후 vi로 열어서 rac2 노드는 전부 주석처리, db_name ,control_files 경로를 변경한다.
SQL> !
[oracle@rac1 ~]$ cd product/10g/db/dbs/
[oracle@rac1 dbs]$ ls
hc_clone.dat init.ora initdw.ora orapwrac1 hc_rac1.dat initrac1.ora
[oracle@rac1 dbs]$ mv initrac1.ora initclone.ora
[oracle@rac1 dbs]$ vi initclone.ora
#rac1.__db_cache_size=92274688
#rac2.__db_cache_size=92274688
#rac1.__java_pool_size=4194304
#rac2.__java_pool_size=4194304
#rac1.__large_pool_size=4194304
#rac2.__large_pool_size=4194304
#rac1.__shared_pool_size=75497472
#rac2.__shared_pool_size=75497472
#rac1.__streams_pool_size=0
#rac2.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/rac/adump'
*.background_dump_dest='/home/oracle/admin/rac/bdump'
#*.cluster_database_instances=2
#*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='/data/temp/control01.ctl'
*.core_dump_dest='/home/oracle/admin/rac/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='clone'
#rac2.instance_number=2
#rac1.instance_number=1
*.job_queue_processes=10
*.log_archive_dest_1='location=/data/arc1'
*.log_archive_dest_2='location=/data/arc2'
*.nls_language='KOREAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=60817408
*.processes=150
#*.remote_listener='LISTENERS_RAC'
*.remote_login_passwordfile='exclusive'
*.sga_target=184549376
#rac2.thread=2
#rac1.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#rac1.undo_tablespace='UNDOTBS1'
#rac2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/home/oracle/admin/rac/udump'
:wq!
3. 운영 DB에서 control file 백업을 받아 재생성 준비
[oracle@rac1 dbs]$ exit
exit
SQL> alter database backup controlfile to trace as '/home/oracle/re2.sql';
Database altered.
4. 백업 받은 control file 내용에서 reuse->set , rac->clone , redo log file 경로 변경하고 데이터 파일 경로를 수정합니다.
SQL> !
[oracle@rac1 ~]$ vi re2.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/data/temp/redo01.log'
) SIZE 5M,
GROUP 2 (
'/data/temp/redo02.log'
) SIZE 5M,
GROUP 3 (
'/data/temp/redo03.log'
) SIZE 5M
DATAFILE
'/data/temp/raw6',
'/data/temp/raw8',
'/data/temp/raw7',
'/data/temp/raw9',
'/data/temp/raw10'
CHARACTER SET KO16MSWIN949
;
:wq!
5. 백업 받아두었던 데이터 파일을 clone 경로로 복사한다
SQL> !
[oracle@rac1 ~]$ cd /data/backup/open/
[oracle@rac1 open]$ ls
raw10_users raw6_system raw7_sysaux raw8_undotbs1 raw9_undotbs2
[oracle@rac1 open]$ dd if=/data/backup/open/raw10_users of=/data/temp/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw6_system of=/data/temp/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw7_sysaux of=/data/temp/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw8_undotbs1 of=/data/temp/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw9_undotbs2 of=/data/temp/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
6. Export ORACLE_SID=clone 로 변경해 준 후 로그인해서 위 4번과정에서 만든 control file 을 실행해서 control file을 재생성 한다.
[oracle@rac1 ~]$ export ORACLE_SID=clone
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 13:26:39 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> @re
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266488 bytes
Variable Size 62917832 bytes
Database Buffers 117440512 bytes
Redo Buffers 2924544 bytes
Control file created.
7. RAC2 의 아카이브 파일을 RAC1으로 복사해온다
SQL> !scp rac2:/data/arc1/* /data/arc1/
2_12_737007054.dbf 100% 2865KB 2.8MB/s 00:01
2_13_737007054.dbf 100% 10MB 4.9MB/s 00:02
2_14_737007054.dbf 100% 953KB 953.0KB/s 00:01
2_15_737007054.dbf 100% 46KB 46.0KB/s 00:00
2_16_737007054.dbf 100% 1024 1.0KB/s 00:00
2_17_737007054.dbf 100% 603KB 602.5KB/s 00:00
2_18_737007054.dbf 100% 1024 1.0KB/s 00:00
2_19_737007054.dbf 100% 1024 1.0KB/s 00:00
2_20_737007054.dbf 100% 1024 1.0KB/s 00:00
2_21_737007054.dbf 100% 357KB 357.0KB/s 00:00
2_22_737007054.dbf 100% 1024 1.0KB/s 00:00
---- 이하 생략 -----
SQL> !scp rac2:/data/arc2/* /data/arc2/
2_12_737007054.dbf 100% 2865KB 2.8MB/s 00:00
2_14_737007054.dbf 100% 953KB 953.0KB/s 00:01
2_15_737007054.dbf 100% 46KB 46.0KB/s 00:00
2_16_737007054.dbf 100% 1024 1.0KB/s 00:00
2_17_737007054.dbf 100% 603KB 602.5KB/s 00:00
2_18_737007054.dbf 100% 1024 1.0KB/s 00:00
2_19_737007054.dbf 100% 1024 1.0KB/s 00:00
2_20_737007054.dbf 100% 1024 1.0KB/s 00:00
---- 이하 생략 ----
8. Recover database until time ‘ 시간 ‘ using backup controlfile 로 복구를 한다
SQL> recover database until time '2010-12-08:12:10:04' using backup controlfile;
ORA-00279: change 652868 generated at 12/08/2010 11:54:51 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_47_737007054.dbf
ORA-00280: change 652868 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 652868 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_46_737007054.dbf
ORA-00326: log begins at change 656321, need earlier change 652868
ORA-00334: archived log: '/data/arc2/2_46_737007054.dbf' <- 아카이브 파일 정보가 틀려 복구 안됨.
/data/arc2/ 아래의 파일과 위에서 제시하는 시간을 비교해서 적당한 아카이브 파일을 찾아야 합니다.
또는 아래의 쿼리를 수행해서 아카이브 파일이 만들어진 시간을 조회해서 찾아낼 수도 있습니다.
*아카이브 로그가 만들어진 시간을 찾는 쿼리*
SQL> set lines 500
SQL> col name for a50
SQL> SELECT THREAD# ,SEQUENCE# , FIRST_CHANGE#, NEXT_CHANGE#, NAME,
2 TO_CHAR(FIRST_TIME,'YYYY-MM-DD:HH24:MI:SS') FIRST_TIME
3 FROM V$ARCHIVED_LOG;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/temp/raw6'
SQL> recover database until time '2010-12-08:12:10:04' using backup controlfile;
ORA-00279: change 652868 generated at 12/08/2010 11:54:51 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_47_737007054.dbf
ORA-00280: change 652868 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_40_737007054.dbf
ORA-00325: archived log for thread 1, wrong thread # 2 in header
ORA-00334: archived log: '/data/arc2/2_40_737007054.dbf'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/temp/raw6'
SQL> recover database until time '2010-12-08:12:10:04' using backup controlfile;
ORA-00279: change 652868 generated at 12/08/2010 11:54:51 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_47_737007054.dbf
ORA-00280: change 652868 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 652868 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_40_737007054.dbf
ORA-00328: archived log ends at change 607120, need later change 652868
ORA-00334: archived log: '/data/arc2/2_40_737007054.dbf
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/temp/raw6'
SQL> recover database until time '2010-12-08:12:10:04' using backup controlfile;
ORA-00279: change 652868 generated at 12/08/2010 11:54:51 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_47_737007054.dbf
ORA-00280: change 652868 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 652868 generated at needed for thread 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/data/arc2/2_43_737007054.dbf
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled
복구는 완료 되었으나 resetlogs 로 open 이 안되어 아래 히든 파라미터를 파라미터 파일에 적용 후
재시작해서 resetlogs 로 open 해야 합니다.
SQL> !vi /home/oracle/product/10g/db/dbs/initclone.ora
_no_recovery_through_resetlogs=true <- 추가하세요
:wq!
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266488 bytes
Variable Size 62917832 bytes
Database Buffers 117440512 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from scott.tt20;
NO NAME
------------ ----------
1 AAA
2 BBB
3 CCC
Step 4. Rac1 노드에서 clone db로 복구한 테이블을 exp 후 imp 해서 복구 완료함
[oracle@rac1 ~]$ exp scott/tiger file=scott20.dmp tables=tt20
Export: Release 10.2.0.4.0 - Production on Wed Dec 8 13:45:49 2010
opyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, 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 TT20 3 rows exp orted
Export terminated successfully without warnings.
[oracle@rac1 ~]$ export ORACLE_SID=rac1
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Dec 8 13:46:15 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select * from scott.tt20;
NO NAME
------------ ----------
1 FFF
2 FFF
3 FFF
SQL> truncate table scott.tt20;
Table truncated.
SQL> !imp scott/tiger file=scott20.dmp ignore=y
Import: Release 10.2.0.4.0 - Production on Wed Dec 8 13:47:09 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, 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 SCOTT's objects into SCOTT
. . importing table "TT20" 3 rows im ported
Import terminated successfully without warnings.
SQL> select * from scott.tt20;
NO NAME
-------------- ----------
1 AAA
2 BBB
3 CCC <- 복구 완료 되었습니다
Case 5. 일부 컨트롤 파일 장애
Step 1. 현재 컨트롤파일 확인 후 1개의 파일 장애 발생
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw24
/dev/raw/raw25
/dev/raw/raw26
SQL> !dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd: writing `/dev/raw/raw24': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
Alert log 파일에 아래의 내용으로 에러가 발생함을 알 수 있습니다.
Read from controlfile member '/dev/raw/raw24' has found a corrupted block (blk# 35, seq# 0)
Hex dump of (file 0, block 35) in trace file /home/oracle/admin/racdb/bdump/racdb1_lmon_4702.trc
Corrupt block relative dba: 0x00000023 (file 0, block 35)
Completely zero block found during control file block read
Hex dump of (file 0, block 35) in trace file /home/oracle/admin/racdb/bdump/racdb1_lmon_4702.trc
Corrupt block relative dba: 0x00000023 (file 0, block 35)
Completely zero block found during control file block read
Thu Oct 7 04:13:12 2010
Errors in file /home/oracle/admin/racdb/bdump/racdb1_lmon_4702.trc:
ORA-00202: 제어 파일: '/dev/raw/raw24'
Thu Oct 7 04:13:12 2010
Errors in file /home/oracle/admin/racdb/bdump/racdb1_lmon_4702.trc:
ORA-00227: 제어 파일에서 감지된 손상된 블록: (블록 35, 블록 수 1).
ORA-00202: 제어 파일: '/dev/raw/raw24'
LMON: terminating instance due to error 227
Thu Oct 7 04:13:13 2010
System state dump is made for local instance
System State dumped to trace file /home/oracle/admin/racdb/bdump/racdb1_diag_4695.trc
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03135: connection lost contact
SQL> !crs_stat -t
Name Type Target State Host
---------------- --------------- ------------ ----------- ----------
ora.racdb.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
ora....B1.lsnr application ONLINE ONLINE racdb1
ora.racdb1.gsd application ONLINE ONLINE racdb1
ora.racdb1.ons application ONLINE ONLINE racdb1
ora.racdb1.vip application ONLINE ONLINE racdb1
ora....B2.lsnr application ONLINE ONLINE racdb2
ora.racdb2.gsd application ONLINE ONLINE racdb2
ora.racdb2.ons application ONLINE ONLINE racdb2
ora.racdb2.vip application ONLINE ONLINE racdb2
step 2. 파일 복원 후 복구
SQL> !dd if=/dev/raw/raw25 of=/dev/raw/raw24 bs=8k
6656+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> startup <- 양쪽 노드 모두 실행합니다.
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
Case 6. 모든 컨트롤 파일 장애 – 삭제 또는 old control file 발생으로 재 생성해서 해결하는 방법
Step 1. 컨트롤 파일 백업 수행
SQL> alter database backup controlfile to '/data/backup/open/control01.ctl' ;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> !dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd: writing `/dev/raw/raw24': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw25 bs=8k
dd: writing `/dev/raw/raw25': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw26 bs=8k
dd: writing `/dev/raw/raw26': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-03135: connection lost contact
Step 2. 백업 복원 후 open 시도
SQL> !dd if=/data/backup/open/control01.ctl of=/dev/raw/raw24 bs=8k
1866+0개의 레코드를 입력하였습니다
1866+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/open/control01.ctl of=/dev/raw/raw25 bs=8k
1866+0개의 레코드를 입력하였습니다
1866+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/open/control01.ctl of=/dev/raw/raw26 bs=8k
1866+0개의 레코드를 입력하였습니다
1866+0개의 레코드를 출력하였습니다
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@racdb1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 04:26:29 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
Step 3. 컨트롤 파일 재 생성
SQL> alter database backup controlfile to trace as '/home/oracle/re.sql';
Database altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !
[oracle@racdb1 ~]$ vi /home/oracle/re.sql <- 컨트롤 파일 다시 생성 스크립트 작성
반드시 NORESETLOGS로 만드세요!
-- 기존 redo log file 에 이상이 없기 때문에 NORESETLOGS 가능합니다 !!
1 CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/dev/raw/raw12',
10 '/dev/raw/raw13'
11 ) SIZE 99M,
12 GROUP 2 (
13 '/dev/raw/raw14',
14 '/dev/raw/raw15'
15 ) SIZE 99M,
16 GROUP 3 (
17 '/dev/raw/raw16',
18 '/dev/raw/raw17'
19 ) SIZE 99M,
20 GROUP 4 (
21 '/dev/raw/raw18',
22 '/dev/raw/raw19'
23 ) SIZE 99M,
24 GROUP 5 (
25 '/dev/raw/raw20',
26 '/dev/raw/raw21'
27 ) SIZE 99M,
28 GROUP 6 (
29 '/dev/raw/raw22',
30 '/dev/raw/raw23'
31 ) SIZE 99M
32 DATAFILE
33 '/dev/raw/raw6',
34 '/dev/raw/raw8',
35 '/dev/raw/raw7',
36 '/dev/raw/raw9',
37 '/dev/raw/raw10',
38 '/dev/raw/raw28'
39 CHARACTER SET KO16KSC5601
40 ;
:wq!
[oracle@racdb1 ~]$ exit
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> @/home/oracle/re.sql
Control file created.
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dev/raw/raw6'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 4. 양쪽 노드 모두 재시작
Case 6. 대기중인 리두 로그 파일 그룹 장애 – 아카이브 완료된 상태
SQL> !vi log.sql
set line 200
col group# for 999
col member for a20
select a.group#,a.member,b.bytes/1024/1024 MB , b.archived,b.status
from v$logfile a, v$log b
where a.group#=b.group#
order by 1
/
SQL> set pagesize 20
SQL> @log
GROUP# MEMBER MB ARC STATUS
---------- -------------------- ---------- --------- ----------------
1 /dev/raw/raw12 99 NO CURRENT
1 /dev/raw/raw13 99 NO CURRENT
2 /dev/raw/raw15 99 YES INACTIVE
2 /dev/raw/raw14 99 YES INACTIVE
3 /dev/raw/raw16 99 YES INACTIVE
3 /dev/raw/raw17 99 YES INACTIVE
4 /dev/raw/raw18 99 NO CURRENT
4 /dev/raw/raw19 99 NO CURRENT
5 /dev/raw/raw20 99 YES INACTIVE
5 /dev/raw/raw21 99 YES INACTIVE
6 /dev/raw/raw23 99 YES INACTIVE
6 /dev/raw/raw22 99 YES INACTIVE
12 rows selected.
SQL> !dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd: writing `/dev/raw/raw15': 장치에 남은 공간이 없음
12801+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
12801+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
위에서 보듯이 운영에 지장 없습니다. 이번엔 같은 장애를 일으키고 종료 후 다시 시작해보겠습니다.
SQL> @log
GROUP# MEMBER MB ARC STATUS
---------- -------------------- ---------- -------- ----------------
1 /dev/raw/raw12 99 YES INACTIVE
1 /dev/raw/raw13 99 YES INACTIVE
2 /dev/raw/raw15 99 NO CURRENT
2 /dev/raw/raw14 99 NO CURRENT
3 /dev/raw/raw16 99 YES INACTIVE
3 /dev/raw/raw17 99 YES INACTIVE
4 /dev/raw/raw18 99 YES INACTIVE
4 /dev/raw/raw19 99 YES INACTIVE
5 /dev/raw/raw20 99 YES INACTIVE
5 /dev/raw/raw21 99 YES INACTIVE
6 /dev/raw/raw23 99 NO CURRENT
6 /dev/raw/raw22 99 NO CURRENT
12 rows selected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arc2
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29
SQL> !dd if=/dev/zero of=/dev/raw/raw12 bs=4k
dd: writing `/dev/raw/raw12': 장치에 남은 공간이 없음
25601+0개의 레코드를 입력하였습니다
25600+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=4k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
25601+0개의 레코드를 입력하였습니다
25600+0개의 레코드를 출력하였습니다
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Alert log 를 확인합니다.
에러가 발생하면서 강제로 종료되는 것을 알 수 있습니다.
Thu Oct 7 20:11:05 2010
Errors in file /home/oracle/admin/racdb/bdump/racdb1_lms1_6398.trc:
ORA-00316: ?? (??? ?) ???? ?? ? ????? ????
Thu Oct 7 20:11:05 2010
System state dump is made for local instance
System State dumped to trace file /home/oracle/admin/racdb/bdump/racdb1_diag_6378.trc
Thu Oct 7 20:11:06 2010
Trace dumping is performing id=[cdmp_20101007201105]
Thu Oct 7 20:11:07 2010
Instance terminated by USER, pid = 6589
해결방법 :
Inactive 상태이며 아카이브도 완료된 상태이므로 그룹을 삭제 후 다시 재 생성합니다.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@racdb1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 20:16:34 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
SQL> @log
GROUP# MEMBER MB ARC STATUS
---------- -------------------- ---------- ------ ----------------
2 /dev/raw/raw14 99 NO CURRENT
2 /dev/raw/raw15 99 NO CURRENT
3 /dev/raw/raw17 99 YES INACTIVE
3 /dev/raw/raw16 99 YES INACTIVE
4 /dev/raw/raw18 99 YES INACTIVE
4 /dev/raw/raw19 99 YES INACTIVE
5 /dev/raw/raw20 99 YES INACTIVE
5 /dev/raw/raw21 99 YES INACTIVE
6 /dev/raw/raw23 99 NO CURRENT
6 /dev/raw/raw22 99 NO CURRENT
10 rows selected.
SQL> alter database add logfile thread 1 group 1
2 ('/dev/raw/raw12','/dev/raw/raw13') size 99M;
Database altered.
SQL> @log
GROUP# MEMBER MB ARC STATUS
---------- -------------------- ---------- ------- ----------------
1 /dev/raw/raw12 99 YES UNUSED
1 /dev/raw/raw13 99 YES UNUSED
2 /dev/raw/raw15 99 NO CURRENT
2 /dev/raw/raw14 99 NO CURRENT
3 /dev/raw/raw16 99 YES INACTIVE
3 /dev/raw/raw17 99 YES INACTIVE
4 /dev/raw/raw18 99 YES INACTIVE
4 /dev/raw/raw19 99 YES INACTIVE
5 /dev/raw/raw20 99 YES INACTIVE
5 /dev/raw/raw21 99 YES INACTIVE
6 /dev/raw/raw23 99 NO CURRENT
6 /dev/raw/raw22 99 NO CURRENT
12 rows selected.
Case 7. Current Redo log file 장애 – 한쪽 노드만 장애 발생
SQL> @log
GROUP# MEMBER MB ARC STATUS
------ -------------------- ---------- ------ ----------------
1 /dev/raw/raw12 99 YES INACTIVE
1 /dev/raw/raw13 99 YES INACTIVE
2 /dev/raw/raw15 99 YES INACTIVE
2 /dev/raw/raw14 99 YES INACTIVE
3 /dev/raw/raw16 99 NO CURRENT
3 /dev/raw/raw17 99 NO CURRENT
4 /dev/raw/raw18 99 NO CURRENT
4 /dev/raw/raw19 99 NO CURRENT
5 /dev/raw/raw20 99 YES INACTIVE
5 /dev/raw/raw21 99 YES INACTIVE
6 /dev/raw/raw23 99 YES INACTIVE
6 /dev/raw/raw22 99 YES INACTIVE
12 rows selected.
SQL> !dd if=/dev/zero of=/dev/raw/raw16 bs=4k
dd: writing `/dev/raw/raw16': 장치에 남은 공간이 없음
25601+0개의 레코드를 입력하였습니다
25600+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=4k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
25601+0개의 레코드를 입력하였습니다
25600+0개의 레코드를 출력하였습니다
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Alert log 파일을 확인합니다.
Thu Oct 7 20:27:43 2010
Errors in file /home/oracle/admin/racdb/bdump/racdb1_lgwr_9866.trc:
ORA-00316: 로그 3 (스레드 1의) 헤더내의 유형 0는 로그파일이 아닙니다
ORA-00312: 온라인 로그 3 스레드 1: '/dev/raw/raw17'
ORA-00316: 로그 3 (스레드 1의) 헤더내의 유형 0는 로그파일이 아닙니다
ORA-00312: 온라인 로그 3 스레드 1: '/dev/raw/raw16'
Thu Oct 7 20:27:43 2010
Errors in file /home/oracle/admin/racdb/bdump/racdb1_lgwr_9866.trc:
ORA-00316: 로그 3 (스레드 1의) 헤더내의 유형 0는 로그파일이 아닙니다
ORA-00312: 온라인 로그 3 스레드 1: '/dev/raw/raw17'
ORA-00316: 로그 3 (스레드 1의) 헤더내의 유형 0는 로그파일이 아닙니다
ORA-00312: 온라인 로그 3 스레드 1: '/dev/raw/raw16'
Thu Oct 7 20:27:43 2010
LGWR: terminating instance due to error 316
System state dump is made for local instance
System State dumped to trace file /home/oracle/admin/racdb/bdump/racdb1_diag_9833.trc
Thu Oct 7 20:27:46 2010
Trace dumping is performing id=[cdmp_20101007202743]
Thu Oct 7 20:27:48 2010
Instance terminated by LGWR, pid = 9866
SQL> !crs_stat -t
Name Type Target State Host
----------------- ------------- ----------- ----------- --------
ora.racdb.db application OFFLINE OFFLINE
ora....b1.inst application ONLINE OFFLINE
ora....b2.inst application ONLINE OFFLINE
ora....B1.lsnr application ONLINE ONLINE racdb1
ora.racdb1.gsd application ONLINE ONLINE racdb1
ora.racdb1.ons application ONLINE ONLINE racdb1
ora.racdb1.vip application ONLINE ONLINE racdb1
ora....B2.lsnr application ONLINE ONLINE racdb2
ora.racdb2.gsd application ONLINE ONLINE racdb2
ora.racdb2.ons application ONLINE ONLINE racdb2
ora.racdb2.vip application ONLINE ONLINE racdb2
위에서 crs_stat 했을 때 양쪽 노드 모두 인스턴스가 비정상적으로 종료된 것을 알 수 있습니다.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@racdb1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 7 20:29:04 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance. <- 강제 종료되어 현재 Shutdown 상태인 것을 알 수 있습니다.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1267020 bytes
Variable Size 92277428 bytes
Database Buffers 180355072 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw16'
복구방법: 아래와 같이 두 가지의 복구 방법이 있으니 상황에 맞게 사용하세요
7-1. 백업된 cold 백업 데이터파일을 전부 복원 한 후 양쪽 노드의 아카이브 로그를 이용해 복구합니다.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw6
/dev/raw/raw8
/dev/raw/raw7
/dev/raw/raw9
/dev/raw/raw10
SQL> !dd if=/data/backup/close/raw6 of=/dev/raw/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/close/raw7 of=/dev/raw/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/close/raw8 of=/dev/raw/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/close/raw9 of=/dev/raw/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
SQL> !dd if=/data/backup/close/raw10 of=/dev/raw/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
SQL> !scp racdb2:/data/arc1/2_* /data/arc1/
2_10_730146831.dbf 100% 9082KB 8.9MB/s 00:01
2_11_730146831.dbf 100% 199KB 199.0KB/s 00:00
2_12_730146831.dbf 100% 1384KB 1.4MB/s 00:00
2_13_730146831.dbf 100% 137KB 137.0KB/s 00:00
2_14_730146831.dbf 100% 3584 3.5KB/s 00:00
2_15_730146831.dbf 100% 2048 2 .0KB/s 00:00
2_16_730146831.dbf 100% 1024 1.0KB/s 00:00
2_17_730146831.dbf 100% 4085KB 4.0MB/s 00:00
2_18_730146831.dbf 100% 167KB 166.5KB/s 00:00
2_19_730146831.dbf 100% 2909KB 2.8MB/s 00:01
2_20_730146831.dbf 100% 1024 1.0KB/s 00:00
2_7_730146831.dbf 100% 19MB 4.8MB/s 00:04
2_8_730146831.dbf 100% 78KB 77.5KB/s 00:00
2_9_730146831.dbf 100% 96MB 5.6MB/s 00:17
SQL> recover database until cancel;
ORA-00279: change 377557 generated at 10/09/2010 07:17:35 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_2_731878740.dbf
ORA-00280: change 377557 for thread 2 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_2_731878740.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dev/raw/raw6'
SQL> recover database until cancel;
ORA-00279: change 377557 generated at 10/09/2010 07:17:35 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_2_731878740.dbf
ORA-00280: change 377557 for thread 2 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 377557 generated at 10/09/2010 10:00:23 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_6_731878740.dbf
ORA-00280: change 377557 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 378276 generated at 10/09/2010 10:14:25 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_7_731878740.dbf
ORA-00280: change 378276 for thread 1 is in sequence #7
ORA-00278: log file '/data/arc2/1_6_731878740.dbf' no longer needed for this
Recovery
……… (중간 생략) ………
ORA-00308: cannot open archived log '/data/arc2/2_4_731878740.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
7.2 백업파일이 cold backup 이 아닌 open backup 일 경우 복구하기
* 이번 경우는 백업 파일을 cold backup 이 아닌 open backup 으로 복구를 하는 경우를 다룹니다.
Archive log 만 존재하면 cold backup 이나 begin backup 이나 방법은 모두 동일합니다.
Step. 1 현재 상태 확인
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
--------- --------------------- ------------ -------------- ---------------- ---------- -------
1 /dev/raw/raw13 49 4 CURRENT 1 NO
1 /dev/raw/raw14 49 4 CURRENT 1 NO
2 /dev/raw/raw15 49 2 INACTIVE 1 YES
2 /dev/raw/raw16 49 2 INACTIVE 1 YES
3 /dev/raw/raw17 49 3 ACTIVE 1 YES
3 /dev/raw/raw18 49 3 ACTIVE 1 YES
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 CURRENT 2 NO
5 /dev/raw/raw22 49 5 CURRENT 2 NO
6 /dev/raw/raw23 49 3 INACTIVE 2 YES
6 /dev/raw/raw24 49 3 INACTIVE 2 YES
12 rows selected.
SQL> @dd
TABLESPACE MB FILE_NAME
------------------- ---------- -------------------
SYSTEM 599 /dev/raw/raw6
UNDOTBS1 99 /dev/raw/raw8
SYSAUX 299 /dev/raw/raw7
UNDOTBS2 99 /dev/raw/raw9
USERS 49 /dev/raw/raw10
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac1
step 2. 데이터 파일을 begin backup 수행
SQL> !vi begin.sql
conn / as sysdba;
alter tablespace system begin backup;
!dd if=/dev/raw/raw6 of=/data/backup/open/raw6_system bs=8k
alter tablespace system end backup;
alter tablespace sysaux begin backup;
!dd if=/dev/raw/raw7 of=/data/backup/open/raw7_sysaux bs=8k
alter tablespace sysaux end backup;
alter tablespace undotbs1 begin backup;
!dd if=/dev/raw/raw8 of=/data/backup/open/raw8_undotbs1 bs=8k
alter tablespace undotbs1 end backup;
alter tablespace undotbs2 begin backup;
!dd if=/dev/raw/raw9 of=/data/backup/open/raw9_undotbs2 bs=8k
alter tablespace undotbs2 end backup;
alter tablespace users begin backup;
!dd if=/dev/raw/raw10 of=/data/backup/open/raw10_users bs=8k
alter tablespace users end backup;
:wq!
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Step 3. 테스트 데이터 생성
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
---------- --------------------- ---------- ---------- ---------------- ------------- --------
1 /dev/raw/raw13 49 4 CURRENT 1 NO
1 /dev/raw/raw14 49 4 CURRENT 1 NO
2 /dev/raw/raw15 49 2 INACTIVE 1 YES
2 /dev/raw/raw16 49 2 INACTIVE 1 YES
3 /dev/raw/raw17 49 3 INACTIVE 1 YES
3 /dev/raw/raw18 49 3 INACTIVE 1 YES
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 CURRENT 2 NO
5 /dev/raw/raw22 49 5 CURRENT 2 NO
6 /dev/raw/raw23 49 3 INACTIVE 2 YES
6 /dev/raw/raw24 49 3 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt02 values (1);
1 row created.
SQL> commit;
Commit complete. <-1 번 그룹에 들어감
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
------------ -------------------- ---------- ---------- ---------------- -------------- --------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 CURRENT 1 NO
2 /dev/raw/raw16 49 5 CURRENT 1 NO
3 /dev/raw/raw17 49 3 INACTIVE 1 YES
3 /dev/raw/raw18 49 3 INACTIVE 1 YES
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 CURRENT 2 NO
6 /dev/raw/raw24 49 6 CURRENT 2 NO
12 rows selected.
SQL> insert into tt02 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 들어감.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
----------- --------------------- ---------- ---------- ---------------- ---------- ---------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 CURRENT 2 NO
6 /dev/raw/raw24 49 6 CURRENT 2 NO
12 rows selected.
SQL> insert into tt02 values (3);
1 row created.
SQL> commit; <- 3번 그룹에 들어감
Commit complete.
Step 4. Rac1 노드의 current redo log 삭제 후 비정상 종료시킴.
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@rac1 ~]$ crs_stat –t
Name Type Target State Host
-------------- ---------------- ------------ --------- ---------
ora.rac.db application OFFLINE OFFLINE <-비정상 종료됨
ora....c1.inst application OFFLINE OFFLINE <-비정상 종료됨
ora....c2.inst application ONLINE OFFLINE <-비정상 종료됨
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
Step 5. 필요한 파일 복원 후 복구 함 – 백업파일은 begin backup file 사용함
SQL> !
[oracle@rac1 ~]$ cd /data/backup/open
[oracle@rac1 open]$ ls
raw10_users raw6_system raw7_sysaux raw8_undotbs1 raw9_undotbs2
[oracle@rac1 open]$ dd if=/data/backup/open/raw10_users of=/dev/raw/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw6_system of=/dev/raw/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw7_sysaux of=/dev/raw/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ dd if=/data/backup/open/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 open]$ scp rac2:/data/arc1/* /data/arc1/
2_1_736641808.dbf 100% 1536 1.5KB/s 00:00
2_1_736646773.dbf 100% 1639KB 1.6MB/s 00:01
2_1_736755624.dbf 100% 40KB 40.0KB/s 00:00
2_2_736611941.dbf 100% 1593KB 1.6MB/s 00:00
2_2_736641808.dbf 100% 5834KB 2.9MB/s 00:02
2_2_736646773.dbf 100% 1053KB 1.0MB/s 00:00
2_2_736755624.dbf 100% 1024 1.0KB/s 00:00
2_3_736641808.dbf 100% 1024 1.0KB/s 00:00
2_3_736646773.dbf 100% 8453KB 2.8MB/s 00:03
2_3_736755624.dbf 100% 3072 3.0KB/s 00:00
2_4_736641808.dbf 100% 2048 2.0KB/s 00:00
2_4_736646773.dbf 100% 719KB 718.5KB/s 00:00
2_4_736755624.dbf 100% 1024 1.0KB/s 00:00
2_5_736641808.dbf 100% 1024 1.0KB/s 00:00
2_5_736755624.dbf 100% 3072 3.0KB/s 00:00
2_6_736641808.dbf 100% 1024 1.0KB/s 00:00
SQL> recover database until cancel;
ORA-00279: change 345340 generated at 12/03/2010 06:28:41 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_4_736755624.dbf <-쓰레드1번 ,시퀀스 4번
ORA-00280: change 345340 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw13 <- log file 명을 직접 입력 합니다.
ORA-00279: change 345340 generated at 12/03/2010 06:07:20 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_5_736755624.dbf
ORA-00280: change 345340 for thread 2 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw21
ORA-00279: change 345527 generated at 12/03/2010 06:33:26 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_5_736755624.dbf
ORA-00280: change 345527 for thread 1 is in sequence #5
ORA-00278: log file '/dev/raw/raw13' no longer needed for this recovery
( 중간 생략 )
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw23
ORA-00279: change 345549 generated at 12/03/2010 06:34:10 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_6_736755624.dbf
ORA-00280: change 345549 for thread 1 is in sequence #6
ORA-00278: log file '/dev/raw/raw15' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw17
ORA-00308: cannot open archived log '/dev/raw/raw17'
ORA-27047: unable to read the header block of file
Additional information: 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt02;
NO
-------------
1
2 <- rac1 Current 에 있던 3만 복구 안된 것을 알 수 있습니다.
Case 8. Current Redo log 장애 – 양쪽 노드 모두 장애 발생
*RAC를 구성하는 전체 노드의 Current Redo log file이 Block Corruption 등의 이유로 동시에 장애가 발생함.
* 전체 노드가 비정상 종료되고 mount 까지만 진행 됨.
* 이 작업을 수행하기 전에 DB를 정상적인 상태로 종료 후 cold backup 받으신 후 작업하세요.
Step 1. 현재 상태 확인
SQL> @log
GROUP# MEMBER MB STATUS THREAD# ARC
----------- ---------------------- ---------- ---------------- ------------ -------
1 /dev/raw/raw13 49 INACTIVE 1 YES
1 /dev/raw/raw14 49 INACTIVE 1 YES
2 /dev/raw/raw15 49 CURRENT 1 NO
2 /dev/raw/raw16 49 CURRENT 1 NO
3 /dev/raw/raw17 49 INACTIVE 1 YES
3 /dev/raw/raw18 49 INACTIVE 1 YES
4 /dev/raw/raw19 49 CURRENT 2 NO
4 /dev/raw/raw20 49 CURRENT 2 NO
5 /dev/raw/raw21 49 INACTIVE 2 YES
5 /dev/raw/raw22 49 INACTIVE 2 YES
6 /dev/raw/raw23 49 INACTIVE 2 YES
6 /dev/raw/raw24 49 INACTIVE 2 YES
12 rows selected.
SQL> @dd
TABLESPACE MB FILE_NAME
------------------- ---------- --------------------
SYSTEM 599 /dev/raw/raw6
UNDOTBS1 99 /dev/raw/raw8
SYSAUX 299 /dev/raw/raw7
UNDOTBS2 99 /dev/raw/raw9
USERS 49 /dev/raw/raw10
Step 2. 테스트용 데이터 생성하기
SQL> create table tt03(no number) tablespace users;
Table created.
SQL> insert into tt03 values (1);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB STATUS THREAD# ARC
------------- --------------------- ---------- ---------------- ---------------- ---------
1 /dev/raw/raw13 49 INACTIVE 1 YES
1 /dev/raw/raw14 49 INACTIVE 1 YES
2 /dev/raw/raw15 49 ACTIVE 1 NO
2 /dev/raw/raw16 49 ACTIVE 1 NO
3 /dev/raw/raw17 49 CURRENT 1 NO
3 /dev/raw/raw18 49 CURRENT 1 NO
4 /dev/raw/raw19 49 CURRENT 2 NO
4 /dev/raw/raw20 49 CURRENT 2 NO
5 /dev/raw/raw21 49 INACTIVE 2 YES
5 /dev/raw/raw22 49 INACTIVE 2 YES
6 /dev/raw/raw23 49 INACTIVE 2 YES
6 /dev/raw/raw24 49 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt03 values (2);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB STATUS THREAD# ARC
--------- --------------------- ---------- ---------------- -------------- -----
1 /dev/raw/raw13 49 CURRENT 1 NO
1 /dev/raw/raw14 49 CURRENT 1 NO
2 /dev/raw/raw15 49 ACTIVE 1 YES
2 /dev/raw/raw16 49 ACTIVE 1 YES
3 /dev/raw/raw17 49 ACTIVE 1 YES
3 /dev/raw/raw18 49 ACTIVE 1 YES
4 /dev/raw/raw19 49 ACTIVE 2 YES
4 /dev/raw/raw20 49 ACTIVE 2 YES
5 /dev/raw/raw21 49 CURRENT 2 NO
5 /dev/raw/raw22 49 CURRENT 2 NO
6 /dev/raw/raw23 49 INACTIVE 2 YES
6 /dev/raw/raw24 49 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt03 values (3);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장됨.
Step 3. 양쪽 노드의 Current Redo log 를 삭제 후 비정상종료 시킴.
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw21 bs=8k
dd: writing `/dev/raw/raw21': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw22 bs=8k
dd: writing to `/dev/raw/raw22': 장치에 남은 공간이 없음
106497+0개의 레코드를 입력하였습니다
106496+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@rac1 ~]$ crs_stat –t
Name Type Target State Host
-------------- --------------- ------------- ---------- --------
ora.rac.db application OFFLINE OFFLINE <- 비정상종료됨
ora....c1.inst application OFFLINE OFFLINE <- 비정상종료됨
ora....c2.inst application ONLINE OFFLINE <- 비정상종료됨
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw14'
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw13'
Step 4. 백업 파일 복원 후 복구 (백업파일은 cold backup , hot backup 상관없음)
SQL> !
[oracle@rac1 ~]$ ls /data/backup/close
raw10_users raw7_sysaux raw9_undotbs2 raw6_system raw8_undotbs1 undotbs2_raw9
[oracle@rac1 ~]$ dd if=/data/backup/close/raw10_users of=/dev/raw/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/close/raw7_sysaux of=/dev/raw/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/close/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/close/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/close/raw6_system of=/dev/raw/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc1/
1_11_736611941.dbf 100% 6033KB 3.0MB/s 00:02
1_12_736611941.dbf 100% 186KB 186.0KB/s 00:00
2_1_736641808.dbf 100% 1536 1.5KB/s 00:00
2_1_736646773.dbf 100% 1639KB 1.6MB/s 00:00
2_2_736611941.dbf 100% 1593KB 1.6MB/s 00:01
2_2_736641808.dbf 100% 5834KB 5.7MB/s 00:01
2_2_736646773.dbf 100% 1053KB 1.0MB/s 00:00
2_3_736641808.dbf 100% 1024 1.0KB/s 00:00
2_3_736646773.dbf 100% 8453KB 4.1MB/s 00:02
2_4_736641808.dbf 100% 2048 2.0KB/s 00:00
2_4_736646773.dbf 100% 719KB 718.5KB/s 00:0 0
2_5_736641808.dbf 100% 1024 1.0KB/s 00:00
2_6_736641808.dbf 100% 1024 1.0KB/s 00:00
[oracle@rac1 ~]$ exit
exit
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- --------------------- ---------- --------------- ---------------- ---------- ---------
1 /dev/raw/raw13 49 7 CURRENT 1 NO
1 /dev/raw/raw14 49 7 CURRENT 1 NO
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 ACTIVE 1 YES
3 /dev/raw/raw18 49 6 ACTIVE 1 YES
4 /dev/raw/raw19 49 4 ACTIVE 2 YES
4 /dev/raw/raw20 49 4 ACTIVE 2 YES
5 /dev/raw/raw21 49 5 CURRENT 2 NO
5 /dev/raw/raw22 49 5 CURRENT 2 NO
6 /dev/raw/raw23 49 3 INACTIVE 2 YES
6 /dev/raw/raw24 49 3 INACTIVE 2 YES
12 rows selected.
SQL> recover database until cancel;
ORA-00279: change 341191 generated at 12/03/2010 05:38:16 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_4_736646773.dbf
ORA-00280: change 341191 for thread 2 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw19
ORA-00279: change 341191 generated at 12/03/2010 05:44:01 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_5_736646773.dbf
ORA-00280: change 341191 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw15
ORA-00279: change 344401 generated at 12/03/2010 06:06:33 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_6_736646773.dbf
ORA-00280: change 344401 for thread 1 is in sequence #6
ORA-00278: log file '/dev/raw/raw15' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw17
ORA-00279: change 344548 generated at 12/03/2010 06:07:12 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_7_736646773.dbf
ORA-00280: change 344548 for thread 1 is in sequence #7
ORA-00278: log file '/dev/raw/raw17' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw13
ORA-00308: cannot open archived log '/dev/raw/raw13'
ORA-27047: unable to read the header block of file
Additional information: 2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt03;
NO
---------------
1
2 <- Current 에 있던 3번은 복구가 안됨을 알 수 있습니다.
Case 9. Redo log file 전체 손상됨 – 한쪽 노드에서만 장애 발생
* 이번 장애는 한쪽 노드(쓰레드)의 모든 리두 로그가 삭제 된 후 비정상 종료된 경우입니다.
한쪽 노드가 비정상적으로 종료되면 나머지 노드도 장애로 비정상 종료됩니다.
* 이 작업을 하기 전에 전체 데이터파일을 백업 받으신 후 작업하세요
* 전체 작업 순서
1. 전체 데이터 파일 백업 수행
2. 테스트용 데이터 생성
3. rac1 노드에서 리두로그 전체 장애 발생 후 비정상 종료
4. 양쪽 노드 비정상 종료된 후 하나의 노드에서 백업된 데이터파일만 복원
5. 다른 노드에 있는 archive log 복사해 옴
6. recover database until cancel 로 복구
7. alter database open resetlogs 로 오픈 후 데이터 확인.
Step 1. 현재 상태 확인 후 전체 데이터 파일 백업 수행
SQL> !vi begin.sql
conn / as sysdba;
alter tablespace system begin backup;
!dd if=/dev/raw/raw6 of=/data/backup/open/raw6_system bs=8k
alter tablespace system end backup;
alter tablespace sysaux begin backup;
!dd if=/dev/raw/raw7 of=/data/backup/open/raw7_sysaux bs=8k
alter tablespace sysaux end backup;
alter tablespace undotbs1 begin backup;
!dd if=/dev/raw/raw8 of=/data/backup/open/raw8_undotbs1 bs=8k
alter tablespace undotbs1 end backup;
alter tablespace undotbs2 begin backup;
!dd if=/dev/raw/raw9 of=/data/backup/open/raw9_undotbs2 bs=8k
alter tablespace undotbs2 end backup;
alter tablespace users begin backup;
!dd if=/dev/raw/raw10 of=/data/backup/open/raw10_users bs=8k
alter tablespace users end backup;
:wq!
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
SQL> !vi log.sql
set line 200
set pagesize 50
col group# for 999
col member for a15
select a.group#, a.member, b.bytes/1024/1024 MB, b.sequence#,
b.status, b.thread#,b.archived
from v$logfile a, v$log b
where a.group#=b.group#
order by 1,5
:wq!
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ ------------------------ ---------- ---------------- ------------- ------------- ---------1 /dev/raw/raw13 49 10 CURRENT 1 NO
1 /dev/raw/raw14 49 10 CURRENT 1 NO
2 /dev/raw/raw15 49 8 INACTIVE 1 YES
2 /dev/raw/raw16 49 8 INACTIVE 1 YES
3 /dev/raw/raw17 49 9 INACTIVE 1 YES
3 /dev/raw/raw18 49 9 INACTIVE 1 YES
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 CURRENT 2 NO
5 /dev/raw/raw22 49 5 CURRENT 2 NO
6 /dev/raw/raw23 49 3 INACTIVE 2 YES
6 /dev/raw/raw24 49 3 INACTIVE 2 YES
12 rows selected.
Step 2. 테스트용 데이터 생성
SQL> create table tt04 (no number) tablespace users;
Table created.
SQL> insert into tt04 values (1);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ ---------------------- ---------- ---------------- ---------------- --------------- -------
1 /dev/raw/raw13 49 10 ACTIVE 1 YES
1 /dev/raw/raw14 49 10 ACTIVE 1 YES
2 /dev/raw/raw15 49 11 CURRENT 1 NO
2 /dev/raw/raw16 49 11 CURRENT 1 NO
3 /dev/raw/raw17 49 9 INACTIVE 1 YES
3 /dev/raw/raw18 49 9 INACTIVE 1 YES
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 CURRENT 2 NO
6 /dev/raw/raw24 49 6 CURRENT 2 NO
12 rows selected.
SQL> insert into tt04 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
---------- ----------------------- ---------- ----------------- ---------------- ------------- --------
1 /dev/raw/raw13 49 10 ACTIVE 1 YES
1 /dev/raw/raw14 49 10 ACTIVE 1 YES
2 /dev/raw/raw15 49 11 ACTIVE 1 YES
2 /dev/raw/raw16 49 11 ACTIVE 1 YES
3 /dev/raw/raw17 49 12 CURRENT 1 NO
3 /dev/raw/raw18 49 12 CURRENT 1 NO
4 /dev/raw/raw19 49 4 INACTIVE 2 YES
4 /dev/raw/raw20 49 4 INACTIVE 2 YES
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 CURRENT 2 NO
6 /dev/raw/raw24 49 6 CURRENT 2 NO
12 rows selected.
SQL> insert into tt04 values (3);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장됨
Step 3. Rac1 Redo log file 전부 삭제 후 비정상 종료
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd: writing `/dev/raw/raw15': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd: writing `/dev/raw/raw16': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
--------------- --------------- ------------- --------- --------
ora.rac.db application OFFLINE OFFLINE <- 비정상 종료됨
ora....c1.inst application OFFLINE OFFLINE <- 비정상 종료됨
ora....c2.inst application ONLINE OFFLINE <- 비정상 종료됨
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw14'
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw13'
Step 4. 양쪽 노드 비정상 종료된 후 하나의 노드에서 백업된 데이터파일만 복원
SQL> !
[oracle@rac1 ~]$ ls /data/backup/open
raw10_users raw6_system raw7_sysaux raw8_undotbs1 raw9_undotbs2
[oracle@rac1 ~]$ dd if=/data/backup/open/raw10_users of=/dev/raw/raw10
25150+0개의 레코드를 입력하였습니다
25149+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/open/raw10_users of=/dev/raw/raw10 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/open/raw6_system of=/dev/raw/raw6 bs=8k
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/open/raw7_sysaux of=/dev/raw/raw7 bs=8k
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/open/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
[oracle@rac1 ~]$ dd if=/data/backup/open/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Step 5. 다른 노드에 있는 archive log 복사해 옴
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc1/
2_1_736641808.dbf 100% 1536 1.5KB/s 00:00
2_1_736646773.dbf 100% 1639KB 1.6MB/s 00:00
2_1_736755624.dbf 100% 40KB 40.0KB/s 00:00
2_1_736757156.dbf 100% 784KB 784.0KB/s 00:00
2_2_736611941.dbf 100% 1593KB 1.6MB/s 00:01
2_2_736641808.dbf 100% 5834KB 2.9MB/s 00:02
2_2_736646773.dbf 100% 1053KB 1.0MB/s 00:01
2_2_736755624.dbf 100% 1024 1.0KB/s 00:00
2_2_736757156.dbf 100% 1024 1.0KB/s 00:00
2_3_736641808.dbf 100% 1024 1.0KB/s 00:00
2_3_736646773.dbf 100% 8453KB 4.1MB/s 00:02
2_3_736755624.dbf 100% 3072 3.0KB/s 00:0 0
2_3_736757156.dbf 100% 1024 1.0KB/s 00:00
2_4_736641808.dbf 100% 2048 2.0KB/s 00:00
2_4_736646773.dbf 100% 719KB 718.5KB/s 00:00
2_4_736755624.dbf 100% 1024 1.0KB/s 00:00
2_4_736757156.dbf 100% 1024 1.0KB/s 00:00
2_5_736641808.dbf 100% 1024 1.0KB/s 00:00
2_5_736755624.dbf 100% 3072 3.0KB/s 00:00
2_5_736757156.dbf 100% 1024 1.0KB/s 00:00
2_6_736641808.dbf 100% 1024 1.0KB/s 00:00
2_6_736757156.dbf 100% 24KB 23.5KB/s 00 :00
Step 6. recover database until cancel 로 복구
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ --------------------- ---------- ----------------- ---------------- ---------------- --------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 ACTIVE 1 YES
2 /dev/raw/raw16 49 8 ACTIVE 1 YES
3 /dev/raw/raw17 49 9 CURRENT 1 NO
3 /dev/raw/raw18 49 9 CURRENT 1 NO
4 /dev/raw/raw19 49 7 CURRENT 2 NO
4 /dev/raw/raw20 49 7 CURRENT 2 NO
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> recover database until cancel;
ORA-00279: change 370081 generated at 12/03/2010 08:39:12 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_7_736763741.dbf
ORA-00280: change 370081 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터 입력
ORA-00279: change 370081 generated at 12/03/2010 08:39:12 needed for thread 2
<- 쓰레드 2번에서 만들어졌다고 정보를 줌
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/raw/raw20 <- 쓰레드 2번의 시퀀스 7번 로그파일 입력함
ORA-00279: change 370284 generated at 12/03/2010 08:43:39 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_8_736763741.dbf
ORA-00280: change 370284 for thread 1 is in sequence #8
ORA-00278: log file '/data/arc2/1_7_736763741.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 엔터 입력
ORA-00279: change 370292 generated at 12/03/2010 08:43:50 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_9_736763741.dbf
ORA-00280: change 370292 for thread 1 is in sequence #9
ORA-00278: log file '/data/arc2/1_8_736763741.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/1_9_736763741.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3 <- rac1 의 current 여서 로그가 없음.
Step 7. alter database open resetlogs 로 오픈 후 데이터 확인.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt04;
NO
--------------
1
2 <- current 에 있던 3번은 복구가 안되고 나머지는 복구 완료됨
Case 10. Redo log file 전체 손상됨 – 양쪽 노드 모두에서 장애 발생함
* 이번 경우는 양쪽 노드의 모든 리두 로그가 삭제되면서 데이터베이스가 비정상적으로 종료된
경우에 어떻게 복구하는지를 살펴봅니다.
* 이 작업을 하기 전에 전체 데이터파일을 백업 받으신 후 작업하세요
* 전체 작업 순서
1. 전체 데이터 파일 백업 수행
2. 테스트용 데이터 생성
3. rac1 , rac2 노드에서 리두 로그 전체 장애 발생 후 비정상 종료
4. 양쪽 노드 비정상 종료된 후 하나의 노드에서 백업된 데이터파일만 복원
5. 다른 노드에 있는 archive log 복사해 옴
6. recover database until cancel 로 복구
7. alter database open resetlogs 로 오픈 후 데이터 확인.
10-1. Begin backup file 로 복구하기
Step. 1. 전체 데이터 파일 백업 수행
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Step2. 테스트용 데이터 생성
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------- ---------------------- -------- ----------------- ------------- -------------- -------
1 /dev/raw/raw13 49 4 CURRENT 1 NO
1 /dev/raw/raw14 49 4 CURRENT 1 NO
2 /dev/raw/raw15 49 2 INACTIVE 1 YES
2 /dev/raw/raw16 49 2 INACTIVE 1 YES
3 /dev/raw/raw17 49 3 INACTIVE 1 YES
3 /dev/raw/raw18 49 3 INACTIVE 1 YES
4 /dev/raw/raw19 49 1 INACTIVE 2 YES
4 /dev/raw/raw20 49 1 INACTIVE 2 YES
5 /dev/raw/raw21 49 2 CURRENT 2 NO
5 /dev/raw/raw22 49 2 CURRENT 2 NO
6 /dev/raw/raw23 49 0 UNUSED 2 YES
6 /dev/raw/raw24 49 0 UNUSED 2 YES
12 rows selected.
SQL> create table tt50 (no number) tablespace users;
Table created.
SQL> insert into tt50 values (1);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장되었습니다.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
----------- ---------------------- ------- ------------ -------------- --------------- -------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 CURRENT 1 NO
2 /dev/raw/raw16 49 5 CURRENT 1 NO
3 /dev/raw/raw17 49 3 INACTIVE 1 YES
3 /dev/raw/raw18 49 3 INACTIVE 1 YES
4 /dev/raw/raw19 49 1 INACTIVE 2 YES
4 /dev/raw/raw20 49 1 INACTIVE 2 YES
5 /dev/raw/raw21 49 2 CURRENT 2 NO
5 /dev/raw/raw22 49 2 CURRENT 2 NO
6 /dev/raw/raw23 49 0 UNUSED 2 YES
6 /dev/raw/raw24 49 0 UNUSED 2 YES
12 rows selected.
SQL> insert into tt50 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장되었습니다.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
----------- ---------------------- ------- ---------- -------------- -------------- --------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 1 INACTIVE 2 YES
4 /dev/raw/raw20 49 1 INACTIVE 2 YES
5 /dev/raw/raw21 49 2 INACTIVE 2 YES
5 /dev/raw/raw22 49 2 INACTIVE 2 YES
6 /dev/raw/raw23 49 3 CURRENT 2 NO
6 /dev/raw/raw24 49 3 CURRENT 2 NO
12 rows selected.
SQL> insert into tt50 values (3);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장되었습니다.
Step 3. rac1 , rac2 노드에서 리두 로그 전체 장애 발생 후 비정상 종료
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- -------------------- ------- ----------------- ------------- --------------- -------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 1 INACTIVE 2 YES
4 /dev/raw/raw20 49 1 INACTIVE 2 YES
5 /dev/raw/raw21 49 2 INACTIVE 2 YES
5 /dev/raw/raw22 49 2 INACTIVE 2 YES
6 /dev/raw/raw23 49 3 CURRENT 2 NO
6 /dev/raw/raw24 49 3 CURRENT 2 NO
12 rows selected.
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd: writing `/dev/raw/raw15': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd: writing `/dev/raw/raw16': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw19 bs=8k
dd: writing `/dev/raw/raw19': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw20 bs=8k
dd: writing `/dev/raw/raw20': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw21 bs=8k
dd: writing `/dev/raw/raw21': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw22 bs=8k
dd: writing `/dev/raw/raw22': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw23 bs=8k
dd: writing `/dev/raw/raw23': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd: writing `/dev/raw/raw24': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
Step 4. 양쪽 노드 비정상 종료된 후 하나의 노드에서 백업된 데이터파일만 복원
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw18'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
SQL> !vi res.sql
!dd if=/data/backup/open/raw10_users of=/dev/raw/raw10 bs=8k
!dd if=/data/backup/open/raw6_system of=/dev/raw/raw6 bs=8k
!dd if=/data/backup/open/raw7_sysaux of=/dev/raw/raw7 bs=8k
!dd if=/data/backup/open/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
!dd if=/data/backup/open/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
:wq!
SQL> @res
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Step 5. 다른 노드에 있는 archive log 복사해 옴
SQL> !
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc1/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_2_737001106.dbf 100% 51KB 50.5KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0K B/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:00
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:00
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc2/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_2_737001106.dbf 100% 51KB 50.5KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0KB/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:00
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:00
Step 6. recover database until cancel 로 복구
[oracle@rac1 ~]$ exit
exit
SQL> recover database until cancel;
ORA-00279: change 461742 generated at 12/06/2010 02:38:32 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_4_737001106.dbf
ORA-00280: change 461742 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 461742 generated at 12/06/2010 02:34:59 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_2_737001106.dbf
ORA-00280: change 461742 for thread 2 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 461919 generated at 12/06/2010 02:42:36 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_5_737001106.dbf
ORA-00280: change 461919 for thread 1 is in sequence #5
ORA-00278: log file '/data/arc2/1_4_737001106.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 461922 generated at 12/06/2010 04:08:12 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_3_737001106.dbf
ORA-00280: change 461922 for thread 2 is in sequence #3
ORA-00278: log file '/data/arc2/2_2_737001106.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_3_737001106.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt50;
NO
--------------
1
2 <- 아카이브 파일 유무에 따라 결과가 달라집니다
Case 10. Redo log file 전체 손상됨 – 양쪽 노드 모두에서 장애 발생함
10-2. cold backup file 로 복구하기
**작업 순서
Step 1. 모든 노드에서 DB 정상종료(shutdown immediate) 후 cold backup 수행
Step 2. DB Open 후 Test 용 데이터 생성
Step 3. 장애 발생
Step 4. Cold backup file 복원
Step 5. Archive file 복사
Step 6. 복구 후 resetlogs 로 open
Step 1. 모든 노드에서 DB 정상종료(shutdown immediate) 후 cold backup 수행
[oracle@rac1 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 5 20:26:44 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@rac1 ~]$ ssh rac2
Last login: Fri Dec 3 19:07:58 2010 from 192.168.79.1
[oracle@rac2 ~]$ sqlplus / as sysdba;
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 6 01:09:06 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>!
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
-------------- ---------------- ----------- --------- ---------
ora.rac.db application OFFLINE OFFLINE <- 종료됨을 확인
ora....c1.inst application OFFLINE OFFLINE <- 종료됨을 확인
ora....c2.inst application OFFLINE OFFLINE <- 종료됨을 확인
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
[oracle@rac1 ~]$ vi close.sql
!dd if=/dev/raw/raw6 of=/data/backup/close/raw6_system bs=8k
!dd if=/dev/raw/raw8 of=/data/backup/close/raw8_undotbs1 bs=8k
!dd if=/dev/raw/raw7 of=/data/backup/close/raw7_sysaux bs=8k
!dd if=/dev/raw/raw9 of=/data/backup/close/raw9_undotbs2 bs=8k
!dd if=/dev/raw/raw10 of=/data/backup/close/raw10_users bs=8k
:wq!
SQL> @close
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Step 2. DB Open 후 Test 용 데이터 생성
SQL> @log
GROUP# MEMBER MB SEQ# STATUS THREAD# ARC
----------- ---------------------- -------- ---------- ---------------- ------------ -------------------
1 /dev/raw/raw13 49 13 CURRENT 1 NO
1 /dev/raw/raw14 49 13 CURRENT 1 NO
2 /dev/raw/raw15 49 11 ACTIVE 1 YES
2 /dev/raw/raw16 49 11 ACTIVE 1 YES
3 /dev/raw/raw17 49 12 ACTIVE 1 YES
3 /dev/raw/raw18 49 12 ACTIVE 1 YES
4 /dev/raw/raw19 49 10 CURRENT 2 NO
4 /dev/raw/raw20 49 10 CURRENT 2 NO
5 /dev/raw/raw21 49 8 INACTIVE 2 YES
5 /dev/raw/raw22 49 8 INACTIVE 2 YES
6 /dev/raw/raw23 49 9 ACTIVE 2 YES
6 /dev/raw/raw24 49 9 ACTIVE 2 YES
12 rows selected.
SQL> create table tt60 (no number) tablespace users;
Table created.
SQL> insert into tt60 values (1);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장됨
SQL> alter system switch logfile ;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- ------------------- ------- --------------- --------------- ----------------- ---------
1 /dev/raw/raw13 49 13 ACTIVE 1 YES
1 /dev/raw/raw14 49 13 ACTIVE 1 YES
2 /dev/raw/raw15 49 14 CURRENT 1 NO
2 /dev/raw/raw16 49 14 CURRENT 1 NO
3 /dev/raw/raw17 49 12 ACTIVE 1 YES
3 /dev/raw/raw18 49 12 ACTIVE 1 YES
4 /dev/raw/raw19 49 10 CURRENT 2 NO
4 /dev/raw/raw20 49 10 CURRENT 2 NO
5 /dev/raw/raw21 49 8 INACTIVE 2 YES
5 /dev/raw/raw22 49 8 INACTIVE 2 YES
6 /dev/raw/raw23 49 9 ACTIVE 2 YES
6 /dev/raw/raw24 49 9 ACTIVE 2 YES
12 rows selected.
SQL> insert into tt60 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- ----------------------- -------- ---------------- ------------- --------------- --------
1 /dev/raw/raw13 49 13 ACTIVE 1 YES
1 /dev/raw/raw14 49 13 ACTIVE 1 YES
2 /dev/raw/raw15 49 14 ACTIVE 1 YES
2 /dev/raw/raw16 49 14 ACTIVE 1 YES
3 /dev/raw/raw17 49 15 CURRENT 1 NO
3 /dev/raw/raw18 49 15 CURRENT 1 NO
4 /dev/raw/raw19 49 10 CURRENT 2 NO
4 /dev/raw/raw20 49 10 CURRENT 2 NO
5 /dev/raw/raw21 49 8 INACTIVE 2 YES
5 /dev/raw/raw22 49 8 INACTIVE 2 YES
6 /dev/raw/raw23 49 9 ACTIVE 2 YES
6 /dev/raw/raw24 49 9 ACTIVE 2 YES
12 rows selected.
SQL> insert into tt60 values (3);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장됨
SQL> select * from tt60;
NO
---------------
1 <- 1번 그룹에 저장됨
2 <- 2번 그룹에 저장됨
3 <- 3번 그룹에 저장됨
Step 3. 장애 발생
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
---------- -------------------- ----- ----------------- ------------ --------------- ---------
1 /dev/raw/raw13 49 13 ACTIVE 1 YES
1 /dev/raw/raw14 49 13 ACTIVE 1 YES
2 /dev/raw/raw15 49 14 ACTIVE 1 YES
2 /dev/raw/raw16 49 14 ACTIVE 1 YES
3 /dev/raw/raw17 49 15 CURRENT 1 NO
3 /dev/raw/raw18 49 15 CURRENT 1 NO
4 /dev/raw/raw19 49 10 CURRENT 2 NO
4 /dev/raw/raw20 49 10 CURRENT 2 NO
5 /dev/raw/raw21 49 8 INACTIVE 2 YES
5 /dev/raw/raw22 49 8 INACTIVE 2 YES
6 /dev/raw/raw23 49 9 ACTIVE 2 YES
6 /dev/raw/raw24 49 9 ACTIVE 2 YES
12 rows selected.
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd: writing `/dev/raw/raw15': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd: writing `/dev/raw/raw16': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw19 bs=8k
dd: writing `/dev/raw/raw19': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw20 bs=8k
dd: writing `/dev/raw/raw20': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw21 bs=8k
dd: writing `/dev/raw/raw21': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw22 bs=8k
dd: writing `/dev/raw/raw22': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw23 bs=8k
dd: writing `/dev/raw/raw23': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd: writing `/dev/raw/raw24': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
---------------------------------------------------------------------
ora.rac.db application OFFLINE OFFLINE <- 강제종료됨
ora....c1.inst application OFFLINE OFFLINE <- 강제종료됨
ora....c2.inst application ONLINE OFFLINE <- 강제종료됨
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw18'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
Step 4. Cold backup file 복원
SQL> !vi res.sql
!dd if=/data/backup/close/raw10_users of=/dev/raw/raw10 bs=8k
!dd if=/data/backup/close/raw6_system of=/dev/raw/raw6 bs=8k
!dd if=/data/backup/close/raw7_sysaux of=/dev/raw/raw7 bs=8k
!dd if=/data/backup/close/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
!dd if=/data/backup/close/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
:wq!
SQL> @res
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Step 5. Archive redo log file 복사
SQL> !
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc1/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0KB/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:01
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:00
[oracle@rac1 ~]$ scp rac2:/data/arc1/* /data/arc2/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0KB/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:00
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:00
Step 6. 복구 후 resetlogs 로 open
SQL> recover database until cancel;
ORA-00279: change 460880 generated at 12/06/2010 02:14:11 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_4_736999898.dbf
ORA-00280: change 460880 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 460880 generated at 12/06/2010 03:48:04 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_4_736999898.dbf
ORA-00280: change 460880 for thread 2 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 461195 generated at 12/06/2010 02:20:44 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_5_736999898.dbf
ORA-00280: change 461195 for thread 1 is in sequence #5
ORA-00278: log file '/data/arc2/1_4_736999898.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 461198 generated at 12/06/2010 03:53:12 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_5_736999898.dbf
ORA-00280: change 461198 for thread 2 is in sequence #5
ORA-00278: log file '/data/arc2/2_4_736999898.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_5_736999898.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt60;
NO
-------------
1
2 <- 아카이브 파일이 어디까지 있냐에 따라서 복구내용 달라짐
Case 10. Redo log file 전체 손상됨 – 양쪽 노드 모두에서 장애 발생함
10-3. 장애 발생 후 shutdown immediate 로 정상 종료 됨
**작업 순서
Step 1. 모든 노드에서 DB 정상종료(shutdown immediate) 후 cold backup 수행
Step 2. DB Open 후 Test 용 데이터 생성
Step 3. 장애 발생 후 shutdown immediate 로 정상 종료됨.
Step 4. Cold backup file 복원
Step 5. Archive file 복사
Step 6. 복구 후 resetlogs 로 open
Step 1. Begin backup 수행
SQL> !vi begin.sql
conn / as sysdba;
alter tablespace system begin backup;
!dd if=/dev/raw/raw6 of=/data/backup/open/raw6_system bs=8k
alter tablespace system end backup;
alter tablespace sysaux begin backup;
!dd if=/dev/raw/raw7 of=/data/backup/open/raw7_sysaux bs=8k
alter tablespace sysaux end backup;
alter tablespace undotbs1 begin backup;
!dd if=/dev/raw/raw8 of=/data/backup/open/raw8_undotbs1 bs=8k
alter tablespace undotbs1 end backup;
alter tablespace undotbs2 begin backup;
!dd if=/dev/raw/raw9 of=/data/backup/open/raw9_undotbs2 bs=8k
alter tablespace undotbs2 end backup;
alter tablespace users begin backup;
!dd if=/dev/raw/raw10 of=/data/backup/open/raw10_users bs=8k
alter tablespace users end backup;
:wq!
SQL> @begin
Connected.
Tablespace altered.
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Tablespace altered.
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Tablespace altered.
Step 2. DB Open 후 Test 용 데이터 생성
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
---------- ---------------------- ----- ---------------- ------------- -------------- -------
1 /dev/raw/raw13 49 7 CURRENT 1 NO
1 /dev/raw/raw14 49 7 CURRENT 1 NO
2 /dev/raw/raw15 49 5 INACTIVE 1 YES
2 /dev/raw/raw16 49 5 INACTIVE 1 YES
3 /dev/raw/raw17 49 6 INACTIVE 1 YES
3 /dev/raw/raw18 49 6 INACTIVE 1 YES
4 /dev/raw/raw19 49 7 CURRENT 2 NO
4 /dev/raw/raw20 49 7 CURRENT 2 NO
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> create table tt70 (no number) tablespace users;
Table created.
SQL> insert into tt70 values (1);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ --------------------- --------- ---------------- --------------- ------------ -------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 CURRENT 1 NO
2 /dev/raw/raw16 49 8 CURRENT 1 NO
3 /dev/raw/raw17 49 6 INACTIVE 1 YES
3 /dev/raw/raw18 49 6 INACTIVE 1 YES
4 /dev/raw/raw19 49 7 CURRENT 2 NO
4 /dev/raw/raw20 49 7 CURRENT 2 NO
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt70 values (1);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- -------------------- -------- ----------------- ------------- -------------- --------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 ACTIVE 1 YES
2 /dev/raw/raw16 49 8 ACTIVE 1 YES
3 /dev/raw/raw17 49 9 CURRENT 1 NO
3 /dev/raw/raw18 49 9 CURRENT 1 NO
4 /dev/raw/raw19 49 7 INACTIVE 2 YES
4 /dev/raw/raw20 49 7 INACTIVE 2 YES
5 /dev/raw/raw21 49 8 CURRENT 2 NO
5 /dev/raw/raw22 49 8 CURRENT 2 NO
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt70 values (3);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장됨.
Step 3. 장애 발생 후 shutdown immediate 로 정상 종료 시도함.
SQL> !dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd: writing `/dev/raw/raw13': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd: writing `/dev/raw/raw14': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd: writing `/dev/raw/raw15': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd: writing `/dev/raw/raw16': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw19 bs=8k
dd: writing `/dev/raw/raw19': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw20 bs=8k
dd: writing `/dev/raw/raw20': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw21 bs=8k
dd: writing `/dev/raw/raw21': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw22 bs=8k
dd: writing `/dev/raw/raw22': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw23 bs=8k
dd: writing `/dev/raw/raw23': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd: writing `/dev/raw/raw24': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown immediate;
ORA-03113: end-of-file on communication channel <- 강제 종료됨
Step 4. backup file 복원 (cold / hot backup 상관없음)
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> !vi res.sql
!dd if=/data/backup/open/raw10_users of=/dev/raw/raw10 bs=8k
!dd if=/data/backup/open/raw6_system of=/dev/raw/raw6 bs=8k
!dd if=/data/backup/open/raw7_sysaux of=/dev/raw/raw7 bs=8k
!dd if=/data/backup/open/raw8_undotbs1 of=/dev/raw/raw8 bs=8k
!dd if=/data/backup/open/raw9_undotbs2 of=/dev/raw/raw9 bs=8k
:wq!
SQL> @res
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
76800+0개의 레코드를 입력하였습니다
76800+0개의 레코드를 출력하였습니다
38400+0개의 레코드를 입력하였습니다
38400+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
12800+0개의 레코드를 입력하였습니다
12800+0개의 레코드를 출력하였습니다
Step 5. Archive log file 복사
SQL> !scp rac2:/data/arc1/* /data/arc1/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_1_737002443.dbf 100% 48KB 47.5KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_2_737001106.dbf 100% 51KB 50.5KB/s 00:00
2_2_737002443.dbf 100% 1024 1.0KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0KB/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_3_737002443.dbf 100% 2560 2.5KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_4_737002443.dbf 100% 1024 1.0KB/s 00:0 0
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_5_737002443.dbf 100% 3072 3.0KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_6_737002443.dbf 100% 1536 1.5KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_737002443.dbf 100% 1536 1.5KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:00
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:00
SQL> !scp rac2:/data/arc1/* /data/arc2/
2_1_736999898.dbf 100% 51KB 51.0KB/s 00:00
2_1_737002443.dbf 100% 48KB 47.5KB/s 00:00
2_2_736998010.dbf 100% 55KB 55.0KB/s 00:00
2_2_736999898.dbf 100% 1024 1.0KB/s 00:00
2_2_737001106.dbf 100% 51KB 50.5KB/s 00:00
2_2_737002443.dbf 100% 1024 1.0KB/s 00:00
2_3_736998010.dbf 100% 1024 1.0KB/s 00:00
2_3_736999898.dbf 100% 3072 3.0KB/s 00:00
2_3_737002443.dbf 100% 2560 2.5KB/s 00:00
2_4_736767418.dbf 100% 2560 2.5KB/s 00:00
2_4_736999898.dbf 100% 128KB 127.5KB/s 00:00
2_4_737002443.dbf 100% 1024 1.0KB/s 00:00
2_5_736767418.dbf 100% 1536 1.5KB/s 00:00
2_5_737002443.dbf 100% 3072 3.0KB/s 00:00
2_6_736767418.dbf 100% 1024 1.0KB/s 00:00
2_6_737002443.dbf 100% 1536 1.5KB/s 00:00
2_7_736767418.dbf 100% 1024 1.0KB/s 00:00
2_7_737002443.dbf 100% 1536 1.5KB/s 00:00
2_8_736767418.dbf 100% 1653KB 1.6MB/s 00:00
2_9_736767418.dbf 100% 73KB 72.5KB/s 00:01
Step 6. 복구 후 resetlogs 로 open
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dev/raw/raw6'
SQL> recover database until cancel;
ORA-00279: change 462477 generated at 12/06/2010 03:00:37 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_7_737002443.dbf
ORA-00280: change 462477 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 462477 generated at 12/06/2010 04:21:55 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_7_737002443.dbf
ORA-00280: change 462477 for thread 2 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 462654 generated at 12/06/2010 03:05:06 needed for thread 1
ORA-00289: suggestion : /data/arc2/1_8_737002443.dbf
ORA-00280: change 462654 for thread 1 is in sequence #8
ORA-00278: log file '/data/arc2/1_7_737002443.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 462657 generated at 12/06/2010 04:25:22 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_8_737002443.dbf
ORA-00280: change 462657 for thread 2 is in sequence #8
ORA-00278: log file '/data/arc2/2_7_737002443.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_8_737002443.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt70;
NO
---------------
1 <- 아카이브 상태에 따라 복구 내용이 달라질 수 있습니다.
** 모든 리두 로그가 삭제된 후 shutdown immediate 를 시도했으나 강제 종료되었습니다.
그래서 나머지 복구 방법은 앞에서 살펴보았던 10-1과 10-2 의 방법과 동일한 것입니다.
Case 11. Redo log file 삭제 장애 후 백업파일 없는 경우 복구
* 앞에서 복구 했던 방법은 모두 백업 데이터 파일이 있어서 해당 파일들을 복원 한 후 복구를 하는
경우였습니다. 그러나 이번 경우는 장애가 난 후 백업 파일이 없다는 가정으로 복구해보겠습니다.
방법은 히든 파라미터를 이용하는 것입니다.
11-1 . Redo log file 이 Active 상태일 때 장애
*작업 순서 요약
Step 1. 테스트 테이블 생성 후 데이터 입력
Step 2. Current Redo log file 삭제 후 강제 종료
Step 3. 백업 파일 복원 없이 복구 하고 오픈 시도-실패함
Step 4. 히든 파일 적용 후 Resetlogs 옵션으로 open 시킴
Step 1. 테스트 테이블 생성 후 데이터 입력
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ -------------------- -------- ---------------- ------------ ------------- -------
1 /dev/raw/raw13 49 7 CURRENT 1 NO
1 /dev/raw/raw14 49 7 CURRENT 1 NO
2 /dev/raw/raw15 49 5 INACTIVE 1 YES
2 /dev/raw/raw16 49 5 INACTIVE 1 YES
3 /dev/raw/raw17 49 6 INACTIVE 1 YES
3 /dev/raw/raw18 49 6 INACTIVE 1 YES
4 /dev/raw/raw19 49 7 CURRENT 2 NO
4 /dev/raw/raw20 49 7 CURRENT 2 NO
5 /dev/raw/raw21 49 5 INACTIVE 2 YES
5 /dev/raw/raw22 49 5 INACTIVE 2 YES
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> create table tt80 (no number) tablespace users;
Table created.
SQL> insert into tt80 values (1);
1 row created.
SQL> commit;
Commit complete. <- 1번 그룹에 저장됨
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- -------------------- ------- ----------------- -------------- --------------- -------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 CURRENT 1 NO
2 /dev/raw/raw16 49 8 CURRENT 1 NO
3 /dev/raw/raw17 49 6 INACTIVE 1 YES
3 /dev/raw/raw18 49 6 INACTIVE 1 YES
4 /dev/raw/raw19 49 7 ACTIVE 2 YES
4 /dev/raw/raw20 49 7 ACTIVE 2 YES
5 /dev/raw/raw21 49 8 CURRENT 2 NO
5 /dev/raw/raw22 49 8 CURRENT 2 NO
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt80 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
---------- ----------------------- ----- ----------------- ------------ -------------- ------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 ACTIVE 1 YES
2 /dev/raw/raw16 49 8 ACTIVE 1 YES
3 /dev/raw/raw17 49 9 CURRENT 1 NO
3 /dev/raw/raw18 49 9 CURRENT 1 NO
4 /dev/raw/raw19 49 7 ACTIVE 2 YES
4 /dev/raw/raw20 49 7 ACTIVE 2 YES
5 /dev/raw/raw21 49 8 CURRENT 2 NO
5 /dev/raw/raw22 49 8 CURRENT 2 NO
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
SQL> insert into tt80 values (3);
1 row created.
SQL> commit;
Commit complete. <- 3번 그룹에 저장됨
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ --------------------- -------- ----------------- ------------ ------------- ------
1 /dev/raw/raw13 49 7 ACTIVE 1 YES
1 /dev/raw/raw14 49 7 ACTIVE 1 YES
2 /dev/raw/raw15 49 8 ACTIVE 1 YES
2 /dev/raw/raw16 49 8 ACTIVE 1 YES
3 /dev/raw/raw17 49 9 CURRENT 1 NO
3 /dev/raw/raw18 49 9 CURRENT 1 NO
4 /dev/raw/raw19 49 7 ACTIVE 2 YES
4 /dev/raw/raw20 49 7 ACTIVE 2 YES
5 /dev/raw/raw21 49 8 CURRENT 2 NO
5 /dev/raw/raw22 49 8 CURRENT 2 NO
6 /dev/raw/raw23 49 6 INACTIVE 2 YES
6 /dev/raw/raw24 49 6 INACTIVE 2 YES
12 rows selected.
위 @log 결과를 보면 1번과 2번 그룹이 전부 Active 인 것을 알 수 있습니다.
즉 1번 그룹에 create table tt80 한 것과 1을 입력한 것 , 2번 그룹에 tt80 테이블에 2를 입력했던 내용이
데이터 파일에 저장되지 않았다는 것을 의미합니다.
이 상태에서 Current 를 삭제 하고 히든 파라미터를 이용하여 open 시켜보겠습니다.
Step 2. Current Redo log file 삭제 후 강제 종료
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
SQL> !crs_stat –t
Name Type Target State Host
--------------------------------------------------------------------------
ora.rac.db application OFFLINE OFFLINE <- 강제종료됨
ora....c1.inst application OFFLINE OFFLINE <-강제종료됨
ora....c2.inst application ONLINE OFFLINE <-강제종료됨
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
Step 3. 백업 파일 복원 없이 복구 하고 오픈 시도-실패함
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw18'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
SQL> recover database until cancel;
ORA-00279: change 467338 generated at 12/06/2010 05:12:53 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_6_737005926.dbf
ORA-00280: change 467338 for thread 2 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_6_737005926.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/raw/raw6'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/raw/raw6'
Step 4. 히든 파일 적용 후 Resetlogs 옵션으로 강제 open 시킴
SQL> show parameter corruption
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
----------
OPEN
SQL> select * from tt80;
select * from tt80
*
ERROR at line 1:
ORA-00942: table or view does not exist
DB가 open 은 되었으나 테이블이 복구가 되지 않았습니다.
11-2 . Redo log file 이 Inactive 상태일 때 장애
* 위와 같은 장애 상황에서 리두 로그 파일이 삭제 되기 전에 inactive 인 경우에 어떻게 복구가 되는지 살펴보겠습니다.
*작업 순서 요약
Step 1. 테스트 테이블 생성 후 데이터 입력
Step 2. Checkpoint 발생시켜 로그 파일을 inactive 상태로 변경
Step 3. Current Redo log file 삭제 후 강제 종료
Step 4. 백업 파일 복원 없이 복구 하고 오픈 시도-실패함
Step 5. 히든 파일 적용 후 Resetlogs 옵션으로 open 시킴
Step 1. 테스트 테이블 생성 후 데이터 입력
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ --------------------- -------- ----------------- ------------- --------------- ------
1 /dev/raw/raw13 49 4 CURRENT 1 NO
1 /dev/raw/raw14 49 4 CURRENT 1 NO
2 /dev/raw/raw15 49 2 INACTIVE 1 YES
2 /dev/raw/raw16 49 2 INACTIVE 1 YES
3 /dev/raw/raw17 49 3 ACTIVE 1 YES
3 /dev/raw/raw18 49 3 ACTIVE 1 YES
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 ACTIVE 2 YES
5 /dev/raw/raw22 49 2 ACTIVE 2 YES
6 /dev/raw/raw23 49 3 ACTIVE 2 YES
6 /dev/raw/raw24 49 3 ACTIVE 2 YES
12 rows selected.
SQL> create table tt80 (no number) tablespace users;
Table created.
SQL> insert into tt80 values (1);
1 row created.
SQL> commit;
Commit complete.<- 1번 그룹에 저장됨.
SQL> alter system switch logfile;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ -------------------- -------- --------------- -------------- ------------- --------1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 CURRENT 1 NO
2 /dev/raw/raw16 49 5 CURRENT 1 NO
3 /dev/raw/raw17 49 3 ACTIVE 1 YES
3 /dev/raw/raw18 49 3 ACTIVE 1 YES
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 ACTIVE 2 YES
5 /dev/raw/raw22 49 2 ACTIVE 2 YES
6 /dev/raw/raw23 49 3 ACTIVE 2 YES
6 /dev/raw/raw24 49 3 ACTIVE 2 YES
12 rows selected.
SQL> insert into tt80 values (2);
1 row created.
SQL> commit;
Commit complete. <- 2번 그룹에 저장됨.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ ---------------------- -------- ---------------- ------------ ------------- ------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 ACTIVE 2 YES
5 /dev/raw/raw22 49 2 ACTIVE 2 YES
6 /dev/raw/raw23 49 3 ACTIVE 2 YES
6 /dev/raw/raw24 49 3 ACTIVE 2 YES
12 rows selected.
SQL> insert into tt80 values (3);
1 row created.
SQL> commit;
Commit complete.<- 3번 그룹에 저장됨.
Step 2. Checkpoint 발생시켜 로그 파일을 inactive 상태로 변경
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
------------ ---------------------- ------ ---------------- ---------------- -------------- -------
1 /dev/raw/raw13 49 4 ACTIVE 1 YES
1 /dev/raw/raw14 49 4 ACTIVE 1 YES
2 /dev/raw/raw15 49 5 ACTIVE 1 YES
2 /dev/raw/raw16 49 5 ACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 ACTIVE 2 YES
5 /dev/raw/raw22 49 2 ACTIVE 2 YES
6 /dev/raw/raw23 49 3 ACTIVE 2 YES
6 /dev/raw/raw24 49 3 ACTIVE 2 YES
12 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> @log
GROUP# MEMBER MB SEQUENCE# STATUS THREAD# ARC
----------- ---------------------- ------ ----------------- --------------- ------------ --------
1 /dev/raw/raw13 49 4 INACTIVE 1 YES
1 /dev/raw/raw14 49 4 INACTIVE 1 YES
2 /dev/raw/raw15 49 5 INACTIVE 1 YES
2 /dev/raw/raw16 49 5 INACTIVE 1 YES
3 /dev/raw/raw17 49 6 CURRENT 1 NO
3 /dev/raw/raw18 49 6 CURRENT 1 NO
4 /dev/raw/raw19 49 4 CURRENT 2 NO
4 /dev/raw/raw20 49 4 CURRENT 2 NO
5 /dev/raw/raw21 49 2 INACTIVE 2 YES
5 /dev/raw/raw22 49 2 INACTIVE 2 YES
6 /dev/raw/raw23 49 3 INACTIVE 2 YES
6 /dev/raw/raw24 49 3 INACTIVE 2 YES
12 rows selected.
Step 3. Current Redo log file 삭제 후 강제 종료
SQL> !dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd: writing `/dev/raw/raw17': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> !dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd: writing `/dev/raw/raw18': 장치에 남은 공간이 없음
6657+0개의 레코드를 입력하였습니다
6656+0개의 레코드를 출력하였습니다
SQL> shutdown abort;
ORACLE instance shut down.
Step 4. 백업 파일 복원 없이 복구 하고 오픈 시도-실패함
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> show parameter corruption;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> show parameter corruption;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SQL> recover database until cancel;
ORA-00279: change 467822 generated at 12/06/2010 05:23:05 needed for thread 2
ORA-00289: suggestion : /data/arc2/2_4_737006423.dbf
ORA-00280: change 467822 for thread 2 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/data/arc2/2_4_737006423.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/raw/raw6'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/dev/raw/raw6'
Step 5. 히든 파일 적용 후 Resetlogs 옵션으로 open 시킴
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 83889376 bytes
Database Buffers 92274688 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from tt80;
NO
-------------
1
2
3
* 리두로그파일의 내용이 checkpoint 가 발생하면서 모두 데이터 파일에 저장이 되어서 히든파라미터를 사용해서
강제로 open을 시켜도 모든 데이터가 다 복구된 것을 볼 수 있습니다.
이상으로 RAC Raw Device 환경에서 장애를 대비해서 백업하고 또 장애 발생시에 복구하는 여러가지 방법들을 살펴 보았습니다.
No archive log mode 에서는 장애가 발생 할 경우에 거의 복구가 불가 하므로 여기서는 언급하지 않지만
기본적인 방법이나 원리는 Archive log mode 에서와 동일 합니다.
지금까지 살펴본 오라클 백업 복구 기술들을 잘 익히셔서 훌륭하고 든든한 오라클의 의사가 되어 주십시오.