管理CDB和PDB

管理CDB和PDB

1 连接CDB和PDB

1.1 查看后台进程

ps -ef | grep ora_ | grep -v grep

1.2 查看监听程序状态

lsnrctl status

1.3 连接多租户数据库cdb3

配置ORACLE_SID，使用OS验证连接cdb3

echo $ORACLE_SID
sqlplus / as sysdba

（推荐使用）或者配置tnsnames.ora，使用服务名登录dbca建库后，tnsnames.ora中增加服务名cdb3

cd $ORACLE_HOME/network/admin
vi tnsnames.ora

写入：

CDB3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dba)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb3.example.com)
    )
  )

sqlplus sys/oracle@cdb3 as sysdba

1.4 查看数据库是否是多租户数据库

select name,cdb from v$database;

1.5 查看当前连接的容器

show con_name
show con_id

CDB$ROOT   根容器

也可以使用sys_context函数查看会话上下文con_name和con_id属性

select sys_context('userenv','con_name'),sys_context('userenv','con_id') from dual;

1.6 查看可插拔数据库pdb状态

col name for a30
select name,con_id,open_mode from v$pdbs;

show pdbs

PDB$SEED   种子PDB，PDB模板

1.7 连接到可插拔数据库pdb1

不使用export ORACLE_SID=pdb1的方式连接pdb

1.7.1 方法一：alter session set container

alter session set container=pdb1;
show con_name
show con_id

注意：已经切换到pdb1，但这种方法连接，sqlplus命令提示符不变

1.7.2 方法二：配置tnsnames.ora，增加pdb1

cd $ORACLE_HOME/network/admin
vi tnsnames.ora

写入：

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dba)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.example.com)
    )
  )

1.7.2.1 sqlplus通过服务名连接pdb1

sqlplus sys/oracle@pdb1 as sysdba
show con_name
show pdbs

1.7.2.2 cdb3使用connect命令切换到pdb1

sqlplus sys/oracle@cdb3 as sysdba
show pdbs
conn sys/oracle@pdb1 as sysdba
show pdbs 

1.8 pdb切换回cdb

1.8.1 方法一：alter session set container

alter session set container=cdb$root;

同样，sqlplus命令提示符仍显示之前状态没有改变

1.8.2 方法二：直接conn / as sysdba

conn / as sysdba

在pdb中，执行conn / as sysdba会切换连接到根容器cdb1，而不是pdb中的sys用户

或者通过服务名的方式切换

conn sys/oracle@cdb1 as sysdba

 

2 CDB实例的启动与关闭

2.1 关闭cdb实例

shutdown immediate;

ps -ef | grep ora_ | grep -v grep

2.2 启动cdb实例

startup nomount;
select name,con_id,open_mode from v$pdbs;

2.3 mount装载CDB

alter database mount;
select instance_name,status from v$instance;

select name,con_id,open_mode from v$pdbs;
show pdbs

2.4 open打开CDB

alter database open;
show pdbs

 

3 打开与关闭PDB

3.1 查看pdb状态

show pdbs

cdb处于open状态时，pdb默认处于mounted状态（种子pdb PDB$SEED始终read only状态）

3.2 pdb中操作：打开pdb

conn sys/oracle@pdb1 as sysdba
show pdbs
alter database open;

3.3 pdb中操作：关闭pdb

show pdbs;
shutdown immediate;

3.4 cdb中操作：打开pdb

conn / as sysdba
show pdbs

alter pluggable database pdb1 open;
show pdbs

3.5 cdb中操作：关闭pdb

alter pluggable database pdb1 close immediate;
show pdbs

3.6 cdb中操作：打开所有非种子pdb

alter pluggable database all open;
show pdbs;

PDB$SEED作为创建其他pdb的模板使用，不建议open read write打开pdb$seed

3.7 自动打开pdb

3.7.1 配置CDB open后PDB打开状态

