12c: Working with Oracle Multitenant

Working with Oracle Multitenant
Environment: Oracle Database 12c (12.1.0) Installed on Windows 7

Creating a CDB creates a service whose name is the CDB name. As a side effect of creating a PDB in the CDB, a service is created inside it with a property that identifies it as the initial current container. The service is also started as a side effect of creating the PDB. The service has the same name as the PDB. Although its metadata is recorded inside the PDB, the invariant is maintained so that a service name is unique within the entire CDB.

Connecting to the CDB Root or to a PDB

C:\Users\inam.HOME>sqlplus sys/oracle@localhost:1521/or12c.home.gov.sa as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 11:14:14 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
OR
C:\Users\inam.HOME>sqlplus sys/sys@or12c as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1
SQL> select name, con_id from v$active_services order by 1;

NAME                                                                 CON_ID
---------------------------------------------------------------- ----------
SYS$BACKGROUND                                                            1
SYS$USERS                                                                 1
or12c.home.gov.sa                                                         1
or12cXDB                                                                  1
pdbor12c.home.gov.sa                                                      3



Use the Easy Connect syntax to connect to the PDB unless a net service name is configured in the tnsnames for the PDB service.

SQL> connect sys/sys@inam-pc:1521/pdbor12c.home.gov.sa as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
PDBOR12C
SQL> show con_id

CON_ID
------------------------------
3

===================================================
Creating a PDB from the Seed PDB
Before starting the PDB creation, create a destination directory for the data files.
D:\app\Inam\oradata\or12c\pdb2or12c

1- Create a PDB from the seed PDB.
a)
set oracle_sid=or12c
sqlplus / as sysdba

create pluggable database pdb2or12c
admin user pdb2or12c_admin identified by oracle
roles = (DBA)
FILE_NAME_CONVERT=('D:\app\Inam\oradata\or12c\pdbseed','D:\app\Inam\oradata\or12c\pdb2or12c');

SQL> create pluggable database pdb2or12c
  2  admin user pdb2or12c_admin identified by oracle
  3  roles = (DBA)
  4  FILE_NAME_CONVERT=('D:\app\Inam\oradata\or12c\pdbseed','D:\app\Inam\oradata\or12c\pdb2or12c');

Pluggable database created.

b) Verify the status, open mode, and service names of the PDBs in the CDB. If the status for a PDB shows NEEDS SYNC,
you can connect to the PDB and run the DBMS_PDB.SYNC_PDB procedure to change the status to NORMAL.

select pdb_name, status from cdb_pdbs;

PDB_NAME	STATUS
PDBOR12C	NORMAL
PDB$SEED	NORMAL
PDB2OR12C	NEW

select name, open_mode from v$pdbs

NAME	OPEN_MODE
PDB$SEED	READ ONLY
PDBOR12C	READ WRITE
PDB2OR12C	MOUNTED

select name, con_id from v$active_services order by 1;

NAME	CON_ID
SYS$BACKGROUND	1
SYS$USERS	1
or12c.home.gov.sa	1
or12cXDB	1
pdb2or12c.home.gov.sa	4
pdbor12c.home.gov.sa	3

c) List the PDB data files.
select name from v$datafile where con_id=4;

NAME
D:\APP\INAM\ORADATA\OR12C\PDB2OR12C\SYSTEM01.DBF
D:\APP\INAM\ORADATA\OR12C\PDB2OR12C\SYSAUX01.DBF

   
2- Managing the CDB and the PDBs
a) Ensure that you are connected to the root as SYSDBA.
b) Shut down the CDB.
SQL> show con_name

CON_NAME
--------------------
CDB$ROOT

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

This operation first will close all PDBs, then dismount the control files, and finally shut down the instance.

c) Startup CDB, it will first start the instance, then mount the control files, and finally open only the root container.
C:\Users\inam.HOME>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 11:52:34 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2403064 bytes
Variable Size             973079816 bytes
Database Buffers          587202560 bytes
Redo Buffers                7323648 bytes
Database mounted.
Database opened.

d) Verify the open mode of the PDBs.
select name, open_mode from v$pdbs

NAME	OPEN_MODE
PDB$SEED	READ ONLY
PDBOR12C	MOUNTED
PDB2OR12C	MOUNTED

e) Open a PDB.
SQL> alter pluggable database PDB2OR12C open;

Pluggable database altered.

select name, open_mode from v$pdbs;

NAME	OPEN_MODE
PDB$SEED	READ ONLY
PDBOR12C	MOUNTED
PDB2OR12C	READ WRITE

f) Open all PDBs at once
SQL> alter pluggable database all open;

Pluggable database altered.

g) Close a PDB.
SQL> alter pluggable database PDB2OR12C close immediate;

Pluggable database altered.

h) Close all PDBs at once.
SQL> alter pluggable database all close immediate;

Pluggable database altered.

