foalabs

Query to find OPP database session details Query: select s.ECID ,s.inst_id, s.SID,s.SERIAL#,p.spid,s.status,s.machine, s.ACTION, s.MODULE, s.TERMINAL,s.sql_id,s.last_call_et,s.event, s.client_info,s.PLSQL_SUBPROGRAM_ID,s.PROGRAM,s.client_identifier , ( SELECT max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) AS sql_text , ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object , ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram , ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object , ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram , 'alter system kill session ' || '''' || s.SID || ',' || s.ser...

Query to find the OPP Actual and Target Process Query:    SELECT b.user_concurrent_queue_name "Concurrent Manager", b.target_node "Node", a.running_processes "Actual Processes", a.max_processes "Target Processes"     FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b    WHERE a.concurrent_queue_id = b.concurrent_queue_id     AND a.concurrent_queue_name = 'FNDCPOPP' ;

SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied When running SQL loader concurrent request, we are seeing below error. Error: SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied SQL*Loader: Release 10.1.0.5.0 - Production on Wed Jun 24 14:08:27 2020 Copyright (c) 1982, 2005, Oracle.  All rights reserved. SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied Program exited with status 1 Concurrent Manager encountered an error while running SQL*Loader for your concurrent request 57822615. Cause: Service Manager logfile (for all managers started by FNDSM).  This causes FNDSM log to grow to enormous size 2 GB $ du -sh FNDSM2342760.mgr 2.1G    FNDSM2342760.mgr Workaround: Workaround 1: Restart Concurrent Managers Workaround 2: Take backup of the file and nullify the file to reduce the size. $ >FNDSM2342760.mgr Also you can refer below Oracle Doc ...

Error in DB Tier Auto Config (java.lang.UnsatisfiedLinkError: libnjni11.so: libnjni11.so: wrong ELF class: ELFCLASS64) After upgrading EBS database to 11GR2, we can encounter error while running autoconfig on the DB node  Error: java.lang.UnsatisfiedLinkError: /d11/oracle/TEST/db/tech_st/11.2.0/lib/libnjni11.so: /d11/oracle/TEST/db/11.2.0/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch) Solution : To fix this issue, Edit the database node xml as below and re-run the autoconfig.  <JDK_TOP oa_var="s_jdktop">/d11/oracle/TEST/db/tech_st/11.2.0/appsutil/jre</JDK_TOP>  <JRE_TOP oa_var="s_jretop">/d11/oracle/TEST/db/tech_st/11.2.0/appsutil/jre</JRE_TOP> update to   <JDK_TOP oa_var="s_jdktop">/d11/oracle/TEST/db/tech_st/11.2.0/jdk/jre</JDK_TOP>  <JRE_TOP oa_var="s_jretop">/d11/oracle/TEST/db/tech_st/11.2.0/jdk/jre</JRE_TOP> Important Note: ---- From 11gR2 DB v...

Downgrading Oracle Database Without Executing catdwgrd.sql or Backup plan to downgrade When we are upgrading the database, we need to ensure that we have a backup plan to downgrade in case of failure. We can create a guaranteed  restore point. My Source version: 10.2.0.4 Target version to upgrade 11.2.0.3 Configure Flashback database in the Source version The Flashback Database feature provides a way to quickly revert entire Oracle database to the state it was in at a past point in time. To use the Flashback database feature the database must be running in archivelog mode. Enable flashback feature in the database when it was running in the source release. Set the environment variables to point to the source Oracle Home (10.2.0.4) and perform a 'startup mount' SQL> startup mount ORACLE instance started. Total System Global Area  918552576 bytes Fixed Size                  2025072 bytes Variable Size        ...

Query to find any Mview Refresh is going on currently in Oracle DB Query: select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

How to Stop Auto Refresh of  Materialized view in Oracle Disable Automatic Refresh of Materialized View. STEP 1.  Connect as mview owner and execute the below query. select * from user_jobs where broken ='N'; STEP 2.  In the WHAT column for the mview refresh job we will see: dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"'); STEP 3.  Get the JOB_ID form the job column and execute below procedure and mview will stop refreshing automatically:   begin dbms_job.broken(JOB_ID,TRUE); commit; end; / Example: begin dbms_job.broken(30,TRUE); commit; end; / How to start refreshing Mview again just run the job. begin dbms_job.run(JOB_ID); commit; end; / Example:   begin dbms_job.broken(30,FALSE); commit; end; /

FRM-92095: Oracle JInitiator version too low If we are trying to open old EBS version which has lower JRE, then we might encounter the error. Solution 1 Add a OS Parameter:   JAVA_TOOL_OPTIONS , and parameter value:  "-Djava.vendor="Sun Microsystems Inc."  as Environment Variables. 1. Open Advanced system settings Start Menu › Control Panel › System and Security › System 2.   Click Environment Variables:- 3.  Add a System variables:- Variable name:  JAVA_TOOL_OPTIONS  Variable value: " -Djava.vendor="Sun Microsystems Inc ." 4.RE-Start your system Solution 2 Add a OS Parameter:  JAVA_TOOL_OPTIONS , and parameter value:  -Djava.vendor="Sun Microsystems Inc."  as Java Runtime Parameter 1.  Open Java Control Panel:-    In the search box enter Java Control Panel Click on Java icon to open the Java Control Panel. 2.  After the Java Control Panel opens,  go to the Java tab as shown below. 3. Click "View", then add ...

