RMAN on Windows | Backup & Restore 11g R2 database

11g R2? too late to post anything for 11g? Wait, there are thousands of Businesses still using Oracle database 10g. So let us see how RMAN could be simplified on Windows. I hardly see many articles explaining such for Windows platform in general.

Considering you have 11g database already installed & setup for Archive logging, we’ll start by setting up RMAN backup first, then try a restore routine on a different sever.

Like cron jobs on Linux environment, for Windows we will use Task Scheduler for RMAN backups. Here are couple of batch files we will be using for making RMAN backup on Windows.

run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\RMAN_BACKUP\PROD_comp_%d_ lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT 'D:\RMAN_BACKUP\archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before 'sysdate-7';
backup current controlfile format 'D:\RMAN_BACKUP\bkpcontrol_file.ctl_%d_%T'               ;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

Save this script as “rman_backup.cmd”. You can save it with any other name ending with .cmd or .bat extension. Make sure you change the name in the next script incase if you choose a name other than “rman_backup.cmd”. Please note, I am taking backup on “D:\RMAN_BACKUP” folder, you should change “D:\RMAN_BACKUP” with the exact location that is used for the backup!

Now, we will create another cmd file that will call the above script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
:: echo %mydate%_%mytime%

set filename="D:\RMAN_BACKUP\logs\%mydate%_%mytime%_rman_backup.log"
:: echo %filename%

rman target / nocatalog cmdfile='D:\scripts\rman_backup.cmd' log='%filename%'

Once again you can save this file with anytime, provided the extension is either .cmd or .bat. I saved this cmd file as “callrman.cmd”

Make sure the path ” D:\RMAN_BACKUP\logs” or equivalent exists prior running the scripts.

Now fire up Windows Task scheduler and create a basic job

That’s all. Everyday, at a said time, RMAN backups will be created and kept in the destination folder. We are using 7 days retention policy with the script file, so on the 8th day obsolete backups will be purged. Depending upon the disk space available, you should adjust the retention policy for your backups. Don’t forget to copy those backup pieces to an external medium to insure maximum availability during a crisis.

That completes the simplest form of Oracle RMAN backups on Windows platform.

RMAN Restore

Currently we are discussing about restoring the RMAN backup to same version of Oracle database. If you want to restore RMAN backup to a higher version of Oracle database, it will be an entirely different exercise.

RMAN backups are the fastest, easiest incase if you are doing it right. No hassles like creating tablespaces, schemas and then waiting hours for the import process to complete! The biggest size of the database I ever dealt with is 550GB and a server with 2×4 core processors, 16GB total memory restores the 140GB backup in less than 1.5 hours. I suggest you to start using RMAN backups the soonest possible regardless whether your database is just few megabytes in size.

Today we will see how to restore RMAN backup from Server A to Server B, ie, to a different physical server with a different database name.

It might sound bit unethical to ask you to create a new database using Oracle database creation wizard for this purpose, however this is what we will do for this exercise. This will save us some time figuring out setting up many other parameters for the new database at later stages.

So let us start.

Start DBCA and create a database with a database name that you prefer for the purpose, for eg: “ORCL”

Once the database created, shut it down & delete all the datafiles from the data store. For example, I had all my data files in the folder C:\oracle\oradata\orcl, where orcl is the database name!

Now copy the RMAN backups from Server A to Server B. I’ve copied only one day backups from Server A to Server B and make a note of the exact location where you copied the backup files. In my case, I copied the files to “C:\RMAN”

From an elevated command prompt start SQLPLUS as sysdba & start the database in non mounted mode.

Now, start another elevated command prompt and connect to RMAN in Auxiliary mode, for duplication of the database.

