Vismo Technologies — Oracle DBA Knowledge Hub: 2024

Vismo Technologies ☰ Menu

Enroll Now →

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]$

Mallikarjun Ramadurg

THE ORACLE SME – ORACLE ACE, EXADATA, OCI, OCP, OCA, RAC EXPERT. Mallikarjun Ramadurg is the one of the active member of Oracle Community. Mallikarjun is an Oracle DBA and EXADATA administration Expert and has a deep knowledge in Oracle EBS APPS. He is having more than 11 Years of experience. He has got In-depth knowledge of database design, build, cloning and administration and he has got good exposure to environments like database HA, RAC, Oracle Cloud and Datawarehouse. He has worked on Oracle ERP, having sound knowledge on EBS application administration, apps cloning and upgradation, patching, security and performance turning. He has worked on informatica administration, implementation, design, mappings, workflows. He has good knowledge on data modeling, Erwin data modeling. He has good hands-on experience in Oracle Cloud and infrastructure level. He is currently working as Oracle SME & Staff Customer Success Engineer @ Rubrik Inc, Bangalore India

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 ~]$

Mallikarjun Ramadurg

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]$

Mallikarjun Ramadurg

Thursday, November 21, 2024

Query taking more time?

1. DML Query (Insert, Update,)

Cause: locks / deadlocks

Fix/Solution: kill / Ask user to do commit/rollback

2. Select Query

- OS side - OS analysis

- top, vmstat, iosts, memory, sar (OEM, OS watcher, Exa watcher, Nagios)

- DB Side - DB analysis

- Query dynamic perf view (v$session, v$longops, v$sql etc...)

- AWR report (ASH report, ADDM report, SQL advisory report)

To identify:

- SQL text (select * from emp;) - We can identify what all tables involved in the query:

Recommendations

- Latest Patch (n-1) (Jan, Apr, Jul, Oct)

- Gather stats (Table/Index/Schema)

- Validation / rebuild Index

- Table move

- Table shrink

- SQL ID (1a1a1a1) - What is the execution plan associated to this SQL ID

- Check execution plan (Plan change)

- SQL Profiling

Mallikarjun Ramadurg

Thursday, September 19, 2024

Running SQL and O/S Commands Within RMAN

Sometimes you may want to run an SQL statement from within RMAN. Use RMAN’s sql command to do this. For example:

RMAN> sql "alter system switch logfile";

If there are single quote marks in your SQL, you need to use two single quote marks as shown in this next example:

RMAN> sql "alter database datafile ''/d0101/ordadta/brdstn/users_01.dbf'' offline";

You can also run O/S commands using a similar technique with the host command:

RMAN> host "ls";

Some SQL commands, such as ALTER DATABASE, are directly supported by RMAN. These can be executed directly from the RMAN command prompt, without using the sql command. For example:

RMAN> alter database mount;

Note that the complete syntax of the SQL ALTER DATABASE command is not supported from within RMAN.

Mallikarjun Ramadurg

Thursday, August 15, 2024

How to export table from MALLIK user in Oracle database?

How to export table from MALLIK user in Oracle database?

1. create directory inside database pointing to physical directory at OS level & grant read and write permission

[oracle@oraclelab1 DUMP]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 6 08:44:29 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> CREATE OR REPLACE DIRECTORY TEST_DIR AS '/u01/DUMP';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO system;

Grant succeeded.

SQL> exit

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

Version 19.17.0.0.0

[oracle@oraclelab1 DUMP]$

2. export table from Mallik user

[oracle@oraclelab1 DUMP]$ expdp system/Mallik123 tables=MALLIK.STUDENT1 directory=TEST_DIR dumpfile=STUDENT.dmp logfile=expdpSTUDENT.log

Export: Release 19.0.0.0.0 - Production on Tue Aug 6 08:46:20 2024

Version 19.17.0.0.0

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

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=MALLIK.STUDENT1 directory=TEST_DIR dumpfile=STUDENT.dmp logfile=expdpSTUDENT.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "MALLIK"."STUDENT1" 6.390 KB 2 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/u01/DUMP/STUDENT.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 6 08:46:37 2024 elapsed 0 00:00:17

[oracle@oraclelab1 DUMP]$ ls -ltrh

total 180K

-rw-r-----. 1 oracle oinstall 176K Aug 6 08:46 STUDENT.dmp

-rw-r--r--. 1 oracle oinstall 1.1K Aug 6 08:46 expdpSTUDENT.log

