Archivelog is full, which makes Oracle inaccessible.

Source: Internet
Author: User
Tags sqlplus

I. BACKGROUND

Ora-00257:archiver error. Connect internal only, until freed.
Considering that the hard disk space has been enlarged in the previous days, the archive log size has reached the limit of archive log size in Oracle. And the DBA is on holiday. Therefore, the decision to clear the unused log at the same time to modify the size of the log limit.
Since I am not familiar with Oracle, I have documented the entire process after completion for easy reference.
Second, the environment
RHEL 6.3 + Oracle 11gR2
Third, the process
Since the archive log space is full and cannot be logged in remotely, SSH to the server and use Sqlplus Sys/pass as SYSDBA to access Oracle.
First look at where Archiv log is located
Sql> Show parameter log_archive_dest;

You will see results similar to the following
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Log_archive_dest string
Log_archive_dest_1 string
Log_archive_dest_10 string

At this point, if value is empty, you can try

sql> archive log list;

And 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

Clearly, more than 99% of the log space has been used
This time, I went directly into the Flash_recovery_are directory
Cd/orace/app/oracle/flash_recovery_area/orcl/archivelog

And then I decided to back up the relevant files first.
Tar jcvf arlog.20140125.tar.xz *

During the first operation, it is discovered that the file has changed during the compression process. Just remembered that I didn't stop Oracle services. So go back to Sqlplus and stop the Oracle first.
sql> shutdown immediate;

This process has been waiting for a long time to complete.
RePack at this time. The packing process lasted almost two hours ...
Once completed, the package is backed up to a different directory and the SCP is to a server for backup files. And then violently deleted all the directories for the next 2013 years in this folder.
1 RM-RF 2013_*

It's done. Start up the database

Sqlplus Sys/pass as Sysdba

Sql> Startup

My first startup failed, prompting Ora-03113:end-of-file on communication channel. I re-executed the next
Sql> Startup Mount

Success. Repair related information with Rman
Rman Target Sys/pass
rman> Delete expired Archivelog all;

When finished, go back to Sqlplus to modify the Archivelog size
Sql> alter system set DB_RECOVERY_FILE_DEST_SIZE=5G Scope=spfile;

Modifications do not take effect immediately. Need to restart Oracle
sql> shutdown Immediate
Sql> Startup

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

The

Archivelog is full, which makes Oracle inaccessible.

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.