Ananias BarretoPlugging In an Unplugged PDB
How to Plug a PDB and Unplugged PDB in Multitenant Architecture. In this Tutorial we will do the procedures using the .pdb file methods.
Current Scenario
Currently in this example, the Database has a CDB$ROOT, where the PDB$SEED and ORADES_PDB01 belong to the CDB$ROOT. There is also an Application Container called APP_CONT_HMP01with an application container called HMP_PDB01.
Here's how the current scenario was created for this example: Access this link
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORADES_PDB01 READ WRITE NO
4 APP_CONT_HMP01 READ WRITE NO
5 HMP_PDB01 READ WRITE NOUnplugging in an PDB
Unplugging a PDB disassociated the PDB from an CDB can be used in the following ways:
When you want to move the PDB to a different CDB;
Do you want to archive the PDB for later use;
When you no longer want the PDB to be available.
When disconnecting from the PDB, you can create an XML file that will contain metadata about the PDB or you can choose the extension (.pdb) which is more complete where it will be compressed into a single file with metadata, data files and the wallet file.
Connect to the root of CDB$ROOT and the PDB (ORADES_PDB01), it must be closed before being disconnected the unplugged PDB is in mounted mode.
Recommendation before any procedure makes a Full Backup!
sqlplus / as sysdba
ALTER PLUGGABLE DATABASE ORADES_PDB01 CLOSE IMMEDIATE;
Session altered.Let's do the Unplugging PDB ORADES_PDB01 to archive and Plugging in another location.
It is necessary to create directories with enough space to properly archive.
ALTER PLUGGABLE DATABASE ORADES_PDB01 UNPLUG INTO '/u01/app/orades/my_directory/data/ORADES_PDB01.pdb';
Pluggable database altered.
We will use the DROP PLUGGABLE DATABASE clauses with INCLUDING DATAFILES to remove the data files from the disk.
DROP PLUGGABLE DATABASE ORADES_PDB01 INCLUDING DATAFILES;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 APP_CONT_HMP01 READ WRITE NO
5 HMP_PDB01 READ WRITE NO
The Pluggable ORADES_PDB01 ceased to exist but it was archived in (.pdb) format.
[orades@orades-19c data]$ pwd
/u01/app/orades/my_directory/data
[orades@orades-19c data]$ ls -la
drwxr-xr-x 2 orades oinstall 23 Sep 25 14:28 .
drwxr-xr-x 3 orades oinstall 18 Sep 25 14:25 ..
-rw-r--r-- 1 orades asmadmin 108540929 Sep 25 14:29 ORADES_PDB01.pdb
Plugging in an PDB
Let's understand some rules when connecting to a PDB and its users.
PDB users who use temporary tablespaces will use temporary tablespaces as the default for the target PDB;
Users who have common accounts that existed on the CDB but not on the destination CDB will no longer have common privileges, to avoid problems Close the PDB connect to root and create a common user account with the same name;
You can also use Data Pump to export / import the blocked user data to the new schema.
Pluggable database
Now let's take ORADES_PDB01 (orades_pdb_01.pdb) and plug it into CONTAINER APP_CONT_HMP01.
ALTER SESSION SET CONTAINER=app_cont_hmp01;
Session altered.
CREATE PLUGGABLE DATABASE ORADES_PDB01 USING '/u01/app/orades/my_directory/data/orades_pdb_01.pdb'
COPY
TEMPFILE REUSE;
Pluggable database created.
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORADES_PDB01 MOUNTED
4 APP_CONT_HMP01 READ WRITE NO
5 HMP_PDB01 READ WRITE NO
Let's open ORADES_PDB01
ALTER PLUGGABLE DATABASE ORADES_PDB01 OPEN;
Warning: PDB altered with errors.Don't worry this warning is normal as ORADES_PDB01 has not yet become the full application's PDB.
See the alert log line '$ORACLE_BASE/diag/rdbms/orades/orades/trace/alert_orades.log':
Violations: Type: 1, Count: 1
ORADES_PDB01(6):*******************************************************
ORADES_PDB01(6):WARNING:Pluggable Database ORADES_PDB01 with pdb id - 6 is
ORADES_PDB01(6): altered with errors or warnings. Please look into
ORADES_PDB01(6): PDB_PLUG_IN_VIOLATIONS view for more details.
ORADES_PDB01(6):*******************************************************The content of the PDB_PLUG_IN_VIOLATIONS view explains that the pdb_to_apppdb.sql script must be run for the PDB to be converted to a regular PDB.
SELECT cause, type, message, status, action FROM PDB_PLUG_IN_VIOLATIONS WHERE name='ORADES_PDB01';
Making an application PDB complete
Connect to the new PDB application 'ORADES_PDB01' and run the script $ORACLE_HOME/ rdbms/admin/pdb_to_apppdb.sql so that common application objects are accessible in the application's PDB.
The script automatically synchronizes the application's PDB with the application's root.
ALTER SESSION SET CONTAINER=ORADES_PDB01;
Session altered.
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORADES_PDB01 READ WRITE YES
Execute o script abaixo fazendo login no novo aplicativo PDB 'ORADES_PDB01'
@$ORACLE_HOME/rdbms/admin/pdb_to_apppdbAfter the script finishes, I need to close and reopen the PDB.
ALTER PLUGGABLE DATABASE ORADES_PDB01 CLOSE IMMEDIATE;
Pluggable database altered.
ALTER PLUGGABLE DATABASE ORADES_PDB01 OPEN;
Pluggable database altered.
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORADES_PDB01 READ WRITE NO
exit; Log out and access sqlplus again and see that there are no more restrictions on PDBs.
show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORADES_PDB01 READ WRITE NO
4 APP_CONT_HMP01 READ WRITE NO
5 HMP_PDB01 READ WRITE NO