Optimizing Storage and Performance with Oracle Database Advanced Compression

 Optimizing Storage and Performance with Oracle Database Advanced Compression

In the world of database management, the challenge of balancing performance with storage efficiency is ever-present. Oracle's Advanced Compression feature offers a robust solution to this by reducing storage footprint, optimizing performance, and saving costs — all without compromising data integrity. In this post, we’ll dive into how you can leverage advanced compression in Oracle, with practical examples and straightforward code snippets.

  Why Use Advanced Compression in Oracle?

Advanced Compression isn't just about saving storage; it enhances data retrieval speed by minimizing the amount of data that needs to be read from disk. This is especially beneficial for larger tables or indices that experience heavy I/O operations. Advanced Compression works across various Oracle database objects and data types, from tables and indexes to LOBs and backups.

  Types of Compression in Oracle Database

Oracle provides several types of compression suited to various workloads:

1. Basic Table Compression: Suitable for read-only or static tables, it compresses data as it's loaded.

2. OLTP Table Compression: Designed for transactional environments, it works dynamically on data inserts and updates.

3. Hybrid Columnar Compression (HCC): Available only on Exadata and a few other Oracle storage solutions, it’s ideal for data warehouses and archival tables.

4. Index Compression: Reduces the storage for indexes by compressing repeated values.

Let’s walk through these options with examples using a hypothetical table and index.

  1. Basic Table Compression

To start, let’s look at Basic Table Compression, commonly used for data warehousing or infrequently updated tables.

Suppose we have a table `Employee_Records` that stores historical data and is rarely modified:

 SQL :

CREATE TABLE Employee_Records (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    department_id NUMBER,

    hire_date DATE

) COMPRESS BASIC;

 

Here, we specify `COMPRESS BASIC`, which instructs Oracle to compress the data during bulk insert operations. Keep in mind that this is ideal for tables where data modifications are infrequent, as Basic Compression doesn’t automatically recompress on updates.

  2. OLTP Table Compression

For transactional tables with frequent updates, OLTP Table Compression is more appropriate. Oracle's advanced algorithm ensures that as rows are inserted or updated, the data remains compressed without additional manual intervention.

Let’s use `Order_Details`, a table holding real-time sales data:

 SQL :

CREATE TABLE Order_Details (

    order_id NUMBER,

    product_id NUMBER,

    quantity NUMBER,

    order_date DATE,

    customer_id NUMBER

) COMPRESS FOR OLTP;

 

With `COMPRESS FOR OLTP`, this table will dynamically compress data during both inserts and updates, keeping storage usage low while maintaining fast performance.

Tip: OLTP compression is a powerful way to balance performance and space in high-transaction environments. However, it requires some CPU overhead, so monitor performance in CPU-bound systems.

  3. Hybrid Columnar Compression (HCC)

HCC is an exclusive feature for Exadata, ZFS, and Oracle Cloud customers. It combines row and column storage, making it highly efficient for queries that scan large datasets. HCC can offer multiple levels, like `COMPRESS FOR QUERY` and `COMPRESS FOR ARCHIVE`, allowing you to optimize for retrieval speed or maximum storage savings.

Using `Sales_Archive` as an example table:

 SQL :

CREATE TABLE Sales_Archive (

    sale_id NUMBER,

    region VARCHAR2(30),

    sale_amount NUMBER,

    sale_date DATE

) COMPRESS FOR QUERY HIGH;

 

In this case, `COMPRESS FOR QUERY HIGH` balances both storage efficiency and query performance, ideal for analytic queries on historical data. For long-term storage where frequent access isn’t necessary, consider `COMPRESS FOR ARCHIVE HIGH`.

  4. Index Compression

Finally, Index Compression can be particularly beneficial for large indexes with repeating values. By compressing the key prefix, Oracle significantly reduces index size, speeding up queries.

Imagine we have a commonly queried index on the `Employee_Records` table:

 SQL :

