Search This Blog

Total Pageviews

Monday, 16 June 2025

OPatch Apply for Grid 19.27 <<<< 37641958

Oracle Grid Infrastructure 19.27 Patch Log

Oracle Grid Infrastructure 19.27 Patch (37641958) - Application Log

📋 Patch Information

  • Patch ID: 37641958
  • Patch Type: GI RU 19.27.0.0.250415
  • OPatch Required: 12.2.0.1.45 or later download only same base p6880880_190000_Linux-x86-64.zip v
  • Oracle Home: /u01/app/19.0.0/grid

📁 Directory Structure of Patch Location

/home/grid/37641958/
├── 37654975/
├── 37643161/
├── 37762426/
├── 36758186/
├── 37642901/
├── README.txt
├── README.html
├── bundle.xml
└── automation/

⚠️ Pre-checks & Requirements

  • Review README.txt and README.html in patch directory.

OPatch Version Check

export PATH=$ORACLE_HOME/OPatch:$PATH
opatch version
# Output: OPatch Version: 12.2.0.1.46 ✅

Disk Space Verification

  • Required: ~14.5GB
  • Initial Free Space on /: 17GB
  • After patch: 12GB available

Relocated Old Patch Data

mv /u01/app/19.0.0/grid/.patch_storage/36912597_Oct_11_2024_06_47_24/ /home/grid/
  
 
 Create space .. 
 
 Delete Inactive Patches (OPatch 12.2.0.1.37+)
 $ORACLE_HOME/OPatch/opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
 
 ./opatch util listorderedinactivepatches
./opatch util deleteinactivepatches
./opatch util cleanup
 

  
  
  

    
🔎 Conflict Checks for Each Subpatch

    
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/<PATCH_ID>

    
✔ Prereq "checkConflictAgainstOHWithDetail" passed.

    
       
    
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/37654975| grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.

[grid@srv1 ~]$
[grid@srv1 ~]$

