关于在CDB_DATA_FILES视图中如何查看seed pdb的datafile问题

在oracle 12c多租户环境里,我们有时想通过cdb_data_files来查看种子数据库(pdb$seed)的文件信息,会发现没有反馈结果,但你可能会发现网上有些案例里却能通过cdb_data_files这个视图查看到关于pdb$seed的信息。这是怎么回事呢?

我在12c几个版本的reference里没有找到关于cdb_data_files的详细说明,不过在 Administrator Guide中找到这句话:

theDBA_PDBSandCDB_DATA_FILESviews to show the name and location of each data file for all of the PDBs in a CDB,including the CDB seed.

这说明cdb_data_files确实有pdb$seed的信息。

在MOS中,文档(1940806.1) PDB$SEED Datafiles Not Appear In CDB_DATA_FILES介绍是参数EXCLUDE_SEED_CDB_VIEW 的设置问题。不过对于这个新参数,oracle 12c各个版本的情况都不相同。

1. 首先在oracle 12.1.0.1版本中,没有参数EXCLUDE_SEED_CDB_VIEW。因此,你会发现在这个版本下,你能从cdb_data_files中直接查看到pdb$seed。

sql>select*fromv$version;

BANNER										CON_ID
------------------------------------------------------------------------------------------
OracleDatabase12cEnterpriseEditionRelease12.1.0.1.0-64bitProduction		0
PL/sqlRelease12.1.0.1.0-Production							0
CORE	12.1.0.1.0	Production								0
TNSforLinux:Version12.1.0.1.0-Production						0
NLSRTLVersion12.1.0.1.0-Production							0

sql>selectfile_namefromcdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/dbdata/cdb1/system01.dbf
/u01/dbdata/cdb1/sysaux01.dbf
/u01/dbdata/cdb1/undotbs01.dbf
/u01/dbdata/cdb1/users01.dbf
/u01/dbdata/cdb1/pdbseed/system01.dbf
/u01/dbdata/cdb1/pdbseed/sysaux01.dbf
/u01/dbdata/cdb1/pdb1/system01.dbf
/u01/dbdata/cdb1/pdb1/sysaux01.dbf
/u01/dbdata/cdb1/pdb1/SAMPLE_SCHEMA_users01.dbf
/u01/dbdata/cdb1/pdb1/example01.dbf

10rowsselected.
sql>selectname,value,descriptionfromv$parameterwherenamelike'%seed%';

norowsselected

sql>SELECTksppinm,ksppstvl,ksppdescFROMx$ksppix,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed%'

KSPPINM		KSPPSTVL	KSPPDESC
--------------------------------------------------------------------------------------------------------------
_deferred_seg_in_seed	TRUE		EnableDeferredSegmentCreationinSeed


2. 不过在12.1.0.2版本里,oracle引入了参数EXCLUDE_SEED_CDB_VIEW来控制查看pdb$seed

我们可以看到,默认情况下是查询不到pdb$seed的

sql>select*fromv$version;

BANNER										CON_ID
------------------------------------------------------------------------------------------
OracleDatabase12cEnterpriseEditionRelease12.1.0.2.0-64bitProduction		0
PL/sqlRelease12.1.0.2.0-Production							0
CORE	12.1.0.2.0	Production								0
TNSforLinux:Version12.1.0.2.0-Production						0
NLSRTLVersion12.1.0.2.0-Production							0

sql>selectfile_namefromcdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/dbdata/cdb2/system01.dbf
/u01/dbdata/cdb2/sysaux01.dbf
/u01/dbdata/cdb2/undotbs01.dbf
/u01/dbdata/cdb2/users01.dbf
/u01/dbdata/cdb2/pdb2/system01.dbf
/u01/dbdata/cdb2/pdb2/sysaux01.dbf
/u01/dbdata/cdb2/pdb2/SAMPLE_SCHEMA_users01.dbf
/u01/dbdata/cdb2/pdb2/example01.dbf

8rowsselected.
sql>SELECTksppinm,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed_cdb%';

KSPPINM		KSPPSTVL	KSPPDESC
--------------------------------------------------------------------------------------------------------------
exclude_seed_cdb_view	TRUE		excludePDB$SEEDfromCDBViewResult

