Oracle Database configure TCP/IP with SSL and TLS for Database Connections

    Encryption is the method to convert information into secret codes to protect from the bad guys. The real meaning of the information is hidden and the process of encrypting and decrypting data is called cryptography. The formulas used for cryptography are called encryption algorithms, or ciphers.

    Oracle supports two network encryption: Oracle Native Network Encryption and TCP/IP with SSL and TLS.

    Oracle native network encryption is very simple and needs to change a parameter in sqlnet.ora to enable encryption.  All you need is to configure the server to use network encryption. The advantage of native network encryption over TLS/SSL is easy configuration. However, the downside of this method is that to a certain extent, a man-in-the-middle attack is still possible. If an attacker can get into the network and redirect clients to use a malicious database, clients will not be knowing about this although all the communications will be encrypted.

    In a nutshell, if you want easy configuration, oracle Native network encryption works for you. For maximum security, use TLS/SSL. The only downside of the TLS/SSL is harder to set up and maintain.

Here are the step-by-step guide to setup and maintaining TLS/SSL.

Server hostname: ora19c

Client hostname: oraclient

1. Execute the commands below in the server.

a. Create a directory in the client and server to store the wallet. In my case, I am planning to use /u01/app/oracle/wallet as my wallet directory in both the Oracle database client and the server.

mkdir -p /u01/app/oracle/wallet

b. Create auto login local wallet with the command below. 

orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 -auto_login_local

c. Create a self-signed certificate with 10 years of validity and load it into the recently created wallet.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

  -dn "CN=$(hostname)" -keysize 1024 -self_signed -validity 3650

d. Check wallet contents. You will notice one user certificate and one trusted certificate with CN exactly the same as the server hostname.

orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd myWallet_321

e. Export wallet so that it can be loaded to the client end. Ship the server-$(hostname)-certificate.txt file once the export is completed. 

orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

   -dn "CN=$(hostname)" -cert /tmp/server-$(hostname)-certificate.crt

f. Validate the content of the server-certificate.crt file. The file should begin with -----BEGIN CERTIFICATE----- and end with -----END CERTIFICATE-----. The output will be something similar to the screenshot below.

cat /tmp/server-$(hostname)-certificate.crt

2. Execute the command below at the client end.

a. Create a directory to store the wallet.

mkdir -p /u01/app/oracle/wallet

b. Create auto login local wallet with the command below. 

orapki wallet create -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 -auto_login_local

c. Create a self-signed certificate with 10 years of validity and load it into the recently created wallet.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-dn "CN=$(hostname)" -keysize 1024 -self_signed -validity 3650

d. Display the contents of the wallet.

orapki wallet display -wallet "/u01/app/oracle/wallet" -pwd myWallet_321

e. Export the wallet so that it can be added to the server as a trusted certificate.

orapki wallet export -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-dn "CN=$(hostname)" -cert /tmp/client-$(hostname)-certificate.crt

f. Check exported file contents.

cat /tmp/client-$(hostname)-certificate.crt

3. Ship server-$(hostname)-certificate.crt to the client host and client-$(hostname)-certificate.crt to the server host. In my case /mnt is the shared directory between both the hosts.

cp /tmp/client-oraclient-certificate.crt /mnt/certs/

cp /tmp/server-ora19c-certificate.crt /mnt/certs/

4. Add client certificate to server wallet as trusted cert in the server host.

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-trusted_cert -cert /mnt/certs/client-oraclient-certificate.crt

# Before Adding the trusted cert

# Adding the trusted cert

# After adding the trusted cert.

5. Add server certificate to client wallet as a trusted cert

orapki wallet add -wallet "/u01/app/oracle/wallet" -pwd myWallet_321 \

-trusted_cert -cert /mnt/certs/server-ora19c-certificate.crt

# Before adding the server trusted cert.

# Adding the server trusted cert.

# After adding the server trusted cert.

6. Server sqlnet.ora configuration.

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/wallet)

     )

   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

7. Client sqlnet.ora configuration.

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /u01/app/oracle/wallet)

     )

   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

8. Server-side Listener configuration

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =

  (SOURCE =

    (METHOD = FILE)

    (METHOD_DATA =

      (DIRECTORY = /u01/app/oracle/wallet)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCPS)(HOST = ora19c)(PORT = 2484))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

# Existing listener status

Bounce listener to reflect the change on listener configuration file: listener.ora

lsnrctl stop

lsnrctl start

Validate listener configuration

