Oracle January Patch 19 2023

  

Oracle January Patch 19 2023

Oracle Critical Patch Update on 19c - January 2023

Oracle Critical Patch Update (CPU)

Patch 34773504 - Combo of OJVM Component Release Update 19.18.0.0.230117 + Grid Infrastructure Jan 2023 Release Update 19.18.0.0.230117

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

Execute the following commands:

STEP 1

Patch 34762026 - GI Release Update 19.18.0.0.230117 (REL-JAN230131)

1)

NOTE: IMPORTANT

GI Home:

You need to update first opatch to the higer or letest version.

You can download letest version from the following link.

For JAN CPU use latest OPatch Version "OPatch 12.2.0.1.36 for DB 19.0.0.0.0 (Jan 2023)". 

Download Latest OPatch from "Download Latest OPatch"

Latest OPatch copy files.

p6880880_190000_Linux-x86-64.zip

cd $GRID_HOME

mv OPatch OPatch_old

cd $DOWNLOAD_PATCH/OPatch

cp -r OPatch $GRID_HOME

You Need to do the same on ORACLE_HOME

Oracle ORACLE_HOME

Latest OPatch copy files

cd $ORACLE_HOME

mv OPatch OPatch_old

cd $DOWNLOAD_HOME/OPatch

cp -r OPatch $ORACLE_HOME

Unzip January patch file downloaded.

p34762026_190000_Linux-x86-64.zip

Check the conflict:

From Grid_Home

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir  $DOWNLOAD_PATCH/34762026/34765931

Oracle Interim Patch Installer version 12.2.0.1.29
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u02/oracle/app/home/product/19.0.0/grid_19
Central Inventory :$ORACLE_BASE/app/oraInventory
   from           :$GRID_HOME/oraInst.loc
OPatch version    : 12.2.0.1.29
OUI version       : 12.2.0.1.4
Log file location : $GRID_HOME/cfgtoollogs//opatch/opatchxxxx

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

I am not going to add  output of all commands. The output should looks like as above.

$DOWNLOAD_PATCH is a directory where the patch is located.

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/34768559
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/34768569

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/34863894

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/33575402

For Database home, as home user

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/34765931
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir $DOWNLOAD_PATCH/34762026/34768559

2)

Shut down all instances and listeners associated with the Oracle home that you are updating.

Stop a PDB from Oracle home:

alter pluggable database KARDBPDB close immediate;

srvctl stop database -d KARDBTST

srvctl status database -d KARDBTST

Database is not running.

Stop Listener from GI Home

srvctl stop  listener -l LISTENER

srvctl status listener -l LISTENER

Listener LISTENER is enabled

Listener LISTENER is not running

Stop ASM

srvctl stop asm -f

srvctl status asm

ASM is not running.

3)

As root user execute the following command.

You have following options

 i) To patch only the GI Home

 <GI_HOME>/OPatch/opatchauto apply <UNZIPPED_PATCH_LOCATION>/34762026 -  oh <GI_HOME>

ii) To patch oracle_home

  opatchauto apply <UNZIPPED_PATCH_LOCATION>/34762026 -oh <oracle_home1_path>

 iii) Patching Oracle Home and Grid Home togather:

 As root user, execute the following command on each node of the cluster:

 # <GI_HOME>/OPatch/opatchauto apply <UNZIPPED_PATCH_LOCATION>/34762026

I am going to apply the path for both grid home and oracle home together.

NOTE: make sure that the Clusterware is running:

crsctl status has

If it is not running, you need to start it as follow.

crsctl start has

export PATH=$PATH:$GRID_HOME/OPatch
$GRID_HOME/OPatch/opatchauto apply $DOWNLOAD_PATCH/34762026

Post-Installation Instructions

For the both Single/Multitenant (CDB/PDB) DB required following steps.

sqlplus / as sysdba

startup

alter pluggable database all open;

