Stay at Home!! Be Safe!! Take Care!!

Thanks all my viewers for your support, I am an Oracle Ace now. For Any queries, please mail us at support@funoracleapps.com

Query to find OPP database session details

No comments

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.serial# ||',@'|| s.inst_id||''''|| ' immediate;' kill_session
from gv$session s ,gv$process p
where
s.module like '%OPP%'
and p.addr=s.paddr
and p.inst_id = s.inst_id

No comments :

Post a Comment

Query to find the OPP Actual and Target Process

No comments

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' ;

No comments :

Post a Comment

SQL*Loader-128: unable to begin a session ORA-01017: invalid username/password; logon denied

No comments

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 for more details and other solution

Concurrent Requests fail with SQL*Loader-101: Invalid Argument for username/password (Due to FNDSM Log File Size Grows 2 GB ) (Doc ID 1542216.1)



No comments :

Post a Comment

Error in DB Tier Auto Config (java.lang.UnsatisfiedLinkError: libnjni11.so: libnjni11.so: wrong ELF class: ELFCLASS64)

No comments

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 version onwards AutoConfig on DB node uses the JRE from <Oracle_Home>/jdk/jre where as earlier versions of DB uses JRE from $ORACLE_HOME/appsutil/jre

No comments :

Post a Comment

Downgrading Oracle Database Without Executing catdwgrd.sql or Backup plan to downgrade oracle db

No comments

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             239077776 bytes
Database Buffers          675282944 bytes
Redo Buffers                2166784 bytes
Database mounted.

Turn on flashback

SQL> alter database flashback on;

Database altered

Create guaranteed restore point

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

Run the following query to view all guaranteed restore points created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE,
     STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME             SCN     TIME                 DI         GUA     STORAGE_SIZE
----------       --- ------------       -------------    ---    ------------
BEFORE_UPGRADE  34552155 23-JUN-20  10.58.42.000000000 AM  YES    312672256

Query the dba_registry view to see the component status and version before upgrade

SQL> select comp_name, status, version from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
STATUS      VERSION
----------- ------------------------------
Oracle Database Catalog Views
VALID       10.2.0.4.0

Oracle Database Packages and Types
VALID       10.2.0.4.0


Shutdown the database

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

Upgrade the database to the target version(Dont change compatibility parameter to higher version while upgrade)

Set the environment variables to point to the target Oracle Home (11.2.0.4) to which the database will be upgraded to.

compatible-10.2.0.4

Please note that changing COMPATIBLE initialization parameter at this stage will prevent use of the downgrade procedure or the ability to flash back the database to a point prior to this.

Startup the database in upgrade mode using the 11.2.0.4 Oracle Home

SQL> startup upgrade (using 11.2.0.4 Oracle Home)
ORACLE instance started.

Total System Global Area  918552576 bytes
Fixed Size                  2077008 bytes
Variable Size             234884784 bytes
Database Buffers          675282944 bytes
Redo Buffers                6307840 bytes
Database mounted.
Database opened.

Run the upgrade script (catupgrd.sql)

SQL> @?/rdbms/admin/catupgrd.sql


Validate any invalid objects after upgrade if any

SQL> @?/rdbms/admin/utlrp.sql

Query the dba_registry view to see the component status and version after upgrade

SQL> select comp_name, version, status from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
11.2.0.4.0 VALID

Oracle Database Packages and Types
11.2.0.4.0 VALID
 

Downgrading database to previous version using Flashback database

Note that data from any transactions that occur after the point in time to which the database is recovered are lost. Flashback Database is an excellent tool for backing out the database upgrade, Using Flashback Database with sufficient space and creating a guaranteed restore point immediately prior to the upgrade is the fastest method to fallback as compared to a restore operation or a database downgrade.

Using flashback database rather than the conventional downgrade procedure is far quicker. This is only practical when only the database upgrade has been done and no application data changes have occurred.

The steps for flashing back the database after upgrade or after a failed upgrade are

Set the environment variables to the 11.2.0.4 Oracle Home

Shut down the upgraded database

SQL> shutdown immediate

Startup mount the 11.2.0.4 database using the 11.2.0.4 Oracle Home and flashback the database to the guaranteed restore point which was created prior to the upgrade.

SQL> startup mount

SQL> flashback database to restore point before_upgrade;

SQL> shutdown immediate

Set the environment variable to point to the old Oracle Home (10.2.0.4)

SQL> startup mount

SQL> alter database open resetlogs;

query the dba_registry view to see the database component status and version

SQL> select comp_name, version, status from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
10.2.0.4.0 VALID

Oracle Database Packages and Types
10.2.0.4.0 VALID



No comments :

Post a Comment

Query to find any Mview Refresh is going on currently in Oracle DB

No comments

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;

No comments :

Post a Comment

How to Stop Auto Refresh of Materialized view in Oracle

No comments

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;
/

No comments :

Post a Comment

FRM-92095: Oracle JInitiator version too low

No comments

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:-

Java
3. Add a System variables:-

  • Variable name: JAVA_TOOL_OPTIONS 
  • Variable value: "-Djava.vendor="Sun Microsystems Inc."



