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

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

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.

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

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

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

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

Friday, June 21, 2024

Flashback database to Guarantee Restore Point or Restore Point

Flashback database to Guarantee Restore Point or Restore Point after Upgrade from 12c to 19c

How to Perform Flashback Database after a Failed Database Upgrade (Doc ID 2882218.1)

How to Downgrade to 12c After Upgrade To 19c Using Flashback database (Doc ID 2887982.1)

We can flashback database at SQL command prompt as well as RMAN command prompt

Here in this blog we are falshing back to GRP after the database upgrade fron 12c to 19c.

- GRP restore point was created in 12c and once we flashback database to GRP after 19c upgrade it will automatically downgrade to 12c.

High Level Steps:

=================

On 19c Home

SQL> shutdown immediate

SQL> startup mount

SQL> flashback database to restore point GRP1;

SQL> shutdown immediate;

on 12c Home

SQL> startup mount

SQL> alter database open resetlogs;

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

oracle 3022 1 0 10:49 ? 00:00:00 ora_smon_TEST1

root 9504 9238 0 15:51 pts/0 00:00:00 grep --color=auto smon

root 13767 1 1 May25 ? 00:27:10 /u01/app/19.0.0.0/grid/bin/osysmond.bin

oracle 14088 1 0 May25 ? 00:00:01 asm_smon_+ASM1

oracle 25219 1 0 11:40 ? 00:00:00 ora_smon_UAT1

oracle 26493 1 0 10:45 ? 00:00:00 ora_smon_DEV1

[root@hostnode1 ~]# su - oracle

Last login: Sun May 26 15:41:45 IST 2024

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEV1

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 status database -d DEV

Instance DEV1 is running on node hostnode1

Instance DEV2 is running on node hostnode2

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

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

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

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

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

Instance DEV1 is not running on node hostnode1

Instance DEV2 is not running on node hostnode2

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 26 15:54:52 2024

Version 19.17.0.0.0

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3690985856 bytes

Fixed Size 8903040 bytes

Variable Size 989855744 bytes

Database Buffers 2684354560 bytes

Redo Buffers 7872512 bytes

Database mounted.

SQL>

SQL> set pages 1000 lines 1000

col TIME for a35

col RESTORE_POINT_TIME for a30

col NAME for a30

select SCN,DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,TIME,RESTORE_POINT_TIME,NAME,PDB_RESTORE_POINT from v$restore_point; 2

SCN DATABASE_INCARNATION# GUA TIME RESTORE_POINT_TIME NAME PDB

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

1550262 2 YES 26-MAY-24 09.12.24.000000000 AM BEFORE_UPGRADE NO

SQL>

SQL> flashback database to restore point BEFORE_UPGRADE;

Flashback complete.

SQL> shut immediate;

Database dismounted.

ORACLE instance shut down.

SQL>

[root@hostnode1 ~]# tail -100f /u01/app/oracle/diag/rdbms/dev/DEV1/trace/alert_DEV1.log

2024-06-05T23:15:53.393655+05:30

flashback database to restore point BEFORE_UPGRADE

2024-06-05T23:15:55.041279+05:30

Flashback Restore Start

2024-06-05T23:16:03.045947+05:30

Flashback Restore Complete

Flashback Media Recovery Start

Started logmerger process

2024-06-05T23:16:04.213698+05:30

Parallel Media Recovery started with 2 slaves

Flashback Media Recovery Log +RECO/DEV/ARCHIVELOG/2024_05_26/thread_1_seq_3.309.1169976533

2024-06-05T23:16:04.649010+05:30

ALTER SYSTEM SET remote_listener=' hostscan.localdomain.com:1521' SCOPE=MEMORY SID='DEV1';

2024-06-05T23:16:04.651212+05:30

ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='DEV1';

Flashback Media Recovery Log +RECO/DEV/ARCHIVELOG/2024_05_26/thread_2_seq_1.306.1169976533

2024-06-05T23:16:05.142464+05:30

Incomplete Recovery applied until change 1550263 time 05/26/2024 09:12:31

2024-06-05T23:16:05.146121+05:30

Flashback Media Recovery Complete

2024-06-05T23:16:05.436014+05:30

Completed: flashback database to restore point BEFORE_UPGRADE

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

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

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [oracle] ? DEV1

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

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

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 5 23:30:27 2024

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 5 23:36:39 2024

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

Database opened.

SQL>

SQL> column comp_name format a40

coluSQL> mn version format a12

column staSQL> tus format a15

select comp_name,SQL> version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 12.2.0.1.0 VALID

Oracle Database Packages and Types 12.2.0.1.0 VALID

JServer JAVA Virtual Machine 12.2.0.1.0 VALID

Oracle XDK 12.2.0.1.0 VALID

Oracle Database Java Packages 12.2.0.1.0 VALID

