ORA-17502: ksfdcre:4 Failed to create file +FRA

A weekend is about the start and got a call and one of our database is having issue. User is unable to connect and all their activity seems hung.

ISSUE : Instance Terminated and make Node2 inaccessible due to Oracle Sees FRA is Full.
But FRA is not Full ~ 2TB is available and db_recovery_file_dest_size=2.7TB. +FRA is ~18% Utilized only.

What i Did? Drop all existing restore point and restarted all node.

So i check the alert log on node 1 database.

 
*************************************
RVWR encountered an error when writing flashback database logs.
See error stack in alert log.
Since a Guarantee Restore Point was created,
RVWR cannot force to turn off flashback.
Therefore RVWR will terminate this instance.
*************************************
Fri Sep 16 05:17:28 2016
Errors in file /optware/orabase/grid/diag/rdbms/ckyclu3/CXXXLU31/trace/CXXXLU31_rvwr_41392.trc:
ORA-38701: Flashback database log 9 seq 5 thread 1: "+FRA"
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
USER (ospid: 41392): terminating the instance due to error 38701
Fri Sep 16 05:17:28 2016
System state dump requested by (instance=1, osid=41392 (RVWR)), summary=[abnormal instance termination].
System State dumped to trace file /optware/orabase/grid/diag/rdbms/ckyclu3/CXXXLU31/trace/CXXXLU31_diag_50122_20160916051728.trc
Fri Sep 16 05:17:30 2016
License high water mark = 81
Fri Sep 16 05:17:33 2016
Instance terminated by USER, pid = 41392
Fri Sep 16 05:17:34 2016
USER (ospid: 1359): terminating the instance
Fri Sep 16 05:17:34 2016
Instance terminated by USER, pid = 1359
Fri Sep 16 05:17:35 2016 

I check the trc file..

  
DDE rules only execution for: ORA 15041
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-38701: Flashback database log 9 seq 5 thread 1: "+FRA"
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
2016-09-16 06:38:09.378017*:KRA:kra.c@960:krarobd(): *************************************************************
2016-09-16 06:38:09.378017*:KRA:kra.c@962:krarobd(): WARNING: A file of type FLASH BACK may exist in
2016-09-16 06:38:09.378017*:KRA:kra.c@965:krarobd(): db_recovery_file_dest that is not known to the database.
2016-09-16 06:38:09.378017*:KRA:kra.c@968:krarobd(): Use the RMAN command CATALOG RECOVERY AREA to re-catalog
2016-09-16 06:38:09.378017*:KRA:kra.c@970:krarobd(): any such files. If files cannot be cataloged, then manually
2016-09-16 06:38:09.378017*:KRA:kra.c@972:krarobd(): delete them using OS command. This is most likely the
2016-09-16 06:38:09.378017*:KRA:kra.c@974:krarobd(): result of a crash during file creation.
2016-09-16 06:38:09.378017*:KRA:kra.c@976:krarobd(): *************************************************************
ORA-38701: Flashback database log 9 seq 5 thread 1: "+FRA"
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted

I check the other node is up. though its up, user unable to connect to it.

  
$ srvctl status database -d CXXXLU3
Instance CXXXLU31 is not running on node xxxldsgt31u
Instance CXXXLU32 is running on node xxxldsgt32u

When i check the configuration of my database is show we have enough space.

  
Disk Group                 Sector   Block   Allocation
Name                         Size    Size    Unit Size State       Type   Total Size (GB)    USED_GB Pct. Used
------------------------- ------- ------- ------------ ----------- ------ --------------- ---------- ---------
DATA                          512   4,096    1,048,576 MOUNTED     EXTERN          17,802 4851.68848     27.25
FRA                           512   4,096    4,194,304 MOUNTED     EXTERN           2,700 468.191406     17.34
GRID                          512   4,096    1,048,576 MOUNTED     NORMAL               3 .610351563     20.44

SQL

SQL> show parameter db_rec;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 2700G
db_recycle_cache_size                big integer 0
SQL>

Based on above info we enough space inside our ASM. as per logfile it says
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database. Mounted the database and execute using RMAN, CATALOG RECOVERY AREA

 SQL> startup mount;

ORACLE instance started.

Total System Global Area 3.2212E+10 bytes
Fixed Size                  7662000 bytes
Variable Size            1.7650E+10 bytes
Database Buffers         9126805504 bytes
Redo Buffers               59445248 bytes
In-Memory Area           5368709120 bytes
Database mounted.
SQL>

RMAN> CATALOG RECOVERY AREA
2> ;

using target database control file instead of recovery catalog
searching for all files in the recovery area
no files found to be unknown to the database

List of files in Recovery Area not managed by the database
==========================================================
File Name: +FRA/CXXXLU3/ONLINELOG/group_1.262.915359275
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_2.263.915358941
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_3.264.915373837
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_4.265.915373195
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_5.662.915358715
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_6.884.915358667
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_7.1141.915373461
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_8.989.915361203
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_9.401.915373899
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_10.993.915373919
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_11.997.915373933
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/CXXXLU3/ONLINELOG/group_12.625.915373965
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter

number of files not managed by recovery area is 12, totaling 24.00GB

RMAN>

Since RMAN says no files found to be unknown to the database, i even check inside the ASM but i dont find any files not own by oracle.

Checked for any restore point and found one.

  
bash-4.1$ sqlplus '/ as sysdba'
SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 16 08:40:40 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
CARD_DATA_SEP_15

SQL>

With the permission of application owner, i dropped the restore point and started the DB.

 
bash-4.1$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 16 08:40:40 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


SQL> drop restore point CARD_DATA_SEP_15;

Restore point dropped.

SQL> alter database flashback off;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
 

Now, before i start Node 1, stopped also Node2 since its not responding. then started one by one.

 
bash-4.1$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 16 08:42:16 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3.2212E+10 bytes
Fixed Size                  7662000 bytes
Variable Size            1.7650E+10 bytes
Database Buffers         9126805504 bytes
Redo Buffers               59445248 bytes
In-Memory Area           5368709120 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> alter database flashback on; < 

Now all Node is up.

  
Instance                Host                                Startup              Database                     Shutdown Active
Name / Number  Thread # Name                         Status Time                 Status   Archiver Logins?    Pending? State  Version
------------- --------- ---------------------------- ------ -------------------- -------- -------- ---------- -------- ------ -----------------
CXXXLU31 (1)          1 xxxldsgt31u                  OPEN   16-SEP-2016 09:00:19 ACTIVE   STARTED  ALLOWED    NO       NORMAL 12.1.0.2.0
CXXXLU32 (2)          2 xxxldsgt32u                  OPEN   16-SEP-2016 09:01:14 ACTIVE   STARTED  ALLOWED    NO       NORMAL 12.1.0.2.0

SQL>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s