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;
No comments:
Post a Comment