Table of Contents
1. 现象
当用户登录时，提示如下信息:
ERROR: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
此报错的意思是只有拥有登录restrict实现的权限的用户才可以执行登录操作。 也就是说，登录的pdb 当前是处于 restricted 模式。
2. 分析
查看当前实例中pdb状态:
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE YES
经查看，果然是这个样子。pdb 当前是restricted .
由于是pdb ，cdb 没有问题，而pdb 不能登录。 回想，这里的pdb， 是从远程pdb clone建立 的。 那么很有可能，是pdb 中的patch 与cdb 是不一致的。
可以通过查看表 pdb_plug_in_violations 中有没有pdb 与 cdb 冲突的记录:
set lines 200 pages 2000
col name for a10
col message for a30
col action for a20
select name,type,message,status,action from pdb_plug_in_violations;
NAME TYPE MESSAGE STATUS ACTION
---------- --------- ------------------------------ --------- --------------------
TESTPDB WARNING PDB requires time zone version RESOLVED Install the required
file(s): primary version 43 w time zone version f
hile CDB does not have such ti ile(s) in CDB's $ORA
me zone version file(s) instal CLE_HOME/oracore/zon
led. einfo/.
TESTPDB ERROR '19.3.0.0.0 Release_Update 190 PENDING Call datapatch to in
4101227' is installed in the C stall in the PDB or
DB but no release updates are the CDB
installed in the PDB
可以看到，果然是有一个问题处于 PENDING 状态。 根据提示信息，版本升级在cdb中已安装，但是在pdb中没有安装 。根据 ACTION 中的提示，可以执行datapatch 命令在pdb中进行安装 。
下面就按提示操作一下吧.
3. 处理
[oracle@localhost trace]$ cd $ORACLE_HOME/OPatch
[oracle@localhost OPatch]$ ./datapatch --verbose
SQL Patching tool version 19.3.0.0.0 Production on Sun May 19 23:01:33 2024
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_19026_2024_05_19_23_01_33/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.3.0.0.0 Release_Update 190410122720: Installed
PDB CDB$ROOT:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 15-5月 -24 09.42.09.626221 下午
PDB TESTPDB:
No release update patches installed
PDB PDB$SEED:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 15-5月 -24 09.49.13.791316 下午
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
For the following PDBs: TESTPDB
No interim patches need to be rolled back
Patch 29517242 (Database Release Update : 19.3.0.0.190416 (29517242)):
Apply from 19.1.0.0.0 Feature Release to 19.3.0.0.0 Release_Update 190410122720
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
Patch 29517242 apply (pdb TESTPDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_HNBOSSGJ_GJZG_2024May19_23_02_08.log (no errors)
SQL Patching tool complete on Sun May 19 23:03:52 2024
下面就查看结果如何:
-- 重启pdb
alter pluggable database all close;
alter pluggable database all open;
-- 查看结果
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
SQL> set lines 200 pages 2000
SQL> col name for a10
SQL> col message for a50
SQL> col action for a50
SQL> select name,type,message,status,action from pdb_plug_in_violations;
NAME TYPE MESSAGE STATUS ACTION
---------- --------- -------------------------------------------------- --------- --------------------------------------------------
PDB$SEED ERROR '19.3.0.0.0 Release_Update 1904101227' is installe RESOLVED Call datapatch to install in the PDB or the CDB
d in the CDB but no release updates are installed
in the PDB
TESTPDB ERROR '19.3.0.0.0 Release_Update 1904101227' is installe RESOLVED Call datapatch to install in the PDB or the CDB
d in the CDB but no release updates are installed
in the PDB
.....
PDB RESTRICTED 状态已变动为NO . 并且，pdb 与 cdb 的冲突 状态（STATUS) 全部变为 RESOLVED.