Oracle Application R12 12.2 one user cannot logon

Updated on: 28th Feb 2023

Our previous solution to fix “one user cannot” logon was doomed by additional issues sooner than expected. We had to regenerate the jar files using ADADMIN finally to close the issue.

===

We are using Oracle EBS R12 for last 12 years and recently upgraded to 12.2.10. We hardly had much technical issues with the instance until recent days, then came a real annoyance.

One particular user was unable to logon. Instead of OA_HTML page, he was either getting redirected to logout page or the OA_HTML page keeps on trying to load. One of the workarounds we came across was, resetting his password. Once the password changed, he was taken to setup password page and to regular landing page. Well, this also turned into troublesome as each time his session expired, we were forced to reset his password.

So what went wrong? We had an issue with concurrent manager outputs and logs opening into a blank page recently. This happened after RMAN backup up, that is scheduled and we had to run autoconfig to fix this issue. Although autoconfig fixed that issue, opened issues with user logon, as explained above. We kept on watching the alerts for possible errors and there were none. Anyhow, we decided to do the following as the ONLY one relevant document we were able to find on Oracle support was “Unable To Login With One User On Oracle EBS Application (Doc ID 2480008.1)” and the case described was not our case.

This is what finally worked:

  • Cleared global cache (didn’t fix issues)
  • Shutdown Apps tier
  • Ran autoconfig on database Tier
  • Ran autoconfig on both Run and Patch file systems
  • Restarted Apps tier and the issues were resolved

We really don’t know what happened after the RMAN & we didn’t update/altered the OS (Oracle Linux 7). Well, things could go south once in a while, after all Oracle EBS is such a huge and complex repository!

Oracle “directory” object | OS level permissions

We are using Oracle EBS R12 and for a custom module, wanted to log the Oracle seeded API outputs to custom log files for later scrutiny & error corrections.

Following the standard procedure, create the folder like below

create or replace directory OMS_LOGS as '/u01/applmgr/oms_logs'

and started testing the directory using SQL Developer where I am logged in as APPS user

I kept getting the below errors

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

and the detailed errors said this could be due to OS level access permissions. Luckily I landed on a stackoverflow discussion & one of the answers clearly said this error “could” be due to the file/folder permissions for user “oracle”.

As “root” I changed the permissions for the path “/u01/applmgr/oms_logs” like below

chmod g+w /u01/applmgr/oms_logs

and that fixed the invalid file operation errors. Hope this helps someone out there!

Oracle Application R12 | The Function Is Not Available Under The Responsibility

Hello guys

It looks like I am getting something new everyday to blog…the latest is from Oracle Application R12 once after I added a new responsibilities to few users.

The Functions those are listed under new responsibility from the HTML page will not launch, instead a popup window appears with the statement “The Xyz Function Is Not Available Under The abcd Responsibility”

I recently had a nightmare with a custom form, that was revamped almost after 6 years of usage. Although the compiling on Production instance doesn’t have any issues, only portions of the form would load & the only few elements displayed were totally misaligned and the cells looked like just a plain straight line…

After many failed attempts, I tried to clear the cache, which we didn’t from a long time. The culprit was the cache. After releasing the cache, I recompiled the form & everything was fine.

With the above issue also, our issue was with the cache. After releasing the cache, the users were able to launch the form from the HTML page itself. If, this didn’t resolve your issues, have a look here

https://knoworacle.wordpress.com/2012/11/03/the-function-is-not-available-under-the-responsibility/

regards,

rajesh

Oracle E-Business Suite | Should you clone on your desktop using Virtual Machine?

Hello guys

A long thread name? Well the topic is vast, hence the long name. I’ve been dealing with Oracle EBS or applications R12 from last 9 years. Initially I was ONLY developing for the new infrastructure, that gradually changed to taking care of the whole instance.

Painfully, however definitely I did familiarize myself with Linux (RHEL) & the database, exposing myself to building systems with copies of EBS running for development & testing.

Our instance is approximately 650GB as on date, including both application stack and database & a cold backup is hardly 125GB in tar balls. I have attempted & succeeded to build the cloned instances on my home desktop machine many times, however the performance was a huge issue, forcing me to discard the setups quite often.

