12c Multitenant Activities

12c Multitenant Activities

1.Backup and Recovery of CDB & PDB databases in 12c.


Backup:

How to take backup of container and pluggable database in command?
rman target /
backup database plus archivelog;
first it will root container, pluggable database and then seed database.

How to take the backup of root container database?
rman>backup database root;

How to take the backup of a individual pluggable database?
rman>backup pluggable database pdb1;
or
rman target=sys@pdb1
rman>backup database;

Recovery:

To do a full recovery of PDB database ?
rman target=/
run{
alter pluggable database pdb1 close;
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}

PITR in pluggable database ?

rman target=/
run{
alter pluggable database pdb1 close;
set until time "SYSDATA - 10/24/2019"; (we can give either TIME, SCN or SEQUENCE)
restore pluggable database pdb1;
recover pluggable databaes pdb1;
alter pluggable database pdb1 open;
}


2.How to clone a local pluggable database (PDB) ?


-connect to root container database
conn / as sysdba

-check the pdb's
select name, open_mode from v$pdbs order by name;

-switch the source PDB to read-only mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open read only;

-clone the PDB
-if you are not using OMF, convert the paths
sql>create pluggable database pdb2 from pdb1
file_name_convert=('/u01/app/oradata/cdb1/pdb1/','/u01/app/oradata/cdb1/pdb2');
alter pluggable database pdb2 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name; (here only pdb2 will be read write mode)

-switch the source PDB to read-write mode.
alter pluggable database pdb1 close;
alter pluggable database pdb1 open;

-check the pdbs status
select name, open_mode from v$pdbs order by name;

How to remove the clone PDB's ?

alter pluggable database pdb2 close;
drop pluggable database pdb2 including datafiles;



How to clone a remote pluggable database (PDB) ?


-connect to remote PDB
conn sys/password@pdb5 as sysdba

-check database name
select name from v$database;

-create & grant a user we can point a database link to
create user remote_clone_user identified by remote_clone_user;
grant create session, create pluggable database to remote_clone_user;

-switch the remote PDB to read-only mode
alter pluggable database pdb5 close;
alter pluggable database pdb5 open read only;

-connect to root container in the local CDB
conn sys/password@CDB1 as sysdba

-check the database name
select name from v$database;

-create a database link to the remote PDB
create database link clone_link connect to remote_clone_user identified by abc123 using 'pdb5';

-clone the remote database over the database link
create pluggable database pdb5new from pdb5@clone_link file_name_convert=('/u01/aap/oradata/cdb3/pdb5/','/u01/app/oradata/cdb1/pdb5new/');

alter pluggable database pdb5new open;

-check the pdb's
select name, open_mode from v$pdbs order by name;

-open the remote PDB in read-write mode again
alter pluggable database pdb5 close;
alter pluggable database pdb5 open;


How to connect to PDB & CDB database's ?


-connect to CDB using OS authentication
conn / as sysdba

-connect to CDB using a local connection
conn system/manger

-check available services
select name, pdb from v$services order by name;
all services are also registered with listener

-connect to CDB using EZCONNECT & tnsnames.ora
conn system/manager@//localhost:1521/cdb1
conn system/manager@cdb1

-display the container name and ID usign sqlplus
show con_name
show con_id

-switch to a user created pluggable database
alter session set container=pdb1;
show con_name

-switch back to root container
alter session set container=cdb$root;

-connect to pdb using tnsnames.ora
conn system/password@pdb1


How to create a empty pluggable database ?


-connect to root container
conn / as sysdba

-create a pdb based on seed
create pluggable database pdb2 admin user pdb_adm identifed by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed)',/u01/pp/oradata/cdb2/pdb3/';
(or)
-set file_name_convert at session level
alter session set pdb_file_name_convert='/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb4/';
create pluggable database pdb4 admin user pdb_adm identified by password1;

Alertlog show below message:

create pluggable database pdb4 admin user pdb_adm identified by *
Tue Dec 31 02:02:26 2019
Opatch XML is skipped for PDB PDB$SEED (conid=2)
 APEX_040200.APEX$_WS_NOTES (CONTENT) - CLOB populated
Tue Dec 31 02:03:17 2019
****************************************************************
Pluggable Database PDB4 with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB4 is WE8MSWIN1252
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#13 to file$(old file#5)
Adding new file#14 to file$(old file#7)
Successfully created internal service pdb4 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=5 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB4 with pdb id - 5 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb4 admin user pdb_adm identified by *


-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NEW
select name, open_mode from v$pdbs;

-finish pdb creation by opening the PDB
alter pluggable database pdb2 open read write;

-check pdb views
select pdb_name, status from dba_pdbs;
Newly created database status show as NORMAL

If you want to drop the newly created PDB's
alter pluggable database pdb3 close;
drop pluggable database pdb3 including datafiles;


How to manage tablespaces in a CDB & PDB ?


connect to root container
conn / as sysdba

show con_name
CDB$ROOT

create a tablespace for the CDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

create a tablespace for PDB:
alter session set container=pdb1;
(or)
conn sys/password@pdb1 as sysdba

show con_name
PDB1

create a tablespace for the PDB:
create tablespace dummy datafile '/u01/app/oradata/dummy01.dbf' size 10m autoextend on next 10m;

adding a datafile to it:
alter tablespace dummy add datafile '/u01/app/oradata/dummy02.dbf' size 10m autoextend on next 10m;

remove the tablespaces:
drop tablespace dummy including contents and datafiles;

Note:

A PDB doesn't have an undo tablespace, and we can't see it
We can see the undo datafile for the CDB from v$datafile

