This blog offers expert insights on Oracle E-Business Suite (EBS), Database Administration, and Oracle Cloud Infrastructure (OCI). Explore in-depth tutorials, best practices, and solutions for Oracle EBS management, Oracle Database performance tuning, and OCI cloud administration. Whether you're optimizing Oracle databases, migrating to the cloud, or managing enterprise applications, our blog provides the latest tips and strategies to enhance your Oracle environment and ensure peak performance.
29 Dec 2020
How to update override email address using backend sql query in EBS 11i/12.x?
26 Dec 2020
EBSO 12.2.x : Query to check status of ADOP session
set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;
Decoding Cutover status:
cutover_status='Y' 'COMPLETED'
cutover_status not in ('N','Y','X') and status='F' 'FAILED'
cutover_status='0' 'CUTOVER STARTED'
cutover_status='1' 'SERVICES SHUTDOWN COMPLETED'
cutover_status='3' 'DB CUTOVER COMPLETED'
cutover_status='D' 'FLIP SNAPSHOTS COMPLETED'
cutover_status='4' 'FS CUTOVER COMPLETED'
cutover_status='5' 'ADMIN STARTUP COMPLETED'
cutover_status='6' 'SERVICES STARTUP COMPLETED'
cutover_status='N' 'NOT STARTED'
cutover_status='X' 'NOT APPLICABLE'
EBS 12.2.X Patching: ADOP Cutover failed while running txkADOPCutOverPhaseCtrlScript.pl
[PROCEDURE] Starting Middle Tier Services
[UNEXPECTED]Error occurred running "perl /test/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPCutOverPhaseCtrlScript.pl -contextfile=/test/app/fs1/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -patchcontextfile=/test/app/fs1/2/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -promptmsg=hide -console=off -sessionid=14 -timestamp=20201108_194155 -outdir=/test/app/fs_ne/EBSapps/log/adop/14/20201108_194155/cutover/testebsmt111 -action=forcestartup"
[UNEXPECTED]Cutover phase has failed.
Cause:
1. Check Status of adop session using below sql query:
set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;
For my issue, cutover status was 5, which means ADMIN startup is completed.
2. Restart the cutover phase with mtrestart=no option
adop phase=cutover mtrestart=no
3. Fix the issues related to services startup on RUN filesystem (which is switched after cutover).
4. Startup the services
5. Perform cleanup phase and complete the adop cycle
adop phase=cleanup
10 Dec 2020
Useful srvctl commands
adpreclone.pl Error on Applications Tier
Autoconfig Error : ORA-01422: exact fetch returns more than requested number of rows
27 Oct 2020
What is Split Brain Syndrome in Oracle RAC?
Split brain syndrome occurs when the Oracle RAC nodes are unable to communicate with each other via private interconnect, but the communication between client and RAC node is maintained. This can cause data Integrity issues when the same block is read or updated by two nodes and changes done from one node are overwritten by the other node because the block being changed is not locked.
When a node fails, the failed node is prevented from accessing all the shared disk devices and groups. This methodology is called I/O Fencing, Disk Fencing or Failure Fencing.
The node which first detects that one of the node is not accessible will evict that node from the RAC cluster group.This problem is solved by configuring the heartbeat connections through the same communication channels that are used to access the clients.
What causes Node eviction in Oracle RAC?
What is node eviction?
Node eviction in RAC is done when a heartbeat indicates that a node is not responding,
and the evicted node is re-started to make it a part of cluster.
Causes for RAC node eviction:
Node eviction on Oracle RAC environment can be due to any of the below reasons.
- A failure of any of the major hardware components (CPU, RAM, network interconnect).
- A server that is experiencing RAM swapping.
- When communications to the voting disk is interrupted, causing the disconnected node to be evicted and re-boot.
- Database or ASM hang condition.
Below is the list of important log files to review in case of a node eviction
- Clusterware alert log
- Database alert log
- CSSD agent logs
- CSSD monitor logs
- System Message logs (/var/log/messages)
What is Flashback feature in Oracle Database?
Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.
Points to Remember
- Flashback Database command can be run either from SQLPLUS or using RMAN Utility.
- FLASHBACK DATABASE command can be used to rewind the database to a target time, SCN or a log sequence number.
- Flashback command works by undoing the changes made to the data files that exist when you run the command.
- Flashback can fix only logical failures, not physical failures.
- If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.
- Avoid using FLASHBACK DATABASE with a target time or SCN that coincides with a NOLOGGING operation, it can cause block corruption.
How to enable Flashback Database feature in Oracle?
Prerequisites for Flashback Database and Guaranteed Restore Points
Flashback Database
Configure the following database settings before enabling Flashback Database:
-Your database must be running in ARCHIVELOG mode.
-You must have a fast recovery area enabled.
-For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM.
Guaranteed Restore Points
To use guaranteed restore points,the COMPATIBLE initialization parameter must be set to 10.2.0 or greater.
Steps to enable Flashback Database:
1. Connect to sqlplus as sysdba and set the desired value for Flashback retention target using below command.
SQL>ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
Here, Flashback retention target is set to window of 3 days(4320 Minutes), default value is 1 day (1440 Minutes).
2. Enable the Flashback Database feature for the whole database using the following command:
SQL>ALTER DATABASE FLASHBACK ON;
3.Use the following command to check if Flashback Database is enabled for your target database:
SQL>SELECT FLASHBACK_ON FROM V$DATABASE;
14 Jun 2020
Oracle EBS 12.2.X Login fails with "Unable to create anonymous session. ICX_SESSION_CREATION_FAILED"
In a 12.2.6 EBS environment, Login fails with below error
Unable to create anonymous session. ICX_SESSION_CREATION_FAILED (userid=6) exception oracle.apps.fnd.common.PoolException: Exception creating new Poolable object. Encountered a java exception with the message Exception creating new Poolable object. Cause:java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: java.lang.reflect.InvocationTargetException.
Analysis:
The error indicates that there are not enough resources to create a new JDBC session. oacore_server.log has the below error messages
####<Aug 08, 2017 3:43:05 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994585553> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:07 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <887561> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994595562> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <955123> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
Number of processes capacity for the Datasource "EBSDatasource" is exhausted.
Solution:
1. Increase the number of sessions in EBSDatasource
Login to weblogic Console, Navigate to Datasource, click on EBSDatasource.
Click on Lock and Edit, Go to Connection Pool Tab, Increase the value in Number of sessions field.
Click on Activate Changes.
This change does not require any restart of services.
(or)
Alternate Solution:
2. Bounce oacore_server to release any inactive sessions and free up the resources
cd $ADMIN_SCRIPTS_HOME
admanagedsrvctl.sh stop oacore_server
admanagedsrvctl.sh start oacore_server
Query to check installed modules in Oracle EBS
The below query query gives the list of Installed/Not Installed/Shared Product Modules in Oracle E-Business Suite (EBS) application
set pages 20000;
col application_id for 9999;
col application_name for A50;
col status for A1;
col application_short_name for A10;
select fa.application_id,
fa.application_short_name,
fpi.status,
fatl.application_name
from
fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fatl
where
(
fa.application_id = fpi.application_id and
fa.application_id = fatl.application_id and
fatl.language = 'US'
)
order by fa.application_short_name;
Check for values in Status column of the output, usually either I or N or S.
I - Installed
S - Shared
N - Not Licensed
Query to check AD and TXK Code level in a Oracle E-Business Suite environment
While preparing patch analysis on EBS environments, some times we find in README file that AD/ TXK level should be at a certain code level to apply the patch.We can use the below query to check AD and TXK Code level.
select ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');
Sample Output
$sqlplus apps
SQL> select ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');
ABBREVIATION NAME CODELEVEL
------------------------------ ---------------------------------------- ----------
ad Applications DBA C.11
txk Oracle Applications Technology Stack C.11
11 Jun 2020
Connecting to a Database from SQLDeveloper fails with IO Error: The Network Adapter Could Not Establish The Connection
Below is the connection string provided to user for Oracle Database Connection. RAC Virtual IP is provided to connect to Database.
TESTDB01=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=test001-vip1.domain.com)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=test002-vip1.domain.com)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=test003-vip1.domain.com)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=TESTDB01)
(INSTANCE_NAME=TESTDB01)
)
)
User tried to connect using one of the VIP and got the below error in SQLDeveloper.
Status : Failure -Test failed: IO Error: The Network Adapter Could Not Establish The Connection.
Analysis:
Verified the Oracle Database Services and Listener Services are running fine.
Other Users were able to connect to SQLDeveloper using the same Connection String.
Solution:
User does not have network communication open to port 1521 on Oracle RAC Virtual IP.
ping test001-vip1.domain.com
ping test002-vip1.domain.com
ping test003-vip1.domain.com
telnet test001-vip1.domain.com 1521
telnet test002-vip1.domain.com 1521
telnet test003-vip1.domain.com 1521
Ping to VIP address works fine but Telnet to port 1521 on the VIP address is giving "Connect Failed" Error.
When Network communication is opened on port 1521 on all the Oracle RAC Virtual IP servers/Addresses, User can connect to Oracle Database using SQLDeveloper.
4 Jun 2020
Troubleshooting concurrent requests struck at Post processing Phase
Need to follow the below steps to perform clean shutdown of Oracle Concurrent Manager.
a) Put Pending Concurrent Requests on hold using the below sql queries.
+Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
+select count(*) from apps.conc_req_on_hold
+ update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);
NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same
+ Commit;
You can find more details about putting Pending Concurrent Jobs on Hold here.
http://www.appsdbadiaries.com/2016/01/concurrent-requests-on-hold.html
b)Bring Down Concurrent Manager using adcmctl.sh stop
c) Update the status of Struck Concurrent Requests to "Terminated"
SQL> update fnd_concurrent_requests set status_code='X',phase_code='C' where status_code='R' and phase_code='R';
Commit;
Use Concurrent Manager Recovery Wizard from OAM to clear the database sessions associated with cancelled Requests.
d) Start Concurrent Manager using adcmctl.sh
e) Remove Hold on Concurrent Requests
SQL>update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);
Commit the changes
SQL>commit;
Steps to create Database link from Oracle Database to Microsoft SQLServer Database:
Pre-requisites
1. Check Network communication is Opened from Oracle Database Server to MSSQL Database Server using telnet
2. Download the Oracle Gateway Server Software same as your Oracle Database version.
Installation of Oracle Gateway Software
=========
Hope you have downloaded the Dg4msql software for sql server
1> Create a directory as gateway under $ORACLE_BASE directory.
2) Invoke the GUI , Select Oracle Gateway for MYSQL and enter the sql server details at the time of installation
3) Before completing the installation, OUI invokes the NETCA and create a new listener for gateway with new port (ex:1526)
Note; In case if you have defined any bash profile for TNS_ADMIN, then please comment it
4. Follow the below note for pre-req for gateway listener
How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install ( Doc ID 562509.1 )
edit your gateway listener as recommended in the above note.
5. create a tnsentry in $ORACLE_HOME/network/admin
6. export TNS_ADMIN=$gateway home/network/admin
lsnrctl stop <gateway listener>
lsnrctl start <gateway listener>
tnsping <gateway alias>
conn / as sysdba
create public database link <test> connect to "<sql server user>" identified by "<password>" using '<tnsnames.ora>'
select * from dual@test;
Script to check free space and used space in Oracle Database tablespaces
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/
RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel
This is an error during Database Clone using RMAN. I was trying to restore database using RMAN Backup
RMAN-00571: ===========================================================
RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database
RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information:
Cause: Backup Configuration was modified.
Solution:
Use the below command to Resolve the error and continue with Database Restore
rman auxiliary /
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to 'DEV' backup location '/Daily_Backup/Backup_16042018';
}
exit;
EOF
adcfgclone.pl script failed during instantiate OHS Stage
Issue is because opmn components were unable to start up with below error messages.
globalInitNLS: NLS boot file not found or invalid
-- default linked-in boot block used
XML parser init: error 201.
globalInitNLS: NLS boot file not found or invalid
-- default linked-in boot block used
Further analysis on the clone logfiles for OHS creation shown that ORA_NLS10 parameter is unset before cloning.
Resolution:
$unset ORA_NLS10
+Restart the failed script
Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing
Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.
Analysis:
Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer
Identify the Concurrent Requests which are in Running Status.
Get the Database Session Details of the currently running concurrent requests using the below Query:
SELECT DISTINCT a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';
Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.
select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;
-- sid value to be taken from output of Sql Query in Step 1
If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.
I will cover more details about SQL Tuning in another post.
How to add space in ACFS filesystem?
1.Before extending the mountpoint storage we need to add an LVM of required Storage Capacity on the server.
For checking the LUN's available on the server, click here
For now, assume the LUN added is /dev/sdf1
2. As root User, check the available disks by running the below command
$ /usr/sbin/oracleasm listdisks
DATA01
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03
3. Create a new disk using the below command
/usr/sbin/oracleasm createdisk DATA02 /dev/sdf1
Where DATA02 is the name of new disk added
/dev/sdf1 is the LUN added for storage extension.
4. Verify the disk is added by running the below command.
$/etc/init.d/oracleasm listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03
5. Navigate to the other cluster nodes and run the scandisks command to reflect the newly added disk on all the nodes of cluster.
$/etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK ]
$/etc/init.d/oracleasm listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03
6. Now login to the database server as GRID user (Where ASM instance is running) to add space to ACFS.
Set the environment as required
export ORACLE_HOME=/u01/app/12.1.0/grid
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM1
$sqlplus / as sysasm
SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA02';
Diskgroup altered.
7. Increase the space on filesystem
As root user,
$acfsutil size +1G /oradata
acfsutil size: new file system size: 591833071616 (564416MB)
How to Resolve FRM-92101 error while launching forms in Oracle EBS 12.2.x versions?
-
When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace. SQL> ALTER TABLESPAC...
-
Below Query is Used to verify if MRP process is running fine on Standby Database, after any maintenance activity that requires disconnect ...
-
Issue: FS_CLONE is failing with error :Could not find patch context file from database Error details: $adop phase=fs_clone ============== C...