Solve the problem that Oracle archive log is full

Source: Internet
Author: User

I. background
Today, I received a development call saying that Oracle cannot be remotely connected. It may be that archivelog is full. I tried logging on to Oracle and prompted
ORA-00257: archiver error. Connect internal only, until freed.
Considering that the hard disk space has been expanded in the past few days, it should be because the archive log size has reached the archive log size limit in Oracle. DBA has a holiday. Therefore, we decided to clear useless logs and modify the limit on log size.
Since I am not familiar with Oracle, I will record the entire process after completing the process for future reference.
II. Environment
RHEL 6.3 + Oracle 11gR2
Iii. Process
Because the archive log space is full, remote logon is not allowed. After ssh is sent to the server, use sqlplus sys/pass as sysdba to log on to oracle.
First, check the location of the archiv log.
SQL> show parameter log_archive_dest;

The following result is displayed:
NAME TYPE VALUE
-----------------------------------------------------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string

If the value is empty, try

SQL> archive log list;

Then I checked the usage

SQL> select * from V $ FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------------------------------------------------------
CONTROLFILE. 13 0 1
ONLINELOG 2.93 0 3
ARCHIVELOG 99.72 0 122
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

 

Obviously, more than 99% of the log space is used.
At this time, I directly go to the flash_recovery_are directory.
Cd/orace/app/oracle/flash_recovery_area/ORCL/archivelog

Then I decided to back up the relevant files first.
Tar Jcvf arlog.20140125.tar. xz *

During the first operation, the system will prompt that the file is changed during the compression process. I didn't stop the Oracle service. So return to sqlplus and stop Oracle first
SQL> shutdown immediate;

This process takes a long time to complete ..
At this time, package again. The packaging process lasted for more than two hours...
After that, back up the compressed package to another directory and scp it to a server used for backup files. Then, it was very violent to delete all the directories in the folder named 2013.
1 rm-rf 2013 _*

Finished. Start the database

Sqlplus sys/pass as sysdba

SQL> startup

The first time I startup failed, the ORA-03113: end-of-file on communication channel was prompted. I re-executed it.
SQL> startup mount

Successful. Fix related information with rman
Rman target sys/pass
RMAN> delete expired archivelog all;

Return to sqlplus to modify the archivelog size.
SQL> alter system set db_recovery_file_dest_size = 5G scope = spfile;

The modification does not take effect immediately. Oracle needs to be restarted
SQL> shutdown immediate
SQL> startup

Check the size at this time
SQL> show parameter db_recover
NAME TYPE VALUE
-----------------------------------------------------------------------------
...
Db_recovery_file_dest_size big integer 5G

The whole process is complete.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.