Oracle 12C PDB 维护基础介绍
Oracle 12C PDB 维护基础介绍
先说基本用法：
先按11G之前进行
conn / as sysdba;
create user test identifed by test;
ORA-65096: 公用用户名或角色名无效.
查官方文档得知“试图创建一个通用用户，必需要用C##或者c##开头”，这时候心里会有疑问，什么是common user？不管先建成功了再说
create C##user test identifed by test;
创建成功
SQL>show con_name;
CON_NAME
------------------------------
CDB$ROOT
selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4066409480 PDB$SEED READ ONLY
3 2270995695 PDBORCL MOUNTED
SQL>alter session set container=PDBORCL;
这时再用create user test identifed by test;建立用户就可以了。
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 0PL/SQL
Release 12.1.0.1.0 - Production 0CORE
12.1.0.1.0 Production 0TNS
for
Linux: Version 12.1.0.1.0 - Production 0NLSRTL
Version 12.1.0.1.0 - Production 0
启动关闭pdb
SQL>
startupORACLE
instance started. Total
System Global
Area 597098496 bytesFixed
Size
2291072 bytesVariable
Size
272632448 bytesDatabase
Buffers 314572800 bytesRedo
Buffers 7602176 bytesDatabase
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>
startupPluggable
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
[oracle@xifenfei
~]$ 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
LISTENERVersion
TNSLSNR for
Linux: Version 12.1.0.1.0 - ProductionStart
Date
11-MAY-2013 18:30:54Uptime
0 days 13 hr. 36 min.
8 secTrace
Level
offSecurity
ON:
Local
OS AuthenticationSNMP
OFFListener
Parameter File /u01/app/grid/product/12.1/network/admin/listener.oraListener
Log File /u01/app/grid/diag/tnslsnr/xifenfei/listener/alert/log.xmlListening
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[oracle@xifenfei
~]$ 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 aliasAttempting
to
contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT
= 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))OK
(20 msec)[oracle@xifenfei
~]$ 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 ProductionWith
the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand
Real
Application Testing options SQL>
show con_name; CON_NAME------------------------------PDB1 [oracle@xifenfei
~]$ 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 ProductionWith
the Partitioning, Automatic Storage Management, OLAP, Advanced Analyticsand
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 6509665096,
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 103C##XFF
3 104 SQL>
alter
session set
container=pdb1; Session
altered. SQL>
show con_name CON_NAME------------------------------PDB1SQL>
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'
AND
GRANTEE='C##XFF'; GRANTEE
CON_ID------------------------------
----------C##XFF
1C##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
sysdbaConnected.SQL>
show parameter open_cursors; NAME
TYPE VALUE------------------------------------
----------- ------------------------------open_cursors
integer
500SQL>
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
fixedtables
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
100SQL>
conn / as
sysdbaConnected.SQL>
show parameter open_cursors; NAME
TYPE VALUE------------------------------------
----------- ------------------------------open_cursors
integer
500
这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义
评论暂时关闭