피파일, 에스피파일 만들기

from 오라클백업과복구/파라미터파일장애복구 2015. 12. 21. 14:32

* 에스피파일이 있을 경우 피파일 생성 가능 *
* 피파일이 있을 경우 에스피파일 생성 가능 *

 

** 요약 **

1. pfile from spfile

2. spfile from pfile

 

 

1. 파라미터 파일 조회 후 피파일 생성(spfile).

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 20
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-rw---- 1 oracle dba 1544 Dec 21 15:24 hc_ARK11G.dat
-rw-r----- 1 oracle dba 2560 Dec 21 15:24 spfileARK11G.ora

/oracle/product/11.2.0/dbhome_1/dbs>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:43:52 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.0/dbhome_
                                                 1/dbs/spfileARK11G.ora

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-rw---- 1 oracle dba 1544 Dec 21 15:24 hc_ARK11G.dat
-rw-r----- 1 oracle dba 2560 Dec 21 15:24 spfileARK11G.ora
-rw-r--r-- 1 oracle dba  834 Dec 21 15:43 initARK11G.ora

/oracle/product/11.2.0/dbhome_1/dbs>rm spfileARK11G.ora 

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 20
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-rw---- 1 oracle dba 1544 Dec 21 15:24 hc_ARK11G.dat
-rw-r--r-- 1 oracle dba  834 Dec 21 15:43 initARK11G.ora

/oracle/product/11.2.0/dbhome_1/dbs>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:44:11 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             222302184 bytes
Database Buffers           83886080 bytes
Redo Buffers                4718592 bytes
Database mounted.
Database opened.

SQL> show parameter pfile;

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

 

 

2. 파라미터 파일 조회 후 에스피파일 생성(pfile).

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-r--r-- 1 oracle dba  834 Dec 21 15:43 initARK11G.ora
-rw-rw---- 1 oracle dba 1544 Dec 21 15:44 hc_ARK11G.dat
-rw-r----- 1 oracle dba 2560 Dec 21 15:48 spfileARK11G.ora

/oracle/product/11.2.0/dbhome_1/dbs>rm initARK11G.ora 

/oracle/product/11.2.0/dbhome_1/dbs>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:48:21 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

SQL> startup
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             222302184 bytes
Database Buffers           83886080 bytes
Redo Buffers                4718592 bytes
Database mounted.
Database opened.

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.0/dbhome_
                                                 1/dbs/spfileARK11G.ora
저작자표시 비영리 변경금지 (새창열림)

'오라클백업과복구 > 파라미터파일장애복구' 카테고리의 다른 글

파라미터 파일 생성 및 관리하기(모든 파라미터 파일 삭제)  (0) 2015.12.21
오라클 스타트업 과정  (0) 2015.12.21
,

파라미터 파일 생성 및 관리하기(모든 파라미터 파일 삭제)

from 오라클백업과복구/파라미터파일장애복구 2015. 12. 21. 14:32

* 파라미터 파일이 모두 삭제된 경우, 설치 시 생성된 기본 파라미터 파일 사용가능 *
* 운영 중에 변경된 내용에 대해서는 반영되지 않은, 순수한 기본 파라미터 파일이므로 어쩔 수 없을 경우에만 사용 *

 

** 요약 **

1. $ORACLE_BASE/admin/<SID>/pfile 경로에 있는 파라미터 파일을 이용하여 디비오픈

2. 에스피파일생성(파라미터 설정복원 필요)

3. 디비재기동

4. 피파일생성

 

 

1. 모든 파라미터 파일 삭제(*.bak로 이름 변경).

/home/oracle>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:06:47 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.0/dbhome_
                                                 1/dbs/spfileARK11G.ora

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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

/home/oracle>cd $ORACLE_HOME/dbs

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-r--r-- 1 oracle dba  835 Aug 30 11:29 initARK11G.ora
-rw-r----- 1 oracle dba 2560 Dec 21 14:58 spfileARK11G.ora
-rw-rw---- 1 oracle dba 1544 Dec 21 15:07 hc_ARK11G.dat

/oracle/product/11.2.0/dbhome_1/dbs>mv initARK11G.ora initARK11G.bak 
/oracle/product/11.2.0/dbhome_1/dbs>mv spfileARK11G.ora spfileARK11G.bak 

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-r--r-- 1 oracle dba  835 Aug 30 11:29 initARK11G.bak
-rw-r----- 1 oracle dba 2560 Dec 21 14:58 spfileARK11G.bak
-rw-rw---- 1 oracle dba 1544 Dec 21 15:07 hc_ARK11G.dat

