130527fra空间不够报错

在创建一张大数据的测试表的过程中，系统报错， 提示FRA空间不够，因为归档联机日志都放在FRA空间中；

SQL> alter system set db_recovery_file_dest_size=10000M scope=both;

System altered.

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\app\calvin\flash_recovery_a
                                                 rea
db_recovery_file_dest_size           big integer 10000M
recovery_parallelism                 integer     0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3407261696 bytes
Fixed Size                  2180504 bytes
Variable Size            2013268584 bytes
Database Buffers         1375731712 bytes
Redo Buffers               16080896 bytes
Database mounted.
Database opened.
SQL>

在创建表的过程中，系统没有反应，登陆EM,发现fra错误：

 

查询到文档 How to Resolve ORA-00257: Archiver is Stuck Error in 10g and higher [ID 278308.1]

Symptoms
Connections to the database receive ORA-00257: archiver is stuck error.


Cause
The max limit for flash recovery area(db_recovery_file_dest_size) is reached.


Solution
1. Check whether the database is in archive log mode and automatic archiving is enabled.

SQL> archive log list;

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by :

SQL> show parameter db_recovery_file_dest;

Check what is the value set for db_recovery_file_dest_size

3. Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

4. If SPACE_USED is equal to SPACE_LIMIT, move the archive logs to a different destination.

5. Archive all the log files

SQL> alter system archive log all;

NOTE:  See Note 315098.1 for pointers on backing up files from the flash recovery area to decrease space usage.

6. If ORA-16020: less destinations available than specified by LOG_ARCHIVE_MIN_SUCCEED_DEST 

is received for step 5, then for each destination give the correct archivelog path and issue:

SQL>alter system set LOG_ARCHIVE_DEST_.. = 'location=/archivelogpath reopen';

NOTE:  
=====

If you have configured Flash Recovery Area (default in 10gR2) -- meaning that you have set the two initialization parameters: 
-- DB_RECOVERY_FILE_DEST_SIZE 
-- DB_RECOVERY_FILE_DEST
you cannot use the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters any more. You must always use the LOG_ARCHIVE_DEST_n parameters in case you have configured flash recovery area. LOG_ARCHIVE_DEST_10 is implicitly set to USE_DB_RECOVERY_FILE_DEST if you create a recovery area and do not set any other local archiving destinations.

If you try to use LOG_ARCHIVE_DEST with a Flash Recovery Area configured, you will receive errors like:

ERROR at line 1: 
ORA-02097: parameter cannot be modified because specified value is invalid 
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or 
DB_RECOVERY_FILE_DEST 

7. Just switch the logs to verify:

SQL> alter system switch logfile;

NOTE:
====

If you want to increase the flash recovery area:

STEPS:
=====

1. Note down the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

2. Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

3. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10g SCOPE=BOTH SID='*'; 

4. Enable the Flash Recovery Area 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/dir1' SCOPE=BOTH SID='*';

If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*'; 

 

解决方法是修改flash recovery area区域的大小。尝试着重启了一下数据库；

SQL> SHUTDOWN IMMEDIATE;
ORA-03113: end-of-file on communication channel

SQL> startup;
ORACLE instance started.

Total System Global Area 3407261696 bytes
Fixed Size                  2180504 bytes
Variable Size            2013268584 bytes
Database Buffers         1375731712 bytes
Redo Buffers               16080896 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 11964
Session ID: 127 Serial number: 3

系统报错ora-03113，查询metalink

查询到文档 OERR: ORA 3113 "end-of-file on communication channel" [ID 19790.1]

Error:  ORA 3113
Text:   end-of-file on communication channel
-------------------------------------------------------------------------------
Cause:  An unexpected end-of-file was processed on the communication channel.
        The problem could not be handled by the SQL*Net, two task, software.
        This message could occur if the shadow two-task process associated with
        a SQL*Net connect has terminated abnormally, or if there is a physical
        failure of the interprocess communication vehicle, that is, the 
        network or server machine went down.

Action: If this message occurs during a connection attempt, check the setup 
        files for the appropriate SQL*net driver and confirm SQL*Net software
        is correctly installed on the server. If the message occurs after a 
        connection is well established, and the error is not due to a physical 
        failure, check if a trace file was generated on the server at failure
        time. Existence of a trace file may suggest an Oracle internal error
        that requires the assistance of customer support. 

*** Important: The notes below are for experienced users - See Note:22080.1


Explanation:
        There are numerous causes of this error. It is a 'catch all' type 
        error which means 'I cannot communicate with the oracle shadow process'.
        This usually occurs when the Oracle server process has died for
        some reason.

Diagnosis:
        Oracle support need more information to determine what caused the
        error. The most useful items are:

        1) Does this occur when trying to (a) make a connection to the 
           database or (b) on an established connection ?

        2) There should be a trace file from the failed session in 
           USER_DUMP_DEST

        3) Does a particular statement reproduce this problem or is it
           random ? Use SQL_TRACE on the session to find the problem
           statement.

Articles:
        ORA-3113 on Unix - What Information to Collect       Note:17613.1
        DIAGNOSING ORA-3113 ERRORS  Note:1020463.6

 查询日志；

ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 47303680 字节磁盘空间 (从 10485760000 限制中)
ARC2: Error 19809 Creating archive log file to 'D:\APP\CALVIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_05_27\O1_MF_1_165_%U_.ARC'
Errors in file d:\app\calvin\diag\rdbms\test\test\trace\test_ora_11964.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10485760000 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from 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 delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file d:\app\calvin\diag\rdbms\test\test\trace\test_ora_11964.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 42884096 bytes disk space from 10485760000 limit
ARCH: Error 19809 Creating archive log file to 'D:\APP\CALVIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_05_27\O1_MF_1_164_%U_.ARC'
Errors in file d:\app\calvin\diag\rdbms\test\test\trace\test_ora_11964.trc:
ORA-16038: log 2 sequence# 164 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:\APP\CALVIN\ORADATA\TEST\REDO02.LOG'

还是fra空间的问题，修改fra空间大小

 

SQL> startup mount;
ORACLE instance started.

Total System Global Area 3407261696 bytes
Fixed Size                  2180504 bytes
Variable Size            2013268584 bytes
Database Buffers         1375731712 bytes
Redo Buffers               16080896 bytes
Database mounted.
SQL> show parameter db_recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\app\calvin\flash_recovery_a
                                                 rea
db_recovery_file_dest_size           big integer 10000M
SQL> alter system set db_recovery_file_dest_size = 20000M scope = both;

System altered.

SQL> alter database open;

Database altered.

SQL> truncate hr.test3;
truncate hr.test3
           *
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword


SQL> truncate table hr.test3;

Table truncated.

并且将hr.test3表删除，系统恢复正常；

 

结论：当系统归档联机日志过大，或者大小超出fra大小时，系统仍旧打开，但是仅限内部链接，EM都无法连接数据库；