Oracle ошибка архивации

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved solution- delete archivelog until sequence thread ; or alter system set lo

Hello Readers, You are here because you faced ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error ? Lets come to point ->

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error means archiver process is stuck because of various reasons due to which redo logs are not available for further transaction as database is in archive log mode and all redo logs requires archiving. And your database is in temporary still state.

Environment Details –

OS Version – Linux 7.8
DB Version – 19.8 (19)
Type – Test environment


SQL> select name,open_mode,database_role,log_mode from v$database;

NAME		     OPEN_MODE		  DATABASE_ROLE    LOG_MODE
-------------------- -------------------- ---------------- ----------
ORACLEDB	     READ WRITE 	  PRIMARY          ARCHIVELOG

SQL>

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARCHIVE,STATUS from v$log;

GROUP# SEQUENCE# BYTES     MEMBERS ARC STATUS   
------ --------- --------- ------- --- -------- 
     1	      25 209715200       2 NO  CURRENT	 
     2	      23 209715200       2 NO  INACTIVE
     3	      24 209715200       2 NO  INACTIVE

SQL>

There are various reason which cause this error-

  1. One of the common issue here is archive destination of your database is 100% full.
  2. The mount point/disk group assigned to archive destination or FRA is dismounted due to OS issue/Storage issue.
  3. If db_recovery_file_dest_size is set to small value.
  4. Human Error – Sometimes location doesn’t have permission or we set to location which doesn’t exists.

What Happens when ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error occurs

Lets us understand what end user see and understand there pain as well. So when a normal user try to connect to the database which is already in archiver error (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved ) state then they directory receive the error –

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved on the screen. 

SQL> conn dbsnmp/oracledbworld
ERROR:
ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.


Warning: You are no longer connected to ORACLE.
SQL>

You might be wondering what happens to the user which is already connected to the oracle database. In this case if they trying to do a DML, it will stuck and will not come out. For example here I am just trying to insert 30k record here again which is stuck and didn’t came out.

SQL> conn system/oracledbworld
connected.

SQL> create table oracledbworld2 as select * from oracledbworld;

Table created.

SQL> insert into oracledbworld select * from oracledbworld2;

29965 rows created.

SQL> /

How to Check archive log location

Either you can fire archive log list or check your log_archive_dest_n to see what location is assigned

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME	  OPEN_MODE    DATABASE_ROLE	LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------- ------------ ----------------
ORACLEDB  READ WRITE   PRIMARY		ARCHIVELOG   YES

SQL> 
SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence	       20
SQL>

When you see USE_DB_RECOVERY_FILE_DEST, that means you have enabled FRA location for your archive destination. So here you have to check for db_recover_file_dest to get the diskgroup name / location where Oracle is dumping the archive log files.

SQL> show parameter db_recover_file_dest

What are Different Ways to Understand if there is ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error

There are different ways to understand what is there issue. Usually end user doesn’t understand the ORA- code and they will rush to you with a Problem statement as -> DB is running slow or I am not able to login to the database.

Check the Alert Log First –

Always check alert log of the database to understand what is the problem here –

I have set the log_archive_dest_1 to a location which doesn’t exists to reproduce ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error. So alert log clearly suggest that

ORA-19504: failed to create file %s
ORA-27040: file create error, unable to create file
Linux-x86-64 Error: 13: Permission denied.

In middle of the alert – “ORACLE Instance oracledb, archival error, archiver continuing”

At 4th Last line you might seen the error – “All online logs need archiving”

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error

Check Space availability –

Once you rule out that there is no human error, and the archive log location exists Now you should check if mount point/ disk group has enough free space available, if it is available for writing and you can access it.

If your database is on ASM, then you can use following query – Check for free_mb/usable file mb and state column against your diskgroup name.

SQL> select name,state,free_mb,usable_file_mb,total_mb from v$asm_diskgroup;

If your database is on filesystem, then you can use following OS command –

For linux, sun solaris -

$df -kh 

For AIX -

$df -gt

If case you have FRA been used for archive destination then we have additional query to identify space available and how much is allocated to it.

SQL> select name, space_limit as Total_size ,space_used as Used,SPACE_RECLAIMABLE as reclaimable ,NUMBER_OF_FILES as "number" from  V$RECOVERY_FILE_DEST;

NAME			           TOTAL_SIZE USED       RECLAIMABLE number
---------------------------------- ---------- ---------- ----------- ----------
/u01/app/oracle/fast_recovery_area 10485760   872185344  68794880	  25
SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$recovery_area_usage;

FILE_TYPE	        USED       RECLAIMABLE number
----------------------- ---------- ----------- ----------
CONTROL FILE		100.94	   0		1
REDO LOG		6000	   0		3
ARCHIVED LOG		1900.78	   452.02	18
BACKUP PIECE		306.09	   204.06	3
IMAGE COPY		0	   0		0
FLASHBACK LOG		0	   0		0
FOREIGN ARCHIVED LOG	0	   0		0
AUXILIARY DATAFILE COPY 0	   0		0

8 rows selected.

You can look at sessions and event to understand what is happening in the database.

If you see there are 3 sessions, SID 237 is my session Rest two sessions are application session and when we look at the event of those two application session it clearly suggest session is waiting for log file switch (archiving needed).

select sid,serial#,event,sql_id from v$session where username is not null and status='ACTIVE';

SID    SERIAL# EVENT				        SQL_ID	    
--- ---------- ---------------------------------------- -------------
237	 59305 SQL*Net message from client	        7wcvjx08mf9r6
271	 46870 log file switch (archiving needed)       7zq6pjtwy552p
276	 18737 log file switch (archiving needed)       a5fasv0jz2mx2

