ORACLE 11G DB RAC ora-00257archiver Error Resolution

Ora-00257archiver Error Resolution

1. Previous processing of single-machine over Oracle 11.2.0.4 archive log disk space is insufficient, but there is no problem with insufficient archive log disk space for Oracle RAC

So there is no expectation that ASM disk space is out of the question

Oracle database is the most commonly used large database system in the industry, I have encountered ORA-00257 error (insufficient space error) in the actual project of single machine Oracle.

By finding the data, it is found that most of this is due to the large archive log, which takes up all the remaining space on the hard disk, can be solved by simply deleting the log or increasing the storage space. But I oracle11g RAC on the two RAC nodes found that their storage space is still very large, but also reported this error, after a large half-day toss, found that the original is oracle11g RAC in the ASM disk space caused by the lack of.

Operating system CentOS 6.5 X86-64linux

Database Oracle 11.2.0.4.0

2. Problem phenomena

The database system has been running for more than a year, and on May 8, when the development colleague connected the database with the application account, it was found unable to connect login, and ora-00257:archive error.connect internal Only,until freed error occurred.

Prompt archive error, by looking for the Oracle error code, explained that there is not enough hard disk space, need to delete the archive log to increase the space, but the server two nodes free space is still 1.4T, currently only used about 10GB, this is why?

And on May 8, in the form of Rman to remove the cleanup system 100 days before the archive log well, did not expect the next day May 9 appeared again. No reason Ah, a day's archive log is larger than 100 days of archive logs, so I questioned the accuracy of database error, that this is only the surface of the archive log space is full, the substantive problem is not known.

3. Diagnostic process:

Because the database is not my erection, the Oracle is not very familiar with, so I do not know the path of the archive log placement, through the sentence lookup also get a +arc/back/archivelog/**, but I just want to break the head, with find the way can not find the specific storage path.

A. Viewing the Redolog of a database

[Email protected]~]$ 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 September-May-17

2 1 52428800 1 NO INACTIVE 3600145 September-May-17

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

The Arc status is no, indicating that the system cannot be archived automatically.

B. Viewing the Oracle Database background archive service process

[[Email protected]~]ps-ef | grep Oracle

Found grid 3081 1 0 10:14? 00:00:00 oracle+asm1 (description= (Local=yes) (address= (PROTOCOL=BEQ)))

A node such as the above service is normal, but another node is local=no similar situation, anyway, service is not normal

C. Viewing the use of parts 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

# #注: There's plenty of space

Sql> select * from V$flash_recovery_area_usage;

File_type percent_space_used percent_space_reclaimable

-------------------- ------------------ -------------------------

Number_of_files

---------------

CONTROL FILE . 0 1

REDO LOG . 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 Archivelog, BACKUPPIRCR are not small, is 0, no occupancy rate, so flash_recovery_area space is a lot of space.

Because before the error, do not know how to deal with the problem, just like a direct shutdown immediate a node to see, did not think of a half-day without any reaction, directly using the shutdown abort forcibly closed, the node appears the following node mount instance of the problem

D. Failed node mount instance error:

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. Find information found, the following approach is possible:

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

Found an error in 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:data file 1: ' +datadg/fmall/datafile/system.259.907327891 '

Recover with cover form also error

F. The following data suggest that the Rman operating system can be used to remove the archive to solve the problem

Rman under Crosscheck and then delete.

[Email protected]~]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 is permitted

Process id:14578

Session id:235 Serial number:2647

Verify the availability of logs, error or wait long no results

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 archive log 30 days ago, waiting for a long time but no results, even as the above error can refer to my following practices

RMAN > Delete Force noprompt archivelog until time ' sysdate-30 ';

Mandatory deletion of archive logs 30 days ago

Website abnormal alarm release, indicating that the archive log to make room for the database to provide a normal application connection.

G. Example proves that an application cannot connect to the database because the archive log space is full

The experienced dba told me that the RAC would have an ASM disk space issue, and I went through the ASM disk space discovery, my mother, Arch space was only released 282M.

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

Once again into Rman, after 15 days of deletion using delete archivelog, view the ASM disk space size:

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

Empty out nearly 700 g of space, too scary, just 15 days can use nearly 700 g of archive log space, my database size is 1.1G, archive logs on a day at more than 10 20 g of the speed of soaring. There must be a thorough investigation of what went wrong.

The first thing to do, of course, is to include the Cleanup Archive log command as a script into the crontab and process it for a certain amount of time. The initial suspicion is that there is something like a dead loop in the application SQL statement that causes the archive log to be so large that I will record it again if there is any subsequent discovery.

To this, the archive log space is full the issue is temporarily resolved.

This article is from the "10793382" blog, please be sure to keep this source http://10803382.blog.51cto.com/10793382/1924973

ORACLE 11G DB RAC ora-00257archiver Error Resolution