Mallik034 (Vismo Technologies): December 2024

Saturday, December 14, 2024

Unable to acquire a central Inventory lock by OPatch due to multiple opatch run

OPatch will sleep for few seconds, before re-trying to get the lock...

High Level steps:

1. Rollback a patch from Oracle Home using opatch utility/tool

2. Meantime when you are rolling back a patch from Oracle Home, Parallelly try to rollback a patch from GI(Grid Home) which will be waiting on acquiring lock.

3. Verify the OPatch process running from Oracle Home and another from GI Home

4. As soon as Oracle Home patch rollback completes then GI home opatch rollback proceed further which was waiting on acquiring lock.

1. Rollback a patch from Oracle Home using opatch utility/tool

[root@oraclelab2 ~]# su - oracle

Last login: Thu May 2 00:06:12 IST 2024 on pts/0

[oracle@oraclelab2 ~]$ . oraenv

ORACLE_SID = [oracle] ? TESTDB

The Oracle base has been set to /u01/app/oracle

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch rollback -id 35042068

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-05-02_00-10-50AM_1.log

Patches will be rolled back in the following order:

35042068

The following patch(es) will be rolled back: 35042068

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0.0/dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Rolling back patch 35042068...

RollbackSession rolling back interim patch '35042068' from OH '/u01/app/oracle/product/19.0.0.0/dbhome_1'

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.slax.rsf, 19.0.0.0.0...

Patching component oracle.ordim.jai, 19.0.0.0.0...

Patching component oracle.bali.jewt, 11.1.1.6.0...

Patching component oracle.bali.ewt, 11.1.1.6.0...

Patching component oracle.help.ohj, 11.1.1.7.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.perlint.expat, 2.0.1.0.4...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

2. Meantime when you are rolling back a patch from Oracle Home, Parallelly try to rollback a patch from GI(Grid Home) which will be waiting on acquiring lock.

Which will hang due unable to acquire a lock on central inventory since already rollback running from Oracle Home put a lock on central inventory

[oracle@oraclelab2 35037840]$ . oraenv

ORACLE_SID = [+ASM] ? +ASM

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab2 35037840]$

[oracle@oraclelab2 35037840]$ env |grep ORA

ORACLE_SID=+ASM

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/19.0.0.0/grid

[oracle@oraclelab2 35037840]$ /u01/app/19.0.0.0/grid/OPatch/opatch lspatches

34580338;TOMCAT RELEASE UPDATE 19.0.0.0.0 (34580338)

34428761;ACFS RELEASE UPDATE 19.17.0.0.0 (34428761)

34444834;OCW RELEASE UPDATE 19.17.0.0.0 (34444834)

34419443;Database Release Update : 19.17.0.0.221018 (34419443)

OPatch succeeded.

[oracle@oraclelab2 35037840]$ /u01/app/19.0.0.0/grid/OPatch/opatch rollback -id 34580338

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /u01/app/19.0.0.0/grid

Central Inventory : /u01/app/oraInventory

from : /u01/app/19.0.0.0/grid/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /u01/app/19.0.0.0/grid/cfgtoollogs/opatch/opatch2024-05-02_00-11-54AM_1.log

Unable to lock Central Inventory. OPatch will attempt to re-lock.

Do you want to proceed? [y|n]

User Responded with: Y

OPatch will sleep for few seconds, before re-trying to get the lock...

3. Verify the OPatch process running from Oracle Home and another from GI Home

[root@oraclelab2 ~]# ps -ef|grep opatch

oracle 7329 7261 0 00:10 pts/0 00:00:00 /bin/sh /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch rollback -id 35042068

