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;