[oracle@oraclelab1 DUMP]$

Mallikarjun Ramadurg

Thursday, August 8, 2024

Oracle On Windows - sysdba user is getting error ORA-01017: invalid username/password; logon denied

Issue:
Windows administrator user is unable to connect to database as sysdba user using OS authentication

C:\Users\Administrator>set ORACLE_HOME=E:\app\mallik\product\19.0.0.0\dbhome_1

C:\Users\Administrator>set ORACLE_SID=ORA19C

C:\Users\Administrator>E:\app\mallik\product\19.0.0.0\dbhome_1\bin\sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 20:29:34 2024

Version 19.3.0.0.0

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\Administrator>

Cause:
Administrator user is not port of ORA_DBA group

Solution or Fix:
Add administrator user to ORA_DBA group

Edit local users and groups

-> add administrator user to ORA_DBA group

Mallikarjun Ramadurg

Oracle On Windows - ORA-12560: TNS:protocol adapter error

Issue:

Unable to connect to database getting error ORA-12560: TNS:protocol adapter error

C:\Users\Administrator>set ORACLE_HOME=E:\app\mallik\product\19.0.0.0\dbhome_1

C:\Users\Administrator>set ORACLE_SID=ORA19C

C:\Users\Administrator>E:\app\mallik\product\19.0.0.0\dbhome_1\bin\sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 23:00:31 2024

Version 19.3.0.0.0

ERROR:

ORA-12560: TNS:protocol adapter error

Enter user-name:

ERROR:

ORA-12560: TNS:protocol adapter error

Enter user-name:

ERROR:

ORA-12560: TNS:protocol adapter error

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\Administrator>

Cause:

Windows service will be down - OracleServiceORA19C

Solution or Fix:

Verify the windows service status for Oracle and restart them

Mallikarjun Ramadurg

Wednesday, July 24, 2024

Can I have sys password and password file password different?

Ans: Yes

If you create the password file with a password different than the sys password, that'll be the password you use to connect as sysdba over a network (the password in the password file is used for sysdba connections)

Facts about sys user password and password file

1. SYS password is defined at the time of database creation

- Same password will be used to create password file

2. We can change the sys user password using SQL command

SQL> alter user sys identified by <password_1>;

3. We can create a password file with a password other than sys user password

orapwd file=orapw<SID> password=<password_2>

4. It is best practice to keep both sys user and password file password to be same

5. Changing the sys password using alter user will automatically update the password file

- Where as creating the password file with new password will not update the sys password inside database.

More details about the sys user password and password file password refer the below docs

Problem to connect as SYSDBA

https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:670117794561

sys password change and orapwd file

https://asktom.oracle.com/ords/f?p=100:11:::::P11_QUESTION_ID:1792735500346727875

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com

Mallikarjun Ramadurg

Thursday, June 20, 2024

Manually Drop RAC database using command line

We have 2 RAC database called DEVCDB and TESTCDB

DEVCBD (DEVCBD1 & DEVCBD2)

TESTCDB (TESTCDB1 & TESTCDB2)

We will drop these RAC databases using manual method in command line.

1. Check RAC database instance status

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

root 2047 1865 0 20:24 pts/2 00:00:00 grep --color=auto smon

oracle 2622 1 0 May10 ? 00:00:26 ora_smon_DEVDB1

oracle 15043 1 0 May12 ? 00:00:29 ora_smon_TESTCDB1

root 16358 1 1 May05 ? 07:15:10 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 17256 1 0 May05 ? 00:00:26 asm_smon_+ASM1

oracle 20885 1 0 May12 ? 00:00:24 ora_smon_DEVCDB1

[root@hostnode1 ~]#

2. login to oracle user and stop the RAC database

[root@hostnode1 ~]# su - oracle

Last login: Fri May 24 20:13:08 IST 2024

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEVCDB1

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl stop database -d DEVCDB

3. Start the database in nomount and set cluster_database=false so that we can start the RAC database instance in exclusive mode

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 24 20:27:15 2024

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1644167168 bytes

Database Buffers 2030043136 bytes

Redo Buffers 7634944 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

4. Start database in mount exclusive restrict mode

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1644167168 bytes

Database Buffers 2030043136 bytes

Redo Buffers 7634944 bytes

Database mounted.

5. Drop database

SQL> drop database;

Database dropped.

SQL> exit

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

Version 19.3.0.0.0

[oracle@hostnode1 ~]$

6. Remove database from the cluster configuration