CREATE INDEX idx_employee_department 

ON Employee_Records (department_id, last_name, first_name) COMPRESS 1;

 

In this example, `COMPRESS 1` compresses the leading column (`department_id`) in the index. If more columns contain duplicate values, you can increase the compression level. Index compression works well in cases where there’s repetition, such as departmental groupings, making it a great fit for HR or CRM databases.

  Monitoring and Verifying Compression Benefits

To see the benefits of compression, you can query Oracle's data dictionary to monitor the space savings:

 SQL :

SELECT segment_name, 

       segment_type, 

       bytes / 1024 / 1024 AS size_MB 

FROM   dba_segments 

WHERE  segment_name = 'EMPLOYEE_RECORDS';

 

By running this query before and after enabling compression, you can quantify the storage savings directly.

  Considerations and Best Practices

1. Testing: Test compression in a non-production environment to assess CPU overhead and performance impact.

2. Monitoring: Regularly monitor space and CPU metrics to ensure that compression is meeting your goals without overtaxing system resources.

3. Reorganizing Tables: Consider periodically reorganizing heavily updated tables with OLTP compression to maintain optimal storage savings.

  Conclusion

Oracle's Advanced Compression provides a flexible toolkit for database administrators looking to optimize both storage and performance. By selecting the right type of compression for each workload, you can achieve substantial space savings while maintaining fast access times. Try implementing compression gradually, starting with non-critical tables, and measure the impact to find the best fit for your database environment.

import a single table from a full export backup in oracle

import a single table from a full export backup and remap it


impdp USERNAME/PASSWORD tables=SCHEMA.TABLE_NAME directory=DPUMP dumpfile=DUMPFILE_%U.dmp
remap_schema=SOURCE:TARGET 
REMAP_TABLE=TABLE_NAME:TABLE_NAME_NEW


Optional things above :

1. Remove remap if you don't want.
2. Add ENCRYPTION_PASSWORD=IF_ANY

Restoring archive logs from an RMAN backup

Restoring archive logs from an RMAN backup is a common task when you need to recover database transactions to a specific point in time. The commands you've provided are examples of how to restore archive logs using RMAN (Recovery Manager). Below are explanations of each command:

1. Using RESTORE ARCHIVELOG FROM LOGSEQ ... UNTIL LOGSEQ ... THREAD

sql

RMAN> RESTORE ARCHIVELOG FROM LOGSEQ=37501 UNTIL LOGSEQ=37798 THREAD=1;

• Purpose: This command restores archive logs from the RMAN backup, specifically those logs that fall between the log sequence numbers 37501 and 37798 for the specified thread (in this case, thread 1).

• Parameters:

  • FROM LOGSEQ=37501: Specifies the starting sequence number of the archive logs you want to restore.
  • UNTIL LOGSEQ=37798: Specifies the ending sequence number of the archive logs to restore.
  • THREAD=1: Indicates the thread number (useful in RAC environments where there are multiple redo threads).

• Use Case: This approach is ideal when you know the exact range of log sequences you need to restore and want to limit the restoration to a specific thread.

2. Using RESTORE ARCHIVELOG BETWEEN SEQUENCE ... AND ...

sql

RMAN> RESTORE ARCHIVELOG BETWEEN SEQUENCE 37501 AND 37798;

• Purpose: This command restores all archive logs between the specified sequence numbers (37501 to 37798) from all threads unless a specific thread is specified elsewhere.

• Parameters:

  • BETWEEN SEQUENCE 37501 AND 37798: Specifies the range of archive log sequences you want to restore.

• Use Case: This command is useful when you want to restore a continuous range of archive logs across all threads without specifying individual thread numbers.

Key Points:

• Ensure that the archive logs you are restoring are available in your RMAN backup.
• These commands do not apply the restored archive logs; they only restore them to the specified location (usually the archive log destination).
• Check the current location and status of the logs using RMAN commands such as LIST BACKUP OF ARCHIVELOG before performing the restore operation.
• It's critical to be cautious with thread specification, especially in RAC environments, to avoid restoring unnecessary logs or missing required logs.

These commands are powerful tools for managing archive log restoration and can help ensure that you have the necessary logs for database recovery or point-in-time recovery operations.

set up the OPatch environment variable for Oracle Patching

To set up the OPatch environment variable, you need to add the OPatch directory (located within your Oracle Home) to your system’s PATH variable. This allows you to run OPatch commands directly from any shell prompt without specifying the full path. Below are the steps for setting up the PATH environment variable for both Korn/Bourne shells (like sh, bash, ksh) and C Shell (csh, tcsh).

Setting Up OPatch Environment Variable

1. For Korn Shell (ksh), Bourne Shell (sh), and Bash Shell:

   Use the export command to modify the PATH variable.

   sh:
   
   # Add OPatch to the PATH
   export PATH=$PATH:$ORACLE_HOME/OPatch
   

   • Explanation:
     • export PATH=$PATH:$ORACLE_HOME/OPatch: This command appends the OPatch directory to the current PATH variable, making the OPatch utility accessible from the command line.
     • $ORACLE_HOME/OPatch: Refers to the OPatch directory within the Oracle Home path.

2. For C Shell (csh, tcsh):

   Use the setenv command to update the PATH variable.

   csh:
   
   # Add OPatch to the PATH
   setenv PATH $PATH:$ORACLE_HOME/OPatch
   

   • Explanation:
     • setenv PATH $PATH:$ORACLE_HOME/OPatch: This command updates the PATH variable by appending the OPatch directory, allowing OPatch commands to be run without specifying the full path.

Important Notes:

• Ensure Oracle Home is Set: Before running these commands, make sure the ORACLE_HOME environment variable is correctly set to your Oracle installation path. You can verify it using echo $ORACLE_HOME.
• Persistent Changes: To make these changes persistent across sessions, add the corresponding command to your shell’s startup file (~/.bash_profile, ~/.profile for Bash/Korn shell or ~/.cshrc for C shell).
• Verify OPatch: After setting the variable, verify the setup by running opatch version to ensure that the command is accessible and the correct OPatch version is in use.

This setup is crucial for applying patches and managing updates in your Oracle environment effectively using OPatch

Simple Oracle Plsql Package for password encryption and decryption

Introduction:

The script provided demonstrates how to create a simple password encryption and decryption package in Oracle using the DBMS_OBFUSCATION_TOOLKIT. Below is a step-by-step breakdown of each part of the code:

1. Setting Up the Environment:

Before running the package, it is recommended to connect to the database as SYSDBA and run the script ?/rdbms/admin/catobtk.sql. This script installs the DBMS_OBFUSCATION_TOOLKIT package necessary for encryption and decryption functionalities.

2. Creating the Table USERS_INFO:

The table USERS_INFO is created to store usernames and encrypted passwords.

CREATE TABLE USERS_INFO

(

  USERNAME VARCHAR2(20 BYTE),

  PASS VARCHAR2(20 BYTE)

);

3. Creating the Package Specification PASSWORD:

The package specification defines two functions: encrypt and decrypt. These functions will handle the encryption and decryption of passwords.

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors

4. Creating the Package Body PASSWORD:

The package body implements the actual logic for the encrypt and decrypt functions using Oracle's DBMS_OBFUSCATION_TOOLKIT.

Key Points:

• The encryption key (c_encrypt_key) must be exactly 8 bytes long.
• Input data for encryption must have a length divisible by eight, achieved using RPAD to pad the input string.

CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- Key for encryption and decryption

  c_encrypt_key VARCHAR2(8) := 'key45678';

  -- Encrypt function

  FUNCTION encrypt (i_password VARCHAR2) RETURN VARCHAR2 IS

    v_encrypted_val VARCHAR2(38);

    v_data          VARCHAR2(38);

  BEGIN

     -- Pad the input data to a length divisible by eight

     v_data := RPAD(i_password, (TRUNC(LENGTH(i_password)/8) + 1) * 8, CHR(0));

     -- Encrypt the data

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(

        input_string     => v_data,

        key_string       => c_encrypt_key,

        encrypted_string => v_encrypted_val);

     RETURN v_encrypted_val;

  END encrypt;

  -- Decrypt function

  FUNCTION decrypt (i_password VARCHAR2) RETURN VARCHAR2 IS

    v_decrypted_val VARCHAR2(38);

  BEGIN

     -- Decrypt the data

     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(

        input_string     => i_password,

        key_string       => c_encrypt_key,

        decrypted_string => v_decrypted_val);

     RETURN v_decrypted_val;

  END decrypt;

END PASSWORD;

/

SHOW ERRORS;

5. Testing the Encryption and Decryption:

The following SQL statements test the encryption and decryption functions:

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;

6. Inserting and Retrieving Encrypted Data:

The script demonstrates inserting an encrypted password into the USERS_INFO table and then retrieving and decrypting it.

--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

Important Notes:

• Security Considerations: The DBMS_OBFUSCATION_TOOLKIT uses DES encryption, which is considered outdated and insecure by modern standards. It’s recommended to use DBMS_CRYPTO for more robust encryption methods, such as AES.
• Error Handling: The script should include error handling to manage issues that may arise during encryption or decryption processes.
• Testing and Validation: Always test encryption and decryption thoroughly, especially when dealing with sensitive data like passwords, to ensure the correct implementation.

This setup demonstrates basic encryption and decryption using Oracle's built-in toolkit, providing a foundation for enhancing database security.

update rows from multiple tables (correlated update)

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

lets take two tables TABA & TABB:

Records in TABA:

Records in TABB:

1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.

The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.

 update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);

2. Update data in two columns in table A based on a common column in table B.

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);

How Secure Can We Make Our Oracle Databases?

How Secure Can We Make Our Oracle Databases?

As a database administrator, one of the most critical questions that constantly arises is: How secure are our databases, and can we make them any more secure? Ensuring the security of your Oracle databases is crucial, as they are often the backbone of an organization’s data infrastructure. Over the years, I’ve adopted several best practices to enhance database security. Below, I share some key tips and strategies that I follow to secure my databases:

1. Grant Access on a Need-to-Know Basis

Always follow the principle of least privilege by granting access only to users who genuinely need it. This minimizes exposure and reduces the risk of unauthorized actions within the database.

    2. Remove Unnecessary Grants and Privileges

Regularly review and clean up unnecessary permissions from users and roles. Privileges that are no longer needed should be revoked to prevent misuse or accidental data exposure.

    3. Audit Failed Logins Regularly

Implement frequent audits of Failed Logins attempts to detect unauthorized access attempts. Monitoring failed logins helps identify suspicious activities and potential security threats. 

    4. Evaluate Requests for Elevated Privileges

When a user requests elevated privileges, always engage in a conversation to understand their requirements. Assess if the elevated access is genuinely necessary and grant it only if justified.

    5. Grant the Minimum Required Access

Ensure that users are granted no more access than what is absolutely necessary for their tasks. Avoid giving broad permissions, as this increases the risk of accidental or malicious actions.

    6. Manage Temporary Access Carefully

Sometimes, users need access temporarily for specific tasks. Make sure to track and revoke these temporary permissions immediately after the task is completed to maintain security.

    7. Define Clear Boundaries for Data Access

Clearly define which users can access specific data. Implement fine-grained access control to restrict access to sensitive data based on roles and responsibilities.

    8. Use User Profiles and Audit Features

Leverage Oracle’s User profiles & Audit capabilities to monitor and enforce security policies. Track all user activities to ensure accountability and detect any unauthorized actions. 

    9. Enforce Complex Password Policies