lsnrctl status

9. Add the following tnsnames in the tnsnames.ora file of client tnsnames configuration

orclpdb1=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=ora19c)

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=orclpdb1)

    )

  )

orclpdb1_ssl=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCPS)

      (HOST=ora19c)

      (PORT=2484)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=orclpdb1)

    )

  )

10. Testing connection.

# Tnsping output

Using TCP connection. The network protocol used will be TCP while executing the select statement.

sqlplus test/test123@orclpdb1

set sqlp "_user @ _connect_identifier >"

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Using TCPS connection. The network protocol used will be TCPS while executing the select statement.

sqlplus test/test123@orclpdb1_ssl

set sqlp "_user @ _connect_identifier >"

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

Read More

Oracle Database Native Encryption

    

     Encryption is the method to convert information into secret codes to protect from the bad guys. The real meaning of the information is hidden and the process of encrypting and decrypting data is called cryptography. The formulas used for cryptography are called encryption algorithms, or ciphers.

    Encryption plays a vital role in protecting information. It provides the following primary benefits.

1. Confidentiality encodes the message's content.
2. Authentication verifies the origin of a message.
3. Integrity proves the contents of a message have remained the same since it was sent.
4. Nonrepudiation prevents senders from denying they sent the encrypted message.

    Oracle database provides network encryption to protect data while traveling through the network. It offers native data network encryption and integrity to ensure that is secure from the bad guys. Oracle Native network encryption converts plaintext data into unintelligible ciphertext based on a key. In a symmetric cryptosystem, the same key is used both for encryption and decryption of the same data. Oracle Database provides the Advanced Encryption Standard (AES) symmetric cryptosystem for protecting the confidentiality of Oracle Net Services traffic.

    Oracle Database supports the Federal Information Processing Standard (FIPS) encryption algorithm, Advanced Encryption Standard (AES). AES is a highly supported algorithm all over the works. It defines three standard key lengths of 128-bit, 192-bit, and 256-bit.

    Encrypting network data provides data privacy so that unauthorized parties cannot view plaintext data as it passes over the network.

Oracle database native encryption is easy to deploy and follows the step-by-step guide to deploy it.

All configurations are done in the "sqlnet.ora" files on the client and server. 

Set the following parameters in the sqlnet.ora file of the server.

SQLNET.ENCRYPTION_SERVER

SQLNET.ENCRYPTION_TYPES_SERVER

Set the following parameters in the sqlnet.ora file of the client.

SQLNET.ENCRYPTION_CLIENT

SQLNET.ENCRYPTION_TYPES_CLIENT

Following are the acceptable values for SQLNET.ENCRYPTION_[SERVER|CLIENT]

1. ACCEPTED: It is the most relaxed and default parameter if the parameter is not set. The client or server will allow both encrypted and non-encrypted connections. 
2. REJECTED: It is the plain-text-only parameter and both client and server will refuse encrypted traffic.
3. REQUESTED: It is a relaxed and somewhat secure way for encryption. The client or server will request encrypted traffic whenever possible but will accept non-encrypted traffic if encryption is not possible.
4. REQUIRED: It is the highly restricted value for the parameter, the client or server will only accept encrypted traffic.

Set the following parameters in sqlnet.ora of the server to encrypt Oracle database network traffic using the AES256 algorithm. 

SQLNET.ENCRYPTION_SERVER=REQUIRED

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

Set the following parameters in sqlnet.ora of the client to encrypt Oracle database network traffic using the AES256 algorithm. 

SQLNET.ENCRYPTION_CLIENT=REQUIRED

SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

The above configuration will discard any unencrypted traffic. To get a lit bit of relaxed configuration we can use the following parameters in the sqlnet.ora file of the server. It will prefer a client to use an encrypted connection to the server but will accept a non-encrypted connection too.

SQLNET.ENCRYPTION_SERVER=REQUESTED

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

It is highly advised to upgrade the Oracle database client version to support encryption. I have noticed a lot of incidents in the database due to clients not supporting the encryption. The output of the encrypted connection has been attached below. Executed command below to find if encryption is enabled while connecting to the database.

set line 1000

col NETWORK_SERVICE_BANNER for a100

select SID, SERIAL#,NETWORK_SERVICE_BANNER 

from V$SESSION_CONNECT_INFO 

where sid=(select sid from v$mystat where rownum=1);

# Unencrypted connection.

# Added parameters to the server side sqlnet.ora

# Added parameters to the client side sqlnet.ora