How to Resolve ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error

It’s always better to know the environment before firing any command. Archive deletion can be destructive for DR setup or Goldengate Setup.

Solution 1

Check if you have DR database and it’s in sync based on that take a call of clearing the archive until sequence. You can use following command on RMAN prompt. 

delete archivelog until sequence <sequence> thread <thread no>;

Solution 2

You can change destination to a location which has enough space.

SQL>archive log list
SQL>show parameter log_archive_dest_1 
(or whichever you are using it, usually we use dest_1)

Say your diskgroup  +ARCH is full and +DATA has lot of space then you can fire

SQL> alter system set log_archive_dest_1='location=+DATA reopen';

You might be wondering why reopen. So since your archive location was full. There are chances if you clear the space on OS level and archiver process still remain stuck. Hence we gave reopen option here.

Solution 3

Other reason could be your db_recovery_file_dest_size is set to lower size. Sometimes we have FRA enabled for archivelog. And we have enough space available on the diskgroup/ filesystem level.

archive log list;
show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size=<greater size then current value please make note of filesystem/diskgroup freespace as well>
example -
Initially it was 20G
alter system set db_recovery_file_dest_size=100G sid='*';

Reference – archive Document 2014425.1

Источник

Содержание

  • 1 1. Закончилось место на дисковом томе, куда пишутся архивные логи
    • 1.1 Варианты минимизации ошибок:
  • 2 2. Закончилось место выделенное под FRA

ORA-00257: archiver error. Connect internal only, until freed. (Ошибка архиватора. Не могу подсоедениться пока занят ресурс)

Эта ошибка может быть вызвана несколькими причинами:

1. Закончилось место на дисковом томе, куда пишутся архивные логи

Для начала нужно понять, куда пишутся архивлоги. Для этого возьмем значения следующих параметров в представлении V$PARAMETER:

  • LOG_ARCHIVE_DEST (Устаревший, используется для БД редакции не Enterprise)
  • LOG_ARCHIVE_DEST_n
  • DB_RECOVERY_FILE_DEST. Этот параметр используется, если не установлено значение для любого параметра LOG_ARCHIVE_DEST_n, либо если для параметра LOG_ARCHIVE_DEST_1 установлено значение USE_DB_RECOVERY_FILE_DEST.
SELECT NAME, VALUE
  FROM V$PARAMETER
 WHERE     (   NAME LIKE 'db_recovery_file_dest'
            OR NAME LIKE 'log_archive_dest__'
            OR NAME LIKE 'log_archive_dest___'
            OR NAME = 'log_archive_dest'
            OR NAME = 'log_archive_duplex_dest')
       AND VALUE IS NOT NULL;

И проверим, по каким из путей нет дискового пространства. Для этого можно воспользоваться командой df -Pk. Далее либо чистим место, на тех томах, где пространство занято на 100 процентов, либо командой ALTER изменяем том на который пишутся архивлоги.

Варианты минимизации ошибок:

1. Если используется параметр LOG_ARCHIVE_DEST, то можно указать дополнительно параметры LOG_ARCHIVE_DUPLEX_DEST и LOG_ARCHIVE_MIN_SUCCEED_DEST.

  • LOG_ARCHIVE_DUPLEX_DEST — в этом параметре указываем каталог на дисковом томе, отличном от используемого в параметре LOG_ARCHIVE_DEST.
  • LOG_ARCHIVE_MIN_SUCCEED_DEST — значение этого параметра указываем равным 1. В этом случае, если том указанный в LOG_ARCHIVE_DEST будет заполнен на 100 процентов, но при этом архивлог будет записан в каталог указанный в LOG_ARCHIVE_DUPLEX_DEST, мы не получим ошибку.
ALTER SYSTEM SET LOG_ARCHIVE_DEST = '/u01/ARC/TST/' SCOPE=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_DUPLEX_DEST = '/u02/ARC/TST/' SCOPE=spfile;
ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST = 1 SCOPE=spfile;

И перезапускаем БД.

2. Если используются параметры LOG_ARCHIVE_DEST_n. В данном случае нам может помочь опция ALTERNATE этого параметра. В случае, если недоступен путь для архивирования лог файла, то архивирование идет по альтернативно указанному пути:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/ARC/TST/ MANDATORY MAX_FAILURE=1 REOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/u02/ARC/TST/ MANDATORY' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=standby_path1 ALTERNATE=LOG_ARCHIVE_DEST_4' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='SERVICE=standby_path2' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ALTERNATE SCOPE=both;

3. Если используется параметр DB_RECOVERY_FILE_DEST, желательно перейти на использование LOG_ARCHIVE_DEST_n.

Для просмотра текущих путей копирования архивлогов можно воспользоваться следующим представлением: V$ARCHIVE_DEST.

2. Закончилось место выделенное под FRA

Если архивлоги настроены на запись в DB_RECOVERY_FILE_DEST_SIZE, то можно так же словить сообщение ORA-00257, в alert.log при этом будет сообщение с ошибкой ORA-19815:

ARC0: Error 19809 Creating archive log file to '/u01/FRA/TST/archivelog/2013_06_28/o1_mf_1_5734_%u_.arc'
Errors in file /orasft/app/diag/rdbms/dwh/TST/trace/TST_arc0_8386.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 644245094400 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************

