RMAN>
run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}
RMAN> 2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 10:39:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=260 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/15/2024 10:39:14
ORA-19870: error while restoring backup piece /u01/app/Rman/c-2798080093-20240512-08
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
ORA-15046: ASM file name '+DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713' is not in single-file creation form
remove spfile !!!
srvctl modify database -d oradb -spfile ''
====
Start again !!!!
rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 12:25:59 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
RMAN> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/Rman' ;
using target database control file instead of recovery catalog
Statement processed
RMAN> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app';
Statement processed
RMAN> run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 12:27:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-MAY-2024 12:27:57
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
Starting restore at 15-MAY-2024 12:28:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: +DATADISK
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATADISK/ORADB/CONTROLFILE/current.273.1169036919
Finished restore at 15-MAY-2024 12:28:39
released channel: ORA_DISK_1
Statement processed
select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--- ---------- -------------- ----------
/u01/app/control01-1205.ctl
NO 16384 1142 0
shutdown immediate;
startup nomount;
restore controlfile to '+DATADISK' from '/u01/app/control01-1205.ctl';
RMAN> restore controlfile to '+DATADISK' from '/u01/app/control01-1205.ctl';
Starting restore at 12-MAY-2024 16:04:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 12-MAY-2024 16:04:36
RMAN>
from Grid find control file
find the control file !!!
ASMCMD> find -t controlfile . *
ASMCMD [+] > find --type controlfile . *
+DATADISK/ORADB/CONTROLFILE/current.272.1168790677
ASMCMD [+] > ls -s +DATADISK/ORADB/CONTROLFILE/current.272.1168790677
Block_Size Blocks Bytes Space Name
16384 1143 18726912 33554432 current.272.1168790677
ASMCMD [+] > ls -l +DATADISK/ORADB/CONTROLFILE/current.272.1168790677
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE MAY 12 16:00:00 Y current.272.1168790677
ASMCMD [+] >
[oracle@srv1 dbs]$ srvctl start database -d oradb -o nomount
[oracle@srv1 dbs]$ sqlplus / as sysdba
create spfile
SQL> create spfile='+DATADISK/spfileoradb.ora' from pfile='initoradb.ora';
File created.
ASMCMD [+] > ls -lt +DATADISK/spfileoradb.ora
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAY 12 16:00:00 N spfileoradb.ora => +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
ASMCMD [+] >
srvctl modify database -d oradb -spfile +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
srvctl start database -d oradb -o nomount
alter the spfile for control file
[oracle@srv1 dbs]$
alter system set control_files='+DATADISK/ORADB/CONTROLFILE/current.272.1168790677' scope=spfile;
srvctl stop database -d oradb
srvctl start database -d oradb
[oracle@srv1 dbs]$ !sql
sqlplus / as sysdba
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADISK/ORADB/PARAMETERFILE/
spfile.273.1168791713
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATADISK/ORADB/CONTROLFILE/cu
rrent.272.1168790677
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
[oracle@srv1 dbs]$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
Password file:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATADISK
Services:
OSDBA group: oinstall
OSOPER group:
Database instance: oradb
====================
[oracle@ora26 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 23.26.3.0.0 - Production on Fri Aug 14 13:34:11 2026
Version 23.26.3.0.0
Copyright (c) 1982, 2026, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1792274208 bytes
Fixed Size 5033760 bytes
Variable Size 503316480 bytes
Database Buffers 1275068416 bytes
Redo Buffers 8855552 bytes
SQL> Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Version 23.26.3.0.0
[oracle@ora26 dbs]$ rman target /
Recovery Manager: Release 23.26.3.0.0 - Production on Fri Aug 14 13:34:33 2026
Version 23.26.3.0.0
Copyright (c) 1982, 2026, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB26 (not mounted)
RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/ora26/CDB26/control01.ctl' ;
restore controlfile to '+DATA' from '/u01/app/oracle/ora26/CDB26/control01.ctl' ;
Starting restore at 14-AUG-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=382 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 14-AUG-26
RMAN>
[grid@ora26 ~]$ asmcmd -p
ASMCMD [+] > find --type CONTROLFILE +data *
+data/CDB26/CONTROLFILE/current.275.1241271299
ASMCMD [+] > ls -s +data/CDB26/CONTROLFILE/current.275.1241271299
Block_Size Blocks Bytes Space Name
16384 1151 18857984 33554432 current.275.1241271299
ASMCMD [+] > ls -l +data/CDB26/CONTROLFILE/current.275.1241271299
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE AUG 14 13:00:00 Y current.275.1241271299
ASMCMD [+] > ls -ls +data/CDB26/CONTROLFILE/current.275.1241271299
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
CONTROLFILE UNPROT FINE AUG 14 13:00:00 Y 16384 1151 18857984 33554432 current.275.1241271299
ASMCMD [+] >
alter system set control_files='+data/CDB26/CONTROLFILE/current.275.1241271299' scope=spfile;
SQL> startup ;
ORACLE instance started.
Total System Global Area 1792274208 bytes
Fixed Size 5033760 bytes
Variable Size 503316480 bytes
Database Buffers 1275068416 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database opened.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/CDB26/CONTROLFILE/curren
t.275.1241271299
control_management_pack_access string DIAGNOSTIC+TUNING
diagnostics_control string IGNORE
SQL>
Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)
-----
crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
=======
RAC: Frequently Asked Questions (RAC FAQ) (Doc ID 220970.1)
p6880880_210000_Linux-x86-64.zip
p34762026_190000_Linux-x86-64.zip >>>>>2.8GB file
unzip p34762026_190000_Linux-x86-64.zip
Archive: p34762026_190000_Linux-x86-64.zip
creating: 34762026/
inflating: 34762026/bundle.xml
creating: 34762026/34768569/
creating: 34762026/34768569/files/
[grid@srv1 ~]$ cd 34762026
[grid@srv1 34762026]$ pwd
/home/grid/34762026
[grid@srv1 34762026]$ ls -ltr
total 168
drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768569
drwxr-x---. 5 grid oinstall 81 Feb 1 2023 34765931
drwxr-x---. 4 grid oinstall 48 Feb 1 2023 33575402
-rw-r--r--. 1 grid oinstall 0 Feb 1 2023 README.txt
drwxr-x---. 2 grid oinstall 4096 Feb 1 2023 automation
drwxr-x---. 4 grid oinstall 48 Feb 1 2023 34863894
drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768559
-rw-rw-r--. 1 grid oinstall 5824 Feb 1 2023 bundle.xml
-rw-rw-r--. 1 grid oinstall 156720 Feb 1 2023 README.html
patch conflicts check.
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$
[grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail
Prereq "checkConflictAgainstOHWithDetail" passed.
[grid@srv1 34762026]$ echo $ORACLE_HOME
/u01/app/19.0.0/grid
---
stop datacases only !!!!!!!
as root to patch GI home:
[root@srv1 ~]# cd /home/grid
export ORACLE_HOME=/u01/app/19.0.0/grid
export PATH=$ORACLE_HOME/OPatch:$PATH
$ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
[root@srv1 ~]# export ORACLE_HOME=/u01/app/19.0.0/grid
[root@srv1 ~]# export PATH=$ORACLE_HOME/OPatch:$PATH
[root@srv1 ~]# echo $ORACLE_HOME
/u01/app/19.0.0/grid
[root@srv1 ~]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
opatch lsinventory | grep 19.18
[root@srv1 grid]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME
OPatchauto session is initiated at Sun May 5 09:06:22 2024
System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2024-05-05_09-06-32AM.log.
Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2024-05-05_09-06-38AM.log
The id for this session is EU5X
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_2024-05-05_09-08-01AM.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_2024-05-05_09-19-15AM.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 SUCCESSFULLY applied:
Patch: /home/grid/34762026/33575402
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34765931
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34768559
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34768569
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
Patch: /home/grid/34762026/34863894
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log
OPatchauto session completed at Sun May 5 09:21:37 2024
Time taken to complete the session 15 minutes, 6 seconds
======================
as grid user
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18
Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)"
Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)"
Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)"
32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988
[grid@srv1 ~]$
$ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18
Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)"
Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)"
Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)"
32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988
[grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch 34863894 : applied on Sun May 05 09:18:11 GST 2024
Patch 34768569 : applied on Sun May 05 09:17:40 GST 2024
Patch 34768559 : applied on Sun May 05 09:17:12 GST 2024
Patch 34765931 : applied on Sun May 05 09:12:51 GST 2024
Patch 33575402 : applied on Sun May 05 09:10:03 GST 2024
[grid@srv1 ~]$
rid@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 [3161362881] and the complete list of patches [33575402 34765931 34768559 34768569 34863894 ] have been applied on the local node. The release patch string is [19.18.0.0.0].
[grid@srv1 ~]$ crsctl query has softwarepatch
Oracle Clusterware patch level on node srv1 is [3161362881].
crsctl stat res -t – to check the status of the resources in the Oracle Restart stack managed by OHASD
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
--------------------------------------------------------------------------------
[grid@srv1 ~]$
to stop has
[grid@srv1 ~]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv1'
CRS-2673: Attempting to stop 'ora.DATADISK.dg' on 'srv1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv1' succeeded
CRS-2677: Stop of 'ora.DATADISK.dg' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.OCRDISK.dg' on 'srv1'
CRS-2677: Stop of 'ora.OCRDISK.dg' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'srv1'
CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'srv1'
CRS-2677: Stop of 'ora.evmd' on 'srv1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'srv1'
CRS-2677: Stop of 'ora.cssd' on 'srv1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[grid@srv1 ~]$
crsctl start has – to manually start the Oracle Restart stack when running disabled or after manually stopping it
crsctl stop has [-f] – to manually stop the Oracle Restart stack. The -f option
crsctl enable has – to enable the stack for automatic startup at server reboot
crsctl disable has – to disable the stack for automatic startup at server reboot
crsctl config has – to display the configuration of Oracle Restart
crsctl check has – to check the current status of Restart
Some of the crsctl commands used for clusters may also be used for Oracle Restart. A typical example follows but there are many more:
======
Patch 35940989 is a superset of patch 35642822
Note
This patch was originally replaced by patch 35940989. The most recent replacement for this patch is 36916690.
Replacement Options (Patches or Patchsets known to Include or Supersede this Patch)
35940989 GI RELEASE UPDATE 19.22.0.0.0 Patch
Oracle 19c Deinstall Utility on OL/RHEL 8 fails with "ERROR:null" (Doc ID 2738856.1)
./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/app/oracle/product/19.0.0/dbhome_1"}
[INS-04013] The argument [-deinstall] is not supported. Run /u01/app/oracle/product/19.0.0/dbhome_1/deinstall/deinstall for deinstallation.
use deinstall
on oracle 19c!!!!!!!!!!!!!!!!!
[oracle@oracledb dbhome_1]$ export CV_ASSUME_DISTID=OL7
[oracle@oracledb dbhome_1]$ ./deinstall
-bash: ./deinstall: Is a directory
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd d
data/ dbjava/ dbs/ deinstall/ demo/ diagnostics/ dmu/ drdaas/ dv/
[oracle@oracledb dbhome_1]$ cd deinstall/
[oracle@oracledb deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DECONFIG TOOL START ############
######################### DECONFIG CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /u01/app/oracle/product/19.0.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory
## [END] Install check configuration ##
Network Configuration check config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check2024-04-02_02-48-23PM.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check2024-04-02_02-48-23PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []: Database Check Configuration END
######################### DECONFIG CHECK OPERATION END #########################
####################### DECONFIG CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u01/app/oracle/product/19.0.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DECONFIG CLEAN OPERATION START ########################
Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_clean2024-04-02_02-48-23PM.log
Network Configuration clean config START
Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_clean2024-04-02_02-48-23PM.log
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
######################### DECONFIG CLEAN OPERATION END #########################
####################### DECONFIG CLEAN OPERATION SUMMARY #######################
#######################################################################
############# ORACLE DECONFIG TOOL END #############
Using properties file /tmp/deinstall2024-04-02_02-47-44PM/response/deinstall_2024-04-02_02-48-20-PM.rsp
Location of logs /u01/app/oraInventory/logs/
############ ORACLE DEINSTALL TOOL START ############
####################### DEINSTALL CHECK OPERATION SUMMARY #######################
A log of this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.out'
Any error messages from this session will be written to: '/u01/app/oraInventory/logs/deinstall_deconfig2024-04-02_02-48-20-PM.err'
######################## DEINSTALL CLEAN OPERATION START ########################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to oracledb
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2024-04-02_02-47-44PM/oraInst.loc
Setting oracle.installer.local to false
## [END] Preparing for Deinstall ##
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node : Done
The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/version/db_1'.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
## [START] Oracle install clean ##
## [END] Oracle install clean ##
######################### DEINSTALL CLEAN OPERATION END #########################
####################### DEINSTALL CLEAN OPERATION SUMMARY #######################
Successfully detached Oracle home '/u01/app/oracle/product/19.0.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/19.0.0/dbhome_1' on the local node.
Oracle Universal Installer cleanup was successful.
Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'oracledb'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL TOOL END #############
from web https://connor-mcdonald.com/2020/08/07/modifying-scheduler-windows/
set linesize 500 pagesize 400
col WINDOW_NAME for a25
col REPEAT_INTERVAL for a70
col DURATION for a25
col SCHEDULE_OWNER for a14
select SCHEDULE_OWNER ,window_name, repeat_interval, duration from dba_scheduler_windows
--where 1=1
order by WINDOW_NAME
;
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 *
9 rows selected.
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
for i in 1 .. x.count
loop
DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/
output !!!
declare
x sys.odcivarchar2list := sys.odcivarchar2list('SATURDAY');
BEGIN
SQL> SQL> 2 3 4 5 for i in 1 .. x.count
6
7 loop
8 DBMS_SCHEDULER.disable(name => 'SYS.'||x(i)||'_WINDOW', force => TRUE);
9
10 DBMS_SCHEDULER.set_attribute(
11 name => 'SYS.'||x(i)||'_WINDOW',
12 attribute => 'REPEAT_INTERVAL',
13 value => 'FREQ=WEEKLY;BYDAY='||substr(x(i),1,3)||';BYHOUR=03;BYMINUTE=0;BYSECOND=0');
DBMS_SCHEDULER.set_attribute(
name => 'SYS.'||x(i)||'_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(60, 'minute'));
DBMS_SCHEDULER.enable(name=>'SYS.'||x(i)||'_WINDOW');
end loop;
END;
/ 14 15 16 17 18 19 20 21 22 23
PL/SQL procedure successfully completed.
======================================================================
from
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 *
to
SCHEDULE_OWNER WINDOW_NAME REPEAT_INTERVAL DURATION
-------------- ------------------------- ---------------------------------------------------------------------- -------------------------
SATURDAY_WINDOW FREQ=WEEKLY;BYDAY=SAT;BYHOUR=03;BYMINUTE=0;BYSECOND=0 +000 01:00:00
How to Create a SQL Patch to add Hints to Application SQL Statements (Doc ID 1931944.1)
want use sql patch on below sql for hint select/*+ opt_param('_optimizer_extended_cursor_sharing_rel' 'none')*/
select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
and d.deptno> 10
group by d.deptno,d.dname;
set linesize 400
col sql_text for a50
select SQL_ID,SQL_TEXT from gv$sql where 1=1 and SQL_TEXT like '%and d.deptno> 10%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
fzsf6kw7q2vxt select d.deptno, d.dname, max(sal) from emp e , de
pt d where e.deptno = d.deptno and d.deptno> 10 gr
oup by d.deptno,d.dname
from web !!
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'g2z10tbxyz6b0',
name => 'validate_fk',
-- hint_text => 'ignore_optim_embedded_hints'
-- hint_text => 'parallel(a@sel$1 8)' -- worked
-- hint_text => 'parallel(8)' -- worked
-- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked
hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
);
dbms_output.put_line(v1);
end;
/
SET SERVEROUTPUT ON
DECLARE
v_sql_id VARCHAR2 (13) := '';
v_patch_name VARCHAR2 (30);
v_hint VARCHAR2 (4096) := 'NO_QUERY_TRANSFORMATION(@"SEL$1")';
BEGIN
v_patch_name :=
DBMS_SQLDIAG.create_sql_patch (sql_id => v_sql_id, hint_text => v_hint);
DBMS_OUTPUT.put_line (v_patch_name);
END;
/
===
set serveroutput on
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'fzsf6kw7q2vxt',
name => 'optimizer_extended_cursor_sharing_rel',
hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
);
dbms_output.put_line(v1);
end;
/
set linesize 400
col sql_text for a50
col name for a37
select name, status, created, sql_text from dba_sql_patches where name='optimizer_extended_cursor_sharing_rel';
set linesize 400
set numf 99999999999999999999999999
select SQL_ID,CHILD_NUMBER,SQL_PROFILE,SQL_PATCH,SQL_PLAN_BASELINE,plan_hash_value plan_hash,EXACT_MATCHING_SIGNATURE from v$sql where sql_id = 'fzsf6kw7q2vxt';
SQL_ID CHILD_NUMBER SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE PLAN_HASH EXACT_MATCHING_SIGNATURE
------------- ------------ --------------- ------------------------------------- ------------------------------------- --------------------------- ---------------------------
fzsf6kw7q2vxt 0 2006461124 9282672672555810008
fzsf6kw7q2vxt 2 optimizer_extended_cursor_sharing_rel SQL_PLAN_81npdmnqyng6s61f3d804 2006461124 9282672672555810008
col OUTLINE_HINTS for a40
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = 9282672672555810008)
) x;
OUTLINE_HINTS
----------------------------------------
opt_param('_optimizer_extended_cursor_sh
aring_rel' 'none')
If needed patch can be disabled using
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH('optimizer_extended_cursor_sharing_rel', 'STATUS', 'DISABLED');
If you want to drop the patch,
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=> 'optimizer_extended_cursor_sharing_rel');
===
Test ----
declare
v1 varchar2(128);
primary:sys@IBRAC-ibrac2 sqlplus> 2 3 begin
v1 := dbms_sqldiag.create_sql_patch(
4 5 sql_id => 'fzsf6kw7q2vxt',
6 name => 'optimizer_extended_cursor_sharing_rel',
7 hint_text => q'{opt_param('_optimizer_extended_cursor_sharing_rel' 'none')}'
8 );
9 dbms_output.put_line(v1);
10 end;
11 /
optimizer_extended_cursor_sharing_rel
PL/SQL procedure successfully completed.
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints from xmltable('/outline_data/hint' passing (select xmltype(comp_data) xml from sys.sqlobj$data where signature = 8311823694834541849)) x;
below hint for Bind mismatch(33)? and test
/*+ opt_param('_optimizer_use_feedback' 'false')
================================================
set pagesize 60
set linesize 300
set trimspool on
column sql_text format a40
column plan_name format a30
column signature format 999999999999999999999
column hint format a50 wrap word
select
prf.plan_name,
prf.sql_text,
prf.signature,
extractvalue(value(hnt),'.') hint
from (
select
so.name plan_name,
so.signature,
so.category,
so.obj_type,
so.plan_id,
st.sql_text,
sod.comp_data
from
sqlobj$ so,
sqlobj$data sod,
sql$text st
where
sod.signature = so.signature
and st.signature = so.signature
and st.signature = sod.signature
and sod.category = so.category
and sod.obj_type = so.obj_type
and sod.plan_id = so.plan_id
and so.obj_type = 3
-- and so.name = 'SQL_Patch_xxxxxxxx'
order by signature, obj_type, plan_id
) prf,
table ( select xmlsequence(
extract(xmltype(prf.comp_data),'/outline_data/hint')
)
from dual ) hnt;
https://github.com/tanelpoder/tpt-oracle/blob/master/nonshared2.sql
define 2='48vf4pg4g5510'
set linesize 200
col REASON_XML for a40
col REASON for a20
define 1=100
COL nonshared_sql_id HEAD SQL_ID FOR A13
COL nonshared_child HEAD CHILD# FOR A10
COL nonshared_reason_and_details HEAD REASON FOR A60 WORD_WRAP
COL reason_xml FOR A100 WORD_WRAP &1
col REASON for a20
BREAK ON nonshared_sql_id
SELECT
'&2' nonshared_sql_id
, EXTRACTVALUE(VALUE(xs), '/ChildNode/ChildNumber') nonshared_child
, EXTRACTVALUE(VALUE(xs), '/ChildNode/reason') || ': ' || EXTRACTVALUE(VALUE(xs), '/ChildNode/details') nonshared_reason_and_details
, VALUE(xs) reason_xml
FROM TABLE (
SELECT XMLSEQUENCE(EXTRACT(d, '/Cursor/ChildNode')) val FROM (
SELECT
--XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))
-- the XMLSERIALIZE + XMLTYPE combo is included for avoiding a crash in qxuageag() XML aggregation function
XMLTYPE (XMLSERIALIZE( DOCUMENT XMLElement("Cursor", XMLAgg(x.extract('/doc/ChildNode')))) ) d
FROM
v$sql_shared_cursor c
, TABLE(XMLSEQUENCE(XMLTYPE('<doc>'||c.reason||'</doc>'))) x
WHERE
c.sql_id = '&2' and c.child_number < 5
)
) xs
/
Bug 31211220 – High version count (cursor leaks) due to bind_equiv_failure (Doc ID 31211220.8)
SQL's Are Not Getting Shared due to BIND_EQUIV_FAILURE in 12.2 (Doc ID 2635456.1)
====
example from Web
-- purge_cursor.sql
DECLARE
l_name VARCHAR2(64);
l_sql_text CLOB;
BEGIN
-- get address, hash_value and sql text
SELECT address||','||hash_value, sql_fulltext
INTO l_name, l_sql_text
FROM v$sqlarea
WHERE sql_id = '&&sql_id.';
-- not always does the job
SYS.DBMS_SHARED_POOL.PURGE (
name => l_name,
flag => 'C',
heaps => 1
);
-- create fake sql patch
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH (
sql_text => l_sql_text,
hint_text => 'NULL',
name => 'purge_&&sql_id.',
description => 'PURGE CURSOR',
category => 'DEFAULT',
validate => TRUE
);
-- drop fake sql patch
SYS.DBMS_SQLDIAG.DROP_SQL_PATCH (
name => 'purge_&&sql_id.',
ignore => TRUE
);
END;
/
declare
v1 varchar2(128);
begin
v1 := dbms_sqldiag.create_sql_patch(
sql_id => 'g2z10tbxyz6b0',
name => 'validate_fk',
hint_text => 'ignore_optim_embedded_hints'
-- hint_text => 'parallel(a@sel$1 8)' -- worked
-- hint_text => 'parallel(8)' -- worked
-- hint_text => q'{opt_param('_fast_full_scan_enabled' 'false')}' -- worked
