Меню

Ora 00257 ошибка архиватора подключите as sysdba только до разрешения проблемы

Содержание

  • 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;

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

Источник

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. 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,

Источник

Источник

When I try connecting to my database, I get the following error.

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

Till yesterday, the database was pretty functional.

Any workaround?

skaffman's user avatar

skaffman

396k96 gold badges814 silver badges768 bronze badges

asked Apr 26, 2011 at 4:27

MAlex's user avatar

3

In SQL*Plus, can you

SQL> show parameter log_archive
  • If LOG_ARCHIVE_START is FALSE,
    you’ll want to set it to TRUE.
  • If
    LOG_ARCHIVE_DEST points to an
    invalid directory, you’ll want to
    change it to point to a valid
    directory.

answered Apr 26, 2011 at 6:39

Justin Cave's user avatar

Justin CaveJustin Cave

225k23 gold badges360 silver badges376 bronze badges

3

ORA-00257:archiver error is occured when your archivelog reached the FRA limit. So you have to clear the archivelogs or you may increase the FRA limit.

To clear the archivelogs, connect to the command prompt and follow steps below:

rman target /
RMAN> delete archivelog all;

It will ask for confirmation and you have to give ‘yes’.

answered Jun 19, 2018 at 11:54

Joe Joy Valiyaveettil's user avatar

1

please note that you can only access SQL*PLUS if you login as
sqlplus / as sysdba

Plus, I think the problem here is space quota for archiving
reaching its max limit.

So its best to clear the logs after making backup on a flash or something

answered Dec 7, 2011 at 6:10

nikunj jain's user avatar

ORA-00257: archiver error. Connect internal only, until freed. problem can be solved as following:
copy archivelog folder to a new destination and empty this directory.

The real problem is that online-backup limit increased what was set as n GB and that become full when you empty this archivelog folder then it will start working fine

answered Apr 6, 2017 at 10:29

Ghayel's user avatar

GhayelGhayel

1,0932 gold badges10 silver badges18 bronze badges

I have encountered this error couple of times, it simply tells that archivelog space has exhausted and need to be freed.

run cmd as administrator

> set oracled_sid=write_oracle_sid_here
> rman target sys/put_sys_password_here
> crosscheck archivelog all;
> delete noprompt expired archivelog all;
>exit;

answered Aug 13, 2019 at 6:01

Sadeq Aramideh's user avatar

Open rman or cmd, then type:
connect target sys/live; press enter
then:
delete archivelog all; press enter

Ask for confirmation press y then enter.
Your issue will be solved.

Dennis Kozevnikoff's user avatar

answered Aug 17, 2021 at 8:48

user16686736's user avatar

Источник

Ошибка 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 можно следующей командой:

Источник

Sujeet APPS DBA

“The road to success is always under construction.”

ora-00257: archiver error. connect as sysdba only until resolved.

ora-00257: archiver error. connect as sysdba only until resolved.


Error While Connecting db through SQL Developer.

Cause
​The archiver process of the oracle database received an error while trying to archive a redo log. The most likely cause of this message is that the destination device is out of space to store the redo log file.

Solution
To resolve this issue, contact the DBA to clear archive log directory and make some room for redo log creation. Once the issue gets resolved, test the connection and run the task.

This command will show you where your archivelogs are being written to:

If the ‘log_archive_dest’ parameter is empty then you are most likely using a ‘db_recovery_file_dest’ to store your archivelogs. You can run the below command to see that location.

SQL> show parameter recovery

NAME TYPE VALUE
———————————— ———— ——————————
db_recovery_file_dest string /u01/fast_recovery_area
db_recovery_file_dest_size big integer 100G

You will see at least these two parameters if you’re on Oracle 10g, 11g, or 12c. The first parameter ‘db_recovery_file_dest’ is where your archivelogs will be written to and the second parameter is how much space you are 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 of you don’t specify a specific location.

SQL> archive log list;

Now, note thatyou can find archive destinations if you are using a destination of USE_DB_RECOVERY_FILE_DEST by:

SQL> show parameter db_recovery_file_dest;

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,

It is important to note that within step five of the ORA-00257 resolution, you may also encounter ORA-16020 in the LOG_ARCHIVE_MIN_SUCCEED_DEST, and you should use the proper archivelog path and use (keeping in mind that you may need to take extra measures if you are using Flash Recovery Area as you will receive more errors if you attempt to use LOG_ARCHIVE_DEST):

