文章详情

登录

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

CDB、PDB应用

2016-02-01 20:35

关注

CDB、PDB应用 [数据库教程]

环境：oracle12c 

常用字典

DBA_xxx All objects in the root or a pluggable database
ALL_xxx Objects accessible by the current user in a PDB
USER_xxx Objects owned by the current user in a PDB

[[email protected] ~]$ sqlplus sys/[email protected]192.168.95.150:1521/pdb01.oracle.com as sysdba
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_system_hlw5ttv4_.dbf
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_sysaux_hlw5ttvb_.dbf
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_undotbs1_hlw5ttvc_.dbf
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_users_hlw5v9dm_.dbf

[[email protected] oradata]$ sqlplus sys/[email protected]192.168.95.150:1521/yh.oracle.com as sysdba
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/YH/datafile/o1_mf_system_hlw5gytl_.dbf
/u01/app/oracle/oradata/YH/datafile/o1_mf_sysaux_hlw5jdj2_.dbf
/u01/app/oracle/oradata/YH/datafile/o1_mf_undotbs1_hlw5k5x7_.dbf
/u01/app/oracle/oradata/YH/datafile/o1_mf_users_hlw5k769_.dbf


CDB_xxx All objects in the CDB (new column CON_ID)

SQL> SELECT file_name,con_id FROM cdb_data_files
FILE_NAME                                                 CON_ID
---------------------------------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/YH/datafile/o1_mf_system_hlw5gytl_.dbf                              1
/u01/app/oracle/oradata/YH/datafile/o1_mf_sysaux_hlw5jdj2_.dbf                              1
/u01/app/oracle/oradata/YH/datafile/o1_mf_undotbs1_hlw5k5x7_.dbf                          1
/u01/app/oracle/oradata/YH/datafile/o1_mf_users_hlw5k769_.dbf                              1
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_system_hlw5ttv4_.dbf           3
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_sysaux_hlw5ttvb_.dbf           3
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_undotbs1_hlw5ttvc_.dbf          3
/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/o1_mf_users_hlw5v9dm_.dbf              3

cdb、pdb服务查看

Every PDB has a default service.
SQL> SELECT name, pdb FROM cdb_services;
NAME             PDB
-------------------- --------------------
SYS$BACKGROUND         CDB$ROOT
SYS$USERS         CDB$ROOT
yhXDB             CDB$ROOT
yh.oracle.com         CDB$ROOT
pdb01.oracle.com     PDB01

cdb、pdb链接

[[email protected] ~]$ lsnrctl status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.95.150)(PORT=1521)))
使用EZ方式连接
sqlplus sys/[email protected]192.168.95.150:1521/yh.oracle.com as sysdba
sqlplus sys/[email protected]192.168.95.150:1521/pdb01.oracle.com as sysdba
编辑tnsnames.ora文件
YH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.95.150)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = yh.oracle.com)
    )
  )
PDB01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.95.150)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01.oracle.com)
    )
  
$ tnsping yh
$ tnsping pdb01
[[email protected] oradata]$ sqlplus sys/[email protected] as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

[[email protected] ~]$ sqlplus sys/[email protected] as sysdba
SQL> show con_name
CON_NAME
------------------------------
PDB01

Connection with SQL*Developer
SQL> CONNECT [email protected]
SQL> EXEC DBMS_SERVICE.CREATE_SERVICE(‘hrpdb‘, ‘hrpdb‘)
SQL> EXEC DBMS_SERVICE.START_SERVICE(‘hrpdb‘)

ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SESSION SET CONTAINER=PDB01;

cdb、pdb启动 停止

Starting Up a CDB Instance
SQL> CONNECT [email protected] AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> STARTUP MOUNT
SQL> ALTER DATABASE cdb1 MOUNT;
SQL> STARTUP
SQL> ALTER DATABASE cdb1 OPEN;   PDBS处于mount状态 pdb$seed处于OPEN READ ONLY

When a CDB is mounted, the root is mounted, which means that the control files are opened, as well as the PDBs.

Opening a PDB
SQL> ALTER SESSION SET CONTAINER=PDB01;
$ sqlplus sys/[email protected] as sysdba


SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1, pdb2 OPEN;


Closing a PDB
SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1, pdb2 CLOSE;
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;


