Ora-00257:archiver error. Connect internal only, until freed error handling method

Ora-00257:archiver error. Connect internal only, until freed error handling method

Background: Recently always in the Oracle database, a few days ago to a company's Oracle database to do a real-time synchronization function, synchronization must open the Archive log function, due to the large volume of production data, less than 1 days to the Oracle log space is full. The following error is reported: Ora-00257:archiver error. Connect internal only, until freed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/72/DC/wKiom1XursDhekvsAATWJ_VoGWM204.jpg "title=" QQ picture 20150908174555.png "alt=" Wkiom1xursdhekvsaatwj_vogwm204.jpg "/>

1. Log in with SYS user
Sqlplus Sys/password as SYSDBA;

2. Look at Archiv log with those logs.
Sql> Show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string

3. Log sequence can be checked with the archive log list
sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 1892
Next Log sequence to archive 1894
Current log Sequence 1894

4. Check the use of Flash recovery area, you can see Archivelog is 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 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.

View the maximum values for the log directory and log space settings:

Sql> Show parameter db_recovery_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest string D:\app\Administrator\flash_rec
Overy_area
Db_recovery_file_dest_size Big Integer 3912M

7 The above results tell us that the archive location is in the default value, placed under Flash_recovery_area (db_recovery_file_dest directory =d:\app\administrator\flash_ Recovery_area

Use Rman to transfer or clear the corresponding archive log, delete some unused date directory files, keep in mind the last few files

---------------------------------------------------------------------------------------
Attention:
If you delete the archive log directly, you must maintain the control file with Rman or the space display is still not released.
---------------------------------------------------------------------------------------

8. Rman Target Sys/pass
[Email protected] 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, Oracle. All rights reserved.

Connected to target Database:tt (dbid=4147983671)

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

10. Delete an expired archive
rman> Delete expired Archivelog all;

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

11. Re-query, 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

======================================================

The following commands are used to verify the validity of the archive log, list invalid archive logs, and how to clear the archive log, listing several commonly used:

Crosscheck Archivelog All; ---> Verify the availability of logs

List expired Archivelog all; ---> List all archived logs that fail

Delete Archivelog until sequence 16; ---> Delete all archived logs prior to log sequence 16 and 16

Delete Archivelog all completed before ' sysdate-7 '; ---> Delete archived logs from 7 days before the system time, the active archive log for the Flashback zone will not be deleted

Delete Archivelog all completed before ' sysdate-1 '; ---> Ibid., 1 days ago

Delete archivelog from time ' sysdate-1 '; ---> Note This command to delete the archive log from within 1 days of the system time

Delete noprompt Archivelog all completed before ' sysdate '; ---> This command clears all archive logs

Delete NoPrompt Archivelog all; ---> Previous command

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Other useful command:
----------------------------------
If the archive log mode is not normal startup, then restore to noarchive log,startup success, then shutdown;
Shutdown immediate;
startup Mount;
ALTER DATABASE Noarchivelog;
ALTER DATABASE open;
Shutdown immediate;

Startup again with 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

It turns out that a log of log Group One cannot be archived.
Sql> ALTER DATABASE Clear unarchived logfile Group 1;
ALTER DATABASE open;

Finally, you can also specify the location of Arch Log, please follow the configuration below
Select name from V$datafile;
Alter system set log_archive_dest= ' D:\app\Administrator\backup ' scope=spfile

or modify the size
Sql> alter system set db_recovery_file_dest_size=100g Scope=both;

Note:

It should be scope=both and scope=spfile.
Oracle has an object called SPFile, which is a dynamic parameter file that sets the parameters of Oracle. The so-called dynamic is that you can change the database parameters without shutting down the database, and record them in SPFile. There are 4 scope options when changing the parameters. Scope is range
+ + Scope=spfile only changes the spfile inside the record, does not change the memory, namely does not take effect immediately, but waits for the next database to start to take effect. There are some parameters that only allow this method to change
+ + Scope=memory Only changes the memory, does not change the spfile. Which means that the next launch will expire.
+ + Scope=both memory and SPFile are changed
+ + does not specify the scope parameter, equivalent to Scope=both.

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://pizibaidu.blog.51cto.com/1361909/1692856

Ora-00257:archiver error. Connect internal only, until freed error handling method