Implement strong password policies that require complexity and regular changes. Complex passwords significantly enhance security by making unauthorized access more difficult. Here is the Link on how to do it 

    10. Use Triggers to Track User Activity

Utilize database triggers to monitor user actions such as logins, DML operations, and changes to critical tables. This helps in tracking user behavior and detecting anomalies. Use Triggers to track user activity.

    11. Encrypt Passwords in Applications

Ensure that passwords used in applications are encrypted. Storing plain-text passwords poses a significant security threat, especially if application code is compromised.

    12. Secure the Oracle Listener with a Password

Protect your Oracle Listener with a password to prevent unauthorized access and control over database connections. This adds an additional layer of security to your database environment.

    13.  Restrict Access to Known Servers and Clients

Use Oracle’s VALID_NODE_CHECKING feature to restrict database access to specific, known servers or clients. This helps protect your database from unauthorized network access. Use this Valid_node_checking Link on how to restrict access to servers/clients.

Conclusion

Securing your Oracle databases is an ongoing process that requires continuous vigilance, regular audits, and updates to security protocols. By implementing these best practices, you can significantly enhance the security of your Oracle environments and protect your data from unauthorized access and potential threats. Remember, security is not a one-time setup but a continuous practice that evolves with emerging threats and changes in your database infrastructure.

java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted signer

I was trying to Install OID (Oracle Identity Manager) and I got this error :

Problem:




        at oracle.as.install.engine.modules.configuration.standard.StandardConfigActionManager.start(StandardConfigActionManager.java:186)
        at oracle.as.install.engine.modules.configuration.boot.ConfigurationExtension.kickstart(ConfigurationExtension.java:81)
        at oracle.as.install.engine.modules.configuration.ConfigurationModule.run(ConfigurationModule.java:86)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.SecurityException: Can not initialize cryptographic mechanism
        at javax.crypto.JceSecurity.<clinit>(JceSecurity.java:88)
        ... 31 more
Caused by: java.lang.SecurityException: The jurisdiction policy files are not signed by a trusted signer!
        at javax.crypto.JarVerifier.verifyPolicySigned(JarVerifier.java:328)
        at javax.crypto.JceSecurity.loadPolicies(JceSecurity.java:317)
        at javax.crypto.JceSecurity.setupJurisdictionPolicies(JceSecurity.java:262)
        at javax.crypto.JceSecurity.access$000(JceSecurity.java:48)
        at javax.crypto.JceSecurity$1.run(JceSecurity.java:80)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.crypto.JceSecurity.<clinit>(JceSecurity.java:77)



Cause:

My current version of java was 1.8.* which is not fully supported.
In this case JDK 1.8.0.1 is installed on all nodes in the cluster and JCE local policy version 6 was used for AES 256 kerberos encryption. JCE must be in sync with the JDK version.
      
[oracle@linux06 jdk1.8.0_111]$ cd ..
[oracle@linux06 java]$ ls
default  jdk1.8.0_111  latest
[oracle@linux06 java]$ cd default/
[oracle@linux06 default]$ ls
bin        javafx-src.zip  man          THIRDPARTYLICENSEREADME-JAVAFX.txt
COPYRIGHT  jre             README.html  THIRDPARTYLICENSEREADME.txt
db         lib             release
include    LICENSE         src.zip


Solution:

Download :

For Java 6 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce-6-download-429243.html

For Java 7 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce-7-download-432124.html

For java 8 use :
http://www.oracle.com/technetwork/java/javase/downloads/jce8-download-2133166.html        

********************    ********************    ********************
Update java with with new java unlimted jusrisdiction :
********************    ********************    ********************

After download and unzip :