当前pdb1 open read write状态

show pdbs
alter pluggable database pdb1 save 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;

shutdown immediate;
startup

3.7.2 取消状态恢复默认

alter pluggable database pdb1 discard state;
select con_name,instance_name,state from cdb_pdb_saved_states;

shutdown immediate;
startup

 

4 自动诊断目录库

col name for a25
col value for a70
select name,value from v$diag_info;

 

5 数据字典视图

5.1 CDB_xxx

5.2 DBA_xxx

5.3 CDB 中查看数据文件、临时文件

pdb处于open状态

alter pluggable database all open;
show pdbs

5.3.1 数据文件

col FILE_NAME for a50
select con_id,file_id,file_name,tablespace_name from cdb_data_files;
select file_id,file_name,tablespace_name from dba_data_files;

5.3.2 临时文件

select file_id,file_name,tablespace_name from cdb_temp_files;
select file_id,file_name,tablespace_name from dba_temp_files;

5.4 PDB中查看数据文件、临时文件

sqlplus sys/oracle@pdb1 as sysdba

5.4.1 数据文件

col FILE_NAME for a50
select con_id,file_id,file_name,tablespace_name from cdb_data_files;
select file_id,file_name,tablespace_name from dba_data_files;

5.4.2 临时文件

select file_id,file_name,tablespace_name from cdb_temp_files;
select file_id,file_name,tablespace_name from dba_temp_files;

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

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

 

6 公共与本地（用户、权限、角色）

6.1 公用用户和本地用户

select username,common,con_id from cdb_users where username in ('SYS','HR');

6.1.1 cdb中创建公用用户

create user cdb_user identified by oracle;
show parameter common_user_prefix

create user c##cdb_user identified by oracle;
select username,common,con_id from cdb_users where username in ('SYS','HR','C##CDB_USER');

drop user c##cdb_user cascade;

6.1.2 pdb中创建本地用户

create user pdb1_user identified by oracle;
select username,common,con_id from cdb_users where username in ('SYS','HR','C##CDB_USER','PDB1_USER');

cdb中查看

select username,common,con_id from cdb_users where username in ('SYS','HR','C##CDB_USER','PDB1_USER');

6.2 公用和本地权限和角色

6.2.1 cdb中给公用用户授权

sqlplus c##cdb_user/oracle@cdb3
sqlplus c##cdb_user/oracle@pdb1

默认没有权限登录

6.2.1.1 container=current

grant connect to c##cdb_user container=current;
sqlplus c##cdb_user/oracle@cdb3
sqlplus c##cdb_user/oracle@pdb1

cdb有权限登录，pdb没有权限

6.2.1.2 container=all

grant connect to c##cdb_user container=all;
conn c##cdb_user/oracle@cdb3
conn c##cdb_user/oracle@pdb1

6.2.2 pdb中给本地用户授权

grant connect to pdb1_user container=all;
grant connect to pdb1_user container=current;

6.3 pdb中解锁hr用户方案

alter user hr identified by hr account unlock;
conn hr/hr@pdb1;
select table_name from user_tables;

6.4 pdb中创建scott用户修改脚本

cd $ORACLE_HOME/rdbms/admin
cp utlsampl.sql utlsampl.sql.pdb1
vi utlsampl.sql.pdb1

sqlplus sys/oracle@pdb1 as sysdba
@?/rdbms/admin/utlsampl.sql.pdb1

 

7 表空间管理

7.1 CDB中创建永久表空间

7.2 分配默认表空间

7.3 创建本地临时表空间

7.4 分配默认临时表空间

 

8 SQL developer连接CDB

win端已经安装了oracle19c client()

8.1 启动SQL developer

8.2 配置win端tnsnames.ora

路径：\PLSQL Developer 14\instantclient_12_2\NETWORK\ADMIN\tnsnames.ora

添加：

CDB3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb3.example.com)
    )
  )

8.3 创建连接