[Oracle - startup error] ORA-01565,ORA-27037

DB 기동시 error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDB0225.ora'

 

 

초기화 파일의 기본 위치

$ORACLE_HOME/dbs

 

 

 

## 사전에 pfile을 만든게 있다면 pfile을 이용해서 기동

SQL> create pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kim.ora' from spfile;

SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kim.ora'
ORACLE instance started.

Total System Global Area 3.0602E+10 bytes
Fixed Size           13872240 bytes
Variable Size         3623878656 bytes
Database Buffers     2.6911E+10 bytes
Redo Buffers           53235712 bytes
Database mounted.
Database opened.


SQL> show parameter spfile
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string

 

 

 

 

 

 

## spfile 기동 

SQL> create spfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kimspfile.ora' from pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kim.ora';

File created.


SQL> startup
ORACLE instance started.

Total System Global Area 3.0602E+10 bytes
Fixed Size           13872240 bytes
Variable Size         3623878656 bytes
Database Buffers     2.6911E+10 bytes
Redo Buffers           53235712 bytes
Database mounted.
Database opened.



SQL> show parameter spfile

NAME                     TYPE           VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /u01/app/oracle/product/19.0.0
                                      .0/dbhome_1/dbs/kimspfile.ora

 

 

 

 

 

## 둘다 없을 시

[oracle@jh-test dbs]$ rm -rf kimspfile.ora
[oracle@jh-test dbs]$ rm -rf kim.ora


SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDB0225.ora'



## crsctl stat res로 config를 볼 수 있습니다. 해당 config에서 spfile 경로를 찾습니다.

[grid@jh-test ~]$ crsctl stat res ora.db0225_icn1zv.db -p

SPFILE=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kimspfile.ora

SQL> create pfile='/u01/app/oracle/jh.ora' from spfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kimspfile.ora';
create pfile='/u01/app/oracle/jh.ora' from spfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kimspfile.ora'
*
ERROR at line 1:
ORA-01565: error in identifying file
'/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/kimspfile.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information:

 

 

 

 

 

 

## 복구 시나리오

## ORACLE_BASE/admin/testdb/pfile
[oracle@jh-test pfile]$ cd /u01/app/oracle/admin/DB0225_icn1zv/pfile


## ORACLE_HOME의 dbs디렉토리로 복사
[oracle@jh-test pfile]$ cp init.ora.125202201144  $ORACLE_HOME/dbs/initDB0225_icn1zv.ora


## 해당 pfile로 DB 기동
SQL> startup pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDB0225_icn1zv.ora'
ORACLE instance started.

Total System Global Area 3.0602E+10 bytes
Fixed Size           13872240 bytes
Variable Size         3489660928 bytes
Database Buffers     2.7045E+10 bytes
Redo Buffers           53235712 bytes
Database mounted.
Database opened.



SQL> show parameter spfile

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string

 

 

 

 

## 현재 pfile로 되어있는 상태. spfile로 올리는 방법

## pfile로 spfile을 생성 후 db재기동해주면 됩니다.
SQL> create spfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/newspfile.ora' from pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDB0225_icn1zv.ora';

File created.



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.



SQL> startup
ORACLE instance started.

Total System Global Area 3.0602E+10 bytes
Fixed Size           13872240 bytes
Variable Size         3489660928 bytes
Database Buffers     2.7045E+10 bytes
Redo Buffers           53235712 bytes
Database mounted.
Database opened.



SQL> show parameter spfile

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /u01/app/oracle/product/19.0.0
                         .0/dbhome_1/dbs/newspfile.ora





## 아래 명령어로도 db설정 및 spfile 경로 확인 가능
[grid@jh-test ~]$ crsctl stat res ora.db0225_icn1zv.db -p
SPFILE=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/newspfile.ora