꿈꾸는 개발자, DBA 커뮤니티 구루비

Parameter File

Oracle 시작 과정에서 반드시 필요한 파일

4.1 Oracle STARTUP 과정

Oracle 시작 화면


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 29 06:52:14 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP							<<< ①
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             339741416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7950336 bytes			<<< ②
Database mounted.						<<< ③
Database opened.						<<< ④
SQL>

Oracle 시작 단계

순번	단계	내용	비고
①	STARTUP	사용자 명령
②	NOMOUNT	Parameter File 내 설정 값으로 Instance 생성(SGA + Background Process) 및 Alert Log 기록
③	MOUNT	Control File 내 Checkpoint 정보로 DB 이상 여부 검증 및 필요시 Instance Recovery (SMON)	내용 수행 실패시 NOMOUNT 단계 중단
④	OPEN	Control File 과 Data File 의 헤더 정보로 DB 이상 여부 검증 및 필요시 Instance Recovery (SMON)	내용 수행 실패시 MOUNT 단계 중단

4.2 Parameter File에 대해 살펴봅니다

Parameter File

구분	오라클 버전	형식	이름	경로	비고
PFILE	Oracle 8i 까지	텍스트	initSID.ora	$ORACLE_HOME/dbs
SPFILE	Oracle 9i 부터	바이너리	spfileSID.ora	$ORACLE_HOME/dbs	에디터로 직접 수정 불가능, PFILE 보다 우선 적용

Dynamic SGA

Oracle 9i 부터 DB 재기동 없이 SGA 설정 파라미터 즉시 변경 가능 (PFILE/SPFILE 무관)
변경 가능 파라미터는 V$PARAMETER.ISSYS_MODIFIABLE = 'IMMEDIATE' 항목 에서 확인 가능

실습1. 모든 Parameter File 손실시 대응하기


-- Parameter File 손실 시 최초 Parameter File(최초 DB 생성시 초기 값으로 구성 됨) 활용 하여 복구
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 29 07:31:28 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
$ pwd
/sw/oracle/app/oracle/product/11.2.0/dbs
$ ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:31 hc_UTF8.dat
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora			<<< SPFILE 존재

$ mv spfileUTF8.ora spfileUTF8.ora.FAILED											<<< Parameter File 장애
$ ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:31 hc_UTF8.dat
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora.FAILED

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 29 07:40:15 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters									<<< 기동시 에러 발생
LRM-00109: could not open parameter file '/sw/oracle/app/oracle/product/11.2.0/dbs/initUTF8.ora'

$ ls /sw/oracle/app/oracle/admin/UTF8/pfile
init.ora.10172011111949																<<< 최초 Parameter File 

$ cp /sw/oracle/app/oracle/admin/UTF8/pfile/init.ora.10172011111949 initUTF8.ora	<<< 복구

$ ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:31 hc_UTF8.dat
-rw-r-----    1 oracle   dba            1926 Oct 29 07:35 initUTF8.ora				<<< 복구된 PFILE
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora.FAILED

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 29 07:36:49 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             234883816 bytes
Database Buffers          289406976 bytes
Redo Buffers                7950336 bytes
Database mounted.
Database opened.																	<<< 정상 기동

SQL>
SQL> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string											<<< 값이 있으면 SPFILE 없으면 PFILE

실습2. PFILE ↔ SPFILE 만들기


SQL> !ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:39 hc_UTF8.dat
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora

SQL> create pfile from spfile;														<<< PFILE 생성

File created.

SQL> !ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:39 hc_UTF8.dat
-rw-r--r--    1 oracle   dba             860 Oct 29 07:42 initUTF8.ora
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora

SQL> !strings spfileUTF8.ora > 1

SQL> !diff 1 initUTF8.ora
13,14c13
< *.control_files='/oradata2/system/control01.ctl','/oradata2/s
< ystem/control02.ctl'
---
> *.control_files='/oradata2/system/control01.ctl','/oradata2/system/control02.ctl'

SQL> !mv spfileUTF8.ora spfileUTF8.ora.FAILED

SQL> !ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:39 hc_UTF8.dat
-rw-r--r--    1 oracle   dba             860 Oct 29 07:42 initUTF8.ora
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora.FAILED

SQL> create spfile from pfile;														<<< SPFILE 생성

File created.

SQL> !ls -al *UTF8*
-rw-rw----    1 oracle   dba            1544 Oct 29 07:39 hc_UTF8.dat
-rw-r--r--    1 oracle   dba             860 Oct 29 07:42 initUTF8.ora
-rw-r-----    1 oracle   dba              24 Nov 17 2011  lkUTF8
-rw-r-----    1 oracle   dba            1536 Oct 23 20:13 orapwUTF8
-rw-r-----    1 oracle   dba            1536 Mar 31 2015  orapwUTF8.151023
-rw-r-----    1 oracle   dba            2560 Oct 29 07:44 spfileUTF8.ora
-rw-r-----    1 oracle   dba            2560 Oct 29 07:31 spfileUTF8.ora.FAILED

실습3. STARTUP / SHUTDOWN 실습하기


$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 29 07:56:10 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SQL> shutdown immediate															<<< 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount															<<< 
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2221336 bytes
Variable Size             339741416 bytes
Database Buffers          184549376 bytes
Redo Buffers                7950336 bytes
SQL> select status from v$instance;

STATUS
------------------------
STARTED

SQL> alter database mount;														<<< 

Database altered.

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database open;														<<< 

Database altered.

SQL> select status from v$instance;

STATUS
------------------------
OPEN

SQL>

SQL> shutdown mount;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown nomount;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown mount immediate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown nomount immediate;
SP2-0717: illegal SHUTDOWN option
SQL> alter database close;														<<< 

Database altered.

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database open;														<<< 
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed


SQL> !oerr ora 16196
16196, 00000, "database has been previously opened and closed"
// *Cause:  The instance has already opened and closed the database,
//          which is allowed only once in its lifetime.
// *Action: Shut down the instance.

SQL> shutdown immediate															<<< 
ORA-01109: database not open													<<< 


Database dismounted.
ORACLE instance shut down.

SQL>

Chap.04 Parameter File 관리 및 장애 복구 (by openlsw) [2015.10.29]

4.1 Oracle STARTUP 과정

4.2 Parameter File에 대해 살펴봅니다

실습1. 모든 Parameter File 손실시 대응하기

실습2. PFILE ↔ SPFILE 만들기

실습3. STARTUP / SHUTDOWN 실습하기