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 Applications R12 | Java applet font scaling issues

We are using Oracle applications R12 12.0.6 with JDK 6.0 while most of the clients are using JRE 7/8 based on different business requirements (With full knowledge that there would be unexpected results due to the compiled versions). We’ve observed a peculiar issue with the JRE applet once after the upgrade to Windows 10 version 1803. The Java applet started showing too small fonts while launched from displays with high DPI resolutions.

Digging in brought me to the following discussion:

https://superuser.com/questions/988379/how-do-i-run-java-apps-upscaled-on-a-high-dpi-display

Although there are multiple recommendations, for EBS environment, Please follow the below instructions:

Launch the JAVA applet by opening a forms based module.

Open task manager and locate “Java(TM) Platform…”, right click and open properties. This will open the properties for “jp2launcher.exe”. Click on “Compatibility” and click on “Change high DPI settings”. Now check “Override high DPI scaling behavior. Scaling performed by:” to “Application”. Refer the image for more details.

Once the changes are made to the properties of the executable, restart the box. It could be specific case, the changes were not applied until a restart for me.

Although it fixes the font scaling issues, the toolbar area of the Oracle forms are remains scaled with pretty small icons. I am yet to find out a fix for this minor annoyance. May be quite soon.

 

regards,

 

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

 

 

Oracle Application R12(12.0.x) | JRE | UNKNOWN Publisher

Hi guys

This time I am going to post a solution towards one of the nagging annoyances with Oracle application R12 (12.0.6 in our specific case) & JRE securities those were implemented by JRE 7 (1.7.x)

Our instance was upgraded to use JRE 6 almost 5 years back during 12.0.4 to 12.0.6 upgrade and ever since there were dozens of JRE updates & the current JRE 1.8.0_161 (AS on 19th January 2018) & with each update, whatever the runtime parameters we add specifically for Oracle Application used to get removed, forcing us to renter it for each user.

I’ve been searching for a proper solution, not only towards the runtime parameter fix, for the security prompts those started annoying our end users whenever they tried to launch the forms, once after we forcefully updated many users who were sticking to 1.6 JRE

So after referring many documents, using some of the past experiences dealing with security certificates, I believe, have figured out how to get rid of those annoyances, minimum for a while.

Solution 1

Runtime parameters being removed after each JRE update

jre

Add a new environment JAVA_TOOL_OPTIONS with a value -Djava.vendor=”Sun Microsystems Inc.”

You may add it under your specific user or system wide, which I usually prefer so that I don’t have to worry about the next user who will be using the same system (for some reasons)

ENV

Solution 2

Addressing JAVA security prompts

Trying to open the forms with latest JRE without adding exceptions will produce the following JAVA security error

1.png

Now we need to add the application URL with complete port details to the security tab of JRE

2

Now, we will be presented with the “toughest” situation to resolve, which will be explained later.

3

Clicking the More Information, JAVA presents more details about the digital certificate issues, stating the publisher of the digital certificate as UNKNOWN, hence this warning appears for the end user each time whenever the user tries to open a Forms based module.

4

You may require Domain Administrator/Administrator privileges to continue the below fixes

1st we have to retrieve the certificate from Oracle application server. Login as application manager user to LINUX environment (We’re setting up the entire stuffs for R12 application that is running on 64Bit Linux)

