Month: April 2023
Unplug and Plug of PDB in a Transparent Data Encryption (TDE) Environment
Within an Oracle environment utilizing Transparent Data Encryption (TDE), the process of transferring a pluggable database (PDB) may necessitate additional procedures. In this blog post, we will explore the potential steps involved when performing an Unplug and Plug operation for a pluggable database.
Our intention is to perform an unplugging operation on the pluggable database EBSPDB from the PROD environment, followed by plugging it into the TEST environment. It’s important to note that both containers have Transparent Data Encryption (TDE) configured.
Source Environment: PROD Database with TDE
SQL> show EBSPDBs
CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 EBSPDB$SEED READ ONLY NO 3 EBSPDB READ WRITE NO
Target Environment: TEST Database with TDE
SQL> show EBSPDBs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 EBSPDB$SEED READ ONLY NO
3 TEST2 READ WRITE NO
1. Export the encryption key on the source EBSPDB
We have to export the encryption key on the source container PROD while connecting to EBSPDB.
The option force is needed if AUTO LOGIN is used
SQL> alter session set container=EBSPDB;
SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "<passpharase>" TO '/u01/oracle/tde/export.p12' FORCE keystore IDENTIFIED BY <passpharase>;
keystore altered.
Verify that the key was exported successfully on the host directory.
ls -l /u01/oracle/tde/export.p12
-rw-r--r--. 1 oracle oinstall 3411 Feb 12 13:34 /u01/oracle/tde/export.p12
2. Unplugging EBSPDB from Source Database PROD
Once the key has been exported, we are now able to proceed with the unplugging of EBSPDB.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database EBSPDB close;
Pluggable database altered.
SQL> show EBSPDBs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 EBSPDB$SEED READ ONLY NO
3 EBSPDB MOUNTED
5 EBSPDBCLONE MOUNTED
SQL> ALTER PLUGGABLE DATABASE EBSPDB UNPLUG INTO '/u01/oracle/EBSPDB.xml';
Pluggable database altered.
3. Plugging the database into the Target TEST Environment
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE PLUGGABLE DATABASE EBSPDB USING '/u01/oracle/EBSPDB.xml';
Pluggable database created.
At this stage, it is expected to encounter errors when attempting to open EBSPDB in TEST. This is a normal occurrence since the encryption key needs to be imported.
SQL> alter pluggable database EBSPDB open;
Warning: EBSPDB altered with errors.
4. Import the key into EBSPDB on TEST
Next, we will proceed with importing the exported key into EBSPDB on the TEST environment.
If EBSPDB is not already open in read/write (R/W) mode, please ensure to open it in such mode before proceeding with the import.
SQL> alter pluggable database EBSPDB open;
Warning: EBSPDB altered with errors.
SQL> show EBSPDBs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 EBSPDB$SEED READ ONLY NO
3 TEST2 READ WRITE NO
4 EBSPDB READ WRITE YES
If the keystore on the pluggable database (PDB) is not already open, please ensure to open it.
SQL> alter session set container=EBSPDB;
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ********;
keystore altered.
Subsequently, proceed with importing the key
SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET ******** FROM '/u01/oracle/tde/export.p12' IDENTIFIED BY ******** WITH BACKUP;
keystore altered.
Now, Please restart the pluggable database EBSPDB on the TEST environment.
SQL> show con_name
CON_NAME
------------------------------
EBSPDB
SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
Now, when starting EBSPDB in open mode, there should be no further errors encountered.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database EBSPDB close;
Pluggable database altered.
SQL> alter pluggable database EBSPDB open;
Pluggable database altered.
SQL> alter pluggable database all save state;
Pluggable database altered.
Subsequently, we can proceed to verify on EBSPDB whether the encrypted objects are accessible.
[edsanimate_start entry_animation_type= “fadeIn” entry_delay= “0” entry_duration= “1” entry_timing= “linear” exit_animation_type= “” exit_delay= “” exit_duration= “” exit_timing= “” animation_repeat= “infinite” keep= “yes” animate_on= “load” scroll_offset= “” custom_css_class= “”]OUTPUT POST PROCESSOR (OPP) LOG CONTAINS ERROR “JAVA.LANG.OUTOFMEMORYERROR”[edsanimate_end]
Tablespace Level Fragmentation in Oracle Database
Introduction
Brief Explanation:
In Oracle, tablespace level fragmentation refers to space fragmentation within a tablespace, leading to inefficient storage utilization and potential performance issues. It occurs when free space within the tablespace is fragmented into smaller, non-contiguous extents.
Tablespace level fragmentation can occur due to various reasons, such as:
Data Insertions, Updates, and Deletions: Frequent data modifications can result in fragmented free space within the tablespace as objects grow and shrink.
Improper Storage Allocation: Inadequate or incorrect storage allocation settings can lead to fragmentation, especially if the initial extent sizes are too small or if automatic segment space management is not utilized.
Uneven Object Growth: When objects within the tablespace grow at different rates, it can lead to uneven distribution of free space and fragmentation.
Inefficient Reorganization or Maintenance: Lack of regular tablespace maintenance activities, such as the reorganization or shrinking of objects, can contribute to fragmentation over time.
The presence of tablespace level fragmentation can negatively impact database performance, leading to slower query execution, increased I/O operations, and inefficient use of storage resources.
Migrating tables and indexes to new tablespaces can indeed be an efficient and effective way to defragment and shrink a large tablespace in Oracle. This approach involves moving the objects from the fragmented tablespace to a new, properly-sized tablespace, thereby consolidating free space and optimizing storage utilization. Here’s an overview of the process:
Step 1. Log in with the dba account and create new tablespaces for the database user.
Sample SQL:
create tablespace XX_ERP2 datafile ‘/path/to/XX_ERP2.dbf’ size 256m autoextend on next 128m maxsize unlimited;
Step 2. Log in with the Database owner’s username/password
Step 3. Run the script below to generate a table migration script for migrating tables to a new tablespace
spool /home/oracle/movTbl.sql
select ‘alter table ‘ || SEGMENT_NAME || ‘ move tablespace XX_ERP2;’
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=’XX_ERP’ and segment_type=’TABLE’
order by FILE_NAME,segment_name;
spool off;
Step 4. Run the script below to generate an Index migration script for migrating the Index to a new tablespace
spool /home/oracle/movIndex.sql
SELECT ‘alter index ‘ || SEGMENT_NAME || ‘ rebuild tablespace XX_ERP2;’
FROM dba_Segments a, dba_data_files b
WHERE b.file_id = a.relative_fno
AND a.tablespace_name = ‘XX_ERP’
AND segment_type = ‘INDEX’
ORDER BY FILE_NAME, segment_name;
spool off;
Step 5. Run the script below to generate the LOB Segments script for migrating the LOB Segments to a new tablespace if available.
spool /home/oracle/movLobSeg.sql
select ‘ALTER TABLE ‘ || table_name || ‘ move lob(‘ || COLUMN_NAME || ‘) STORE AS (TABLESPACE XX_ERP2);’
from dba_tab_columns
where owner=’XX_ERP’ and data_type=’CLOB’;
spool off;
Step 6. Check if anything is missing in the Original Tablespace to be Migrated
set lines 300
col owner format A26
col segment_name format A26
col segment_type format A26
col tablespace_name format A26
col relative_fno format 99999
col file_name format A50
SELECT owner, segment_name, segment_type,a.tablespace_name, a.relative_fno, b.file_name
FROM dba_Segments a,
dba_data_files b
WHERE b.file_id=a.relative_fno
and a.tablespace_name=’XX_ERP’
order by FILE_NAME,segment_name;
Step 7. Do not forget to change the default tablespace of the user to the Newly Created One
ALTER USER default tablespace XX_ERP2;
Step 8. Change the Old Tablespace Offline once all the Objects are transferred to the newly created Tablespace with the Scripts Generated.
alter tablespace XX_ERP offline;