2012年 – 第6页 – Oracle and Linux

删除undo表空间遇到ORA-30013及ORA-01548的解决思路

2月 10,2012 留下评论 Oracle

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时，遭遇ORA-30013及ORA-01548的错误，下面记录过程及解决方法。

1 创建新的UNDO表空间UNDOTBS3，并将当前实例的UNDO切换到UNDOTBS3之后，删除旧的UNDOTBS1时，遇到下述错误：

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

2 从上可以看到UNDOTBS1目前正在被使用。查询MetaLink,Unable to Drop Undo Tablespace ORA-30013 [ID 835944.1]获取基本思路，依据该文档给出的提示，执行下述命令：

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs 
  2  where tablespace_name='UNDOTBS1' and status = 'ONLINE';

SEGMENT_NAME     OWNER  TABLESPACE_NAME    STATUS
---------------- ------ ------------------ ---------
_SYSSMU2$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU3$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU6$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU8$        PUBLIC UNDOTBS1           ONLINE
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.

3 从上步的结果，结合文档ID 835944.1，基本上可以找到问题的原因：当前数据库中有未结束的分布式事务，而这些未结束的分布式事务占用了UNDOTBS1，最终导致不能删除UNDOTBS1。文档ID 835944.1给出的解决方案是结束掉这些分布式事务。MetaLink上给出的思路是参照 Note 401302.1文档，而不凑巧的是，现在这篇文档无法查看，不得已，尝试重启数据库并删除UNDOTBS1。重启之后，alert日志里看到如下错误信息：

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-10 11:27:15.527
*** SESSION ID:(431.1) 2012-02-10 11:27:15.527
*** 2012-02-10 11:27:15.527
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:27:50.676
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:28:42.707
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found

尝试再次删除UNDOTBS1：

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

这次报出ORA-01548的错误！！！原来还是分布式事务未提交导致的：

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.
SQL>

4 继续MetaLink：ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]根据该文档，执行下述命令：

SQL> Select segment_id,segment_name,status,tablespace_name
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         2 _SYSSMU2$                      PARTLY AVAILABLE UNDOTBS1
         3 _SYSSMU3$                      PARTLY AVAILABLE UNDOTBS1
         6 _SYSSMU6$                      PARTLY AVAILABLE UNDOTBS1
         8 _SYSSMU8$                      PARTLY AVAILABLE UNDOTBS1
SQL>

You find you have segments that are 'Partly Available' This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. 当回滚段状态为Partly Available时，说明还是有事务没结束！！！

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn
  7  in(2,3,6,8);

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL>

通过上面的结果，看到2、3、6、8号回滚段上有活动的事务。该文档依然指出解决方案是查看Note 401302.1文档，而该文档又无法打开，不得已Google之，参照 http://blog.itpub.net/post/38439/477038 获得解决问题的最终方法。

5 根据http://blog.itpub.net/post/38439/477038 直接强制提交这些活动的分布式事务：

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '2.38.583286';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '8.7.957413';

Commit complete.

SQL> commit force '3.29.982959';

Commit complete.

SQL> commit force '6.14.945326';

Commit complete.

SQL> commit force '13.19.507098';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             forced commit
3.29.982959            forced commit
6.14.945326            forced commit
13.19.507098           forced commit

6 rows selected.

SQL> Select segment_id,segment_name,status,tablespace_name           
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

no rows selected

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 
  2  KTUXESTA Status,                                          
  3  KTUXECFL Flags                                            
  4  FROM x$ktuxe                                              
  5  WHERE ktuxesta!='INACTIVE'                                
  6  AND ktuxeusn                                              
  7  in(2,3,6,8);                                              

no rows selected

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

6 终于干掉了那个旧的UNDO，而此时，alert日志里的信息如下：

Fri Feb 10 15:08:16 CST 2012
DISTRIB TRAN 44444444.D7D4863A714B974489CD48496956271900000000
  is local tran 2.38.583286 (hex=02.26.8e676)
  change pending prepared tran, scn=125560421 (hex=0.077be665)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:08:56 CST 2012
DISTRIB TRAN 44444444.D88A6D59B486E44D8BAD8DABFDCF289C00000000
  is local tran 8.7.957413 (hex=08.07.e9be5)
  change pending prepared tran, scn=194136242 (hex=0.0b9248b2)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:08 CST 2012