download $APPL_TOP/admin/appltop.cer file to local computer (If your implementation team has cloned the instances across, the appltop.cer will remain the same across different servers, so the single certificate integration with the user computer should deal with all different instances of the Oracle application (Test, Development & Production)

Once the certificate is downloaded or copied to the local computer, you need to install it. We’ll install the certificate twice, in two different locations.

Follow the images below for more details

5

We will install the certificate for the “Local Machine”, so that all users using the same computer will get benefitted

6

For the 1st instance, We MUST install this certificate to “Trusted Root Certification Authorities” Container.

For 78

 

9

Once the certificate is imported, We should install the certificate once again, Select “Local System” and follow the prompts. We don’t have to specify the container this time.

10

Now, trying to open a forms based module will present you another warning

11

Checking “Do not show this again for apps from the publisher and location above” will suppress this dialog until the certificate remains the same for the application server.

Hope this helps few out there!

regards,

rajesh

 

 

Oracle Application R12 | HTML Page load issues with Internet Explorer 11 | Windows 10

Hi guys

Our Oracle application R12 (12.0.6) instance has JDK 6 environment & We don’t have any plans to migrate to any recent versions of Oracle Application those are supported by later Java environments.

Almost all users those use Oracle applications are using JRE for many other engineering programs (Calculations programs by Vendors) & frequent updates of the JRE happens automatically at the user ends. We have a significant crowd that is still using JRE 1.6_20 as base JRE, who seldom have the following issue(s) discussed below.

Over Windows 10, Internet Explorer 11 fails to load HTML pages properly & many times after the logon to the application, the HTML menu page is loaded in the explorer, leaving a 4-5 white space lines. Refer the image below

halfpage

Basically this issue is observed with boxes those have latest JRE (x86) installed and used with parameter -Djava.vendor=”Sun Microsystems Inc.”

image

Oracle blogs strictly says, Compatibility view for Oracle applications R12 shouldn’t be enabled for Internet Explorer 11. I have tried enabling the Compatibility view for the Oracle applications, without any positive changes.

As a last resort, I reset the Internet Explorer 11

image

and after restarting the computer, Oracle Application HTML pages started loading correctly, without leaving white spaces between the IE Window and application top frame.

Hope this helps few folks out there!

rajesh

Oracle EBS R12(12.0.x) | Disable concurrent programs executing immediately after a clone

Hi guys

One of the annoyances suffered by business is the alerts and concurrent programs those start sending highly confusing messages to users immediately after a cloning completion. Application tier cloning finishes by trying to start all related services after a successful cloning. Hence the best method to stop the concurrent manager/alerts from firing up is NOT to let the cloning process start application tier level services, so that you can disable alerts/concurrent programs those are NOT required by the cloned instance.

First step is to alter the $COMMON_TOP/clone/bin/adcfgclone.pl (perl script, please refer the image)

image

Change the portion in the highlighted block as shown below. This will stop the clone script from kick starting the application services after a successful clone.

[code language=”perl” gutter=”false”]
elsif (!($PLATFORM eq "win32")) {
print "\n Not Starting application Services for $s_dbSid:\n";
#bugfix:5838814 subgovin
#runPipedCmd("$s_config_home/admin/scripts/adstrtal.$scrExt -nopromptmsg", "$s_apps_user", "$gConfig{‘appspass’}");
}
[/code]
Now, you may proceed to disable all or selected alerts/concurrent programs using backend before starting the Application services.

Please note, this workaround is ONLY tested on 12.0.x releases of Oracle Applications. Please adjust to your environments.

References:

http://onlineappsdba.blogspot.com/2008/06/prevent-concurrent-requests-executing.html

http://oracle-latest-technology.blogspot.com/2013/11/how-to-disable-all-scheduled-concurrent.html

regards,

rajesh

Oracle Applications R12 | error while loading shared libraries: libig.so.0…

 

applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>frmcmp_batch module=$AU_TOP/forms/US/XXITMDTLS.fmb userid=apps/apps output_file=$ONT_TOP/forms/US/XXITMDTLS.fmx Module_Type=FORM compile_all=Special
frmcmp_batch: error while loading shared libraries: libig.so.0: cannot open shared object file: No such file or directory
applprod@erp-prodbak.my.home:/u01/applprod/PROD/apps/apps_st/appl/au/12.0.0/forms/US>

Recently I cloned our Production instance to check the feasibility of OEL 6.7 & as usual securely copied most of the bash profile for the Oracle and Application manager users from the production server.

I came across the “cannot open shared object file: No such file or directory” error while trying to compile an old form module & hadn’t have a clue for how to deal with this error, that happened to me first time in last 8 years with Oracle applications.

I crosschecked the folder permissions against production instance and everything was fine. All I could remember which could have gone wrong was narrowed down to the bash profile for the application user and after validating I realized that I was sourcing a wrong environment file!

Instead of sourcing the “/apps/apps_st/appl/APPSPRODBAK_hostname.env” file, I was sourcing “/apps/apps_st/appl/PRODBAK_hostname.env”. I realized it while trying to echo the $ORACLE_HOME variable as application manager user.

So, if you come across “error while loading shared libraries” error while trying to compile, before start dismantling your instance, try to see whether you have sourced the environment variables correctly.

regards,

rajesh

Java runtime 1.8.0.91 | Oracle EBS R12 (12.0.6)

Hi guys

You could run into a situation after the latest Java client runtime update, your R12 instance failing to load forms, complaining about the lower version of JRE. This is mainly due to the fact that, if your Windows OS is 64Bit, the latest java runtime update installs both 64Bit & 32Bit and most of the R12 environments run against 32Bit JRE.

Internet explorer (the only one browser that runs the JRE properly for EBS R12) loads the 64Bit java runtime by default from a 64Bit Windows OS & eventually the forms will fail to load.

At present the only possible solution to address this situation is to uninstall the 64bit version of java runtime, using “Programs and features” or “Add remove programs” based on the version of Windows you are using.

Hope this helps few out there :)

