Shulou(Shulou.com)06/01 Report--

ORA-00257archiver error solution

1. Previously, we have dealt with the problem of insufficient disk space for oracle 11.2.0.4 archive logs, but have not dealt with the problem of insufficient disk space for ORACLE RAC archive logs.

So I didn't expect it to be the issue of insufficient disk space in asm.

Oracle database is the most commonly used large database system in the industry. I encountered ORA-00257 error (insufficient space error) in the actual project of stand-alone ORACLE.

By looking for information, it is found that most of them say that this is due to too many archived logs, which takes up all the remaining space on the hard disk, which can be solved by simply deleting logs or increasing storage space. But I found on two RAC nodes on Oracle11g RAC that they still have a lot of storage space, but they also reported this error. After more than half a day of twists and turns, I found that it was caused by insufficient disk space in ASM in Oracle11g RAC.

Operating system CentOS 6.5x86-64Linux

Database Oracle 11.2.0.4.0

two。 Problem phenomenon

The database system has been running for more than a year. After my colleagues connected to the database with their application account on May 8th, they found that they could not connect and login, and there was an ORA-00257:archive error.connect internal only,until freed error.

The archive error is prompted. By looking up the ORACLE error code, it is explained that the hard disk space is insufficient and the archive log needs to be deleted to increase the space. However, there is still 1.4T of free space on both nodes of the server. At present, only about 10GB is used. Why?

After deleting and cleaning the archived logs of the system 100 days ago in the form of rman on May 8th, I did not expect that it would appear again on May 9th the next day. There is no reason why a day's archived log is larger than a hundred days' archived log, so I question the accuracy of the database error report, thinking that it is only that the superficial archived log space is full, but the real problem is still unknown.

3. Diagnostic process:

Because I didn't build the database, and I'm not familiar with oracle, I don't know the path where the archive log is placed, and I get a + ARC/back/archivelog/** or something through sentence search, but I just want to break my head, and I can't find a specific storage path with find.

a. Check the database REDOLOG

[root@~] $sqlplus / as sysdba

SQL > connect / as sysdba

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#FIRST_TIME

1 1 101 52428800 1 NO CURRENT 3621973 9-May-17

2 1 99 52428800 1 NO INACTIVE 3600145 9-May-17

3 1 100 52428800 1 NO INACTIVE 3611932 September-May-17

It is found that the ARC status is NO, which means that the system cannot archive automatically.

b. View the Oracle database background archiving service process

[oracle@~] ps-ef | grep oracle

Found grid 3081 10 10:14-00:00:00 oracle+ASM1 (DESCRIPTION= (LOCAL=YES) (ADDRESS= (PROTOCOL=beq)

One node, such as the above service, is normal, but the other node is in a similar situation of LOCAL=NO. Anyway, the service is not normal.

c. View the usage of each part of the FLASH_RECOVERY_AREA space

SQL > select * from v$recovery_file_dest

NAME

SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--

+ BACKUP

1.2885E+11 484442112 0 5

# # Note: there is plenty of space.

SQL > select * from v$flash_recovery_area_usage

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

NUMBER_OF_FILES

-

CONTROL FILE. 04 0 1

REDO LOG. 33 0 4

ARCHIVED LOG 0 0 0

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

NUMBER_OF_FILES

-

BACKUP PIECE 0 0 0

IMAGE COPY 0 0 0

FLASHBACK LOG 0 0 0

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

NUMBER_OF_FILES

-

FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

Found that ARCHIVELOG, BACKUPPIRCR are not big, that is, 0, there is no occupancy rate, so there is still plenty of space for FLASH_RECOVERY_AREA space.

Because I didn't know how to deal with the problem when I reported an error before, just like shutdown immediate a node directly, but I didn't expect to use shutdown abort to shut it down forcefully without any reaction for a long time. The node has the following node mount instance report problem.

d. An error is reported when the instance is mounted to the failed node:

SQL > startup

ORACLE instance started.

Total System Global Area 413372416 bytes

Fixed Size 2253784 bytes

Variable Size 327158824 bytes

Database Buffers 79691776 bytes

Redo Buffers 4268032 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 2420

Session ID: 1 Serial number: 5

e. Looking up the data, it is found that the following is feasible:

SQL > conn / as sysdba

Connected to an idle instance.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 413372416 bytes

Fixed Size 2253784 bytes

Variable Size 327158824 bytes

Database Buffers 79691776 bytes

Redo Buffers 4268032 bytes

SQL > alter database mount

Database altered.

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

An error was found when opening the data file.

SQL > recover database until time '2017-05-09'

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 1-file is in use or recovery

ORA-01110: datafile 1:'+ DATADG/fmall/datafile/system.259.907327891'

Recovery in the form of overlay also reported an error

f. Later, it is suggested that the RMAN operating system can be used to delete the archive to solve the problem.

Crosscheck under rman and then delete.

[oracle@~] rman target sys/pass

RMAN > crosscheck archivelog all

Validation succeeded for archived log

Archived log file name=+ARCH/fmall/archivelog/2017_03_08/thread_2_seq_6362.21528.938070989 RECID=43022 STAMP=938070990

Validation succeeded for archived log

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row

RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows

ORACLE error from target database:

ORA-01089: immediate shutdown in progress-no operations are permitted

Process ID: 14578

Session ID: 235 Serial number: 2647

Check the availability of the log, report an error or wait a long time for no result.

RMAN > delete archivelog all completed before 'sysdate-30'

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

If you delete the archived log from 30 days ago and wait for a long time but no result, or even report an error as above, you can refer to my following practice

RMAN > delete force noprompt archivelog until time 'sysdate-30'

Mandatory deletion of archived logs from 30 days ago

The abnormal alarm of the website is released, which means that the archived log is free, so that the database can provide application connection normally.

g. An example shows that the application cannot connect to the database because the archive log space is full.

The experienced DBA told me that RAC will have an ASM disk space issue. After querying the ASM disk space, I found that, my God, the ARCH space is only 282m free.

SQL > select name,total_mb,free_mb from v$asm_disk

NAME TOTAL_MB FREE_MB

GRIDDG_0001 4768 4585

ARCH_0000 944137 282

DATADG_0000 1238390 1202771

GRIDDG_0000 4768 4553

BACKUP_0000 953675 949001

Enter RMAN again, delete it using delete archivelog for another 15 days, and check the disk space size of ASM:

SQL > select name,total_mb,free_mb from v$asm_disk

NAME TOTAL_MB FREE_MB

GRIDDG_0001 4768 4585

ARCH_0000 944137 716045

DATADG_0000 1238390 1202771

GRIDDG_0000 4768 4553

BACKUP_0000 953675 949001

Nearly 700 gigabytes of space has been vacated, which is terrible. Nearly 700 gigabytes of archived log space can be used in just 15 days. The size of my database is only 1.1 gigabytes, and archived logs grow at a rate of more than ten or twenty gigabytes a day. There must be something wrong and must be thoroughly investigated.

Of course, the first estimate is to script the command to clean up the archive log into crontab and process it at a certain time. The initial suspicion is that there is something similar to an endless loop in the application's sql statement, which makes the archive log so large that I will record it again if I find anything later.

As a result, the issue that the archive log space is full is temporarily resolved.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.