Category: NOT a DBA

Oracle EBS R12 Vision Instance

On By windows7bugsIn NOT a DBA, Oracle Applications(R12)Leave a comment

Did you ever wish for having your own copy of Oracle EBS R12? Well, Oracle provides a copy that is all yours called VISION instance. The below post is one of the best I came across, explaining how to setup your own VISION instance on Oracle VirtualBox

Get your own Oracle EBS R12.2 Instance On VirtualBox

I hope, it was an easy ride. Oracle’s virtual appliance is perfectly sized for moderate hardware, without GUI out of the box. We’ll see how to install GUI, configure the appliance for network access etcetera in this post.

Basically you get Oracle Linux 7.9 OS, EBS R12 12.2.10 with Oracle database 19.9 in this appliance. You must update the OS to latest before installing GUI. So, let us setup the environment for the update now.

Stop the running application and database instances.

su - oracle
cd /u01/install/APPS/scripts
./stopapps.sh
./stopdb.sh

Oracle has the appliance set to start the database and application instances automatically during every reboot. I strongly suggest you to disable the automatic start of these instances. Oracle starts “ebscdb” and “apps” services every time when the box restarts. We can disable these services using chkconfig command

chkconfig apps off
chkconfig ebscdb off

That’s it. Now, when you restart the box, Oracle services will not start automatically.

Shutdown the server. Change the network configuration for the sever like seen in the below image.

This means, your server will start sharing the host’s active connection(s). If your host machine has an active internet connection, your EBS server will able to access internet this way from it’s environment.

Start the EBS server and complete the OS update. This should be around 2.2GB total downloads and minimum 30-40 minutes, depending upon the hardware resources you have allocated for the virtual machine.

Once the box is update, proceed with “Server with GUI” group installation.

yum groupinstall "Server with GUI"

Restart the server after GUI installed. You can temporarily switch to graphical run level by issuing the command

systemctl isolate graphical.target

and to make the graphical interface as default, issue the below command as root

systemctl set-default graphical.target

Please note, your box doesn’t have VirtualBox extensions yet, which is a MUST for proper mouse integration and more. Prior installing the VirtualBox extensions, make sure to install kernel devel package. Issue the following command

yum install kernel-uek-devel

Additionally install the packages gcc, make & perl (pre-installed usually)

yum install gcc make perl -y

Reboot the server & install the VirtualBox extensions.

If you remember, we changed the network settings earlier for the update & if you try to start the database and application, this may not work. Depending upon how you want to access the VISION instance, you have to configure additional networking now. I suggest to leave the NAT configuration as it is and to configure an additional Network adapter for the intended connectivity.

For example, if you want to share the VISION instance with your teammates within a Class C network, do the following.

Use “Bridged Adapter” & select the adaptor that is connected to the network under “Name”. Recent computers have multiple network and WiFi adapters & selecting the correct adapter is very important for client connectivity. Make sure to setup “Promiscous Mode” to “Allow All”.

Find one free IP address and set up the same for your EBS VISION box. You will have to setup this information at multiple places.

For example, change the hosts file (most important)

Network settings. My box have NAT as first Network Adapter and Host Only network as 2nd Network Adapter. Hence my configurations are like below

