【expdp/impdp】 ORA-06502、ORA-39077 修復補丁安裝與回退操作

expdp/impdp操作報錯資訊如下：

    ORA-31626: job does not exist

    ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

    ORA-06512: at "SYS.KUPV$FT_INT", line 428

    ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

    ORA-06512: at "SYS.KUPC$QUE_INT", line 250

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

      錯誤資訊分析為datapump元件中sequence數值大於6位數導致。參考MOS文件1550344.1分析，此錯誤由oracle Bug 16473783導致,在Oracle 12.2版本中被修復。建議安裝Patch 16928674修復此bug。

安裝環境：
OS:RHEL 5.5 x86 64bit
Oracle版本:11.2.0.3
Opatch版本：p6880880
Patch版本：Patch 16928674

補丁安裝操作

1.檢視當前opatch版本

[oracle@ENMOEDU dmp]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/

[oracle@ENMOEDU OPatch]$ ./opatch version

Invoking OPatch 11.2.0.1.7

OPatch Version: 11.2.0.1.7

OPatch succeeded.

2.上傳最新Opath到$ORACLE_HOME目錄

[oracle@ENMOEDU dbhome_1]$ ls -trl

-rw-r--r--  1 root   root     32995358 Aug  8 11:17 p6880880_112000_Linux-x86-64.zip

3.備份原Opath目錄

[oracle@ENMOEDU dbhome_1]$ mv OPatch/ OPatch.bak

4.解壓最新Opath

[oracle@ENMOEDU dbhome_1]$ unzip p6880880_112000_Linux-x86-64.zip

5.檢視opatch版本

[oracle@ENMOEDU dbhome_1]$ cd OPatch

[oracle@ENMOEDU OPatch]$ ./opatch version

OPatch Version: 11.2.0.3.5

OPatch succeeded.

6.上傳補丁

[oracle@ENMOEDU tmp]$ cd /u01/

[oracle@ENMOEDU u01]$ mkdir patch

[oracle@ENMOEDU u01]$ cd patch

[oracle@ENMOEDU patch]$ ls

p16928674_112030_Generic.zip

7.解壓補丁

[oracle@ENMOEDU patch]$ unzip p16928674_112030_Generic.zip

8.檢查補丁

[oracle@ENMOEDU patch]$ ls

16928674  p16928674_112030_Generic.zip

[oracle@ENMOEDU patch]$ cd 16928674/

[oracle@ENMOEDU16928674]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_11-26-35AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

9.安裝補丁

[oracle@ENMOEDU 16928674]$ $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_11_28_27/apply2014-08-08_11-28-27AM_1.log

Applying interim patch '16928674' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Verifying environment and performing prerequisite checks...

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y

Backing up files...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Verifying the update...

Patch 16928674 successfully applied

Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_11_28_27/apply2014-08-08_11-28-27AM_1.log

OPatch succeeded.

10.檢查安裝結果

[oracle@ENMOEDU 16928674]$ $ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_11-30-08AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-08-08_11-30-08AM.txt

----------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  16928674     : applied on Fri Aug 08 11:28:51 CST 2014

Unique Patch ID:  17483843

   Created on 2 Apr 2014, 04:20:57 hrs PST8PDT

   Bugs fixed:

     16928674

----------------------------------------------------------------------------

OPatch succeeded.

11.檢視補丁執行指令碼路徑

[oracle@ENMOEDU 16928674]$ ls

etc  files  postinstall.sql  README.txt

[oracle@ENMOEDU 16928674]$ pwd

/u01/patch/16928674

12.執行補丁指令碼

[oracle@ENMOEDU OPatch]$ sqlplus / as sysdba

SQL> @/u01/patch/16928674/postinstall

Calling rdbms/admin/prvtbpci.plb on 08-AUG-14 11.34.01.504642 AM +08:00

Package body created.

Package body created.

補丁回退操作

1.檢視補丁狀態

[oracle@ENMOEDU OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_15-17-56PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-08-08_15-17-56PM.txt

----------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  16928674     : applied on Fri Aug 08 11:28:51 CST 2014

Unique Patch ID:  17483843

   Created on 2 Apr 2014, 04:20:57 hrs PST8PDT

   Bugs fixed:

     16928674

---------------------------------------------------------------------------

2.執行回退操作

[oracle@ENMOEDU OPatch]$ ./opatch rollback -id 16928674

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_15_57_17/rollback2014-08-08_15-57-17PM_1.log

RollbackSession rolling back interim patch '16928674' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

RollbackSession removing interim patch '16928674' from inventory

Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/16928674_Aug_08_2014_15_57_17/rollback2014-08-08_15-57-17PM_1.log

OPatch succeeded.

3.檢視補丁狀態

[oracle@ENMOEDU OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.5

Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version    : 11.2.0.3.5

OUI version       : 11.2.0.3.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-08-08_15-59-13PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-08-08_15-59-13PM.txt

----------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

----------------------------------------------------------------------------

OPatch succeeded.

4.執行回退指令碼

[oracle@ENMOEDU OPatch]$ sqlplus / as sysdba

SQL> @/u01/patch/16928674/postinstall

Calling rdbms/admin/prvtbpci.plb on 08-AUG-14 04.05.47.030762 PM +08:00

Package body created.

Package body created.



----------------end----------------
            
                DBA_建瑾
                2014.8.9