DISTRIB TRAN 44444444.4A67F0F3F3EA464081883577EE646AAB00000000
  is local tran 3.29.982959 (hex=03.1d.effaf)
  change pending prepared tran, scn=195270309 (hex=0.0ba396a5)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:20 CST 2012
DISTRIB TRAN 44444444.97CB87A6BAE9E943B761C9C7FDA9844600000000
  is local tran 6.14.945326 (hex=06.0e.e6cae)
  change pending prepared tran, scn=196753377 (hex=0.0bba37e1)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:36 CST 2012
DISTRIB TRAN 44444444.192AEFF6D316B2468F5D74FE5EBDC9EC00000000
  is local tran 13.19.507098 (hex=0d.13.7bcda)
  change pending prepared tran, scn=332059676 (hex=0.13cad41c)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:13:17 CST 2012
drop tablespace undotbs1 including contents and datafiles
Fri Feb 10 15:13:25 CST 2012
Deleted Oracle managed file +ORADATA/glndb/datafile/undotbs1.261.726057859
Completed: drop tablespace undotbs1 including contents and datafiles

7 总结：对于分布式事务，目前还不是很清楚。而这个案例中涉及到的情况还有可能出现更为复杂的情况，需要深入研究一下，而我遇到的这种情况属于比较简单的。

删除用户报ORA-24005错误及解决办法

2月 9,2012 有1条评论 Oracle

在一套10.2.0.5.0的双节点RAC数据库上，删除用户时报出ORA-00604及ORA-24005的错误：

 
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> drop user gdhytest cascade;
drop user gdhytest cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
SQL>

后经查找MetaLink：Ora-24005 Error Trying To Drop User Sysman Cascade [ID 456437.1] 找到原因：被删除的用户ghhytest拥有queue table。

 
SQL> set pagesize 100 
SQL> col object_name format a40 
SQL> select object_name,object_type from dba_objects
  2  where owner='GDHYTEST' AND OBJECT_NAME LIKE '%AQ%'
  3  ;
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
AQ$_GPSSTATUS_QUEUE_TABLE_H              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_I              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T              TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR              TABLE
SQL>

解决方法：

1 gdhytest登录数据库，执行DBMS_AQADM.DROP_QUEUE_TABLE进行删除queue talbe:

 
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2 发现权限不够，赋权，重新删除：

 
SQL> conn / as sysdba;
Connected.
SQL> grant dba to gdhytest;
Grant succeeded.
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24
characters
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE GDHYTEST.GPSSTATUS_QUEUE_TABLE_H does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1

3 依然报错！！！QUEUE_TABLE too long,不得已，重命名queue table进行删除：

 
SQL> rename  AQ$_GPSSTATUS_QUEUE_TABLE_H to queue1;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
QUEUE1                         TABLE
7 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE1',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
6 rows selected.
SQL>

4 如法炮制，重命名其它queue table,然后执行删除：

 
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_I to queue_a;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_NR to queue_b;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_T to queue_c;
Table renamed.
SQL> rename aq$_GPS_TEMP_QUEUE_TABLE_NR to queue_d;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_60452             TABLE
SYS_IOT_OVER_60459             TABLE
QUEUE_B                        TABLE
QUEUE_A                        TABLE
QUEUE_C                        TABLE
QUEUE_D                        TABLE
6 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_A',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_B',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_C',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_D',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
no rows selected

5 最后彻底删除gdhytest用户：

 
SQL> conn / as sysdba;
Connected.
SQL> drop user gdhytest cascade;
User dropped.
SQL>

调试存储过程：ORA-0131 Insufficient privileges 处理

2月 7,2012 留下评论 Oracle

昨天，一开发同事反映说在PL/SQL Developer工具里无法调试存储过程，报错信息如下：

ORA-0131:Insufficient privileges.

Note:Debugging requires the DEBUG CONNECT SESSION system privileges.

后经查找，是缺失 DEBUG CONNECT SESSION 系统权限所致。

解决办法：以SYS用户登录数据库，执行赋权操作：

SQL> grant  DEBUG CONNECT SESSION to user_name;

附1：有网友指出还需赋予DEBUG ANY PROCEDURE的权限，经测试，该权限可不用赋予！

附2：可以从数据字典role_sys_privs表查看该权限相关信息：

