Oracle patch Info
from web
list-ohpatches.sh
#!/bin/bash
# Fred Denis -- fred.denis3@gmail.com -- May 21st 2021
#
# Show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
#
set -o pipefail
#
# Variables
#
TS="date "+%Y-%m-%d_%H%M%S"" # A timestamp for a nice outut in a logfile
GREP="." # What we grep -- default is everything
UNGREP="nothing_to_ungrep_unless_v_option_is_used$$" # What we don't grep (grep -v) -- default is nothing
COLS=$(tput cols) # Size of the screen
ORACLE="oracle" # User to run opatch lspatches if script ran as root
#
# Cleanup on exit -- this will be executed on normal exit as well as if the script is killed
# The place to cleanup things / send emails whatever happens to the script
#
cleanup() {
err=$?
if [[ -s "${TEMP2}" ]]; then
if [[ "${err}" == "0" ]]; then # If already an error, no need to check for nb missing patches
# Check for errors
NB_ERR=$(cat "${TEMP2}" | awk '{cpt+=$1} END {print cpt}')
exit "${NB_ERR}"
fi
fi
# Delete tempfiles
rm -f "${TEMP}" "${TEMP2}"
exit ${err}
}
sig_cleanup() {
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] I have been killed !" >&2
exit 666
}
trap cleanup EXIT
trap sig_cleanup INT TERM QUIT
#
# Usage function
#
usage() {
printf "\n\033[1;37m%-8s\033[m\n" "NAME" ;
cat << END
$(basename $0) - Show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
END
printf "\n\033[1;37m%-8s\033[m\n" "SYNOPSIS" ;
cat << END
$0 [-g] [-c] [-G] [-v] [-s] [-u] [-h]
END
printf "\n\033[1;37m%-8s\033[m\n" "DESCRIPTION" ;
cat << END
$(basename $0) Based on oratab, show nice tables of the installed and/or missing patches for some GI/DB Oracle Homes
You can then quickly find a missing patch across a RAC system
$(basename $0) will by default check all the nodes of a cluster (based on olsnodes) which requires ASM to be running
and oraenv to be working with the ASM aslias defined in oratab; If you have no ASM alias in oratab,
you may suffer from https://unknowndba.blogspot.com/2019/01/lost-entries-in-oratab-after-gi-122.html
You can specify a comma separated list of host or a file containing one host per line
$(basename $0) by default checks all the homes defined in oratab, you can use --grep/--home and --ungrep/--ignore to limit your home selection (see examples below)
$(basename $0) relies on opatch lspatches which must run as oracle user (and not root); if the script is started as root,
the opatch lspatches commands will be run after su - ${ORACLE} (see -u | --oracleuser for more on this)
END
printf "\n\033[1;37m%-8s\033[m\n" "OPTIONS" ;
cat << END
-g | --groupfile ) A group file containing a list of hosts
-c | --commalist | --hosts ) A comma separated list of hosts
-G | --grep | --oh | --home ) Pattern to grep from /etc/oratab
-v | --ungrep | --ignore ) Pattern to grep -v (ignore) from /etc/oratab
-s | --showhomes | --show ) Just show the homes from oratab resolving the grep/ungrep combinations
-u | --oracleuser ) User to use to run opatch lspatches if the script is started as root, default is ${ORACLE}
-h | --help ) Shows this help
END
printf "\n\033[1;37m%-8s\033[m\n" "EXAMPLES" ;
cat << END
$0 # Analyze and show all the homes of nodes of a cluster
$0 --show # Show the homes from oratab (only show, dont do anything else)
$0 --grep grid # Analyze the grid home
$0 --grep db --ungrep 12 # Only the DB homes but not the 12 ones
$0 --grep db --ungrep 12 --groupfile ~/dbs_group # Same as above on the hosts contained in the ~/dbs_group file
$0 --home db --ignore 12 --hosts exa01,exa06 # Same as above but only on hosts exa02 and exa06
$0 --home db --ignore 12 --hosts exa01,exa06 -u oracle2 # Same as above but started as root; will then su - oracle2 automatically
END
exit 999
}
#
# Options -- Long and Short, options needs to be separa
# Options are comma separated list, options requiring a parameter need to be followed by a ":"
#
SHORT="g:,c:,g:,v:,u:,sh"
LONG="groupfile:,commalist:,hosts:,grep:,oh:,home:,ungrep:,ignore:,oracleuser:,showhomes,help"
# Check if the specified options are good
options=$(getopt -a --longoptions "${LONG}" --options "${SHORT}" -n "$0" -- "$@")
# If not, show the usage and exit
if [[ $? -ne 0 ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Invalid options provided: $*; use -h for help; cannot continue." >&2
exit 864
fi
#
eval set -- "${options}"
# Option management, not the "shift 2" when an option requires a parameter and "shift" when no parameter needed
while true; do
case "$1" in
-g | --groupfile ) GROUP="$2" ; shift 2 ;;
-c | --commalist | --hosts ) HOSTS="$2" ; shift 2 ;;
-G | --grep | --oh | --home) GREP="$2" ; shift 2 ;;
-v | --ungrep | --ignore ) UNGREP="$2" ; shift 2 ;;
-u | --oracleuser ) ORACLE="$2" ; shift 2 ;;
-s | --showhomes ) SHOW_HOMES="True" ; shift ;;
-h | --help ) usage ; shift ;;
-- ) shift ; break ;;
esac
done
#
# Different OS support
#
OS=$(uname)
case ${OS} in
SunOS)
ORATAB=/var/opt/oracle/oratab
AWK=/usr/bin/gawk ;;
Linux)
ORATAB=/etc/oratab
AWK=`which awk` ;;
HP-UX)
ORATAB=/etc/oratab
AWK=`which awk` ;;
AIX)
ORATAB=/etc/oratab
AWK=`which awk` ;;
*) echo "Unsupported OS, cannot continue."
exit 666 ;;
esac
#
# Options verifications
#
if [[ $(id -u) -eq 0 ]]; then # We are root
su - "${ORACLE}" -c id > /dev/null 2>&1 # Check if we can su as ${ORACLE}
if [ $? -ne 0 ]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Script has been executed as root and the user to use to run opatch lspatches is ${ORACLE}; unfortunately we were unable to connect to this user; cannot continue." >&2
exit 122
fi
fi
if [[ ! -f "${ORATAB}" ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${ORATAB}; cannot continue." >&2
exit 123
fi
if [[ ! -f "${AWK}" ]]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find a modern versin of awk; cannot continue." >&2
exit 124
fi
#
# Show Homes only if -s option specified
#
if [[ "${SHOW_HOMES}" == "True" ]]; then
printf "\n\033[1;37m%-8s\033[m\n\n" "ORACLE_HOMEs in ${ORATAB}:" ;
cat ${ORATAB} | grep -v "^#" | grep -v "^$" | grep -v agent | ${AWK} 'BEGIN {FS=":"} { printf("\t%s\n", $2)}' | grep ${GREP} | grep -v ${UNGREP} | sort | uniq
printf "\n"
exit 0
fi
if [[ -z "${HOSTS}" ]]; then # HOSTS is empty, -c option not provided
if [[ -f "${GROUP}" ]]; then # Group file exists, we make it a comma separated list
HOSTS=$(cat "${GROUP}" | grep -v "^$" | grep -v "^#" | ${AWK} '{printf("%s,", $1)}' | sed s'/,$//')
else # No group file nor hosts lists, lets get the node list from olsnodes
. oraenv <<< $(ps -ef | grep pmon | grep asm | awk '{print $NF}' | sed s'/.*+/+/') > /dev/null 2>&1
if [ $? -ne 0 ]; then
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] ASM does not seem to be running and/or oraenv not working so we cannot guess the node list; please use -g or -c to specify a node list then and restart." >&2
exit 125
else # ASM env all set, lets get the nodes list automatically
HOSTS=$($(which olsnodes) | ${AWK} '{printf ("%s,",$1)}' | sed s'/,$//')
fi
fi
fi
#
#
#
TEMP2=$(mktemp)
printf "\033[1;36m%s\033[m\n" "$($TS) [INFO] Starting collecting GI/OH patch information"
printf "\033[1;33m%s\033[m\n" "$($TS) [WARNING] It may be a bit slow if you have many nodes and patches as opatch lspatches is slow"
for OH in $(cat ${ORATAB} | grep -v "^#" | grep -v "^$" | grep -v agent | grep ${GREP} | grep -v ${UNGREP} | awk 'BEGIN {FS=":"} {print $2}'| sort | uniq); do
if [[ -f "${OH}/OPatch/opatch" ]] && [[ -x "${OH}/OPatch/opatch" ]]; then
TEMP=$(mktemp)
[[ $(id -u) -eq 0 ]] && chmod 777 "${TEMP}"
printf "\033[1;36m%s\033[m\n" "$($TS) [INFO] Proceeding with ${OH} . . ."
for HOST in $(echo ${HOSTS} | sed 's/,/ /g'); do
if [[ $(id -u) -eq 0 ]]; then # Script started as root, need to sudo as oracle for opatch lspatches
su - "${ORACLE}" << END
ssh -q "${HOST}" "${OH}/OPatch/opatch lspatches" | grep "^[1-9]" | sort | awk -v H="${HOST}" -F ";" '{print H";"\$1";"\$2}' | sed 's/(.*)//g' >> "${TEMP}"
END
else
ssh -q "${HOST}" "${OH}/OPatch/opatch lspatches" | grep "^[1-9]" | sort | awk -v H="${HOST}" -F ";" '{print H";"$1";"$2}' | sed 's/(.*)//g' >> "${TEMP}"
fi
done
"${AWK}" -v hosts="${HOSTS}" -v cols="${COLS}" -v tempfile="${TEMP2}" \
'BEGIN { FS = ";" ;
# some colors
COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = "31m" ;
GREEN = "32m" ;
YELLOW = "33m" ;
BLUE = "34m" ;
TEAL = "36m" ;
WHITE = "37m" ;
MISSING = "Missing" ; # Patch is missing
HERE = "x" ; # Patch is installed
# Default columns size
COL_NODE = 8 ;
COL_PATCH = 6 ;
COL_DESCR = 10 ;
cols = cols -5 ; # Screen size, dont want to be too short
nb_missing = 0 ; # Number of missing patches
split(hosts, tab_hosts, ",") ; # An array with the hosts; n is number of hosts
n = asort(tab_hosts) ; # Sort by hostname
for (x in tab_hosts){
if (length(tab_hosts[x]) > COL_NODE) {COL_NODE = length(tab_hosts[x]) + 2}
}
}
#
# A function to center the outputs with colors
#
function center( str, col_size, color, sep) { right = int((col_size - length(str)) / 2) ;
left = col_size - length(str) - right ;
return sprintf(COLOR_BEGIN color "%" left "s%s%" right "s" COLOR_END sep, "", str, "" ) ;
}
#
# A function that just print a "---" white line
#
function print_a_line(size){
printf("%s", COLOR_BEGIN WHITE) ;
for (k=1; k<=size; k++) {printf("%s", "-");} ; # n = number of nodes
printf("%s", COLOR_END"\n") ;
}
{ # Save all the patches list
if ($2 in all_patches){ cpt++ ;
} else {
all_patches[$2] = $3 ;
if (length($2) > COL_PATCH) {COL_PATCH = length($2) + 2}
if (length($3) > COL_DESCR) {COL_DESCR = length($3) + 1}
}
# Save all the patches per node
tab_patches[$1][$2] = $2 ;
}
END {
# To make it fit and nice depending on the screen size
out_size=(COL_PATCH+n*COL_NODE+COL_DESCR+n+2) ;
if (out_size > cols) {
COL_DESCR = COL_DESCR - (out_size - cols) ;
out_size = cols ;
}
# Header
print_a_line(out_size) ;
printf("%-"COL_PATCH"s|", " Patch id") ;
for (i=1; i<=n; i++){ # Each node
printf("%s", center(tab_hosts[i], COL_NODE, WHITE, "|")) ;
}
printf(" %-"COL_DESCR"s", "Patch description") ;
printf("\n") ;
print_a_line(out_size) ;
y = asorti(all_patches, all_patches_sorted)
for (j=1; j<=y; j++){
patch_id = all_patches_sorted[j] ;
printf("%s", center(patch_id, COL_PATCH, WHITE, "|")) ;
for (i=1; i<=n; i++){ # Each node
if (length(tab_patches[tab_hosts[i]][patch_id]) > 0){
printf("%s", center(HERE , COL_NODE, GREEN, "|")) ;
} else {
printf("%s", center(MISSING, COL_NODE, RED , "|")) ;
nb_missing++ ;
}
}
printf(" %-"COL_DESCR"s", substr(all_patches[patch_id], 1, COL_DESCR)) ;
printf("\n") ;
}
# Footer
print_a_line(out_size) ;
print nb_missing >> tempfile ;
}' "${TEMP}"
rm -f "${TEMP}"
else
printf "\033[1;31m%s\033[m\n" "$($TS) [ERROR] Cannot find ${OH}/OPatch/opatch; will skip ${OH}" >&2
fi
done
./list-ohpatches.sh
2023-09-29_125128 [INFO] Starting collecting GI/OH patch information
2023-09-29_125128 [WARNING] It may be a bit slow if you have many nodes and patches as opatch lspatches is slow
2023-09-29_125128 [INFO] Proceeding with /u01/app/19.0.0/grid . . .
Friday, 29 September 2023
Oracle patch Info
Thursday, 14 September 2023
Oracle spfile backup and restore
to find spfile !!! asmcmd find --type PARAMETERFILE +DATA1 "*" asmcmd find --type PARAMETERFILE '*' '*' +DATA1/ORCL/PARAMETERFILE/spfile.263.1147405219 +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
===
or !!
spfile info !!!
SET PAGESIZE 9999 linesize 500
SET VERIFY off
COLUMN full_alias_path FORMAT a63 HEAD 'File Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
compute sum label "" of bytes space on disk_group_name
compute sum label "Grand Total: " of bytes space on report
SELECT
CONCAT('+' || disk_group_name, SYS_CONNECT_BY_PATH(alias_name, '/')) full_alias_path
, bytes
, space
, NVL(LPAD(type, 18), '<DIRECTORY>') type
, creation_date
, disk_group_name
, LPAD(system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
)
WHERE type IS NOT NULL
and type like '%PARAMETERFILE%'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
/
System File Name Bytes Space File Type Creation Date Created? --------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- -------- +DATA/GARBAGE/PARAMETERFILE/spfile.269.966963741 4,608 1,048,576 PARAMETERFILE 01-FEB-2018 17:02:20 Y
===
asmcmd ls -ltr +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
WARNING: option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE SEP 13 07:00:00 Y spfile.271.1147416877
asmcmd spbackup +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877 /home/grid/spfile_ORCL.bak
error !!!!!!!!!!!!!!!!!!
asmcmd spbackup +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877 /home/grid/spfile_ORCL.bak
ORA-15056: additional error message
ORA-06512: at line 7
ORA-17503: ksfdopn:2 Failed to open file +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
ORA-15309: could not access database SPFILE in ASM instance
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 635
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
asmcmd -p
srvctl start database -d ORCL
srvctl config database -d ORCL
Database unique name: ORCL
Database name:
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_3
Oracle user: oracle
Spfile: +DATA1/ORCL/PARAMETERFILE/spfile.271.1147416877
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA1
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: ORCL1,ORCL2
Configured nodes: 533853-oralab4,533854-oralab5
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
run {
allocate channel ch1 device type disk;
backup spfile;
}
backup spfile format '/home/oracle/spfile_%t_s%s_s%p_spfile' ;
RMAN>
Starting backup at 14-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-SEP-23
channel ORA_DISK_1: finished piece 1 at 14-SEP-23
piece handle=/home/oracle/spfile_1147492136_s2_s1_spfile tag=TAG20230914T034856 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-SEP-23
Starting Control File and SPFILE Autobackup at 14-SEP-23
piece handle=+DATA1/ORCL/AUTOBACKUP/2023_09_14/s_1147492137.264.1147492139 comment=NONE
Finished Control File and SPFILE Autobackup at 14-SEP-23
RMAN>
run {
backup spfile format '/home/grid/spfile_%t_s%s_s%p_spfile' ;
backup spfile;
}
RMAN> SELECT DBID FROM V$DATABASE;
DBID
----------
1674589424
RUN{
set dbid=1674589424;
RESTORE SPFILE TO PFILE '/home/oracle/spfile_1147492136_s2_s1_spfile.txt' FROM '/home/oracle/spfile_1147492136_s2_s1_spfile';
}
RMAN> RESTORE SPFILE TO PFILE '/home/oracle/spfile_1147492136_s2_s1_spfile.txt' FROM '/home/oracle/spfile_1147492136_s2_s1_spfile';
Starting restore at 14-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/spfile_1147492136_s2_s1_spfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-SEP-23
RMAN>
ls -ltr /home/oracle/spfile_1147492136_s2_s1_spfile.txt
-rw-r--r--. 1 oracle asmadmin 1707 Sep 14 03:54 /home/oracle/spfile_1147492136_s2_s1_spfile.txt
Wednesday, 6 September 2023
Tuesday, 5 September 2023
DBCA Silent Mode 19c
create 19c database
for file system
-storageType FS
-datafileDestination /u01/db_files
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
export Oracle home and sid
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -characterSet AL32UTF8 -sysPassword xxxxx -systemPassword xxxxx -createAsContainerDatabase false -totalMemory 6000 -storageType ASM -datafileDestination '+DATA' -emConfiguration NONE -ignorePreReqs -sampleSchema true
for RAC
./dbca -silent -nodelist ora1,ora2 -createDatabase -templateName General_Purpose.dbc -gdbName ORCL -sid ORCL -SysPassword test123 -SystemPassword test123 -emConfiguration none -redoLogFileSize 1024 -recoveryAreaDestination '+DATA1' -storageType ASM -asmsnmpPassword welcome1 -diskGroupName '+DATA1' -recoveryGroupName '+DATA1' -listeners LISTENER -registerWithDirService false -characterSet WE8ISO8859P9 -nationalCharacterSet AL16UTF16 -databaseType MULTIPURPOSE -automaticMemoryManagement true -totalMemory 60480 -sampleSchema true &
Monday, 4 September 2023
How to change retention of securefile Lob segment
How to change retention of securefile Lob segment How to change retention of securefile Lob segment (Doc ID 2175438.1) we can only specify RETENTION parameter For SECUREFILE LOBs. Also note that you can specify either PCTVERSION or RETENTION for BASICFILE LOBs, but not both. show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> CREATE test.TABLE cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ; Table created. create table test.cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ; Table created. set linesize 300 col owner for a20 col TABLE_NAME for a20 col COLUMN_NAME for a20 col RETENTION_TYPE for a15 select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT'; OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTI RETENTION_VALUE -------------------- -------------------- -------------------- ---------- ---------- --- ------- --------------- TEST CUST_INT C_LOB YES DEFAULT alter system set undo_retention=1500; [-PRIMARY-]sys@ORCLD> alter system set undo_retention=1500; System altered. [-PRIMARY-]sys@ORCLD> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 1500 <<< UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention undo_tablespace string UNDOTBS1 [-PRIMARY-]sys@ORCLD> alter table
