Oracle: Resolve ORA-00257 – Connect AS SYSDBA only until resolved

Recently, one of the Oracle users complained that the database was unusable and received the error below

ORA-00257

Check #1: Are we physically out of space?

When the host is out of space on the drive/device where the FRA has been set, it can produce this error.

I looked at the space situation and it looked good (PowerShell command is from the dbatools module and allows you to get this server space info. from your own host)

PS C:\WINDOWS\system32> Get-DbaDiskSpace -com MyHostName | Format-Table

Server       Name Label     SizeInGB FreeInGB PercentFree BlockSize
------       ---- -----     -------- -------- ----------- ---------
MyHostName   C:\  OS         99.9    64.02       64.08      4096
MyHostName   D:\  DATA       500    89.78       17.96      4096

Check #2: The Alert Log

Next, I looked at the alert log and saw the problem and the solution was offered right there:

Thu Apr 18 20:15:03 2019
Errors in file D:\ORACLE\diag\rdbms\MyInstance\MyInstance\trace\MyInstance_arc7_4596.trc:
ORA-19809: limite dépassée pour les fichiers de récupération
ORA-19804: impossible de récupérer un espace disque de 45032960 octets de la limite 268435456000
ARC7: Error 19809 Creating archive log file to '\\MyHostName\ORAFRA$\MyInstance\ARCHIVELOG\2019_04_18\O1_MF_1_518897_%U_.ARC'
Thu Apr 18 20:15:03 2019
Errors in file D:\ORACLE\diag\rdbms\MyInstance\MyInstance\trace\MyInstance_arc8_4576.trc:
ORA-19815: AVERTISSEMENT : db_recovery_file_dest_size octets sur 268435456000 sont utilisés (100.00%) ; il reste 0 octets disponibles.
Thu Apr 18 20:15:03 2019
************************************************************************
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.
************************************************************************
Thu Apr 18 20:15:04 2019
Errors in file D:\ORACLE\diag\rdbms\MyInstance\MyInstance\trace\MyInstance_arc8_4576.trc:
ORA-19809: limite dépassée pour les fichiers de récupération
ORA-19804: impossible de récupérer un espace disque de 45032960 octets de la limite 268435456000
ARC8: Error 19809 Creating archive log file to '\\MyHostName\ORAFRA$\MyInstance\ARCHIVELOG\2019_04_18\O1_MF_1_518897_%U_.ARC'
Thu Apr 18 20:15:05 2019
Errors in file D:\ORACLE\diag\rdbms\MyInstance\MyInstance\trace\MyInstance_arc9_4572.trc:
ORA-19815: AVERTISSEMENT : db_recovery_file_dest_size octets sur 268435456000 sont utilisés (100.00%) ; il reste 0 octets disponibles.
Thu Apr 18 20:15:05 2019
************************************************************************
You have following choices to free up space from recovery area:

Sorry, the messages are in French. I did a Google Translate to English below

Thu Apr 18 20:15:03 2019
Errors in file D: \ ORACLE \ diag \ rdbms \ MyInstance \ MyInstance \ trace \ MyInstance_arc7_4596.trc:
ORA-19809: Exceeded limit for recovery files
ORA-19804: Unable to recover disk space of 45032960 bytes from limit 268435456000
ARC7: Error 19809 Creating archive log file to '\\ MyHostName \ ORAFRA $ \ MyInstance \ ARCHIVELOG \ 2019_04_18 \ O1_MF_1_518897_% U_.ARC'
Thu Apr 18 20:15:03 2019
Errors in file D: \ ORACLE \ diag \ rdbms \ MyInstance \ MyInstance \ trace \ MyInstance_arc8_4576.trc:
ORA-19815: WARNING: db_recovery_file_dest_size bytes on 268435456000 are used (100.00%); there are 0 bytes left.
Thu Apr 18 20:15:03 2019
************************************************** **********************
You have to choose from the following areas:
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
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   RMAN CROSSCHECK and system
   DELETE EXPIRED commands.
