转：ORACLE 12C PDB 维护基础介绍
CDB和PDB是ORACLE 12C一个很亮的新特性,由于他们的引入导致传统的ORACLE数据库管理理念不少发生了改变,这里列举了部分最基本的cdb和pdb管理方式cdb和pdb关系图
ORACLE 12C版本
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
启动关闭pdb
SQL> startup
ORACLE instance started.
Total System Global Area597098496 bytes
Fixed Size 2291072 bytes
Variable Size 272632448 bytes
Database Buffers 314572800 bytes
Redo Buffers 7602176 bytes
Database mounted.
Database opened.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database pdb1 close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter PLUGGABLE database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 READ WRITE
SQL> alter PLUGGABLE database all close;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 MOUNTED
4 3872456618 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> startup
Pluggable Database opened.
SQL>select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
3 3313918585 PDB1 READ WRITE
pdb的管理可以在cdb中进行也可以在pdb中进行,如果是cdb中进行,需要PLUGGABLE关键字,如果是pdb中直接和普通数据库一样
登录pdb
$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:02
Copyright (c) 1991, 2013, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 11-MAY-2013 18:30:54
Uptime 0 days 13 hr. 36 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/product/12.1/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xifenfei)(PORT=5500))
(Security=(my_wallet_directory=/u01/oracle/12.1/db_1/admin/cdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "cdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "cdbXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
The command completed successfully
$ tnsping pdb1
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 12-MAY-2013 08:07:09
Copyright (c) 1997, 2013, Oracle.All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (20 msec)
$ sqlplus sys/xifenfei@pdb1 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:08:02 2013
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
PDB1
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun May 12 08:09:14 2013
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
PDB1
pdb可以通过alter session container进入也可以直接通过tns方式登录
创建用户
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4048821679 PDB$SEED READ ONLY
3 3313918585 PDB1 READ WRITE
4 3872456618 PDB2 MOUNTED
SQL> create user xff identified by xifenfei;
create user xff identified by xifenfei
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:An attempt was made to create a common user or role with a name
// that wass not valid for common users or roles.In addition to
// the usual rules for user and role names, common user and role
// names must start with C## or c## and consist only of ASCII
// characters.
// *Action: Specify a valid common user or role name.
//
SQL> create user c##xff identified by xifenfei;
User created.
SQL> SELECT USERNAME,CON_ID,USER_ID FROM CDB_USERS WHERE USERNAME='C##XFF';
USERNAME CON_ID USER_ID
---------- ---------- ----------
C##XFF 1 103
C##XFF 3 104
SQL> alter session set container=pdb1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> create user xff identified by xifenfei;
User created.
SQL> create user c##abc identified by xifenfei;
create user c##abc identified by xifenfei
*
ERROR at line 1:
ORA-65094: invalid local user or role name
创建用户默认的是container=all,在cdb中只能创建全局用户(c##开头),会在cdb和所有的pdb中创建该用户(但是pdb中的全局用户需要另外授权才能够在pdb中访问)。在pdb中只能创建的用户为本地用户
用户授权
SQL> grant connect to c##xff;
Grant succeeded.
SQL> select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE='C##XFF';
GRANTEE CON_ID
------------------------------ ----------
C##XFF 1
SQL> grant resource to c##xff container=all;
Grant succeeded.
SQL>select GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='RESOURCE' ANDGRANTEE='C##XFF';
GRANTEE CON_ID
------------------------------ ----------
C##XFF 1
C##XFF 3
用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权
修改参数
SQL> alter system set open_cursors=500 container=all;
System altered.
SQL> conn sys/xifenfei@pdb1 as sysdba
Connected.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
SQL>alter system set open_cursors=100;
alter system set open_cursors=100
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter database open;
Database altered.
SQL> alter system set open_cursors=100;
System altered.
SQL>show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 100
SQL> conn / as sysdba
Connected.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 500
这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义 学习了
12c有很多值得我们去学习的地方，目前正在研究，以后会写一些心得体会 有个疑问PDB有公司正式环境开始用了吗？具体有什么好处呢，方便迁移？
页:
[1]