EBS R12 Cloned Instance | opatch

Not being a DBA has it’s own perks ;) Freedom to try to fix everything that do not look “legit” from all angles.

My last attempt was to rebuild “Central Inventory” for a cloned instance of Oracle Applications R12 (EBS 12.0.6) on Linux. This instance was cloned from a certified platform, to a totally unsupported platform. In addition to, the DBAs those migrated from 10g R2 to 11g R2 left much of the post clone activities pending immediately once after the instance came online.

So, trying to patch Oracle database 11gR2(11.2.0.4) brought me to a situation that never existed:

  • “opatch” was “not” recognized as a command!
  • “opatch lsinventory” run always screamed about corrupt/missing central inventory location.

The “opatch” not being recongized as a command was due to $ORACLE_HOME/OPatch path missing, easily fixed by adding the path to the existing $PATH environment variable for the Oracle user.

One of the other issues you might face up with 11.2.0.4 could be the unsupported opatch version. You need to replace the current opatch version with the latest supported. Please refer to patch #6880880 & Install the patch marked in the below image

Please read the “README” to know how to replace the existing version with the latest supported.

Once the opatch stack taken care of, we still need to fix the central inventory, that keeps reported as being missing.

[oratest@bak clone]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/TEST/db/tech_st/11.2.0/inventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-35-04AM_1.log
 LsInventorySession failed: OPatch failed to locate Central Inventory.
 Possible causes are:
     The Central Inventory is corrupted
     The oraInst.loc file specified is not valid.
 OPatch failed with error code 73

Let us see how to rebuild the Central Inventory for the EBS R12 cloned instance now.

If your current server (the one that has the cloned instance of the application) never had an Oracle installation executed from the same, will not have /etc/oraInst.loc file, instead the file must be available under $ORACLE_HOME & the file MUST BE pointing towards the source server paths. For example, my cloned instance oraInst.loc contents are like below:

>cat oraInst.loc
 inventory_loc=/u05/oraprod/oraInventory
 inst_group=oinstall

where inventory_loc path wrong for the current server.

You may create something equivalent to what already exists with the oraInst.loc file, for example (in my case) a path like /u01/oratest/oraInventory as “Oracle” user.

So, you need to amend the oraInst.loc file that is available with $ORACLE_HOME path like below

>cat oraInst.loc
 inventory_loc=/u01/oratest/oraInventory
 inst_group=oinstall

Now you can proceed building the Central Inventory.

logon as Oracle user

SHUTDOWN THE DATABASE & ALL OTHER ORACLE DATABASE RELATED SERVICES

  1. source the environment
  2. switch to $ORACLE_HOME/appsutil/clone
  3. execute ouicli.pl (./ouicli.pl)

Now, We will attach the current ORACLE_HOME with the Central Inventory. Many things could go wrong here, so make sure that you refer the document “How to create, update or rebuild the Central Inventory for Applications R12” in case if you are stuck for reasons those are not mentioned in this post.

As Oracle user

switch to $ORACLE_HOME/oui/bin

Execute

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=""

Example:

./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \
 ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="TEST_Oracle_HOME"

If there are no issues, you should get ” ‘AttachHome’ was successful ” confirmation once after the installer finishes the execution.

Test opatch once again

>opatch lsinventory

The above should provide you an output like below:

[oratest@bak bin]$ opatch lsinventory
 Oracle Interim Patch Installer version 11.2.0.3.25
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/oraInventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    : 11.2.0.3.25
 OUI version       : 11.2.0.4.0
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-42-05AM_1.log
 Lsinventory Output file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2020-11-11_10-42-
 Local Machine Information::
 Hostname: bak.localdomain.com
 ARU platform id: 0
 ARU platform description::
 There are no Interim patches installed in this Oracle Home.
 
 OPatch succeeded.

That’s all folks. You can proceed with your patching for the database now! Hope this helps few certified DBAs out there ;)

rajesh

Install Oracle forms & reports 12.2.1.4 with Weblogic 12c 12.2.1.4

Hi guys

Last reviewed on: 13th September 2020.

This time we are going to see how we could install and configure Oracle forms & reports 12.2.1.4, the latest version using WebLogic Server 12c 12.2.1.4

Side Note: Kindly refer this document from Oracle https://docs.oracle.com/en/middleware/fusion-middleware/12.2.1.3/sysrs/system-requirements-and-specifications.pdf

The Visual C++ requirements mentioned for 12.2.1.3 should suffice 12.2.1.4 also, without which you will NOT able to startup OHS instance.

Prior Installing Oracle Server Software on your computer, please make sure that your computer has a proper name (hostname) like “JOHN-PC, RAJESH-PC, ORACLE-SERVER” etc instead of defaults like ‘DESKTOP-jyyytyuukkyjj’ after an installation of Windows. In addition, please make sure that your laptop/Desktop machine has a Static IP address for your computer.

As a rule of thumb, regardless whether you are going to install and configure Oracle software on LINUX or Windows operating systems, always update the OS to latest patch levels, prior you start with the Oracle software installation(s). I strongly recommend using Oracle VirtualBox based Virtual Machines for the lab & later you may move the tried out solution to your preferred environments (TEST, Development). I don’t recommend any of the suggestions I make through my blog posts to be applied to PRODUCTION environments without consulting a Professional support entity.

Download Software from Oracle

Fusion Middleware Infrastructure Installer (Weblogic)

https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html

Oracle Forms & Reports 12.2.1.4

https://www.oracle.com/middleware/technologies/forms/downloads.html

JAVA SE Development Kit 1.8

https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

Install JDK

I prefer installing JDK on C: drive & Usually my JDK is installed on C:\Java\JDK folder. The shortest name, the best to handle it later. Once the 64Bit JDK is installed, you need to setup either JAVA_HOME environment variable or add a new entry in to the system path. I prefer a system path modification than user path modification.

Okay, there are hell loads of Oracle software installed on my system. Don’t worry, I manage them. Just make sure the JDK installation path is on top of the list and you are good to go

Install Weblogic Server 12.2.1.4

It may take a while for the jar file to extract and start the installer. Please wait patiently. I was setting up the entire infrastructure using Windows 10 VM & due to the limited resources, hence the entire ordeal was kind of time taking this time for me.

You can safely skip the Auto Update

Naming Oracle installation folders is an art. The complex the folder structures, the complex later configurations would be. I usually prefer to install all my Oracle software on D: drive. Please feel comfortable to format the Oracle home as per your requirements. Maintaining the same format helps me to locate and sometimes replace files from previous installations.

Select “Fusion Middleware Infrastructure”

WebLogic or Middleware installation is NOT complex anymore on Windows 10 OS. There are no special hacks needs to complete the installation.

Install forms & reports 12.2.1.4

Extract the downloaded zip file & you MUST start the installer as “Administrator”.

We’ve to install the Forms and Reports stack on an existing Oracle Home. So please select the Oracle Home where you have installed “WebLogic” Server. Alternatively you can always install the stack on a different Oracle Home, which I never tried earlier.

Select “Forms and Reports Deployment”, as the last two versions of Forms & Reports allow you to install a Standalone Forms Builder.

Select the correct JDK Home

Please make sure that you have Visual C++ libraries pre-installed prior proceeding. If you don’t have the VC++ libraries installed, the OHS instance will not start & you might experience additional problems during installation also.