参数exclude_seed_cdb_view默认值为true,当设置为false时,就能查看到pdbs$seed了

sql>altersystemsetexclude_seed_cdb_view=FALSE;

Systemaltered.

sql>selectfile_namefromcdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/dbdata/cdb2/pdb2/system01.dbf
/u01/dbdata/cdb2/pdb2/sysaux01.dbf
/u01/dbdata/cdb2/pdb2/SAMPLE_SCHEMA_users01.dbf
/u01/dbdata/cdb2/pdb2/example01.dbf
/u01/dbdata/cdb2/system01.dbf
/u01/dbdata/cdb2/sysaux01.dbf
/u01/dbdata/cdb2/undotbs01.dbf
/u01/dbdata/cdb2/users01.dbf
/u01/dbdata/cdb2/pdbseed/system01.dbf
/u01/dbdata/cdb2/pdbseed/sysaux01.dbf

10rowsselected.


3. 而在新版本12.2.0.1中,oracle已经把EXCLUDE_SEED_CDB_VIEW作为隐含参数了,默认值同样为TURE。

sql>select*fromv$version;

BANNER										CON_ID
------------------------------------------------------------------------------------------
OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction		0
PL/sqlRelease12.2.0.1.0-Production							0
CORE	12.2.0.1.0	Production								0
TNSforLinux:Version12.2.0.1.0-Production						0
NLSRTLVersion12.2.0.1.0-Production							0

sql>selectfile_namefromcdb_data_files;

FILE_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/dbdata/cdb3/system01.dbf
/u01/dbdata/cdb3/sysaux01.dbf
/u01/dbdata/cdb3/undotbs01.dbf
/u01/dbdata/cdb3/users01.dbf
/u01/dbdata/cdb3/orclpdb/system01.dbf
/u01/dbdata/cdb3/orclpdb/sysaux01.dbf
/u01/dbdata/cdb3/orclpdb/undotbs01.dbf
/u01/dbdata/cdb3/orclpdb/users01.dbf

8rowsselected.
sql>SELECTksppinm,x$ksppcvyWHEREx.indx=y.indxANDksppinmlike'%seed_cdb%';

KSPPINM		KSPPSTVL	KSPPDESC
--------------------------------------------------------------------------------------------------------------
_exclude_seed_cdb_view	TRUE		excludePDB$SEEDfromCDBViewResult

不过好在这个参数可以在instance或session级别修改

sql>altersystemset"_exclude_seed_cdb_view"=FALSE;

Systemaltered.

sql>altersessionset"_exclude_seed_cdb_view"=FALSE;

Sessionaltered.
sql>selectfile_namefromcdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/dbdata/cdb3/system01.dbf
/u01/dbdata/cdb3/sysaux01.dbf
/u01/dbdata/cdb3/undotbs01.dbf
/u01/dbdata/cdb3/users01.dbf
/u01/dbdata/cdb3/orclpdb/system01.dbf
/u01/dbdata/cdb3/orclpdb/sysaux01.dbf
/u01/dbdata/cdb3/orclpdb/undotbs01.dbf
/u01/dbdata/cdb3/orclpdb/users01.dbf
/u01/dbdata/cdb3/pdbseed/system01.dbf
/u01/dbdata/cdb3/pdbseed/sysaux01.dbf
/u01/dbdata/cdb3/pdbseed/undotbs01.dbf

11rowsselected.

从上面的实验可以看到,oracle越来越重视对pdb$seed的保护。从第一版全部开放,再到12.1.0.2引入普通参数进行查询控制,最后干脆改为隐含参数。

相关文章

数据库版本:11.2.0.4 RAC(1)问题现象从EM里面可以看到,在23号早上8:45~8:55时,数据库等待会话暴增...
(一)问题背景最近在对一个大约200万行数据的表查看执行计划时,发现存在异常,理论上应该返回100多万...
(一)删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操作时,会在RM...
(1)DRA介绍 数据恢复顾问(Data Recovery Advise)是一个诊断和修复数据库的工具,DRA能够修复数据文...
RMAN(Recovery Manager)是Oracle恢复管理器的简称,是集数据库备份(backup)、修复(restore)和恢复...
(1)备份对象 可以使用RMAN进行的备份对象如下: --整个数据库:备份所有的数据文件和控制文件; --数...