This time, I decided NOT to discard as my new desktop at home is a beast compared to my previous machine, that was neither less a best ;) & to figure out “something” that will address the “performance” bottleneck.

I created a new VM using Oracle VirtualBox with following specs

6 processors, 20GB memory & 2 fixed size VDI files (120GB, 600GB) respectively for application and database repositories. I was aware of a limitation already, I was setting up the VDI files on a Western Digital Green series 2TB drive! which spins at 5400RPM!

Well, everything went smooth & and I had the instance up and running in couple of hours time & this time, the response of the instance was awesome. I even boasted about finally winning over the “biatch” to my team & sat back feeling “too proud” for the moment.

Next day (Month End 30th April 2019)

I am all excited after figuring out a way to flush GNOME desktop environment & replace it with Xfce & new tricks…

Started the VM at home, started the EBS instance and tried to access the instance from the same machine. I couldn’t even get the login page…something was gravely wrong. I decided to check the performance monitor and found the following:

Slowly I was forced to recognize the terror! The Standard Concurrent manager was configured to process 25 requests at the same time with a cache of 5 & 30 seconds sleep between the requests. Our month end has a number of scheduled jobs + Gather schema statistics in the queue. My VM was breaking up with the I/O. My 2TB storage oriented disk was NOT spinning fast enough to provide the data for the processes & I was left with the BIGGEST question of the hour “Now what?”

I stopped the Concurrent manager, adjusted the processes to 5 for Standard manager & restarted the instance. Left the instance running whole night and 1st May morning, the instance was back to normal performance as whole the scheduled jobs were finished during the night.

Next day I added one 1TB SSD & moved the application & database VDI files over to it. I was able to get the login screen within 2-3 seconds once after the application started from the VM. I submitted number of create accounting and other resource hungry jobs, which were completed in few seconds time…

Now, my setup is ONLY for the sake of it. It doesn’t have many users, it is idle most of the time & almost every day I shutdown the desktop machine after a day’s usage. This might not be the scenario at a real TEST environment. You may need to implement archive logging & RMAN, those all requiring more space & faster access to storage. A Desktop has less resources & the ONLY positive element you are going to live with is the pleasure of building it & knowledge gained while fixing few new issues.

So, can you build a performance oriented R12 using Desktop environment, the short answer is yes. Does it worth the efforts? Well, definitely YOU are the ONE who have to answer it.

Follow the space & soon I will post a thread explaining the entire exercises. If you are in a hurry, you may refer this

The above article loosely explains how to clone R12 instance on Linux 7. However the same could be followed for Linux 6 as well (both RHEL & OEL)

regards,

rajesh

 

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,

 

RMAN | Restore Linux Backup to Windows

Update(06-March-2018)

Once after I built the Windows Instance from Linux Backups, I have started a thread with community.oracle.com, expecting answers for few concerns. Below, please have a look at the thread

https://community.oracle.com/tech/developers/discussion/4126422/rman-oracle-11gr2-11-2-0-4-restoring-linux-backup-to-windows-instance

jgarry states, as the redo logs are not applied, I am risking data loss. Now, I don’t really think someone would move from Linux to Windows for Oracle database, when the opposite happens most of the times.

We will consider the entire exercise as limited LAB & for some reasons, if this has to be performed for a production instance, make sure AN IMAGE BACKUP/Cold Backup is available to avoid possible data loss. Cold backup routine as below:

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN>backup database; #replace with your backup routine

RMAN>

Hi guys

Greetings. I have been hell busy during last few weeks. Traveling, fixing stuffs & as usual learning new “things”. This time I am working with RMAN, the recovery manager for Oracle database & trying to establish something that is NOT that orthodox or this is how I feel once after going through many documents.  Well, remember we did hack installations, we got “stuffs” work ;). So why not give it a try?

Attempted: Restoring Linux RMAN backup(s) to Windows. Doable? Well, YES.

Cons: No idea yet (6-March-2018, possible data loss as the redo log files will not be readable)

Could be used at Production: At your own risk

I am not going to break the flow anywhere, it is a lengthy one shot document. Prior giving it a try, make sure you have copied the backups from Linux machine to your Windows Machine.

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