That completes the Forms & Reports 12.2.1.4 installation.

Create a REPO for the Classic Domain

To create a classic domain, you must have a repository available with the database. Weblogic 12c supports Oracle 11G 11.2.0.4 or later database for the repositories & my pick will be always 12c Database as the 11.2.0.4 is only accessible for customers with valid Oracle support. You may refer this document for a detailed “how to” towards installing Oracle 12c database.

From an elevated command prompt, switch to Oracle Home\oracle_common\bin folder & execute “rcu.bat”

Check the below image for details

Choose the defaults

Provide the connection parameters. If the database is on another server, you don’t have to install additional clients, connection parameters will take care of the connection. Refer the below image for details (Adjust according to your database setup)

My 12c Database already has a repository for Forms & Reports 12c 12.2.1.3, hence I created a new prefix “DEV1”. If you are creating a repo for the first time, you can use “DEV” or any other 3 letter combination (make sure you will able to remember it laters).

Select all the components as seen with the below image. The selected are mandatory, especially the Metadata Services.

Use the same password everywhere! Unless you are setting up a production instance, use the same password everywhere. Example, I use “Abc1234” for the schemas, Weblogic Admin account, node manager etc, which makes the administration much easier. For production, follow the Oracle standards.

That completes the repository creation for a Classic domain.

Create a Classic Domain for deploying forms & reports application

From an elevated command prompt, switch to Oracle Home\oracle_common\common\bin (eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin) & execute config.cmd, that will kick start the classic domain configuration wizard.

Select the defaults, unless you have specifics. We will be creating a new domain with the following example.

Refer the below images and select the same components for a successful domain creation for forms and reports.

As I mentioned earlier, use the same password here, so that you don’t have to worry about forgetting & recovering it at later stages.

We will setup the domain for production (never tried Development, so don’t know how exactly both differ from each other). Leave the JDK selection as it is.

Now, your earlier created repository will come in act. Refer the below image(s) and adjust as per your environment

Make sure all the Schema validations are successful. Next step select components as per the below image

Make sure the Server Group is filled the value as seen with the below image. Don’t miss it!

We’ll add an instance OHS now. Click on the add button and refer the below images for more details

Fill in the Listen Address with “localhost” and leave rest to defaults

That finishes the creation of your classic domain.

You should able to access the newly created WebLogic admin server through the URL http://localhost:7001/console

I’m hurrying to publish the POST without further delays, as I am once again caught up with multiple assignments & keeping up with promises are NOT something I am look up to at this time. However, I will add up the missing configurations for OHS and setting up the Forms and reports very soon an an annexure as early as possible!

19th June 2020

Today I installed & configured another Weblogic Server environment & realized that I was supposed to amend this post with more details, is a long due!

I chose a Container+PDB 12c database instance for my Weblogic Server this time. It was fun, finally learning few things about Container and pluggable databases & I am hoping to write another post about setting up Weblogic, Forms and Reports with such a setup in near future.

Now without wasting more time, let us see how to finalize the freshly configured classic domain for Oracle Forms and Reports deployment.

Switch to the your newly created domain’s bin folder from an elevated command prompt. Eg:

“D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin”. Usually I always installed Oracle software on D: drive & follow the same naming structure for the folders. It helps me to backup and restore configuration & other files that help me to finish setting up the environments faster.

Start the nodemanger now by issuing the command by executing the command “startnodemanger.cmd”

It may take few seconds to minutes until the node manager starts for the first time. Please remember, you shouldn’t close this command window, which will automatically stop the node manager.

Open another elevated command prompt window and switch to your classic domain’s bin folder. Start the Weblogic admin server by issuing the command “startWeblogic.cmd”

Okay, with node manager, 99.9% chances that you may not bite bullet. With Weblogic Admin server, this is not the case. For number reasons, the Server will try to start and give up in the middle. So don’t panic. If the Weblogic Admin Server is not coming up to a “RUNNING” status after extended period of waiting, go ahead and restart your computer. Start node manager and then try to start the weblogic admin server once again.

The initial startup could take up to 5-6 minutes (depending upon your hardware, this time could vary) & you should see a status like below

Remember not to close the window, which will immediately stop the Weblogic Admin Server. The status of the window will keep on updated against the activities happening at the server side. Once started, you can safely keep both node manager and Weblogic Admin Server CMD windows minimized.

You can logon to the Weblogic Admin server from http://localhost:7001/console

Once after successfully logging in, this is the window you are presented with. We’ve to configure both Forms & Report servers now, by trying to start them. This process will keep on adding many elements to your start menu. Let us see we can start Forms (WLS_FORMS) & Reports (WLS_REPORTS) servers now

Click on the “Control” tab, select both “WLS_FORMS” & “WLS_REPORTS” from servers and press the “Start” button. Once start button clicked, you can press the refresh button to monitor the status of the Servers. Do not forget to click the refresh button again to stop refreshing once after the servers show the status “Running”

It could take minutes for both the servers to reach the “Running” stage. Keep watching the Admin Server command window for unexpected errors. If you have followed all the steps as I’ve instructed throughout my thread, I can confidently say that you shouldn’t have a single hick up until this moment.

Once both servers are running, we can complete the OHS setup finally. Open another elevated command prompt. Switch to Oracle_Common\common\bin folder

Eg: D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin and start Weblogic Scripting Tool (wlst)

Now you can issue the command “ohs_updateInstances()” at the wlst prompt & once the configuration completed, exit wlst by issuing “exit()”

You can try to logon to Weblogic Enterprise Manager console and start the newly setup OHS instance now.

Logon to Weblogic Enterprise Manager from http://localhost:7001/em & click the carousal menu by the left hand side

and expand HTTP Server to view your newly created OHS instance. In our case “ohs1”. Click on “ohs1” to open the control panel for the instance

Click the Start up button to start the OHS instance & after showing you message window stating you cannot stop the starting/stopping process, OHS will start. It could take few seconds to minutes depending upon your hardware and available resources.

Give special attention to “Machine Name”.

Now we will see how to bring OHS instance in action by setting up the few other factors like node manager security, configuration files.

Logon to Weblogic console @ http://localhost:7001/console

Lock & Edit the domain security values, please refer the image below for details

nm

Provide a meaningful name for the node manager account and supply a password. I used the same password for the total 12c deployment, unless you are setting up a PRODUCTION instance, you may use the common password across the 12c instances for easy administration of objects when required. Shutdown the services (better). Once the node manager properties are changed, restart the WLS admin server. Please note, always stop all running servers (forms & reports) services prior shutting down the Admin server & I suggest you gracefully shutdown the Admin server by running “StopWeblogic.cmd” instead of using the Weblogic console.

Move to your Oracle Home folder. I have installed Weblogic server in D:\Weblogic\Middleware folder, hence my Oracle home folder is

D:\Weblogic\Middleware\Oracle_Home\

(Adjust your paths accordingly)

Edit forms.conf file in the folder D:\Weblogic\Middleware\Oracle_Home\forms\templates\config\

By default this template file comes with commented examples, like following

#<Location /forms/>
#        SetHandler weblogic-handler
#        WebLogicHost host
#        WeblogicPort port
#</Location>

