Skip to main content

Welcome to DBA Master – Database Tips, Tricks, and Tutorials

Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Post a Comment

Oracle Patching Using Opatch Utility

Opatch:

It is a utility for patching Oracle software, specifically Oracle databases and related products. It is used to apply, roll back, and check Oracle patches, including interim patches, one-off patches, and bundle patches.

Types of Patches:

Patch Type

Purpose

Release Frequency

Includes Security Fixes?

Includes Bug Fixes?

Includes New Features?

One-off Patch

Fix a specific issue

As needed

No

Yes

No

PSU (Patch Set Update)

Security + Bug fixes

Quarterly

Yes

Yes

Minimal

CPU (Critical Patch Update)

Security fixes only

Quarterly

Yes

No

No

RU (Release Update)

Security + Bug + Functional changes

Quarterly

Yes

Yes

Yes

BP (Bundle Patch)

Exadata/Windows patches

As needed

Yes

Yes

Yes

Data Patch

Updates database dictionary

With PSU/RU

No

Yes

No

Needed:

  • OPatch version (12.2.0.1.30 or later for Oracle 19c). Opatch file name is p6880880_200000_Linux-x86-64.zip
  • Patch from https://updates.oracle.com/download/29834717.html . Patch file name is p31771877_190000_Linux-x86-64.zip


Step 1: Check Opatch Version:

$cd /usr/local/oracle/19c/OPatch/
$./opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.

Step 2: Update the Opatch Tool:

$ pwd
/usr/local/oracle/19c
$ mv OPatch/ OPatch_BKP
$ unzip p6880880_200000_Linux-x86-64.zip
Archive: p6880880_200000_Linux-x86-64.zip
creating: OPatch/
inflating: OPatch/emdpatch.pl
creating: OPatch/oracle_common/
creating: OPatch/oracle_common/modules/
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
creating: OPatch/oplan/

$ cd OPatch
$ ./opatch version
OPatch Version: 12.2.0.1.23
OPatch succeeded.

Step 3: Check the Patche status for current:

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10

SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,action,status, description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;

 

col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Identifying Invalid Objects

COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’ ORDER BY owner, object_type, object_name;

Step 4: Down the Database and listener

Step 5: take Backup on Oracle Home and Inventory Directories or RMAN Full Backup

Step 6: Run the Optach utility.

$ unzip p31771877_190000_Linux-x86-64.zip
$ cd 31771877
export ORACLE_HOME=/usr/local/oracle/19c/
export PATH=/usr/local/oracle/19c/OPatch:$PATH
opatch apply

OR

$ unzip p31771877_190000_Linux-x86-64.zip
$ cd 31771877
/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/19.0.0/dbhome_1

.

.

.

 

Step 7: List Patches

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lsinv -oh /u01/app/oracle/product/19.0.0/dbhome_1

Step 8: start the database and listener

Step 9: Apply the SQL Changes run datapatch

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/datapatch -verbose

Step 10: Verify the DBA_REGISTRY_SQLPATCH

SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN patch_type FORMAT A10
COLUMN description FORMAT A32
COLUMN status FORMAT A10
COLUMN version FORMAT A10
spool check_patches_19c.txt

select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id;
spool off

Step 10: verify the invalid Objects:

SQL> select count(*) from dba_objects where status='INVALID';

Any Invalid objcects Run Below

@?/rdbms/admin/utlrp.sql

Check the last updated OPatch

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lsinventory
/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/opatch lspatches

Rolling Back a Patch (If Needed)

/u01/app/oracle/product/19.0.0/dbhome_1/OPatch /opatch rollback -id 31771877

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database 19C Performance Tunning - PART 1

Advantages: 1. Improved Query Performance •    Optimized SQL execution plans lead to faster query response times. •    Reduces unnecessary full table scans and improves indexing strategies. •    Parallel execution tuning speeds up large data processing tasks. 2. Better Resource Utilization •    Efficient use of CPU, memory, disk I/O, and network resources. •    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache. •    Helps in load balancing across multiple instances in RAC (Real Application Clusters). 3. Increased System Scalability •    Ensures that the database can handle a growing number of users and transactions. •    Proper tuning allows scaling without degrading performance. •    Optimized parallel processing ensures better performance on multi-core servers. 4. Lower Infrastructure Costs •    Reduces the need for add...
Post a Comment
Read more

Oracle RMAN Backup And Restore

RMAN: (Oracle 8) RMAN (Recovery Manager) is a utility provided by Oracle Database to perform backup, restore, and recovery operations. It is a command line tool. Features of RMAN in Oracle 19c Comprehensive Backup Capabilities: Full and incremental backups. Block-level backups for efficient data storage. Archived redo log backups. Fast Recovery Area (FRA) integration for centralized backup storage. Efficient Recovery Options: Point-in-time recovery (PITR). Complete and incomplete recovery. Flashback database capabilities for quick undo of changes. Multitenant Database Support: RMAN fully supports container databases (CDBs) and pluggable databases (PDBs). Provides flexibility to back up and recover individual PDBs or entire CDBs. Automatic Space Management: Manages disk space in the FRA. Automatically deletes obsolete backups and archived logs. Data Deduplication and Compression: Backup optimization through block-level deduplication. Built-in compression algorithms to reduce storage req...
Post a Comment
Read more

Oracle 19c Database Software Installation in OEL8

 Pre-requisites for OS level:            Set the static IP Address     Disable the Firewall (systemctl stop firewalld & systemctl disable firewalld)     set SELINUX=permissive on /etc/selinux/config  ##Need to restart the server use init 6 Oracle Installation Pre-requisites Methods     Automatic Setup     Manual Setup      Automatic requisites Setup: (avoid step 1 to step 5): dnf install -y oracle-database-preinstall-19c Install the dependencies: curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm Manual Setup: step 1: Add the karenl parameters and values vi /etc/sysctl.conf     fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel....
Post a Comment
Read more