ORA-00257: How to Handle archiver error. Connect internal only, until freed error

Post: original address http://blog.csdn.net/panys/article/details/3838846

Archive log is full
ORA-00257: How to Handle archiver error. Connect internal only, until freed error

1. Log On with the sys user
Sqlplus sys/pass @ tt as sysdba

2. Check the location of the 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. If the VALUE is null, you can use archive log list to check the archive directory andLog 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 usage of the flash recovery area. You can see that the archivelog is large enough to reach96.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 occupied by the 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 the 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

7The above results show that the default archive location is stored in the flash_recovery_area directory (db_recovery_file_dest ).=/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 files in the date directory that are not used, and keep the last few files (for example, files later than 360)

Bytes ---------------------------------------------------------------------------------------
Note:
After deleting archived logs, you must use the RMAN maintenance control file. Otherwise, the space is still not released.
Bytes ---------------------------------------------------------------------------------------

8. rman target sys/pass
[Root @ sha3 oracle] # rman target sys/pass

Recovery Manager: Release 10.2.0.4.0-Production on Tue Jan 20 01:41:26 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Connected to target database: tt (DBID = 4147983671)

9. Check for uselessArchivelog
RMAN> crosscheck archivelog all;

10. Delete expired Archives
RMAN> delete expired archivelog all;

Delete archivelog until time 'sysdate-1'; delete All archivelog

11. query again and find that the usage rate is normal and has been reduced23.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 usefulCommand:
----------------------------------
If startup is not normal in archive log mode, it is restored to noarchive log first. After startup is successful, shutdown;
Shutdown immediate;
Startup mount;
Alter database noarchivelog;
Alter database open;
Shutdown immediate;

Startup again in 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
SQL> select group #, sequence # from v $ log;

GROUP # SEQUENCE #
--------------------
1 62
3 64
2 63

Logs originally in log group 1 cannot be archived.
SQL> alter database clear unarchived logfile group 1;
Alter database open;

Finally, you can specify the location of the Arch Log as follows:
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;