You need to uncomment one of the blocks starting from <Location /forms> until </Location> & add properties specific to your deployment, for example, my computer’s name is “RAJESH-PC” and I am using default ports for both forms and reports applications, hence I have the properties set like following

<Location /forms/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9001
</Location>

Once altered, copy forms.conf to OHS instance “moduleconf” folder. I have copied the file to following location: (Alter according to your setup)

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now we will edit the configuration file for reports

Switch to “D:\Weblogic\Middleware\Oracle_Home\reports\conf” folder & edit “reports_ohs.conf” and add a block like following (alter according to your setup)

<Location /reports/>
SetHandler weblogic-handler
WebLogicHost HOME-THEATER
WebLogicPort 9002
</Location>

Copy the edited file to

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\moduleconf

Now the OHS instance know about the deployments and once after restarted, you can try to start the forms based application through the OHS instance, rather than calling it against the default port (9001)

Once the OHS instance is updated with the new information, try to start the OHS instance from a command prompt using storeUserConfig parameter

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1 storeUserConfig

ohs_manually

1st run, you will be prompted for node manager password, supply the password you have setup earlier and OHS instance should start without creating a fuzz in usual scenarios

In future if you want to start the OHS instance, unless made changes to the configurations, just run

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\bin>startComponent.cmd ohs1

Above concludes the 1st of level of configurations for Oracle forms based application. You can use Weblogic 12c Enterprise Manager to configure most of the forms environment related parameters like forms path and other, while I am yet to find out relevant configuration areas for reports.

image

You can manually modify the formsweb.cfg & default.env files to start with as well

Regardless whether you are going to use the in-process report server or a stand alone instance of the reports, many changes should be made to the configuration files, so that you can call the reports server from a Forms module

Generally we will be modifying multiple configuration files, I will try my level best to provide the exact locations for the files which you should change according to your installation preferences

Configuration files path (Please note, through versions, there are minor changes happened to these configuration files. The images below were copied from my previous post about configuring for deployment, however the settings remain the same for 12.2.1.4 (as tested on 19th June 2020))

D:\Weblogic\Middleware\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration

  1. rwservlet.properties – Servlet specific properties
  2. rwserver.conf – Server properties
  3. cgicmd.dat – key mapping file

1. As far you are using in-process reports server, nothing much to alter with “rwservlet.properties” file. Basically you should see entries like following in the opened file:

image


2. rwserver.conf (Please check the below image)

image

3. cgicmd.dat: Many values we supply to call a report using Web.Show_Document method could potentially expose vital information like the database details & many other. “cgicmd.dat” file helps us to map such values to keys and later use them wherever applicable. With this example, I will demonstrate, how the database username, password and service name are mapped using a key. Please check the image attached below

image

Once these configurations files are altered, we need to create a report instance for the WLS_REPORT server. Open an elevated command prompt and switch to “D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin”. Start wlst & connect to the server.

Issue the command

createReportsToolsInstance(instanceName='rptools1', machine='AdminServerMachine')

Note, you can choose your own name for the report instance. Once the report instance created, exit wlst and you can try to start the WLS_REPORT server from the weblogic console.

This comprehensive note should help you to quickly start with Oracle Forms & Reports 12c (12.2.1.4)

I am avoiding few elements like publishing a Forms based application for now. If you feel, should be added, please let me know through the comments section.

regards,

rajesh

Weblogic 12c | Admin Server startup error(s)

Hello guys

Two days back I’ve seen a tweet from Michael Ferrante asking the “Forms Enthusiasts” to hold their breathe, as Oracle is going to announce something “Exciting” for Oracle forms. Are you EXCITED as much I am? Well, I am pretty sure, it is going to be a push for AuraPlayer, as he has mentioned them in the tweet.

Regardless, I’ve a local instance of Weblogic Server 12.2.1.3, installed and a classic domain created. I stopped using it as my firm decided to drop the idea to migrate our 20+ years old Developer 6i developed ERP to Weblogic due to the complexity of Oracle licensing, followed by the uncertainty of Oracle support for Oracle forms beyond 2025 (Which is just 6 years away).

Hell with it, I wanted to see how far I have configured the domain and ended up setting up the forms server, publishing the half baked legacy application. As I have made more than dozen Weblogic installations in last 2-3 years time, I had almost everything backed up from different installations like configuration files, environment and registry files. All I needed was to copy and replace them at most of the places to bring the legacy application online within couple of hours time.

However, I have noticed that while starting the Weblogic Admin Server (from the command prompt, using supplied .cmd files), I was getting a number of java errors & all of them were related to FADS (Forms Application Development Services) & some text were like below (Sorry for the wrapping issues)

weblogic.application.library.LibraryDeploymentException: [J2EE:160141]
Could not initialize the library Extension-Name: fads-dbtools-library.
Ensure that the deployment unit is a valid library type (WAR, EJB, EAR, plain JAR).
D:\Weblogic\Middleware\Oracle_Home\forms..\sqldeveloper\sqldeveloper\lib\oracle.dbtools-common.jar
(The system cannot find the file specified) with :
D:\Weblogic\Middleware\Oracle_Home\forms..\sqldeveloper\sqldeveloper\lib\oracle.dbtools-common.jar
at weblogic.application.internal.library.LibraryDeploymentFactory.getLibData(LibraryDeploymentFactory.java:98)
at weblogic.application.internal.library.LibraryDeploymentFactory.createDeployment(LibraryDeploymentFactory.java:50)
at weblogic.application.internal.DeploymentManagerImpl.createDeployment(DeploymentManagerImpl.java:195)
at weblogic.application.internal.DeploymentManagerImpl.access$800(DeploymentManagerImpl.java:61)
at weblogic.application.internal.DeploymentManagerImpl$DeploymentCreatorImpl.createDeployment(DeploymentManagerImpl.java:628)
Truncated. see log file for complete stacktrace