[oracle@hostnode1 ~]$ srvctl status database -d DEVCDB

Instance DEVCDB1 is not running on node hostnode1

Instance DEVCDB2 is not running on node hostnode2

[oracle@hostnode1 ~]$ srvctl remove database -d DEVCDB

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

[oracle@hostnode1 ~]$

7. Similarly follow the above step #1 to step #6 for dropping the TESTCDB database.

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

root 3673 1 1 May01 ? 08:49:44 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 4112 1 0 May01 ? 00:00:33 asm_smon_+ASM2

oracle 7539 1 0 May12 ? 00:00:33 ora_smon_TESTCDB2

oracle 10041 1 0 May10 ? 00:00:31 ora_smon_DEVDB2

oracle 18734 1 0 May12 ? 00:00:26 ora_smon_DEVCDB2

root 32313 32171 0 20:24 pts/0 00:00:00 grep --color=auto smon

[root@hostnode2 ~]#

[root@hostnode2 ~]# su - oracle

Last login: Fri May 24 18:03:45 IST 2024

[oracle@hostnode2 ~]$

[oracle@hostnode2 ~]$ . oraenv

ORACLE_SID = [oracle] ? TESTCDB2

ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1

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

[oracle@hostnode2 ~]$ srvctl stop database -d TESTCDB