SQL> conn / as sysdba;
Connected.
SQL> select * from role_sys_privs where privilege like 'DEBUG%' order by 2;
ROLE             PRIVILEGE                ADM
---------------- ------------------------ ---
DBA              DEBUG ANY PROCEDURE      YES
JAVADEBUGPRIV    DEBUG ANY PROCEDURE      NO
DBA              DEBUG CONNECT SESSION    YES
JAVADEBUGPRIV    DEBUG CONNECT SESSION    NO

简单记录，以作备忘！

知识还是点点记录好！

impdp ORA-31655错误处理一例

2月 3,2012 留下评论 IMPDP, Oracle

今天上午，收到开发同事发过来的邮件：

需要将从之前用EXPDP备份的dump文件中，将某张表还原到一个新的schema下。

电话沟通后，原来是想要将逻辑备份的dump文件中FR8_ZH这个用户下的SB_DATA_RIGHT，导入到同库下的FR8_TEST9这个schema下。

① 于是开始干活：

C:\Documents and Settings\Administrator>impdp directory=my_dump dumpfile=2012-01 -23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIGHT
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:32:53
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
ORA-31655: 尚未为作业选择数据或元数据对象 
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"
 启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S B_DATA_RIGHT 
作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:33:19 成功完成

发现，报出ORA-31655的错误！！！

而

SQL> !oerr ora 31655
 31655, 00000, "no data or metadata objects selected for job" 
// *Cause:  After the job parameters and filters were applied, 
//          the job specified by the user did not reference any objects. 
// *Action: Verify that the mode of the job specified objects to be moved. 
//          For command line clients, verify that the INCLUDE, EXCLUDE and 
//          CONTENT parameters were correctly set.  For DBMS_DATAPUMP API 
//          users, verify that the metadata filters, data filters, and 
//          parameters that were supplied on the job were correctly set.
SQL>

而ORA-31655的错误是说，在导入数据的命令中，impdp没有找到正确的对象元数据。

② 加上INCLUDE关键字，重新执行导入：

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012- 01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIG HT include=table:\"like \'SB_DATA_RIGHT%\'\"
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:36:35
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
ORA-31655: 尚未为作业选择数据或元数据对象 已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01" 
启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S B_DATA_RIGHT include=table:"like \'SB_DATA_RIGHT%\'
" 作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:37:02 成功完成

发现依然报ORA-31655的错误！

③ 不得已，查找Metalink，ORA-31655 On imdp As a Privileged User Wih INCLUDE= After Upgrade To 10.2.0.5/11.2 [ID 1225108.1] 得到下述的解决办法：

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later [Release: 10.2 and later ]
Oracle Server – Enterprise Edition – Version: 11.2.0.1.0 to 11.2.0.2.0 [Release: 11.2 to 11.2]
Information in this document applies to any platform.

Symptoms

Datapump import raises an ORA-31655 “no data or metadata objects selected for job” on import when using the REMAP_SCHEMA and INCLUDE= clauses when performed as a privileged user (i.e. a user with the IMP_FULL_DATABASE role).

Changes

The database has been upgraded to 10.2.0.5 or 11.2.

Cause

This is intended behavior and occurs due to the fix for bug:6831823 which went in to 11.2 and 10.2.0.5.

The ORA-31655 error is now signaled following the fix for bug:6831823, as that bug which existed in previous versions meant that if a privileged user performed a table import from a full export dump, and the specified table name existed in multiple schemas, then all the tables would be imported where as only the table for the importing user should have been imported, as documented in the Oracle� Database Utilities 10g Release 2 (10.2) manual, chapter 3 ‘Data Pump Import’, under the definition of the TABLES= clause, which states “If you do not supply a schema_name, it defaults to that of the current user”. This also applies to the INCLUDE= clause, and hence the error is now raised.

Documentation bug:10140472 has been created to get this more clearly stated under the INCLUDE and REMAP_SCHEMA options in future documentation sets.

Solution

Perform the import using the TABLES= clause rather than the INCLUDE=TABLE: clause, e.g.:

Change From:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

To:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test tables=SCOTT.EMP,SCOTT.DEPT

impdp scott directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEPT\’\)\”

找到问题的原因后，执行下述命令重新导入成功！

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012- 01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=fr8_zh.sb_d ata_right
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:48:02
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01" 启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=f r8_zh.sb_data_right 
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE 
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA 
. . 导入了 "FR8_TEST9"."SB_DATA_RIGHT"                 5.328 MB    4484 行 
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX 
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT 
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
 作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:48:49 成功完成