/oracle/product/11.2.0/dbhome_1/dbs>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:08:42 2015

Copyright (c) 1982, 2013, 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 '/oracle/product/11.2.0/dbhome_1/dbs/initARK11G.ora'
SQL> 
SQL> exit
Disconnected
/oracle/product/11.2.0/dbhome_1/dbs>cd $ORACLE_BASE/admin/pfile
-bash: cd: /oracle/admin/pfile: No such file or directory
/oracle/product/11.2.0/dbhome_1/dbs>cd $ORACLE_BASE
/oracle>ls -rlt
total 44
drwxr-xr-x  7 oracle dba  4096 Aug 27  2013 database
drwx------  2 oracle dba 16384 Aug 23 07:44 lost+found
drwxr-xr-x  3 oracle dba  4096 Aug 23 12:57 product
drwxrwx---  5 oracle dba  4096 Aug 23 13:10 oraInventory
drwxrwxr-x 11 oracle dba  4096 Aug 23 13:10 diag
drwxr-xr-x  2 oracle dba  4096 Aug 23 13:25 checkpoints
drwxr-x---  6 oracle dba  4096 Aug 24 11:19 cfgtoollogs
drwxr-x---  3 oracle dba  4096 Aug 24 15:49 admin
/oracle>cd admin
/oracle/admin>ls -rlt
total 4
drwxr-x--- 6 oracle dba 4096 Aug 24 15:49 ARK11G
/oracle/admin>cd ARK11G/
/oracle/admin/ARK11G>cd /oracle/product/11.2.0/dbhome_1/d 
-bash: cd: /oracle/product/11.2.0/dbhome_1/d: No such file or directory
/oracle/admin/ARK11G>cd /oracle/product/11.2.0/dbhome_1/dbs/
/oracle/product/11.2.0/dbhome_1/dbs>ls
hc_ARK11G.dat  init.ora  initARK11G.bak  lkARK11G  orapwARK11G  spfileARK11G.bak
/oracle/product/11.2.0/dbhome_1/dbs>
/oracle/product/11.2.0/dbhome_1/dbs>
/oracle/product/11.2.0/dbhome_1/dbs>
/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-r--r-- 1 oracle dba  835 Aug 30 11:29 initARK11G.bak
-rw-r----- 1 oracle dba 2560 Dec 21 14:58 spfileARK11G.bak
-rw-rw---- 1 oracle dba 1544 Dec 21 15:07 hc_ARK11G.dat
/oracle/product/11.2.0/dbhome_1/dbs>cd $ORACLE_BASE/admin/ARK11G/pfile
/oracle/admin/ARK11G/pfile>ls -rlt
total 4
-rw-r----- 1 oracle dba 1474 Aug 24 15:50 init.ora.72420151645

 

 

2. 디비 스타트업.

/oracle/admin/ARK11G/pfile>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 15:09:53 2015

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

Connected to an idle instance.

SQL> startup pfile='/oracle/admin/ARK11G/pfile/init.ora.72420151645'
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size                  2251856 bytes
Variable Size             163578800 bytes
Database Buffers           50331648 bytes
Redo Buffers                5169152 bytes
Database mounted.
Database opened.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
저작자표시 비영리 변경금지 (새창열림)

'오라클백업과복구 > 파라미터파일장애복구' 카테고리의 다른 글

피파일, 에스피파일 만들기  (0) 2015.12.21
오라클 스타트업 과정  (0) 2015.12.21
,

오라클 스타트업 과정

from 오라클백업과복구/파라미터파일장애복구 2015. 12. 21. 14:30

* 오라클 스타트업 과정 *

【startup】 ⇨➀⇨ 【no mount】 ⇨➁⇨ 【mount】 ⇨➂⇨ 【open】

 

➀ 단계에서는 반드시 파라미터파일이 필요. 파라미터파일을 피쥐에이로 읽어와서 에스쥐에이를 생성함. 얼럿로그 기록을 시작.

4. 엔드백업

 

 

1. 데이터파일, 기타파일(파라미터파일, 패스워드파일) 조회.

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
SYSTEM                         /oradata/system01.dbf
SYSAUX                         /oradata/sysaux01.dbf
UNDOTBS1                       /oradata/undotbs01.dbf
USERS                          /oradata/users01.dbf


SQL> SQL> select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------
TEMP                           /oradata/temp01.dbf


SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/11.2.0/dbhome_1/
                                                 dbs/spfileARK11G.ora
                                                 
SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

