在检查逻辑备份的时候，发现了一个schema备份未成功，在自己运维的RAC环境中, 只有两个实例。每一个实例下有4个schema，唯独这个天天被并行访问的schema产生异常。

点击(此处)折叠或打开

;;;
Export: Release 11.2.0.3.0 - Production on 星期一 12月 7 02:30:00 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
;;; 由于以下参数, 旧有模式处于活动状态:
;;; 旧有模式参数: "log=********20151207.log" 位置: Command Line, 替换为: "logfile=********20151207.log"
;;; 旧有模式设置了 reuse_dumpfiles=true 参数。
启动 "********"."SYS_EXPORT_SCHEMA_14": ********/******** DIRECTORY=dpdata DUMPFILE=********20151207.dmp SCHEMAS=******** EXCLUDE=TABLE:" LIKE('LOCATION%')" logfile=********20151207.log reuse_dumpfiles=true
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 49.35 GB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
处理对象类型 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/TRIGGER
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39097: 数据泵作业出现意外的错误 -12801
ORA-39065: MAIN 中出现意外的主进程异常错误
ORA-12801: 并行查询服务器 PZ99, instance inds-02:inds2 (2) 中发出错误信号
ORA-01460: 转换请求无法实施或不合理
作业 "********"."SYS_EXPORT_SCHEMA_14" 因致命错误于 02:35:37 停止
查询了下资料， RAC的双节点的 PARALLEL_DEGREE_POLICY 也都是manual的
SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter PARALLEL_DEGREE_POLICY;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
parallel_degree_policy string MANUAL
SQL>

无法备份成功的原因，官方给出的解释

点击(此处)折叠或打开

Changes
The fix for Bug 9375300 is applied (this fix is included starting with 11.2.0.2 PSU 5 and in 11.2.0.3).
The workaround from:
Note 1427537.1 - DataPump Import (IMPDP) Fails With Errors ORA-39097 ORA-39065 ORA-12801 ORA-1460 In RAC Environment
to set PARALLEL_DEGREE_POLICY=MANUAL in all instances was used without success.
Cause
The errors are caused by:
Bug 13099577 - ORA-1460 with parallel query
This is a regression introduced in 11.2.0.2.5 and 11.2.0.3 by the fix for Bug 9375300
The bug is fixed a.o. fixed in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
For the above and other fixed versions, refer to:
Please also refer to
Note 13099577.8 - Bug 13099577 - ora-12801 and ORA-1460 with parallel query

运维的RAC 环境是通过修改参数值PARALLEL_FORCE_LOCAL解决的，也可以通过PSU方式解决

点击(此处)折叠或打开

A possible workaround is to set (in all instances):
PARALLEL_FORCE_LOCAL=TRUE
By setting PARALLEL_FORCE_LOCAL to TRUE, the parallel server processes are restricted.
This way, they can only operate on the same RAC node where the DW coordinator process resides.

点击(此处)折叠或打开

SQL> alter system set parallel_force_local=true;
系统已更改。
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean TRUE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 985
parallel_min_percent integer 0
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 512
parallel_threads_per_cpu integer 2
recovery_parallelism

在负责RAC数据库中的解决方法：

点击(此处)折叠或打开

alter system set parallel_force_local=FALSE;
expdp XXX/XXX@DS133 DIRECTORY=dpdata DUMPFILE=XXX`date +'%Y%m%d'`.dmp PARALLEL=1 CLUSTER=N SCHEMAS=XXX EXCLUDE=TABLE:\" LIKE$\'LOCATION%\'$\" log=XXX`date +'%Y%m%d'`.log

ITPub博客

一个schema备份日志发现ORA-39097,ORA-39065,ORA-12801,ORA-01460 异常

最新文章