Тут же дают и варианты решения:

  1. Изменить политику удержания и удаления архивлогов rman.
  2. Сделать бекап архивлогов на ленту с удалением с диска.
  3. Добавить дисковое пространство во FRA командой: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 1024g  SCOPE=both;
  4. Удалить ненужные архивлоги командами RMAN:
orasft@DB:/home/orasft$ rman target /
-- Если архивлоги не нужны для бекапа:

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- Если архивлоги нужны для бекапа:

CROSSCHECK ARCHIVELOG ALL; 

DELETE EXPIRED ARCHIVELOG ALL;

При удалении архивлогов можем получить ошибку:

RMAN-06207: WARNING: 1235 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /u01/FRA/archivelog/2013_06_09/o1_mf_1_6468_95bhbb33_.arc
RMAN-06214: Archivelog      /u01/FRA/archivelog/2013_06_09/o1_mf_1_6469_95bhh7rb_.arc

Это означает, что rman не может найти файлы для удаления. Тут же нам предлагают воспользоваться командой CROSSCHECK перед удалением: CROSSCHECK ARCHIVELOG ALL;

Чтобы избежать возникновения ошибки переполнения места выделенного под FRA используем для архивлогов параметр LOG_ARCHIVE_DEST_n.

Проверить насколько заполнена FRA можно следующей командой:

SELECT SUM (PERCENT_SPACE_USED) AS "% Used FRA"
  FROM V$FLASH_RECOVERY_AREA_USAGE S;

Автор Андрей Котован

Источник

Содержание

  1. ORA-00257: Archiver error. Connect AS SYSDBA only until resolved
  2. What Cause ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error –
  3. What Happens when ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error occurs
  4. How to Check archive log location
  5. What are Different Ways to Understand if there is ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error
  6. Check the Alert Log First –
  7. Check Space availability –
  8. You can look at sessions and event to understand what is happening in the database.
  9. How to Resolve ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error
  10. Solution 1
  11. Solution 2
  12. Solution 3
  13. Ошибка ORA-00257: archiver error. Connect internal only, until freed
  14. 1. Закончилось место на дисковом томе, куда пишутся архивные логи
  15. Варианты минимизации ошибок:
  16. 2. Закончилось место выделенное под FRA
  17. ORA-00257: archiver error. Connect internal only, until freed.
  18. Comments
  19. ORA-00257: archiver error. Connect internal only,until freed.
  20. Comments

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved

Hello Readers, You are here because you faced ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error ? Lets come to point ->

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error means archiver process is stuck because of various reasons due to which redo logs are not available for further transaction as database is in archive log mode and all redo logs requires archiving. And your database is in temporary still state.

Environment Details –

OS Version – Linux 7.8
DB Version – 19.8 (19)
Type – Test environment

Table of Contents

What Cause ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error –

There are various reason which cause this error-

  1. One of the common issue here is archive destination of your database is 100% full.
  2. The mount point/disk group assigned to archive destination or FRA is dismounted due to OS issue/Storage issue.
  3. If db_recovery_file_dest_size is set to small value.
  4. Human Error – Sometimes location doesn’t have permission or we set to location which doesn’t exists.

What Happens when ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error occurs

Lets us understand what end user see and understand there pain as well. So when a normal user try to connect to the database which is already in archiver error (ORA-00257: Archiver error. Connect AS SYSDBA only until resolved ) state then they directory receive the error –

ORA-00257: Archiver error. Connect AS SYSDBA only until resolved on the screen.

You might be wondering what happens to the user which is already connected to the oracle database. In this case if they trying to do a DML, it will stuck and will not come out. For example here I am just trying to insert 30k record here again which is stuck and didn’t came out.

How to Check archive log location

Either you can fire archive log list or check your log_archive_dest_n to see what location is assigned

When you see USE_DB_RECOVERY_FILE_DEST, that means you have enabled FRA location for your archive destination. So here you have to check for db_recover_file_dest to get the diskgroup name / location where Oracle is dumping the archive log files.

What are Different Ways to Understand if there is ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error

There are different ways to understand what is there issue. Usually end user doesn’t understand the ORA- code and they will rush to you with a Problem statement as -> DB is running slow or I am not able to login to the database.

Check the Alert Log First –

Always check alert log of the database to understand what is the problem here –

I have set the log_archive_dest_1 to a location which doesn’t exists to reproduce ORA-00257: Archiver error. Connect AS SYSDBA only until resolved Error. So alert log clearly suggest that

ORA-19504: failed to create file %s
ORA-27040: file create error, unable to create file
Linux-x86-64 Error: 13: Permission denied.

In middle of the alert – “ORACLE Instance oracledb, archival error, archiver continuing”

At 4th Last line you might seen the error – “All online logs need archiving”

Check Space availability –

Once you rule out that there is no human error, and the archive log location exists Now you should check if mount point/ disk group has enough free space available, if it is available for writing and you can access it.

If your database is on ASM, then you can use following query – Check for free_mb/usable file mb and state column against your diskgroup name.

If your database is on filesystem, then you can use following OS command –

If case you have FRA been used for archive destination then we have additional query to identify space available and how much is allocated to it.

You can look at sessions and event to understand what is happening in the database.

If you see there are 3 sessions, SID 237 is my session Rest two sessions are application session and when we look at the event of those two application session it clearly suggest session is waiting for log file switch (archiving needed).

How to Resolve ORA-00257: Archiver error. Connect AS SYSDBA only until resolved error

It’s always better to know the environment before firing any command. Archive deletion can be destructive for DR setup or Goldengate Setup.

Solution 1

Check if you have DR database and it’s in sync based on that take a call of clearing the archive until sequence. You can use following command on RMAN prompt.

Solution 2