[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/37643161| grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.


[grid@srv1 ~]$
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/37762426| grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.

[grid@srv1 ~]$
[grid@srv1 ~]$
[grid@srv1 ~]$
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/37642901| grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.



[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/37641958/36758186| grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.


       
    
    
    
  


  

    
🛠️ Patch Apply Procedure

    
⚠️ Ensure current directory is NOT /root or /

    
cd /home/grid/37641958
$ORACLE_HOME/OPatch/opatchauto apply /home/grid/37641958 -oh $ORACLE_HOME
# ❌ Error: Incompatible OPatch version (23.x vs required 12.2.0.1.x)
    


    
✅ Fixed by Replacing OPatch:

    
unzip -qqd /u01/app/19.0.0/grid /home/grid/p6880880_190000_Linux-x86-64.zip
chown -R grid:oinstall OPatch


================
Download ..

unzip -qqd /u01/app/19.0.0/grid /home/grid/p6880880_190000_Linux-x86-64.zip
chown -R grid:oinstall OPatch


+ASM:/u01/app/19.0.0/grid:N             # line added by Agent
[root@srv1 grid]# . oraenv
ORACLE_SID = [root] ? +ASM
The Oracle base has been set to /u01/app/grid
[root@srv1 grid]# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@srv1 grid]#
[root@srv1 grid]#
[root@srv1 grid]# echo $ORACLE_HOME

/u01/app/19.0.0/grid
[root@srv1 grid]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/37641958 -oh $ORACLE_HOME

OPatchauto session is initiated at Mon Jun 16 13:10:57 2025

System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2025-06-16_01-11-06PM.log.

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2025-06-16_01-11-12PM.log
The id for this session is JATU

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid
Patch applicability verified successfully on home /u01/app/19.0.0/grid


Executing patch validation checks on home /u01/app/19.0.0/grid
Patch validation checks successfully completed on home /u01/app/19.0.0/grid


Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.0.0/grid
Prepatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2025-06-16_01-13-40PM.log
CRS service brought down successfully on home /u01/app/19.0.0/grid


Start applying binary patch on home /u01/app/19.0.0/grid
Binary patch applied successfully on home /u01/app/19.0.0/grid


Running rootadd_rdbms.sh on home /u01/app/19.0.0/grid
Successfully executed rootadd_rdbms.sh on home /u01/app/19.0.0/grid




Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.0.0/grid
Postpatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2025-06-16_01-27-11PM.log
CRS service started successfully on home /u01/app/19.0.0/grid

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:srv1
SIHA Home:/u01/app/19.0.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /home/grid/37641958/36758186
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2025-06-16_13-11-36PM_1.log
Reason: /home/grid/37641958/36758186 is not required to be applied to oracle home /u01/app/19.0.0/grid


==Following patches were SUCCESSFULLY applied:

Patch: /home/grid/37641958/37642901
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2025-06-16_13-14-37PM_1.log

Patch: /home/grid/37641958/37643161
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2025-06-16_13-14-37PM_1.log

Patch: /home/grid/37641958/37654975
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2025-06-16_13-14-37PM_1.log

Patch: /home/grid/37641958/37762426
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2025-06-16_13-14-37PM_1.log



OPatchauto session completed at Mon Jun 16 13:30:20 2025
Time taken to complete the session 19 minutes, 14 seconds


Time taken to complete the session 19 minutes, 14 seconds


After Patch space 

[root@srv1 grid]# df -Ph
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             3.8G     0  3.8G   0% /dev
tmpfs                3.8G  1.1G  2.7G  30% /dev/shm
tmpfs                3.8G  9.7M  3.8G   1% /run
tmpfs                3.8G     0  3.8G   0% /sys/fs/cgroup
/dev/mapper/ol-root   50G   39G   12G  77% /   --------------<<<<<< 
/dev/sda1           1014M  233M  782M  23% /boot
/dev/mapper/ol-home  339G  9.3G  330G   3% /home
tmpfs                771M   20K  771M   1% /run/user/54323
tmpfs                771M     0  771M   0% /run/user/0
[root@srv1 grid]#




    


    
✅ Successful Patch Output Summary

    
==Following patches were SKIPPED:
Patch: 36758186 - Not required

==Following patches were SUCCESSFULLY applied:
Patch: 37642901
Patch: 37643161
Patch: 37654975
Patch: 37762426

Total Time: 19 minutes, 14 seconds
    

  


  

    
📌 Post Patch Verification


    
Patch Inventory Check

    
opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch 37762426 applied at: Jun 16 13:26:01 GST 2025
Patch 37654975 applied at: Jun 16 13:25:36 GST 2025
Patch 37643161 applied at: Jun 16 13:24:49 GST 2025
Patch 37642901 applied at: Jun 16 13:19:57 GST 2025
    


    
SQL Version Check

    
sqlplus / as sysasm
SELECT * FROM v$version;
# Output: Version 19.27.0.0.0
    


    
Filesystem Usage After Patch

    
/dev/mapper/ol-root   50G   39G   12G  77% /

  


  

  

    
👨‍💻 Check Patch and Environment

    
. oraenv
ORACLE_SID = +ASM
export PATH=$ORACLE_HOME/OPatch:$PATH



[root@srv1 grid]# su - grid
Last login: Mon Jun 16 13:46:05 +04 2025 on pts/2



[grid@srv1 ~]$ cat /etc/oratab
#Backup file is  /u01/app/grid/crsdata/srv1/output/oratab.bak.srv1.grid line added by Agent
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
oradb:/u01/app/oracle/product/19.0.0/db_1:N             # line added by Agent
doradb:/u01/app/oracle/product/19.0.0/db_1:N            # line added by Agent
+ASM:/u01/app/19.0.0/grid:N             # line added by Agent

[grid@srv1 ~]$ . oraenv

ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/grid





[grid@srv1 ~]$ crsctl query has releaseversion

Oracle High Availability Services release version on the local node is [19.0.0.0.0]
[grid@srv1 ~]$ crsctl query has softwareversion

Oracle High Availability Services version on the local node is [19.0.0.0.0]
[grid@srv1 ~]$ crsctl query has releasepatch

Oracle Clusterware release patch level is [2119256259] and the complete list of patches [36758186 37642901 37643161 37654975 37762426 ] have been applied on the local node. The release patch string is [19.27.0.0.0].
[grid@srv1 ~]$ crsctl query has softwarepatch


Oracle Clusterware patch level on node srv1 is [2119256259].


*******************************************************************************************



[grid@srv1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADISK.dg
               ONLINE  ONLINE       srv1                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       srv1                     STABLE
ora.OCRDISK.dg
               ONLINE  ONLINE       srv1                     STABLE
ora.asm
               ONLINE  ONLINE       srv1                     Started,STABLE
ora.ons
               OFFLINE OFFLINE      srv1                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       srv1                     STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       srv1                     STABLE
ora.oradb.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
ora.oradb.prodb_srvp.svc
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[grid@srv1 ~]$
Posted by at No comments:

Sunday, 15 June 2025

Oracle 19c Installation on CentOS Stream 9

Oracle 19c Installation on CentOS Stream 9

Oracle Database 19c Installation on CentOS Stream 9

1. Environment Details

OS: CentOS Stream release 9
Disk Layout: / (20G), /u01 (100G), /home (25G), /boot (960M)
Oracle Version: 19.27.0.0.0
Patch: 37642901

2. Required Packages

dnf install -y bc binutils gcc gcc-c++ glibc glibc-devel ksh \
libaio libaio-devel libX11 libXau libXi libXtst libgcc libnsl \
libstdc++ libstdc++-devel libxcb make smartmontools sysstat \
elfutils-libelf elfutils-libelf-devel libXrender libXrender-devel

3. User and Directory Setup

groupadd -g 54321 oinstall
groupadd -g 54322 dba
useradd -u 54321 -g oinstall -G dba oracle
passwd oracle

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

4. Kernel Parameters

# /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p

5. Resource Limits

# /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768

# /etc/pam.d/login
session required pam_limits.so

6. Oracle Environment Variables

# ~/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=vihaan
export PATH=$PATH:$ORACLE_HOME/bin

7. Installation and Patching

unzip LINUX.X64_193000_db_home.zip -d $ORACLE_HOME
mv OPatch OPatch-backup
unzip -qqd $ORACLE_HOME p6880880_190000_Linux-x86-64.zip
export PATH=$ORACLE_HOME/OPatch:$PATH

./opatch version
# Output: 12.2.0.1.46

export CV_ASSUME_DISTID=OL8
./runInstaller -applyRU /home/oracle/37642901

# Ignore compat-libcap1-1.10 check when prompted

8. Post-Installation


???
$ORACLE_HOME/bin/relink as_installed

# Verify patches
./opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch  37642901 : applied on Sat Jun 14 10:33:37 BST 2025
Patch  29585399 : applied on Thu Apr 18 08:21:33 BST 2019

9. Database Startup

sqlplus / as sysdba

SQL> Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0




SQL> alter pluggable database VIHAAN27 open ;

Warning: PDB altered with errors.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  YES
         3 VIHAAN27                       READ WRITE YES

=====================


steps to solve this 
Execute datapatch individually in each of the containers, starting with CDB$ROOT :


1. Apply datapatch is CDB$ROOT :
  ===================================
  ./datapatch -verbose -pdbs CDB\$ROOT

2. Apply datapatch is PDB :
  ===================================
  $ ./datapatch -verbose -pdbs 

3. Apply datapatch is PDB$SEED :
  ==================================
  SQL> alter session set container=PDB$SEED;
  SQL> alter session set "_oracle_script"=TRUE;
  SQL> alter pluggable database pdb$seed close immediate instances=all;
  SQL> alter pluggable database pdb$seed OPEN READ WRITE;
  SQL> select open_mode from v$database;
  SQL> exit

  Execute: ./datapatch -verbose -pdbs PDB\$SEED

  SQL> alter session set "_oracle_script"=FALSE;
  
4. show pdbs -> If this shows the PDB$SEED in restricted mode, bounce the database once.

5. Check for the relevant patch existence in the registry$sqlpatch for all the containers (CDB$ROOT, PDB$SEED and PDB) :

  set linesize 150
  col logfile for a90
  select patch_id, action, logfile, status from registry$sqlpatch;


============================================


alter session set container=CDB$ROOT;



 set linesize 200
 column name format a10
 column cause format a10
 column type format a10
 column message format a95
 column action format a30
col TIME for a30	 
 select TIME,name, cause, type, message,status from PDB_PLUG_IN_VIOLATIONS where status = 'PENDING';

TIME                           NAME       CAUSE      TYPE       MESSAGE                                                                                         STATUS
------------------------------ ---------- ---------- ---------- ----------------------------------------------------------------------------------------------- ---------
25-JUN-25 10.37.19.783536      VIHAAN27   SQL Patch  ERROR      19.27.0.0.0 Release_Update 2504061311: APPLY with status WITH ERRORS in the PDB                 PENDING
25-JUN-25 10.50.00.392479      PDB$SEED   SQL Patch  ERROR      19.27.0.0.0 Release_Update 2504061311: APPLY with status WITH ERRORS in the PDB                 PENDING






QL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  YES
         3 VIHAAN27                       READ WRITE YES


[root@ora19 oracle]# updatedb
[root@ora19 oracle]# locate datapatch
/u01/app/oracle/product/19.0.0/dbhome_1/OPatch/datapatch



./datapatch -verbose -pdbs CDB\$ROOT
SQL Patching tool version 19.27.0.0.0 Production on Wed Jun 25 10:48:28 2025
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4538_2025_06_25_10_48_28/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.27.0.0.0 Release_Update 250406131139: Installed
  PDB CDB$ROOT:
    Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 14-JUN-25 11.00.09.198524

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

SQL Patching tool complete on Wed Jun 25 10:48:40 2025

[oracle@ora19 OPatch]$ sqlme

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 25 10:49:17 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

Warning: PDB altered with errors.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter session set container=PDB$SEED;

Session altered.

@?/rdbms/admin/utlrp.sql remotely - Oracle Forums

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


 ./datapatch -verbose -pdbs PDB\$SEED
SQL Patching tool version 19.27.0.0.0 Production on Wed Jun 25 11:02:59 2025
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_5662_2025_06_25_11_02_59/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.27.0.0.0 Release_Update 250406131139: Installed
  PDB PDB$SEED:
    Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 25-JUN-25 10.57.08.184703

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: PDB$SEED
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied

SQL Patching tool complete on Wed Jun 25 11:03:11 2025



SQL> alter session set container=VIHAAN27 ;

Session altered.




SQL> alter session set container=VIHAAN27 ;

Session altered.

SQL>  @?/rdbms/admin/utlrp.sql

Session altered.



 ./datapatch -verbose -pdbs VIHAAN27
SQL Patching tool version 19.27.0.0.0 Production on Wed Jun 25 11:05:23 2025
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_6098_2025_06_25_11_05_23/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.27.0.0.0 Release_Update 250406131139: Installed
  PDB VIHAAN27:
    Applied 19.27.0.0.0 Release_Update 250406131139 with errors on 14-JUN-25 11.05.28.279212

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: VIHAAN27
    No interim patches need to be rolled back
    Patch 37642901 (Database Release Update : 19.27.0.0.250415 (37642901)):
      Apply from 19.1.0.0.0 Feature Release to 19.27.0.0.0 Release_Update 250406131139
    No interim patches need to be applied


WARNING: Following components are NOT in a valid state.
         This could cause patching failure. If it does, consider
         running utlrp.sql to bring components to VALID state.
         Then, re-run datapatch.

         VIHAAN27 : SDO [INVALID]

Installing patches...


Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 37642901 apply (pdb VIHAAN27): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_VIHAAN_VIHAAN27_2025Jun25_11_05_36.log (no errors)
SQL Patching tool complete on Wed Jun 25 11:12:50 2025



  set linesize 200
  col logfile for a90
  select patch_id, action, logfile, status from registry$sqlpatch;


 PATCH_ID ACTION                         LOGFILE                                                                                    STATUS
---------- ------------------------------ ------------------------------------------------------------------------------------------ -------------------------
  37642901 APPLY                          /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_VIHAAN_CDBROOT_2025J SUCCESS
                                          un14_10_47_31.log


SQL> alter pluggable database VIHAAN27 open ;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 VIHAAN27                       READ WRITE NO
SQL>



VIHAAN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = vihaan)
    )
  )