regards,

rajesh

 

 

R12 | Clone response file | adcfgclone automation

Hi guys

If you are familiar with R12 cloning, the entire process requires loads of input from a DBA before the cloning really starts kicking in. Now, there could be hundreds of reasons for the cloning process to fail and keeping on entering the responses for the cloning prompts are NOT at all fun, minimum for me.

So I started searching for “R12 clone response file” & google landed me in this post

Excellent way Hussein Sawwan-Oracle has explained how to create the response files for both db, apps tier.

clone

First of all, context file to response file creation has a validation flag, which confirms the responses provided are valid throughout the response collection. This validate has a downside with the port pool selection. You will receive a port is not free error while the Oracle application is online, for which a system reboot is most recommended (rather than selecting “No” for the validation process”) after graceful shutdown of both application and database tiers.

I’m using .bash_profile files to source the environment for both my Oracle (oraprod) and Application Manager (applprod) accounts, hence I was able to shorten the efforts like following

As Oracle user (oraprod)

# cd $ORACLE_HOME/appsutil/clone/bin
# perl adclonectx.pl contextfile=$CONTEXT_FILE outfile=/u02/db.xml

Provided the responses and the db.xml was successfully created under /u02 and the context file creation log was created under /u02/log folder.

Please make sure you do “echo $CONTEXT_FILE” to check whether you $CONTEXT_FILE returns a meaningful file name!

As Application Manager user (applprod)

# cd $COMMON_TOP/clone/bin
# perl adclonectx.pl contextfile=$CONTEXT_FILE outfile=/u02/appl.xml

Now you should able to run the cloning processes using the newly cloned context files, for example

# perl adcfgclone.pl dbTier /u02/db.xml
# perl adcfgclone.pl appsTier /u02/appl.xml

That’s all folks, it works like a charm & you are NOT anymore typing in whole those lengthy paths and other variables :)

regards,

rajesh

RC-50004, AC-00005: No write permissions for creating the Context file – /tmp/temp.xml

Hi guys

Linux file/folder permissions could be very confusing for a beginner, minimum for me! After these many years of interacting with Linux I still get confused deciding what rights should be given to users or groups on certain folders :)

I came across a situation where the application tier cloning was continuously failing with the title error

RC-50004, AC-00005: No write permissions for creating the Context file – /tmp/temp.xml ?

I checked the for the /tmp folder permissions and realized the application user (applmgr/appltest) had proper right permissions on /tmp folder, yet the adcfgclone will not proceed after

[code language=”text”]

Do you want to preserve the Display [erp-prod:0.0] (y/n) ? : n

Target System Display [erp-prodbak:0.0] :
RC-50004: Error occurred in CloneContext:
AC-00005: No write permissions for creating the Context file – /tmp/temp.xml
Raised by oracle.apps.ad.context.AppsContext
Check Clone Context logfile /u01/applprod/PROD/apps/apps_st/comn/clone/bin/CloneContext_1208121418.log for details.

[/code]

After few attempts I came against another blog, that was explaining the situation in a different way. http://newgendba.blogspot.com/2009/08/how-to-quick-solve-this-post-clone.html

Blogger has suggested to check whether temp.xml created by another user exist in the /tmp folder, which was the actual reason in my case. I found temp.xml file created by “root” and deleted the same and cloning process just went all the way fine.

 

Hope this info helps few of those once in a bluemoon DBAs out there :)

 

regards,

rajesh