How to restart an Oracle database when the Flash Recovery Area (FRA) is full and it is not possible to log in to the database - WKB55931

OBJECTIVE or GOAL

Restart an Oracle database when the FRA is full and the database is down. (Restarting the database in the "mount" state is not possible, and logging in to the database with SQL*Plus is not possible.)

ENVIRONMENT

• All Waters products that use an Oracle database

PROCEDURE

1. Modify the database init parameter to give it twice the size of the flash recovery area.
   1. Look in the alert_sdms.log file for the error message.
      • Rename the alert log if it is too large to open in Notepad
      • Reproduce the startup error so that Oracle recreates the alertlog
   2. The error message notes that the flash recovery area is full. It gives the path for that area, the parameter name, and the value.
   3. Look for the init.ora file on the database. Normally it is not present, but you can create it with "create pfile from spfile" at the SQL*Plus prompt, or from a backup copy in Drive:\oracle\product\11.2.0\admin\sdms\pfile
   4. Copy that file. Edit the copy so that db_recovery_file_dest_size is double the current value
      • Make sure that the db_recovery_file_dest parameter in the pfile is the same as in the active flash_recovery_area! The pfile may have an old value for that parameter.
      • Make sure that the control_files parameter in init.ora matches that of the spfile.  Open the spfile in notepad to get the current value of control_files.  Copy this value into the init.ora and match the syntax of the init file.  This way, when the database starts, it will use the current control files, rather than paths to obsolete or nonexistent control files
   5. Rename the file to initServiceName.ora, where ServiceName is the service name of the affected database instance. These are the default service names for Waters products:
      • NuGenesis LMS and SDMS: SDMS
      • NuGenesis Stability: SLIM
      • Empower 3: WAT12
   6. Copy the initServiceName.ora file to:
      • NuGenesis 8: Drive:\oracle\product\11.2.0\SDMS\database
      • NuGenesis 9: Drive:\oracle\product\12.2.0\Oracle12cR2\database
      • Empower 3: Drive:\Empower\Oracle12c\database
   7. Rename the existing SP*.ORA file to .OLD; ORacle will not use the init file if the spfile is present
   8. Restart the Oracle Service for the database instance
   9. At the "idle>" prompt in sqlplus, run the command (or specify the full path to initServiceName.ora):
      • startup pfile=initServiceName.ora
      • The database will use the new parameters to start, and it should now open, given that the recovery-area limit was doubled
      • Use the command to make the changed parameter permanent:
        • create spfile from pfile
      • Shut down and start up the database to ensure that it will start with the spfile
2. Delete old backupsets from the FRA.
3. Log in to RMAN and issue the "crosscheck archivelog all" command.
4. Ensure that the standard NuGenesis or Empower database backup procedures are in place.

ADDITIONAL INFORMATION

If it is not possible to log in to the database—for example, if the database instance was shut down due to the FRA error and it did not register with the TNS listener—then step 1.7 above will not be possible.  In this case, first confirm which instances have registered with TNS with the following command:

• lsnrctl status

If the instance is not in that list:

1. Delete the file spfileServiceName.ora from the \database folder.
2. Copy the initServiceName.ora file into the \database folder.
3. Restart the instance.

In the absence of an spfile, Oracle uses the init file for the instance on startup, without the need to specify anything on the command line. The instance should register with TNS and be available through tnsping and sqlplus. Log in to the instance as SYSDBA using sqlplus, and proceed with the rest of the steps in the procedure above.