quit

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Any databases that have invalid objects need to execute utlrp.sql run

For example:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

-- You can check valid or invalid objects with the following command.

set pagesize 250;

col STATUS format a20;

col COMP_NAME format a35;

select COMP_NAME, version, status from dba_registry;

STEP 2

Patch 34786990 - Oracle JavaVM Component Release Update 19.18.0.0.230117

For a Non Oracle RAC environment, shut down all databases and listeners associated with the Oracle home that you are updating.

From Oracle_home.

Normally no need to stop ASM. You need to just stop the DB and Listener

alter pluggable database KARDBPDB close immediate;

srvctl stop database -d KARDBTST

srvctl stop  listener -l LISTENER

export PATH=$PATH:/usr/ccs/bin

#$ORACLE_HOME/OPatch/opatch apply $DOWNLOAD_PATCH/34786990

Post installation Steps.

startup

alter pluggable database all open;

exit

cd $ORACLE_HOME/OPatch

./datapatch -verbose

sqlplus / as sysdba

cd $ORACLE_HOME/rdbms/admin

SQL> @utlrp.sql

$ORACLE_HOME/OPatch/opatch lspatches |sort

==== DONE ====

Verify the patch Version and Status of the patch:

col ID format a5

col COMMENTS format a20

col VERSION format a12

col BUNDLE format a5

col ACTION_TIME format a30

col ACTION format a10

set linesize 150

select substr(action_time,1,30) action_time, substr(id,1,8) id, substr(action,1,10) action, substr(version,1,8) version, substr(comments,1,20) comments from sys.registry$history ;

set pagesize 20;

set linesize 200;

col ACTION_TIME format a30;

col DESCRIPTION format a50;

select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION,STATUS from registry$sqlpatch order by ACTION_TIME ;

select PATCH_ID,DESCRIPTION,STATUS from registry$sqlpatch order by ACTION_TIME ;

Enjoy !

Your comments are most valuable and it would help me to come up with better posts. 

 VNC Server Installation Steps

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

yum groupinstall "Server with GUI"
unalias shutdown
shutdown -r now
yum install tigervnc-server xorg-x11-fonts-Type1
yum install xorg-x11-xauth xorg-x11-fonts-* xorg-x11-utils

vncpasswd

pwd
/root
cd .vnc/

The File config file looks like as follow and no need to change.

vi .vnc/config

# securitytypes=vncauth,tlsvnc
# desktop=sandbox
# geometry=2000x1200
# 1920x1200
# localhost

# alwaysshared

VNC start stop script

Create a VNC start script as follow. 

more startVNC_55
vncserver -kill :55
sleep 5

vncserver :55 -geometry 1920x1200

./startVNC_55 &

ps -ef|grep vnc

MobaX

hostname=vm-adm-vnc-th.com

port=5955

For DISPLAY

NOTE: check following setting 

vi /etc/ssh/sshd_config
Uncheck and set to no
X11UseLocalhost no

service sshd restart

xhost +

or

xhost

ssh username@hostname -X

Test with the command

xclock

Your comments are most valuable and it would help me to come up with better posts. 

PLS-00302:component

 PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

ORA-06550: line 1, column 7:

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

SQL> exec dbms_stats.unlock_table_stats( ownname => 'HR' , tabname => 'EMPLOYEES');
BEGIN dbms_stats.unlock_table_stats( ownname => 'HR' , tabname => 'EMPLOYEES'); END;

                 *
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Solution:

exec sys.DBMS_STATS.UNLOCK_TABLE_STATS( ownname => 'HR' , tabname => 'EMPLOYEES');

Your comments are most valuable and it would help me to come up with better posts. 

Oracle create private public synonym command

How to create synonym in Oracle 

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.  

Private Synonym:

1. Login as scheman user under where should create synonym

2.  schemaname is equal The Schema name for example. 

     TEST01

     TEST02

The user schema TEST01 should get synonym from all tables unter the schema TEST02 located.

