广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >CDB、PDB应用
  • 764

0
分享到

CDB、PDB应用

CDBPDB应用 数据库入门数据库基础教程数据库mysql 2016-02-01 20:02:27 764人浏览 无得
摘要

环境：oracle12c  常用字典 DBA_xxx All objects in the root or a pluggable database ALL_xxx Objects accessible by the current us

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

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: CDB、PDB应用

本文链接: https://www.lsjlt.com/news/7777.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • CDB、PDB应用
    环境：oracle12c  常用字典 DBA_xxx All objects in the root or a pluggable database ALL_xxx Objects accessible by the current us...
    99+
    2016-02-01
    CDB PDB应用 数据库入门 数据库基础教程 数据库 mysql
  • oracle cdb、pdb参考
    CDB、PDB概念介绍CDB与PDB是Oracle 12C引入的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多...
    99+
    2022-10-18
  • CDB与PDB的连接
    1.连接sqlplus system/xxxxx@10.8.5.206:1521/pdbtestALTER SESSION SET CONTAINER = PDB$SEED;2.再CDB中切换连接C:\Us...
    99+
    2022-10-18
  • CDB/PDB深入学习
    改变PDB大小SQL> show pdbs ...
    99+
    2022-10-18
  • Oracle12C新特性——cdb与pdb
     Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数...
    99+
    2022-10-18
  • CDB or PDB打开归档
    11g 怎么开归档，多租户就怎么打开归档就好了。 APPLIES TO: Oracle Database - Enterprise Edition - Version 12.1.0.1 and later...
    99+
    2022-10-18
  • 不同CDB之间迁移PDB
    测试环境源环境目标环境主机操作系统 Oracle Linux 7.2    主机名zhankys         Oracle...
    99+
    2022-10-18
  • Oracle 12C RMAN transport tablespace from PDB of RAC CDB to remote PDB
    这个例子是将Linux 平台上的RAC CDB数据库中PDB(jypdb)数据库中的tts,cs表空间(tts,cs用户的缺省永久表空间)传输到Linux 平台上的单实例CDB数据库中的PDB(jypd...
    99+
    2022-10-18
  • Oracle 12C CDB、PDB常用管理命令
    --查看PDB信息（在CDB模式下） show pdbs --查看所有pdb select name,open_mode from v$pdbs; --v$pdbs为PDB信息视图 sele...
    99+
    2022-10-18
  • 多租户：在Oracle12.2中 从Non-CDB迁移到PDB，从PDB迁移另一个CDB中
    1、从PDB或者Non-CDB迁移到另一个CDB的PDB准备从PDB或者Non-CDB迁移到CDB容器数据库中PDB，由于PDB和Non-CDB迁移到CDB的步骤几乎相同，PDB和Non-CDB迁移非常相似...
    99+
    2022-10-18
  • Oracle 12c CDB&PDB 基本维护
    1.查看数据库是否为多租户数据库（CDB）SQL> SELECT CDB FROM V$DATABASE; CDB YES2.查看当前容器名字以下命令只能在12c客户端输入SQL> sh...
    99+
    2022-10-18
  • CDB、PDB参数的区别有哪些
    本篇内容主要讲解“CDB、PDB参数的区别有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“CDB、PDB参数的区别有哪些”吧!1、CDB和PDB共用一个参数...
    99+
    2022-10-18
  • Oracle 12c中怎么利用Non-CDB创建PDB
    本篇文章给大家分享的是有关Oracle 12c中怎么利用Non-CDB创建PDB，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。可以移动Non-...
    99+
    2022-10-18
  • Oracle12cR2的CDB与PDB简单管理操作
    Oracle 12C引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB...
    99+
    2022-10-18
  • ProxmoxVE 之 安装oracle12C 数据库（CDB和PDB）
      上面左边是我的个人微信，如需进一步沟通，请加微信。  右边是我的公众号“Openstack私有云”，如有兴趣，请关注。    前两天在PVE环境上搭建了...
    99+
    2022-10-18
  • 浅谈oracle 12C的新特性-CDB和PDB
    最近看到好多人都在尝试oracle中的12C新特性-容器数据库，今年3月orcle推出了Release2版本，可以算是一个稳定版本了。下午着手尝试了一下，还是蛮不错得1.前言CDB与PDB是Oracle&n...
    99+
    2022-10-18
  • oracle12c解决plsql登录CDB和PDB问题
    今天闲来无事，在win10上安装了oracle12c，想着平时可以操作操作，在安装过程中有一个创建为容器数据库的勾选，虽然不明白是什么意思，我还是勾选了。 后来在使用plsql登录的时候，发现没有sc...
    99+
    2022-10-18
  • Oracle 12c Non CDB 数据库切换成PDB
    数据库从10.2（11g）升级到12c或者在12c中创建的,数据库就是NON CDB，和12c 之前的版本就没有什么区别,所以一般会把12cnoncdb转为pdb进行管理。 测试步骤如下： 一：源库 1. ...
    99+
    2022-10-18
  • oracle 12c PDB随CDB启动以及链接PDB的方式是什么
    今天就跟大家聊聊有关oracle 12c PDB随CDB启动以及链接PDB的方式是什么，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。本来不知道有这个...
    99+
    2022-10-19
  • ORACLE 12C 开机自动启动监听、CDB、PDB
    linux下数据库实例监听开机自启动设置2018年8月1日 zhanky 测试介绍系统版本：Oracle linux 7.2 x64数据库版本：Oracle Database 12c Enterprise ...
    99+
    2022-10-18
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作