Basic Introduction to Oracle 12C PDB Maintenance

Basic Introduction to Oracle 12C PDB Maintenance

Basic usage:

Run the command before 11G.

Conn/as sysdba;

Create user test identifed by test;

ORA-65096: The public user name or role name is invalid.

Check the official document and find that "to create a general-purpose user, you must start with C # Or c #." At this time, I have some questions. What is a common user? No matter whether it is created first

Create C # user test identifed by test;

Created successfully

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;

In this case, create user test identifed by test; and create a user.

CDB and PDB are a very bright new feature of Oracle 12C. Due to their introduction, many traditional ORACLE database management concepts have changed. Here we list some of the most basic cdb and pdb management methods.
Relationship between cdb and pdb

ORACLE 12C version

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 0 CORE
12.1.0.1.0 Production 0TNS
For

Linux: Version 12.1.0.1.0-Production 0 NLSRTL
Version 12.1.0.1.0-Production 0

Enable and disable 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

The management of pdb can be performed in cdb or pdb. If it is performed in cdb, the PLUGGABLE keyword is required. If it is pdb, it is directly the same as that of a common database.

Log on to 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,201 3, Oracle. All

Rights reserved. Connecting
To

(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = xifenfei) (PORT = 1521) STATUS
Of

The LISTENER ------------------------ Alias
LISTENERVersion
TNSLSNR

Linux: Version 12.1.0.1.0-ProductionStart
Date

11-MAY-2013 18: 30: 54 Uptime
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)

This service... Service
"Cdb"

Has 1 instance (s). Instance
"Cdb ",
Status READY, has 1 handler (s)

This service... Service
"CdbXDB"

Has 1 instance (s). Instance
"Cdb ",
Status READY, has 1 handler (s)

This service... Service
"Pdb1"

Has 1 instance (s). Instance
"Cdb ",
Status READY, has 1 handler (s)

This service... Service
"Pdb2"

Has 1 instance (s). Instance
"Cdb ",
Status READY, has 1 handler (s)

This service...
Command completed successfully [oracle @ xifenfei
~] $ Tnsping pdb1 TNS
Ping Utility

Linux: Version 12.1.0.1.0-Production on

12-MAY-2013 08:07:09 Copyright
(C) 1997,201 3, Oracle. All

Rights reserved. Used
Parameter files: Used
TNSNAMES adapter

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

Sysdba SQL * Plus:
Release 12.1.0.1.0 Production on

Sun May 12 08:08:02 2013 Copyright
(C) 1982,201 3, 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/

Sysdba SQL * Plus:
Release 12.1.0.1.0 Production on

Sun May 12 08:09:14 2013 Copyright
(C) 1982,201 3, 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 can be accessed through alter session container or directly logged on through tns

Create user

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

Xifenfei; create

User
Xff identified

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

Create
A common user

Or
Role

A name //
That wass not

Valid

Common users or

Roles. In

Addition //
The usual rules

User
And
Role names, common user

And
Role //
Names must start

C ## or

C ## and

Consist only

Of
ASCII //
Characters .//
* Action:
Specify a valid common user

Or
Role name. // SQL>
Create

User
C ## xff identified

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
Xifenfei; User

Created. SQL>
Create

User
C ## abc identified

Xifenfei; create

User
C ## abc identified

Xifenfei * ERROR
At

Line 1: ORA-65094:
Invalid local

User
Or
Role name

The Default User Creation method is container = all. In cdb, only global users can be created (starting with c ), this user will be created in cdb and all pdb (but global users in pdb need additional authorization to be able to access in pdb ). Only Local Users can be created in pdb.

User authorization

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

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

By default, user authorization only applies to the current container. In cdb, you can specify container = all to grant permissions to all open pdb users.

Modify parameters

SQL>
Alter

System set

Open_cursors = 500 container = all; System
Altered. SQL>
Conn sys/xifenfei @ pdb1

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/

SysdbaConnected. SQL>
Show parameter open_cursors; NAME

Type value ------------------------------------
----------- ------------------------------ Open_cursors
Integer

500

Here we can see that modification in cdb will be inherited by pdb; modification in pdb will overwrite the parameter meaning inherited by pdb from cdb.