For the NAT (Connection #1) I have the following setup

and for the Host Only network (Connection #2)

By having the NAT as first adapter, my box is able to connect to internet, shared from the host and my host can access the EBS instance from the virtual machine using the host only network.

That’s all folks.

Install Oracle Forms & Reports 12.2.1.19 on Windows

On By windows7bugsIn NOT a DBA, Oracle Forms, Weblogic, Windows 10, Windows 11Leave a comment

No post should look good, started with a statement asking the visitor to move on because “There is nothing much to see here”. Trust me, if you already referred my previous post(s) for Oracle Forms & Reports 12.2.1.4, then there is nothing new I have for you in this post. As I don’t have much to offer other than what I have already did through my previous post, let us see how we can insure your fresh attempt to get this whole complex stack installed & configured successfully on your computer.

Are you excited? Don’t be. Oracle has released the much waited Windows version for their Oracle Forms & Reports couple of days back and I must say, I was super excited based on the promises given about the IDE improvements. For improvements what I could see what line numbers inside PL/SQL editor.

Software Requirements

Side note before anything else. Please note, you must install software from the same architecture. Said, if you install JDK 32-Bit and expect your 64-Bit Weblogic to work properly, that is not going to happen. Hence insure all the software you download and install belong to the same architecture.

Microsoft Visual C++ Redistributable packages (Specific to Weblogic 12.2)

and for Oracle Forms 12.2.1.19, you have to install Visual Studio 2017 VC++ compoments.

Oracle JDK

Weblogic Server Software

Oracle Forms & Reports 12.2.1.19

Before you start the installation, let’s do a checklist

  • Is your Windows machine updated with latest patches for OS and .Net?
  • Do you have a static IP address for your computer/laptop?
  • What is the name of your computer? does it looks like some random letters?
  • Have your read the installation document? :) nobody does it right?

Legal, cracked, pirated… OS comes in different flavors & nothing can stop it. However, keeping your box up to date is your responsibility. Oracle is a very complex technology, that depends upon many OS elements. A broken box will not get you anywhere.

Static IP address is 2nd most important element when you are installing a server software, that is WebLogic in our case. As this server has to be identified and reached by clients, a fixed IP address is a must, however not always possible when a user has limited infrastructure. Microsoft has provided “Microsoft Loopback” interface for such requirements. Please Google and find more about setting it up, if you are one of those users. Microsoft Loopback helps you to setup a static IP address, please note other computers in your network cannot reach your computer using this IP address or setup!

Name of your computer or HOST name. Much of the Computer/Laptop that you buy with Windows preinstalled come with some whacky names. You should change such names to something more meaningful like “JOHN-PC”,”XYZ” etecetra.

I didn’t either read installation manuals until recent times. I realized that, I could avoid hours or sometimes days long Googling merely by reading the instruction manuals.

Once you are done with Microsoft Visual Studio C++ redistributable package(s) installations, we can proceed. Please follow the below post that should take you through installation and setting up a classic weblogic domain, report server instance etc. I hope you will have least issues installating and configuring Oracle Forms & Reports following my post 🙏(Don’t get discouraged by the title of the referred post, as I stated in the beginning, there is nothing new HERE!). The one thing I want to warn you about the time that your computer might take to bring up the UI for the Oracle Forms & Reports installer. This could be nothing less that 7-8 minutes, much more depending upon your hardware resources.

Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4

All the best and if you are still stuck, do let me through comments!

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration]

On By windows7bugsIn NOT a DBA, Oracle Database 19cLeave a comment

I do loads of labs & after upgrading a cloned instance of EBS R12 (12.2) database 19c (19.14) to 19.16 & started noticing the error “ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration]” being logged. I tried to figure out what went wrong after the upgrade & after referring dozens of blog posts and Oracle documents, couldn’t fix this error.

Then, I configured this VM for Oracle ORDS & broke the system by applying few updates that overwritten few mandatory packages for Oracle EBS, forcing me to wipe the system and fresh installation of Oracle Linux 7.9

After applying the prerequisites, I brought up the cloned instance once again & decided to go by the book. After the database clone, I didn’t forget to apply “root.sh” from $ORACLE_HOME/bin folder as “root” this time.

If you are ever coming across this error, please ensure the folder/file accesses ($ORACLE_HOME/bin) are like the images below. Running “root.sh” fixes much of these access rights.

“oradism“

“extjob” & “jssu“

This should mostly address the VKTM related issues. If not, please proceed with further measures. If interested refer these documents/posts.

https://asanga-pradeep.blogspot.com/2021/07/ora-00800-soft-external-error-arguments.html

ORA-00800: Soft External Error, Arguments: [Set Priority Failed], [VKTM] [Check traces and OS configuration] (Doc ID 2551359.1)

Hope this helps few out there! Merry Christmas and a Happy New Year in advance!

Microsoft SQL Server | CREATE FILE encountered operating system error 5(Access is denied.)

On By windows7bugsIn .net programming, NOT a DBA, T-SQLLeave a comment

