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$ROOTselectcon_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* fromv$version; BANNER
CON_ID--------------------------------------------------------------------------------
----------Oracle
Database12c 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
forLinux: Version 12.1.0.1.0 - Production 0NLSRTL
Version 12.1.0.1.0 - Production 0启动关闭pdbSQL>
startupORACLE
instance started. Total
System GlobalArea 597098496 bytesFixed
Size 2291072 bytesVariable
Size 272632448 bytesDatabaseBuffers 314572800 bytesRedo
Buffers 7602176 bytesDatabasemounted.Databaseopened.SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 MOUNTED 4
3872456618 PDB2 MOUNTED SQL>
alterPLUGGABLE databasepdb1 open; Pluggable
databasealtered. SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 READWRITE 4
3872456618 PDB2 MOUNTED SQL>
alterPLUGGABLE databasepdb1 close; Pluggable
databasealtered. SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 MOUNTED 4
3872456618 PDB2 MOUNTED SQL>
alterPLUGGABLE databaseall
open; Pluggable
databasealtered. SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 READWRITE 4
3872456618 PDB2 READWRITE SQL>
alterPLUGGABLE databaseall
close; Pluggable
databasealtered. SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 MOUNTED 4
3872456618 PDB2 MOUNTED SQL>
altersession setcontainer=pdb1; Session
altered. SQL>
startupPluggable
Databaseopened.SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 3
3313918585 PDB1 READWRITEpdb的管理可以在cdb中进行也可以在pdb中进行,如果是cdb中进行,需要PLUGGABLE关键字,如果是pdb中直接和普通数据库一样登录pdb[oracle@xifenfei
~]$ lsnrctl status LSNRCTL
forLinux: Version 12.1.0.1.0 - Production on12-MAY-2013 08:07:02 Copyright
（c） 1991, 2013, Oracle. Allrights reserved. Connecting
to（DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=xifenfei）（PORT=1521）））STATUS
ofthe LISTENER------------------------Alias
LISTENERVersion
TNSLSNR forLinux: 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:
LocalOS 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） forthis service...Service
"cdb"has 1 instance（s）. Instance
"cdb",
status READY, has 1 handler（s） forthis service...Service
"cdbXDB"has 1 instance（s）. Instance
"cdb",
status READY, has 1 handler（s） forthis service...Service
"pdb1"has 1 instance（s）. Instance
"cdb",
status READY, has 1 handler（s） forthis service...Service
"pdb2"has 1 instance（s）. Instance
"cdb",
status READY, has 1 handler（s） forthis service...The
command completed successfully[oracle@xifenfei
~]$ tnsping pdb1 TNS
Ping Utility forLinux: Version 12.1.0.1.0 - Production on12-MAY-2013 08:07:09 Copyright
（c） 1997, 2013, Oracle. Allrights reserved. Used
parameter files: Used
TNSNAMES adapter toresolve the aliasAttempting
tocontact （DESCRIPTION = （ADDRESS = （PROTOCOL = TCP）（HOST = xifenfei）（PORT
= 1521））（CONNECT_DATA = （SERVER = DEDICATED）（SERVICE_NAME = pdb1）））OK
（20 msec）[oracle@xifenfei
~]$ sqlplus sys/xifenfei@pdb1 assysdba SQL*Plus:
Release 12.1.0.1.0 Production onSun May 12 08:08:02 2013 Copyright
（c） 1982, 2013, Oracle. Allrights reserved. Connected
to:Oracle
Database12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWiththe Partitioning, Automatic Storage Management, OLAP, Advanced AnalyticsandReal
Application Testing options SQL>
show con_name; CON_NAME------------------------------PDB1 [oracle@xifenfei
~]$ sqlplus / assysdba SQL*Plus:
Release 12.1.0.1.0 Production onSun May 12 08:09:14 2013 Copyright
（c） 1982, 2013, Oracle. Allrights reserved. Connected
to:Oracle
Database12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWiththe Partitioning, Automatic Storage Management, OLAP, Advanced AnalyticsandReal
Application Testing options SQL>
altersession setcontainer=pdb1; Session
altered. SQL>
show con_name; CON_NAME------------------------------PDB1pdb可以通过alter session container进入也可以直接通过tns方式登录创建用户SQL>
show con_name; CON_NAME------------------------------CDB$ROOT SQL>
selectcon_id,dbid,NAME,OPEN_MODE
fromv$pdbs; CON_ID
DBID NAME OPEN_MODE----------
---------- ------------------------------ ---------- 2
4048821679 PDB$SEED READONLY 3
3313918585 PDB1 READWRITE 4
3872456618 PDB2 MOUNTED SQL>
createuser
xff identified byxifenfei;createuser
xff identified byxifenfei *ERROR
atline 1:ORA-65096:
invalid common useror
role name SQL>
！oerr ora 6509665096,
00000, "invalid
common user or role name"//
*Cause: An attempt was made tocreate
a common useror
role witha name//
that wass notvalid forcommon users orroles. Inaddition to//
the usual rules foruser
and
role names, common userand
role //
names must start withC## orc## andconsist onlyof
ASCII //
characters.//
*Action:
Specify a valid common useror
role name.// SQL>
createuser
c##xff identified byxifenfei; Usercreated. SQL>
SELECTUSERNAME,CON_ID,USER_ID FROMCDB_USERS WHEREUSERNAME="C##XFF"; USERNAME
CON_ID USER_ID----------
---------- ----------C##XFF
1 103C##XFF
3 104 SQL>
altersession setcontainer=pdb1; Session
altered. SQL>
show con_name CON_NAME------------------------------PDB1SQL>
createuser
xff identified by
xifenfei; Usercreated. SQL>
createuser
c##abc identified byxifenfei;createuser
c##abc identified byxifenfei *ERROR
atline 1:ORA-65094:
invalid localuser
or
role name创建用户默认的是container=all,在cdb中只能创建全局用户（c##开头）,会在cdb和所有的pdb中创建该用户（但是pdb中的全局用户需要另外授权才能够在pdb中访问）。在pdb中只能创建的用户为本地用户用户授权SQL>
grantconnect
to
c##xff; Grantsucceeded. SQL>
selectGRANTEE,con_id fromcdb_ROLE_PRIVS whereGRANTED_ROLE="CONNECT"AND
GRANTEE="C##XFF"; GRANTEE
CON_ID------------------------------
----------C##XFF
1 SQL>
grantresource toc##xff container=all; Grantsucceeded. SQL>
selectGRANTEE,con_id fromcdb_ROLE_PRIVS whereGRANTED_ROLE="RESOURCE"AND
GRANTEE="C##XFF"; GRANTEE
CON_ID------------------------------
----------C##XFF
1C##XFF
3用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权修改参数SQL>
altersystem setopen_cursors=500 container=all; System
altered. SQL>
conn sys/xifenfei@pdb1 assysdbaConnected.SQL>
show parameter open_cursors; NAME TYPE VALUE------------------------------------
----------- ------------------------------open_cursors
integer 500SQL>
altersystem setopen_cursors=100; altersystem setopen_cursors=100*ERROR
atline 1:ORA-01219:
databaseor
pluggable databasenot
open:
queries allowed onfixedtables
orviews only SQL>
alterdatabase
open; Databasealtered. SQL>
altersystem setopen_cursors=100; System
altered. SQL>
show parameter open_cursors; NAME TYPE VALUE------------------------------------
----------- ------------------------------open_cursors
integer 100SQL>
conn / assysdbaConnected.SQL>
show parameter open_cursors; NAME TYPE VALUE------------------------------------
----------- ------------------------------open_cursors
integer 500这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址