29. Multi Tenant Database Practical

Multi Tenant Database

1. Open a terminal of a server and set environment of the Container database name like [ SUPERCDB ] and Startup . so CDB is started .

2. Check what are the Pluggable databases are there , the command [ show pdbs ]

3. The pdb$seed is the template of the Pluggable database. and 1 PDB is there.

Commands and views

Show pdbs

Here PDB$SEED IS A TEMPLATE AND PDB is child database of superdb parent database . PDB is mounted so we need to start that

To start the PDB ,-> ALTER PLUGGABLE DATABASE PDB OPEN;

If there is multiple PDBS and want to start all then , -> ALTER PLUGGABLE DATABASE ALL OPEN;

Q1 . Want to know what is the Container ddatabase name , => show con_name

select name,open_mode,cdb from v$database;

select con_id,name,open_mode from v$containers;

Login or enter into a pdb, ALTER SESSION SET CONTAINER = PDB;

now we want to access the HR user so , alter user hr identified by oracle account unlock;

Q2. Now if we want to login back to the container database:

ALTER SESSION SET CONTAINER = CDB$ROOT;

Q3. THE DATAFILES OF CDB AND PDB

See The pluggable database PDB , we create at first by dbca PDBSEED is the template of the pluggable database

Q4. Want create a new PDB

Command > create puggable database market admin user pdbadmin identified by oracle file_name_convert =('/u01/app/oracle/oradata/SUPERDB/pdbseed/','/u01/app/oracle/oradata/SUPERDB/market/') ;

This ss because the system needs to use a template to make the datafiles for the new pluggable database, and pdbseed is that template which is used to create the new pluggable database. See the new pdb is created.

Check >>>

To close a PDB > alter pluggable database pdb close ;

To Start All pluggable Databases Up and Running at once > alter pluggable database all open;

To Close All pluggable Database at once > alter pluggable database all close;

Q5. Want to create a CLONE of a pluggable database [ market clone pdb is sale ] (clone means the users, objects and data inside will be copied. )

create pluggable database sale from market file_name_convert = ('/u01/app/oracle/oradata/SUPERDB/market/',''/u01/app/oracle/oradata/SUPERDB/sale/');

Here we can see market pdb is not open,so we cannot make a clone of this database ,we have to open market pdb.

See this time instead of using pdbseed inside the file name convert (‘/u01/app/oracle/oradata/SUPER/pdbseed/’) we used pluggable database marketing directly (‘/u01/app/oracle/oradata/SUPER/marketing/’) datafiles as the template to create the clone.

Q6. Want to rename of a pdb [ suppose i rename market ppdb ]

alter pluggable database market rename global_name to mrkt;

Here we can see to rename a pdb operation not allowed from outside a pluggable database , so login to market pdb by [ ALTER SESSION SET CONTAINER = MARKET ; ]

This time we see pdb is not in retricted mode. Restricted mode means only dba can login, no other user can login. To change the name of a pluggable database. So we need to close the pdb and login with cdb ,who can change the restricted mode state.

alter pluggable database market close; alter session set container = cdb$root; alter pluggable database market open restricted;

Now we can login into market pdb and rename the pdb

Q7. Want to unplug a pdb (mrkt)

Alter pluggable database sales unplug into ‘/u01/sales.xml’;

We can’t unplug a pluggable database if its open.

So i have to close sale pdb and then altered and check the xml file.

There is a view to see the status of pdbs desc dba_pdbs

Q8. What are the objects and views

desc cdb_objects

select CON_ID ,OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,STATUS from cdb_objects;

Q9.Want to drop a pluggable database:

TO drop a pdb , we have to be loged in cdb$root and close all pdbs we cannot drop a pluggable database which is already plugged. We need to unplug it first. then drop.

Q10. create Common USER

An user Who will exist in CDB$ROOT and all PDBS and will be able to login to all the pdbs CREATE USER C##RAM IDENTIFIED BY ORACLE CONTAINER =ALL;

SEE THE USER C##LOGAN IS PRESENT IN THE CDB$ROOT AND BOTH PLUGGABLE DATABASES PDB AND MRKT. THIS USER IS COMMON TO ALL CDB AND PDB

SEE WE CANNOT OPEN OR CLOSE A PLUGGABLE DATABASE WHEN WE ALREADY LOGGED IN ANOTHER PLUGGABLE DATABASE. ONLY FROM CDB$ROOT THIS WILL DONE

Q11. Want To create an user specific to a pdb;

I CREATE AN USER THOR IN THE PDB PLUGGABLE DATABASE , BUT WHEN I LOGGED IN TO THE CDB$ROOT HERE NO THOR USER IS PRESENT as well as in MRKT pdb also. That user is only present specifically where it was created.