诗檀软件专业数据库修复团队
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。
解决方案
- 将实例参数"parallel_force_local" 设为 false:
alter system set parallel_force_local=false scope=both sid='*';
-
再次执行datapatch
参考
NOTE:2062080.1 - Alter Pluggable Database Close Instances=ALL Not Working