You might come across this error when trying to attach an existing MDF file. 

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Microsoft SQL Server\MSSQL\Data\AM8K.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5123-database-engine-error

------------------------------
BUTTONS:

OK
------------------------------

Please do the following:

Make sure currently logged in Windows/Domain user has access to the physical folder where the MDF file resides.

Start “Microsoft SQL Server Management Studio” as administrator.

Now, try to attach the MDF file, this should resolve the Access Denied error.

Oracle Database 19c | Rollback Patch

On By windows7bugsIn NOT a DBA, Oracle Database 19cLeave a comment

Recently I updated one of my posts that discussed about applying patches to Oracle 19c instance, that has minimum one PDB. This time we will discuss about how to rollback a patch. For the exercise I will be rolling back patch number 34110685 “Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)”

Please note, to install patch 34110685 you must have opatch 12.2.0.1.30 or higher. The same applies for rolling back also.

Use opatch to find out the latest patch details.

D:\Oracle19c\OPatch>opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\Oracle19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.32
OUI version       : 12.2.0.7.0
Log file location : D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_10-59-45AM_1.log

Lsinventory Output file location : D:\Oracle19c\cfgtoollogs\opatch\lsinv\lsinventory2022-09-15_10-59-45AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: ora.abc.com
ARU platform id: 233
ARU platform description:: Microsoft Windows (64-bit AMD)


Installed Top-level Products (1):

Oracle Database 19c                                                  19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  34110685     : applied on Sun Aug 14 12:02:50 AST 2022
Unique Patch ID:  24797704
Patch description:  "Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)"
   Created on 28 Jul 2022, 09:34:43 hrs UTC
   Bugs fixed:
     34208548, 34264633, 34291960, 34293849, 34311758, 30151886, 31649223
     32012137, 32079097, 32223654, 33115620, 33182177, 33324055, 33360476
     33390342, 33423383, 33510227, 33899902, 33907346, 33957025, 33964258
     34034279, 34088985, 34088989, 34110342, 34132318, 34147169, 34162335,.......



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

OPatch succeeded.

Once we have the latest patch details, shutdown all running Oracle instances and stop Windows services for Oracle (listener & database services)

D:\Oracle19c\OPatch>opatch rollback -id 34110685
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\Oracle19c
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.32
OUI version       : 12.2.0.7.0
Log file location : D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_11-02-48AM_1.log


Patches will be rolled back in the following order:
   34110685
The following patch(es) will be rolled back: 34110685

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'D:\Oracle19c')


Is the local system ready for patching? [y|n]
y
User Responded with: Y

Rolling back patch 34110685...

RollbackSession rolling back interim patch '34110685' from OH 'D:\Oracle19c'

Patching component oracle.has.common.cvu, 19.0.0.0.0...

Patching component oracle.has.rsf, 19.0.0.0.0...

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

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.has.common, 19.0.0.0.0...

Patching component oracle.rdbms.util, 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.dbjava.jdbc, 19.0.0.0.0...

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

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

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

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

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

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

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

Patching component oracle.ntoledb.odp_net_2, 19.0.0.0.0...

Patching component oracle.has.db, 19.0.0.0.0...

Patching component oracle.ntoramts, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

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

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

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

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

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

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

Patching component oracle.duma, 19.0.0.0.0...

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

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

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

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

Patching component oracle.sdo, 19.0.0.0.0...

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

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

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

Patching component oracle.oraolap, 19.0.0.0.0...

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

Patching component oracle.ntoledbolap, 19.0.0.0.0...

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

Patching component oracle.ntoledb, 19.0.0.0.0...

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

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

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

Patching component oracle.ovm, 19.0.0.0.0...

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

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

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.clrintg.ode_net_2, 19.0.0.0.0...

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

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

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

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

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

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

Patching component oracle.aspnet_2, 19.0.0.0.0...

Patching component oracle.usm.deconfig, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

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

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

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.oracler.server, 19.0.0.0.0...

Patching component oracle.rdbms.plsql, 19.0.0.0.0...

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

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

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

Patching component oracle.rdbms.olap, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

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

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

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