LISTENER_VIHAAN =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora19.lan)(PORT = 1521))



[oracle@ora19 admin]$ lsnrctl service|grep -i vihaan27
Service "vihaan27" has 1 instance(s).



VIHAAN27 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VIHAAN27)
    )
  )
  
  
  
VIHAAN27 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VIHAAN27)
    )
  )
  
  
  
  
[oracle@ora19 admin]$ tnsping VIHAAN27

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 25-JUN-2025 11:25:41

Copyright (c) 1997, 2025, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19.lan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = VIHAAN27)))
OK (20 msec)



  sqlplus 'sys/sys@VIHAAN27 as sysdba'
  
  
  sqlplus 'sys/sys@VIHAAN27 as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 25 11:27:35 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 VIHAAN27                       READ WRITE NO
SQL>














     
Requirements for Installing Oracle Database/Client 19c (19.22 or higher) on OL9 or RHEL9 64-bit (x86-64) 
(Doc ID 2982833.1)

bc
binutils
compat-openssl11
elfutils-libelf
fontconfig
glibc
glibc-devel
ksh
libaio
libasan
liblsan
libX11
libXau
libXi
libXrender
libXtst
libxcrypt-compat
libgcc
libibverbs
libnsl
librdmacm
libstdc++
libxcb
libvirt-libs
make
policycoreutils
policycoreutils-python-utils
smartmontools
sysstat

