Oracle 12c Multi-tenant common management commands

--Enter CDB
Sqlplus/nolog
Conn Sys/sys as dBA

--View Database basic information
Select name,cdb from V$database;

--View information about the container
Select Con_id,name from V$containers;

--View the path to the data file
Select Con_id,file_name from Cdb_data_files order by 1;

--Displays current connection container information
Show con_id Con_name User

--Show All PDB
Show PDBs

--start and stop root containers

--Start data
--cdb
Sqlplus/as SYSDBA
Startup

--pdb (All PDB)
Alter pluggable database all open;
--Start PDB1 Library
Alter pluggable database PDB1 open;

--Close the database
--cdb
Shutdown immediate

--pdb1
Alter pluggable database PDB1 close;
Alter pluggable database PDB1 close immediate;
--Close all PDB
Alter pluggable database all close immediate;

--Create public users (SYS and system are automatically created public users in pluggable)
Sqlplus/as SYSDBA
Create user c#dba identified by Alex;


--Create a common role
Sqlplus/as SYSDBA
Create role C # #dbaprivs Container=all;

Grant DBA to C # #dbaprivs Container=all;

--Public roles for public users
Grant C # #dbaprivs to C#dba Container=all;

--Switch container
Alter session set CONTAINER=EHRPDB;
--Switch back to the root container
Alter session set Container=cdb$root;


--2 Creating pluggable Databases
--2.1 Cloning seed Container
Create pluggable Database cpcdb
Admin user Cpcadm identified by Cpcadm
file_name_convert= ('/oracle/app/oracle/oradata/edw/pdbseed ', '/oradata/edw/cpcdb/datafile/');


--2.2 Creating a database directly
CREATE Pluggable DATABASE Hrdb
ADMIN USER HRDBA identified by HRDBA
STORAGE (MAXSIZE 100G max_shared_temp_size 2G)
DEFAULT tablespace hrdb datafile '/oradata/edw/hrdb/datafile/hrdb001.dbf ' SIZE 512M autoextend on next 8M maxsize 10G;

CREATE Pluggable DATABASE cpcdb
ADMIN USER CPCDBA identified by CPCDBA
STORAGE (MAXSIZE 100G max_shared_temp_size 2G)
DEFAULT tablespace tbscpc datafile '/oradata/edw/cpcdb/datafile/tbscpc001.dbf ' SIZE 512M autoextend on next 8M maxsize 10G ;

--2.3 Creating a data case


Create pluggable Database cpcdb
Admin user Cpcadm identified by Cpcadm
file_name_convert= ('/oracle/app/oracle/oradata/edw/pdbseed ', '/oradata/edw/cpcdb/datafile/');

Create pluggable Database ncdb
Admin user Ncadm identified by Ncadm
file_name_convert= ('/oradata/edw/pdbseed ', '/oradata/edw/ncdb/datafile/');


CREATE Pluggable DATABASE ncdb
ADMIN USER NCDBA identified by NCDBA
STORAGE (MAXSIZE 100G max_shared_temp_size 2G);

--access to NCDB's pluggable database
Sqlplus Sys/[email protected]:1521/ncdb as Sysdba

--Close the PDB database
Alter pluggable database NCDB close immediate;
Alter pluggable database Hrdb close immediate;
Alter pluggable database cpcdb close immediate;
Alter pluggable database edwpdb close immediate;
--Delete the PDB database
DROP pluggable DATABASE ncdb including datafiles;
DROP pluggable DATABASE hrdb including datafiles;
DROP pluggable DATABASE cpcdb including datafiles;
DROP pluggable DATABASE edwpdb including datafiles;

--Create a PDB case
--1, CPCDB
Create pluggable Database cpcdb
Admin user Cpcadm identified by Cpcadm
file_name_convert= ('/oradata/edw/pdbseed/', '/oradata/edw/cpcdb/');

--2, NCDB
Create pluggable Database ncdb
Admin user Ncadm identified by Ncadm
file_name_convert= ('/oradata/edw/pdbseed/', '/oradata/edw/ncdb/');


--3, Hrdb
Create pluggable Database Hrdb
Admin user Hradm identified by Hradm
file_name_convert= ('/oradata/edw/pdbseed/', '/oradata/edw/hrdb/');

Alter pluggable database all open;

--Login CPCDB
$sqlplus Sys/[email protected]:1531/cpcdb as Sysdba

$sqlplus Sys/[email protected]:1533/ncdb as SYSDBA--contains! Need to enter the password separately

Oracle 12c Multi-tenant common management commands