weblogic.application.internal.DeploymentStateChecker$DeploymentAssertionError: Unexpected transition: current state for application fads#1.0 : STATE_NEW attempt to transition to STATE_ADMIN
Dumping 2 callbacks
———————- BEGIN CALLBACK DUMP ——-
java.lang.Exception: prepare
at weblogic.application.internal.DeploymentStateChecker.save(DeploymentStateChecker.java:152)
at weblogic.application.internal.DeploymentStateChecker.prepare(DeploymentStateChecker.java:157)
at weblogic.deploy.internal.targetserver.AppContainerInvoker.prepare(AppContainerInvoker.java:65)
at weblogic.deploy.internal.targetserver.AppDeployment.prepare(AppDeployment.java:166)
at weblogic.management.deploy.internal.DeploymentAdapter$1.doPrepare(DeploymentAdapter.java:41)
at weblogic.management.deploy.internal.DeploymentAdapter.prepare(DeploymentAdapter.java:193)
at weblogic.management.deploy.internal.AppTransition$1.transitionApp(AppTransition.java:31)
at weblogic.management.deploy.internal.ConfiguredDeployments$2.doItem(ConfiguredDeployments.java:741)
at weblogic.management.deploy.internal.parallel.BucketInvoker$2.run(BucketInvoker.java:95)
at weblogic.work.ContextWrap.run(ContextWrap.java:46)
at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:670)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:644)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:415)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:355)
java.lang.Exception: activate
at weblogic.application.internal.DeploymentStateChecker.save(DeploymentStateChecker.java:152)
at weblogic.application.internal.DeploymentStateChecker.activate(DeploymentStateChecker.java:164)
at weblogic.deploy.internal.targetserver.AppContainerInvoker.activate(AppContainerInvoker.java:90)
at weblogic.deploy.internal.targetserver.BasicDeployment.activate(BasicDeployment.java:274)
at weblogic.deploy.internal.targetserver.BasicDeployment.activateFromServerLifecycle(BasicDeployment.java:507)
at weblogic.management.deploy.internal.DeploymentAdapter$1.doActivate(DeploymentAdapter.java:53)
at weblogic.management.deploy.internal.DeploymentAdapter.activate(DeploymentAdapter.java:202)
at weblogic.management.deploy.internal.AppTransition$2.transitionApp(AppTransition.java:52)
at weblogic.management.deploy.internal.ConfiguredDeployments$2.doItem(ConfiguredDeployments.java:741)
at weblogic.management.deploy.internal.parallel.BucketInvoker$2.run(BucketInvoker.java:95)
at weblogic.work.ContextWrap.run(ContextWrap.java:46)
at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:670)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:644)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:415)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:355)
———————- END CALLBACK DUMP ——-
at weblogic.application.internal.DeploymentStateChecker.throwAssertion(DeploymentStateChecker.java:88) at weblogic.application.internal.DeploymentStateChecker.illegal(DeploymentStateChecker.java:107) at weblogic.application.internal.DeploymentStateChecker.up(DeploymentStateChecker.java:122) at weblogic.application.internal.DeploymentStateChecker.activate(DeploymentStateChecker.java:166) at weblogic.deploy.internal.targetserver.AppContainerInvoker.activate(AppContainerInvoker.java:90) Truncated. see log file for complete stacktrace

Basically, these errors NEVER truly interrupt anything with deploying a forms based application, unless you want to do more with FADS. FADS deployment looks for some specific libraries and missing them were generating the above errors. As I mentioned, for a classic domain, addressing this error may not be truly necessary (for testing). I just love NO errors under usual scenarios, hence decided to fix it.

I referred the below documents to find a proper solution

The 1st document insists that we must delete/rename the pre-installed SQLDEVELOPER (3.x) under ORACLE_HOME (eg: D:\Weblogic\Middleware\Oracle_Home) & install a version that is minimum 4.2, however doesn’t mentioned anything about the highest version supported.

After checking the fads configuration py file, I realized that the files FADS deployment trying to locate during the startup were NOT available with SQLDEVELOPER 18.x:

‘/sqldeveloper/sqldeveloper/lib/oracle.dbtools-common.jar’
‘/sqldeveloper/sqldeveloper/lib/oracle.sqldeveloper.sqlcl.jar’

The 2nd document I referred explained about configuring FADS & the blogger used SQLDEVELOPER 17.2 for his exercise. Hence I downloaded the specific version 17.2, extracted and copied the folder “sqldeveloper” to ORACLE_HOME & followed the instructions as provided within the Oracle document.

Error while using SQLDEVELOPER 18.x

D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin>wlst.cmd D:\Weblogic\Middleware\Oracle_Home\forms\fads\fads_config.py
Picked up JAVA_TOOL_OPTIONS: -Djava.vendor=”Sun Microsystems Inc.”
Initializing WebLogic Scripting Tool (WLST) …
Welcome to WebLogic Server Administration Scripting Shell
Type help() for help on available commands

fads configuration script
Admin Server will be shutdown by running this script.
Do you want to continue? [Y/n] :Y
You need to install Oracle SQL Developer 4.2 or higher under ORACLE_HOME. Did you install SQL Developer 4.2? [Y/n] :Y
error:
SQL Developer 4.2 is not installed under D:\Weblogic\Middleware\Oracle_Home
Install it and run this script again.
Download Oracle Sql Developer from http://www.oracle.com.
exiting…
Problem invoking WLST – Traceback (innermost last):
File “D:\Weblogic\Middleware\Oracle_Home\forms\fads\fads_config.py”, line 341, in ?
File “D:\Weblogic\Middleware\Oracle_Home\forms\fads\fads_config.py”, line 207, in fullConfig
NameError: system

Successful configuration using SQLDEVELOPER 17.x

D:\Weblogic\Middleware\Oracle_Home\oracle_common\common\bin>wlst.cmd D:\Weblogic\Middleware\Oracle_Home\forms\fads\fads_config.py
Picked up JAVA_TOOL_OPTIONS: -Djava.vendor=”Sun Microsystems Inc.”
Initializing WebLogic Scripting Tool (WLST) …
Welcome to WebLogic Server Administration Scripting Shell
Type help() for help on available commands

fads configuration script
Admin Server will be shutdown by running this script.
Do you want to continue? [Y/n] :Y
You need to install Oracle SQL Developer 4.2 or higher under ORACLE_HOME. Did you install SQL Developer 4.2? [Y/n] :Y
SQL Developer 4.2 is installed under D:\Weblogic\Middleware\Oracle_Home
connecting to WebLogic:
Please enter your username :weblogic
Please enter your password :
Please enter your server URL [t3://localhost:7001] :
Connecting to t3://localhost:7001 with userid weblogic …
Successfully connected to Admin Server “AdminServer” that belongs to domain “base_domain”.
Warning: An insecure protocol was used to connect to the server.
To ensure on-the-wire security, the SSL port or Admin port should be used instead.
obtaining Admin Server host/port information
Location changed to domainRuntime tree. This is a read-only tree
with DomainMBean as the root MBean.
For more help, use help(‘domainRuntime’)
fadsui.ear:-> D:\Weblogic\Middleware\Oracle_Home\user_projects\applications\base_domain\forms\fads\fads-ui.ear
webservices – http://localhost:7001/fads/apis (updated to http://192.168.125.1:7001/fads/apis)
Saving…
Totals {connections=1, rest=1, updated=1}
updating FADS OWSM policy
creating fads keystore
Already in Domain Runtime Tree
Keystore created
Already in Domain Runtime Tree
Key pair generated
Context is missing, therefore using current context “/WLS/base_domain”.
Successfully configured property “keystore.type”.
Successfully configured property “location”.
Successfully configured property “keystore.sig.csf.key”.
Successfully configured property “keystore.enc.csf.key”.
creating fads WSM policy set
Session started for modification.
Description defaulted to “Global policy attachments for RESTful Resource resources.”
The policy set was created successfully in the session.
Policy reference “oracle/multi_token_rest_service_policy” added.
The configuration override property “propagate.identity.context” having value “true” has been added to the reference to policy with URI “oracle/multi_token_rest_service_policy”.
The policy set restPolicySet is valid.
Creating policy set restPolicySet in repository.
Session committed successfully.
importing fads authorization policy
import fadsWSpolicy passed D:\Weblogic\Middleware\Oracle_Home\forms\fads\policy\fadsWSMPolicy.zip
Importing “META-INF/policies/oracle/binding_authorization_template_fads”
Successfully imported “1” documents
Location changed to edit custom tree. This is a writable tree with No root.
For more help, use help(‘editCustom’)
Starting an edit session …
Started edit session, be sure to save and activate your changes once you are done.
Saving all your changes …
Saved all your changes successfully.
Activating all your changes, this may take a while …
The edit lock associated with this edit session is released once the activation is completed.
Activation completed
shutting down the Admin Server
Shutting down the server AdminServer with force=false while connected to AdminServer …
Disconnected from weblogic server: AdminServer
shutting down the Admin Server
…….done with fads post configuration…………
please start the Admin Server

Exiting WebLogic Scripting Tool.

Once the configurations are successfully completed, the configuration process shuts down the Admin Server. Start the Admin Server & there should not be errors towards missing FADS libraries.

regards,

rajesh

Oracle Linked Server | MS SQL

Updated on: 20th November 2020

I see many visits to this thread from Microsoft Technet references & one of the visitors left me the following comment:

For 12c client, I tried applying the steps you described and it did not work.
 I found that there is a bug affecting Oracle Provider for OLE DB – Version 12.1.0.2 and later
 Oracle OLEDB Throws Error “DBCOLUMNFLAGS_ISFIXEDLENGTH” to Handle CHAR(n BYTE) From Linked Server With MS SQL Server (Doc ID 2484393.1)

Thanks Larisa for your kindness to share the information & found that the error is tightly related to Oracle database version 12.1.x.x. Oracle note provides a workaround using OPENQUERY. I don’t think a “workaround” as a permanent solution, Instead, if possible, upgrade your database to 12.2 or higher versions as a possible permanent fix. Oracle claims this bug was addressed by “Windows Database Bundle Patch 26720785 and later versions”. If you don’t have Oracle’s valid support contract, will not able to download the patch & the only possible solution could be upgrading your 12c database while the download repositories still holding the 12c media. So hurry up :)