[oracle@hostnode2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 24 20:27:15 2024

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1073741824 bytes

Database Buffers 2600468480 bytes

Redo Buffers 7634944 bytes

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 3690986544 bytes

Fixed Size 9141296 bytes

Variable Size 1073741824 bytes

Database Buffers 2600468480 bytes

Redo Buffers 7634944 bytes

Database mounted.

SQL> drop database;

Database dropped.

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

Version 19.3.0.0.0

SQL> exit

^[[A^[[A[oracle@hostnode2 ~]$ srvctl status database -d TESTCDB

Instance TESTCDB1 is not running on node hostnode1

Instance TESTCDB2 is not running on node hostnode2

[oracle@hostnode2 ~]$ srvctl remove database -d TESTCDB

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

[oracle@hostnode2 ~]$

Regards,

Mallik

Mallikarjun Ramadurg

Sunday, June 2, 2024

Copy password file from FS to ASM diskgroup

Copy password file from FS to ASM diskgroup:

1. Verify passwordile available on FS

[oracle@oraclenode1 ~]$ ll /tmp/orapwRAC12C

-rw-r----- 1 oracle oinstall 6144 Jun 2 11:01 /tmp/orapwRAC12C

2. set the enviroment and copy the password file from FS to ASM diskgroup

[oracle@oraclenode1 tmp]$ . oraenv

ORACLE_SID = [RACSb1] ? RACSB1

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

[oracle@oraclenode1 tmp]$

[oracle@oraclenode1 tmp]$ asmcmd -p

ASMCMD [+] > pwcopy --dbuniquename RACSB '/tmp/orapwRAC12C' '+DATA/RACSB/PASSWORD/orapwRACSB'

ASMCMD [+] >

3. Verify the password file on ASM diskgroup

[oracle@oraclenode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? +ASM1

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

[oracle@oraclenode1 ~]$ asmcdm -p

ASMCMD [+] > cd +DATA/RACSB/PASSWORD/

ASMCMD [+DATA/RACSB/PASSWORD] > ls -l

Type Redund Striped Time Sys Name

PASSWORD UNPROT COARSE JUN 02 11:00:00 N orapwracsb => +DATA/RACSB/PASSWORD/pwdracsb.309.1170587277

PASSWORD UNPROT COARSE JUN 02 11:00:00 Y pwdracsb.309.1170587277

ASMCMD [+DATA/RACSB/PASSWORD] >

4. Verify the database connection using password file

[oracle@oraclenode1 ~]$ sqlplus sys/Mallik123#@RACSB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 2 11:10:38 2024

Last Successful login time: Sun Jun 02 2024 11:00:35 +05:30

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

Mallikarjun Ramadurg

Tuesday, April 2, 2024

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month.

[oracle@oracledb script]$ ps -ef|grep smon

oracle 2524 1 0 12:53 ? 00:00:00 ora_smon_GGSOURCE

oracle 3122 1 0 12:54 ? 00:00:00 ora_smon_ORA12C

oradev 4029 1 0 12:56 ? 00:00:00 ora_smon_ORACDB

oracle 13118 1 0 Feb22 ? 00:00:56 ora_smon_ORCL

oracle 26246 18177 0 17:33 pts/0 00:00:00 grep --color=auto smon

[oracle@oracledb script]$

2. Set the environment to a database where you are generating the archivelog generation for last 30 days

[oracle@oracledb script]$ . oraenv

ORACLE_SID = [ORCL] ? ORCL

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

[oracle@oracledb script]$

3. Hours archivelog generation monitoring script

[oracle@oracledb script]$ ll *hour*

-rw-r--r--. 1 oracle oinstall 1916 Oct 26 2022 archivelog_generation_hourly.sql

[oracle@oracledb script]$ more archivelog_generation_hourly.sql

set verify off

set feed off

set timing off

PROMPT

PROMPT number of hourly redo switches for the last 31 days

set pages 1000 lines 1000

SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",

COUNT (1) "Total",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",

ROUND (COUNT (1) / 24, 2) "Avg"

FROM gv$log_history

WHERE thread# = inst_id

AND first_time > sysdate-31

GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')

ORDER BY 1,2;

[oracle@oracledb script]$

4. Run the hour archivelog monitoring script against ORCL database

[oracle@oracledb script]$ sqlplus / as sysdba @archivelog_generation_hourly.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 1 17:34:09 2024

Version 19.11.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

number of hourly redo switches for the last 31 days

Date INST_ID Day Total h0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13

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

01-MAR-24 1 Fri 25 0 0 0 0 0 0 0 0 0 0 0 0 0 0

02-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

03-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

04-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

05-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

06-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

07-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

08-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

09-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

10-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

11-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

12-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

13-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

14-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

15-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

16-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

17-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

18-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

19-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

20-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

21-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

22-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

23-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

24-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

25-MAR-24 1 Mon 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

26-MAR-24 1 Tue 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

27-MAR-24 1 Wed 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

28-MAR-24 1 Thu 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

29-MAR-24 1 Fri 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

30-MAR-24 1 Sat 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

31-MAR-24 1 Sun 96 4 4 4 4 4 4 4 4 4 4 4 4 4 4

01-APR-24 1 Mon 93 4 4 4 4 4 4 4 4 4 4 4 4 4 23

SQL> exit

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

Version 19.11.0.0.0

[oracle@oracledb script]$

Regards,

Mallikarjun / Vismo Technologies

WhatsApp: +91 9880616848 / +91 9036478079

Cell: +91 9880616848 / +91 9036478079

Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com

Mallikarjun Ramadurg

Saturday, December 14, 2024

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 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

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

Thursday, November 21, 2024

Query taking more time?

Thursday, September 19, 2024

Running SQL and O/S Commands Within RMAN

Thursday, August 15, 2024

Thursday, August 8, 2024

Oracle On Windows - sysdba user is getting error ORA-01017: invalid username/password; logon denied

Issue:Windows administrator user is unable to connect to database as sysdba user using OS authentication

Cause: Administrator user is not port of ORA_DBA group

Solution or Fix:Add administrator user to ORA_DBA group

Oracle On Windows - ORA-12560: TNS:protocol adapter error

Unable to connect to database getting error ORA-12560: TNS:protocol adapter error

Windows service will be down - OracleServiceORA19C

Verify the windows service status for Oracle and restart them

Wednesday, July 24, 2024

Can I have sys password and password file password different?

1. SYS password is defined at the time of database creation

2. We can change the sys user password using SQL command

3. We can create a password file with a password other than sys user password

4. It is best practice to keep both sys user and password file password to be same

5. Changing the sys password using alter user will automatically update the password file

Thursday, June 20, 2024

Manually Drop RAC database using command line

1. Check RAC database instance status

2. login to oracle user and stop the RAC database

3. Start the database in nomount and set cluster_database=false so that we can start the RAC database instance in exclusive mode

4. Start database in mount exclusive restrict mode

5. Drop database

6. Remove database from the cluster configuration

7. Similarly follow the above step #1 to step #6 for dropping the TESTCDB database.

Sunday, June 2, 2024

1. Verify passwordile available on FS

Tuesday, April 2, 2024

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month.

2. Set the environment to a database where you are generating the archivelog generation for last 30 days

3. Hours archivelog generation monitoring script

4. Run the hour archivelog monitoring script against ORCL database

Issue:
Windows administrator user is unable to connect to database as sysdba user using OS authentication

Cause:
Administrator user is not port of ORA_DBA group

Solution or Fix:
Add administrator user to ORA_DBA group