An example of solution to system downtime caused by full archiving logs of Oracle databases

Source: Internet
Author: User
When the first downtime occurs, the system memory overflows. Therefore, the application server is restarted and an error is reported when the application server is started. The error is that the Oracle database cannot be connected. Therefore

When the first downtime occurs, the system memory overflows. Therefore, the application server is restarted and an error is reported when the application server is started. The error is that the Oracle database cannot be connected. Therefore

When the first downtime occurs, the system memory overflows. Therefore, the application server is restarted and an error is reported when the application server is started. The error is that the Oracle database cannot be connected. Connect to the database server, open EM, and the system reports an error.

The error message "archive log writing failed" is displayed. Check whether the disk space is full on the server. After the disk space is cleared, restart the database to solve the problem. Then, the server disk space is resized, and 1 TB of disk space is directly allocated to the oracle data disk.

This problem occurs again for the second time. After careful check and confirmation with colleagues, it is found that the archive log of the ORACLE database is enabled, by default, the ORACLE database archiving function is not enabled.

Run the SQL command to View Details:

SQL> sqlplus/as nolog; ------------------- start SQL * Plus

SQL> connect sys/password @ orcl as sysdba;

SQL> archive log list;

Database Log mode archiving Mode

Enable automatic archiving

Archiving end point USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4888

Next archive log sequence 4890

Current Log sequence 4890

SQL> show parameter db_recovery_file_dest;

NAME TYPE VALUE

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

Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0/flash_recovery_area

Db_recovery_file_dest_size big integer 20G

Solution:

I. First, you must handle the full log space:

1. Specify database instances

C:/Documents and Settings/Administrator> SET ORACLE_SID = orcl

2. Connect to the database

C:/Documents ents and Settings/Administrator> rman target sys/password @ orcl

3. view the status of archived logs

RMAN> list archivelog all;

4. manually delete archived log files

RMAN> exit

SQL> alter system set db_recovery_file_dest_size = 214748364800; --- set the space used (20*1024*1024*1024)

System altered

SQL> show parameter db_recovery_file_dest; --- view the archive Log Path quota

NAME TYPE VALUE

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

Db_recovery_file_dest string D: \ oracle \ product \ 10.2.0/flash_recovery_area

Db_recovery_file_dest_size big integer 200G

After the database is restarted, the system can be used normally.

However, because archive logs are enabled, even if the space is set to GB, the daily data growth by 2 GB can be achieved in 3 months. You need to customize tasks to regularly clean up the archived logs, the deletion of archived logs can only be performed in RMAN, so a new bat file is created on the database server (the file name is random)

Edit this file as follows:

Rman target 'sys/password'your file 'd: \ your .txt '-the path and file name here are random.

In the command, create a new TXT file under the corresponding directory and open and edit the file,

Delete archivelog all completed before 'sysdate-7 ';

Then, add a task in the scheduled Windows task to specify that the bat file is executed regularly on a daily basis.

After one week of operation, archived logs are regularly cleared every day. The system is normal.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

,

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.