i) Create a trigger to open all PDBs after CDB startup.  In a development environment, it is convenient to ensure that all PDBs open with the instance.

SQL> create or replace trigger Sys.OpenPDBS_After_Startup after startup on database
  2  begin
  3     execute immediate 'alter pluggable database all open';
  4  end After_Startup;
  5  /

Trigger created.

shutdown immediate

startup

select name, open_mode from v$pdbs;
j) Rename a PDB, you must be connected to the PDB to rename it other wise you will get error.
SQL> alter pluggable database PDBOR12C close immediate;
Pluggable database altered.

SQL> alter pluggable database PDBOR12C open restricted;

Pluggable database altered.

select name, restricted from v$pdbs;

NAME	RESTRICTED
PDB$SEED	NO
PDBOR12C	YES
PDB2OR12C	NO

SQL>  alter pluggable database PDBOR12C rename global_name to PDB1OR12C;
 alter pluggable database PDBOR12C rename global_name to PDB1OR12C
                                                         *
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database

SQL> connect sys/oracle@inam-pc:1521/PDBOR12C.home.gov.sa as sysdba
Connected.

SQL> alter pluggable database PDBOR12C rename global_name to PDB1OR12C;

Pluggable database altered.

Close and open the PDB.
SQL> alter pluggable database close immediate;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

select pdb_name, status from cdb_pdbs;

PDB_NAME	STATUS
PDB1OR12C	NORMAL
PDB$SEED	NORMAL
PDB2OR12C	NORMAL

  
10- Managing Storage in a CDB and Its PDBs
SQL> connect / as sysdba
SQL> show con_id

CON_ID
------------------
1

select tablespace_name, con_id from cdb_tablespaces where con_id=1;

TABLESPACE_NAME	CON_ID
SYSTEM	1
SYSAUX	1
UNDOTBS1	1
TEMP	1
USERS	1

select file_name, con_id from cdb_data_files where con_id=1;

FILE_NAME	CON_ID
D:\APP\INAM\ORADATA\OR12C\USERS01.DBF	1
D:\APP\INAM\ORADATA\OR12C\UNDOTBS01.DBF	1
D:\APP\INAM\ORADATA\OR12C\SYSAUX01.DBF	1
D:\APP\INAM\ORADATA\OR12C\SYSTEM01.DBF	1

select file_name, con_id from cdb_temp_files where con_id=1; 

FILE_NAME	CON_ID
D:\APP\INAM\ORADATA\OR12C\TEMP01.DBF	1

a) Create a permanent tablespace in the root.
SQL> create tablespace cdata datafile 'D:\APP\INAM\ORADATA\OR12C\CDATA01.DBF' SIZE 10M;

Tablespace created.

b) Create a temporary tablespace in the root.
SQL> create temporary tablespace temp_root tempfile 'D:\APP\INAM\ORADATA\OR12C\TEMPROOT01.DBF' SIZE 10M;

Tablespace created.

select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY' and con_id=1
c) Create a tablespace in a PDB.
SQL> connect system/sys@inam-pc:1521/PDB1OR12C.home.gov.sa
Connected.

SQL> create tablespace ldata datafile 'D:\APP\INAM\ORADATA\OR12C\PDBOR12C\LDATA01.DBF' SIZE 10M;

Tablespace created.

When you are connected to a PDB, the CDB_xxx or DBA_xxx views show the same information.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;

SQL> select file_name, con_id from cdb_data_files order by con_id;

SQL> select file_name from dba_data_files; 

11- Security in PDBs 
Each container in a CDB holds common and local users. Any user, common or local, can only exercise the granted privileges inside the specific container to which it is connected.

• Common users are created from the root and are  automatically replicated in each PDB except the seed PDB. Common users can connect to any PDB. The name assigned to a common user must start with c##.
  
• Local users are created in a PDB they need to access. Local users can only connect to the PDB where they are created. They are not visible to the other PDBs of the same CDB.

 a) Create a common user while you are connected to the root.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##u1 identified by oracle container=all;

User created.

grant create session to c##u1 container=all

select username, common, con_id from cdb_users where username like 'C##%';

USERNAME	COMMON	CON_ID
C##U1	YES	1
C##U1	YES	3

b) Connect as a common user in a PDB.

SQL> connect system/sys@inam-pc:1521/PDB1OR12C.home.gov.sa
Connected.
SQL> create user u2 identified by oracle;

User created. 

12- Dropping PDBs
When you drop a PDB, you can specify to keep or delete the data files. Keeping the data files is required when you unplug a PDB and want to plug it into another CDB (or the same CDB). The data files are reused when plugging in the PDB.

a) Close the PDBs.

connect / as sysdba

alter pluggable database all close immediate;

select name, open_mode from v$pdbs;

b)Drop the PDBs, including their data files.
SQL> drop pluggable database PDB2OR12C including datafiles;
Pluggable database dropped.

select name from v$pdbs;