C:\WINDOWS\system32>d:

D:\>cd Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>oradim -new -sid LINUXDB
Instance created.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 1 13:34:58 2018

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

connected to target database (not started)

RMAN> set DBID=439294518 shutdown immediate

using target database control file instead of recovery catalog
Oracle instance shut down
--We will use a copy of Windows environment specific INIT file to
--Manually create a INIT file for our new database
--sample
--make sure you have created all paths mentioned in the INIT file prior restarting the database
LINUXDB.__db_cache_size=486539264
LINUXDB.__java_pool_size=8388608
LINUXDB.__large_pool_size=8388608
LINUXDB.__shared_pool_size=746586112
LINUXDB.__streams_pool_size=0
*.audit_file_dest='D:\Oracle11g64\admin\LINUXDB\adump'
*.compatible='11.2.0.4.0'
*.control_files='D:\Oracle11g64\oradata\LINUXDB\control01.ctl','D:\Oracle11g64\oradata\LINUXDB\control02.ctl','D:\Oracle11g64\oradata\LINUXDB\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DB11G' #source database name
*.db_recovery_file_dest='D:\Oracle11g64\oradata\LINUXDB\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=H:\db11g\archivelog'
*.log_archive_format='DB11G_ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1256194048
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
--Sample INITFILE end

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1252663296 bytes

Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

RMAN> restore controlfile from 'H:\db11g\backup\bkpcontrol_file.ctl_DB11G_20180301';

Starting restore at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL
Finished restore at 01-MAR-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with 'H:\db11g\backup';

Starting implicit crosscheck backup at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 01-MAR-18

Starting implicit crosscheck copy at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 01-MAR-18

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern H:\db11g\backup

List of Files Unknown to the Database
=====================================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

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

List of Cataloged Files
=======================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

--Once the catalog built, query the schema for datafile details
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/DB11G/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3 0 UNDOTBS1 *** /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/DB11G/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/DB11G/temp01.dbf

--We can get the last sequence number of the archivelog by running the below query
--Which we will use for media recovery

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DB11G
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000005_0969624950_0001.arc

2 1 6 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000006_0969624950_0001.arc

3 1 7 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000007_0969624950_0001.arc

4 1 8 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000008_0969624950_0001.arc

--As 8 being the last sequence, we will add 8+1=9 AS the sequence number for the media recovery
RMAN> run
{
SET UNTIL SEQUENCE 9 THREAD 1;
set newname for datafile 1 to 'D:\Oracle11g64\oradata\LINUXDB\system01.dbf';
set newname for datafile 2 to 'D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf';
set newname for datafile 3 to 'D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf';
set newname for datafile 4 to 'D:\Oracle11g64\oradata\LINUXDB\users01.dbf';
restore database;
switch datafile all;
recover database;
}

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\Oracle11g64\oradata\LINUXDB\users01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to D:\Oracle11g64\oradata\LINUXDB\system01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:22
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:35
Finished restore at 01-MAR-18

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=969632162 file name=D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF

Starting recover at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000007_0969624950.0001 thread=1 sequence=7
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000008_0969624950.0001 thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-MAR-18

RMAN> exit

Recovery Manager complete.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>

--SQL Activities
Microsoft Windows [Version 10.0.16299.248]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>d:

D:\>cd D:\Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:18:44 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT GROUP#, STATUS FROM V$LOG
2 /

GROUP# STATUS
---------- ----------------
1 INACTIVE
3 CURRENT
2 INACTIVE

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

--As the datafiles for the redo logs are pointing towards the LINUX file system
--We need to recreate redo log files for the Windows environment
--Prior that, we will try to drop those redo log groups which are inactive
--In our case redo log group 3 is the one active, hence 1,2 should be available to be dropped

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance
linuxdb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/DB11G/redo02.log'

--Minimum 2 redo log files are required for the instance, so dropping redo log group 2 fails in our case
--So we will add more redo groups to the database