oracle 7466 7329 69 00:10 pts/0 00:01:51 /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/jre/bin/java -Xmx3072m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch -cp /u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/OraInstaller.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/OraInstallerNet.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/OraPrereq.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/share.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/orai18n-mapping.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/xmlparserv2.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/emCfg.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/oui/jlib/ojmisc.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/ocm/lib/emocmclnt.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/jlib/opatch.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/jlib/opatchsdk.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/automation.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/apache-commons/commons-cli-1.0.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/jlib/oracle.opatch.classpath.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/jaxb/activation.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/jaxb/jaxb-api.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/jaxb/jaxb-impl.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/jaxb/jsr173_1.0_api.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/OsysModel.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/osysmodel-utils.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/CRSProductDriver.jar:/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/oplan/jlib/oracle.oplan.classpath.jar -DCommonLog.LOG_SESSION_ID= -DCommonLog.COMMAND_NAME=rollback -DOPatch.ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1 -DOPatch.DEBUG=false -DOPatch.MAKE=false -DOPatch.RUNNING_DIR=/u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch -DOPatch.MW_HOME= -DOPatch.WL_HOME= -DOPatch.COMMON_COMPONENTS_HOME= -DOPatch.OUI_LOCATION=/u01/app/oracle/product/19.0.0.0/dbhome_1/oui -DOPatch.FMW_COMPONENT_HOME= -DOPatch.OPATCH_CLASSPATH= -DOPatch.WEBLOGIC_CLASSPATH= -DOPatch.SKIP_OUI_VERSION_CHECK= -DOPatch.NEXTGEN_HOME_CHECK=false -DOPatch.PARALLEL_ON_FMW_OH= oracle/opatch/OPatch rollback -id 35042068 -invPtrLoc /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc

oracle 7979 31071 0 00:11 pts/1 00:00:00 /bin/sh /u01/app/19.0.0.0/grid/OPatch/opatch rollback -id 34580338

oracle 8125 7979 0 00:11 pts/1 00:00:00 /u01/app/19.0.0.0/grid/OPatch/jre/bin/java -d64 -Xmx3072m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/u01/app/19.0.0.0/grid/cfgtoollogs/opatch -cp /u01/app/19.0.0.0/grid/oui/jlib/OraInstaller.jar:/u01/app/19.0.0.0/grid/oui/jlib/OraInstallerNet.jar:/u01/app/19.0.0.0/grid/oui/jlib/OraPrereq.jar:/u01/app/19.0.0.0/grid/oui/jlib/share.jar:/u01/app/19.0.0.0/grid/oui/jlib/orai18n-mapping.jar:/u01/app/19.0.0.0/grid/oui/jlib/xmlparserv2.jar:/u01/app/19.0.0.0/grid/oui/jlib/emCfg.jar:/u01/app/19.0.0.0/grid/oui/jlib/ojmisc.jar:/u01/app/19.0.0.0/grid/OPatch/ocm/lib/emocmclnt.jar:/u01/app/19.0.0.0/grid/OPatch/jlib/opatch.jar:/u01/app/19.0.0.0/grid/OPatch/jlib/opatchsdk.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/automation.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/apache-commons/commons-cli-1.0.jar:/u01/app/19.0.0.0/grid/OPatch/jlib/oracle.opatch.classpath.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/jaxb/activation.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/jaxb/jaxb-api.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/jaxb/jaxb-impl.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/jaxb/jsr173_1.0_api.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/OsysModel.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/osysmodel-utils.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/CRSProductDriver.jar:/u01/app/19.0.0.0/grid/OPatch/oplan/jlib/oracle.oplan.classpath.jar -DCommonLog.LOG_SESSION_ID= -DCommonLog.COMMAND_NAME=rollback -DOPatch.ORACLE_HOME=/u01/app/19.0.0.0/grid -DOPatch.DEBUG=false -DOPatch.MAKE=false -DOPatch.RUNNING_DIR=/u01/app/19.0.0.0/grid/OPatch -DOPatch.MW_HOME= -DOPatch.WL_HOME= -DOPatch.COMMON_COMPONENTS_HOME= -DOPatch.OUI_LOCATION=/u01/app/19.0.0.0/grid/oui -DOPatch.FMW_COMPONENT_HOME= -DOPatch.OPATCH_CLASSPATH= -DOPatch.WEBLOGIC_CLASSPATH= -DOPatch.SKIP_OUI_VERSION_CHECK= -DOPatch.NEXTGEN_HOME_CHECK=false -DOPatch.PARALLEL_ON_FMW_OH= oracle/opatch/OPatch rollback -id 34580338 -invPtrLoc /u01/app/19.0.0.0/grid/oraInst.loc

