Banco de Dados · 27 de agosto de 2019 · 3 min de leitura
Recovery Database – Perca do Spfile e Pfile
CENÁRIO
Banco de dados encontra-se inativo.
ANALISANDO O AMBIENTE
Inicialmente iremos verificar no alert log em busca do real problema.
[oracle@WINT]$tail -f $ORACLE_BASE/diag/rdbms/wint/WINT/trace/alert_WINT.log
ORA-01565: Unable to open Spfile /u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileORCL.ora.
Wed Jul 31 15:07:54 2019
Thread 1 advanced to log sequence 2 (LGWR switch)
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ORCL/redo02.log
Wed Jul 31 15:07:54 2019
Archived Log entry 82 added for thread 1 sequence 1 ID 0x5bf57269 dest 1:
Wed Jul 31 15:07:55 2019
Thread 1 advanced to log sequence 3 (LGWR switch)
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ORCL/redo03.log
Wed Jul 31 15:07:55 2019
Archived Log entry 83 added for thread 1 sequence 2 ID 0x5bf57269 dest 1:
Wed Jul 31 15:07:56 2019
Thread 1 cannot allocate new log, sequence 4
Checkpoint not complete
Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/ORCL/redo03.log
Wed Jul 31 15:07:57 2019
Thread 1 advanced to log sequence 4 (LGWR switch)
Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ORCL/redo01.log
Wed Jul 31 15:07:57 2019
Thread 1 advanced to log sequence 5 (LGWR switch)
Current log# 2 seq# 5 mem# 0: /u01/app/oracle/oradata/ORCL/redo02.log
Wed Jul 31 15:07:57 2019
Archived Log entry 84 added for thread 1 sequence 3 ID 0x5bf57269 dest 1:
Wed Jul 31 15:07:57 2019
Archived Log entry 85 added for thread 1 sequence 4 ID 0x5bf57269 dest 1:Na saída do alert log, nosso banco de dados reclamou por não apresentar o spfile ‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileORCL.ora’
O próximo passo será recriar o pfile com os parâmetros do banco que estão registrados no alert log.
$vim initWINT ora
processes = 600
nls_language = "BRAZILIAN PORTUGUESE"
nls_territory = "BRAZIL"
sga_target = 2256M
control_files = "/u01/oradata/WINT/control01 ctl"
control_files = "/u01/oradata/WINT/control02 ctl"
db_block_size = 8192
compatible = "12 2 0 1 0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 1000M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=WINTXDB)"
local_listener = "LISTENER_WINT"
_cursor_obsolete_threshold= 1024
audit_file_dest = "/u01/app/oracle/admin/WINT/adump"
audit_trail = "DB"
db_name = "WINT"
open_cursors = 300
pga_aggregate_target = 750M
diagnostic_dest = "/u01/app/oracle"
MÃOS A OBRA
Iremos subir o banco com base no arquivo pfile e recriar o spfile;
SYS @ WINT:>startup pfile='/u01/app/oracle/12 2 0 1/db_1/dbs/initWINT ora';
Inst▒ncia ORACLE iniciada
Total System Global Area 2365587456 bytes
Fixed Size 8623400 bytes
Variable Size 738200280 bytes
Database Buffers 1610612736 bytes
Redo Buffers 8151040 bytes
Banco de dados montado
Banco de dados aberto
SYS @ WINT:>create spfile from pfile;Para validarmos iremos baixar e subir a instância com o objetivo do mesmo subir com os parâmetros do Spfile.
SQL> shutdown immediate;
Database closed
Database dismounted
ORACLE instance shut down
1 SYS @ WINT:>startup;
2 Inst▒ncia ORACLE iniciada
3
4 Total System Global Area 2365587456 bytes
5 Fixed Size 8623400 bytes
6 Variable Size 738200280 bytes
7 Database Buffers 1610612736 bytes
8 Redo Buffers 8151040 bytes
9 Banco de dados montado
10 Banco de dados aberto
11 SYS @ WINT:>create spfile from pfile;
Feito isto fazemos um select para validar o status
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
WINT READ WRITE PRIMARY
O arquivo spfileWINT.ora retornará para o seu diretório de origem e o banco estará apto para prosseguir.
[oracle@WINT dbs]$ ll -ha
total 24K
drwxr-xr-x 2 oracle oinstall 98 Ago 1 20:46
drwxrwxr-x 76 oracle oinstall 4,0K Abr 3 14:40
-rw-rw---- 1 oracle oinstall 1,6K Ago 1 20:43 hc_WINT dat
-rw-r--r-- 1 oracle oinstall 914 Ago 1 20:36 initWINT ora
-rw-r----- 1 oracle oinstall 24 Fev 26 22:19 lkWINT
-rw-r----- 1 oracle oinstall 3,5K Fev 26 23:16 orapwWINT
-rw-r----- 1 oracle oinstall 2,5K Ago 1 20:46 spfileWINT ora
Atualizado em 27 de agosto de 2019.