You can change destination to a location which has enough space.

You might be wondering why reopen. So since your archive location was full. There are chances if you clear the space on OS level and archiver process still remain stuck. Hence we gave reopen option here.

Solution 3

Other reason could be your db_recovery_file_dest_size is set to lower size. Sometimes we have FRA enabled for archivelog. And we have enough space available on the diskgroup/ filesystem level.

Источник

Ошибка ORA-00257: archiver error. Connect internal only, until freed

ORA-00257: archiver error. Connect internal only, until freed. (Ошибка архиватора. Не могу подсоедениться пока занят ресурс)

Эта ошибка может быть вызвана несколькими причинами:

1. Закончилось место на дисковом томе, куда пишутся архивные логи

Для начала нужно понять, куда пишутся архивлоги. Для этого возьмем значения следующих параметров в представлении V$PARAMETER:

  • LOG_ARCHIVE_DEST (Устаревший, используется для БД редакции не Enterprise)
  • LOG_ARCHIVE_DEST_n
  • DB_RECOVERY_FILE_DEST. Этот параметр используется, если не установлено значение для любого параметра LOG_ARCHIVE_DEST_n, либо если для параметра LOG_ARCHIVE_DEST_1 установлено значение USE_DB_RECOVERY_FILE_DEST.

И проверим, по каким из путей нет дискового пространства. Для этого можно воспользоваться командой df -Pk. Далее либо чистим место, на тех томах, где пространство занято на 100 процентов, либо командой ALTER изменяем том на который пишутся архивлоги.

Варианты минимизации ошибок:

1. Если используется параметр LOG_ARCHIVE_DEST, то можно указать дополнительно параметры LOG_ARCHIVE_DUPLEX_DEST и LOG_ARCHIVE_MIN_SUCCEED_DEST.

  • LOG_ARCHIVE_DUPLEX_DEST — в этом параметре указываем каталог на дисковом томе, отличном от используемого в параметре LOG_ARCHIVE_DEST.
  • LOG_ARCHIVE_MIN_SUCCEED_DEST — значение этого параметра указываем равным 1. В этом случае, если том указанный в LOG_ARCHIVE_DEST будет заполнен на 100 процентов, но при этом архивлог будет записан в каталог указанный в LOG_ARCHIVE_DUPLEX_DEST, мы не получим ошибку.

И перезапускаем БД.

2. Если используются параметры LOG_ARCHIVE_DEST_n. В данном случае нам может помочь опция ALTERNATE этого параметра. В случае, если недоступен путь для архивирования лог файла, то архивирование идет по альтернативно указанному пути:

3. Если используется параметр DB_RECOVERY_FILE_DEST, желательно перейти на использование LOG_ARCHIVE_DEST_n.

Для просмотра текущих путей копирования архивлогов можно воспользоваться следующим представлением: V$ARCHIVE_DEST.

2. Закончилось место выделенное под FRA

Если архивлоги настроены на запись в DB_RECOVERY_FILE_DEST_SIZE, то можно так же словить сообщение ORA-00257, в alert.log при этом будет сообщение с ошибкой ORA-19815:

Тут же дают и варианты решения:

  1. Изменить политику удержания и удаления архивлогов rman.
  2. Сделать бекап архивлогов на ленту с удалением с диска.
  3. Добавить дисковое пространство во FRA командой: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 1024g SCOPE=both;
  4. Удалить ненужные архивлоги командами RMAN:

При удалении архивлогов можем получить ошибку:

Это означает, что rman не может найти файлы для удаления. Тут же нам предлагают воспользоваться командой CROSSCHECK перед удалением: CROSSCHECK ARCHIVELOG ALL;

Чтобы избежать возникновения ошибки переполнения места выделенного под FRA используем для архивлогов параметр LOG_ARCHIVE_DEST_n.

Проверить насколько заполнена FRA можно следующей командой:

Источник

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

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

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.
ORA-00257 is a common error in Oracle 10g. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery are, or db_recovery_file_dest_size .

Now i am getting this error always. How can i rectify this permanently. Can anyone suggest what are the steps to do to solve this.

How do you rectify this «error» everytime you get it ? If you are clearing / deleting files in the db_recovery_file_dest location than you would know that you should either
a. Increaes db_recovery_file_dest_size (ensuring that the filesystem does have that much space, else increase the filesystem size as well !)
b. retain fewer files in this location (reduce retention or redundancy)

If you aren’t using a db_recovery_file_dest or the archivelogs are going elsewhere and you are manually purging archivelogs, you should look at increasing the size of the available filesystem.

If you are retaining multiple days archivelogs on disk, and running daily full backups, re-consider why you have multiple days archivelogs on disk.

If the problem occurs because of large batch jobs generating a large quantum of redo, either buy enough disk space OR rre-consider the jobs.

Источник

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

Please help me to fixed that error.

