ORA-00349: failure obtaining block size for '+oradat

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 11.2.0.4

에러 : ORA-00349: failure obtaining block size for '+oradat

ASM db에서 FileSystem db 으로 clone db를 만들던 중

alter database open 명령시 발생한 오류

1 2 3 4

SQL> alter database open resetlogs; * ERROR at line 1: ORA-00349: failure obtaining block size for '+oradat

해결 방법 : redo log 파일이 정상적으로 있는지 확인

redo log 파일이 있는지 확인

1 2 3 4 5 6 7 8 9 10 11

$ ls -al /app/oracle/oradata/newdev/ total 2979820 drwxr-xr-x. 2 oracle oinstall       4096 Feb 23 05:54 . drwxr-x---. 4 oracle oinstall       4096 Feb 23 02:28 .. -rw-r-----. 1 oracle oinstall 1073750016 Feb 23 06:12 bokgu01.dbf -rw-r-----. 1 oracle oinstall   18825216 Feb 23 06:16 control01.ctl -rw-r-----. 1 oracle oinstall   18825216 Feb 23 06:16 control02.ctl -rw-r-----. 1 oracle oinstall  629153792 Feb 23 06:16 sysaux01.dbf -rw-r-----. 1 oracle oinstall  734011392 Feb 23 06:16 system01.dbf -rw-r-----. 1 oracle oinstall  209723392 Feb 23 06:16 undotbs01.dbf -rw-r-----. 1 oracle oinstall  209723392 Feb 23 06:01 undotbs02.dbf

rman 으로 복구 구 controlfile 과 datafile만 존재함

redo log file은 ASM 경로로 지정되어 있기 때문에 에러메세지로 '+oradat 까지 잘려서 나오는듯함

같은서버가 아니라 다른서버에서 가져온 rman 백업본으로 작업한 것이기때문에 redolog file이 없음

그럴땐 컨트롤파일 재생성을 해야함

컨트롤 파일 생성문 백업

1 2 3

SQL> alter database backup controlfile to trace as '/home/oracle/recon.sql';   Database altered.

컨트롤 파일 생성문 수정

컨트롤파일 생성파일 확인(기존)

REUSE RESETLOGS가 적힌 부분만 남기고 다른부분은 REUSE NORESETLOGS 부분은 삭제한 상태

$ cat recon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (  <-- 이부분때문에 발생한 문제
    '+oradata',
    '+orafra'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+oradata',
    '+orafra'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/app/oracle/oradata/newdev/system01.dbf',
  '/app/oracle/oradata/newdev/sysaux01.dbf',
  '/app/oracle/oradata/newdev/undotbs01.dbf',
  '/app/oracle/oradata/newdev/undotbs02.dbf',
  '+ORADATA/racdb/datafile/users.264.970626185',
  '+ORADATA/racdb/datafile/users.268.970884085',
  '+ORADATA/racdb/datafile/users.269.970884253',
  '/app/oracle/oradata/newdev/bokgu01.dbf'
CHARACTER SET KO16MSWIN949
;
 
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/rman/db_%U_%T''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/db_control_%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/arch/rac11g_11970626162.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
 
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 (
    '+oradata',
    '+orafra'
  ) SIZE 50M BLOCKSIZE 512 REUSE,
  GROUP 4 (
    '+oradata',
    '+orafra'
  ) SIZE 50M BLOCKSIZE 512 REUSE;
 
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
 
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/newdev/temp01.dbf' REUSE;
-- End of tempfile additions.
--

변경 사항 : 

redo log 위치 ASM 에서 filesystem으로 변경

사용하지 않는 데이터파일 구문 삭제

RECOVER DATABASE, ALTER DATABASE OPEN RESETLOGS 구문 삭제

rac2번 노드(threads) redo log 생성 구문 삭제

-- 등 주석 구문 삭제

컨트롤파일 생성파일 변경 확인

$ cat recon.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/app/oracle/oradata/newdev/redo01.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/app/oracle/oradata/newdev/redo02.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/app/oracle/oradata/newdev/redo03.log'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/app/oracle/oradata/newdev/system01.dbf',
  '/app/oracle/oradata/newdev/sysaux01.dbf',
  '/app/oracle/oradata/newdev/undotbs01.dbf',
  '/app/oracle/oradata/newdev/undotbs02.dbf',
  '/app/oracle/oradata/newdev/bokgu01.dbf'
CHARACTER SET KO16MSWIN949
;
 
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/rman/db_%U_%T''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/db_control_%F''');
 
--ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/newdev/temp01.dbf' REUSE;

db 종료

SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.

컨트롤파일 재생성

SQL> @recon.sql
ORACLE instance started.
 
Total System Global Area 1686925312 bytes
Fixed Size            2253864 bytes
Variable Size          419433432 bytes
Database Buffers     1258291200 bytes
Redo Buffers            6946816 bytes
 
Control file created.
 
 
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.
 
 
PL/SQL procedure successfully completed.

open 시도

SQL>  alter database open resetlogs;
 
Database altered.

원인 : redo log 경로가 asm으로 잡혀있어서 발생한 문제

참조 :