记一次sql执行卡死的问题

lig

　　这几天一直在进行aix环境下10.2.0.1数据库升级为10.2.0.5的实验
　　但是在跑数据库脚本的时候总是跑到某一步数据库就没反应了，然后检查数据库等待事件一直显示为空闲等待
　　日志中报错
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc1_23310.trc:
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:19:29 CDT 2015
　　ARCH: Archival stopped, error occurred. Will continue retrying
　　Sun May 31 20:19:29 CDT 2015
　　ORACLE Instance test - Archival Error
　　Sun May 31 20:19:29 CDT 2015
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:19:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc0_26602.trc:
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:24:07 CDT 2015
　　Shutting down instance (immediate)
　　Sun May 31 20:24:07 CDT 2015
　　Shutting down instance: further logons disabled
　　Sun May 31 20:24:07 CDT 2015
　　Stopping background process CJQ0
　　Sun May 31 20:24:07 CDT 2015
　　Stopping background process MMNL
　　Sun May 31 20:24:09 CDT 2015
　　Stopping background process MMON
　　License high water mark = 2
　　Sun May 31 20:24:10 CDT 2015
　　Job queue slave processes stopped
　　Sun May 31 20:24:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc1_23310.trc:
　　ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
　　Sun May 31 20:24:29 CDT 2015
　　************************************************************************
　　You have following choices to free up space from flash 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.
　　************************************************************************
　　ARCH: Archival stopped, error occurred. Will continue retrying
　　Sun May 31 20:24:29 CDT 2015
　　ORACLE Instance test - Archival Error
　　Sun May 31 20:24:29 CDT 2015
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:24:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc1_23310.trc:
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:25:29 CDT 2015
　　ARCH: Archival stopped, error occurred. Will continue retrying
　　Sun May 31 20:25:29 CDT 2015
　　ORACLE Instance test - Archival Error
　　Sun May 31 20:25:29 CDT 2015
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:25:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc0_26602.trc:
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:29:09 CDT 2015
　　Active call for process 23850 user 'oracle' program 'oracle@boweston (TNS V1-V3)'
　　System State dumped to trace file /u01/app/oracle/admin/test/udump/test_ora_18216.trc
　　SHUTDOWN: waiting for active calls to complete.
　　Sun May 31 20:30:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc1_23310.trc:
　　ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% us
　　Sun May 31 20:30:29 CDT 2015
　　************************************************************************
　　You have following choices to free up space from flash 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.
　　************************************************************************
　　ARCH: Archival stopped, error occurred. Will continue retrying
　　Sun May 31 20:30:29 CDT 2015
　　ORACLE Instance test - Archival Error
　　Sun May 31 20:30:29 CDT 2015
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:30:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc1_23310.trc:
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:31:29 CDT 2015
　　ARCH: Archival stopped, error occurred. Will continue retrying
　　Sun May 31 20:31:29 CDT 2015
　　ORACLE Instance test - Archival Error
　　Sun May 31 20:31:29 CDT 2015
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:31:29 CDT 2015
　　Errors in file /u01/app/oracle/admin/test/bdump/test_arc0_26602.trc:
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　Sun May 31 20:33:47 CDT 2015
　　sculkget: failed to lock /u01/app/oracle/product/10.2.0/db_1/dbs/lkinsttest exclusive
　　sculkget: lock held by PID: 18216
　　Oracle Instance Startup operation failed. Another process may be attempting to startup or shutdown this Instance.
　　Failed to acquire instance startup/shutdown serialization primitive
　　Sun May 31 20:33:52 CDT 2015
　　Instance shutdown cancelled
　　Sun May 31 20:34:00 CDT 2015
　　Starting ORACLE instance (normal)
　　trace中报错
　　*** 2015-05-31 18:56:48.308 64950 kcrr.c
　　ARC0: Error 19809 Creating archive log file to '/u01/app/oracle/flash_recovery_area/TEST/archivelog/2015_05_31/o1_mf_1_90_%u_.arc'
　　*** 2015-05-31 18:56:48.308 63059 kcrr.c
　　kcrrfail: dest:10 err:19809 force:0 blast:1
　　*** 2015-05-31 18:56:48.351 22402 kcrr.c
　　ORA-16038: log 3 sequence# 90 cannot be archived
　　ORA-19809: limit exceeded for recovery files
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 18:57:29.674
　　*** 2015-05-31 18:57:29.674 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:03:29.671
　　*** 2015-05-31 19:03:29.671 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:09:29.680
　　*** 2015-05-31 19:09:29.680 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:15:29.688
　　*** 2015-05-31 19:15:29.688 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:21:29.689
　　*** 2015-05-31 19:21:29.689 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:26:48.410
　　*** 2015-05-31 19:26:48.410 22402 kcrr.c
　　ORA-16014: log 3 sequence# 90 not archived, no available destinations
　　ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test/redo03.log'
　　*** 2015-05-31 19:31:48.505
　　ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
　　*** 2015-05-31 19:31:48.506
　　经查找资料确认为db_recover_file_dest_size,大小设置不够，导致日志归档失败，sql无法继续执行
　　修改参数
　　SQL> show parameter db_recovery
　　NAME                                 TYPE        VALUE
　　------------------------------------ ----------- ------------------------------
　　db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
　　_area
　　db_recovery_file_dest_size           big integer 2G

　　SQL>>
　　System>　　修改成功
　　修改后sql重新执行，执行通过