Patching component oracle.jdk, 1.8.0.201.0...
RollbackSession removing interim patch '34110685' from inventory
Inactive sub-set patch [33575656] has become active due to the rolling back of a super-set patch [34110685].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: D:\Oracle19c\cfgtoollogs\opatch\opatch2022-09-15_11-02-48AM_1.log

OPatch succeeded.

Restart all Windows services for Oracle & startup the database instance.

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:07:07 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294965408 bytes
Fixed Size                  9275552 bytes
Variable Size            1207959552 bytes
Database Buffers         3070230528 bytes
Redo Buffers                7499776 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

Now we should apply the data patch to the database. 

D:\Oracle19c\OPatch>datapatch.bat -verbose
SQL Patching tool version 19.14.0.0.0 Production on Thu Sep 15 11:09:47 2022
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: D:\Oracle19c\cfgtoollogs\sqlpatch\sqlpatch_1148_2022_09_15_11_09_47\sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.14.0.0.0 Release_Update 211229195225: Installed
  PDB CDB$ROOT:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.13.440000 PM
  PDB SCT:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.16.841000 PM
  PDB PDB$SEED:
    Applied 19.16.0.0.0 Release_Update 220717095735 successfully on 14-AUG-22 12.38.15.127000 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED SCT
    No interim patches need to be rolled back
    Patch 34110685 (Windows Database Bundle Patch : 19.16.0.0.220719 (34110685)):
      Rollback from 19.16.0.0.0 Release_Update 220717095735 to 19.14.0.0.0 Release_Update 211229195225
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 34110685 rollback (pdb CDB$ROOT): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_CDBROOT_2022Sep15_11_10_47.log (no errors)
Patch 34110685 rollback (pdb PDB$SEED): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_PDBSEED_2022Sep15_11_13_25.log (no errors)
Patch 34110685 rollback (pdb SCT): SUCCESS
  logfile: D:\Oracle19c\cfgtoollogs\sqlpatch\34110685\24797704/34110685_rollback_SCTCD_SCT_2022Sep15_11_13_26.log (no errors)
SQL Patching tool complete on Thu Sep 15 11:16:25 2022

Now we have to recompile database components.

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:24:39 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


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

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:24:49

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:24:50

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> alter session set "_oracle_script"=TRUE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ WRITE;

Pluggable database altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:25:56

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:25:58

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

Pluggable database altered.

SQL> alter session set "_oracle_script"=FALSE;

Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb$seed OPEN READ ONLY;

Pluggable database altered.

SQL> alter session set container="SCT";

Session altered.

SQL> @?/rdbms/admin/utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2022-09-15 11:27:44

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2022-09-15 11:27:46

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  3

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> shutdown immediate
Pluggable Database closed.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

D:\Oracle19c\OPatch>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:28:27 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

That’s all folks. My PDB SCT has 3 objects in error and they are custom objects created by us & not anymore required for the application.

I hope this gives a fair about how to rollback an applied patch from Oracle database. As my database is pretty small with limited number of objects, I didn’t face any issues. This may not be the case when you are dealing with a huge PRODUCTION database. Insure as many backups are taken prior you rollback a PRODUCTION instance.

Linux HugePages for Oracle

On By windows7bugsIn Linux, NOT a DBA, Oracle database 11g, Oracle database 12c, Oracle Database 19cLeave a comment

Few weeks back one DBA told me there is no need to setup HugePages as we are not using 40+GB SGA & we had it enabled from the initial days of 11g migration from 10g (10.2.0.3). Well, here is how you can setup HugePages for your Oracle database instance.

A brief explanation that might help you to decide whether you should enable HugePages or not is quoted from Tim Hall’s article below.

“For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle 11g systems running on x86-64 architectures.“

Recently I had a short Twitter exchange with Tim Hall about the subject & He mentioned that he always enables it.

Please refer the article from below link, it is by Tim Hall, a geek in this field and the owner of https://oracle-base.com

https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64

Exercises explained in this article is applicable to 19c also (verified as working). Please make sure that your version of Oracle database supports these settings before attempting. Please do not copy this to PRODUCTION environments without enough testing.

TNS-03505: Failed to resolve name