when i log in to the db, i only can log in as sys user.when log in as sys user, i can see and access the data in other schema except one table.
But i log in as schema user, it got
«ORA-00257: archiver error. Connect internal only,until freed.» error message.:-(
OS is Unix and Oracle is 9i.
What’s the possible problem? And how to solve it?

Message was edited by:
user533045

It looks like your archive destination is out of space. Check the available space on the filesystem where archive destination is, if it is full then try to get some more free space there by either deleting the old archives or adding more disks. Here is from oracle:

oerr ora 257
00257, 00000, «archiver error. Connect internal only, until freed.»
// *Cause: The archiver process received an error while trying to archive
// a redo log. If the problem is not resolved soon, the database
// will stop executing transactions. The most likely cause of this
// message is the destination device is out of space to store the
// redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving.

I am newbies to oracle.Can u guide me more detail how to check and solve.
Cos I don’t know how to check. 😛

Message was edited by:
user533045

Login to the database and issue:

show parameter log_archive

Check the path where the archive files are gettting stored. Then check the available free space using OS command:

There should be enough free space available on the filesystem where archivelogs are.

Post the output here.

SQL> show parameter log_archive

NAME TYPE VALUE
———————————— ———— ——————————
log_archive_dest string
log_archive_dest_1 string LOCATION=/usr/oracle/log/KCMSP
/archive
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string

NAME TYPE VALUE
———————————— ———— ——————————
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable

NAME TYPE VALUE
———————————— ———— ——————————
log_archive_duplex_dest string
log_archive_format string %t_%s.dbf
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
log_archive_trace integer 0

ktbux713:root:/usr/oracle #df -k
/home (/dev/vg00/lvol7 ) : 24424 total allocated Kb
19600 free allocated Kb
4824 used allocated Kb
19 % allocation used
/opt (/dev/vg00/lvol4 ) : 2090584 total allocated Kb
865440 free allocated Kb
1225144 used allocated Kb
58 % allocation used
/tmp (/dev/vg00/lvol5 ) : 407712 total allocated Kb
347872 free allocated Kb
59840 used allocated Kb
14 % allocation used
/usr/oracle/dbs (/dev/vg01/lvol1 ) : 7924877 total allocated Kb
4007856 free allocated Kb
3917021 used allocated Kb
49 % allocation used
/usr/oracle/index (/dev/vg01/lvol4 ) : 8885391 total allocated Kb
4960994 free allocated Kb
3924397 used allocated Kb
44 % allocation used
/usr/oracle/log (/dev/vg01/lvol2 ) : 10240000 total allocated Kb
0 free allocated Kb
10240000 used allocated Kb
100 % allocation used
/usr/oracle/oradata (/dev/vg01/lvol3 ) : 35577478 total allocated Kb
8347402 free allocated Kb
27230076 used allocated Kb
76 % allocation used
/usr/oracle/product (/dev/vg00/lvol9 ) : 11242342 total allocated Kb
338026 free allocated Kb
10904316 used allocated Kb
96 % allocation used
/usr (/dev/vg00/lvol6 ) : 2093120 total allocated Kb
534824 free allocated Kb
1558296 used allocated Kb
74 % allocation used
/var (/dev/vg00/lvol8 ) : 4699288 total allocated Kb
2079736 free allocated Kb
2619552 used allocated Kb
55 % allocation used
/stand (/dev/vg00/lvol1 ) : 269032 total allocated Kb
229872 free allocated Kb
39160 used allocated Kb
14 % allocation used
/ (/dev/vg00/lvol3 ) : 204416 total allocated Kb
53136 free allocated Kb
151280 used allocated Kb
74 % allocation used
Regards,

Источник

Источник

This post has been updated from the original content here.

The ORA-00257 error is a very common Oracle database error. The error is basically trying to tell us that we have run out of logical or physical space on our ASM diskgroup, mount, local disk, or db_recovery_file_dest location where our archivelogs are being stored.

When this issue crops up it usually prevents all connections to the database except for admin level access to allow for the DBA to fix the problem. Usually this is easily resolved in most cases and the below steps outline exactly how to fix the problem.

The first step is to determine where the archivelogs are being stored. Once we know the location we will be able to confirm if this is a logical or physical space shortage.

To find out where our archivelogs are being stored let’s log into SQLPLUS and run a few commands.

Note: Depending on configuration, there could be multiple destinations for archivelogs, each one will need to be checked if there are more than one destination set.

From the first command we run here, we can see that the archiver is indeed enabled and the archivelog destination for this particular database is being derived from another parameter called db_recovery_file_dest.

SQL> archive log list
Database log mode          	Archive Mode
Automatic archival         	Enabled
Archive destination        	USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 	8190
Next log sequence to archive   8192
Current log sequence       	8192

When we look at the db_recovery_file dest parameter we see that the archivelogs are being written to the +RECO diskgroup and the size of that space is 20TB. This means it can hold up to 20TB of space before filling up.

SQL> show parameter db_recovery_file

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest            	string  	+RECO
db_recovery_file_dest_size       	big integer 20T

In this particular database we can see that the value of the log_archive_dest parameter is empty because we are using the db_recovery_file_dest parameter to state where the logs are being stored.

SQL> show parameter log_archive_dest

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                 	string

If we were using a regular filesystem location on a local disk or mount it might look something like the below.

SQL> archive log list
Database log mode          	Archive Mode
Automatic archival         	Enabled
Archive destination        	/u02/oracle/db01/archivelogs/
Oldest online log sequence 	8190
Next log sequence to archive   8192
Current log sequence       	8192
SQL> show parameter db_recovery_file

NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest            	string  	/u02/oracle/db01/archivelogs/
db_recovery_file_dest_size       	big integer 20T

You will see at least these two parameters on Oracle 10g, 11g, 12c, or 19c. The first parameter ‘db_recovery_file_dest’ is where our archivelogs will be written to and the second parameter is how much logical space we’re allocating for not only those files, though also other files like backups, redo logs, controlfile snapshots, and a few other files that could be created here by default if we don’t specify a specific location.

SQL> show parameter log_archive_dest
NAME                             	TYPE    	VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                	 string  	/u02/oracle/db01/archivelogs/

So now that we know the location(s) of our archivelogs we can now check to see if it’s a logical or physical space issue. For physical this is easy enough as the diskgroup, disk, or mount point where the archivelogs are being stored would be at 100% capacity.

Physical – If it’s a physical issue, we can take one of the below steps to fix the issue

  1. Add more space to the mount where the logs are going.
  2. Take a backup of the logs via RMAN and have it delete input. (Note: If your backups are going to the same place your archivelogs are going, we will need to take a backup on a different mount point/drive.)
  3. Move the archivelog location to another mount/drive/location temporarily while you do one of the above.
  4. Delete the archivelogs if we don’t need the ability to restore the database. This could be useful for a dev instance that is refreshed nightly or something similar.

Anyone of the above three will clear the error and allow users to log into the database again almost instantly.

Logical – If it’s a logical issue we simply need to take one of the below actions.

  1. increase the db_recovery_file_dest_size to a larger size to allow for more archivelogs to be written to the archivelog location. (Note: Be sure to check the underlying disk/mount point before increasing this parameter to ensure there is proper space on the disk/mount.)
  2. As with the physical issue, simply take an RMAN backup of the logs and have it delete input to clear space.
  3. Change the db_recovery_file_dest or log_archive_dest to another location that has space
  4. Delete the archivelogs if they are not needed for recovery

I would recommend using rman for either the backup or delete methods as this will keep your catalog of backups up to date and clean. Also just to point out, that we if we need to ever recover this database we should be taking regular scheduled RMAN backups with a retention policy that also include archivelogs to help keep the archivelog location free for writing of more logs.

Please comment below if this helped you fix your ORA-00257: archiver error. Connect internal only, until freed issue. If you require more help please contact us to speak with a certified Oracle DBA support expert.

Источник

Возникает ошибка ORA-00257 (ошибка нехватки места). При поиске информации большинство из них говорят, что это вызвано слишком большим количеством архивированных журналов и заполнением всего оставшегося места на жестком диске. Это можно решить, просто удалив журналы. или увеличение места для хранения.

(1) Oralce 11g изменяет режим архива, каталог и размер журнала (Я принял этот подход）

Во-первых, переключите Oracle в режим архива.
   1. Закройте Oracle.
SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.

2. Запуск в состоянии монтирования
SQL> startup mount

ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             738199088 bytes
Database Buffers         1828716544 bytes
Redo Buffers                2945024 bytes
Database mounted.

  3. Перейти в режим архива.
SQL>   alter database archivelog
  Database altered.
Примечания: «archivelog» — это режим архивации; «noarchivelog» — неархивный режим.

  4. Измените базу данных на «открытую».
SQL>  alter database open

  5. Просмотр информации о режиме архивации.
SQL>   archvie log list
Подскажет режим архива, включен ли он, параметры
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     88
Next log sequence to archive   90
Current log sequence           90

Во-вторых, смените каталог архива
1. Просмотрите параметр db_recovery_file_dest

(1) «db_recovery_file_dest» используется для определения каталога для хранения архивных журналов. С помощью команды show parameter значение отображаемого параметра (по умолчанию) fast_recovery_area. Вот примеры:

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
———————————— ———— ——————————
db_recovery_file_dest                string      /usr/local/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 4182M
 

(2) Проверьте представление v $ recovery_file_dest, вы можете узнать ограничение пространства fast_recovery_area, используемое пространство, количество файлов и т. Д.

SQL> select * from v$recovery_file_dest;

NAME     SPACE_LIMIT      SPACE_USED     SPACE_RECLAIMABLE     NUMBER_OF_FILES
———— ———- —————— —————
D:oraclefast_recovery_area  4385144832    4346230272           2741248             102

   2. Измените каталог архивного журнала.
Синтаксис: alter system set parameter = value scope = spfile; (установить в каталог с большим пространством, я установил его здесь как home)

Пример: SQL>alter system set db_recovery_file_dest=’/home/oracle/flash_recovery_area’ scope=spfile;

                System altered.

В-третьих, измените размер архива журнала

   1. Просмотрите значение параметра’db_recovery_file_dest_size ‘
SQL> show parameter db_recov

NAME                                 TYPE        VALUE
———————————— ———— ——————————
db_recovery_file_dest                string      /usr/local/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 4182M

   2. Измените размер значения параметра’db_recovery_file_dest_size.

SQL> alter system set db_recovery_file_dest_size=41820M scope=spfile;

System altered.

   3. Закройте базу данных, перезапустите базу данных.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup open;
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             738199088 bytes
Database Buffers         1828716544 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.

   4. Еще раз проверьте значение параметра’db_recovery_file_dest_size.
SQL> show parameter db_reco
NAME                                 TYPE        VALUE
———————————— ———— ——————————
db_recovery_file_dest                string      /home/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 41820M

После перезапуска в это время все в порядке.

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

(Следующее только для справки)

(2) Опытные решения Baidu: следующие（Я принял этот подход）

решать:

1、

Сервер входа SecureCRT, переключение пользователя oracle, подключение к oracle

[[email protected]~]# su — oracle

[[email protected]~]$ sqlplus /nolog

SQL> connect /as sysdba

2、

Проверьте использование области восстановления флэш-памяти, вы увидите, что архив архива уже очень большой, достигнув 99,94

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

3、

Рассчитайте пространство, занимаемое областью восстановления флеш-памяти

SQL> select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;

Вышеупомянутая проверка, вы можете напрямую установить размер файла архива следующим образом:

4、

Измените пространство FLASH_RECOVERY_AREA на 6 ГБ, убедитесь, что на диске достаточно места, прежде чем изменять

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=8g;

5、

Теперь давайте очистим архивный журнал archivelog, производственная среда рекомендует резервное копирование.

Расположение каталога журнала запросов

show parameter recover;

Удалить архивный журнал, USERDB — это имя экземпляра базы данных.

cd /u01/app/oracle/flash_recovery_area/USERDB/archivelog

Используйте учетную запись root для удаления файлов в этом каталоге или резервного копирования в других местах

6、

Используйте операцию rman, содержимого слишком много, снимается только часть снимка экрана

[[email protected] archivelog]$ rman

RMAN> connect target sys/sys_passwd

crosscheck backup; 

delete obsolete; 

delete expired backup; 

crosscheck archivelog all; 

delete expired archivelog all; 

// На этом шаге появится запрос, введите ДА и нажмите Enter

host; // Выходим из rman

 7、

Подтвердите успешность операции

#  sqlplus /nolog

SQL>  connect /as sysdba

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

8. После завершения удаления подключитесь к базе данных.

Метод открытия и закрытия архива ORACLE

sql> archive log list; # Проверяем, архивный ли это метод  
sql> alter system set log_archive_start=false scope=spfile; # Отключить автоматическое архивирование  
sql> shutdown immediate;  
sql>  startup mount; # Открыть контрольный файл, не открывать файл данных  
sql> alter database  noarchivelog; # Перевести базу данных в неархивный режим  
sql> alter database open; # Открываем файл данных  
sql> archive log  list; #View в это время не в архивном режиме

bash: sqlplus: команда не найдена решение

su — oracle

vi ~/.bash_profile

Добавьте в конце следующее

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/JRE

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/JRE/lib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export LIBPATH=${CLASSPATH}:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib

export ORACLE_OWNER=oracle

export SPFILE_PATH=$ORACLE_HOME/dbs

export ORA_NLS10=$ORACLE_HOME/nls/data

Внесите настройки в силу

source /home/oracle/.bash_profile

Обратитесь к моему другому журналу:

http://www.cnblogs.com/gmq-sh/p/5773374.html

Немедленное завершение работы Oracle не может закрыть решение для базы данных

При использовании команды немедленного выключения для выключения базы данных на тестовом сервере, база данных не может быть выключена в течение длительного времени, как показано ниже.

   1: [[email protected] admin]$ sqlplus / as sysdba
   2:  
   3: SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 21 13:55:13 2013
   4:  
   5: Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
   6:  
   7:  
   8: Connected to:
   9: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
  10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
  11:  
  12: SQL> shutdown immediate;

В другом сеансе проверьте результат вывода журнала аварийных сигналов, как показано ниже: Информация журнала аварийных сигналов находится в выходных данных.Active processes prevent shutdown operationПосле этого подсказки больше не выводятся.

   1: [[email protected] bdump]$ tail -f 20 alert_epps.log 
   2:  
   3: Thu Nov 21 13:55:23 2013
   4: Starting background process EMN0
   5: Shutting down instance: further logons disabled
   6: EMN0 started with pid=59, OS id=19244
   7: Thu Nov 21 13:55:24 2013
   8: Errors in file /u01/app/oracle/admin/epps/udump/epps_ora_19242.trc:
   9: ORA-04098: trigger 'ADMIN.PIND_ON_SHUT' is invalid and failed re-validation
  10: Thu Nov 21 13:55:24 2013
  11: Shutting down instance (immediate)
  12: License high water mark = 10
  13: All dispatchers and shared servers shutdown
  14: Thu Nov 21 14:00:29 2013
  15: SHUTDOWN: Active processes prevent shutdown operation

О SHUTDWON НЕМЕДЛЕННО закройте базу данных:

Метод немедленного закрытия (НЕМЕДЛЕННО)
     Метод немедленного завершения работы позволяет завершить работу базы данных в кратчайшие сроки.При немедленном завершении работы базы данных ORACLE выполнит следующие операции:
* Запретить любому пользователю установить новое соединение и в то же время запретить текущему подключенному пользователю запускать какие-либо новые вещи. К
* Все, что не было отправлено, будет возвращено. К
* ORACLE больше не ожидает активного отключения пользователей, а напрямую закрывает и удаляет базу данных, а также завершает работу экземпляра.

способ 1:

В это время вы можете убить процесс ORACLE в системе (LOCAL = NO) с помощью следующей команды

[[email protected] ~]$ ps -ef|grep ora|grep -v grep|grep -v ora_|grep LOCAL=NO|awk ‘{print $2}’ 
[[email protected] ~]$ ps -ef|grep ora|grep -v grep|grep -v ora_|grep LOCAL=NO|awk ‘{print $2}’|xargs kill

В это время база данных может быть отключена плавно.

Способ 2:

Используйте CTRL + C, чтобы отменить операцию, а затем используйте команду shutdown abort, чтобы завершить работу базы данных. Конечно, в производственной среде команду shutdown abort следует использовать с осторожностью, она часто используется, когда нет альтернативы. Потому что
После выполнения прерывания выключения все выполняющиеся операторы SQL будут немедленно завершены. Все незавершенные транзакции не будут отменены. Oracle не ждет, пока пользователи, подключенные в данный момент к базе данных, выйдут из системы. Восстановление экземпляра требуется при следующем запуске базы данных, поэтому следующий запуск может занять больше времени, чем обычно.

Способ 3: Более распространенный метод в Интернете.

1: Сначала остановите службу приложения. К
2: Во-вторых, остановите службу прослушивания (слушатель)
3: Через некоторое время запустите сценарий, написанный вами, чтобы проверить, существует ли какой-либо пользовательский процесс. Если да, KILL.
4 ：shutdown immediate;

Конечно, иногда реальная ситуация иная: например, на шаге 1 администратор баз данных не имеет полномочий сервера приложений. Невозможно остановить службу приложения. Конечно, это не мешает выполнению следующих шагов. К

Закройте запрос архива: ORA-38774: невозможно отключить восстановление носителя — включена база данных flashback

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 — 64bit Production

База данных переходит в состояние MOUNT:

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38774: cannot disable media recovery — flashback database is enabled

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            L:oradatatestarch11

Oldest online log sequence     227

Current log sequence           231

*******VICTORY LOVES PREPARATION*******

Источник

ORA-00257 is one of the commonest error in Oracle DBA life. He/She often has to deal with it.Oracle Database almost got frozen because of it and all transaction get stopped .Let us see how to deal with ORA-00257 error

ORA-00257

Error Message

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

 As per The oerr ORA-00257 error means

ORA-00257: archiver error. Connect internal only, until freed. Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter archive_log_dest is set up properly for archiving.

Where do you see this error message

1) You will see below type of error message pertaining in alert log

Following is some if the information from the alert log:

Errors in file u01oracleproduct11.2.0diagrdbmsTESTtesttracetest_arc1_1010.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.Wed jan 21 02:44:02 2016
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARC1: Failed to archive thread 1 sequence 1459 (1809)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed jan 21 02:44:02 2016
Errors in file u01oracleproduct11.2.0diagrdbmsTESTtesttracetest_arc1_1010.trc
ORA-16038: log 3 sequence# 1459 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'u01oracleoradataTESTredo03.LOG'

2) If you try to login with non sysdba user, you will the below error message

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
SQL