Login as  TEST01

sqlplus TEST01

set echo off
set verify off
set pagesize 0
set trimspool on
set headsep off
set linesize 100
set heading off
set feedback off
set timing off
set time off
set termout off
set headsep off
spool create_private_synonom.sql
SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects
WHERE owner = 'TEST02'
AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');
spool off

Verify the script before you execute. Examples line

...
create synonym EMPLOY_HR for TEST02.EMPLOY_HR ;

...

Execute the script as follow.

sqlplus TEST01

SQL> @create_private_synonom.sql

Or use simple different way.

set echo off

set feedback off; 

set heading off; 

set term off

set pagesize 1000

set linesize 100

SET PAGES 0

spool create_synonom_table.sql

select 'create synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'  FROM all_tables WHERE owner='TEST02';

spool off

select 'create synonym ' ||view_name || ' for ' || owner || '.' || view_name || ';'  FROM all_views WHERE owner='TEST02';

Public Synonym:

For Public synonym we need to just add the keyword public.

SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects

WHERE owner = 'TEST02'

AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');

Your comments are most valuable and it would help me to come up with better posts.

 

How to Share NFS Mount Step by Step

Mounting the NFS Share

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

NOTE:  Following guide will show you how to Mount the NFS Share

• Source server  [your Server Name:test-db-01]

    ssh test-db-01
    vi /etc/exports
    /oracle/rman/backup test-db-02(rw,sync) test-db-03(rw,sync)

    /oracle/rman/backup/log jumphost-01(ro,sync,nohide,crossmnt)

           

         Execute following command 

     

    service nfs restart

    exportfs -ra

    

• Destination Server [test-db-02, test-db-03,jumphost-01]

 login to the destination server where you want to mount the filesystem. In our case are   the above listed server.

          

     ssh test-db-02  and ssh test-db-03

     mkdir -p /oracle/rman/backup

     chown -R oracle:dba /backup

     mount -t nfs test-db-01:/oracle/rman/backup /oracle/rman/backup

           

           Same step for test-db-03:

          

          And same steps for the jumphost-01 server.

          ssh jumphost-01

     mkdir -p /oracle/rman/backup/log

     chown -R oracle:dba /backup

     mount -t nfs jumphost-   01:/oracle/rman/backup/log /oracle/rman/backup/log

    

     Add the entry on each server in the fstab file. In case of reboot the server,

          The mount will happen automatically.

     vi /etc/fstab

     test-db-01:oracle/rman/backup               oracle/rman/backup            nfs                defaults        0 0

 

Clean the flashback recovery area

free space in flash recovery area

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

 NOTE: You can remove the file on the filesystem level.

cd /fast_recovery_area/TESTDB/archivelog
ls -al 
2022_10_07/
2022_10_08/
2022_10_09/

2022_10_10/

I am going to remove all files older then 5 hours.

find /fast_recovery_area/TESTDB/archivelog/2022*  \( -mmin +360 -a -name "*.arc" \) -exec rm {} \; > /dev/null 2>&1 

Login into DB and check the parameter:

sqlplus / as sysdbaq

col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999

select
name,
ceil( space_limit / 1024 / 1024 /1024) size_gb,
ceil( space_used / 1024 / 1024 /1024) used_gb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by

name desc;

NAME                                  SIZE_GB      USED_GB  PCT_USED
-------------------------------- ------------  ------------ --------

/fast_recovery_area                     2,600        2,590       100

Increate the sieze as follow. 

NOTE: NO NEED DB RESART:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =3500G SCOPE=BOTH;

RMAN command required to make empty space show via sql command..

rman target /
crosscheck archivelog all;

delete noprompt force expired archivelog all;

Make empty all files older then 0ne day:

#delete noprompt archivelog all completed before 'sysdate - 1';

Or older the 6 hours:

delete noprompt archivelog all completed before 'sysdate - 6/24';

