Oracle 19c 以上部分DB版本执行在 read only 模式下执行DBMS_METADATA.GET_DDL 报错ORA-16000/ORA-06512

测试目的：

解决在 read only库(DG库要解决这个问题，在主库中执行补丁应用操作)上使用dbms_metadata.get_ddl 提取相关对象结果报错： ORA-16000，ORA-06512，导致的BUG 30858671
说明：Bug 30858671 18c DBMS_METADATA.GET_DDL Reports ORA-16000 in Read Only Mode　（Doc ID 30858671.8）

受影响DB版本：

产品（组件）	Oracle 服务器（Rdbms）
据信受影响的版本范围	版本 >= 18.1 但低于 21.1
**已确认受影响的版本**	19.21.0 19.20.0 19.19.0 19.18.0 19.17.0 19.16.0 19.15.0 19.14.0 19.13.0 19.12.0 19.11.0 19.10.0 19.9.0 19.8.0 19.7.0 19.6.0 19.5.0 19.4.0 19.1.0 18.10.0 18.7.0 18.5.0
受影响的平台	通用（所有/大多数平台受到影响）

报错描述：

The issue addressed by this bug is:

SQL> alter database open read only;

Database altered.

SQL> select dbms_metadata.get_ddl('USER','SYSTEM') from dual;

ERROR:

ORA-16000: database or pluggable database open for read-only access

ORA-06512: at "SYS.DBMS_METADATA", line 6681

ORA-06512: at "SYS.DBMS_METADATA", line 6466

ORA-06512: at "SYS.DBMS_LOCK", line 378

ORA-06512: at "SYS.DBMS_LOCK", line 411

ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1670

###############################################

测试环境:
OS:
Red Hat Enterprise Linux Server release 7.9 (Maipo)

DB:
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 5 15:36:01 2023
Version 19.3.0.0.0

OPatch Version: 12.2.0.1.40

###############################################

数据库层面：
SQL> alter database open read only;

Database altered.

SQL>

SQL> set long 50000
set linesize 500
set pagesize 0
select dbms_metadata.get_ddl('INDEX','DESC_EMP_INDEX','SCOTT') from dual;
SQL> SQL> SQL>
ERROR:
ORA-16000: database or pluggable database open for read-only access
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_METADATA", line 6516
ORA-06512: at "SYS.DBMS_LOCK", line 378
ORA-06512: at "SYS.DBMS_LOCK", line 411
ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1738
ORA-06512: at "SYS.DBMS_METADATA", line 1216
ORA-06512: at "SYS.DBMS_METADATA", line 1314
ORA-06512: at "SYS.DBMS_METADATA", line 6439
ORA-06512: at "SYS.DBMS_METADATA", line 6572
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1

no rows selected

###############################################
测试步骤：

1、升级Opatch工具版本，P6880880
2、打补丁 P30858671
3、将数据库打开至open read only进行测试

#############################################
测试过程：

1、升级Opatch工具版本，P6880880

详见 P6880880的readme.txt

###############

2、打补丁 P30858671.

详见 P30858671的readme.txt的readme.txt

注意：我这里是关闭数据库打开，然后打开数据库在执行 datapatch 操作

注意点：

[oracle@shsnc-01 OPatch]$ datapatch ##打开数据库执行，这里可能会比较慢
SQL Patching tool version 19.3.0.0.0 Production on Tue Dec 5 15:19:17 2023
Copyright (c) 2012, 2019, Oracle. All rights reserved.

Log file for this invocation: /u01/oracle/cfgtoollogs/sqlpatch/sqlpatch_10383_2023_12_05_15_19_17/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 30858671 (18C DBMS_METADATA.GET_DDL FAILED WITH ORA-16000 IN READ ONLY MODE):
Binary registry: Installed
SQL registry: Not installed

Current state of release update SQL patches:
Binary registry:
19.3.0.0.0 Release_Update 190410122720: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 2023-12-05 11:54:04.753474

Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
No release update patches need to be installed
The following interim patches will be applied:
30858671 (18C DBMS_METADATA.GET_DDL FAILED WITH ORA-16000 IN READ ONLY MODE)

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...done
Patch 30858671 apply: SUCCESS
logfile: /u01/oracle/cfgtoollogs/sqlpatch/30858671/23609949/30858671_apply_SHSNC_2023Dec05_15_19_31.log (no errors)
SQL Patching tool complete on Tue Dec 5 15:25:31 2023
[oracle@shsnc-01 OPatch]$

###############
3、将数据库打开至open read only进行测试

[oracle@shsnc-01 OPatch]$ opatch lspatches
30858671;18C DBMS_METADATA.GET_DDL FAILED WITH ORA-16000 IN READ ONLY MODE
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)

OPatch succeeded.
[oracle@shsnc-01 OPatch]$

SQL> alter database open read only;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
set long 50000
set linesize 500
set pagesize 0
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
SQL> SQL> SQL> SQL>

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 2
55 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTE
NTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("D
EPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXT
ENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CEL
L_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL> SQL>
SQL>
SQL> set long 50000
set linesize 500
set pagesize 0
select dbms_metadata.get_ddl('INDEX','DESC_EMP_INDEX','SCOTT') from dual;
SQL> SQL> SQL>
CREATE INDEX "SCOTT"."DESC_EMP_INDEX" ON "SCOTT"."EMP" ("
ENAME" DESC, "SAL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEX
TENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CE
LL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL>

SQL>
SQL>
SQL> select open_mode from v$database;
READ ONLY

SQL>

#############################################

结束。