ORACLE 12c

12c
Links: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/pdb/pdb_unplug_plug/pdb_unplug_plug.html
           https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89250
          http://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1.php
Installation is same as 11g but needs more storage space.
There are two important parts
CDB (Container Database)
PDB (Pluggable Database)






1 CDB holds multiple PDBs
CDB is created when we create a Database and a PDB is also created with it.
We can create CDB and PDB with DBCA , if we select the option “ container database”  it will become a container database otherwise it will be a normal database.

Data Files:
Container Database (CDB): SYSTEM,SYSAUX,UDNO,TEMP,USERS
Pluggable Database(PDB): SYSTEM,SYSAUX,TEMP,USERS    (it uses the UNDO tablespace of CDB)
Select * from cdb_data_files;
Select * from dba_data_files;    There are equal number of cdb as dba (just replace dba with cdb)      


We can add new tablespace to each of the PDB as and when required by first connection to that PDB and executing the normal create tablespace sql.
Views
The introduction of the multitenant option brings with it an extra layer of data dictionary views, allowing reporting across the root container and the pluggable databases (PDBs). Ignoring editions for the moment, prior releases had the following hierarchy.
DBA_ : All objects in the database.
|
--ALL_ : Objects accessible by the current user, including those owned by the current user.
  |
  --USER_ : Objects owned by the current user.
With Oracle 12c, an extra layer is added to the hierarchy.
CDB_ : All objects in the root container and all PDBs.
|
--DBA_ : All objects in the root container or PDB, depending on the current settings.
  |
  --ALL_ : Objects accessible by the current user, including those owned by the current user.
    |
    --USER_ : Objects owned by the current user.

LOG Files:
               Both CDB and PDB uses the same logfiles.

Users and Roles:
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
           Likewise, there are two types of roles.
Common Role : The role is present in all containers (root and all PDBs).
Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Create Common Users
CONN / AS SYSDBA

-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;

-- Create the common user using the default CONTAINER setting.
CREATE USER c##test_user2 IDENTIFIED BY password1;
GRANT CREATE SESSION TO c##test_user2;

Create Local Users
CONN / AS SYSDBA

-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

-- Connect to a privileged user in the PDB.
CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.
CREATE USER test_user4 IDENTIFIED BY password1;
GRANT CREATE SESSION TO test_user4;

    
Users and Schemas in a CDB and PDB
 



Connection: SQL PLUS
Connecting to CDB:  sqlplus sys/oracle@//standby:1521/cdb1
Connecting to PDB: sqlplus sys/oracle@//standby:1521/pdb1
Altering Session: alter session set container=pdb2;
   alter session set container=pdb$seed;
   alter session set container=cdb$root;

There is no SID for PDB  cannot connect to PDB directly from SQL Developer

To connect PDB from SQL Developer we have to configure tnsname.ora as follows

TNSNAMES.ORA
CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB1)
    )
  )

PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB2)
    )
  )

PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )



IMPORTANT DATA DICTIONARY VIEWS
--To view information about the containers in a CDB:
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

--To view information about PDBs:
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

--To view the open status of each PDB:
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

--Showing the Tables Owned by Specific Schemas in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND
t.OWNER IN('C##TEST_USER1','TEST') AND
p.PDB_ID = t.CON_ID --and t.TABLE_NAME='TEST'
ORDER BY p.PDB_ID;
 

--Showing the Users in Multiple PDBs
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;

--Showing the Data Files for Each PDB in a CDB
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
-Showing the Temp Files in a CDB
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;

--Showing the Services Associated with PDBs
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
  ORDER BY PDB;

--To determine the current container ID:
SHOW CON_ID

--To determine the current container Name:
SHOW CON_NAME
                                             




                                                                      PLUG AND UNPLUG PDB
Connect to CDB1 with sys as sysdba
UNPLUG PDB
Use SQL*Plus to close the PDBs before they can be unplugged.
alter pluggable database pdb1 close immediate;
Unplug the closed PDB and then specify the path and name of the XML file.
alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/pdb1.xml';
Drop the closed PDB and keep the data files.
drop pluggable database pdb1 keep datafiles;
Verify the status of the unplugged PDB
select pdb_name, status from cdb_pdbs where pdb_name in ('PDB1');

COMPATIBILITY CHECK
sqlplus / as sysdba
[if cdb2 is not started up, start it up now.]
set serveroutput on
DECLARE
   compatible BOOLEAN := FALSE;
BEGIN   
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
        pdb_descr_file => '/u01/app/oracle/oradata/pdb1.xml');
   if compatible then
      DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? YES');
   else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB1 compatible? NO');
   end if;
END;

PLUG IN PDB: NOCOPY METHOD
Connect to CDB2 with sys as sysdba
create pluggable database pdb_plug_nocopy using '/u01/app/oracle/oradata/pdb1.xml' 
NOCOPY 
TEMPFILE REUSE;
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_NOCOPY';
select open_mode from v$pdbs where name='PDB_PLUG_NOCOPY';
List the data files of the plugged PDB.
select name from v$datafile where con_id=3;
alter pluggable database pdb_plug_nocopy open;
exit

PLUG IN PDB: COPY METHOD
Create and define a destination for the new data files, plug the unplugged PDB into the CDB, and then copy the data files of the unplugged PDB.
mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_copy
sqlplus / as sysdba
Use the data files of the unplugged PDB to plug the PDB into the CDB and copy the data files to a new location.
create pluggable database pdb_plug_copy using '/u01/app/oracle/oradata/pdb2.xml' 
COPY 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_copy');
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_COPY';
select open_mode from v$pdbs where name='PDB_PLUG_COPY';
List the data files of the plugged PDB.
select name from v$datafile where con_id=4;
exit

PLUG IN PDB: AS CLONE MOVE METHOD
Create and define a destination for the new data files, use the data files of the unplugged PDB to plug the PDB into another CDB, and then move the data files to another location.
mkdir /u01/app/oracle/oradata/cdb2/pdb_plug_move
sqlplus / as sysdba
Plug the PDB into the CDB and move the data files to a new location.
create pluggable database pdb_plug_move using '/u01/app/oracle/oradata/pdb2.xml' 
MOVE 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');
An error message is returned because of the non-uniqueness of the GUID. This is a good example of using the AS CLONE clause. 
create pluggable database pdb_plug_move 
AS CLONE using '/u01/app/oracle/oradata/pdb2.xml' 
MOVE 
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb2','/u01/app/oracle/oradata/cdb2/pdb_plug_move');
Verify the status and open mode of the plugged PDB. Proceed to the next section, "Opening the Plugged PDB," to finalize the plugging operation.
select pdb_name, status from cdb_pdbs where pdb_name='PDB_PLUG_MOVE';
select open_mode from v$pdbs where name='PDB_PLUG_MOVE';
List the data files of the plugged PDB.
select name from v$datafile where con_id=5;