Different Levels of SUDO Access.

As a root user, visudo and insert the following based on your requirement.

###############################################

# APPLMGRSU users can su to applmgr

User_Alias  APPLMGRSU = user1

APPLMGRSU ALL = NOPASSWD: /bin/su - applmgr

###############################################

# ORACLESU users can su to oracle

User_Alias  ORACLESU = user1

ORACLESU ALL = NOPASSWD: /bin/su - oracle

###############################################

# allow the user to issue commands as root without a password

user1 ALL=(root) NOPASSWD: ALL

###############################################

#Append the following entry to run ALL command without a password for a user named user1:

user1 ALL=(ALL) NOPASSWD:ALL

SUDO access to Developers to bounce some of services.

One of the scenario that I come across, some of developers wanted to bounce apache / opmn services after their development activity to see their changes got effected or not.  It is hard for DBAs every time to do such things.  To overcome that, we have granted sudoers access to some of key developers, so that they can bounce on their own on non-prod environments.  

As root, on application server:

visudo

Insert this into file:

###############################################

# DEVORACLE users can run the following commands as applmgr

User_Alias  DEVORACLE = user1, user2, user3

DEVORACLE ALL = (applmgr) NOPASSWD: /u01/scripts/stopopmn, /u01/scripts/startopmn, /u01/scripts/startapache, /u01/scripts/stopapache

As applmgr, on application server:

cp $ADMIN_SCRIPTS_HOME/adopmnctl.sh /u01/scripts/.

cp $ADMIN_SCRIPTS_HOME/adapcctl.sh /u01/scripts/.

Make easy commands to developers:

$ cd /u01/scripts

$ echo "/u01/scripts/adopmnctl.sh stopall" > stopopmn

$ echo "/u01/scripts/adopmnctl.sh startall" > startopmn

$ echo "/u01/scripts/adapcctl.sh stop" > stopapache

$ echo "/u01/scripts/adapcctl.sh start" > stopapache

Here are the files you see in /u01/scripts after all done:

adapcctl.sh

adopmnctl.sh

startapache

stopapache

startopmn

stopopmn

Change permissions to all files under /u01/scripts:

$ chmod 700 *

These are the commands that only the developers listed by name in the sudoers file (above) can run:

sudo -u applmgr /u01/scripts/stopopmn

sudo -u applmgr /u01/scripts/startopmn

sudo -u applmgr /u01/scripts/stopapache

sudo -u applmgr /u01/scripts/startapache

How to find database growth on a Monthly Basis

SQL query to find database growth month wise and year.  

select to_char(CREATION_TIME,'RRRR') Year, to_char(CREATION_TIME,'MM') Month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

Sample Output:

YEAR MO         GB

---- -- ----------

2019 02         96

2019 03         65

2019 04        239

2019 05         32

2019 06         64

2019 07         95

2019 09        315

2019 10         31

2019 11        375

2020 04        313

10 rows selected.

ORA-01940: cannot drop a user that is currently connected

When I was trying to drop a schema, I got below error.

SQL> drop user obi_dw cascade;

drop user obi_dw cascade

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

Find out the session for connected user.

SQL> SELECT sid, serial#,status,username, logon_time,(last_call_et/60/60/24) "Days Inactive" FROM v$session WHERE username ='OBI_DW';

       SID    SERIAL# STATUS   USERNAME                       LOGON_TIM Days Inactive

---------- ---------- -------- ------------------------------ --------- -------------

        10         15 INACTIVE OBI_DW                         11-MAY-20    .000381944

Kill the session.

SQL> alter system kill session '10,15';

System altered.

Try to drop the user. 

SQL> drop user obi_dw cascade;

User dropped.

SQL>

ORA-01555 caused by SQL statement

Q: Why do we get ORA-01555 error?.

Ans: Suppose there are two users(User1 and User2). User1 is executing long update statement on oracle data without commit and at the same time User2 is also selecting same data which is getting updated by User1.

Both Queries are still running and no one gets output yet.

After some time, Query execution of User1 gets completed and he commits the data. Now what does happen with User2?. 

He gets ORA-01555 error. The reason behind this is ,"User2 doesn't get old image of the data", because User1 has changed the data.

Solution:

Increasing UNDO tablespace size is not the solution for this.

Run Undo advisory

Increase UNDO Retension Period

Enable Undo Retention Guarantee

Run this Select Query during non-business hours where heavy transactions are not happening.

Tune the Query

How to check product patch level or code level oracle apps?

To obtain the current patchset levels for each specific E-Business Suite functional product use the following steps:

1. Log into OAM.

(Responsibility "Oracle Applications Manager", menu "OAM Support Cart")
and Navigate:

2. Support Cart --> Applications Signature --> Collect --> Check "Product Information" box
--> Click on "View" (eyeglasses)

This will display the following patchset information:

Application Name
Current Patch Level
Product Version
Status (Installed, Shared Product, Inactive)

3. The patchset level information can be retrieved directly from sqlplus with the following diagnostic script:

$AD_TOP/sql/adutconf.sql

4. Use the script below to find patchset level.

SELECT app_short_name, MAX(patch_level)

FROM apps.ad_patch_driver_minipks

GROUP BY app_short_name ;

Followed the below Metalink Document:

E-Business Suite Applications Manager Steps To Get The Patchset Level of Each Specific Oracle Applications Functional Product in R12 (Doc ID 550654.1)