Recovery Database – Perca do Spfile e Pfile

27 ago

Recovery Database – Perca do Spfile e Pfile

Design sem nome (2)

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