As Organizations grow, will end up with multiple software solutions taking care of different areas of the business. Based on the availability, there would be situations where business will end up with different database technologies and data exchanges between such will become a mandatory element to avoid duplication and additional efforts.

Today we will discuss about a requirement that involves MS SQL Server and Oracle database. While Microsoft has well established solutions called as Linked Servers, that allows the MS SQL to connect with heterogeneous database technologies, Oracle’s approach is pretty tedious and may require more efforts that one could anticipate. Yes, I am talking about Oracle “Golden Gate”, which allows an Oracle database to access other database technologies through a “gateway”

Instead of discussing the complex “Golden Gate”, we will see how simple (& complex at the same time) it is to setup Linked Servers from Microsoft SQL Server (for Oracle)

Install the client

Depending upon your OS architecture(32Bit/64Bit), you need to install the appropriate Oracle client. I suggest, install 64Bit Oracle Client on 64Bit OS and 32Bit client over 32Bit OS. You save loads of efforts by doing so. I always do a full installation of the Oracle client and later add missing components those are mandatory for “Distributed Transactions”. So what is a distributed transaction?

  • Your MS SQL Database table receives a row (record)
  • Your expectation is to replicate the same row to Oracle table
  • You have an “after insert” trigger defined with the MS SQL Table

A full installation of Oracle client (Administration) doesn’t install the mandatory component to facilitate the above requirement. You must install “Oracle Services for Microsoft Transaction Server” in order to do such a distributed transaction from MS SQL to Oracle database, after invoking the Client Installer. Make sure while the installer kicks in, you select the already existing Oracle home to make sure that the installation will not create another home for the additional components those you will install. Cross check whether the Oracle OLE DB driver is installed & install the Oracle Services for Microsoft Transaction Server component.

Analyze your Oracle provider and make changes to the stack

Make sure your provider, in our case, “OraOLEDB.Oracle” is configured prior you create linked servers.

Enable the following options

  • Allow inprocess
  • Nested Queries
  • Support “Like” operator

and disable

  • Index as access path. Disabling this option is mandatory for “Distributed Transaction”. If your Oracle table has indexes and this option is not disabled, an insert attempt from table trigger will fail with the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “ERPPROD” returned a “NON-CLUSTERED and NOT INTEGRATED” index “XXFPPUNCHM_N1” with the incorrect bookmark ordinal 0.

Create a linked Server