On By windows7bugsIn NOT a DBA, Oracle database 12c, Oracle Database 19cLeave a comment

One of the major annoyances with Oracle EBS CDB/PDB environment settings I found that, I cannot connect to the PDB (for EBS) from the CDB environment (default). I always have to source the PDB environment in order to connect the PDB and do my stuffs.

As usual, I am doing this for my LAB & you may not copy this to your PRODUCTION environment.

So this what I get when I try to connect to PDB from CDB environment.

oracle@erp-devp.xyz.com:/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin>tnsping DEVP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 14:59:03

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora

TNS-03505: Failed to resolve name

The easiest solution was to add an additional entry for the PDB within the $TNS_ADMIN tnsnames.ora file. Let us see how to. Please note these might get overwritten when you run autoconfig.

Save the file & you are ready to go. Notice carefully the service name and instance name values for “DEVP” and “DEVPCDB”. The PDB will have own name for the service, however the instance name will point towards the CDB name & this is the way it should be.

oracle@erp-devp.xyz.com:/home/oracle>tnsping DEVP

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-MAR-2022 15:06:20

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/oratest/DEVP/db/tech_st/19.0.0/network/admin/DEVP_erp-devp/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL=tcp)(HOST=erp-devp.xyz.com)(PORT=1526)) (CONNECT_DATA = (SERVICE_NAME=DEVP)(INSTANCE_NAME=DEVPCDB)))
OK (0 msec)
oracle@erp-devp.xyz.com:/home/oracle>

That’s all. Regardless whether it is EBS or not, you can keep adding the PDB details into the tnsnames.ora file & if the service is registered with the listener, will able to connect without sourcing the specific environment variables.

ORA-12560: TNS:protocol adapter error

On By windows7bugsIn NOT a DBA, Oracle database 11g, Oracle database 12c, Oracle Database 19cLeave a comment

Few “years” back, while it was almost impossible to install Oracle 10g on Windows 7/8, I made some attempts & managed to install 32Bit versions of 10g 10.2.0.3.x successfully. A particular release 10.2.0.4 had helluva of issues, mainly TNS related & I can see many visits to that thread even today! I believe, those visitors are not coming over there for 10g, instead for TNS.

On Windows, the below is a typical situation & we’ll see the primary reasons for the same. 

Microsoft Windows [Version 10.0.22000.556]
(c) Microsoft Corporation. All rights reserved.

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 21 07:59:29 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

On Windows, everything is another service. So we’ll start with checking whether the “Oracle” service for the database instance has started.

I’m using Oracle database on my laptop mostly for learning purposes, hence the service startup is set as “Manual”. This gives me control over the resources consumed by the database. Not just the service, I have setup the instance to start manual to insure Oracle service doesn’t open the database automatically. Latest Windows OS that use quick boot will save the state of the service, hence you might find your database readily available once after a reboot, although you set everything as manual :)

Let’s see what happens after starting the “Oracle Service”.

Well, that fixes one of the many reasons for the dreaded ORA-12560: TNS:protocol adapter error! As soon I am hit with another, will amend this post.

Restore RMAN backup to same host with same database name | Windows

On By windows7bugsIn NOT a DBA, Oracle Database 19cLeave a comment

I think I have mentioned few times already that I am not a certified (Certifitted as we call it sarcastically due to the pure ignorance of much of them) DBA, yet I love executing experiments with Oracle database technology and come up with some hacks, tricks etc. As usual, whatever you read ahead is NOT recommended for PRODUCTION environment and if you end up setting up something that works finally for your PRODUCTION instance, well you are sole responsible for it.

For this experiment, we will use one 19c database with multitenant structure installed on Windows 10/11. Before you attempt the below, please make sure that you have taken the backup of control file trace, spfile copy as pfile and a full backup of database using RMAN

Control file backup trace.

SQL>alter database backup controlfile to trace as 'D:\folder_name\control.txt';

SPfile to Pfile (This file should be available at $ORACLE_HOME\database\ folder)

SQL>create pfile from spfile;

Creating pfile is not mandatory incase if you are restoring the backup to same host and the paths are intact. Pfile becomes useful when you are restoring the database to another host with same directory structure.

