old Control file 복구(Raw Device)

Redo Log 가 이상 없을때 하는 방법이다.

1. 장애상황만들기
2. 컨트롤 파일 재생성 (NORESETLOGS)
3. RAC 사용 중지
4. 컨트롤 파일 생성
5. 데이터 베이스 복구
6. RAC 사용

 

1. 장애상황 만들기(node1)

컨트롤 파일 백업받기

 SQL> alter database backup controlfile to '/data/open/control.ctl';

CheckPoint 발생시키기

SQL> alter system checkpoint;
/
/ 

장애 발생시키기

SQL> !dd if=/dev/zero of=/dev/raw/raw7 bs=8k

SQL> !dd if=/dev/zero of=/dev/raw/raw8 bs=8k

SQL> !dd if=/dev/zero of=/dev/raw/raw9 bs=8k

SQL> alter system checkpoint;

alter system checkpoint

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

SQL> startup

ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1272696 bytes

Variable Size              83887240 bytes

Database Buffers           96468992 bytes

Redo Buffers                2920448 bytes

ORA-00214: control file '/dev/raw/raw8' version 392 inconsistent with file

'/dev/raw/raw7' version 329

 백업 받은 컨트롤파일을 적용시키기

SQL> !dd if=/data/open/con.ctl of=/dev/raw/raw7 bs=8k

1906+0개의 레코드를 입력하였습니다

1906+0개의 레코드를 출력하였습니다

SQL> !dd if=/data/open/con.ctl of=/dev/raw/raw8 bs=8k

1906+0개의 레코드를 입력하였습니다

1906+0개의 레코드를 출력하였습니다

SQL>  !dd if=/data/open/con.ctl of=/dev/raw/raw9 bs=8k

1906+0개의 레코드를 입력하였습니다

1906+0개의 레코드를 출력하였습니다

Oracle을 Startup 해보자.

SQL> startup

ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1272696 bytes

Variable Size              83887240 bytes

Database Buffers           96468992 bytes

Redo Buffers                2920448 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 290129 generated at 03/11/2012 19:16:58 needed for thread 1

ORA-00289: suggestion : /data/arc2/1_19_777308864.dbf

ORA-00280: change 290129 for thread 1 is in sequence #19

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

auto

ORA-00308: cannot open archived log '/data/arc2/1_19_777308864.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/data/arc2/1_19_777308864.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database open noresetlogs;

alter database open noresetlogs

*

ERROR at line 1:

ORA-01588: must use RESETLOGS option for database open

위에서 /data/arc2/1_19_777308846.dbf 를 찾지만 존재하지 않는다. 문제의 19번이다.

현재 Archive Log 상태를 확인한다.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /data/arc2

Oldest online log sequence     18

Next log sequence to archive   19   << 이 부분을 잘 보자. 19번이다.

Current log sequence           19

2. Control File 재생성(node1)

SQL> alter database backup controlfile to trace as '/home/oracle/con.sql';
SQL> !vi /home/oracle/con.sql
NoResetLogs 로 수정한다.

SQL> shutdown abort;
 ORACLE instance shut down.

SQL> @con
ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1272696 bytes

Variable Size              83887240 bytes

Database Buffers           96468992 bytes

Redo Buffers                2920448 bytes

CREATE CONTROLFILE REUSE DATABASE "RAC" NORESETLOGS  ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-12720: operation requires database is in EXCLUSIVE mode

Cluster_database 사용하지 않기.

SQL> alter system set cluster_database=false scope=spfile;
SQL> shutdown abort
ORACLE instance shut down.

SQL>  @con 

ORACLE instance started.

Total System Global Area  184549376 bytes

Fixed Size                  1272696 bytes

Variable Size              83887240 bytes

Database Buffers           96468992 bytes

Redo Buffers                2920448 bytes

Control file created.

SQL> alter system set cluster_database=true scope=spfile;

System altered. 

현재 Archive Log 상태를 확인한다. 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Disabled

Archive destination            /data/arc2

Oldest online log sequence     18

Next log sequence to archive   18 << 19에서 18로 변경되었다.

Current log sequence           19

데이터 베이스 복구(node1)

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery if it was restored from backup, or END

BACKUP if it was not

ORA-01110: data file 1: '/dev/raw/raw3'

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

RAC 사용(node1, node2)
아까 cluster_database parameter 를 true 로 바꾸고 재시작을 하지 않았기 때문에 현재 single Instance로 동작한다. 두개의 node 를 shutdown immediate 한 후에 startup 하면 된다.

node1 sql> shutdown immediate

Database closed.

Database dismounted.

node1 sql> startup
node2 sql> startup