Note - For 64-Bit Oracle Database Client Installs,below Packages are required
libnsl2
libnsl2-devel

©

Posted by at No comments:

Tuesday, 6 May 2025

Oracle Sql_id Stale statistics


Oracle Sql_id Stale statistics

SQL  stale optimizer statistics




define sql_id='5u4sk8mw75xxx'

set pagesize 100
set linesize 150
set trims off
set tab off
set verify off
column table_name format a50
column index_name format a50
column object_type format a40
column owner format a15


PROMPT ==========
PROMPT Tables
PROMPT ==========
with plan_tables as (
select distinct object_name,object_owner, object_type 
from v$sql_plan 
where object_type like 'TABLE%' 
and   sql_id      = '&sql_id')
select t.object_owner owner,
       t.object_name table_name,
       t.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness   
from   dba_tab_statistics s,
       plan_tables        t
where  s.table_name = t.object_name
and    s.owner      = t.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by t.object_owner, t.object_name;

PROMPT ==========
PROMPT Indexes
PROMPT ==========
with plan_indexes as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'INDEX%'
and   sql_id      = '&sql_id')
select i.object_owner owner,
       i.object_name index_name,
       i.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from   dba_ind_statistics s,
       plan_indexes       i
where  s.index_name = i.object_name
and    s.owner      = i.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by i.object_owner, i.object_name;