Shutdown the database & delete the datafile from paths. For example, my datafiles were in the following paths.

There were three paths from which I needed to delete the data files. From CDB root, PDB seed and PDB itself. You should get a clear idea of the exact locations for your data files from the control file trace export.

Once after deleting the files, start the database at nomount state. The latest version of databases allow you to execute many SQL commands from RMAN itself, however, I always prefer SQL prompt for SQL activities. Suite yourself.

SQL>startup nomount

From another command/terminal window connect to RMAN

D:\RMAN>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 12 11:47:15 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SCTCDB (not mounted)

Restore the control file from latest backup

RMAN> restore controlfile from 'D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312';

Starting restore at 12-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=498 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL01.CTL
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL02.CTL
Finished restore at 12-MAR-22

Now try to mount the database.

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

Once the database mounted, let us see what the schema reprorts

RMAN> report schema;

Populate catalog from the backup.

RMAN> catalog start with 'D:\RMAN';

searching for all files that match the pattern D:\RMAN

List of Files Unknown to the Database
=====================================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312

Once the above completes successfully, we can proceed with restoring the backup.

RMAN> run
2> {allocate channel ch1 device type disk;
3> restore database;
4> switch datafile all;
5> switch tempfile all;
6> release channel ch1;
7> }

allocated channel: ch1
channel ch1: SID=252 device type=DISK

Starting restore at 12-MAR-22

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00004 to D:\ORACLE19C\DB\ORADATA\SCTCDB\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00005 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00009 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00011 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00010 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00007 to D:\ORACLE19C\DB\ORADATA\SCTCDB\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00008 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00012 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:02
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00006 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:35
Finished restore at 12-MAR-22



released channel: ch1

With the above restore, I used only one channel, please adjust based on available hardware resources. Once the backup completes, we can recover the database using the available archive logs. For that you need to know what is the latest available sequence number.

RMAN> list backup of archivelog all;

From the image above, you could see that the latest Sequence number for the SCN is 20, however the listing is kind of confusing (remember I am not a DBA, however eventually I will figure out why the listing is messed up). Once the latest sequence number identified, we can try to recover until last sequence + 1

RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> allocate channel ch3 device type disk;
5> allocate channel ch4 device type disk;
6> recover database until sequence 21;
7> release channel ch1;
8> release channel ch2;
9> release channel ch3;
10> release channel ch4;
11> }

allocated channel: ch1
channel ch1: SID=252 device type=DISK

allocated channel: ch2
channel ch2: SID=128 device type=DISK

allocated channel: ch3
channel ch3: SID=375 device type=DISK

allocated channel: ch4
channel ch4: SID=499 device type=DISK

Starting recover at 12-MAR-22

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC
archived log for thread 1 with sequence 20 is already on disk as file D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC
archived log file name=D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC thread=1 sequence=19
archived log file name=D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-22

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

Finally, we can open the database resetting online redo log files

RMAN> alter database open resetlogs;

Database MUST open without creating any issues at this stage. Continue checking for the database vitals.

Oracle Application | “apps” user account is locked!

On By windows7bugsIn NOT a DBA, Oracle Applications(R12), Oracle relatedLeave a comment

Today while running automatic configuration on the Application Tier, I entered the “apps” user password wrong! After the automatic configuration completed, having errors, I couldn’t start the Application tier.

It just said invalid password or database is not open.

Later after going through adautoconfig log files, I could see a number of failed connection attempts stating the account was locked, however I least expected this account to be “apps” (issues that one runs into when not properly exposed to the stack)

I opened SQL from application user and tried to login as apps/apps & was told the account was locked. I unlocked the apps account and ran the autoconfig once again, this time supplying the correct password. Everything went smooth and I was able to start the Application Tier.

So this is what happened. I supplied a wrong password for autoconfig & consecutive attempts to connect database from different scripts used the same credentials & after 10 failed attempts the account got locked up! causing rest of the issues.

So, be very careful when you are supplying apps password to scripts. As I am not an APPS DBA, it took me a while to figure this one out!

Optionally you may apply a better solution as mentioned here. Regardless, just be careful when you are supplying “apps” user password