3)  If you try to login with sysdba user and check the wait event for the session , you will find session waiting log archive switch event
Why ORA-00257 error occurs

This error happens as the target location for archive log is either full or not available. The Oracle ARCH background process is responsible for taking the redo logs from the online redo log file system and writing them to the flat file is not able to write to the filesystem

How to resolve ORA-00257 errors

1) First of all we should find the archive log destination for the database

sqlplus / as sysdba
SQL> archive log list;

you can also find archive destinations by either USE_DB_RECOVERY_FILE_DEST

SQL> show parameter db_recovery_file_dest;

Also get the size of the db_recovery_file_dest

show parameter db_recovery_file_dest_size;

2) The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
+FLASH  21474836480 21474836480 212428800 200

3) We can have many solutions in these situation

a) Increase the size of db_recovery_file_dest if you have space available in Oracle ASM or filesystem whatever you are using

alter system set db_recovery_file_dest_size=40g;

b) We can delete the archive log which have been already backed up

rman target /
delete archivelog UNTIL TIME = 'SYSDATE-1' backed up 1 times to sbt_tape;rman target /
RMAN>delete archivelog
until time 'SYSDATE-3';
or,
RMAN>delete archivelog all;
or
delete archivelog UNTIL TIME = 'SYSDATE-1.5' backed up 1 times to sbt_tape;