最后，登录数据库后，检查FR8_TEST9用户下的SB_DATA_RIGHT表中的数据，发现一切正常。

解决ORA-12516错误一则

1月 30,2012 有1条评论 Oracle

在上周五晚上通宵加班将一套10.2.0.5.0的Linux 虚拟机环境下的数据量为260GB 的双节点RAC数据库顺利迁移至一台物理机器的开发数据库后，这两天开发的同事反映说连不上物理机开发库了。

起初，我也没有太在意，的确是因为这两天公司内部网络不太正常，ping物理开发库延时比较严重，偶有timed out现象，就一直以为是网络的问题了。直到中午的时候，同事说网络基本正常了，用PL/SQL Developer客户端工具连数据库的时候报出Ora-12516的错误，这才引起了我的注意！

上Metalink查了一下，看了“Troubleshooting Guide TNS-12519 TNS-12516 ORA-12519 ORA-12516 [ID 552765.1]”的文章后，才知道了自己迁移数据库后粗心大意犯下的错误。原来的RAC数据库中每个实例中都将process初始化参数都设置了400，sessions=445，而现在是单台数据库对外提供服务，导致会话数不够用，最终导致的Ora-12516的错误！其实，对于这种开发库而言，公司的开发同事并不多，怎么可能导致445个会话还不够连接使用呢？其实，造成问题的最根本原因是开发人员的应用程序中的连接池配置的有问题，连接数配置过高导致的！！！！

找到了问题的基本原因后，就将process初始化参数从400改为600，进而sessions自动被置为665，transactions参数自动置为731。然后，重启数据库。

在oracle数据库的初始化参数中，有一类参数是推倒参数，其中：

sessions=1.1*processes+5,transactions=1.1*sessions.

这样，问题得到了基本的解决。

事后，又出现了一些不痛不痒的问题。

这不，年后简单记录下发生在这个春节前的种种问题，及解决问题的方法：

① 会话数不够用，导致Ora-12516的错误。解决方法，加大process初始化参数，或者通知修改应用程序中的连接数；

② 通过在数据库端配置profile来控制每个会话的活动时间，过期由数据库自动断开会话；

③ 通过使用共享服务器模式来控制数据库服务器端的进程资源；

④ 最头大的问题就是，有个开发的同事将自己PC机器的IP地址设置了同数据库服务器相同的IP地址，导致其他开发人员一直连接不上数据库！！！这个问题可不是第一次遇见，解决办法，协同网络管理员将数据库服务器IP绑定到MAC。

其实，这本来是去年春节前遇到的一则案例，后来由于太忙，赶着要回家，于是拖到了今天才发布出来！

记生平第一次跑完马拉松全程比赛

1月 16,2012 留下评论 Life

时间：2012年1月7日

地点：厦门

人物：本人及所有参赛运动员外加广大热心观众

事件：生平第一次参加国际马拉松全程邀请赛

比赛路线图：单击查看

成绩：6时36分12秒

概要：本来应该是早上8点准时开赛的，由于自己没能提前进场，耽误了10分钟，结果8:10分开始比赛。前半程跑起来感觉比较轻松，后半程，尤其是22公里到24公里之间，感觉身体极其疲惫，每迈一步都觉得很吃力，几度想放弃，又几度坚持下来了，过了这两公里的疲劳期之后，感觉身体又缓解了很多，不停地补充能量、水分，慢慢的边跑边走，熬到了30公里处，觉得舒服了很多，想想剩下的10多公里不就是一咬牙的事儿嘛。等看到32公里的标示牌时，那叫一个胜利就在前方不远处！就这样，坚持着，坚持着，不停给自己鼓劲，不放弃，最终于下午2点46分12秒到达厦门国际会议展览中心的全程马拉松终点！

赛后回顾：等跑完全程下来，觉得42.195公里的全程马拉松不过如此而已！难就难在坚持，其实，也就是一咬牙的事儿！虽然我花了6个多小时的时间，那么是否能跑出更好的成绩呢，我给自己的答案是明年厦门国际马拉松全程赛场见！

遗憾：跑完全程下来，让同事帮我到赛委会领取成绩证书，最后，却弄丢了证书。

厦门马拉松官方网站：http://www.xmim.org/cn/

延伸阅读：马拉松的故事，以下内容摘自网络：

马拉松与一场战争有关，马拉松原是一个地名，是波希战争的主战场。现在的马拉松的标准距离是马拉松平原到雅典中心广场的距离——42195米。古代波斯帝国有个名叫大流士一世的国王，到公元前6世纪末，他已经征服了周围许多国家和地区，那时候，波斯军队攻占了东起印度河流域，西至撒哈拉大沙漠的广大地区。剩下最后一个侵略目标就是隔海相望的希腊半岛了。

    公元前500年，希腊人建在小亚细亚西南靠近爱琴海边的米利都城邦，率先起来进行摆脱波斯人统治的斗争。雅典应米利都的请求，与另一城邦爱勒特里亚一起派出25艘战船前往援助。希腊人很快攻陷了波斯在小亚细亚的总督府萨底斯，并放火把它烧了。但终因敌不过强大的波斯军队，米利都最后还是失败了。当初，雅典派战船支援米利都的消息传到波斯都城苏萨时，大流士一世十分震惊。他搭弓向西射了一箭，发誓要向雅典人报仇雪恨。他甚至命令奴仆，在他每次吃饭前都要大呼三声：“皇上！记住雅典人！”
    公元前492年，波斯海陆大军在大流士一世的女婿的率领下，第一次进攻希腊本土。不料，这次出师不利，舰队在阿陀斯海角遭遇大风暴，没有踏入希腊本土。
    大流士一方面继续备战，另方面派使者到希腊各城邦去索取土和水，试探希腊各城邦对波斯的态度，妄想采用外交恐吓的卑劣手段降服希腊人。半岛北部的一些城邦害怕强大的波斯，被迫照做了。只有南部的雅典和斯巴达等城邦顶住压力，坚决拒绝投降。斯巴达人把大流士的使者拖到一口井的旁边，指着井口说：“井里有水又有土，要多少就随便拿吧！”说完，把他投进了井里。
    大流士被彻底地激怒了。他决定派有丰富作战经验的将领率军第二次远征希腊。公元前490年，波斯军横渡爱琴海，波斯军一举攻占了爱勒特里亚。接着，波斯军向南开进，杀向雅典。他们在雅典东北60公里的马拉松平原登陆，打算一举攻占这片土地。波斯军一旦攻占了马拉松，沿着一条大道向东翻越一个山丘，步行几十公里，就可抵达雅典城。
大军压境，雅典面临亡国的威胁。情急之下，雅典人派有名的长跑好手斐利皮德斯到斯巴达请求援兵。可斯巴达推辞说，他们正在过一种节日，按习惯必须等到月亮圆时，才能出兵。
    如果坐等援军，就只有死路一条。雅典人只得依靠自己的力量，保卫自己的国家。他们动员了所有公民，征集到一万名重装兵。此外，还得到普拉提亚派来的一千名援军。雅典军队只有波斯的十分之一，力量对比十分悬殊。在这种不利的形势下，雅典军统帅米太亚得决定不与波斯人硬拼，把所有的士兵集中到马拉松，列成方阵。其中精锐兵力布署在两侧，正面中间部分的兵力相对薄弱。两军接触后，波斯军又施用中间突破的老办法，迫使雅典方阵里的中军向后退却。待气势汹汹的波斯军队追赶雅典中军时，雅典方阵两侧的精锐兵力以迅雷不及掩耳之势，杀向已经拉得很长的波斯军队。毫无准备的波斯军队猝不及防，立即乱了阵容，纷纷逃向海上的战船。雅典军又转向后方与中军联合，围歼了波斯中军，取得了马拉松战役的胜利，共歼灭波斯军6000多人，而雅典仅损失了192名士兵。
    为了把胜利的喜讯尽快地传到雅典城里，米太亚得派跑得快的斐利皮德斯去完成这一任务。斐利皮德斯带着创伤和打仗的疲劳，立即往回飞跑。他以惊人的毅力，一口气跑了40多公里。抵达雅典城中央广场时，他只喊了声：“庆祝吧！雅典得救了！”就倒在地上牺牲了。
    后人为了纪念这件事，决定1896年在雅典举行的第一届奥林匹克运动会上，新设一个竞赛项目，叫马拉松赛跑，距离以当年斐利皮德斯跑过的路程为准。这之后的几届奥运会，马拉松赛跑的距离一直没有统一。1920年，第七届奥运会前夕，人们重新测量了从马拉松到雅典中央广场的距离，才正式定为42公里195米。
     这就是马拉松赛跑的来历。

Archive 2012年2月10日

Applies to:

Symptoms

Changes

Cause

Solution