Create Oracle SQL Tuning Task Manually

    An Oracle SQL Tuning Task is a logical container used by Oracle Database's built-in SQL Tuning Advisor to analyze and optimize specific SQL statements.

    When you create a tuning task, you register one or more SQL statements (retrieved from memory, the Automatic Workload Repository (AWR), or entered manually as text) to be evaluated. When executed, the database analyzes the execution path, identifies performance bottlenecks, and generates a detailed optimization report.

CREATE TUNING TASK

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '5mn47p0gv2q83',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 4000,
                          task_name   => 'Task_5mn47p0gv2q83',
                          description => 'Manual Tuning on 06 SEP 16');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXECUTE TUNING TASK

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Task_5mn47p0gv2q83');

CHECK STATUS OF TASK

SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'Task_5mn47p0gv2q83';

CHECK RECOMMENDATION

SET LONG 10000;
SET PAGESIZE 1000;
SET LINESIZE 1000;
SELECT DBMS_SQLTUNE.report_tuning_task('Task_5mn47p0gv2q83') AS recommendations FROM dual;

Read More

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

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

Move or Rename Oracle 19c home

      

        I installed Oracle database 19.3 in my lab with Oracle Home /u01/app/oracle/product/19.3.0. I have applied RU patches and now my Oracle database version become 19.7.0. Though the existing home did not produce any error to me it looks lit bit odd so I planned to move Oracle home to /u01/app/oracle/product/19.7.0. 

       I tried the MOS document titled How to move RAC Database ORACLE_HOME from one location to another one (Doc ID 1438719.1) but clone.pl is no longer the best option for 19c. I read a few posts and finally, the following steps worked for me.

Check if Oracle database and shut down Oracle database if it is running. Stop listener process too.

shutdown immediate
lsnrctl stop

Create a new Oracle Home with Linux mkdir command.

mkdir /u01/app/oracle/product/19.7.0

Change directory to existing Oracle Home and execute runInstaller to create gold Image.

cd $ORACLE_HOME
./runInstaller -silent  -createGoldImage -destinationLocation /u01/app/oracle/product/19.7.0

Change directory to new Oracle HOme and unzip the Gold Image.

cd /u01/app/oracle/product/19.7.0
unzip db_home_2021-08-30_05-04-53AM.zip

Create a directory dbhome_1 and move all of the content to dbhome_1. We can create the dbhome_1 directory in the earlier steps and directory extract the Gold Image to the directory.

Once the extract is completed copy the response file and modify it. Once the file is modified option values look something like the below.

Execute runInstaller. Since I am using OEL 8 I need to export CV_ASSUME_DISTID env variables before running runInstaller.

export CV_ASSUME_DISTID=OEL7.6
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ~/db_install.rsp \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true

Check Inventory.

cat /u01/app/oraInventory/ContentsXML/inventory.xml

Change Environment variables.

vi ~/.bash_profile

Remove old ORACLE_HOME

cd /u01/app/oracle/product/19.3.0/dbhome_1/deinstall
./deinstall

Finally, delete the older ORACLE HOME.

rm /u01/app/oracle/product/19.3.0

Read More

CRS-6706: Oracle Clusterware Release patch level ('') does not match Software patch level (''). Oracle Clusterware cannot be started.

If you noticed CRS-6706 error while starting cluster after patching as below. The error is due to the rootcrs.sh -prepatch not run successfully before patching. The user ran rootcrs.sh -unlock and apply the patch manually. Please follow the steps below to resolve it.

An error was noticed during cluster start.

crsctl start crs

Execute the command below as user root. The command below will sync the patch locally.

cd $GI_HOME/bin

./clscfg -localpatch

Run the command below as user root to lock the crs

cd $GI_HOME/crs/install

./rootcrs.sh -lock

Start Oracle cluster with the command below.

cd $GI_HOME/bin

./crsctl start crs

Check crs with the command below.

cd $GI_HOME/bin

./crsctl stat res -t

.

Read More