Right click on the “Linked Server” node and select “New Linked Server…”. I am providing you details of a linked server that is already created at my end. Adjust your linked server details accordingly. Make sure, the Oracle client installation folder is your “PATH” & that the tnsnames.ora file has an entry for the Oracle service (that you will enter in “Provider string” column.

If you have entered the mandatory elements correct, you have successfully created a linked server. If anything goes wrong you will be prompted about it, and address them. Please note, you can always revisit and change the Server options at later stages. However, options under “General” cannot be modified. If requires modifications, you need to drop and recreate the linked server once again.

Test the newly created linked Server

The actual issues starts from now. Please note, my laptop that I use for all developments has multiple versions of Oracle database and clients installed in addition to .Net development tools, Android, PHP etc to name few. If you are planning to implement the linked server solution for a production environment, make sure you have only one Oracle product installed along with MS SQL server. On the other hand you are going to have end number of complexities, few of which are not easily addressed.

For example, my development machine has:

  • Oracle database 11g
  • Oracle Client 12c (12.1)
  • Oracle Database 12c (12.2)

Which is a more than complex situation to address when it is all about MS SQL linked servers. One of the toughest issues to address is the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.

It took me almost 1.5 days to figure out what could be wrong as a simple SQL query like following against the newly created linked server continuously provided me the above error.

Select count(*) from [ERPTEST]..[APPS].[XXFPPUNCHM] 
 go

I’ve come across a post over stackoverflow.com which said, this could be due multiple Oracle products being installed in the same box & there were few instructions to overcome, which didn’t workout for me. However, I was successful with the production server, in which I only had the Oracle 11g client installed. To insure the real time replication of the data from MS SQL to Oracle database, I had to alter few registry values & restart the server. To my utter surprises, the same scenario I tested over three different boxes & all three experiences were different from each other.

  • The production server where I have SQL Server 2014 standard edition would not post rows to Oracle database, that is a part of “Distributed Transaction” without the registry hacks.
  • My development laptop wouldn’t even fetch rows from Oracle database without tweaking the PATH environment element & registry with proper .dll paths
  • My home PC does everything without having to tweak the path of registry where I have almost the same setup like my development laptop. The ONLY one difference with my home PC is, instead of Oracle client 12c, I have 11g client.

Now we will address each of such situations. Please note, the following exercises require you to make registry changes, so please make sure that you take a full backup of the registry prior attempting any given possible solutions. (If you are having ONLY one Oracle product installed (Database or Client, please move to Step#2 )

Step#1: Register Oracle OLEDB driver (This is to insure that we are using the same stack across the solution). Only one version of OLEDB driver could be activated at a time, regardless how many Oracle products are installed. If you had 12c installed after 11g, you must be having the 12c OLEDB driver activated.

From an elevated command prompt, switch to Oracle Client/Database BIN folder (eg: D:\oracle\product\11.2.0\dbhome_1\BIN)

Issue the following command

regsvr32 OraOLEDB11.dll

This should register the OLE DB driver for you.

Step#2: Check your OS PATH environment element, your client/database bin path must be the first Oracle product entry, eg:

This will insure that tnsnames.ora will be sought in this path, in addition to Oracle dlls. As we are using Oracle database 11g as the first product from the list of other Oracle products installed in the environment element PATH (refer the image above), we will hack the registry with all elements related to the specific product (Again, please make a backup of the registry, minimum the specific key)

Registry key: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

Refer the image above and adjust the entries as per your Oracle installation. Once the registry is modified, restart your computer (mandatory)

Once the box restarted, try to insert a row into the Oracle table. Example

insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 VALUES(GETDATE(),'IT Office','0046','0')
 go

SQL Management studio must stop complaining about “The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.” right after setting up the correct .dll files and the relevant paths in the registry.

Now create your table trigger, through which you want to push a row to the Oracle table. A simple after insert trigger could be defined like following:

create trigger addRecordsToERPTable2 on [UNIS].[dbo].[tRajesh]
 after insert
 as
 begin
 insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
 Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
 FROM inserted
 end
 go

You may come across an error while a distributed transaction is initiated, Management Studio complaining about “Msg 8501, Level 16, State 3, Procedure addRecordsToERPTable2, Line 13
MSDTC on server ” is unavailable.” This is a pretty simple error to address. Open the Windows Services & check whether the service “Distributed Transaction Coordinator” has started. My development machine initially had this service startup mode set as “Manual”, I changed it to “Automatic delayed start” and started the service. Adjust according to your situation.

Basically the above few things should address most of the common issues you would face with Oracle Linked Server from MS SQL.

Finally, Oracle clearly states, there are limitations using their driver for Linked Servers from MS SQL. So, expect for unexpected while using such a setup. For me, it was simple transactions. If you are expecting rapid replications based on complex business requirements, please do test your scenarios as much as possible prior adapting the above hacks.

regards,

rajesh


OLE DB provider “OraOLEDB.Oracle” for linked server “” returned message “New transaction cannot enlist in the specified transaction coordinator.”

Hi guys

A pretty long title? Well, recently I came across a situation where I needed a trigger with MS SQL server table to insert some information into our Oracle database.

The MS SQL Server is hosted in a Windows 64 bit OS, with Oracle 11g 64Bit client installed (For 64Bit OS, you must install Oracle client 64Bit for the Oracle OLEDB provider)

I did some sample inserts using the Management studio and created a trigger like following with one of the sample tables:

create trigger addRecordsToERPTable on [UNIS].[dbo].[tRajesh]
after insert
as
begin
  insert into [XYZ].[APPS].[XXFPPUNCHM] 
  (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
  Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
  FROM inserted
end
go

So the idea was pretty simple, like an audit, as soon as the SQL table “rRajesh” has a new row inserted, the after insert trigger should sent the same row to underlying table over Oracle. Instead I started getting the following error:

OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” returned message “New transaction cannot enlist in the specified transaction coordinator. “.
Msg 7391, Level 16, State 2, Procedure addRecordsToERPTable, Line 5
The operation could not be performed because OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” was unable to begin a distributed transaction.

I’m not very familiar with MS SQL or the complexities related to Linked Server environments. So, started my next series of Google searches. I referred tons of discussions, however was not getting anywhere with the dreaded situation. During the frantic search for a solution, I executed the instructions available over different links.

https://stackoverflow.com/questions/6999906/distributed-transaction-error

https://community.oracle.com/thread/2265534

Even after making changes as mentioned with the above threads, I still kept on receiving the same errors while a row was inserted into my SQL sample table. So I continued searching for a solution and came across a thread

https://microsoft.public.sqlserver.security.narkive.com/WDGBVTrk/msdaora-was-unable-to-begin-a-distributed-transaction-why-sql-oracle

This thread was pointing towards a Microsoft’s post addressing this particular situation.

http://support.microsoft.com/kb/280106

Although the article addresses pretty Old OS and Oracle environments, the solution is still applicable on later OS and Oracle clients. For example, My MS SQL Server is installed over Windows 2008 R2 and the Oracle client I am using with the server is 11G R2 64Bit.

Let us see quickly what Microsoft provides as a solution.

I checked the registry of my server and found something pretty interesting like below:

Now, Oracle names almost all their major dll files in a particular fashion. Most of the times you will find the dll files having the major version numbers by the end of the filename, for example, if your Oracle database is 8.0, your client dll file will be “Oraclient8.dll” and if you are using Oracle 11g, the filename would be “Oraclient11.dll”

After taking a full backup of the registry, I modified the values with 11g specific & restarted the Server (as per the instructions available for Oracle 8.1 in the Microsoft document.)

Once the server started, I went ahead and tried to insert a new row into my sample table and that was it. No more errors and the row was inserted to both MS SQL table and Oracle table at the same time.

So if you were frantically searching for a solution, this post may help you to resolve it.

regards,

rajesh

Oracle EBS | Restoring database from RMAN backup

Hi guys

I always wondered how the heck a RMAN backup could be restored to Oracle EBS environment. Last two years, I spent considerable time learning Oracle database technology, especially about the backup and restore procedures (learning a lesson after a disaster)

We have the following approach towards Oracle EBS backup:

Daily RMAN hot backup (Friday’s exempted as we do a full cold backup), with 7 days retention policy.

Weekly cold backup for both application and database tiers

Cold backup is written to a backup server, then copied to an external driver (USB attached). This way the cold backup remains in 3 different places.

Usually whenever a fresh clone requested, we were building them against the latest cold backups. Many cases a Friday cold backup clone is NOT what the consultants want, they need much recent data on a Thursday! This has forced me to start pondering over google to find a properly written document to support my own experiments and not like other areas of database, the availability of decent documents were scarce!

So I approached one of my APPS DBA friends, who was always there to help me & readily made me available with a document that he used for his specific environment. After some tweaks I was able to successfully restore the RMAN BACKUP to Oracle EBS environment without troubles (Well, the time it took were much more than that from a cold backup cloning)

I assume, if you are going through this write-up, you have the below setups:

  1. Single instance Oracle EBS R12 Deployment
  2. You are using Oracle 11g R2
  3. You are on LINUX
  4. You already have a cloned instance, properly configured SPFILE & listener services
  5. You have environment parameters set and called through .bash_profile(s)

Steps

copy RMAN files from Production server to TEST server using SCP. The document asks me to create the same path structure, haven’t tried other options yet. For example, the Production server has /u03/RMAN/DAILYBKP for the daily RMAN backups, hence I made the same available with TEST instance as well. The below command copies the RMAN backups those are one day older from the current date to TEST instance.

[code language=”text” gutter=”false”]time scp `find -maxdepth 1 -type f -mtime -1` root@erptest.xyz.com:/u03/RMAN/DAILYBKP[/code]

Once the files are copied, shutdown both application and database instances respectively.

Delete the .dbf files (data files) from the “apps_st/data/” folder.

Example, my TEST server has the data files here “/u01/oratest/TEST/db/apps_st/data”, so I did a rm -rf * here to remove all the files available in this folder. We’ve approximately 500GB data files in this folder.

Once the files are deleted, open a terminal and switch to Oracle database user. Source the environment incase if the .bash_profile is not set to.

Issue “sqlplus / as sysdba” command

Startup the database in not mounted state

startup nomount

Start another terminal and switch to Oracle database user. Now let us start RMAN recovery

Before attempting the RMAN duplicate, make sure the ORACLE user has full ownership on the backup folder. If not, RMAN will start reporting the controlfile not found & other associated errors and the duplication will fail.

I’ve a cronjob, executed as root to copy the rman files everyday to TEST instance, hence the default ownership on the backup folder is set to root, that I realized today (4th Feb 2019) while trying to duplicate the database!

rman auxiliary /

That should connect you to RMAN duplicate database mode.

Now, the most important few things. The RMAN backup will have datafile names associated with path information. For example, my production server keeps the datafiles in the absolute path “/u05/oraprod/PROD/db/apps_st/data/”, where in my TEST server has the cloned database instance over “/u01/oratest/TEST/db/apps_st/data”. So we will have to rename the database files prior they are restored. The RMAN run command set that you will create will take care of renaming the files.

Create a RMAN command set like below (adjust accordingly)

My production instance database name is “PROD” and my TEST instance name is “TEST”, so the duplicate database command should be adjusted accordingly.

[code language=”text” gutter=”false”]
run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to “TEST” backup location ‘/u03/RMAN/DAILYBKP’ nofilenamecheck
db_file_name_convert=(‘/u05/oraprod/PROD/db/apps_st/data/’,’/u01/oratest/TEST/db/apps_st/data/’)
LOGFILE
GROUP 1 (
‘/u01/oratest/TEST/db/apps_st/data/redo01a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo01b.log’
) SIZE 1000M ,
GROUP 2 (
‘/u01/oratest/TEST/db/apps_st/data/redo02a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo02b.log’
) SIZE 1000M ,
GROUP 3 (
‘/u01/oratest/TEST/db/apps_st/data/redo03a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo03b.log’
) SIZE 1000M ,
GROUP 4 (
‘/u01/oratest/TEST/db/apps_st/data/redo04a.log’,
‘/u01/oratest/TEST/db/apps_st/data/redo04b.log’
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}[/code]

You may keep the RMAN RUN commands saved in a text file for future use!

At the RMAN prompt execute the newly created RMAN RUN command set. It is going to be a long wait, the bigger the database, the more time you wait for the restoration to happen.

Wait until the RMAN processes are finished. You should be back to RMAN prompt

From a SQL Plus session, shutdown the database

Now restart the database at mount state.

startup mount

Disable Archive logging prior doing anything else

SQL> alter database noarchivelog;

Shutdown the database. Now we will do the post cloning activities against the database instance.

Switch to the appsutil/clone/bin over $ORACLE_HOME path. Example “/u01/oratest/TEST/db/tech_st/11.2.0/appsutil/clone/bin”

Issue the following:

perl adcfgclone.pl dbTechStack

Input details for your instance and complete the post cloning activities. This shouldn’t take much time!

Once the post cloning activities are completed, switch to ..install/TEST_erptest folder, example: “/u01/oratest/TEST/db/tech_st/11.2.0/appsutil/install/TEST_erptest”

Start SQLPlus from this location and execute “adupdlib.sql”

SQL> @adupdlib.sql so

Once the procedure run finishes, issue COMMIT

Commit;

Run Autoconfig for your Application instance and you should be online soon.

Oracle | Thread 1 cannot allocate new log, Private strand flush not complete

Hello guys

Okay, I was silent for couple of months. I took a much needed break and back to work now. As few of you may already know, I am not a DBA (Certified), yet I have dealt with Oracle databases throughout my career & today was “another day” when I came across something new after restoring RMAN backup to a TEST environment.

Actually the entire “how to document” was provided by a APPS DBA friend (thanks to such geeks who are never bothered about someone else “learning the tricks” and challenging them! Geeks remain geeks) & without giving much attention to few elements, I “successfully” duplicated the 11g R2 database. Once after the database came online, I realized that the instance was pretty slow & immediately monitored the alert logs.

I started reading few entries like following:

Thread 1 cannot allocate new log, sequence 56
Private strand flush not complete
  Current log# 3 seq# 55 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo03a.log
  Current log# 3 seq# 55 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo03b.log
Beginning log switch checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 4 seq# 56 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo04a.log
  Current log# 4 seq# 56 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo04b.log
Completed checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thu Oct 04 12:14:14 2018
Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 5986177240998
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thread 1 cannot allocate new log, sequence 58
Private strand flush not complete
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thu Oct 04 12:14:25 2018
Beginning log switch checkpoint up to RBA [0x3a.2.10], SCN: 5986177241136
Thread 1 advanced to log sequence 58 (LGWR switch)
  Current log# 2 seq# 58 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo02a.log
  Current log# 2 seq# 58 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo02b.log
Thu Oct 04 12:14:47 2018
Thread 1 cannot allocate new log, sequence 59

I landed over a discussion at https://community.oracle.com/thread/364032?start=0&tstart=0 & few others, and at many places I read suggestions towards the redo log files getting filled too fast because of the smaller sizes allocated. I check the production instance, the redo log files were of size 1000M when the TEST instance log files were of size 100M!

So my next requirement was to resize the redo log files without damaging the database.

I came across an excellent post here https://uhesse.com/2010/01/20/how-to-change-the-size-of-online-redologs/ that explains how to create new redo log files and to drop the old ones without affecting the database (or users). The best part is, you don’t even have to take the database offline for any of the suggested activities.

So if you ever face such a situation, give it a try. You would be happy like me :)

regards,

rajesh

 

 

WebLogic 12c 12.2.1.3 Installation & configuring for forms & reports

Hi guys

Update: 13th February 2019

Downloading the installing Oracle software could be a challenging task at times, WebLogic 12c Forms & Reports installation is NOT very different Especially if you are as usual skipping the “read me” areas)

I will try to explain the download and extraction activities related Oracle forms and reports 12c below.

Visit https://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html for downloading the installation media. There are two ZIP files for Oracle forms and reports. Download both of them.

Once the files are downloaded, you need to extract both the zip files into a single folder

1st ZIP archive extracts “setup_fmw_12.2.1.3.0_fr_win64.exe” & 2nd ZIP archive extracts “setup_fmw_12.2.1.3.0_fr_win64-2.zip”. During installation,
“setup_fmw_12.2.1.3.0_fr_win64.exe” expects
“setup_fmw_12.2.1.3.0_fr_win64-2.zip”, hence, do NOT extract
“setup_fmw_12.2.1.3.0_fr_win64-2.zip”. To make it simple, for Oracle installation, move the following files to a new folder, call it “Forms_Installer_12c”

D:\Forms_Installer_12c

and copy/move both
“setup_fmw_12.2.1.3.0_fr_win64.exe” & “setup_fmw_12.2.1.3.0_fr_win64-2.zip” to this folder. Less confusing this way :)

========

One of the posts that is doing great according to a blogger’s expectation is about WebLogic 12c installation & configuring it for deploying Oracle forms/reports based applications.

Well, I totally missed the fact that Oracle periodically updates their software & missed out the current version of WebLogic 12c 12.2.1.3. It didn’t even ring a single bell when someone asked me about an error that was happening during his attempts to get 12c installed, which was specific to 12.2.1.3.

My previous post about WebLogic 12c strictly deals with version 12.2.1.0.0 for the areas of creating a new repository for WebLogic infrastructure and configuring a user domain. There are minor changes with 12.2.1.3, and those changes sure require mentions.

Prerequisites

Oracle WebLogic 12c, Oracle forms and reports require Visual C++ verion 11 preinstalled. Although the installation would proceed after showing missing prerequisites, I will NOT suggest you to take unknown risks. If possible, make sure your box is completely updated with latest Microsoft patches for the specific operating system.

This time I will take the freedom to believe that you already have Oracle 12c database installed and avoid going through the database installation procedures.

As a thumb rule, install Java JDK 8, the latest version available in a folder like C:\java\jdk or C:\JDK or D:\Jdk (the shortest path name possible).

Switch to the folder where you have downloaded the software required for Weblogic 12.2.1.3 installation and configuration from an “elevated” command prompt.

Check the below image for details:

https://i2.wp.com/simpleoracle.com/wp-content/uploads/2018/05/11.png?resize=640%2C335&ssl=1
2
3

It looks like Oracle has finally realized that 99% of the public will opt skip updates, hence they made it as default this time ;)

