Patching: Apply Oracle Database Patch on Linux/Unix host

 How to apply Oracle Database Patches in Linux?

This guide explains how to apply database patches into oracle database and oracle home running on Unix/Linux/ Aix etc.
First, we need to download the required patch from oracle support or metalink. 
Then ftp/scp the patch in a shared path on the server. 

Login to the server:

Setup environment variables.

ORACLE_SID=dev

ORACLE_HOME=/u01/app/oracle

ORACLE_BASE=/u01/app

PATCH_TOP=<Temporary location where the patch is staged>

PATH=$ORACLE_HOME/OPatch:$PATCH_TOP:$PATH:.

Note: Setup the PATH Variable to point to OPatch directory and the Patch directory where the patch is staged.

ORA-09925: Unable to create audit trail file

Issues with Database login (ORA-09925)

ERROR:

ORA-09925: Unable to create audit trail file

SVR4 Error: 49: Disc quota exceeded

Additional information: 9925

ORA-01075: you are currently logged on

This error occurs when the filesystem is filled up and oracle is not able to allocate any space to connect to the database. To troubleshoot, we need to check the mountpoint and cleanup the log files or any old dump files. 

ORA-01940: cannot drop a user that is currently connected

Issue with drop user in Oracle

While Dropping a user if the drop user command is throwing error like:

ORA-01940: cannot drop a user that is currently connected

Follow the below steps:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' "SQL Statement" from v$session where username=UPPER('&username');

SQL Statement

--------------------------------------------------------

alter system kill session '101,265842' immediate;

Execute the alter statement to kill the active session.

alter system kill session '101,265842' immediate;

System altered.

Now the drop user will work:

drop user <username> cascade;

Note: cascade option drops all the dependent objects under the schema.

How to check RMAN progress?

Check RMAN job progress 

The below query can be used to identify the progress of any rman job. Either backup or restore.

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

set line 200;

set pages 500;

set long 5000;

select sl.sid, sl.opname,

to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,

sysdate+(TIME_REMAINING/60/60/24) done_by

from v$session_longops sl, v$session s

where sl.sid = s.sid

and sl.serial# = s.serial#

and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')

and sofar != totalwork

and totalwork > 0

/

Basic ASM Commands

Most commonly used ASM Commands

OPatch Failed with error code 73

Oracle OPatch  Error

Error:

Oracle Home       : <ORACLE_HOME> Path 

Central Inventory : The inventory that is being used by this opatch session

   from           : The inventory that is stored in the Oracle Home

OPatch version    : 12.2.0.1.44

OUI version       : 12.2.0.7.0

Log file location : /u01/oracle/product/19.3.0/cfgtoollogs/opatch/opatch<date>.log

OPatchSession cannot load inventory for the given Oracle Home <oracle_home>. Possible causes are:

   No read or write permission to ORACLE_HOME/.patch_storage

   Central Inventory is locked by another OUI instance

   No read permission to Central Inventory

   The lock file exists in ORACLE_HOME/.patch_storage

   The Oracle Home does not exist in Central Inventory

UtilSession failed: OPatch failed to locate Central Inventory.

Possible causes are:

    The Central Inventory is corrupted

    The oraInst.loc file specified is not valid.

This is because, sometime the /etc/oraInst.loc is pointing to the ora inventory in a temp location which the $ORACLE_HOME/oraInst.loc is not aware.

cat /etc/oraInst.loc

cat $ORACLE_HOME/oraInst.loc

check if both the files are pointing to the same location. if not, take a backup of the file and update the central inventory in $ORACLE_HOME/oraInst.loc.

cp -p $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.old

Modify the file accordingly for parameter inventory_loc=<Full PATH of the inventory, which can be copied from /etc/oraInst.loc>

Note: One more observation on the file permission, if you are running the grid with different usernames, the same group and sharing the same inventory, make sure, the files under /u01/oraInventory/ContentsXML have 644 permissions. So that while doing patching with Oracle users, it can modify those XML files.

Hope this helps resolve the error, happy patching!!

Best Wishes!!

OPatch Session Hung Forever | CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

Performance Issue with OPatch

Opatch Hung forever

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

If you ever face issues with opatch utility is taking long time to run the prerequisite check and in the log you see the below line.

CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX
CUP_LOG: Found poh CUP XXXXXX is a subset of other poh CUP: XXXXX

It is time to cleanup some inactive patches from opatch inventory, to do that please follow the below instructions.

cd $ORACLE_HOME/OPatch

--This will list the inactive patches

./opatch util listorderedinactivepatches

--This will remove the inactive patches

./opatch util deleteinactivepatches

This will take some time depending on how many old inactive patches is present in the inventory.

Reference:

OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage Directory (Doc ID 2942102.1)

Oracle DB Links

What is DB link? 

Oracle DB links are used to connect to different sources through procedures/views. It helps creating views or materialized views by pulling data from different db sources.

To check the db links present in the database, the below query can be used.

Query:

select * from dba_db_links;

SQL> desc dba_db_links;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 OWNER                                     NOT NULL VARCHAR2(128)

 DB_LINK                                   NOT NULL VARCHAR2(128)

 USERNAME                                           VARCHAR2(128)

 HOST                                               VARCHAR2(2000)

 CREATED                                   NOT NULL DATE

 HIDDEN                                             VARCHAR2(3)

 SHARD_INTERNAL                                     VARCHAR2(3)

 VALID                                              VARCHAR2(3)

 INTRA_CDB                                          VARCHAR2(3)

Create a DB link:

Create or replace database link "<DB link name>" connect as "<remote username>" identified by "<Remote user password>" using "<Remote DB TNS details>";

