escenario:
La herramienta de monitoreo de base de datos no genero ninguna alarma y de un momento a otro se detuvo dejando a toda la aplicación fuera de linea.
1) Revisión del alert log
Fri Jan 03 09:44:31 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc2_30903.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 197132288 bytes disk space from 429496729600 limit
ARC2: Error 19809 Creating archive log file to '+FRA'
Fri Jan 03 09:44:31 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc1_30901.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 429496729600 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Jan 03 09:44:31 2020
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Fri Jan 03 09:44:31 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc1_30901.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 210763776 bytes disk space from 429496729600 limit
Fri Jan 03 09:44:31 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc0_30899.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 429496729600 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Jan 03 09:44:31 2020
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Fri Jan 03 09:44:31 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc0_30899.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 190840832 bytes disk space from 429496729600 limit
ARC0: Error 19809 Creating archive log file to '+FRA'
Fri Jan 03 09:44:31 2020
Archived Log entry 839377 added for thread 1 sequence 199361 ID 0xd9194d3c dest 1:
Fri Jan 03 09:44:32 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc2_30903.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 429496729600 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Jan 03 09:44:32 2020
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Fri Jan 03 09:44:32 2020
Errors in file /u02/app/oracle/diag/rdbms/geos/GEOS/trace/GEOS_arc2_30903.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 197132288 bytes disk space from 429496729600 limit
ARC2: Error 19809 Creating archive log file to '+FRA'
2) Tratando de subir la DB
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 7759462400 bytes
Fixed Size 3009240 bytes
Variable Size 4672455976 bytes
Database Buffers 3078619136 bytes
Redo Buffers 5378048 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5508
Session ID: 1137 Serial number: 28254
3) Evidencias y análisis
lo anterior me identifico que el problema esta asociado a que el size asignado al parámetro "db_recovery_file_dest_size" se encontraba lleno.
4) Validaciones
* Subir la base de datos en estado "mount"
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 7759462400 bytes
Fixed Size 3009240 bytes
Variable Size 4672455976 bytes
Database Buffers 3078619136 bytes
Redo Buffers 5378048 bytes
Database mounted.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 199196
Next log sequence to archive 199196
Current log sequence 199207
* Validar los parametros de "db_recovery_file_dest" y "db_recovery_file_dest_size"
show parameter db_recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 300G
------------------------------------------------------------------------------------------------------------------------------
SQL> select name, floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used/ 1024 / 1024) "Used MB"
from v$recovery_file_dest; 2
NAME Size MB Used MB
--------------------------------------
+FRA 309600 309600
IDENTIFICAMOS QUE EL ESPACIO ASIGNADO AL PARÁMETRO DE "db_recovery_file_dest_size" SE "LLENO".
* validamos el espacio existente en el ASM sobre el +FRA
SQL> COL "% USED" FOR 99.0
SELECT GROUP_NUMBER, name, total_mb, TOTAL_MB-free_mb "USED MB",free_mb, 100- (free_mb/total_mb*100) "% USED", TYPE, STATE FROM v$asm_diskgroup;SQL>
GROUP_NUMBER NAME TOTAL_MB USED MB FREE_MB % USED TYPE STATE
------------ ------------------------------ ---------- ---------- ---------- ------ ------ -----------
2 FRA 511995 309600 202395 61.5 EXTERN CONNECTED
1 DATA 1023990 915791 108199 89.4 EXTERN CONNECTED
TENEMOS ESPACIO PARA AMPLIAR EL PARAMETRO
* ALTERAMOS EL VALOR DEL PARAMETRO
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=400G SCOPE=BOTH;
System altered.
* REINICIAMOS NUESTRA BASE DE DATOS Y LA SUBIMOS NORMAL (OPEN)
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 7759462400 bytes
Fixed Size 3009240 bytes
Variable Size 4412409128 bytes
Database Buffers 3338665984 bytes
Redo Buffers 5378048 bytes
Database mounted.
Database opened.
No hay comentarios:
Publicar un comentario