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中繼承的參數含義