The database is running in archive mode with automatic archiving is turned on . log Archive destination is in FRA (flash Recovery Area) .The DB_RECOVERY_FILE_DEST_SIZE was set to be 2G. Once when i startup the database it goes at mount stage and throw the following error .
ORA-1034 : Oracle not available
ORA-16014 : log 3 sequence# xx not archivedThen i check my alert logfile and find the space related issue in fra .i.e Automatic Archiving gets Stop when there is no space in disk. I fire the following command to resolve the issue .
SQL> startupORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1332892 bytes
Variable Size 281020772 bytes
Database Buffers 25165824 bytes
Redo Buffers 6340608 bytes
Database mounted.
ORA-1034 : Oracle not available
SQL>alter system set log_archive_dest_1='location=<< new location>>' ;
SQL> alter system archive log all to '<< new destination >>' ;( Here we may increase the fra size or change the archive destination , as i have changed the archive destination )
SQL> shut immediate ( or sometimes shut abort if hangs)
SQL> startup
Explanation :
Once the archive destination becomes full the location also becomes invalid. Normally Oracle does not do a recheck to see if space has been made available.
SQL> alter system archive log all to '<< new location >>' ;
The Above command gives Oracle a valid location for the archive logs. Even after using this the archive log destination parameter is still invalid and automatic achive does not work. We can also use this to allow to do a Shutdown immediate instead of Shutdown abort.
2.) Shutdown and restart of the database resets the archive log destination parameter to be valid . Do not forget to make disk space available before starting the database.
3.) Use the REOPEN attribute of the LOG_ARCHIVE_DEST_n parameter to determine whether and when ARCn attempts to re-archive to a failed destination following an error. REOPEN applies to all errors, not just OPEN errors. REOPEN=n sets the minimum number of seconds before ARCn should try to reopen a failed destination. The default value for n is 300 seconds. A value of 0 is the same as turning off the REOPEN option, in other words, ARCn will not attempt to archive after a failure. If we change the archive destination then there is no need of specifying repoen option .
ENJOY :-)
2 comments:
Dear Neeraj,
All your posts are really helpful. I have one question about this, Is the database down if the Archive location full?
Thanks,
Siva
Hi Shiva ,
Thanks for your comments .
If there is no space on archive location, then database get hung . It will not get down .
Have good time .
Enjoy :)
Post a Comment