4

By following the classic way of naming the installation folders, you save tremendous time & efforts to locate configuration files (Especially if you are following my posts). I always choose to install the weblogic stack on D:\Weblogic\Middleware\Oracle_Home. This helps me to setup the environment next time by keeping exact path information for configuration files etc.

5
6
7
8
9
10

Select “Fusion Middleware infrastructure” (Default)

That’s all folks, your Weblogic stack is installed & you are ready to go ahead with the installation of Oracle forms and reports 12c now.

11
12
13
14
15
16a
17
18
19
20
21

Basically you shouldn’t ignore the following “Warning” about Visual C++ element. I’ve found that Visual C++ causing serious issues with OHS instance starting during my previous attempts, hence make sure you install Visual C++ recommended version is installed in the machine.

The above installation(s) should be go all smoother than previous versions & now we can move to configuring our first domain using the latest WebLogic Stack installed.

Setup repository for the domain

Suggestion(s):

  1. I suggest you totally disabling the password expiry for the entire database that you will be using for your WebLogic lab (12c). Ignoring this element could land you in difficult situations in case if you are not meticulously maintaining the password changing routines.
  2. Always invoke scripts as administrator, this provides you the elevation on Windows 7 onward Windows OS

Execute rcu.bat from the following installation path. Please note, I have installed WebLogic software onD:\Weblogic\Middleware\Oracle_Home\oracle_common\bin folder (Which is the standard way I name the installation folder, this helps me to copy much of the elements between installations, upgrade processes), so I will be executing the “rcu.bat” file from “D:\Weblogic\Middleware\Oracle_Home\oracle_common\bin”. Adjust your path accordingly.

