ORA-65096: 公用用户名或角色名无效引发的思考

解决方式：

alter session set "_ORACLE_SCRIPT"=true;alter session set container=PDBORCL;

原因：

查官方文档得知“试图创建一个通用用户，必需要用C##或者c##开头”，这时候心里会有疑问，什么是common user？不管先建成功了再说

create C##user test identifed by test;

创建成功

CDB和PDB是ORACLE 12C一个很亮的新特性,由于他们的引入导致传统的ORACLE数据库管理理念不少发生了改变,这里列举了部分最基本的cdb和pdb管理方式
cdb和pdb关系图

ORACLE 12C版本

SQL>select* fromv$version;

 

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

OracleDatabase12c 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

TNSforLinux: 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 GlobalArea  597098496 bytes

FixedSize                 2291072 bytes

VariableSize            272632448 bytes

DatabaseBuffers          314572800 bytes

Redo Buffers                7602176 bytes

Databasemounted.

Databaseopened.

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;

 

    CON_ID       DBID NAME                          OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4048821679 PDB$SEED                       READONLY

         3 3313918585 PDB1                           MOUNTED

         4 3872456618 PDB2                           MOUNTED

 

SQL>alterPLUGGABLE databasepdb1 open;

 

Pluggabledatabasealtered.

 

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;

 

    CON_ID       DBID NAME                          OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4048821679 PDB$SEED                       READONLY

         3 3313918585 PDB1                           READWRITE

         4 3872456618 PDB2                           MOUNTED

 

SQL>alterPLUGGABLE databasepdb1 close;

 

Pluggabledatabasealtered.

 

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;

 

    CON_ID       DBID NAME                          OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4048821679 PDB$SEED                       READONLY

         3 3313918585 PDB1                           MOUNTED

         4 3872456618 PDB2                           MOUNTED

 

SQL>alterPLUGGABLE databaseall open;

 

Pluggabledatabasealtered.

 

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;

 

    CON_ID       DBID NAME                          OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4048821679 PDB$SEED                       READONLY

         3 3313918585 PDB1                           READWRITE

         4 3872456618 PDB2                           READWRITE

 

SQL>alterPLUGGABLE databaseall close;

 

Pluggabledatabasealtered.

 

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$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> startup

PluggableDatabaseopened.

SQL> selectcon_id,dbid,NAME,OPEN_MODEfromv$pdbs;

 

    CON_ID       DBID NAME                          OPEN_MODE

---------- ---------- ------------------------------ ----------

         3 3313918585 PDB1                           READWRITE

pdb的管理可以在cdb中进行也可以在pdb中进行,如果是cdb中进行,需要PLUGGABLE关键字,如果是pdb中直接和普通数据库一样

登录pdb

[oracle@xifenfei ~]$ lsnrctl status

 

LSNRCTLforLinux: Version 12.1.0.1.0 - Production on12-MAY-2013 08:07:02

 

Copyright (c) 1991, 2013, Oracle.  Allrights reserved.

 

Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521)))

STATUSofthe LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR forLinux: Version 12.1.0.1.0 - Production

StartDate               11-MAY-2013 18:30:54

Uptime                    0 days 13 hr. 36 min. 8 sec

TraceLevel              off

Security                 ON:LocalOS 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) 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 alias

Attemptingtocontact (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.

 

 

Connectedto:

OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Withthe Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

andReal 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.

 

 

Connectedto:

OracleDatabase12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Withthe Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

andReal Application Testing options

 

SQL>altersession setcontainer=pdb1;

 

Session altered.

 

SQL> show con_name;

 

CON_NAME

------------------------------

PDB1

pdb可以通过alter session container进入也可以直接通过tns方式登录

创建用户

SQL> show con_name;

 

CON_NAME

------------------------------

CDB$ROOT

 

SQL>selectcon_id,dbid,NAME,OPEN_MODEfromv$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

            *

ERRORatline 1:

ORA-65096: invalid common useror role name

 

 

SQL> !oerr ora 65096

65096, 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        103

C##XFF              3        104

 

SQL>altersession setcontainer=pdb1;

 

Session altered.

 

SQL> show con_name

 

CON_NAME

------------------------------

PDB1

SQL>createuser xff identified byxifenfei;

 

Usercreated.

 

SQL>createuser c##abc identified byxifenfei;

createuser c##abc identified byxifenfei

            *

ERRORatline 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                                  1

C##XFF                                  3

用户授权默认情况下是只会给当前container,在cdb中也可以指定container=all,对所有open的pdb且存在该用户都进行授权

修改参数

SQL>altersystem setopen_cursors=500 container=all;

 

System altered.

 

SQL> conn sys/xifenfei@pdb1 assysdba

Connected.

SQL> show parameter open_cursors;

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

open_cursors                        integer    500

SQL> altersystem setopen_cursors=100;

 altersystem setopen_cursors=100

*

ERRORatline 1:

ORA-01219:databaseor pluggable databasenot open: queries allowed onfixed

tablesorviews only

 

 

SQL>alterdatabase open;

 

Databasealtered.

 

SQL>altersystem setopen_cursors=100;

 

System altered.

 

SQL>  show parameter open_cursors;

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

open_cursors                        integer    100

SQL> conn / assysdba

Connected.

SQL> show parameter open_cursors;

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

open_cursors                        integer    500

这里可以看到在cdb中修改,pdb会继承进去;如果在pdb中修改会覆盖pdb从cdb中继承的参数含义