from ..
https://github.com/jkstill/oracle-script-lib/blob/master/sql/stats-sqlid.sql




ttitle off
btitle off

define 1='5u4sk8mw75xxx'
define 2='Y'
col s_diag_pack new_value s_diag_pack noprint
col s_sql_id new_value s_sql_id noprint
var v_sql_id varchar2(13)



set feed off term off 
select '&1' s_sql_id from dual;

set term on

whenever sqlerror exit 128

begin 
	:v_sql_id := '&s_sql_id';
	if 
		length(:v_sql_id) < 1
		or 
		:v_sql_id is null
	then
		raise value_error;
	end if;
end;
/

whenever sqlerror continue
set feed on


define 2='Y'

set feed off term off 
select decode(upper('&2'),'Y','','--') s_diag_pack from dual;
set feed on term on


set pagesize 100 linesize 300 trimspool on  heading on

col partition_start format a6 head 'PSTART'
col sql_id format a13
col partition_stop format a6 head 'PSTOP'
col owner format a20
col table_name format a30
col index_name format a40
col phv format a60 wrap
col last_analyzed format a19
col stale_stats format a12 head 'stale_stats'
col num_rows format 99,999,999,999
col blocks format 9,99,999,999
col partition_position format 999999 head 'PP'
col TABLE_NAME  for a27
break on sql_id skip 1

--spool stats-sqlid.txt

with objects as (
	-- extra inline view is to eliminate duplicates in listagg()
	select 
		sql_id
		, listagg(phv,',') within group(order by phv)  phv
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
	from (
		select distinct
			sql_id
			, phv
			, object_owner
			, object_name
			, object_type
			, partition_start
			, partition_stop
		from (
			select 
				sql_id
				, plan_hash_value phv
				, object_owner
				, object_name
				, object_type
				, case partition_start
					when 'ROW LOCATION' then 'ROWID'
					else partition_start
				end partition_start
				, case partition_stop
					when 'ROW LOCATION' then 'ROWID'
					else partition_stop
				end partition_stop
			from v$sql_plan
			where sql_id = :v_sql_id
			and object_owner is not null
			and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
			&s_diag_pack union all
			&s_diag_pack select 
				&s_diag_pack sql_id
				&s_diag_pack , plan_hash_value phv
				&s_diag_pack , object_owner
				&s_diag_pack , object_name
				&s_diag_pack , object_type
				&s_diag_pack , case partition_start
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_start
				&s_diag_pack end partition_start
				&s_diag_pack , case partition_stop
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_stop
				&s_diag_pack end partition_stop
			&s_diag_pack from dba_hist_sql_plan
			&s_diag_pack where sql_id = :v_sql_id
			&s_diag_pack and object_owner is not null
			&s_diag_pack and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
		)
	)
	group by
		sql_id
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
),
indexes as (
	select * from objects where object_type in ('INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)')
),
tables as (
	select * from objects where object_type in ('TABLE','CLUSTER','TABLE (FIXED)')
)
select 
	sql_id
	, phv
	, owner
	, table_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as table_name
	, null index_name
	, partition_position
	, t.partition_start
	, t.partition_stop
	, num_rows
	, blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_tab_statistics s
 join tables t on t.object_owner = s.owner
	and t.object_name = s.table_name