We are all set to restore the RMAN backup to Server B now. However, there are few things we have to insure to make a successful restoration. For example renaming the datafiles. Based on the new datastore location where you are restoring the database files, each data file should be renamed. Please refer the below sample script for the same.

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 "ORCL" backup location 'C:\RMAN' nofilenamecheck
db_file_name_convert=('D:\Oracle\oradata\PROD','C:\Oracle\oradata\orcl')
LOGFILE
GROUP 1 (
'C:\Oracle\oradata\orcl\redo01a.log',
'C:\Oracle\oradata\orcl\redo01b.log'
) SIZE 1000M ,
GROUP 2 (
'C:\Oracle\oradata\orcl\redo02a.log',
'C:\Oracle\oradata\orcl\redo02b.log'
) SIZE 1000M ,
GROUP 3 (
'C:\Oracle\oradata\orcl\redo03a.log',
'C:\Oracle\oradata\orcl\redo03b.log'
) SIZE 1000M ,
GROUP 4 (
'C:\Oracle\oradata\orcl\redo04a.log',
'C:\Oracle\oradata\orcl\redo04b.log'
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

As I am duplicating the database to existing one, I don’t have to go through the tedious process of changing the dbid or database name etc. Once the restore is done, all I need is to disable the archive logging and go online! We will see those in next few steps.

Please give attention to “db_file_name_convert“, Server A had the datafiles in the path “D:\oracle\oradata\PROD” and the Server B, we will be restoring the datafile to C:\oracle\ordata\orcl folder. So we must mention those changes here in the script. The same way we need to rename the logfiles and locations also. I hope the above script is self explanatory on that regard.

You may not need all those channel allocation for a small database, please adjust the number of cannels allocated as per your requirements. Give a try with less or more number of channels incase if you want to learn what happens :)

Execute the above script from the RMAN prompt.

If there are no errors, based on the size of the database & hardware resources RMAN will return to the prompt once the activities are finished.

Before doing anything else, we need to insure that the Archive logging is disabled for the newly restored database. Rush to the previous SQLPlus session that you started with nomount. This session is already disconnected, hence reconnect and shutdown immediate.

Now start the database in mount state and disable the archive logging.

Shutdown the database and restart normally.

That’s all, your Server B “orcl” has all the data from Server A “PROD” database now. If this is a production recovery, you can change the database name using “NID” utility, that is supported from Oracle database 9i onwards. Hope this helps few Oracle database beginners out there.

As we have restored full database with archive logs, there is no need to restore logs. Cross verify the database details

Cheers guys!

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

Oracle APEX 18.x Installation | Windows

Hello guys

Referenced documents:

https://matthiashoys.wordpress.com/2012/04/06/uninstallation-of-apex-from-a-default-oracle-11gr2-database/

https://www.oracledbwr.com/oracle-apex/oracle-application-express-18-1-installation/

Let us see how Oracle APEX (Which will eventually replace Oracle FORMS (regardless whether you like that fact or not) for small to medium applications) could be installed on your Windows 10/Server this time.

We’ll be trying the latest version of ORACLE APEX 18.2 & Windows 10 Pro running the latest version 1809 (as on date, fully patched) and Oracle database 11g R2 (11.2.0.4). Oracle depends upon VC++ components loads, so make sure your Windows box is fully patched.

Step #1

Download Oracle APEX installer from APEX repository. Please note, for APEX 18.x, you MUST have 11G R2 11.2.0.4 or later database. 11g R2 11.2.0.4 is only available for customers with valid support, hence if you are a student, freelancer, you best bet is 12c database, that is still freely available to download.

Step #2

Unzip the downloaded .zip file to a finely structured directory. For example, you can extract the zip file to D:\APEX, that will create another “apex” folder within “D:\APEX\”. Check the attached image

I usually keep all the Oracle Installers under a single folder, called “D:\Oracle_Installers”. So basically the rest of the instructions, I will be using the same directory structure. Please don’t get confused.

Step #3

Switch to the apex folder from an elevated command prompt, in my case to “D:\Oracle_Installers\apex_18.2\apex”. Source the database environment (eg: SET ORACLE_SID=ORCL) in case if you have multiple Oracle products installed in the same box