/home/oracle>cd $ORACLE_HOME/dbs

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt orapw*
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt *.ora
-rw-r----- 1 oracle dba 2560 Oct  2 16:16 spfileARK11G.ora
-rw-r--r-- 1 oracle dba  871 Oct  2 17:42 initARK11G.ora

 

 

2. 열린 백업(핫 백업/온라인 백업) 수행. (/oracle_hotbackup)

/oracle/product/11.2.0/dbhome_1/dbs>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 5 13:34:14 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database begin backup;

Database altered.

SQL> alter database backup controlfile to '/oracle_hotbackup/control01.ctl';

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

/home/oracle>cd /oradata

/oradata>ls -rlt
total 1717212
drwx------ 2 oracle dba     16384 Aug 23 07:44 lost+found
-rw-r----- 1 oracle dba  20979712 Oct 15 12:02 temp01.dbf
-rw-r--r-- 1 oracle dba  52429312 Dec 21 13:58 redo02.log
-rw-r----- 1 oracle dba  52429312 Dec 21 13:58 redo01.log
-rw-r----- 1 oracle dba   5251072 Dec 21 13:59 users01.dbf
-rw-r----- 1 oracle dba 209723392 Dec 21 13:59 undotbs01.dbf
-rw-r----- 1 oracle dba 734011392 Dec 21 13:59 system01.dbf
-rw-r----- 1 oracle dba 629153792 Dec 21 13:59 sysaux01.dbf
-rw-r----- 1 oracle dba  52429312 Dec 21 13:59 redo03.log
-rw-r--r-- 1 oracle dba   9748480 Dec 21 13:59 control02.ctl
-rw-r--r-- 1 oracle dba   9748480 Dec 21 13:59 control01.ctl

/oradata>cp -av *.dbf /oracle_hotbackup/
`sysaux01.dbf' -> `/oracle_hotbackup/sysaux01.dbf'
`system01.dbf' -> `/oracle_hotbackup/system01.dbf'
`temp01.dbf' -> `/oracle_hotbackup/temp01.dbf'
`undotbs01.dbf' -> `/oracle_hotbackup/undotbs01.dbf'
`users01.dbf' -> `/oracle_hotbackup/users01.dbf'

/oradata>cd $ORACLE_HOME/dbs

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt
total 24
-rw-r--r-- 1 oracle dba 2851 May 15  2009 init.ora
-rw-r----- 1 oracle dba 1536 Aug 24 15:50 orapwARK11G
-rw-r----- 1 oracle dba   24 Aug 24 15:50 lkARK11G
-rw-r--r-- 1 oracle dba  835 Aug 30 11:29 initARK11G.ora
-rw-rw---- 1 oracle dba 1544 Dec 21 13:58 hc_ARK11G.dat
-rw-r----- 1 oracle dba 2560 Dec 21 14:03 spfileARK11G.ora

/oracle/product/11.2.0/dbhome_1/dbs>cp -av initARK11G.ora orapwARK11G /oracle_hotbackup/
`initARK11G.ora' -> `/oracle_hotbackup/initARK11G.ora'
`orapwARK11G' -> `/oracle_hotbackup/orapwARK11G'

/oracle/product/11.2.0/dbhome_1/dbs>ls -rlt /oracle_hotbackup 
total 1553884
-rw-r----- 1 oracle dba      1536 Aug 24 15:50 orapwARK11G
-rw-r--r-- 1 oracle dba       835 Aug 30 11:29 initARK11G.ora
-rw-r----- 1 oracle dba  20979712 Oct 15 12:02 temp01.dbf
-rw-r----- 1 oracle dba   5251072 Dec 21 14:06 users01.dbf
-rw-r----- 1 oracle dba 209723392 Dec 21 14:06 undotbs01.dbf
-rw-r----- 1 oracle dba 734011392 Dec 21 14:06 system01.dbf
-rw-r----- 1 oracle dba 629153792 Dec 21 14:06 sysaux01.dbf
-rw-r----- 1 oracle dba   9748480 Dec 21 14:06 control01.ctl

/oradata>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 21 14:01:26 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE             523461 21-DEC-15
         2 NOT ACTIVE             523461 21-DEC-15
         3 NOT ACTIVE             523461 21-DEC-15
         4 NOT ACTIVE             523461 21-DEC-15
저작자표시 비영리 변경금지 (새창열림)

'오라클백업과복구 > 파라미터파일장애복구' 카테고리의 다른 글

피파일, 에스피파일 만들기  (0) 2015.12.21
파라미터 파일 생성 및 관리하기(모든 파라미터 파일 삭제)  (0) 2015.12.21
,
<< previous 1 2 3 4 next >>

티스토리툴바