Now execute the above sql command again and check the space:

NAME                                  SIZE_MB      USED_MB PCT_USED
-------------------------------- ------------ ------------ --------

/fast_recovery_area                     3,500        2,590       74

You can use the follwing scrip to execute the RMAN commands.

delete_archivelog.sh

more delete_archivelog.sh

#!/bin/bash
date '+%Y-%m-%d  %H:%M:%S'
. /set_home_env TESTDB #set DB environment variables.
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
rman target / <<EOF
crosscheck archivelog all;
delete noprompt force expired archivelog all;
#delete noprompt archivelog all completed before 'sysdate - 1';
delete noprompt archivelog all completed before 'sysdate - 6/24';
EOF
date '+%Y-%m-%d  %H:%M:%S'
cd /fast_recovery_area/TESTDB/archivelog

find /fast_recovery_area/TESTDB/archivelog/* -type d -empty -print -exec rm -r {} \;

Your comments are most valuable and it would help me to come up with better posts.

Extend device using LVM

How to Extend LVM Partition with lvextend command

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 

Extent physical volume using LVM 

# Use  VCenter and give the disk.

ls -l /dev/disk/by-path/ <<== Scan the correct scsi device

echo 1 > /sys/class/scsi_device/0\:0\:3\:0/device/rescan

vgdisplay

fdisk -l

...

...

   Device Boot      Start         End      Blocks   Id  System

/dev/sdc1            2048   209715199   104856576   83  Linux

...

...

partprobe

fdisk /dev/sdc <<== With the command  another disk is recognized. Example: sdc1 or  sdc2 recognized

n              #n   add a new partition
p              #p   print the partition table
enter
enter
enter
w              #w   write table to disk and exit
partprobe

fdisk -l

...

...

   Device Boot      Start         End      Blocks   Id  System

/dev/sdc1            2048   209715199   104856576   83  Linux

/dev/sdc2       209715200   419430399   104857600   83  Linux

...

...

pvscan

PV /dev/sdc1   VG vg_data         lvm2 [<100.00 GiB / 0    free]

PV /dev/sdc2                      lvm2 [100.00 GiB]

pvcreate /dev/sdc2    <<== sdc2 is a new disk
pvscan

vgs

  VG      #PV #LV #SN Attr   VSize    VFree

  vg_data   1   1   0 wz--n- <100.00g    0

vgextend vg_data /dev/sdc2

vgs

  VG      #PV #LV #SN Attr   VSize   VFree

  vg_data   2   1   0 wz--n- 199.99g <100.00g

lvextend -l +100%FREE /dev/mapper/vg_data-lv_data

xfs_growfs /data

Done:

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

Steps for Physical Host extend LVM File system with multipath.

rescan-scsi-bus.sh -a -w -m

multipath -ll

#LUN WWID finden. You do with pipe

lsscsi -i -s 

vgdisplay

lvdisplay

vi /etc/multipath.conf  # add new LUN ID 

service multipathd reload

multipath -ll

parted /dev/mapper/tst-03DATA_03

GNU Parted 3.1

Using /dev/mapper/tst-01DATA_03

Welcome to GNU Parted! Type 'help' to view a list of commands.

(parted) mklabel gpt

(parted) mkpart tst-03DATA_03 2048s 100%

(parted) align-check opt 1

1 aligned

(parted) quit

ll /dev/mapper/

pvcreate /dev/mapper/tst-03DATA_03p1

You can use multiple PV in one command.

pvcreate /dev/mapper/xxx /dev/mapper/xxx

pvscan

vgs

vgextend vg_data /dev/mapper/tst-03DATA_03p1

You can as well as extend multiple VG as one command  

vgextend /dev/mapper/xxx /dev/mapper/xxx

lvextend --resizefs -l +100%FREE /dev/vg_data/lv_data

df -hl

Done

Your comments are most valuable and it would help me to come up with better posts.