************************************************** **********************
Thu Apr 18 20:15:04 2019
Errors in file D: \ ORACLE \ diag \ rdbms \ MyInstance \ MyInstance \ trace \ MyInstance_arc8_4576.trc:
ORA-19809: Exceeded limit for recovery files
ORA-19804: Unable to recover disk space of 45032960 bytes from limit 268435456000
ARC8: Error 19809 Creating archive log file to '\\ MyHostName \ ORAFRA $ \ MyInstance \ ARCHIVELOG \ 2019_04_18 \ O1_MF_1_518897_% U_.ARC'
Thu Apr 18 20:15:05 2019
Errors in file D: \ ORACLE \ diag \ rdbms \ MyInstance \ MyInstance \ trace \ MyInstance_arc9_4572.trc:
ORA-19815: WARNING: db_recovery_file_dest_size bytes on 268435456000 are used (100.00%); there are 0 bytes left.
Thu Apr 18 20:15:05 2019
************************************************** **********************
You have to choose from the following areas:<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Check #3: Check the space allocated to DB_RECOVERY_FILE_DEST_SIZE

Basically it is saying that the “BACKUP RECOVERY AREA” is full. Although there is ample diskspace, the parameter DB_RECOVERY_FILE_DEST_SIZE determines how much space Oracle can use for database recovery related activities.

To check the space allocated to DB_RECOVERY_FILE_DEST_SIZE you could use one of the following options

SQL*Plus

SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 250G

SQL:

This also shows you the current usage as opposed to just the setting with SQL*Plus above

select name, floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used/ 1024 / 1024) "Used MB"
from v$recovery_file_dest;

As can be seen, I have it set to 250GB and most of it is used:

NAME Size MB Used MB
\\MyHostName\Orafra$ 256000 255991

Solutions:

The solution to the problem is offered in the alert log itself:

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.

I chose to increase the space for DB_RECOVERY_FILE_DEST_SIZE as we are going through other problems with Backup.


SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=300G SCOPE=BOTH;

System altered.

Alternate solutions

You could move the archive log files to a different drive/folder where there is space and recatalog them using the command below by pointing to the directory the archive logs were moved to and then crosscheck/delete expired (shown later).

catalog start with '/path/to/directory/';

If you know that certain archive log files were already backed up, you could remove them to make room as shown in this post:

https://oraclespin.com/2009/05/18/how-to-delete-archive-logs-already-archived-to-backup-device/

Run this in RMAN (and change to disk if you are not using sbt_tape)

--Get the list archive logs already backed up atleast once
list archivelog all backed up 1 times to sbt_tape;

--Then remove them if any were returned by the above
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
}

Alternatively, you could first backup the archive log files if you can and then remove them from the recovery area. If you are unable to backup for whatever reason, the archive log files can be physically moved to another location for safekeeping (and recatalog later when you move back) and update the RMAN catalog. You need to check the location pointed to by “db_recovery_file_dest”

SELECT * FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

..physically move the archive log files and update RMAN catalog to reflect the freed-up space in the recovery area.

crosscheck archivelog all;
delete expired archivelog all;

The problem is resolved for now!

Advertisements

4 thoughts on “Oracle: Resolve ORA-00257 – Connect AS SYSDBA only until resolved

  1. Dear Jana,
    I follow your posts and they are really wonderful.
    I see that you also write a post about Oracle, but not too often.
    Do you know any blog or something similar when can I follow specifically Oracle related posts for beginner and intermediate users? Somewhere I can find useful staff about Oracle and possible useful scripts and training.
    Thank you

    1. Thank you Mujc. I really appreciate the positive feedback. These days, I primarily work with SQL Server.

      Here are a few sites I like
      * https://jonathanlewis.wordpress.com/ – please see the Blog Roll section for other prominent Oracle bloggers
      * https://oracle-base.com/articles/sql/articles-sql#getting-started – This is an excellent site for reference
      * https://asktom.oracle.com/ – has the most authoritative information in question/answers form and has the most reliable information.

      Any time you see the OakTable.net logo on someone’s Oracle site, that is a symbol of quality.

      Sorry, I don’t have a bigger list. Perhaps I need to add links to my blog for good references.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s