Archive log of Oracle database full disk space solution

1. Database cannot be started

Sql> Startup
The ORACLE routine has started.

Total System Global area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database buffers 197132288 bytes
Redo buffers 7139328 bytes
Database loading complete.
ORA-16038: Log 2 Serial number 44 cannot be archived
ORA-19809: Exceeded the number of restore files limit
ORA-00312: Online log 2 thread 1:
' D:/oracle/product/10.2.0/oradata/orcl/redo02. LOG '

2. View $oracle_home/admin/sid/bdump/alert_sid.log Log

Thu Feb 19 09:45:33 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_660.trc:
Ora-19815:warning:db_recovery_file_dest_size of 2147483648 bytes is 99.95% used, and has 1129472 remaining bytes Le.

Thu Feb 19 09:45:33 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/udump/orcl_ora_4708.trc:
ORA-19815: Warning: db_recovery_file_dest_size byte (total 2147483648 bytes) has been used 99.95%, and 1129472 bytes are still available.

Thu Feb 19 09:45:33 2009
************************************************************************
You are have following choices to free up spaces from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
Thu Feb 19 09:45:33 2009
************************************************************************
Then consider changing RMAN archivelog deletion POLICY.
2. Back up files to tertiary device such as tape using RMAN
You are have following choices to free up spaces from flash recovery area:
BACKUP RECOVERY Area Command.
3. ADD disk spaces and increase db_recovery_file_dest_size parameter to
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
reflect the new space.
Then consider changing RMAN archivelog deletion POLICY.
4. Delete unnecessary the files using RMAN Delete command. If an operating
System command is used to delete files, then the use RMAN crosscheck and
2. Back up files to tertiary device such as tape using RMAN
DELETE expired commands.
************************************************************************
BACKUP RECOVERY Area Command.
Thu Feb 19 09:45:34 2009
Errors in file d:/oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_660.trc:
Ora-19809:limit exceeded for recovery files
Ora-19804:cannot Reclaim 44217856 bytes disk space from 2147483648 limit

From the information above know is the Flash recovery Area Flash recovery space in the default 2G space is full of archived logs, there is no space for the archive log.

3, Exit Sqlplus, enter Rman

Sql> exit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options disconnected

C:/Documents and Settings/mengzhaoliang>rman target/

Recovery manager: Release 10.2.0.1.0-production on Thursday February 19 09:57:48 2009

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

Connected to target database: ORCL (dbid=1193443472, not open)

4. View archive Log
rman> list Archivelog all;

Replacing the recovery directory with the target database control file

Archived List of log replicas
Keyword THRD Seq S short time name
------- ---- ------- - ---------- ----
1 1 A 1 July-November -08 d:/oracle/product/10.2.0/flash_recovery_area/o
Rcl/archivelog/2008_11_18/o1_mf_1_1_4l44z9cq_. ARC
1 2 A 1 August-November -08 d:/oracle/product/10.2.0/flash_recovery_area/o
Rcl/archivelog/2008_11_18/o1_mf_1_2_4l4w4o0h_. ARC
1 3 A 1 August-November -08 d:/oracle/product/10.2.0/flash_recovery_area/o

5, you can backup the archive log to other space, and then use Rman to delete the archive log;

rman> Delete Archivelog all;

Channel Freed: Ora_disk_1
Allocated channel: Ora_disk_1
Channel ora_disk_1:sid=151 Devtype=disk

Archived List of log replicas
Keyword THRD Seq S short time name
------- ---- ------- - ---------- ----
1 1 A 1 July-November -08 d:/oracle/product/10.2.0/flash_recovery_area/o
Rcl/archivelog/2008_11_18/o1_mf_1_1_4l44z9cq_. ARC
Are you sure you want to delete the above objects (enter YES or NO)? Yes
Archived logs that have been deleted
Archive log file name =d:/oracle/product/10.2.0/flash_recovery_area/orcl/archivelog/200
_11_18/o1_mf_1_1_4l44z9cq_. ARC Record id=40 timestamp =671101294

6, Exit Rman, enter Sqlplus, open the database

Rman> exit

Recovery manager complete.

C:/Documents and Settings/mengzhaoliang>sqlplus "/as sysdba"

Sql*plus:release 10.2.0.1.0-production on Thursday February 19 10:03:02 2009

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

Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options

sql> ALTER DATABASE open;

The database has changed.

The database is already open.