Step #4

Start SQLPLUS as sysdba

A default installation of 11g R2 installs APEX, which you should remove at this stage. If you are too eager to know which version is installed, you my run the following query

select version from dba_registry where comp_id='APEX';

If you haven’t removed the APEX installation, it should return you a row with installed version information of APEX

You should remove the existing APEX prior installing the 18.x version(s). To remove the installed version, please execute as following

@apxremov.sql

Wait for the uninstallation to complete, exit SQL and start SQLPLUS once again as sysdba (Mandatory)

Now we can start the installation of APEX 18.x. Prior starting the installation let us create a dedicated tablespace for APEX.

CREATE TABLESPACE APEX DATAFILE 'D:\ORADATA\ORCL\apex01.db' SIZE 1G;

Now execute the SQL for installing APEX 18.x

@apexins APEX APEX TEMP /i/

(Note the forward slash "/")

Wait for the installation to finish. My box has a SSD, hence the installation completes in few minutes time (3-5 minutes I believe). It may take bit more time, depending upon the hardware resources available.

Once the installation completes successfully, we have to configure the APEX. To configure execute “apex_epg_config.sql” that is available in the current path. Please note, here is the catch, as with the installation, Oracle expects forward slashes around the parent folder where you have the image files. So, I have extracted the installation files to “D:\Oracle_Installers\apex_18.2\” & this is the PARENT directory for apex installer. Hence we will call the EPG script as below (correct your path appropriately)

@apex_epg_config.sql D:/Oracle_Installers/apex_18.2/

(Note all back slashes are replaced with forward slashes)

Once the script finally exists after successfully copying the image files to APEX repositories, you need to unlock the following Oracle database schemas

ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;

Now, go ahead with the last configuration by executing

@apxconf.sql

This script will ask for ADMIN user details, Select ADMIN as admin user and provide an email address. Please note, you must provide a punctuation symbol for the password and allowed punctuations are as below

(!"#$%&()``*+,-/:;?_).

That’s all folks. If there are no errors, you are all good to go. You may need to configure your database server for better performances, which we will discuss bit later.

Now try to access the APEX installation. Unless you have changed the ports, you should able to access the portal by visiting http://localhost:8080/apex/apex_admin

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 Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403

Hello guys

Recently one of our accountants forwarded me a screenshot, that was showing “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403” while he was trying to enter invoices against a “NEWLY” created vendor/supplier.

posterror

Our Oracle Application R12 (12.0.6) is considered 99.99999% stable, without a single technical or functional issue that really become a show stopper throughout last many years.

Well, this particular issue looked perplexing as it was not dealt by Oracle Application’s error reporting & slowly we started dwelling Oracle support documents those were dealing with the given forms error “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403”

Most of the documentations where mentioning about IBY duplicate pay party, which was not our case. Hence, I decided to open the associated form APXINWKB.fmb & located the WHEN-VALIDATE-ITEM trigger associated with the column “Purchase Order Number”. I couldn’t find any irregularities between an order that didn’t raise the error and this particular Purchase order did raise the exception, which was unhandled.

After two days of continuous attempts, I remembered that such errors happen in other forms modules when we had missing information for new vendors/suppliers. Must be due to a bug, there were times when site level details were NOT populated to organizations level details for a vendor/customer & I decided to go through all mandatory elements those were expected while creating a new vendor/supplier.

I sat with my colleague and we reached to “Payment Method”, and realized that the default payment method was not set for this particular vendor against the organization where we were getting this unhandled exception.

Once the payment method was set, the invoice was posted for the vendor successfully! So, if you come across these kind of unhandled exceptions across Oracle’s proprietary forms those deal with payments/invoices, prior exhausting yourself with cloning and patching, make sure you have all the mandatory elements for customer/vendors are properly filled in and assigned to all the organizations.

Hope this finding helps few consultants out there!

regards,

rajesh

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

 

 

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