Hope this helps. Please let me know if you face any issues, comment below.

Find Concurrent Program Run History from backend

Query to find concurrent program run history and status

SELECT f.request_id,

         pt.user_concurrent_program_name

             user_conc_program_name,

         f.actual_start_date

             start_on,

         f.actual_completion_date

             end_on,

         p.concurrent_program_name

             concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'RUNNING',

                 'C', 'COMPLETED',

                 f.phase_code)

             phase,

         DECODE (f.status_code,  'C', 'NORMAL',  'E', 'ERROR',  f.status_code)

             Status,

         f.requested_by,

         fu.user_id,

         fu.user_name

    FROM apps.fnd_concurrent_programs   p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests   f,

         apps.fnd_user                  fu

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('lang')

         AND f.actual_start_date IS NOT NULL

         AND f.actual_start_date >

             TO_DATE ('15-DEC-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

         AND f.actual_completion_date <

             TO_DATE ('17-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

         AND f.requested_by = fu.user_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME = '&User_concurrent_program_name'

--AND fu.user_name = '&user_name'

ORDER BY f.actual_start_date ASC; 

##Change the date accordingly.

Temp Tablespace Usage

Oracle Temp Tablespace usage

Check for Temp tablespace usage

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 as FILE_SIZE,sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_temp_files

where tablespace_name ='TEMP' group by file_name,tablespace_name,autoextensible order by file_name;

Query to find Tablespace utilization in Oracle Database

Oracle Tablespace Utilization

Find the tablespace utilization on Oracle Database (10g,11g,12c,19c)

column file_name format a45

column tablespace_name format a10

col tablespace_name for a40

set verify off

set pages 3000

set line 3000

 

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes* 100, 0), '990.00') free_pct,

decode(sign((NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes/1024/1024, 0)),-1,0,

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.90 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB" FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

order by free_pct;

Best Wishes!!

Find session details sid/pid from concurrent request id

Oracle Performance Analysis - Long running Concurrent Requests

The following query can be used to find the sid,pid, spid for a specific concurrent request. If a program is running for long time and user requests to verify the root cause, this will help DBA to find the sid and then dba can go beyond this and look for sqlid, plan etc. This will be helpful while doing performance analysis

SELECT a.request_id,

       d.sid,

       d.serial#,

       d.sql_id,

       d.osuser,

       d.process,

       d.client_identifier "requestor" ,

       c.SPID

  FROM apps.fnd_concurrent_requests   a,

       apps.fnd_concurrent_processes  b,

       v$process c,

       v$session d

 WHERE     a.controlling_manager = b.concurrent_process_id

       AND c.pid = b.oracle_process_id

       AND b.session_id = d.audsid

       AND a.request_id = '&REQUEST_ID';

 

Oracle Database User DBA Profile

Working with Oracle DBA Profile

There are several profiles can be created in a database, depending on the business need of any organization. In simple words the default profile has some extended feature which triggers a red light for auditors and it is vulnerable as well. 

Execute the below query to find out the characteristics of any given profile.

select * from dba_profiles where profile='DEFAULT';

It will list up all the resources that are being defined for this profile and its limit. 

Likewise you can create your own profile resource type and limit.

Example:

create profile test_profile_unlimited limit 

SESSIONS_PER_USER UNLIMITED

CPU_PER_SESSION UNLIMITED

  CPU_PER_CALL UNLIMITED

  CONNECT_TIME UNLIMITED

  IDLE_TIME UNLIMITED

  LOGICAL_READS_PER_SESSION UNLIMITED

  LOGICAL_READS_PER_CALL UNLIMITED

  COMPOSITE_LIMIT UNLIMITED

  PRIVATE_SGA UNLIMITED

  FAILED_LOGIN_ATTEMPTS UNLIMITED

  INACTIVE_ACCOUNT_TIME DEFAULT

  PASSWORD_LIFE_TIME UNLIMITED

  PASSWORD_REUSE_TIME UNLIMITED

  PASSWORD_REUSE_MAX UNLIMITED

  PASSWORD_LOCK_TIME UNLIMITED

  PASSWORD_GRACE_TIME UNLIMITED; 

This will create a profile which will be having all the resources set to unlimited.  User having this profile has unlimited attempt for password and user can reuse same password unlimited time. Which is not at all recommended for the system. This could only be used if the user is a service user/ ant AI user / any Bot user . 

If you have any questions regarding user profile please comment or send direct mail. Are you facing any error while creating user profile or assigning any profile to user. Let me know in comment and I will try to reproduce the issue on my test system and fix it. Thanks for your time!!

Create Oracle Database User

Oracle Database User Management

This is the simplest task for a Database Administrator. 

Login to sqlplus session from your OS(Windows/Linux/AIX) using sysdba / a user who has create user privilege. 

sqlplus / as sysdba

alter user <username> identified by <password>;

this will create a user with default tablespace and profile. Depending on the organization's policy and user's requirement you might need to mention few other options in the query. Those are advanced options you can explore more on my advance create user query.

Now user needs some privileges to connect to the database and do some transactions or query any tables.

CONNECT - this is the Oracle-defined default privilege for any user to be able to connect to the database.

grant connect to <username>;

alternatively, grant create session to <username>;  will do the same.

Now, for the user to be able to query any table in the db, one basic privilege is required.

grant select any table to <username>;

This will create a db user in Oracle. 

To see the user is created properly you can validate with below query.

select * from dba_users where username='<username>';

The dba_users is a data dictionary view which eventually being created from all_users. To know more about all_users and dba_users column properties stay tuned!!

Best Wishes!!