Oracle PDB 启动报 Warning: PDB altered with errors. 状态显示受限(RESTRICTED)

最新推荐文章于 2024-11-13 16:04:01 发布

原创最新推荐文章于 2024-11-13 16:04:01 发布 · 2.8k 阅读

3 ·

CC 4.0 BY-SA版权

常见问题同时被 2 个专栏收录

151 篇文章

订阅专栏

数据库

147 篇文章

订阅专栏

本文档记录了解决Oracle PDB在启动时出现'Warning: PDB altered with errors'警告，状态变为受限的问题。通过查询pdb_plug_in_violations视图发现是由于默认表空间或临时表空间不存在导致。解决方案是在PDB中创建缺失的表空间，然后重启PDB，最终成功将PDB状态恢复为正常。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle PDB 启动报 Warning: PDB altered with errors. 状态显示受限(RESTRICTED)

startup 或 startup force 启动 PDB 遇到问题，进入受限模式（RESTRICTED）

SQL> startup;
Warning: PDB altered with errors.
Pluggable Database opened.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE YES

PDB报错可以在 pdb_plug_in_violations 视图中查看:

set line 200
col cause for a10
col type for a5
col message for a165
col time for a20
select cause,type,message,time from pdb_plug_in_violations;

CAUSE TYPE MESSAGE
---------- ----- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
TIME
--------------------
Sync Failu ERROR Sync PDB failed with ORA-959 during 'CREATE USER "C##SHEN" IDENTIFIED BY * DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" container = all'
re
16-JAN-20 10.51.29.8
96697 PM

ORA-959 错误是表空间不存在，报错语句发生在 CREATE USER ......
默认表空间：USERS，临时表空间：TEMP，肯定至少有一个表空间在该PDB中不存在。因此，解决方法就是在 PDB2 中新建对应缺失的表空间：

设置显示格式
col tablespace_name for a40
col username for a20
col default_tablespace for a40

查询临时表空间：
select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;
无

查询当前用户的默认表空间：
select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
-------------------- ----------------------------------------
SYS SYSTEM

查询所有表空间及使用信息：
select a.tablespace_name,total "Total(M)",free "Free(M)",total-free "Used(M)",round(((total-free)/total)*100,2) "Used(%)" from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

TABLESPACE_NAME Total(M) Free(M) Used(M) Used(%)
---------------------------------------- ---------- ---------- ---------- ----------
SYSAUX 370 17.625 352.375 95.24
UNDOTBS1 100 77.3125 22.6875 22.69
PDB 100 98.25 1.75 1.75
SYSTEM 260 7 253 97.31
TEST_CDC 600 597 3 .5

SQL> col name for a100
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCL/pdb2/system01.dbf
/opt/oracle/oradata/ORCL/pdb2/sysaux01.dbf
/opt/oracle/oradata/ORCL/pdb2/undotbs01.dbf
/opt/oracle/oradata/ORCL/pdb2/pdb01.dbf
/opt/oracle/oradata/ORCL/pdb2/TEST01.DBF
/opt/oracle/oradata/ORCL/pdb2/TEST02.DBF
/opt/oracle/oradata/ORCL/pdb2/TEST03.DBF

7 rows selected.

创建缺失的表空间：
create temporary tablespace temp tempfile '/opt/oracle/oradata/ORCL/pdb2/temp01.dbf' size 500m autoextend on next 100m maxsize 15g extent management local;
create tablespace users datafile '/opt/oracle/oradata/ORCL/pdb2/users01.dbf' size 500m autoextend on next 100m maxsize 10g;

再次查询临时表空间：
col File for a80
col Tablespace for a20
select f.file#,t.ts#,f.name "File",t.name "Tablespace" from v$tempfile f,V$tablespace t where f.ts# = t.ts#;

FILE# TS# File Tablespace
---------- ---------- -------------------------------------------------------------------------------- --------------------
4 3 /opt/oracle/oradata/ORCL/pdb2/temp012020-11-23_08-28-39-091-AM.dbf TEMP

再次查询数据表空间：
select a.tablespace_name,total "Total(M)",free "Free(M)",total-free "Used(M)",round(((total-free)/total)*100,2) "Used(%)" from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

TABLESPACE_NAME Total(M) Free(M) Used(M) Used(%)
---------------------------------------- ---------- ---------- ---------- ----------
SYSAUX 370 17.625 352.375 95.24
UNDOTBS1 100 77.25 22.75 22.75
PDB 100 98.25 1.75 1.75
USERS 500 499 1 .2
SYSTEM 260 6.9375 253.0625 97.33
TEST_CDC 600 597 3 .5

重启PDB数据库：
shutdown immediate;
startup;
show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO

OK，问题解决了，PDB2的状态显示正常了，不再是受限模式了