Full Oracle flash back (ORA-16014)

An old test AIX server was ignored. Recently, Oracle was full. Cleaned up.

Version: Oracle 10gR2 for AIX

Symptom:

SQL> alter database open;alter database open*ERROR at line 1:ORA-16014: log 3 sequence# 157 not archived, no available destinationsORA-00312: online log 3 thread 1:'/opt/oracle/software/oradata/orcl/redo03.log'

This error is reported because the flash back area is full:

-- Expand the flash back space SQL> alter system set db_recovery_file_dest_size = 3g scope = both; System altered. SQL> alter database open; Database altered.

Query the relevant information of the flash back area:

-- View the archive mode SQL> archive log list; -- archive space usage SQL> select * from v $ flash_recovery_area_usage; -- the space occupied by the flash back area SQL> select sum (percent_space_used) * 3/100 from v $ flash_recovery_area_usage;

Release space:

-- Delete backup crosscheck backupset; delete noprompt expired backupset; delete obsolete; delete noprompt expired backup; delete noprompt expired copy; -- clear archive -- clear expired archive crosscheck archivelog all; delete noprompt expired archivelog all; -- if it is not enough, force delete the archive delete noprompt archivelog all completed before 'sysdate-7' a specified number of days ago ';

In addition to deleting files in the flash back area or expanding space, you can also specify the archive to other directories.

SQL> alter system set log_archive_dest_1='location=/nfsdisk/oraarc' scope=both ;System altered.SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /nfsdisk/oraarcOldest online log sequence     161Next log sequence to archive   161Current log sequence           163

Test the settings:
SQL> alter system switch logfile;
But I did not pay attention to the carelessness during the test, and I found it stuck during the execution. I checked v $ archive_dest,
The status of LOG_ARCHIVE_DEST_1 is ERROR. This is caused by directory permission issues.

SQL> col DESTINATION format a20SQL> col dest_name format a20SQL> select dest_id,dest_name,STATUS,DESTINATION from v$archive_dest;   DEST_ID DEST_NAME            STATUS             DESTINATION---------- -------------------- ------------------ --------------------         1 LOG_ARCHIVE_DEST_1   ERROR              /nfsdisk/oraarc         2 LOG_ARCHIVE_DEST_2   INACTIVE         3 LOG_ARCHIVE_DEST_3   INACTIVE         4 LOG_ARCHIVE_DEST_4   INACTIVE         5 LOG_ARCHIVE_DEST_5   INACTIVE         6 LOG_ARCHIVE_DEST_6   INACTIVE         7 LOG_ARCHIVE_DEST_7   INACTIVE         8 LOG_ARCHIVE_DEST_8   INACTIVE         9 LOG_ARCHIVE_DEST_9   INACTIVE        10 LOG_ARCHIVE_DEST_10  INACTIVE10 rows selected.

Re-authorize the user and permission of the directory:

# chown oracle:oinstall /nfsdisk/oraarc# chmod -R 777 /nfsdisk/oraarc

In this case, the file can be archived successfully.

MAIL: xcl_168@aliyun.com

BLOG: http://blog.csdn.net/xcl168