Rman Duplicate is Failing With Segmentation Fault Error: RMAN duplicate was done successfully but in last step i.e. opening duplicate database with resetlogs failed with "Segmentation Fault" and when trying to open the database manually the database was getting crashed with and the below error in alert.log SQL> alter database open; alter database open * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced Cause: Undo tablespace name in Target and Duplicate database was different due to which RMAN duplicate was failing with "Segmentation Fault". Solution: Make sure that the undo_tablespace name in the Duplicate database should be same as Target Database. The undo tablespace name can be set using the below parameter in the parameter file UNDO_TABLESPACE=<tablespace name>  and start backup the database in nomount and verify the new undo tablespace name is being used. show parameter undo Then start the duplicate process again.

How to Migrate Linux Cups Printer from one server to other server CUPS stores its configuration at /etc/cups directory, we have to do is copy /etc/cups to a new server.  Steps: 1. Open terminal and type the commands on old Linux server: # tar -cvzf /tmp/cups-$(hostname).tar.gz /etc/cups 2. Copy /tmp/cups* to new server using SCP or any desired method: # scp /tmp/cups* new.linux.server.com:/tmp 3. Login to new server and take backup of existing cups. # mv /etc/cups /etc/cups.backup # cd / # tar -zxvf /tmp/cups* 4. Restart the cups service: #  service cups restart

How to migrate OS User Accounts from one Linux Server to Other/New Linux Server  We might get into these type of requirements when we are migrating from old server to new server. Below are the important files to consider while moving users from one server to another • /etc/passwd contains information about the user. • /etc/group contains information about the group. • /etc/shadow contains encrypted password for the user. • /var/spool/mail contains user mails are stored in this file. • /home/user contains users home directory, hence it may contain user data. Users that are added to the Linux system always start with UID and GID values of as specified by Linux distribution or set by admin.  Limits according various Linux distro: RHEL/CentOS/Fedora Core : Default is 500 and upper limit is 65534 (/etc/libuser.conf). Debian and Ubuntu Linux : Default is 1000 and upper limit is 29999 (/etc/adduser.conf).      Steps:   Connect as root User on old server 1. Backup...

Query to get list of EBS users with responsibility and last login date (responsibility wise) Script: SELECT     usr.user_name,     rsp.responsibility_name,     MAX(ful.start_time) "LAST_CONNECT" FROM     apps.icx_sessions ses,     apps.fnd_user usr,     apps.fnd_logins ful,     apps.fnd_responsibility_tl rsp WHERE      ses.login_id (+) = ful.login_id     AND         ses.responsibility_id = rsp.responsibility_id (+)     AND         ses.responsibility_application_id = rsp.application_id (+)     AND         usr.user_id = ful.user_id  and rsp.responsibility_name is not null GROUP BY     usr.user_name,     rsp.responsibility_name ORDER BY     usr.user_name,     rsp.responsibility_name,     last_connect

Attach a Oracle Home in Oracle Inventory on Server Error: When running opatch lsinventory getting below error Oracle Home       : /u01/oracle/product/11.2.0 Central Inventory : /home/oracle/oraInventory    from           : /u01/oracle/product/11.2.0/oraInst.loc OPatch version    : 11.2.0.3.6 OUI version       : 11.2.0.4.0 Log file location : /u01/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2017-09-12_15-30-47PM_1.log Inventory load failed... OPatch cannot load inventory for the given Oracle Home. Possible causes are:    Oracle Home dir. path does not exist in Central Inventory    Oracle Home is a symbolic link    Oracle Home inventory is corrupted LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo OPatch failed with error code 73 Cause: Checking the inventory.xml file the respective oracle home was not found. Solution: To fix this, add the new O...

Deleting/Removing Control M characters in Linux Control-M is a character found at the end of a line usually in files in linux/unix. We need to delete these characters before processing this file. To put a control m character, please Press Cntrl followed by V and M(Control-V+Control-M) and not as Shift-6-M. Viewing the control M characters we can use below commands: 1) cat -v filename 2) vi -b filename To Remove Control M characters we can use below methods: 1. dos2unix The simplest of all is using the dos2unix command. dos2unix filename Note: I have seen occurences where the this command dont remove all Control-M characters so I prefer below menthod   2. tr Command $ tr -d '^M' <filename To store output after deleting the Control-M we can use below tr -d '^M' <filename > newfile We can also use below  $ tr -d "\015" <filename >newfile 3. sed command $ sed -e 's/^M//g' filename To remove and save changes to file use below $ sed -i ...

How to setup SAMBA in Linux In this post I am going to share steps to configure Samba in Linux. For this I have created 2 virtual machines out of which once will act as client and other will act as server. 1) Centos1home.lab--Server Machine for Samba 2) Centos2home.lab--Client Machine which will access Samba Shared file system. Setup on Server Machine: • Install samba packages # Become root user # yum install samba samba-client samba-common • Enable samba to be allowed through firewall (Only if you have firewall running) # firewall-cmd --permanent --zone=public --add-service=samba [root@centos1home ~]# firewall-cmd --permanent --zone=public --add-service=samba success # firewall-cmd --reload [root@centos1home ~]# firewall-cmd --reload success • To stop and disable firewall or iptables( Optional ) # systemctl stop firewalld # systemctl stop iptables # systemctl disable firewalld # systemctl disable iptables • Create Samba share directory and assign permissions # mkdir -p /funoracleapps/...