OLAP Analytic Workspace 12.2.0.1.0 VALID

Oracle Real Application Clusters 12.2.0.1.0 VALID

Oracle XML Database 12.2.0.1.0 VALID

Oracle Workspace Manager 12.2.0.1.0 VALID

Oracle Text 12.2.0.1.0 VALID

Oracle Multimedia 12.2.0.1.0 VALID

COMP_NAME VERSION STATUS

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

Spatial 12.2.0.1.0 VALID

Oracle OLAP API 12.2.0.1.0 VALID

Oracle Label Security 12.2.0.1.0 VALID

Oracle Database Vault 12.2.0.1.0 VALID

15 rows selected.

SQL> exit

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl downgrade database -d DEV -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -targetversion 12.2.0.1.0

[oracle@hostnode1 ~]$ env |grep ORA

ORACLE_SID=DEV1

ORACLE_BASE=/u01/app/oracle

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ srvctl config database -d DEV

Database unique name: DEV

Database name: DEV

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

Oracle user: oracle

Spfile: +DATA/DEV/PARAMETERFILE/spfile.319.1169935561

Password file: +DATA/DEV/PASSWORD/pwddev.269.1169935335

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: RECO,DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: DEV1,DEV2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 ~]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 00:35:43 2024

Connected to an idle instance.

SQL>

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initDEV1.ora_backup';

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

SQL> create spfile='+DATA/DEV/PARAMETERFILE/spfileDEV.ora' from pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initDEV1.ora_backup';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@hostnode1 dbs]$ srvctl modify database -d DEV -spfile '+DATA/DEV/PARAMETERFILE/spfileDEV.ora'

[oracle@hostnode1 dbs]$ srvctl start database -d DEV

[oracle@hostnode1 dbs]$ srvctl status database -d DEV

Instance DEV1 is running on node hostnode1

Instance DEV2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ . oraenv

ORACLE_SID = [DEV1] ? TEST1

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

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl status database -d TEST

Instance TEST1 is running on node hostnode1

Instance TEST2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

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

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$ srvctl stop database -d TEST

[oracle@hostnode1 dbs]$ env |grep ORA

ORACLE_SID=TEST1

ORACLE_BASE=/u01/app/oracle

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 6 00:57:52 2024

Version 19.17.0.0.0

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area 3690985856 bytes

Fixed Size 8903040 bytes

Variable Size 1191182336 bytes

Database Buffers 2483027968 bytes

Redo Buffers 7872512 bytes

RMAN> exit

Recovery Manager complete.

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 6 01:00:19 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> set pages 1000 lines 1000

col TIME for a35

col RESTORE_POSQL> SQL> INT_TIME for a30

col NAME for a3SQL> 0

select SCN,DATABASE_INCARNATIOSQL> N#,GUARANTEE_FLASHBACK_DATABASE,TIME,RESTORE_POINT_TIME,NAME,PDB_RESTORE_POINT from v$restore_point;

SCN DATABASE_INCARNATION# GUA TIME RESTORE_POINT_TIME NAME PDB

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

1565819 2 YES 26-MAY-24 09.50.46.000000000 AM GRP_1716697245868 NO

SQL> exit

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

Version 19.17.0.0.0

[oracle@hostnode1 dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 6 01:00:39 2024

Version 19.17.0.0.0

connected to target database: TEST (DBID=2468082526, not open)

RMAN>

RMAN> flashback database to restore point GRP_1716697245868;

Starting flashback at 06-JUN-24

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=39 instance=TEST1 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file +DATA/TEST/ARCHIVELOG/2024_05_26/thread_1_seq_3.324.1169978063

archived log for thread 2 with sequence 1 is already on disk as file +DATA/TEST/ARCHIVELOG/2024_05_26/thread_2_seq_1.321.1169977865

media recovery complete, elapsed time: 00:00:03

Finished flashback at 06-JUN-24

RMAN>

[oracle@hostnode1 ~]$ cd /u01/app/oracle/diag/rdbms/test/TEST1/trace/

[oracle@hostnode1 trace]$ tail -f alert_TEST1.log

Flashback Media Recovery Start

Started logmerger process

2024-06-06T01:01:33.460764+05:30

Parallel Media Recovery started with 2 slaves

2024-06-06T01:01:33.613604+05:30

ALTER SYSTEM SET remote_listener='hostscan.localdomain.com:1521' SCOPE=MEMORY SID='TEST1';

2024-06-06T01:01:33.615983+05:30

ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='TEST1';

2024-06-06T01:01:40.947588+05:30

Flashback Media Recovery Log +DATA/TEST/ARCHIVELOG/2024_05_26/thread_1_seq_3.324.1169978063

2024-06-06T01:01:42.005664+05:30

Flashback Media Recovery Log +DATA/TEST/ARCHIVELOG/2024_05_26/thread_2_seq_1.321.1169977865

2024-06-06T01:01:42.372236+05:30

Incomplete Recovery applied until change 1565820 time 05/26/2024 09:50:53

2024-06-06T01:01:42.376499+05:30

Flashback Media Recovery Complete

Completed: RMAN flashback database to before scn 1565820 in incarnation 2

RMAN> shutdown immediate;

database dismounted

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ . oraenv

ORACLE_SID = [TEST1] ?

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

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ env |grep ORA

ORACLE_SID=TEST1

ORACLE_BASE=/u01/app/oracle

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

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

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/bin/srvctl downgrade database -d TEST -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -targetversion 12.2.0.1.0

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

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

Oracle user: oracle

Spfile: +DATA/TEST/PARAMETERFILE/spfileTEST.ora

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:05:55 2024

Connected to an idle instance.

SQL>

SQL> create pfile from spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora';

File created.

SQL> exit

Disconnected

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl modify database -d TEST -spfile ''

[oracle@hostnode1 dbs]$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

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

Oracle user: oracle

Spfile:

Password file: +DATA/TEST/PASSWORD/pwdtest.315.1169935897

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: oinstall

OSOPER group: oinstall

Database instances: TEST1,TEST2

Configured nodes: hostnode1,hostnode2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@hostnode1 dbs]$

