DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, December 30, 2015

Query to get password for EBS Application User.



SELECT usr.user_name,
get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, encrypted_foundation_password) FROM DUAL) AS apps_password
FROM fnd_user WHERE user_name = (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,1,INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1)
FROM DUAL)),usr.encrypted_user_password) PASSWORD FROM fnd_user usr
WHERE usr.user_name = '&USERNAME';

Monday, December 28, 2015

Query to find Errored concurrent Requests in Last 24 Hrs:

SELECT b.request_id, a.user_concurrent_program_name,
       b.phase_code AS completed, b.status_code AS error,
       u.user_name requestor,
       TO_CHAR (b.actual_start_date, 'MM/DD/YY HH24:MI:SS') starttime,
       ROUND ((b.actual_completion_date - b.actual_start_date) * (60 * 24),
              2
             ) runtime,
       b.completion_text
  FROM fnd_concurrent_programs_tl a, fnd_concurrent_requests b, fnd_user u
 WHERE a.concurrent_program_id = b.concurrent_program_id
   AND b.phase_code = 'C'
   AND b.status_code = 'E'
   AND b.actual_start_date > SYSDATE - 1
   AND b.requested_by = u.user_id
   AND a.LANGUAGE = 'US';

Query to find tablespace usage above 80%

set line 200
SELECT d.tablespace_name "TS Name", d.num_files "Num Files",
       d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",
       (d.BYTES - NVL (f.freebytes, 0)) "Used MB",
       ((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"
  FROM (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes
            FROM dba_free_space
        GROUP BY tablespace_name) f,
       (SELECT   tablespace_name, COUNT (DISTINCT (file_id)) num_files,
                 sum(greatest(maxbytes,bytes))/1024/1024 asize,
/* Below logic does not calculate size properly if maxbytes is less than bytes
                   SUM (DECODE (maxbytes, 0, BYTES, maxbytes))
                 / 1024
                 / 1024 asize,
*/
                 SUM (BYTES) / 1024 / 1024 BYTES
            FROM dba_data_files
        GROUP BY tablespace_name) d
 WHERE d.tablespace_name = f.tablespace_name(+)
   AND d.tablespace_name not like '%UNDO%'
   AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;

WARNING: oradism did not start up correctly. Return code: 16 errno 0 info1 1001 info2 65535

The problem was with the oradism executable itself.

The oradism executable cannot have 755 (rwx-r-x-r-x) permissions, and should be owned by root like other executables in the $ORACLE_HOME/bin directory.

The permissions for oradism must be 4750 with the SetUID bit for the owner, e.g. for 11.2:

Solution:

1. cd $ORACLE_HOME/bin
2. chown root oradism
3. chmod 4750 oradism
4. bounce the database

Purge bad sql plan from memory

Find the Address and hash_value.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS HASH_VALUE
---------------- ----------
000000085EF77CF0  808321006


SQL> exec DBMS_SHARED_POOL.PURGE ('000000085EF77CF0, 808321006', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

ORA-00600: internal error code, arguments: [1350], [1], [23], [], [], [], [], []

Issue :
ORA-600 while running gather stats for fixed objects.
declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
[1350], [1], [23], [], [], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_STATS”, line 24867
ORA-06512: at “SYS.DBMS_STATS”, line 25408
ORA-06512: at line 3
ORA-06512: at line 33
Solution :
Either unset the ORA_NLS10 environment variable or set it to "$ORACLE_HOME/nls/data" retry gather stats .
If the problem persists please bounce the DB and listener and try again.

Thursday, December 24, 2015

There are no valid navigations for this responsibility

USER MANAGEMENT ERROR:






SOLUTION:


1. Log into the applications as SYSADMIN User.

2. Choose User Management responsibility.

3. Navigate to Users web page.

4. Search and find the user you want to inherit the Security Administrator and Customer Administrator Roles.

5. Click on Update Icon.

6. Click on Assign Roles button.

7. Find and choose ‘Security Administrator’ Role.

8. Apply.

9. Repeat the Steps (6-8) for ‘Customer Administrator’ Role.

10. Log as the user who was assigned User Management Responsibility and facing the issue.

11. Retest the issue.



Weblogic Clustering Across the Servers - Weblogic

Start your browser and go to the Administration Console by specifying its address as: http://machine1:7001/console ,where machine1 is the ip address or host name of the machine that you used in step 2 above to create the Administration Server. This should display the Administration Console login form.


Enter the server administration name and password you specified in step 2 when creating the domain - username defaults to weblogic - and click Log In to login.

The first thing that we need to do now is to create the managed servers. Using the Domain Structure tree navigate to Environment and then Servers. On the Summary of Servers screen Configuration tab click on the New button to create a new Managed Server.

On the Create a New Server screen specify the Server Name, Server Listen Address and Server Listen Port. For the Server Listen Address specify the address of the first physical machine. You can click Next to verify your input and finally Finish to proceed with the creation of the Managed Server.

The Administration Console proceeds with the creation of the server. When done displays a success message in the Messages area and the server is shown in the Summary of Servers table in a SHUTDOWN state.

Repeat the steps above to create the Managed Server sunning on the other physical machine. Make sure that you specify the Server Listen Address of the other physical machine.

Now that the Managed Servers have been created we will proceed with the creation of the WebLogic Machines - one for each physical machine - and assign the Managed Servers to them.

Using the Domain Structure tree navigate to Environment and then Machines. On the Summary of Machines table click on the New button to create a new WebLogic Machine.

On the Create a New Machine page specify the name of the first machine and click OK to create the Machine.

A success message in the Messages should indicate the successful creation of the Machine and the Machine will be available in the Summary of Machines table.

Now, click on the newly created Machine and then on the Node Manager tab under the Configuration tab. The node manager will be used on each physical machine to start the Managed Servers. On this tab we need to properly configure the node manager parameters. Make sure that the Type is set to Plain and that the Listen Address corresponds to the ip address of the physical machine where this Machine's node manager is running. You can leave the port to the default 5556. Don't forget to click on the Save button to save your settings.

Verify that your settings were set successful by observing the Messages area.

Now you will need to assign the appropriate Managed Server to the Machine. You do this by clicking on the Servers tab under the Machine Configuration tab and then the Add button.

In the Add a Server to Machine page ensure that the appropriate Managed Server - the one that corresponds to this Machine - is selected in the Select a server choice list. Click Next or Finish to proceed with the server assignment.

The Managed Server should be assigned successfully and should be displayed in the Servers table for this Machine.

Now repeat the steps above to create a WebLogic Machine for the second physical machine. Make sure that you configure the Machine correctly and assign the appropriate Managed Server to it.

Now with the WebLogic Machines created and configured we can start the node manager on the machine. Remember that we are still working on the same machine where we run the Configuration Wizard in the first place. It can be started by running the startNodeManager.sh script in the wlserver_10.3/server/bin directory under the Middleware Home. Before starting it, go over the start-up parameters specified in the nodemanager.properties configuration file in the wlserver_10.3/common/nodemanager directory and verify that it is not configured for SSL by ensuring that the SecureListener parameter is set to false. Once started the terminal window should indicate that the node manager is  running and listening to the port configured.

To verify that the node manager is reachable by the WebLogic Machine, return to the Machines page in the Administrator Console and on the Summary of Machines table click on the first Machine - Machine1 in this case the one we are currently configuring. Click on the Monitoring tab and observe in the Node Manager Status tab that the node manager Status is Reachable.
 
 
Copy the domain file structure to the other physical machine and enroll it to the WebLogic Domain

WebLogic comes with a pack utility that allows you to pack a domain and move it from one place to another. We will instead use plain old Linux tar and gzip to pack our WebLogic domain directory file structure and move it from one physical machine - the one that we have done steps 2 and 3 so far - to the other. Before doing so, we will shutdown the domain and node manager if they are still running on the first machine. Once the packing is done, we will re-start them.

To shutdown the domain, simply press Ctrl-C within the console running the domain. Alternatively, you could shutdown the  domain by shutting down the Administration Server using the Administration Console. Once the  domain is shutdown, go to the Middleware Home directory and issue the following tar command to pack the domain directory file structure and contents, i.e. the user_projects directory: tar cvf user_projects.tar user_projects


When the tar command is done, zip the archive by typing: gzip user_projects.tar. The file that needs to be moved and unpacked on the other machine is called user_projects.tar.gz


The compressed archive of the user_projects domain file structure should be moved to the Middleware Home on the other physical machine - remember that the Middleware Home directories on both physical machines should be exactly the same - and uncompress by typing gunzip user_projects.tar.gz


Finally extract the archive by typing tar xvf user_projects.tar. This will create the user_projects directory structure on the second physical machine.


Now you need to re-start the domain - on the first (original) machine - and use the  WebLogic Scripting Tool - on the second machine - to enroll the domain structure - the one we just extracted - to the WebLogic Domain. So, after making sure that the domain is started and is in RUNNING state, issue the following commands on the second machine - the one we are now setting up - to enroll it to the WebLogic Domain:

Start the WebLogic Scripting Tool by changing to the wlserver_10.3/common/bin directory and typing wlst. On the wlst prompt issue the following command to connect to the WebLogic Domain running on the other machine: connect('weblogic','weblogic1','t3://192.168.1.106:7001')






Verify that the connection was successful and then issue the following command to enroll this machine to the WebLogic Domain


nmEnroll('/home/weblogic/Oracle/Middleware/user_projects/domains/clusteredDomain', '/home/weblogic/Oracle/Middleware/wlserver_10.3/common/nodemanager')

Verify that the machine was successfully enrolled into the Domain.




Ensure that both WebLogic Machines are operational


Now that the second machine was successfully enrolled into the WebLogic Domain, we can start the node manager on the second machine and verify that it is Reachable by the WebLogic Machine. We have already verified that the node manager on the first machine is Reachable back in step 3.


You can start the node manager by running the startNodeManager.sh script in the wlserver_10.3/server/bin directory. Again, ensure that the node manager is not started in SSL mode by setting the SecureListener parameter to false in the nodemanager.properties configuration file in wlserver_10.3/common/nodemanager. Note that nodemanager.properties might not exist until you first run startNodeManager.sh.


Now, with the node managers running on both physical machines return to the Administration Console and verify that the node managers are Reachable for both Machines.


Create and configure the WebLogic Cluster


To create a WebLogic Cluster use the Administration Console and via the Domain Structure tree navigate to Environment and then Clusters. On the Summary of Clusters table click the New button to create a new WebLogic Cluster.


On the Create a New Cluster page enter the Name of the Cluster, select Unicast for the Messaging Mode and click OK.


Ensure that the WebLogic Cluster is created successfully by observing the messages on the top Messages area and the new Cluster appearing in the Clusters table. Now, click on the newly created Cluster to go to the Settings for Cluster page. On the Configuration General tab on the Cluster Address field enter the IP addresses of the physical machines separated by comma(s) supplying 7003 as the cluster listen port, e.g. 192.168.1.106:7003,192.168.1.107:7003 in this example. Click Save to save the changes.


While still on the Settings for Cluster page, go to the Configuration Servers tab and click Add on the Servers table to assign the Managed Servers to the Cluster.


On the Add a Server to Cluster page select the Managed Servers - one at a time - to add to the Cluster using the Select a server drop down and click Finish.


Repeat this step for both Managed Servers. In the end both Managed Servers should be shown on the Servers table for the Cluster.



Ensure that the Cluster is operational

Now that the Cluster is setup we can start the Managed Servers and do some monitoring on the Cluster to ensure its proper operation. You can start the Managed Servers either by running the startManagedWebLogic.sh script in the user_projects/domains/clusteredDomain/bin directory - for each physical machine - or from within the Administration Console. We will use the Administration Console to do so, which will at the same time validate our setup. So, go to the Summary of Servers page - via the Domain Structure tree by clicking on Environment and then Servers - and click on the Control tab. Click on the  checkboxes to select both Managed Servers - notice that they are both in SHUTDOWN state - and click the Start button to start them.


The Administration Console will delegate the start-up process to the node managers running on both machines. Each node manager will start the Managed Server assigned to the Machine controlled by the node manager. The process might take a minute or so to complete and once done both Managed Servers should be displayed on the Servers table with a RUNNING state. The Status of Last Action should be TASK COMPLETED. You will need to periodically refresh the page to see the final start-up status.


Now with both Managed Servers running, let's take a look at our Cluster status. Return to the Summary of Clusters page - via Environment and then Clusters on the navigation tree - and click on the Cluster in the Clusters table. Then click on the Monitoring tab and observe the Cluster Server status on the Summary tab.


Click on the Health and Failover tabs to see other monitoring information and status.


Done..!!
 

Reset the AdminServer Password in WebLogic 11g/12c

If you forget the AdminServer password for your WebLogic 11g domain, you can reset it from the command line using the following process.

    Set up the following environment variables. They are not necessary for the process itself, but will help you navigate. In this case my domain is called "ClassicDomain". Remember to change the value to match your domain.

    export MW_HOME=/u01/app/oracle/middleware
    export DOMAIN_HOME=$MW_HOME/user_projects/domains/ClassicDomain

    Shut down the WebLogic domain.

    $ $DOMAIN_HOME/bin/stopWebLogic.sh

    Rename the data folder.

    $ mv $DOMAIN_HOME/servers/AdminServer/data $DOMAIN_HOME/servers/AdminServer/data-old

    Set the environment variables.

    $ . $DOMAIN_HOME/bin/setDomainEnv.sh

    Reset the password using the following command. Remember to substitute the appropriate username and password.

    $ cd $DOMAIN_HOME/security
    $ java weblogic.security.utils.AdminAccount <username> <password> .

    Update the "$DOMAIN_HOME/servers/AdminServer/security/boot.properties" file with the new username and password. The file format is shown below.

    username=<username>
    password=<password>

    Start the WebLogic domain.

    $ $DOMAIN_HOME/bin/startWebLogic.sh

Workflow Email Notifications not working for a particular user in Oracle Apps R12.1.2

 To confirm whether the problem exists or not.

 I have checked the mail status of notifications sent to the user and they are in FAILED status.

STEP 1:

First, I have to checked the user preferences from FND_USER_PREFERENCES table with the following query

SELECT *
FROM FND_USER_PREFERENCES
WHERE user_name LIKE '&user_name'
AND module_name = 'WF' ;

I could see that preference value is defined for MAILTYPE preference.

STEP 2:

I have to checked whether email address is correct or not in FND_USER, PER_ALL_PEOPLE_F and FND_USERS tables.

Email address is all the above three tables are valid and correct.

STEP 3:
Next I have checked the user role in WF_LOCAL_ROLES table with the following query

SELECT * FROM WF_LOCAL_ROLES
WHERE name LIKE '&user_name';

I got two records with different ORIG_SYSTEM values, interestingly both roles are active but generally only one role with ORIG_SYSTEM = PER must be active.

Tuesday, December 22, 2015

13C Oracle Enterprise Manager Cloud Control Release 1 (13.1.0.0)


13c OEM released



12C SQL commands in RMAN




RMAN> connect target /

connected to target database: TESTDB (DBID=2602403303)
using target database control file instead of recovery catalog

RMAN> create table test (id number);
Statement processed

RMAN> select * from test;
no rows selected

RMAN> insert into test values (1);
Statement processed

RMAN> select * from test;

        ID
----------
         1

RMAN> begin
2>      for c1 in 1..20 loop
3>       insert into test values (c1);
4>      end loop;
5>    end;
6> /

Statement processed

RMAN> select count(1) from test;

  COUNT(1)
----------
        21

RMAN> rollback;
Statement processed

RMAN> select * from test;
no rows selected

RMAN> drop table test purge;

Statement processed

RMAN> select * from test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command
ORA-00942: table or view does not exist

RMAN>

12c introduces the Parallel Upgrade Utility





Oracle Database 12c introduces the Parallel Upgrade Utility (catctl.pl), which enables components that do not have to be upgraded in a specific order to be upgraded at the same time, taking full advantage of CPU capacity. The Parallel Upgrade Utility reduces the total amount of time it takes to perform an upgrade by loading the database dictionary in parallel using multiple SQL processes to upgrade the database.
-n specifies the number of processes to use for parallel operations (default = 4). The maximum is 8. Set this parameter to 0 to run catctl.pl in serial mode.

$ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l $ORACLE_HOME/diagnostics catupgrd.sql

12c Datagurd New Features




Far sync standby database


A Far Sync Standby Database is a cascading Standby Database which acts as a Redo Log Repository for a Terminal Database. It does not contain any Datafiles. Only Log Transport Services are active on a Far Sync Standby Database.The Advantage of a Far Sync Standby Database is that it can be a local ArchiveLog Repository for the Primary Database acting in Maximum Protection Mode where the Physical or Logical Standby Database can be on a far remote Site.
New Options for Cascaded Standby Databases

In 12c it is possible to cascade a Standby Database in Real-Time, ie. the first Standby Database can send Redo from the Standby RedoLogs to the cascaded Standby Database. The Data Guard Broker now supports Cascaded Standby Databases, too.

SYSDG Administration Privilege

New sys role for dataguard SYSDG and it is possible to grant the SYSDG-Privilege to a User in order to perform Data Guard-related Operations. The full SYSDBA-Privilege is not required any more to manage a Data Guard Environment, but can still be used.

Online Movement of Online Datafiles

We can now move Online Datafile without stopping Managed Recovery and manually copy and rename Files. This can even be used to move Datafiles from or to ASM, It is in standby what about production?? Yes it is possible to move the datafiles in production also.

Multitenant Database Support

Data Guard supports Physical Standby Database for a Multitenant Database

12c FLEX ASM




Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.


Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they don't require any network heartbeat.

12c datapump ENCRYPTION_PWD_PROMPT=Y





  • new parameter ENCRPTION_PWD_PROMPT as default set to N
  • if the parameter is set to Y then user is asked for encryption password during export
  • the password is not visible via commands like PS nor stored in scripts
  • old 11g ENCRYPTION_PASSWORD and ENCRYPTION_PWD_PROMPT=Y can’t be used in the same time and generate an error

ORA-01019: unable to allocate memory in the user side




The Problem

One of the in-house developed tools which connects to the database to allow a user to perform configuration updates was throwing an error:

ORA-01019: unable to allocate memory in the user side

Most of the docs that I could find online told me the same thing, namely:

Cause: The user side memory allocator returned error
Action: Increase the processes heap size or switch to the old set of calls

IO Load in the database


IO Load in the database

select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by 3 desc;

Find the object which are locked / ORA-04021: timeout occurred while waiting to lock object



Find the object which are locked

This query is for finding the object_name which is been locked
select oracle_username, os_user_name,SESSION_ID,locked_mode,object_name,object_type from v$locked_object a, dba_objects b where a.object_id=b.object_id;

This query is for finding the SID of the locked object_name
SELECT * FROM v$access WHERE OBJECT = '<object_name>';

This query is for finding the SID and Serial# of the locked SID
SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.sid=<Locked SID>;

This query is for killing the sid
ALTER SYSTEM KILL SESSION 's.sid, s.serial#';


SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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.request_id = &Request_ID;

ORA-04021: timeout occurred while waiting to lock object





VError report:
ORA-04021: timeout occurred while waiting to lock object 
04021. 00000 - "timeout occurred while waiting to lock object %s%s%s%s%s"
*Cause: While waiting to lock a library object, a timeout is occurred.

*Action: Retry the operation later.


V$ACCESS – You can use the V$ACCESS view to see which users have locks on which objects in your database
     SELECT SID, OWNER, OBJECT, TYPE
     FROM V$ACCESS
     WHERE OBJECT = 'object_name';
$SESSION_EVENT and V$SESSION_WAIT – use these views to see what Oracle wait events the session(s) are waiting on
     SELECT *
     FROM V$SESSION_EVENT
     WHERE SID = <sid>
     ORDER BY TIME_WAITED DESC;

     SELECT *
     FROM V$SESSION_WAIT
     WHERE SID = <sid>;

V$LOCKED_OBJECT – This view will also help you see who is locking the object

Wednesday, December 9, 2015

EBS Printer troubleshooting

Solaris:

1. Oracle EBS printer logfile location - /var/lp/logs/requests

2. Lpstat –p printer name – to check the printer status

3. echo "test printing"| lpr -P TPF1320PO – to give test printing from OS


4. s 0x0010 – this status indicates, it was successfully printed.

RMAN-06613: Connect identifier for DB_UNIQUE_NAME DBNAME_DGS not configured


Rman backup is failed with the error RMAN-06613: Connect identifier for DB_UNIQUE_NAME DBNAME_DGS not configured.

Scenario:
Trying to backup from standby database with the catalog database. No Issue seen if we don’t use catalog database.

Full Error:

doing automatic resync from primary
resyncing from database with DB_UNIQUE_NAME DBNAME_DGP
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of crosscheck command at 12/08/2015 04:12:05
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 12/08/2015 04:12:05
RMAN-06613: Connect identifier for DB_UNIQUE_NAME DBNAME_DGS not configured


Solution:

1.      1. It can be skipped if you already registered the database with the catalog
From Primary rman prompt (rman target / catalog=username/password@catalogdb)
RMAN>register database;
2.     
2. On catalog database 
select * from rc_database where name = ‘DBNAME’;
select db_unique_name, database_role, site_key from node where db_key=(from the previous output);

3.      
3. From Primary rman prompt (rman target / catalog=username/password@catalogdb)
configure db_unique_name 'DBNAME_DGS' connect identifier 'DBNAME_DGS';