c) If you have not taken the backup,then it is advise  to take backup and then delete the archive log files

rman target /run {
allocate channel d1 type disk;
backup archivelog all delete input format '/u11/ora/arch_%d_%u_%s';
release channel d1;
}

d) Sometimes old backup piece ,flashback logs may be occupying space in the db_recovery_file_dest, you check the content of db_recovery_file_dest

SQL>Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;FILE_TYPE USED RECLAIMABLE number
------------ ---------- ----------- ----------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 4.77 0 2
BACKUPPIECE 56.80 0 10
IMAGECOPY 0 0 0
FLASHBACKLOG 11.68 11.49 63

Sometimes old guaranteed restore point might be present, Dropping will release the space.

Flashback Database explained and limitation

how to Flashback when we have dataguard

Top 10 Useful Queries for Flashback Database

e) If we dont need archive log , then simply deleting will also serve the purpose

rman target /
DELETE NOPROMPT ARCHIVELOG ALL;

Other useful command in this case

LIST COPY   OF ARCHIVELOG ALL         COMPLETED AFTER 'SYSDATE-1';
DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-4';
LIST COPY   OF ARCHIVELOG UNTIL TIME = 'SYSDATE-18';
BACKUP ARCHIVELOG COMPLETION TIME BETWEEN  'SYSDATE-28' AND 'SYSDATE-7';

Important Note

Please dont delete archive log file manually from the filesystem, it will not update control file and it will not clear the issue. Then you have do crosscheck all in rman and delete obsolete and expired

f)  We can specify alternate path for archiving

Archiving is automatically performed to the alternate dest2

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oradata/flash_recovery_area'
db_recovery_file_dest_size=200G

4) Once space is available in db_recovery_file_dest, Please check the system by doing alter system switch logfile

alter system switch logfile;
system alerted

Also try connecting with non sysdba user to make sure ,issue is resolved

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
connected
SQL>

It is advisable to take regular backup of archive log and delete them. We should put a monitoring script to keep a check on the flash recovery area space.

Related Articles
ORA-00936 missing expression
ORA-01017: invalid username/password; logon denied
ORA-29913 with external tables
ora-00904 invalid identifier
ORA-00001 unique constraint violated
ORA-01111 in MRP
How to find archive log history
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

Ezoic

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Oracle ошибка ora 00936
  • Oracle ошибка 28000
  • Oracle как изменить пароль пользователя
  • Oracle vm virtualbox ошибка при установке
  • Oracle vm virtualbox setup wizard ended prematurely because of an error

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии