ORA-01035

Table of Contents

• 1. 现象
• 2. 分析
• 3. 处理

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.

Validate