# Encrypted connections.

Read More

Decrypt Oracle Weblogic password.

    Oracle WebLogic Server is a Java EE application server currently developed by Oracle Corporation. Oracle acquired WebLogic Server when it purchased BEA Systems in 2008. It is a chance that we may forget the WebLogic admin username and password. Following is the step-by-step guide to recovering the WebLogic admin user and password.

1. Set oms environment variables.

. oraenv <<< oms 

2. Changed directory to the GCDomain/bin

cd  /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/bin/

ls -alh

3. The WebLogic settings are in the setDomainEnv.sh script file(setDomainEnv.cmd for Windows and setDomainEnv.sh for Linux). Execute the command below to set WebLogic environment variables.

./setDomainEnv.sh

4. The username and password are stored in encrypted format in the file boot.properties below. Search the file with the name. There may be multiple boot.properties files, but the required one is inside the security directory.

find /u01 -iname boot.properties

Choose the one with the security/boot.properties as in the screenshot below.

/u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security/boot.properties

5. WebLogic Scripting Tool (WLST) is a command-line scripting interface system administrators and operators use to monitor and manage WebLogic Server instances and domains. Execute wlst.sh inside the oracle_common/common/bin directory.

${ORACLE_HOME}/oracle_common/common/bin/wlst.sh

Type the following command to fetch the password.

Once you execute the wlst.sh the prompt will be like below. 

wls:/offline>

6. Execute the command one at a time. Make sure you replace the location of the boot.properties noted in the earlier step.

from weblogic.security.internal import BootProperties

BootProperties.load("<boot.properties location>", false)

prop = BootProperties.getBootProperties()

print "username: " + prop.getOneClient()

print "password: " + prop.getTwoClient()

The password was recovered successfully.

Read More

How to find Oracle Enterprise Manager startup time.

 

    Oracle Enterprise Manager(OEM) is an on-premises solution for a comprehensive monitoring and management solution for Oracle products such as Oracle Database and Engineered Systems. A lot of plugins, monitoring Templates, thresholds, and other features make OEM a great tool for monitoring.

    Few days ago, I was asked to find the Oracle Management Service(OMS) startup time. I googled a lot and search for a better way to present the startup time for the OEM process. Finally, after 2 hours of searching I found the solution to find the startup time of the OEM.

    Following is the step-by-step process to find the startup time of the OMS startup time.

1. Click on Setup >> Manage Cloud Control >> Management Servers.

2. Click on the Management Servers >> Monitoring >> Status History.

3. The time value in the Up Since provides the time the OMS server has been started.

4. At the top left side, there is a drop-down menu to check the Overall Availability Duration. We have the option to check availability during the last 24 hours, last 7 days, last 31 days, and custom.

Read More

How to change ASMSNMP user password in Oracle RAC Database.

        The ASMSNMP user is an Oracle ASM user with privileges to monitor Oracle ASM instances. The ASMSNMP user password is required to configure monitoring. Following are the steps by step guides to change asmsnmp password if you forgot.

        The ASMSNMP user password is required while creating an Oracle RAC database. If you forgot, then you can follow the below steps to reset asmsnmp user password.

        Log in as grid user or export to  ORACLE ASM-HOME and SID as ASM instance if configured both grid and oracle in one user.

Method 1: Use alter user command.

        The command requires the SYSASM privilege to run. A user logged in as SYSDBA cannot change their password using this command.

Connecting to the ASM instance and executing an alter user

. oraenv <<< +ASM1

$ sqlplus / as sysasm

SQL> select * from v$pwfile_users;

USERNAME                        SYSDB  SYSOP  SYSAS
------------------------------  -----  -----  -----
SYS                             TRUE   TRUE   TRUE
ASMSNMP                         TRUE   FALSE  FALSE

SQL> ALTER USER asmsnmp IDENTIFIED BY <new_password>;

SQL> exit

Method 2: Using orapwusr command. 

The orapwusr attempts to update passwords on all nodes in a cluster. 

Connect as grid user, and export ASM instance variables.

. oraenv <<< +ASM1
$ asmcmd
ASMCMD> lspwusr
Username  sysdba  sysoper  sysasm
     SYS    TRUE     TRUE    TRUE
 ASMSNMP    TRUE    FALSE   FALSE

ASMCMD> lspwusr
Username  sysdba  sysoper  sysasm
     SYS    TRUE     TRUE    TRUE
 ASMSNMP    TRUE    FALSE   FALSE