root 8558 8496 0 00:13 pts/2 00:00:00 grep --color=auto opatch

[root@oraclelab2 ~]#

4. As soon as Oracle Home patch rollback completes then GI home opatch rollback proceed further which was waiting on acquiring lock.

[oracle@oraclelab2 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/opatch rollback -id 35042068

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /u01/app/oracle/product/19.0.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-05-02_00-10-50AM_1.log

Patches will be rolled back in the following order:

35042068

The following patch(es) will be rolled back: 35042068

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0.0/dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Rolling back patch 35042068...

RollbackSession rolling back interim patch '35042068' from OH '/u01/app/oracle/product/19.0.0.0/dbhome_1'

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.slax.rsf, 19.0.0.0.0...

Patching component oracle.ordim.jai, 19.0.0.0.0...

Patching component oracle.bali.jewt, 11.1.1.6.0...

Patching component oracle.bali.ewt, 11.1.1.6.0...

Patching component oracle.help.ohj, 11.1.1.7.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.perlint.expat, 2.0.1.0.4...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.ldap.ssl, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...

RollbackSession removing interim patch '35042068' from inventory

Inactive sub-set patch [34419443] has become active due to the rolling back of a super-set patch [35042068].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-05-02_00-10-50AM_1.log

OPatch succeeded.

[oracle@oraclelab2 ~]$

[oracle@oraclelab2 35037840]$ /u01/app/19.0.0.0/grid/OPatch/opatch rollback -id 34580338

Oracle Interim Patch Installer version 12.2.0.1.41

Oracle Home : /u01/app/19.0.0.0/grid

Central Inventory : /u01/app/oraInventory

from : /u01/app/19.0.0.0/grid/oraInst.loc

OPatch version : 12.2.0.1.41

OUI version : 12.2.0.7.0

Log file location : /u01/app/19.0.0.0/grid/cfgtoollogs/opatch/opatch2024-05-02_00-11-54AM_1.log

Unable to lock Central Inventory. OPatch will attempt to re-lock.

Do you want to proceed? [y|n]

User Responded with: Y

OPatch will sleep for few seconds, before re-trying to get the lock...

Patches will be rolled back in the following order:

34580338

The following patch(es) will be rolled back: 34580338

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/19.0.0.0/grid')

Is the local system ready for patching? [y|n]

User Responded with: Y

Rolling back patch 34580338...

RollbackSession rolling back interim patch '34580338' from OH '/u01/app/19.0.0.0/grid'

Patching component oracle.tomcat.crs, 19.0.0.0.0...

RollbackSession removing interim patch '34580338' from inventory

Inactive sub-set patch [29401763] has become active due to the rolling back of a super-set patch [34580338].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/app/19.0.0.0/grid/cfgtoollogs/opatch/opatch2024-05-02_00-11-54AM_1.log

OPatch succeeded.

[oracle@oraclelab2 35037840]$

Add RAC Database to Clusterware

#Add RAC Database to Clusterware

srvctl add database -d CDB -n CDB -o '/u01/app/oracle/product/19.0.0.0/dbhome_1' -p '+DATA/CDB/PARAMETERFILE/spfile.437.1148238013' -t IMMEDIATE -a 'DATA,RECO'

#Add RAC Database Instances

srvctl add instance -d CDB -i CDB1 -n node1

srvctl add instance -d CDB -i CDB2 -n node2

#Check the RAC Database configuration

srvctl config database -d CDB

#Check and start the RAC Database

srvctl start database -d CDB

srvctl status database -d CDB

#Stop the RAC Database and remove from the cluster if needed

srvctl stop database -d CDB

srvctl remove database -d CDB

#disable auto start-up for the RAC database

srvctl disable database -d CDB

srvctl enable database -d CDB

logs:

=====

[root@node1 ~]# ps -ef|grep smon

root 20464 1 1 Nov12 ? 08:16:44 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 20796 1 0 Nov12 ? 00:00:47 asm_smon_+ASM1

oracle 23058 1 0 Nov12 ? 00:01:20 ora_smon_RAC12C1

root 31533 29768 0 01:39 pts/0 00:00:00 grep --color=auto smon

[root@node1 ~]# . oraenv

ORACLE_SID = [root] ? +ASM1

The Oracle base has been set to /u01/app/oracle

[root@node1 ~]#

[root@node1 ~]# env |grep ORA

ORACLE_SID=+ASM1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/19.0.0.0/grid

[root@node1 ~]#

[root@node1 ~]# olsnodes

node1

node2

[root@node1 ~]#

[root@node1 ~]# crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.LISTENER.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_PRIMDB.lsnr

ONLINE OFFLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_RAC12C.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.chad

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.helper

OFFLINE OFFLINE node1 IDLE,STABLE

OFFLINE OFFLINE node2 IDLE,STABLE

ora.net1.network

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.ons

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.DATA.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE node1 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE node2 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE node2 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE node2 169.254.9.52 10.38.9

.112,STABLE

ora.RECO.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.asm(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 Started,STABLE

3 ONLINE OFFLINE STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.cvu

1 ONLINE ONLINE node2 STABLE

ora.dhbstg.db

1 ONLINE OFFLINE STABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.gridtgt.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.mgmtdb

1 ONLINE ONLINE node2 Open,STABLE

ora.node1.vip

1 ONLINE ONLINE node1 STABLE

ora.node2.vip

1 ONLINE ONLINE node2 STABLE

ora.qosmserver

1 ONLINE ONLINE node2 STABLE

ora.rac12c.db

1 ONLINE ONLINE node1 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

2 ONLINE ONLINE node2 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

ora.rhpserver

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE node1 STABLE

ora.scan2.vip

1 ONLINE ONLINE node2 STABLE

ora.scan3.vip

1 ONLINE ONLINE node2 STABLE

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

[root@node1 ~]#

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [oracle] ? CDB1

The Oracle base has been set to /u01/app/oracle

[oracle@node1 ~]$

[oracle@node1 ~]$ env |grep ORA

ORACLE_SID=CDB1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@node1 ~]$ srvctl status database -d CDB

PRCD-1120 : The resource for database CDB could not be found.

PRCR-1001 : Resource ora.cdb.db does not exist

[oracle@node1 ~]$

[oracle@node1 ~]$ srvctl add database -d CDB -n CDB -o '/u01/app/oracle/product/19.0.0.0/dbhome_1' -p '+DATA/CDB/PARAMETERFILE/spfile.437.1148238013' -t IMMEDIATE -a 'DATA,RECO'

[oracle@node1 ~]$

[oracle@node1 ~]$ srvctl config database -d CDB

Database unique name: CDB

Database name: CDB

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/CDB/PARAMETERFILE/spfile.437.1148238013

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances:

Configured nodes:

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@node1 ~]$ srvctl add instance -d CDB -i CDB1 -n node1

[oracle@node1 ~]$ srvctl add instance -d CDB -i CDB2 -n node2

[oracle@node1 ~]$ srvctl config database -d CDB

Database unique name: CDB

Database name: CDB

Oracle home: /u01/app/oracle/product/19.0.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/CDB/PARAMETERFILE/spfile.437.1148238013

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: CDB1,CDB2

Configured nodes: node1,node2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@node1 ~]$ srvctl status database -d CDB

Instance CDB1 is not running on node node1

Instance CDB2 is not running on node node2

[oracle@node1 ~]$ srvctl start database -d CDB

[oracle@node1 ~]$ srvctl status database -d CDB

Instance CDB1 is running on node node1

Instance CDB2 is running on node node2

Verify the database status at Cluster level using crsctl

[oracle@node2 ~]$ crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.LISTENER.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_PRIMDB.lsnr

ONLINE OFFLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_RAC12C.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.chad

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.helper

OFFLINE OFFLINE node1 IDLE,STABLE

OFFLINE OFFLINE node2 IDLE,STABLE

ora.net1.network

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.ons

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.DATA.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE node1 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE node2 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE node2 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE node2 169.254.9.52 10.38.9

.112,STABLE

ora.RECO.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.asm(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 Started,STABLE

3 ONLINE OFFLINE STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.cdb.db

1 ONLINE ONLINE node1 Open,HOME=/u01/app/o

racle/product/19.0.0

.0/dbhome_1,STABLE

2 ONLINE ONLINE node2 Open,HOME=/u01/app/o

racle/product/19.0.0

.0/dbhome_1,STABLE

ora.cvu

1 ONLINE ONLINE node2 STABLE

ora.dhbstg.db

1 ONLINE OFFLINE STABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.gridtgt.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.mgmtdb

1 ONLINE ONLINE node2 Open,STABLE

ora.node1.vip

1 ONLINE ONLINE node1 STABLE

ora.node2.vip

1 ONLINE ONLINE node2 STABLE

ora.qosmserver

1 ONLINE ONLINE node2 STABLE

ora.rac12c.db

1 ONLINE ONLINE node1 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

2 ONLINE ONLINE node2 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

ora.rhpserver

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE node1 STABLE

ora.scan2.vip

1 ONLINE ONLINE node2 STABLE

ora.scan3.vip

1 ONLINE ONLINE node2 STABLE

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

[oracle@node2 ~]$

[oracle@node1 ~]$ srvctl remove database -d CDB

PRKO-3141 : Database CDB could not be removed because it was running

[oracle@node1 ~]$ srvctl stop database -d CDB

[oracle@node1 ~]$ srvctl remove database -d CDB

Remove the database CDB? (y/[n]) y

[oracle@node1 ~]$

[oracle@node2 ~]$ crsctl stat res -t

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

Name Target State Server State details

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

Local Resources

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

ora.LISTENER.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_PRIMDB.lsnr

ONLINE OFFLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.LISTENER_RAC12C.lsnr

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.chad

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.helper

OFFLINE OFFLINE node1 IDLE,STABLE

OFFLINE OFFLINE node2 IDLE,STABLE

ora.net1.network

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

ora.ons

ONLINE ONLINE node1 STABLE

ONLINE ONLINE node2 STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.DATA.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE node1 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE node2 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE node2 STABLE

ora.MGMTLSNR

1 ONLINE ONLINE node2 169.254.9.52 10.38.9

.112,STABLE

ora.RECO.dg(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 OFFLINE OFFLINE STABLE

ora.asm(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 Started,STABLE

3 ONLINE OFFLINE STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

1 ONLINE ONLINE node2 STABLE

2 ONLINE ONLINE node1 STABLE

3 ONLINE OFFLINE STABLE

ora.cvu

1 ONLINE ONLINE node2 STABLE

ora.dhbstg.db

1 ONLINE OFFLINE STABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.gridtgt.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

2 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.mgmtdb

1 ONLINE ONLINE node2 Open,STABLE

ora.node1.vip

1 ONLINE ONLINE node1 STABLE

ora.node2.vip

1 ONLINE ONLINE node2 STABLE

ora.qosmserver

1 ONLINE ONLINE node2 STABLE

ora.rac12c.db

1 ONLINE ONLINE node1 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

2 ONLINE ONLINE node2 Open,HOME=/u01/app/o

racle/product/12.2.0

.1/dbhome_1,STABLE

ora.rhpserver

1 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE node1 STABLE

ora.scan2.vip

1 ONLINE ONLINE node2 STABLE

ora.scan3.vip

1 ONLINE ONLINE node2 STABLE

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

[oracle@node2 ~]$

Thursday, December 5, 2024

SQL command prompt customization!!!

How to change SQL prompt? Customization on sql prompt!!!

Connect to database by exporting environmental variables

. oraenv

>>> DEVDB

sqlplus / as sysdba

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:35:48 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL>

As oracle owner add the following line at the glogin.sql script which is located under $ORACLE_HOME/sqlplus/admin

Display the connected instance name in sql prompt?

set sqlprompt "_connect_identifier > "

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:04 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

DEVDB >

Display the connected username and instance name in sql prompt?

set sqlprompt "_user '@' _connect_identifier > "

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:44 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SYS @ DEVDB >

Logs:

=====

[root@oraclelab1 ~]# ps -ef|grep smon

oracle 29528 1 0 12:31 ? 00:00:00 ora_smon_DEVDB

root 30073 29314 0 12:35 pts/0 00:00:00 grep --color=auto smon

[root@oraclelab1 ~]# su - oracle

Last login: Thu Dec 5 12:31:01 IST 2024 on pts/0

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [DEVDB] ? DEVDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DEVDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:35:48 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 ~]$ cd $ORACLE_HOME/sqlplus/admin

[oracle@oraclelab1 admin]$ ll glogin.sql

-rw-r--r--. 1 oracle oinstall 342 Jan 13 2006 glogin.sql

[oracle@oraclelab1 admin]$ vi glogin.sql

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ cat glogin.sql

-- NAME

-- glogin.sql

-- DESCRIPTION

-- SQL*Plus global login "site profile" file

-- Add any SQL*Plus commands here that are to be executed when a

-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.

-- USAGE

-- This script is automatically run

set sqlprompt "_connect_identifier > "

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:04 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

DEVDB > exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 admin]$ vi glogin.sql

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ cat glogin.sql

-- NAME

-- glogin.sql

-- DESCRIPTION

-- SQL*Plus global login "site profile" file

-- Add any SQL*Plus commands here that are to be executed when a

-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.

-- USAGE

-- This script is automatically run

#set sqlprompt "_connect_identifier > "

set sqlprompt "_user '@' _connect_identifier > "

[oracle@oraclelab1 admin]$

[oracle@oraclelab1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:37:44 2024

Version 19.17.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SYS @ DEVDB > exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 admin]$

Mallik034 (Vismo Technologies)

Saturday, December 14, 2024

Unable to acquire a central Inventory lock by OPatch due to multiple opatch run

Unable to acquire a central Inventory lock by OPatch due to multiple opatch run

High Level steps:

1. Rollback a patch from Oracle Home using opatch utility/tool

2. Meantime when you are rolling back a patch from Oracle Home, Parallelly try to rollback a patch from GI(Grid Home) which will be waiting on acquiring lock.

3. Verify the OPatch process running from Oracle Home and another from GI Home

4. As soon as Oracle Home patch rollback completes then GI home opatch rollback proceed further which was waiting on acquiring lock.

Add RAC Database to Clusterware

Add RAC Database to Clusterware

#Add RAC Database to Clusterware

#Add RAC Database Instances

#Check the RAC Database configuration

#Check and start the RAC Database

#Stop the RAC Database and remove from the cluster if needed

#disable auto start-up for the RAC database

Thursday, December 5, 2024

SQL command prompt customization!!!

How to change SQL prompt? Customization on sql prompt!!!

OCI 2025 Certification Learning Paths and Exams Now Available

Social Plugin

Blog Archive