Oracle 在RAC 数据库的datapatch失败，显示错误:”PLS-00201 , ORA-01109 & ORA-01219″

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]

ORA-01109

oerr ora 1109
01109, 00000, "database not open"
// *Cause: A command was attempted that requires the database to be open.
// *Action: Open the database and try the command again

ORA-01219

[oracle@ocm_test01 dul1]$ oerr ora 1219
01219, 00000, "database not open: queries allowed on fixed tables/views only"
// *Cause: A query was issued against an object not recognized as a fixed
// table or fixed view before the database has been opened.
// *Action: Re-phrase the query to include only fixed objects, or open the
// database.

适用于：

Oracle Database – Enterprise Edition – 版本 12.1.0.2 及以上
本文信息适用于任何平台。

症状

在RAC数据库的datapatch 失败，显示错误：

./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 on Thu Oct 29 16:27:18 2015
Copyright (c) 2015, Oracle. All rights reserved.
Log file for this invocation: /u01/app/product/cfgtoollogs/sqlpatch/sqlpatch_17613_2015_10_29_16_27_18/sqlpatch_invocation.log
..
Connecting to database…OK
注: Datapatch仅会对在open状态的PDB应用或回滚SQL 修正，没有补丁会被应用于关闭的 PDB。
..
..
Bootstrapping registry and package to current versions…done
Error in bootstrap log /u01/app/product/cfgtoollogs/sqlpatch/sqlpatch_17613_2015_10_29_16_27_18/bootstrap_CDB_PDBSEED.log:
Error at line 7: PLS-00201: identifier ‘DBMS_REGISTRY.NOTHING_SCRIPT’ must be declared
Error at line 23: PLS-00201: identifier ‘DBMS_REGISTRY.NOTHING_SCRIPT’ must be declared
Error at line 32: PLS-00364: loop index variable ‘REC’ use is invalid
Error at line 41: PLS-00201: identifier ‘CDBVIEW.CREATE_CDBVIEW’ must be declared
Error at line 51: SP2-1506: START, @ or @@ command has no arguments
Error at line 62: ORA-01109: database not open
Error at line 74: ORA-01109: database not open

bootstrap_CDB_PDBSEED.log 也显示：

Starting bootstrap on 29-OCT-15 04.27.22.440262 PM -04:00

:sql_file := dbms_registry.nothing_script;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00201: identifier ‘DBMS_REGISTRY.NOTHING_SCRIPT’ must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

old 25: IF &full_bootstrap THEN
new 25: IF FALSE THEN
FROM dba_tab_columns
*
ERROR at line 13:
ORA-06550: line 13, column 12:
PL/SQL: ORA-01219: database or pluggable database not open: queries allowed on
fixed tables or views only
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 37, column 18:
PLS-00201: identifier ‘DBMS_REGISTRY.NOTHING_SCRIPT’ must be declared

警报日志显示以下错误：

Thu Oct 29 16:27:21 2015
alter pluggable database pdb$seed close immediate instances=all
Thu Oct 29 16:27:22 2015
ALTER SYSTEM: Flushing buffer cache inst=3 container=2 local
Pluggable database PDB$SEED closed
Completed: alter pluggable database pdb$seed close immediate instances=all
alter pluggable database pdb$seed OPEN READ WRITE
Pdb PDB$SEED hit error 16001 during open read write (1) and will be closed.
Thu Oct 29 16:27:22 2015
ORA-16001: database already open for read-only access by another instance
ALTER SYSTEM: Flushing buffer cache inst=3 container=2 local
ORA-16001 signalled during: alter pluggable database pdb$seed OPEN READ WRITE…

原因

Datapatch内部发出以下命令：

alter pluggable database pdb$seed close immediate instances=all

这个命令应该关闭在所有实例的pdb$seed且状态应对pdb$seed的所有实例被mount。

但这里datapatch仅在本地节点执行此命令。其他节点仍显示 pdb$seed 为READ ONLY 且 仅本地节点为mount。
在该datapatch后执行：

alter pluggable database pdb$seed OPEN READ ONLY;

失败显示：

ORA-16001: database already open for read-only access by another instance

由于在其他节点的实例在只读模式且不更改为monuted，所以上面的命令失败。
结果发现，初始化参数parallel_force_local=true 被设置，所以没有生成一个并行slave 来关闭远程pdb$seed。

解决方案

1. 将实例参数”parallel_force_local” 设为 false:

alter system set parallel_force_local=false scope=both sid=’*’;

2. 再次执行datapatch

参考

NOTE:2062080.1 - Alter Pluggable Database Close Instances=ALL Not Working