The username is case-sensitive while using orapwusr command, use ASMSNMP in upper case.
ASMCMD> orapwusr
usage: orapwusr { { { --add | --modify [--password] }[--privilege {sysasm|sysdba|sysoper} ] } | --delete } user
help:  help orapwusr
ASMCMD> orapwusr --modify --password ASMSNMP
Enter password: *******



-- 12c and above.

[oracle@host19c +ASM1]$ asmcmd 
ASMCMD> lspwusr 
       Username sysdba sysoper sysasm  
            SYS   TRUE    TRUE   TRUE  
CRSUSER__ASM_001   TRUE   FALSE   TRUE  
        ASMSNMP   TRUE   FALSE  FALSE  

ASMCMD> help orapwusr 
usage: orapwusr {--add | --modify | --delete | --grant {sysasm|sysdba|sysoper} | --revoke {sysasm|sysdba|sysoper} } <user> 
 ASMCMD> orapwusr --modify ASMSNMP 
Enter password: ******** 
ASMCMD> 

Read More

Install Oracle Database using docker in Mac M1

 

   

    The new Mac with Apple Silicon is ARM 64-bit architecture and does not directly support x86_64 or AMD 64-bit applications. There are multiple ways to make an x86_64 bit application run with the arm processor. There is no easy and straightforward way to install Oracle Database using docker in mac with M1/M2 chip. Following is the step-by-step guide to install the Oracle database using docker in a mac M1 processor.

Install Homebrew and other required packages.

    Homebrew is a package manager for macOS. It is one of the first tools that need to install on your mac to install docker for x86_64 containers. It is used to install (and remove) software programs for the terminal, or CLI. Almost every useful open-source package is available through Homebrew. Installing Homebrew is pretty easy. Execute the command below in the terminal window to install Homebrew.

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

    Set environment variables to include brew binary in the PATH environment variable. Execute the following command in the terminal

export PATH=/opt/homebrew/bin:$PATH

    Uninstalled any docker package installed by App Store or using dmg files. It will avoid any crossover with the software. Install the following brew packages. Here, Docker is an open platform for developing, shipping, and running applications using Docker containers. Similarly, Docker Compose is a tool that was developed to help define and share multi-container applications. Colima is a container runtime on MacOS (and Linux) with minimal setup. Colima is almost a drop-in replacement for Docker Desktop. Colima directly supports the ARM chip and it can start up the VM that will run the x86_64 containers. Qemu is an emulator for x86 and arm chips.

$ brew install colima
$ brew reinstall qemu
$ brew install docker
$ brew install docker-compose

    Start a VM to run an x86_64 docker container.  Execute command in the terminal. The command below starts a VM that uses 4 CPUs, and 12G of memory, and supports the x86_64 architecture. This should allow me to download and run an x86_64 container.

colima start -c 4 -m 12 -a x86_64

Validate the status of the VM that was started.

colima list

Once docker everything is ready, export a running docker x86_64 container with the Oracle database running. I tried to install the Oracle database using Oracle's official docker repo but encountered some java related issues. The second time, I installed OEL 7.9 and tried to install Oracle database software and Oracle database but it failed again with the Java issue. I will troubleshoot the issue later but for this post, I am saving my running Intel-based x86_64 docker container image to a file and loading a file to the docker with an M1 chip.

    Due to a license issue, I cannot share my Oracle Database docker Image file. I am assuming ora19c.gz is a file where I have loaded Oracle docker Image.

Syntax to load the docker image to a file.

docker save oracle/database:19.3.0-ee | gzip  > ora19c.gz

    Once the docker image is saved to the file with the name ora19c.gz. Ship the file to the Mac book Maching with M1 chip. Load the image file copy with the command below.

docker load < ora19c.gz

Create and run a new container from an image oracle.

docker run -d -t -i --privileged --name=oracle19c --hostname=oracle19c -p 1522:1521 -v /Users/raj:/mnt ora19c oracle/database:19.3.0-ee

Verify docker container is running.

docker ps 

Execute /bin/bash command in a running docker container.

docker exec -it -u oracle oracle19c /bin/bash

Starting Oracle Database container later.

Verify if colima is started, if not start with the command below.

/opt/homebrew/bin/colima list
/opt/homebrew/bin/colima start

Start Oracle Database container.

docker start oracle19c

Execute /bin/bash in the running oracle database container.

docker exec -it -u oracle oracle19c /bin/bash

Read More