The real reason why Oracle instances are weird down

The real reason why Oracle instances are weird down

1, monitoring colleagues saidOracleThe test Library also automatically Downup.

I login check to see that there is no Oracle background process running, the Oracle instance is actually shut down, immediately to start it.

# failed to start Sql> startup; ORACLE instance started.   Total System Global area 1603411968 bytes Fixed size              2213776 bytes Variable size                989857904 Bytes Database buffers      603979776 bytes Redo buffers                  7360512 bytes Database mounted. Ora-03113:end-of-file on communication channel                     nbsp                                 Pro Cess id:13735 Session id:191 Serial number:3     Sql>

2, Analysis Downthe cause

To see the background alert log:

Errors in FILE/ORACLE/APP/ORACLE/DIAG/RDBMS/POWERDES/POWERDES/TRACE/POWERDES_ORA_8179.TRC: Ora-19815:warning:db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available. ************************************************************************ You have the following choices to free up space from the recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, Then consider changing RMAN ARCHIVELOG deletion POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY Area Command. 3. ADD disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN Delete command. If an operating System command was used to the delete files, then use RMAN crosscheck and DELETE EXPIRED Commands. ************************************************************************ Errors in FILE/ORACLE/APP/ORACLE/DIAG/RDBMS/POWERDES/POWERDES/TRACE/POWERDES_ORA_8179.TRC: Ora-19809:limit exceeded for recovery files Ora-19804:cannot Reclaim 50173952 bytes disk space from 5218762752 limit Arch:error 19809 Creating Archive log file to '/oracle/app/oracle/flash_recovery_area/powerdes/archivelog/2016_09_09/ O1_mf_1_1589_%u_.arc ' Errors in FILE/ORACLE/APP/ORACLE/DIAG/RDBMS/POWERDES/POWERDES/TRACE/POWERDES_ORA_8179.TRC: Ora-16038:log 2 sequence# 1589 cannot be archived Ora-19809:limit exceeded for recovery files Ora-00312:online Log 2 thread 1: '/data/oracle/powerdes/redo02.log ' USER (ospid:8179): Terminating the instance due to error 16038 Instance terminated by USER, PID = 8179

Cause analysis found, is the ora-19815:warning:db_recovery_file_dest_size of 5218762752 bytes is 100.00% used archive log flash back area full, So Oracle is automatically down. 4 solutions are also suggested:

(1) Setting the archive log expiration policy

(2) Execute the Backup RECOVERY Area Command

(3) Increase db_recovery_file_dest_size size

(4) Rman deletes expired archive logs

Here's the fastest solution for (4): Rman Deletes archive logs

3. Rman Delete Archive Log resolution

(1) Start the database by Mount mode to ensure that Rman can log in.

# Start with Mount first sql> startup Mount; ORACLE instance started. Total System Global area 1603411968 bytes Fixed Size 2213776 bytes Variable Size 989857904 bytes Database buffers 603979776 bytes Redo buffers 7360512 bytes Database mounted. Sql> Sql>

(2) Log in with Rman, prepare to clean up the expired archive log, delete archivelog all completed before ' sysdate-1 '; Delete archived logs from one day ago:

# then Rman logs in to clean up the archive log [[Email Protected]_test_121_90 ~]$ [[Email protected]_test_121_90 ~]$ rlwrap Rman target/ Recovery manager:release 11.2.0.1.0-production on Wed Sep 14 13:39:15 2016 Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved. Connected to target Database:powerdes (dbid=3458668465, not open) rman> Delete Archivelog all completed before ' sysdate-1 '; Using target database control file instead of recovery catalog Allocated Channel:ora_disk_1 Channel ora_disk_1:sid=129 Device Type=disk List of archived Log Copies for database with Db_unique_name Powerdes ===================================================================== Key thrd Seq S Low Time ------- ---- ------- - --------- 1 1469 A 21-mar-16 Name:/oracle/app/oracle/flash_recovery_area/powerdes/archivelog/2016_03_21/o1_mf_1_1469_ch00ykdh_.arc 1 1470 A 21-mar-16 Name:/oracle/app/oracle/flash_recovery_area/powerdes/archivelog/2016_03_22/o1_mf_1_1470_ch07y0vk_.arc ......

(3) Finally open the database

# finally open the database Sql> Sql> sql> ALTER DATABASE open; Database altered. Sql>

4, the long-term solution

Another way to add db_recovery_file_dest_size value, after modification, added to the startup parameters, Permanent effect:

Sql> Show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ Db_recovery_file_dest_size Big Integer 4977M Sql> # Expand parameter Values sql> ALTER SYSTEM SET db_recovery_file_dest_size=8g scope=both; System altered. Sql> Show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ Db_recovery_file_dest_size Big Integer 8G Sql> # error correction to startup parameters Sql> create Pfile from SPFile; File created. Sql> # View usage Status Sql> select Name,space_limit,space_used,number_of_files from V$recovery_file_dest; NAME -------------------------------------------------------------------------------- Space_limit space_used Number_of_files ----------- ---------- --------------- /oracle/app/oracle/flash_recovery_area 6442450944 2905126912 61 Sql>

5, a permanent solution to the problem

The 4th step, increase the db_recovery_file_dest_size value, will prolong the use of the flashback zone, but one day will be full, in order to permanently solve the problem, ready to do a scheduled task every day to clean up the expired archive log, This ensures that there is enough storage space in the flashback area.

Cleanup Expired archive Log script archivelog_clear.sh:

#!/bin/sh Back_dir=/oracle/backup/data Export date= ' DATE +%f ' Su-oracle-c " Mkdir-p $BACK _dir/$DATE Rman log= $BACK _dir/$DATE/rman_backup.log target/<<eof Crosscheck backup; Crosscheck Archivelog All; Delete noprompt expired backup; Delete noprompt expired archivelog all; Delete NoPrompt obsolete; Exit Eof "

Set up a crontab task to run cleanup of an expired archive log every day

* * * */oracle/backup/scripts/archivelog_clear.sh >>/oracle/backup/data/_fullback.log 2>&1

The real reason why Oracle instances are weird down