分类目录归档：ORA-xxxxx

expdp遭遇ORA-39006/ORA-39213故障解决

发表于 2012 年 10 月 31 日 由 惜分飞

expdp导出数据遇到ORA-39006/ORA-39213错误,通过执行执行dbms_metadata_util.load_stylesheets解决
expdp工作异常

--导出awr信息
SQL> @?/rdbms/admin/awrextr.sql
…………
Exception encountered in AWR_EXTRACT
ORA-39006: internal error
ORA-39213: Metadata processing is not available
begin
*
ERROR at line 1:
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 911
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4710
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 656
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 962
ORA-06512: at line 3

--导出一个表
$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 13:03:20

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

错误提示

$ oerr ora 39006
39006, 00000, "internal error"
// *Cause:  An unexpected error occurred while processing a Data Pump job.
//          Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS 
//          will further describe the error.
// *Action: Contact Oracle Customer Support.
$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause:  The Data Pump could not use the Metadata API.  Typically,
//          this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
//          to reload the stylesheets.

解决ORA-39006/ORA-39213问题

--查询数据库已经安装组件
SQL> col COMP_NAME for a35
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                           VERSION                        STATUS
----------------------------------- ------------------------------ ----------------------
Oracle Database Catalog Views       10.2.0.1.0                     VALID
Oracle Database Packages and Types  10.2.0.1.0                     VALID
Oracle Workspace Manager            10.2.0.1.0                     VALID
JServer JAVA Virtual Machine        10.2.0.1.0                     VALID
Oracle XDK                          10.2.0.1.0                     VALID
Oracle Database Java Packages       10.2.0.1.0                     VALID
Oracle Expression Filter            10.2.0.1.0                     VALID
Oracle Data Mining                  10.2.0.1.0                     VALID
Oracle Text                         10.2.0.1.0                     VALID
Oracle XML Database                 10.2.0.1.0                     VALID
Oracle Rules Manager                10.2.0.1.0                     VALID
Oracle interMedia                   10.2.0.1.0                     VALID
OLAP Analytic Workspace             10.2.0.1.0                     VALID
Oracle OLAP API                     10.2.0.1.0                     VALID
OLAP Catalog                        10.2.0.1.0                     VALID
Spatial                             10.2.0.1.0                     VALID
Oracle Enterprise Manager           10.2.0.1.0                     VALID

17 rows selected.

--如果缺少下面组件,使用下面对应的程序安装
Oracle Database Catalog Views
Oracle Database Packages and Types 
JServer JAVA Virtual Machine
Oracle XDK    
Oracle Database Java Packages

--使用下面脚本安装（根据组件选择）
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
 
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql
 
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql
 
SQL> connect / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

--执行sys.dbms_metadata_util.load_stylesheets
SQL> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

测试expdp导出

$ expdp "'/ as sysdba'" dumpfile=xifenfei.dmp tables=scott.t_xifenfei  Directory=AWR_DIR

Export: Release 10.2.0.1.0 - 64bit Production on Wednesday, 31 October, 2012 14:18:04

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' dumpfile=xifenfei.dmp 
tables=scott.t_xifenfei Directory=AWR_DIR 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 7 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."T_XIFENFEI"                        5.374 MB   57376 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /data/enmotech/xifenfei.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:18:11

测试证明，在不缺少相关组件的情况下,使用dbms_metadata_util.load_stylesheets可以解决expdp导出报ORA-39006/ORA-39213错误；如果缺少组件,需要先安装对应组件,然后再执行dbms_metadata_util.load_stylesheets解决该问题

发表在 ORA-xxxxx, Oracle | 标签为 , | 3 条评论

rman备份出现ORA-19625/ORA-27054解决

发表于 2012 年 10 月 25 日 由 惜分飞

RAC环境NFS挂载归档日志使用rman备份出现ORA-19625/ORA-27054错误分析
系统运行环境

OS:AIX 6100-06
DB:11.1.0.6.0 RAC
归档：挂载NFS

rman执行archive log时候报错

sql statement: alter system archive log  current
Starting backup at 25-OCT-11
current log archived
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/17/2012 13:03:51
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /rarchlogA/1_13775_764866137.dbf
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6

这里由于RAC存放归档使用了NFS文件系统,在使用rman备份归档日志执行alter system archive log current的时候发生如下错误.