[oracle@hostnode1 trace]$ asmcmd -p

ASMCMD [+] > asmcmd -p

ASMCMD [+] > cd +DATA/TEST/PARAMETERFILE

ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l

Type Redund Striped Time Sys Name

PARAMETERFILE UNPROT COARSE JUN 06 01:00:00 Y spfile.274.1169981285

PARAMETERFILE UNPROT COARSE JUN 06 01:00:00 N spfileTEST.ora => +DATA/TEST/PARAMETERFILE/spfile.274.1169981285

ASMCMD [+DATA/TEST/PARAMETERFILE] > rm spfile.274.1169981285

ASMCMD [+DATA/TEST/PARAMETERFILE] > ls -l

ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA/TEST/'

ASMCMD [+DATA/TEST/PARAMETERFILE] >

ASMCMD [+DATA/TEST] > exit

[oracle@hostnode2 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:16:49 2024

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initTEST1.ora';

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

SQL> create spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora' from pfile='/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initTEST1.ora';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ cat > initTEST1.ora

spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora'

[oracle@hostnode1 dbs]$ pwd

/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs

[oracle@hostnode1 dbs]$

[oracle@hostnode2 dbs]$ cat > initTEST2.ora

spfile='+DATA/TEST/PARAMETERFILE/spfileTEST.ora'

[oracle@hostnode2 dbs]$

[oracle@hostnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:20:13 2024

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 3690987520 bytes

Fixed Size 8627200 bytes

Variable Size 905972736 bytes

Database Buffers 2768240640 bytes

Redo Buffers 8146944 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

[oracle@hostnode1 dbs]$

[oracle@hostnode1 dbs]$ srvctl start database -d TEST

[oracle@hostnode1 dbs]$ srvctl status database -d TEST

Instance TEST1 is running on node hostnode1

Instance TEST2 is running on node hostnode2

[oracle@hostnode1 dbs]$

[oracle@hostnode1 ~]$ . oraenv

ORACLE_SID = [TESt1] ? TEST1

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

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

[oracle@hostnode1 ~]$

[oracle@hostnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 6 01:32:38 2024

Connected to:

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

SQL> column comp_name format a40

column version format a12

columSQL> SQL> n status format a15

select comp_SQL> name,version,status from dba_registry;

COMP_NAME VERSION STATUS

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

Oracle Database Catalog Views 12.2.0.1.0 VALID

Oracle Database Packages and Types 12.2.0.1.0 VALID

JServer JAVA Virtual Machine 12.2.0.1.0 VALID

Oracle XDK 12.2.0.1.0 VALID

Oracle Database Java Packages 12.2.0.1.0 VALID

OLAP Analytic Workspace 12.2.0.1.0 VALID

Oracle Real Application Clusters 12.2.0.1.0 VALID

Oracle XML Database 12.2.0.1.0 VALID

Oracle Workspace Manager 12.2.0.1.0 VALID

Oracle Text 12.2.0.1.0 VALID

Oracle Multimedia 12.2.0.1.0 VALID

COMP_NAME VERSION STATUS

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

Spatial 12.2.0.1.0 VALID

Oracle OLAP API 12.2.0.1.0 VALID

Oracle Label Security 12.2.0.1.0 VALID

Oracle Database Vault 12.2.0.1.0 VALID

15 rows selected.

SQL> exit

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

[oracle@hostnode1 ~]$

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

Friday, June 21, 2024

Flashback database to Guarantee Restore Point or Restore Point after Upgrade from 12c to 19c

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