/u02 has still 20G space and I deleted all files in fast recovery but archivelog seemed frozen and numb and dead? I really hate this archivelog thing
You follow everything in the docs how to free it but it wont listen anymore once it stucked up.
Anyways this one did the trick:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6196547584 bytes
Fixed Size 2232952 bytes
Variable Size 3657436552 bytes
Database Buffers 2516582400 bytes
Redo Buffers 20295680 bytes
SQL> alter database noarchivelog;
SQL> alter database open;
Oracle provide lot of Documents to manage the database, there is some steps/process to follow to manage/maintain your database.
If you know what is the importance of Archive log file for the database before enabling the Archive log mode you have to plan
how you manage those Archive logs like backup or some deletion Policy, free Archive area.. etc.
If you read the Doc , i believe on that you can also find about the regular backup of Archive Area as per your Archive generation frequency. or deletion of Archive log with the help of some scheduled job . and Noarchivelog is not an option , like your did..
i have one suggestion before doing any thing please think Twice* or read before that.. not After that..
In case if your DB is Production. How will you continue to operate your DB in no archivelog mode.
The solution for above mentioned problem is either:
SQL> Shutdown abort;
SQL> Startup Mount;
1) Change your Archiving Destination by :
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='NEW_PATH_LOCATION'
2) Increase the size of Archiving Destination by first determining the size of the Archiving destination which intially you have:-
SQL> SELECT NAME,(space_limit/1048576),(space_used/1048576) from v$recovery_file_dest;
And after that increase the space according to above output:-
SQL> alter system set db_recovery_file_dest_size=more_size_than_getting_size_above scope=both;
SQL> Alter database open;
SQL> Shutdown immediate;
3) Zip the archive logs and move it to anyother destination but be careful to preserve this you require all this archivelogs during recovery.