Shutting Down a CDB Instance
SQL> CONNECT [email protected] AS SYSDBA
SQL> SHUTDOWN IMMEDIATE

SQL> CONNECT [email protected] AS SYSDBA
SQL> SHUTDOWN IMMEDIATE

Automatic PDB Opening
? AFTER STARTUP → ON DATABASE
CREATE OR REPLACE TRIGGER Open_All_PDBs
after startup on database
begin
execute immediate ‘alter pluggable database all open‘;
end Open_All_PDBs;
/

ALTER PLUGGABLE DATABASE OPEN RESTRICTED;

ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

Changing PDB Mode: With SQL Developer

修改pdb设置

Modifying a PDB Settings
? Bring a PDB datafile online
SQL> CONNECT [email protected] AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE DATAFILE ‘/u03/pdb1_01.dbf‘ ONLINE;
? Change the PDB default tablespace
 ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;
? Change the PDB default temporary tablespace
SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs;
? Set the PDB storage limit
SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 2G);
? Change the global name
SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdbAPP1;

Instance Parameter Change Impact
ISSES_MODIFIABLE         VARCHAR2(5)    
ISSYS_MODIFIABLE         VARCHAR2(9)    
ISPDB_MODIFIABLE         VARCHAR2(5) 


Using ALTER SYSTEM Statement on PDB
在PDB执行Alter system命令
ALTER SYSTEM FLUSH SHARED_POOL;    
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET parameter
ALTER SYSTEM KILL SESSION
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SWITCH LOGFILE;  ---不能在PDB数据库执行

cdb、pdb的表空间管理

在CDB中创建永久表空间
SQL> CREATE TABLESPACE yh_tbs01 DATAFILE
  2  ‘/u01/app/oracle/oradata/YH/datafile/yh_tbs0101.dbf‘ SIZE 100M;
SQL> SELECT tablespace_name FROM dba_tablespaces
  2  WHERE contents=‘PERMANENT‘;
SQL> ALTER DATABASE DEFAULT TABLESPACE yh_tbs01;  --设置默认的表空间
SELECT property_name,property_value FROM database_properties
WHERE property_name=‘DEFAULT_PERMANENT_TABLESPACE‘;


  
在PDB中创建永久表空间
CREATE TABLESPACE pdb01_tbs01
DATAFILE ‘/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/pdb01_tbs01.dbf‘ SIZE 100M; 
SQL> ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb01_tbs01;  --设置默认的表空间
SQL> ALTER  DATABASE DEFAULT TABLESPACE pdb01_tbs01;

SELECT property_name,property_value FROM database_properties
WHERE property_name=‘DEFAULT_PERMANENT_TABLESPACE‘;


Creating Local Temporary Tablespaces

Only one default temporary tablespace or tablespace group is allowed per CDB or PDB.
Each PDB can have temporary tablespaces or tablespace groups.

SELECT property_name,property_value FROM database_properties
WHERE property_name=‘DEFAULT_TEMP_TABLESPACE‘;

CDB:
在CDB中创建临时表空间
CREATE TEMPORARY TABLESPACE yh_temp02 TEMPFILE
‘/u01/app/oracle/oradata/YH/datafile/yh_temp0201.dbf‘ SIZE 100M;
SELECT property_name,property_value FROM database_properties
WHERE property_name=‘DEFAULT_TEMP_TABLESPACE‘;
DEFAULT_TEMP_TABLESPACE    TEMP
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE yh_temp02;   --修改CDB的默认的临时表空间


PDB中
在CDB中创建临时表空间
CREATE TEMPORARY TABLESPACE pdb01_temp02
TEMPFILE ‘/u01/app/oracle/oradata/YH/AC560BDC2E9C51A9E053965FA8C00201/datafile/pdb01_temp02.dbf‘ SIZE 100M;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb01_temp02;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE pdb01_temp02;

SELECT property_name,property_value FROM database_properties
WHERE property_name=‘DEFAULT_TEMP_TABLESPACE‘;

cdb、pdb用户及权限管理

Managing Security in CDB and PDBs
common and local

The commands to create local users and roles in a PDB are the same as for a non-CDB.