[oracle@linux06 JCE]$ unzip jce_policy-8.zip
Archive:  jce_policy-8.zip
   creating: UnlimitedJCEPolicyJDK8/
  inflating: UnlimitedJCEPolicyJDK8/local_policy.jar
  inflating: UnlimitedJCEPolicyJDK8/README.txt
  inflating: UnlimitedJCEPolicyJDK8/US_export_policy.jar
[oracle@linux06 JCE]$ ls -ll
total 16
-rw-rw-r--. 1 oracle oracle 8409 Dec 14 10:39 jce_policy-8.zip
drwxrwxr-x. 2 oracle oracle 4096 Dec 20  2013 UnlimitedJCEPolicyJDK8
[oracle@linux06 JCE]$ pwd
/u01/app/SFTW/JCE
[oracle@linux06 JCE]$ ls
jce_policy-8.zip  UnlimitedJCEPolicyJDK8
[oracle@linux06 JCE]$ cd UnlimitedJCEPolicyJDK8/
[oracle@linux06 UnlimitedJCEPolicyJDK8]$ ls
local_policy.jar  README.txt  US_export_policy.jar

********************    ********************    ********************
as root user backup and replace files (US_export_policy & local_policy.jar)
********************    ********************    ********************

[oracle@linux06 security]$ su root
Password:
[root@linux06 security]# ls
blacklist          java.policy    local_policy.jar
blacklisted.certs  java.security  trusted.libraries
cacerts            javaws.policy  US_export_policy.jar
[root@linux06 security]# cd /usr/java/default/jre/lib/security
[root@linux06 security]# mv US_export_policy.jar US_export_policy.jar_bak
[root@linux06 security]# mv local_policy.jar local_policy.jar_bak
[root@linux06 security]# ls -ll
total 164
-rw-r--r--. 1 root root   4054 Sep 22 18:23 blacklist
-rw-r--r--. 1 root root   1273 Sep 22 18:23 blacklisted.certs
-rw-r--r--. 1 root root 112860 Sep 22 18:23 cacerts
-rw-r--r--. 1 root root   2466 Sep 22 18:23 java.policy
-rw-r--r--. 1 root root  27358 Sep 22 18:23 java.security
-rw-r--r--. 1 root root     98 Sep 22 18:23 javaws.policy
-rw-r--r--. 1 root root   3405 Sep 22 18:35 local_policy.jar_bak
-rw-r--r--. 1 root root      0 Sep 22 18:23 trusted.libraries
-rw-r--r--. 1 root root   2920 Sep 22 18:35 US_export_policy.jar_bak
[root@linux06 security]# pwd
/usr/java/default/jre/lib/security
[root@linux06 security]# cp /u01/app/SFTW/JCE/UnlimitedJCEPolicyJDK8/US_export_policy.jar /usr/java/default/jre/lib/security
[root@linux06 security]# cp /u01/app/SFTW/JCE/UnlimitedJCEPolicyJDK8/local_policy.jar /usr/java/default/jre/lib/security
[root@linux06 security]# ls -ll
total 172
-rw-r--r--. 1 root root   4054 Sep 22 18:23 blacklist
-rw-r--r--. 1 root root   1273 Sep 22 18:23 blacklisted.certs
-rw-r--r--. 1 root root 112860 Sep 22 18:23 cacerts
-rw-r--r--. 1 root root   2466 Sep 22 18:23 java.policy
-rw-r--r--. 1 root root  27358 Sep 22 18:23 java.security
-rw-r--r--. 1 root root     98 Sep 22 18:23 javaws.policy
-rw-r--r--. 1 root root   3035 Dec 14 10:47 local_policy.jar
-rw-r--r--. 1 root root   3405 Sep 22 18:35 local_policy.jar_bak
-rw-r--r--. 1 root root      0 Sep 22 18:23 trusted.libraries
-rw-r--r--. 1 root root   3023 Dec 14 10:46 US_export_policy.jar
-rw-r--r--. 1 root root   2920 Sep 22 18:35 US_export_policy.jar_bak