Perder o SPFILE e PFILE do banco de dados Oracle não ocorre no dia a dia, mas pode acontecer de encontrar cliente sem estes dois arquivos essenciais para inicar o banco de dados Oracle. Então abaixo segue a dica de como iniciar o banco de dados sem estes dois arquivos.

[oracle@oraprd ~]$ # Iniciando o banco de dados com o Alert Log.
[oracle@oraprd ~]$ echo $ORACLE_BASE
/oraprd01/app/oracle

Copiar do Alert Log e colocar num arquivo para criar o pfile apenas os Parâmetros do Sistema com não valores principais. Na linha da última inicialização do banco de dados abaixo de “System parameters with non-default values:” possui os parâmetros esseciais para inicialização do banco de dados. Segue abaixo a forma de inicar o banco de dados e com o pfile que irá ser criado e após criar o spfile do pfile utilizado.

[oracle@oraprd ~]$ cd /oraprd01/app/oracle/diag/rdbms/orcl/orcl/trace/
[oracle@oraprd trace]$ vi alert_orcl.log
Wed Aug 17 13:04:58 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =109
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /ora/prod/11.1/dbs/spfileorcl.ora
System parameters with non-default values:
  processes                = 900
  sessions                 = 995
  nls_date_format          = ""
  memory_target            = 404M
  control_files            = "/oraprd02/oradata/orcl/control01.ctl"
  control_files            = "/oraprd02/oradata/orcl/control02.ctl"
  control_files            = "/oraprd02/oradata/orcl/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  log_archive_dest         = ""
  log_archive_dest_1       = "LOCATION=/oraarchive/orcl/"
  log_archive_dest_2       = ""
  log_archive_format       = "arch_%t_%s_%r.arc"
  archive_lag_target       = 60
  db_file_multiblock_read_count= 7
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  job_queue_processes      = 1000
  audit_file_dest          = "/oraprd01/app/oracle/admin/orcl/adump"
  audit_trail              = "NONE"
  db_name                  = "orcl"
  open_cursors             = 300
  optimizer_index_caching  = 0
  control_management_pack_access= "DIAGNOSTIC+TUNING"
  diagnostic_dest          = "/oraprd01/app/oracle"

[oracle@oraprd trace]$ cd ~
[oracle@oraprd ~]$ vi init_alert_log.ora
[oracle@oraprd ~]$ cat init_alert_log.ora
  processes                = 900
  sessions                 = 995
  nls_date_format          = ""
  memory_target            = 404M
  control_files            = "/oraprd02/oradata/orcl/control01.ctl"
  control_files            = "/oraprd02/oradata/orcl/control02.ctl"
  control_files            = "/oraprd02/oradata/orcl/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  log_archive_dest         = ""
  log_archive_dest_1       = "LOCATION=/oraarchive/orcl/"
  log_archive_dest_2       = ""
  log_archive_format       = "arch_%t_%s_%r.arc"
  archive_lag_target       = 60
  db_file_multiblock_read_count= 7
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  job_queue_processes      = 1000
  audit_file_dest          = "/oraprd01/app/oracle/admin/orcl/adump"
  audit_trail              = "NONE"
  db_name                  = "orcl"
  open_cursors             = 300
  optimizer_index_caching  = 0
  control_management_pack_access= "DIAGNOSTIC+TUNING"
  diagnostic_dest          = "/oraprd01/app/oracle"

[oracle@oraprd ~]$ sqlplus /nolog 

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 17 13:17:44 2011

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

SQL> conn sys/***** as sysdba
Connected.
SQL> startup pfile='/home/oracle/init_alert_log.ora'
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1300352 bytes
Variable Size             293603456 bytes
Database Buffers          121634816 bytes
Redo Buffers                6131712 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/home/oracle/init_alert_log.ora';

File created.

SQL> !

[oracle@oraprd ~]$ cd /oraprd01/app/oracle/product/11.1.0/db_1/dbs/
[oracle@oraprd dbs]$ ls -l
total 57500
-rw-r----- 1 oracle dba 37820928 Jun  6 18:12 arch1_84_753064624.dbf
-rw-r----- 1 oracle dba  5774848 Jun  6 20:26 arch1_85_753064624.dbf
-rw-rw---- 1 oracle dba     1544 Jun  6 00:34 hc_orcl.dat
-rw-r--r-- 1 oracle dba    12920 May  3  2001 initdw.ora
-rw-r--r-- 1 oracle dba     8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle dba       24 Jun  6 00:36 lkORCL
-rw-r----- 1 oracle dba     1536 Aug  2 15:39 orapworcl
-rw-r----- 1 oracle dba 15155200 Aug  9 16:29 snapcf_orcl.f
-rw-r----- 1 oracle dba     2560 Aug 17 13:26 spfileorcl.ora
[oracle@oraprd dbs]$ exit

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

Total System Global Area  422670336 bytes
Fixed Size                  1300352 bytes
Variable Size             293603456 bytes
Database Buffers          121634816 bytes
Redo Buffers                6131712 bytes
Database mounted.
Database opened.
SQL>

Pronto iniciamos o banco de dados com o pfile criado a partir do Alert Log.

Autor: Maycon Tomiasi

Formado em Tecnologia da Informação na FIPP (Faculdade de Informática de Presidente Prudente), Analista DBA Oracle pela Teiko Soluções em Tecnologia da Informação, residente em Blumenau/ SC, Certificado OCP 10g/11g/12c, OCS 11g Implementation, OCE 11g Performance Tuning, OCE 11g RAC & GRID e OPN Specialist. Conhecimentos em PHP.

• 
•