create temporary tablespace in the PDB:
create temporary tablespace temp2 tempfile '/u01/app/oradata/temp02.dbf' size 10m autoextend on next 10m;

remove a temporary tablespace:
drop tablespace temp2 including contents and datafiles;

There are 2 options for setting default tablespaces in PDB:

alter database default tablespace users;
alter database default temporary tablespace temp;
(or)
use PDB syntax for clarity
alter pluggable database default tablespace user;
alter pluggable database default temporary tablespace temp;


How to managing users and roles for CDB's and PDB's ?


connect to root container database

creating a common user:
create user c##test_user1 identified by password1 container=all;
grant create session to c##test_user1 container=all;

container=all default when issued from root container

create user c##test_user2 identified by password1;
grant create session to c##test_user2;

Switch container while connected to a common user or switch to PDB, alter session set container=PDB1;
conn sys/password1@pdb1 as sysdba

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;

container=current default when issued from non-root container.
create user test_user4 identified by password1;
grant create session to test_user4;

Make a local user a DBA role:
grant PDB_DBA to test_user4;

create a common role
conn / as sysdba
create role c##test_role1;
grant it to a common user
grant c##test_role1to c##test_user1 container=all;

grant it to a lcoal user
conn sys/password1@pdb1
grant c##test_role1 to test_user3;

create local role:
create role test_role1;
grant create session to test_role1;


How to pluggable database PDB's archive files ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display the current PDB's:

show pdbs

pdb1 read write

unplug:

alter pluggable database <pdb1> close immediate;
alter pluggable database <pdb1> unplug into '/u01/mqm/pdb1.pdb';

we can safely drop the PDB after archiving
drop pluggable database pdb1 including datafiles;

We can check the content of the pdb archive files using unzip -l command
It will show the .dbf files plus .xml file


plug-in:

check its safe to plugin.

set serveroutput on
declare
1_result BOOLEAN;
BEGIN
1_result := DBMS_PDB.check_plug_compatibility(pdb_descr_file => '/tmp/pdb1.pdb',
      pdb_name =>'pdb1');
IF 1_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/

plugin from an archive file.
create pluggable database pdb1 using '/u01/mqm/pdb1.pdb';
alter pluggable database pdb1 open read write;

show pdbs


How to refresh pluggable database PDB's ?


connect to CDB1 instance
conn sys/password1@cdb1 as sysdba

display current pdb's

show pdbs

pdb5 read write

create link user for DBlink
create user c##remote_clone_user identified by remote_clone_user container=all;
grant create session, create pluggable database to c##remote_clone_user container=all;

connect to CDB1 and create database link.
conn sys/password1@cdb1 as sysdba
create database link clone_link connect to c##remote_clone_user identified by remote_clone_user using 'cdb3';

create refreshable read-only PDB
create pluggable database pdb5_ro from pdb5@clone_link refresh mode manual;
alter pluggable database pdb5_ro open read only;

Make changes to the source PDB.
conn sys/password1@cdb3 as sysdba
alter session set container=pdb5;
create tablespace test_tbs datafile size 10m autoextend on next 10m;

check for the TEST_TBS tablespace in the refreshable PDB.
conn sys/password1@cdb1 as sysdba
alter session set container=pdb5_ro;
select tablespace_name from dba_tablespaces;
It will not show the test_tbs tablespace.

Refresh the read-only PDB.
alter session set container=pdb5_ro;
alter pluggable database close immediate;
alter pluggable database refresh;
alter pluggable database open read only;
select tablespace_name from dba_tablespaces;
Now it will show the test_tbs tablespace.

alter the refreshable PDB refresh mode.
alter pluggable database pdb5_ro refresh mode every 120 minutes;
select pdb_id, pdb_name, refresh_mode, refresh_interval from dba_pdbs order by 1;


How to startup & shutdown of PDB's & CDB's database's ?


CDB shutdown:
conn  / as sysdba
shutdown immediate;

CDB startup:
conn / as sysdba
startup

using alter command.
alter pluggable datababase <pdb1_name,pdb2_name,or all> open or close;

How to unplug and plugin pluggable database (PDB's) ?

check the availabe pdb's by connecting root container database.
conn / as sysdba
select name, open_mode from v$pdbs order by name;

create a new PDB to test the unplug/plugin.
create pluggable database pdb2 admin user pdb_adm identified by password1 file_name_convert=('/u01/app/oradata/cdb1/pdbseed/','/u01/app/oradata/cdb1/pdb2/');
alter pluggable database pdb2 open read write;

unplug the PDB:
alter pluggable database pdb2 close;
alter pluggable database pdb2 uplug to '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml';

check the pdbs are present.
select name, open_mode from v$pdbs order by name;
Here pdb2 should be mount stage.

Drop the PDB, but keep the files.
drop pluggable database pdb2 keep datafiles;

check again the pdb status.
select name, open_mode from v$pdbs order by name;
It will drop the PDB2 database.

check the compatibility.

plugin and rename the PDB:
create pluggable database pdb5 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml' file_name_convert=('/u01/app/oradata/cdb1/pdb2/','/u01/app/oradata/cdb1/pdb5/');
alter pluggable database pdb5 open;

check the status
select name, open_mode from v$pdbs order by name;

drop the new PBD
alter pluggable database pdb5 close;
alter pluggable database pdb5 including datafiles;

plugin the database using the same name and file position.
create pluggable database pdb2 using '/u01/app/mqm/oradata/cdb1/pdb2/pdb2.xml'
nocopy
tempfile reuse;
alter pluggable database pdb2 open read write;

check the status
select name, open_mode from v$pdbs order by name;