CDB+PDB访问和管理

第一章、连接CDB和PDB

1、设置Oracle_SID

登录之前；先加载环境变量

source .bash_profile 
echo $ORACLE_SID

2、查看数据库是否是多租户数据库

查询 v$database 视图；若cdb为“YES”;表明是多租户数据库

select name, cdb from v$database;

3、查看当前连接的容器

show con_name
show con_id
-- 或者使用sys_context函数
col con_id format a6
col con_name format a20
select sys_context('userenv','con_name') as con_name,sys_context('userenv','con_id') as con_id from dual;

4、查看可插拔数据库PDB状态

-- 使用 show pdbs 命令查看
sys@LOTTU>show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  MOUNTED
--通过 v$pdbs 数据字典视图查看pdb状态
col name for a30
select name, con_id, open_mode from v$pdbs;

5、连接到可插拔数据库pdb1

• 使用 altersession set container

-- 切换容器
alter sessionset container = pdb1;
-- 查看当前容器名
show con_name
-- 查看当前pdb
show pdbs
--切回cdb
alter session set container = "cdb$root"

• 使用服务名连接

-- 查看pdb1的服务名
select name,con_id from v$services;
--简易连接方式
conn sys/password@ip:port/pdb1hr1 as sysdba
--配置tnsnames.ora
pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lottu)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )
conn sys/password@pdb1hr1 as sysd

第二章、CDB实例的启动和关闭

1、关闭cdb实例

shutdown immediate
# 所有的pdb均已经关闭
# CDB关闭
# CDB已卸载
# 实例关闭

查看后台进程

ps -ef | grep ora_ | grep -vgrep

2、启动CDB实例

startup
# startup 分为3个步骤
# 实例启动  startup nomount
select instance_name, statusfrom v$instance;
# mount 状态 
alter database mount
# 打开数据库
alter database open

3、管理PDB

a、打开pdb

默认cdb启动；pdb是处于mount状态；除了PDB$SEED始终是readonly

# 直接连接pdb 
conn sys/li0924520_@pdb1 as sysdba
alter database open
#或者执行
alter pluggable database pdb1 open;
alter pluggable database all open;
alter pluggable database pdb1 open read only;

b、关闭pdb

# 直接连接pdb 
conn sys/li0924520_@pdb1 as sysdba
shutdownimmediate
#或者执行
alter pluggable database pdb1 close immediate;
alter pluggable database all clase;

c、自动打开pdb

# CDB startup 后自动保留PDB状态
alter pluggable databasepdb1 save state;
alter pluggable database all save state;
alter pluggable database pdb1,pdb2 save state;
alter pluggable database all except pdb3 save state;
# CDB startup 后放弃PDB状态
alter pluggable database pdb1 discard state;
# 查看配置
col con_name for a10
col instance_name for a20
col state for a10
select con_name,instance_name, state from cdb_pdb_saved_states;

d、修改pdb

-- 在系统级别修改pdb
可以使用alter system命令动态修改pdb
alter system flush {shared_pool | buffer_cache | flash_cache};
alter system  checkpoint;
alter system killsession;
-- 在数据库基本修改pdb
alter pluggable database close immediate;
alter pluggabledatabase open read only;
alter pluggable database default tablespace tbs_pdb;
alter pluggable database default teporary tablespace temp_pdb;
alter pluggable database storage(maxsize=200G);
alter pluggable database storage(maxsize unlimited);
alter pluggable database onlogging;
alter pluggable database enable force logging;

第三章、数据字典

1、查看数据文件

col file_name for a100
col tablespace_name for a30
setlinesize 1000
select con_id,file_id,file_name,tablespace_name from cdb_data_files;
select file_id,file_name,tablespace_name from dba_data_files;

2、 查看临时文件

col file_name for a100
col tablespace_name for a30
set linesize 1000
select con_id,file_id,file_name,tablespace_name from cdb_temp_files;
select file_id,file_name,tablespace_name from dba_temp_files;

3、查看控制文件、日志文件

selectmember from v$logfile;
select name from v$controlfile;

第四章、用户权限管理

1、先看哪些用户是公共用户

公共用户：在所有pdb都存在；且有使用权限

本地用户：仅在自己pdb中

col username for a20
col commont for a10
select username,common,con_id from cdb_users;
-- common 为 yes；为公共用户

2、创建公共用户

-- 查看公共用户的前缀；默认是C##
show parameter common_user_prefix
--
create user C##lottu01 identifiedby "li0924520_" container=all;

3、创建本地用户

-- 本地用户只能在pdb创建
create user lottu01 identified by "li0924520_";

4、 赋权

-- 给公共用户赋权
grant connect to C##lottu01 container=all;
-- 给本地用户赋权
grant connectto lottu01 container=current;

第五章、修改参数

1、修改CDB参数

alter system set parameter = value container = [all | current ];

2、PDB参数

在PDB＿SPFILE$字典里

如何在v$system_parameter视图中的列ispdb_modifiable是TRUE；那么可以在PDB中进行修改