What if the Oracle archive log is full? ____oracle

1. Log in with SYS user
Sqlplus Sys/pass@tt as Sysdba

2. Look at the location of Archiv log
Sql> Show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string

3. General value is empty, you can use archive log list; Check the archive directory and log sequence
sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 360
Next Log sequence to archive 360
Current Log Sequence 362

4. Check the use of Flash recovery area, you can see the archivelog has been very large, reached 96.62
Sql> select * from V$flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable Number_of_files
------------ ------------------ ------------------------- ---------------
Controlfile 13 0 1
Onlinelog 2.93 0 3
Archivelog 96.62 0 141
Backuppiece 0 0 0
Imagecopy 0 0 0
Flashbacklog 0 0 0

5. Calculate the space already occupied by Flash recovery area
sql> select sum (percent_space_used) *3/100 from V$flash_recovery_area_usage;

SUM (percent_space_used) *3/100
-----------------------------
2.9904

6. Find recovery directory, show parameter recover
Sql> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest String/u01/app/oracle/flash_recovery_area
Db_recovery_file_dest_size Big Integer 5G
Recovery_parallelism integer 0


7 The above results tell us that the archive location is using the default value, placed under Flash_recovery_area (Db_recovery_file_dest directory =/u01/app/oracle/flash_recovery_area)
[Root@sha3 10.2.0]# Echo $ORACLE _base
/u01/app/oracle

[Root@sha3 10.2.0]# CD $ORACLE _base/flash_recovery_area/tt/archivelog
Transfer or clear the corresponding archive log, delete some unused date directory files, pay attention to keep the last few files (for example, after 360)

---------------------------------------------------------------------------------------
Attention:
After the archive log is deleted, the control file must be maintained with Rman, otherwise the space display is still not released.
---------------------------------------------------------------------------------------

8. Rman Target Sys/pass
[Root@sha3 oracle]# Rman Target Sys/pass

Recovery manager:release 10.2.0.4.0-production on Tue 20 01:41:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to target Database:tt (dbid=4147983671)

9. Check for some useless archivelog
Rman> crosscheck Archivelog All;

10. Delete Expired Archive
rman> Delete expired Archivelog all;

Delete Archivelog until time ' sysdate-1 '; Delete all archivelog until the day before

11. Check again, found that the use rate is normal, has dropped to 23.03
Sql> select * from V$flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable Number_of_files
------------ ------------------ ------------------------- ---------------
Controlfile 13 0 1
Onlinelog 2.93 0 3
Archivelog 23.03 0 36
Backuppiece 0 0 0
Imagecopy 0 0 0
Flashbacklog 0 0 0

Other useful command:
----------------------------------
If archive log mode can not be normal startup, then first restore into noarchive log,startup success, and then shutdown;
Shutdown immediate;
startup Mount;
ALTER DATABASE Noarchivelog;
ALTER DATABASE open;
Shutdown immediate;

Startup again to archive log mode
Shutdown immediate;
startup Mount;
Show parameter log_archive_dest;
ALTER DATABASE Archivelog;
Archive log list;
ALTER DATABASE open;

If not, delete some archlog log
Sql> select group#,sequence# from V$log;

group# sequence#
---------- ----------
1 62
3 64
2 63

A log that is a log group one cannot be archived
Sql> ALTER DATABASE Clear unarchived logfile Group 1;
ALTER DATABASE open;

Finally, you can also specify the position of Arch Log, please follow the configuration below
Select name from V$datafile;
Alter system set log_archive_dest= '/opt/app/oracle/oradata/usagedb/arch ' scope=spfile

or modify the size
Sql> alter system set DB_RECOVERY_FILE_DEST_SIZE=3G Scope=both;