1
2

We will choose “System Load and Product Load” which is default for our repository as well. Please note, you should use the same “rcu.bat” for dropping the repositories, if you have to at later stages.

3

For all WebLogic labs, I suggest the same password for Database and WebLogic. You can go for simple passwords like “YourName123” which satisfies “most” of the implied complexity for passwords.

4
5

If you have entered the database credentials and other details correctly, the checking should be done within no time.

Now we will create the repository for our domain. Please note, the new profile name could be anything. Instead of “DEV” which is by default assigned by Oracle, you may choose your name in that place. Make sure you note down the name of the profile somewhere for future references. Select all the components as seen with the below image.

(Please note, I have copied the images from 12.2.1.4 installation and replaced few of the images with this post as one comment mentioned that I didn’t choose MetaData Services with the old image(s) provided, so please don’t get confused. Rest of the images provided may “miss” the metadata services related information & instead of “DEV”, you may see “DEV1” at places. Please ignore)

7a
8
9
10
11
12
13

Select same password for all schema.

I have the entire setup on a SSD, hence the time to create and configure the repo could be significantly much less than over a spinning drive system.

14

That completes our repository creation. Please make sure that you note down the repository name and the password(s) somewhere for future references.

Now we will create our 1st domain with our fresh Weblogic 12c 12.2.1.3 environment. Just make sure that the JDK installation path is the 1st entry in the PATH environment variable.

Invoke “config.cmd” file as administrator from “D:\Weblogic\Middleware\Oracle_common\common\bin” folder. Adjust the path according to your installation preferences.

1

We will create a new domain for the lab now. Leave the default domain location as suggested by Oracle. Change it ONLY if you know what you are doing at this point of time.

2

For Oracle Forms and reports, the below product selection is spanned over 3 different images. Make a list of the items selected, and make sure you don’t miss any of them before proceeding to next step.

3
3a
3b

Once all elements are selected for the domain, you can proceed.

4

As a standard approach, I am using the same password for the WebLogic Administrator account, leave the suggested administrator name as “weblogic”

5
6

We’ll go for production for Domain Mode. JDK location will be automatically picked. Do not change it, unless you have a valid reason.

Now we have to supply the repository details, those we have saved during the creation of repository. The details will be cross checked prior by the configuration tool. Once after supplying the details click the “Get RCU Configuration” button.

7
8
9
10

Once the repository validations done, it’s time to select the components for your domain. Make sure the checked elements are selected for your domain as well.

11

Do not miss to select “WSMPM-MAN-SVR” for the Server Groups, failing will FAIL the configuration of the domain. I don’t know what the heck it is, I may read about it one day and update myself. For now, I suggest you select the said group from the drop down list and proceed.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Failing to select the Server Groups will land you on this page, which you don’t want! Make sure you will do the “WSMPM-MAN-SVR” Selection for the Server Groups.

29

I corrected the error at my side by dropping the repository, recreating & configuring the domain once again.

30
31

So we have created a classic domain now. We need to setup an OHS instance and setup the domain for deploying our forms based applications now.

To configure the newly configured domain, you may use the 12.2.1.2 configuration instructions as posted here

Right now, you can use my previous posts about Install Weblogic 12c and setting up OHS, forms & reports for WebLogic 12c as reference materials to setup your new classic domain or wait for me to post a followup with version specific details.

  1. https://simpleoracle.com/2016/01/05/oracle-12c-database-weblogic-forms-reports/
  2. https://simpleoracle.com/2016/01/13/oracle-12c-forms-reports-configure-for-deployments/

Hope it helps few souls out there

regards,

rajesh

Oracle E-Business Suite R12 | SGA vs PGA vs NOT being a DBA!

Hi guys

I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)

We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.

I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.

We needed “something” to hold a copy of the EBS instance while it was being virtualized.

So, I cloned this machine & before continuing let me describe what this is hardware is like:

Processor:

[code language=”text” gutter=”false”]

Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 23
Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
Stepping: 6
CPU MHz: 1992.000
BogoMIPS: 4987.29
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 6144K
NUMA node0 CPU(s): 0-7

[/code]

Storage space

[code language=”text” gutter=”false”]

[root@erp-bak ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sdj2 59G 12G 44G 22% /
tmpfs 9.7G 3.9M 9.7G 1% /dev/shm
/dev/sdj1 2.0G 325M 1.5G 18% /boot
/dev/sdj5 738G 531G 170G 76% /u01
/dev/mapper/mpathbp1 591G 320G 242G 57% /u02
/dev/mapper/mpathcp1 197G 38G 150G 20% /u03
/dev/sdf1 591G 70M 561G 1% /u04
/dev/sdi1 269G 59M 256G 1% /u05

[/code]

in addition to the local disks this server have partitions mounted from a IBM SAN.

Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)

I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.

Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.

While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.

Few of the persistent issues were:

After a cold boot

  • The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
  • It takes ages for to open the concurrent programs window
  • Our custom forms & LOVs lag to extremes and so on..

Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!

Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…

I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye

I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!

I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!

Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:

Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.

Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)

I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!

regards,

rajesh