Oracle Flash-back zone full

An old test AIX server, which no one has ever adjusted, has recently seen Oracle Flash-back full. Cleaned up.

Version:oracle 10GR2 for AIX

Phenomenon:

?

1 2 3 4 5 6 7

sql> ALTER DATABASE open; ALTER DATABASE open * ERROR at line 1:ora-16014:log 3 sequence# 157 not archived, no available destinations Ora-00312:o nline Log 3 thread 1: '/opt/oracle/software/oradata/orcl/redo03.log '

The error was reported because the flashback zone was full:

?

1 2 3 4 5 6 7 8

--Enlarging the flash-back space sql> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G Scope=both;   System altered.   sql> ALTER DATABASE open; Database altered.

Flash Back zone related information inquiry:

?

1 2 3 4 5 6 7 8

--View archive Mode sql> archive log list;   --Archive Space usage sql> select * from V$flash_recovery_area_usage; --The space occupied by the Flashback zone sql> select sum (percent_space_used) *3/100 from V$flash_recovery_area_usage;

Free space:

?

1 2 3 4 5 6 7 8 9 10 11 12-13

--delete backup crosscheck backupset; Delete noprompt expired backupset; Delete obsolete; Delete noprompt expired backup;   Delete noprompt expired copy; --Clear out the archive--clear out the expired archive crosscheck Archivelog all; Delete noprompt expired archivelog all; --not enough to force the deletion of the archive before the specified number of days delete noprompt Archivelog all completed before ' sysdate-7 ';

In addition to deleting files in the flash-back area or expanding the space, you can also do so by assigning the archive to a different directory.

?

1 2 3 4 5 6 7 8 9 10 11

Sql> alter system set log_archive_dest_1= ' Location=/nfsdisk/oraarc ' Scope=both;   System altered. sql> archive log list; Database Log mode Archive mode Automatic archival Enabled Archive destination/nfsdisk /oraarc oldest online log sequence 161 Next log sequence to archive 161 current log sequence 163

After the setting is complete, test:

Sql>alter system switch logfile;

But when I test, because careless, authorize not to pay attention to, cause execution when found to be stuck, check v$archive_dest,

The log_archive_dest_1 status is found to be error. This is caused by a directory permissions problem.

?

sql> col Destination format A20 sql> Col dest_name format A20 sql> 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  INACTIVE   rows selected.

1 2 3 4 5 6 7 8 9 a

Re-authorize the users and permissions that the directory belongs to:

?

1 2	# chown Oracle:oinstall/nfsdisk/oraarc # chmod-r 777/nfsdisk/oraarc

This is the time to archive successfully.

Mail:xcl_168@aliyun.com

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