statement suspended, wait error to be cleared

发表于 2011 年 11 月 24 日 由 惜分飞

一、现场报告
导入数据到Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX的时候，hang住了，求救

[oracle@TestServer-RHAS-5 dmpdir]$ impdp system/ DIRECTORY=dmpdir DUMPFILE=cscnew.20111123.dmp LOGFILE=cscnew.20111123.log SCHEMAS=CSCNEW remap_schema=CSCNEW:TESTB remap_tablespace=CSC_TAB_1:TESTB table_exists_action=replace
…………
. . imported "TESTB"."TAB_CS_SELF_WORKTIME"                  0 KB       0 rows
. . imported "TESTB"."TAB_CS_SELF_WORKTIME_DETAIL"           0 KB       0 rows
. . imported "TESTB"."TAB_CS_USERMENU"                       0 KB       0 rows
. . imported "TESTB"."TAB_PUB_BANK"                          0 KB       0 rows
. . imported "TESTB"."TAB_PUB_BUSISRVINFO"                   0 KB       0 rows
. . imported "TESTB"."TAB_PUB_CONTACT"                       0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

二、处理过程
1、分析是否是impdp是否因为网络等情况终止掉

[oracle@TestServer-RHAS-5 ~]$ ps -ef|grep impdp
oracle    2520  1837  0 09:59 pts/8    00:00:00 grep impdp
oracle   23819 20966  0 09:39 pts/6    00:00:00 impdp         DIRECTORY=dmpdir DUMPFILE=cscnew.20111123.dmp LOGFILE=cscnew.20111123.log SCHEMAS=CSCNEW remap_schema=CSCNEW:TESTB remap_tablespace=CSC_TAB_1:TESTB table_exists_action=replace
[oracle@TestServer-RHAS-5 ~]$ ps -ef|grep LOCAL=YES
oracle    2692  1837  0 10:00 pts/8    00:00:00 grep LOCAL=YES
oracle   10754 10694  0 09:15 ?        00:00:09 oraclemcrm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   23835 23819  0 09:40 ?        00:00:00 oraclemcrm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

通过上面的查询，证明impdp进程工作正常

2、查询等待事件