Create  a Local User
PDB中创建local user  不能在root创建create local user
SELECT username,common FROM dba_users;
CREATE USER pdbuser01 IDENTIFIED BY oracle;   --CREATE USER pdbuser01 IDENTIFIED BY oracle CONTAINER=CURRENT;
SELECT * FROM dba_users
WHERE username=‘PDBUSER01‘;
The DROP USER and ALTER USER commands are identical, as the commands in a nonCDB.



A common user can only be created in the root container.
show parameter common_user_prefix
NAME                         TYPE    VALUE                                           
---------------------------- ------- ----------------------------------------------- 
common_user_prefix           string  C##  
CREATE USER c##yh01 IDENTIFIED BY oracle;   --CREATE USER c##yh01 IDENTIFIED BY oracle CONTAINER=ALL;
SELECT * FROM dba_users
WHERE username=‘C##YH01‘;
--a common privilege
GRANT CREATE SESSION TO c##yh01 CONTAINER=ALL;

[[email protected] ~]$ sqlplus c##yh01/[email protected]
[[email protected] ~]$ sqlplus c##yh01/[email protected]
--a local privielge
GRANT CREATE TABLE TO c##yh01 CONTAINER=CURRENT;
GRANT CREATE TABLE TO pdbuser01;   --GRANT CREATE TABLE TO pdbuser01; CONTAINER=CURRENT

REVOKE a common privilege
REVOKE CREATE TABLE FROM c##yh01 CONTAINER=ALL;

REVOKE a local prvilege
REVOKE CREATE TABLE FROM pdbuser01 CONTAINER=CURRENT;


COMMON ROLE and LOCAL ROLE
CREATE  ROLE c##role1 CONTAINER=ALL;  --COMMON ROLE
CREAT ROLE pdb01_role1 CONTAINER=CURRENT --LOCAL ROLE

Local roles can be granted to local or common users
Common roles can be granted to local or common users.
Local roles can be granted to common roles.
Common roles can be granted to local roles.

Common role
GRANT CREATE TABLE TO c##role1 CONTAINER=ALL;
GRANT CREATE VIEW TO c##role1 CONTAINER=CURRENT;

Creating Common and Local Profiles
A common profile
A local profile

CREATE PROFILE C##profile1 LIMIT
password_life_time 1000
CONTAINER=ALL;

CREATE PROFILE profile1 LIMIT
password_life_time 1000
CONTAINER=CURRENT;

ALTER USER c##yh01 PROFILE c##profile1 CONTAINER=ALL;

ALTER USER c##yh01 PROFILE c##profile1 CONTAINER=CURRENT;
ALTER USER pdbuser01 PROFILE profile1 CONTAINER=CURRENT;
ALTER USER c##yh01 PROFILE profile1 CONTAINER=CURRENT;

 

CDB、PDB应用

原文地址：https://www.cnblogs.com/wukc/p/13458575.html

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     813人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     435人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

CDB、PDB应用

CDB、PDB应用
数据库2016-02-01

oracle cdb、pdb参考

数据库2024-04-02

CDB/PDB深入学习

数据库2024-04-02

CDB与PDB的连接

数据库2024-04-02

Oracle 12C CDB、PDB常用管理命令

数据库2024-04-02

CDB or PDB打开归档

数据库2024-04-02

Oracle12C新特性——cdb与pdb

数据库2024-04-02

不同CDB之间迁移PDB

数据库2024-04-02

Oracle 12C RMAN transport tablespace from PDB of RAC CDB to remote PDB

数据库2024-04-02

多租户：在Oracle12.2中 从Non-CDB迁移到PDB，从PDB迁移另一个CDB中

数据库2024-04-02

Oracle 12c CDB&PDB 基本维护

数据库2024-04-02

Oracle 12c中怎么利用Non-CDB创建PDB

数据库2024-04-02

CDB、PDB参数的区别有哪些

数据库2024-04-02

Oracle 12c Non CDB 数据库切换成PDB

数据库2024-04-02

oracle12c解决plsql登录CDB和PDB问题

数据库2024-04-02

浅谈oracle 12C的新特性-CDB和PDB

数据库2024-04-02

12C多租户关于CDB、PDB的常用SQL语句

数据库2024-04-02

oracle 12c PDB随CDB启动以及链接PDB的方式是什么

数据库2024-04-02

如何使用RMAN对CDB中的PDB进行复制

数据库2024-04-02

Oracle12cR2的CDB与PDB简单管理操作

数据库2024-04-02
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