parameter


    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 -Djava.vendor="Sun Microsystems Inc." into the Runtime Parameter box
     
     

    No comments :

    Post a Comment

    Rman Duplicate is Failing With Segmentation Fault

    No comments

    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.

    No comments :

    Post a Comment

    How to Migrate Linux Cups Printer from one server to other server

    No comments

    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

    No comments :

    Post a Comment

    How to migrate OS User Accounts from one Linux Server to Other/New Linux Server

    No comments

    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 the user accounts from old server for all users having user id above 500.


    # mkdir -p /stage/common (This can be any directory)
    # export UGIDLIMIT=500
    # awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534)' /etc/passwd > /stage/common/passws.olds

    2. Backup the groups in /etc/group file:

    # awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534)' /etc/group > /stage/common/group.olds

    3. Backup the shadow file:

    # awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534) {print $1}' /etc/passwd | tee - |egrep -f - /etc/shadow > /stage/common/shadow.olds

    4. Backup the users home directory and users mails:

    # tar -zcvpf /stage/common/home.tar.gz /home

    5. Backup the user's mails:

    # tar -zcvpf /stage/common/mail.tar.gz /var/spool/mail

    In case required you can copy the cronjobs for users as well from /var/spool/cron

    6. Copy the Backup files to new server using scp or any preferrable method.

    7. Restore the data into the new server.

    # cat passwd.old >> /etc/passwd
    # cat group.old >> /etc/group
    # cat shadow.old >> /etc/shadow
    # /bin/cp shadow.old /etc/gshadow

    8. Restore the user's data as well as the user's mails to the new server.

    # tar -zxvf /path/to/location/home.tar.gz
    # tar -zxvf /path/to/location/mail.tar.gz

    9. Reboot Server

    No comments :

    Post a Comment

    Query to get list of EBS users with responsibility and last login date (responsibility wise)

    No comments

    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

    No comments :

    Post a Comment

    Attach a Oracle Home in Oracle Inventory on Server

    No comments

    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 OH to your inventory. From your OH that is missing, do the following:

    cd $ORACLE_HOME/oui/bin
    ./runInstaller -invPtrLoc /d43/oracle/test/11.2.0/oraInst.loc -attachHome ORACLE_HOME=/d43/oracle/test/11.2.0 ORACLE_HOME_NAME="Ora11gHome_TEST"

    Starting Oracle Universal Installer...

    Checking swap space: must be greater than 500 MB.   Actual 8191 MB    Passed
    The inventory pointer is located at /u01/oracle/product/11.2.0/oraInst.loc
    'AttachHome' was successful.


    Now run Opatch again

    No comments :

    Post a Comment

    Deleting/Removing Control M characters in Linux

    No comments

    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 's/^M//g' filename

    4. vi Editor in the escape mode type below:
     :1,%s/^M//g
     
    5. awk command

    The 1 is used to print the lines.
    $ awk 'sub(/^M/,"");1' filename

    No comments :

    Post a Comment

    How to setup SAMBA in Linux

    No comments

    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

    Samba Server

    • 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/samba
    # chmod a+rwx /funoracleapps/samba
    # chown -R nobody:nobody /funoracleapps

    [root@centos1home ~]# ls -ld /funoracleapps/samba
    drwxrwxrwx. 2 nobody nobody 6 Jun  1 13:18 /funoracleapps/samba
    

    • We need to change the SELinux security context for the samba shared
    directory as follows: (Only if you have SELinux enabled)
    # chcon -t samba_share_t /funoracleapps/samba

    • If you want to disable SELinux, follow these instructions(Optional)
    # sestatus (To check the SELinux status)

    # vi /etc/selinux/config
    Change
    SELINUX=enforcing
    To
    SELINUX=disabled

    # reboot

    • Modify /etc/samba/smb.conf file to add new shared filesystem (Make sure to
    create a copy of smb.conf file)
    Delete everything from smb.conf file and add the following parameters
    [global]
    workgroup = WORKGROUP
    netbios name = centos
    security = user
    map to guest = bad user
    dns proxy = no
    [Anonymous]
    path = /funoracleapps/samba
    browsable = yes
    writable = yes
    guest ok = yes
    guest only = yes
    read only = no

    • Verify the Samba Configuration setting
    # testparm

    [root@centos1home ~]# testparm
    Load smb config files from /etc/samba/smb.conf
    Loaded services file OK.
    Server role: ROLE_STANDALONE
    
    Press enter to see a dump of your service definitions
    
    # Global parameters
    [global]
    	dns proxy = No
    	map to guest = Bad User
    	netbios name = CENTOS
    	security = USER
    	idmap config * : backend = tdb
    
    
    [Anonymous]
    	guest ok = Yes
    	guest only = Yes
    	path = /funoracleapps/samba
    	read only = No
    

    • Once the packages are installed, enable and start Samba services
    # systemctl enable smb
    # systemctl enable nmb
    # systemctl start smb
    # systemctl start nmb




    Server is configured successfully

    Mount on Windows client

    • Go to start
    • Go to search bar
    • Type \\192.168.56.101 (This is my server IP, you can check in CentOS IP by running the command ifconfig)

    Mount on Linux client

    Become root
    # yum -y install cifs-utils samba-client

    Samba Client


    Create a mount point directory

    # mkdir -p /him/funsambashare/

    Mount the samba share
    # mount -t cifs //192.168.56.101/Anonymous /him/funsambashare/
    # Enter root  password

    Samba Filesystem

    No comments :

    Post a Comment