[oracle@TestServer-RHAS-5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 24 10:00:26 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select event from v$session_wait where wait_class#<>6; 

EVENT
----------------------------------------------------------------
SQL*Net message to client
statement suspended, wait error to be cleared

通过这个查询，发现一个异常等待事件:statement suspended, wait error to be cleared。
查询MOS，确定是表空间不足引起impdp suspended
Statement Suspended, Wait Error To Be Cleared Wait Event [ID 761848.1]

Oracle Database provides a means for suspending, and later resuming, 
the execution of large database operations in the event of space allocation failures. 
This enables you to take corrective action instead of the Oracle Database server returning an error to the user. 
After the error condition is corrected, the suspended operation automatically resumes. 
This feature is called resumable space allocation. The statements that are affected are called resumable statements. 
The time between suspending the execution till correction of the error is reported as 
"statement suspended, wait error to be cleared" wait event.

3、查看alert.log日志文件确认

[oracle@TestServer-RHAS-5 ~]$ cd /opt/oracle/admin/mcrm/bdump/
[oracle@TestServer-RHAS-5 bdump]$ tail -30 alert_mcrm.log 
Thu Nov 24 09:29:20 2011
create tablespace testb
datafile '/opt/oradata/mcrm/testb.dbf'
size 1500M autoextend on next 50M maxsize 2000M
Thu Nov 24 09:29:51 2011
Completed: create tablespace testb
datafile '/opt/oradata/mcrm/testb.dbf'
size 1500M autoextend on next 50M maxsize 2000M
Thu Nov 24 09:40:00 2011
The value (30) of MAXTRANS parameter ignored.
kupprdp: master process DM00 started with pid=111, OS id=23858
         to execute - SYS.KUPM$MCP.MAIN('SYS_IMPORT_SCHEMA_01', 'SYSTEM', 'KUPC$C_1_20111124094000', 'KUPC$S_1_20111124094000', 0);
kupprdp: worker process DW01 started with worker id=1, pid=112, OS id=23870
         to execute - SYS.KUPW$WORKER.MAIN('SYS_IMPORT_SCHEMA_01', 'SYSTEM');
Thu Nov 24 09:43:11 2011
statement in resumable session 'SYSTEM.SYS_IMPORT_SCHEMA_01.1' was suspended due to
    ORA-01652: unable to extend temp segment by 128 in tablespace TESTB
Thu Nov 24 10:00:45 2011
Thread 1 advanced to log sequence 4761 (LGWR switch)
  Current log# 3 seq# 4761 mem# 0: /opt/oradata/mcrm/redo03.log

4、查询TESTB表空间使用情况

SQL> select bytes/1024/1024,maxbytes/1024/1024,user_bytes/1024/1024 
  2  from dba_data_files where tablespace_name='TESTB';

BYTES/1024/1024 MAXBYTES/1024/1024 USER_BYTES/1024/1024
--------------- ------------------ --------------------
           2000               2000            1998.9375

5、解决问题

Thu Nov 24 10:04:21 2011
alter tablespace TESTB add datafile '/opt/oradata/mcrm/testb01.dbf' size 100m  autoextend on next 1m maxsize 30g
Thu Nov 24 10:04:25 2011
Completed: alter tablespace TESTB add datafile '/opt/oradata/mcrm/testb01.dbf' size 100m  autoextend on next 1m maxsize 30g
Thu Nov 24 10:04:26 2011
statement in resumable session 'SYSTEM.SYS_IMPORT_SCHEMA_01.1' was resumed

通过这个日志可以看出，表空间不足的问题解决后（可以添加数据文件，或者resize数据文件大小），impdp的job又开始运行

发表在 Oracle性能优化 | 评论关闭

ORA-09968, ORA-01102 When Starting a Database

发表于 2011 年 11 月 23 日 由 惜分飞

一、网友错误
pub上网友遇到一个问题

Tue Nov 22 10:31:19 2011
ALTER DATABASE   MOUNT
Tue Nov 22 10:31:19 2011
sculkget: failed to lock /u01/app/oracle/product/10.2.01/db_1/dbs/lkORCL exclusive
sculkget: lock held by PID: 26308
Tue Nov 22 10:31:19 2011
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26308
Tue Nov 22 10:31:19 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

我给的建议是重启数据库解决，其实重启数据库是关闭了当前开启的实例，然后开启报错的实例，所以重启成功

二、错误重现

Tue Nov 22 10:31:19 2011
ALTER DATABASE   MOUNT
Tue Nov 22 10:31:19 2011
sculkget: failed to lock /u01/app/oracle/product/10.2.01/db_1/dbs/lkORCL exclusive
sculkget: lock held by PID: 26308
Tue Nov 22 10:31:19 2011
ORA-09968: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26308
Tue Nov 22 10:31:19 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...




[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 23 09:07:21 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter name ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
global_names                         boolean     FALSE
instance_name                        string      test
lock_name_space                      string
log_file_name_convert                string
service_names                        string      test
SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /opt/oracle/oradata/test/contr
                                                 ol01.ctl
SQL> create pfile='/tmp/t_pfile' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ vi /tmp/t_pfile 

*.__db_cache_size=67108864
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=117440512
*.__streams_pool_size=8388608
*.archive_lag_target=0
*.audit_file_dest='/opt/oracle/admin/test/adump'
*.background_dump_dest='/opt/oracle/admin/test/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/opt/oracle/oradata/test/control01.ctl'
*.core_dump_dest='/opt/oracle/admin/test/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/oracle/oradata/test/archivelog'
*.open_cursors=1000
*.pga_aggregate_target=66060288
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/test/udump'
~
~

"/tmp/t_pfile" 28L, 1043C written
[oracle@ECP-UC-DB1 ~]$ export ORACLE_SID=tt1
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 23 09:10:47 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup pfile='/tmp/t_pfile' mount;
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             134219808 bytes
Database Buffers           67108864 bytes
Redo Buffers                6303744 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ more /opt/oracle/admin/test/bdump/alert_tt1.log 
Wed Nov 23 09:11:26 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  __shared_pool_size       = 117440512
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 8388608
  sga_target               = 209715200
  control_files            = /opt/oracle/oradata/test/control01.ctl
  db_block_size            = 8192
  __db_cache_size          = 67108864
  compatible               = 10.2.0.3.0
  log_archive_dest_1       = location=/opt/oracle/oradata/test/archivelog
  archive_lag_target       = 0
  db_file_multiblock_read_count= 16
  db_recovery_file_dest    = /opt/oracle/flash_recovery_area
  db_recovery_file_dest_size= 2147483648
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                = 
  dispatchers              = (PROTOCOL=TCP) (SERVICE=testXDB)
  job_queue_processes      = 10
  background_dump_dest     = /opt/oracle/admin/test/bdump
  user_dump_dest           = /opt/oracle/admin/test/udump
  core_dump_dest           = /opt/oracle/admin/test/cdump
  audit_file_dest          = /opt/oracle/admin/test/adump
  db_name                  = test
  open_cursors             = 1000
  pga_aggregate_target     = 66060288
PMON started with pid=2, OS id=28086
PSP0 started with pid=3, OS id=28088
MMAN started with pid=4, OS id=28090
DBW0 started with pid=5, OS id=28092
LGWR started with pid=6, OS id=28094
CKPT started with pid=7, OS id=28096
SMON started with pid=8, OS id=28098
RECO started with pid=9, OS id=28100
CJQ0 started with pid=10, OS id=28102
MMON started with pid=11, OS id=28104
Wed Nov 23 09:11:28 2011
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=28106
Wed Nov 23 09:11:28 2011
starting up 1 shared server(s) ...
Wed Nov 23 09:11:28 2011
ALTER DATABASE   MOUNT
Wed Nov 23 09:11:28 2011
sculkget: failed to lock /opt/oracle/product/10.2.0/db_1/dbs/lkTEST exclusive
sculkget: lock held by PID: 12339
Wed Nov 23 09:11:28 2011
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12339
Wed Nov 23 09:11:28 2011
ORA-1102 signalled during: ALTER DATABASE   MOUNT...

通过这个试验，再现了网友的ORA-09968, ORA-01102错误

三、MOS解释
ORA-09968, ORA-01102 When Starting a Database

发表在 ORA-xxxxx | 评论关闭

OGG Troubleshooting TCP/IP Errors In Open Systems

发表于 2011 年 11 月 22 日 由 惜分飞

经常和ogg打交道的同仁们，WARNING OGG-01223 TCP/IP error 111这个错误肯定不陌生，今天发现MOS上关于WARNING OGG-01223 TCP/IP error 111错误处理思路及其方案的文档写的非常好，看了之后深受启发，贡献出来给大家
WARNING OGG-01223 TCP/IP error 111
//www.xifenfei.com/wp-content/uploads/2011/11/WARNING_OGG-01223_TCPIP_error_111.pdf

发表在 GoldenGate | 评论关闭