How to recover from an Oracle DB archivelog full error

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

How to recover from an Oracle DB if the disk/file system for archive logs gets full

Note: if DB is hung at shutdown i.e. shu immediate, then force a shutdown, like below

SQL> shutdown abort;
SQL> startup nomount;
SQL> alter database mount
SQL> exit

Get size used for if needed

SQL> col name format A50
SQL> col space_limit format A10
SQL> col space_used format A10
select  name,  (space_limit/1024/1024) ||'MB' as Space_Limit,
(space_used/1024/1024)||'MB' as Space_Used from  v$recovery_file_dest;

Remove Archive logs with rm in ASMCMD

asmcmd -p>
cd DATA/OEM13/ARCHIVELOG/2015_12_31
ASMCMD [+DATA/OEM13/ARCHIVELOG/2015_12_31] > rm thread_1*
ASMCMD [+DATA/OEM13/ARCHIVELOG/2015_12_31] > exit

# Update rman with log info
rman target /
crosscheck archivelog all
delete expired archivelog all

Open the DB for usage

SQL> alter database open

Change archivelog max size

SQL> alter system set db_recovery_file_dest_size=10g scope=both;

To delete all all archivelog’s

RMAN>DELETE ARCHIVELOG ALL;
or
RMAN>delete noprompt expired archivelog all;

To disable archivelog mode

SQL> startup nomount;
SQL> alter database mount;
SQL> alter database noarchivelog;
SQL> alter database open;

# Verify
SQL> select NAME,LOG_MODE from v$database;

Leave a Reply

avatar
3000
  Subscribe  
Notify of