相关目录挂载情况

$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
…………
/dev/lv_arch      50.00     43.24   14%      166     1% /arch1
oradb2:/arch2     50.00     35.31   30%      463     1% /arch2
/dev/baklv        90.00     83.82    7%       10     1% /backup


$ mount:
…………
        /dev/lv_oracle   /oracle          jfs2   Oct 14 11:27 rw,log=/dev8
         /dev/lv_arch     /arch1           jfs2   Oct 14 11:27 rw,log=/dev8
     oradb2   /arch2           /arch2           nfs3   Oct 14 11:47

通过这里可以知道,这里使用默认的参数挂载NFS,从而使得NFS在rman工作时候不能正常工作。

错误原因

From  Oracle 10G R2 , Oracle checks the options with which a NFS mount is mounted on the filesystem. 
and this is done to ensure that no corruption of the database can happen as incorrectly 
mounted NFS volumes can result in data corruption. 

There are no single set of NFS mount options that work across all Oracle platforms 
Please ensure that you have the proper mount options specified by the NAS vendor /Vendor user guide 


The exact checks used for an NFS mounted disk vary between platforms but in general 
the basic checks will include the following checks
 

a) The mount table (eg; /etc/mnttab) can be read to check the mount options 
b) The NFS mount is mounted with the "hard" option 
c) The mount options include rsize>=32768 and wsize>=32768 
d) For RAC environments, where NFS disks are supported, the "noac" mount option is used.

解决方案
1.临时解决方案
As suggested in the bug the workaround recommended is to use the Event 10298.
alter system set events ’10298 trace name context forever, level 32′;

2.永久解决方案
具体见：//www.xifenfei.com/3269.html

发表在 ORA-xxxxx | 评论关闭

ORACLE 11.2.0.3 生成awr html文件报SYS.DBMS_WORKLOAD_REPOSITORY异常

发表于 2012 年 10 月 22 日 由 惜分飞

在想分析数据库性能的关键时刻,突然发现awr不能正常的工作,那就和你上了战场突然发现枪没有子弹一样的郁闷,今天就遇到了11.2.0.3在win的环境中awr生成html不能正常工作.通过查询mos发现该问题出现在各种平台中(win,linux,aix等),提醒大家注意该问题.
数据库版本

SQL> SELECT * FROM V$VERSION;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

awr报错(html)

SQL> @?/rdbms/admin/awrrpt.sql
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: 在 line 1

设置errorstack

SQL> alter session set events '6502 trace name errorstack level 12';

会话已更改。

分析错误

----- Error Stack Dump -----
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
----- Current SQL Statement for this session (sql_id=572fbaj0fdw2b) -----
select output from table(dbms_workload_repository.awr_report_html( :dbid,
                                                            :inst_num,
                                                            :bid, :eid,
                                                            :rpt_options ))


----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
94348684       919  package body SYS.DBMS_WORKLOAD_REPOSITORY
983BAD54         1  anonymous block
----- Call Stack Trace -----
_skdstdst()+121      CALLrel  _kgdsdst()           19D99520 2
_ksedst1()+93        CALLrel  _skdstdst()          19D99520 0 1 485816 4863B2
                                                   485816
_ksedst()+49         CALLrel  _ksedst1()           0 1
_dbkedDefDump()+368  CALLrel  _ksedst()            0
6                                                  
_ksedmp()+44         CALLrel  _dbkedDefDump()      C 0
_dbkdaKsdActDriver(  CALLreg  00000000             C
)+4209                                             
…………

通过查询mos发现Bug 13575143一致，可以确定是该bug,但是通过进一步测试证明不光是awrrpt会出现该错误,awr的相关报告中,只要是展示html结果的都有可能出现类此错误(比如awrrpti.sql/awrddrpt.sql/awrddrpi.sql等等).同时这里通过进一步分析发现其实该bug的起源是Bug 6458801(REPLACE on a CLOB can corrupt multibyte data ID 6458801.8),不过该bug说明已经在11.2.0.1中修复,其实通过这里的分析发现并没有真正的在11.2.0.3中修复该bug,针对该问题没有官方没有提供较好解决方法,只能是用过WORKAROUND来临时解决

They are able to generate the AWR report in the .txt format
发表在 ORA-xxxxx | 标签为 , | 3 条评论