union all
select 
	sql_id
	, phv
	, owner
	, table_name 
	, index_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as index_name
	, partition_position
	, i.partition_start
	, i.partition_stop
	, num_rows
	, leaf_blocks blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_ind_statistics s
 join indexes i on i.object_owner = s.owner
	and i.object_name = s.index_name
order by sql_id
	, owner
	, table_name
	, index_name nulls first
	, partition_position nulls first
/

--spool off
Posted by at No comments:

Tuesday, 25 March 2025

segment type on tablespace 


segment type on tablespace
=============================================================================


-- segment_type_per_tbs.sql

set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from dba_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by owner, tablespace_name, segment_type
order by owner, tablespace_name, segment_type;

===================================
--- all the cdb 
set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select con_id,owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from cdb_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by  con_id,owner, tablespace_name, segment_type
order by con_id,owner, tablespace_name, segment_type;
Posted by at No comments:

Monday, 10 March 2025

ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 



idle sqlplus> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initvihcdbd8x.ora';
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





 sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:36:54 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected.
idle sqlplus> startup ;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





[oracle@rac02 dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 10 05:43:36 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01089: immediate shutdown or close in progress - no operations are permitted


**************************************************************************************************************************************************************

Before this  Cannot login to the Database after Changing $ORACLE_HOME Directory Permission ( ORA-01012 ) (Doc ID 2497191.1)


Try below !!!!!!!!!!!!!!!!


https://anuj-singh.blogspot.com/2013/12/how-to-use-prelim-option-in-sqlplus.html


 echo $ORACLE_SID
vihcdbd8x

[oracle@ibrac02 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0/dbhome_1

[oracle@rac02 dbs]$






[oracle@rac02 dbs]$ sqlplus -prelim

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:48:28 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: / as sysdba

idle sqlplus> shutdown abort ;
ORACLE instance shut down.


idle sqlplus>


pwd
/u01/app/oracle/product/12.2.0/dbhome_1



idle sqlplus> startup nomount pfile='initvihcdbd8x.ora'
ORACLE instance started.

Total System Global Area 2.2750E+10 bytes
Fixed Size                 19421832 bytes
Variable Size            3355444600 bytes
Database Buffers         1.9327E+10 bytes
Redo Buffers               47685632 bytes
idle sqlplus>
Posted by at No comments:

Friday, 7 March 2025

Oracle copy command !!!!

Oracle copy command !!!! ================================================================== 


SQL> alter session set container=PDB1 ;

Session altered.



SQL> create table dba_table1 as select * from dba_tables where 1=1 ;

Table created.



SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.




SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      4369




SQL>
SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      6554

SQL>


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;SQL>

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      8739


===


SQL> COPY FROM system/sys@//192.168.1.120:1521/pdb1.localdomain TO system/sys@//192.168.1.120:1521/pdb1.localdomain  APPEND DBA_TABLE1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
Table DBA_TABLE1 created.

   2186 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2186 rows inserted into DBA_TABLE1.
   2186 rows committed into DBA_TABLE1 at system@//192.168.1.120:1521/pdb1.localdomain.

SQL>
Posted by at No comments:

Tuesday, 25 February 2025

Find Oracle Objects ... 


Find Oracle Objects 
============================================

set pagesize 200 linesize 200
set term on feed on
--set term off feed off
col object_name         format a30
col object_type         format a30
col owner               format a15
col created             format a21
col last_ddl_time       format a21
col status              format a10


select distinct * from (select 
'D' con_id
,object_name
	,object_type
	, owner
	, status
	, to_char(created,'DD/MM/yyyy hh24:mi:ss') created
	, to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like upper('%&&uobject%')
union all
select 
to_char(con_id)
,object_name
	,object_type
	, owner
	, status
	, to_char(created,'DD/MM/yyyy hh24:mi:ss') created
	, to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time
from cdb_objects
where object_name like upper('%&&uobject%')
union all
select 'v' con_id,name object_name, type object_type, 'SYS' owner, 'FIXED' status, null created, null last_ddl_time
from v$fixed_table
where name like upper('%&&uobject%')
order by object_name
)
/
undefine uobject
Posted by at No comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive