Idle sessions

Run the following script from the SYS account in SQL*Plus:


REM****************************************************************************
REM IDLE_TIME MONITOR SCRIPT
REM****************************************************************************

THIS SCRIPT PROVIDES AN EASY WAY FOR THE DBA TO MONITOR SESSION IDLE TIMES. THE OUTPUT OF THE SCRIPT SHOWS THE
SID FOR EACH SESSION RUNNING AGAINST THE DATABASE, THE LAST TIME THIS SESSION WAS ACTIVE (INCLUDING DATE AND TIME),
THE CURRENT TIME AND THE AMOUNT OF TIME (IN SECONDS AS WELL AS MINUTES) ELAPSED SINCE THE SESSION BECAME INACTIVE.
THIS SCRIPT IS WRITTEN TO BE RUN FROM THE SYS ACCOUNT IN SQLPLUS.


column sid format 999
column last format a22 heading "Last non-idle time"
column curr format a22 heading "Current time"
column secs format 99999999.999 heading "idle-time |(seconds)"
column mins format 999999.99999 heading "idle-time |(minutes)"

select sid, to_char((sysdate - (hsecs - value)/(100*60*60*24)),
'dd-mon-yy hh:mi:ss') last, to_char(sysdate, 'dd-mon-yy hh:mi:ss') curr,
(hsecs - value)/(100) secs, (hsecs - value)/(100*60) mins
from v$timer, v$sesstat>
where statistic# = (select statistic# from v$statname
where name = 'process last non-idle time');

REM****************************************************************************


Monitoring Session IDLE_TIMES (Doc ID 16728.1)

SGA_TARGET vs SGA_MAX_SIZE

sga_max_size sets the maximum value for sga_target


If sga_max_size is less than the sum of db_cache_size + log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.


In simple terms sga_target is less than or equal to sga_max_size


SGA_TARGET provides the following:

• Single parameter for total SGA size

• Automatically sizes SGA components

• Memory is transferred to where most needed

• Uses workload information

• Uses internal advisory predictions

• STATISTICS_LEVEL must be set to TYPICAL



By using one parameter we don't need to use all other SGA parameters like.

• DB_CACHE_SIZE (DEFAULT buffer pool)

• SHARED_POOL_SIZE (Shared Pool)

• LARGE_POOL_SIZE (Large Pool)

• JAVA_POOL_SIZE (Java Pool)


Enable SGA_TARGET

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0 ---------------As value is zero is not enabled

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 600M


As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=400m;
System altered.

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -------
sga_target big integer 400M



Resize SGA_TARGET


• SGA_TARGET is dynamic
• Can be increased till SGA_MAX_SIZE
• Can be reduced till some component reaches minimum size
• Change in value of SGA_TARGET affects only automatically sized components

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_max_size big integer 600M

SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_target big integer 400M


SQL> alter system set sga_target=500m


WE can resize it to only 600m if we will try to increase it from 600m we will get error.


SQL> alter system set sga_target=605m;

alter system set sga_target=605m *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
For that we must first increase our SGA_MAX_SIZE parameter value.But we must restart out instance because its STATIC parameter.
SQL> alter system set sga_max_size=956 scope=spfile;
System altered.

SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size 1337492 bytes
Variable Size 624953196 bytes
Database Buffers 369098752 bytes
Redo Buffers 4800512 bytes
Database mounted.
Database opened.

SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- --------
sga_max_size big integer 956M

SQL> alter system set sga_target=900m;
System altered.

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.
SQL> alter system set sga_target=0;

System altered.

Few Info questions

Question. How verify the sysadmin password from command line?
Answer: This utility can be used to verify the GUEST/ORACLE password
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,'’)from dual;
If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’WELCOME123') from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN123')
——————————————————————————–
N
SQL> select fnd_web_sec.validate_login(‘SYSADMIN’,’SYSADMIN’) from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘SYSADMIN’,’SYSADMIN’)
——————————————————————————–
Y

How you will troubleshoot if concurrent request is taking long time?

First you will try to check how far the query has gone (if in v$session_longops for example, or reading some session statistics) and if there is contention with
another session (such as a lock for example) because it talks about a 'concurrent request'.
Method 1: Trace the session using dbms_system.set_sql_trace_in_session (or) if u r using 10g, using dbms_monitor and
look at the trace file to identify where the issue is. You can get the explain plan and tune the query.
Method 2:
1. Is this a new query (or) an existing query
2. Look for the wait event associated to this query.
3. What is the elapsed time for this query?
4. Are there are any db deadlocks?
5. Are there any long operations?
6. Are there any long transactions?
7. Based on the sql hash value, look at the sql_text from v$sql
8. Run an explain plan (or) use oradebug and trace the query if what bind variables are using and how the data distribution is.
9. Based on the above information, look for when statistics was collected
10. If it is an old query, check if the plan has changed and if so, why?
Once u understand where the problem is, then solutions can be defined ranging from creating sql profiles / stored outlines (or) adding hints to the query and thoroughly test your changes. Always remember to make one change and test for performance change. If you make a series of changes, it will be very difficult to isolate which has caused the performance improvement.

What are the basic steps for Printer Installation in EBS 11i/R12?

For most printing needs, the Pasta Utility offers quick setup and easy maintenance. For additional flexibility, Oracle E-Business Suite allows you to define your own printer drivers and print styles.
1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address)
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name you have set in the hosts file
6. Save
7. Bounce the Concurrent Manager

What are basic steps for Workflow Mailer configuration?

use Oracle Application Manager (OAM) to configure Workflow Notification Mailer.
For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/FNDC*.txt
Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
If you wish to configure Inbound Notification as well then ensure IMAP Server should be configured with a valid user (create Inbox, Processed & Discard folder for this User)
Schedule “Workflow Background Process” Concurrent Request:
1. Frequency : Every 5 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:No
2. Frequency : Every 60 Minutes – Parameter : Deferred:No, Timeout:Yes, Stuck:No
4. Frequency : Daily – Parameter : Deferred:No, Timeout:No, Stuck:Yes
5. Frequency : Every 10 Minutes – Parameter : Deferred:Yes, Timeout:No, Stuck:
6. Frequency : Every 6 Hours – Parameter : Deferred:Yes, Timeout:Yes, Stuck:Yes
The main component of the Oracle Workflow Notification Mailer is the executable WFMAIL. This is a server side program that queries the database for any pending notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.

In the cloning process, when do you utilize dbTechStack vs just dbTier?
perl adcfgclone.pl dbTechStack vs perl adcfgclone.pl dbtier
There are different components with RapidClone that are used when cloning an Oracle Applications instance. These are:
dbTechStack (RDBMS ORACLE_HOME)
database (database only, including control file creation)
dbconfig (database only, with no control file creation)
dbTier (both dbTechStack and database)
atTechStack (Tools and Web ORACLE_HOMEs)
appltop (APPL_TOP only)
appsTier (both atTechStack and appltop)
perl adcfgclone.pl dbtier
This will configure the ORACLE_HOME on the target database tier node + recreate the control files. This is used for cold backup.
perl adcfgclone.pl dbTechStack
This will configure the ORACLE_HOME on the target database tier node only. When running this command you will have to recreate the control files manually. This is used to clone the Database separately, for example using RMAN hot backup.
When you use “dbTier” option, the perl script will configure both the tech stack and data stack(ORACLE_HOME and Oracle Database) whereas “dbTechStack” option will only configures ORACLE_HOME and it WILL NOT create database and this is generally used while doing hot clone where db creation is a manual step.

User creation in 12C

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root.
SQL> show con_name
CON_NAME
——————————
CDB$ROOT

SQL> create user scott identified by scott;
create user scott identified by scott
*
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user scott identified by scott container=current;
create user scott identified by scott container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL> create user scott identified by scott container=all;
create user scott identified by scott container=all
*
ERROR at line 1:
ORA-65096: invalid common user or role name

//If you wish to create common user under CDB$ROOT than create user start with C## and c##, as follows:
Note:
Common user will be created under root container only.
Current container must be set to CDB$ROOT.
SQL> create user C##scott identified by scott;
User created.
SQL> create user c##scott identified by scott container=all;
User created.
OR
// Creating local user in PDB:
SQL> alter session set container=sales;
Session altered.
SQL> sho con_name
CON_NAME
——————————
SALES
SQL> create user test identified by test;
User created.
***********************************************************************