SQL> alter database add logfile group 4
2 ('D:\Oracle11g64\oradata\LINUXDB\redo04.rdo','D:\Oracle11g64\oradata\LINUXDB\redo04a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 5
2 ('D:\Oracle11g64\oradata\LINUXDB\redo05.rdo','D:\Oracle11g64\oradata\LINUXDB\redo05a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 6
2 ('D:\Oracle11g64\oradata\LINUXDB\redo06.rdo','D:\Oracle11g64\oradata\LINUXDB\redo06a.rdo') size 50M;

Database altered.

--Now try to drop the rego log file group 2
SQL> alter database drop logfile group 2;

Database altered.

--Can we drop the redo group 3 finally?
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance linuxdb (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/DB11G/redo03.log'

--We cannot drop the redo group 3 because it is being the current redo group for the database
--We cannot switch the log file group because the database is not open

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

--We cannot rename the redo log group members because the filenames are not recognized by the Windows environment

SQL> alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log';
alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/DB11G/redo03.log"

--Though we know the database cannot be opened, let us give it an attempt
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/DB11G/redo03.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
--Expected, we will proceed to next step

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';
alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc'
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount
2 /

Database altered.
--We will trace the current control file to a readable format now

SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

--Start the database in nomount state, so that we can try to create a fresh control file for the database
--Using NORESETLOGS
--COPY AND PASTE THE CREATE CONTROL FILE SEGEMENT FROM THE controlfile trace
--Do not forget to remove the line(s) pointing towards redo log files from LINUX enviornment
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;
CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread

--So, next attempt we will try to create the control file using RESETLOGS

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

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

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
Database opened.
SQL> create temporary tablespace TEMP01 TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP01.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMP TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP02.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

--You may drop the TEMP01 tablespace and content once after a shutdown, startup procedure

--Now we will attempt to change the DBID & DATABASE name for the database

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

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>nid target=/ DBNAME=LINUXDB

DBNEWID: Release 11.2.0.4.0 - Production on Thu Mar 1 14:52:32 2018

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

Connected to database DB11G (DBID=439294518)

Connected to server version 11.2.0

Control Files in database:
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL

Change database ID and database name DB11G to LINUXDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 439294518 to 3227660209
Changing database name from DB11G to LINUXDB
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - modified
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\TEMP02.DB - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down

Database name changed to LINUXDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database LINUXDB changed to 3227660209.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:52:57 2018

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter system set DB_NAME=LINUXDB scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Give it a try, recompile the invalid objects (my case there were none).

That’s all folks

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

Oracle Weblogic | Reports 11g | cgicmd.dat | Key mapping issues

 

Hi guys

I’ve been installing and configuring Weblogic 10.3.6 & 12c (recently) and came across a peculiar issue, where the reports server failing to map the keys I’ve specified with cgicmd.dat file.

The best I could recollect from my memories was, I only added the keys to a single instance of cgicmd.dat file, as there are many instances of the same file once after classic domains are created.

Under linux installations you will find the file in 3 different locations once after the domain created and WLS_REPORTS server started.

 

[root@wls Middleware]# find /u01/Oracle/Middleware/ -name cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat
/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/cgicmd.dat
/u01/Oracle/Middleware/Oracle_FRHome1/reports/conf/cgicmd.dat

[root@wls Middleware]#

image

Under usual scenarios you need to add the keys ONLY to the cgicmd.dat file present in

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/config/fmwconfig/servers/WLS_REPORTS/applications/reports_11.1.2/configuration/

folder.

With my latest installations of 12c & 11g, I had issue getting the keys mapped properly & after loads and loads of googling I decided to add the same key to all available instances of cgicmd.dat one after another.

So I started with the first instance of cgicmd.dat file, that was found under

/u01/Oracle/Middleware/user_projects/domains/ClassicDomain/servers/WLS_REPORTS/tmp/_WL_user/reports_11.1.2/uxabaw/configuration/cgicmd.dat

which is WLS_REPORTS temporary folder. I added the key, restarted the WLS_REPORTS server & the key was mapped as required!

This could be a nasty bug because I don’t remember adding the keys to multiple files while I tried out forms and reports 11g Release 2 almost 2 years back!

The most interesting part of the entire exercise is, once the key mapping happens successfully, you can remove the keys from cgicmd.dat file that in the temporary location and everything works! Could be the magic of cache Winking smile

So if you come across key mapping issues, you know where to look at for a quick fix.

for Windows7bugs

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