SQL>alter system set LOG_ARCHIVE_DEST_.. = ‘location=/archivelogpath reopen’;

The last step in resolving ORA-00257 is to change the logs for verification using:

SQL> alter system switch logfile;

According to the alert log, my db_recovery_file_dest was full and need to increase its size.
If you have enough space on underlying file system then simply increase the size of db_recovery_file_dest_szie otherwise first increase the size of the storage then modify this parameter.

SQL> show parameter db_recover

NAME TYPE VALUE
———————————— ———— ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4500M

SQL> alter system set db_recovery_file_dest_size=5G;
System altered.

Now you can invoke sqlplus

$sqlplus /nolog
SQL>conn / as sysdba
SQL> archive log list;
Check the Archive destination and delete all the logs

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list;

You should purge the archived logs with RMAN:

RMAN>delete archivelog all;

RMAN> crosscheck archivelog all;

RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

SQL> select * from V$RECOVERY_AREA_USAGE;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7days;
RMAN> delete obsolete;

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7days;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

RMAN> Delete archivelog all completed before ‘SYSDATE-1’;

SQL> archive log list;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

SQL> select * from V$RECOVERY_AREA_USAGE;

SQL> select * from v$flash_recovery_area_usage;

Источник

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.

Источник

Oracle: Resolve ORA-00257 – Connect AS SYSDBA only until resolved

Recently, one of the Oracle users complained that the database was unusable and received the error below

Check #1: Are we physically out of space?

When the host is out of space on the drive/device where the FRA has been set, it can produce this error. We run Oracle on Windows. So, it could be

  • Shadow Copy
  • Orphaned files not referenced by the database
  • Extraneous non-database files
  • Archivelog files no longer needed after being backed up (DELETE INPUT option)

I looked at the space situation and it looked good (PowerShell command is from the dbatools module and allows you to get this server space info. from your own host)

Check #2: The Alert Log

Next, I looked at the alert log and saw the problem and the solution was offered right there:

Sorry, the messages are in French. I did a Google Translate to English below

Check #3: Check the space allocated to DB_RECOVERY_FILE_DEST_SIZE

Basically it is saying that the “BACKUP RECOVERY AREA” is full. Although there is ample diskspace, the parameter DB_RECOVERY_FILE_DEST_SIZE determines how much space Oracle can use for database recovery related activities.

To check the space allocated to DB_RECOVERY_FILE_DEST_SIZE you could use one of the following options

SQL*Plus

SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE

This also shows you the current usage as opposed to just the setting with SQL*Plus above

As can be seen, I have it set to 250GB and most of it is used:

NAME Size MB Used MB
\MyHostNameOrafra$ 256000 255991

Check #4: Are backups succeeding?

Check your backup history to make sure Archived Redo Log File backups are successful. If the backups are failing then the Oracle Flash Recovery Area (FRA) may get full. Run archive log backups using the standard procedure you use in your shop. To check if backups are failing you can use this SQL (I am not the author, original source unknown)

Run in SQL*Plus. Substitute &NUMBER_OF_DAYS and tweak WHERE clause as necessary

Solutions:

If disk cleanup is necessary, do so. If backups are failing, remedy the situation and get a successful archive log backup. If FRA is too small, increase the size.

In my case today, the solution to the problem is offered in the alert log itself:

I chose to increase the space for DB_RECOVERY_FILE_DEST_SIZE as we are going through other problems with Backup.

Alternate solutions

You could move the archive log files to a different drive/folder where there is space and recatalog them using the command below by pointing to the directory the archive logs were moved to and then crosscheck/delete expired (shown later).

If you know that certain archive log files were already backed up, you could remove them to make room as shown in this post:

Run this in RMAN (and change to disk if you are not using sbt_tape)

Alternatively, you could first backup the archive log files if you can and then remove them from the recovery area. If you are unable to backup for whatever reason, the archive log files can be physically moved to another location for safekeeping (and recatalog later when you move back) and update the RMAN catalog. You need to check the location pointed to by “db_recovery_file_dest”

..physically move the archive log files and update RMAN catalog to reflect the freed-up space in the recovery area.

Источник

Источник

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

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

А вот еще интересные материалы:

  • Яшка сломя голову остановился исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного исправьте ошибки
  • Ясность цели позволяет целеустремленно добиваться намеченного где ошибка
  • Ora 12528 ошибка как исправить
  • Or ieh 01 ошибка как исправить

    • ➤