OEM account dbsnmp locked.

 Use Case:  

We have changed passwords as part of regular DBA activity.  OEM account dbsnmp got locked after change.

 

Fix:

1. Change passwords for users sys and system
2. Run orapwd
3. In OEM, update config for sys: Setup>Security>Named Credentials
4. Change password for user dbsnmp
5. Quickly update OEM config for dbsnmp: Setup>Security>Monitoring Credentials

 

If the dbsnmp account still keeps locking, bounce the db and try steps 4 and 5 again.

 Good Luck.

 

 

Creating Additional Users on a Linux Instance (OCI)

In general, all OCI Linux servers created with OPC user by default, which has super privileges and can be used by admin users only.  If you would like provision access to developers on Linux server, we need to create new users and will see the process below how to create and access them.

High level plan to create and new user:

1. Generate SSH key pairs for the users offline.
2. Add the new users.
3. Append a public key to the ~/.ssh/authorized_keys file for each new user.

Detailed steps:

The new users then can SSH to the instance using the appropriate private keys.

To create an additional SSH-enabled user:

1. Generate an SSH key pair for the new user.

2. Copy the public key value to a text file for use later in this procedure.

3. Log in to the instance.

4. Become the root user:
    

   sudo su

5. Create the new user:

    useradd <new_user>

6. Create a .ssh directory in the new user’s home directory:

    mkdir /home/<new_user>/.ssh

7. Copy the SSH public key that you saved to a text file into the /home/new_user/.ssh/authorized_keys file:

    echo <public_key> > /home/<new_user>/.ssh/authorized_keys

8. Change the owner and group of the /home/username/.ssh directory to the new user:

    chown -R <new_user>:<group> /home/<new_user>/.ssh

9. To enable sudo privileges for the new user, run the visudo command and edit the /etc/sudoers file as follows:

a. In /etc/sudoers, look for:

    %<username> ALL=(ALL) NOPASSWD: ALL

b. Add the following line immediately after the preceding line:

    %<group> ALL=(ALL) NOPASSWD: ALL

The new user can now sign in to the instance.

I was able to create new user and followed all above steps as is and tried to login with new user, and I got below error message.

"Server refused our key"

"No supported authentication methods available (server sent: publickey.gssapi-keyex.gassapi-with-mic)".

After couple of hours research, found 2 issues.

Issue-1:

/home/<new user>/.ssh folder doesn't have 700 permissions and it has 755, changed .ssh folder permissions to 700.

Issue-2:

/home/new_user/.ssh/authorized_keys file suppose to have 600 permissions, but it was 755, changed back to 600.

Now you were able to login the linux server with new user.

Source: https://docs.cloud.oracle.com/en-us/iaas/Content/Compute/Tasks/addingusers.htm

Permanently change the hostname of an Oracle Cloud Infrastructure (OCI) compute instance

For Oracle Linux 6:

1) Edit /etc/sysconfig/network
change the parameter value for "hostname"
example:
$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=instance-ol6 <--------- change here

2) In the file /etc/oci-hostname.conf change the value of PRESERVE_HOSTINFO to 2.
$cat /etc/oci-hostname.conf
PRESERVE_HOSTINFO=2

This change will be persistent across reboots.

For Oracle Linux 7:

1) Update the /etc/hostname file with below command.
    hostnamectl set-hostname <new name>

2) Edit the oci configuration file for hostnames as given below to update the needed value to 2 and save changes.
    $ vi /etc/oci-hostname.conf
    PRESERVE_HOSTINFO=2
  

3) Edit the FQDN from OCI console GUI, go to compute instances
--> select the instance
--> scroll down
--> at the left bottom corner select attached VNIC under resources
--> edit appropriate VNIC
--> change hostname to update FQDN
--> update VNIC

4) Reboot the instance

5) Check the hostname with hostname command.

Find locks on object

Couple of different queries listed to find locks on objects / tables ...etc.

1. How to find any session is locked on a specific object:

Use case, one of the user trying to update a particular object, which is hanging to update.  Used below query to see who is accessing that object and cleared it.

Query:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='&Schema_Name' AND a.object='&Object_Name' and a.sid=s.sid;

Example:

select a.sid, s.serial#, s.status from v$access a, v$session s where a.owner='APPS' AND a.object='XXC_FS_RWP_BEE_PKG' and a.sid=s.sid;

2. Find Locks on Table Level:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

Query to find Concurrent Requests submitted by a particular user

Query to find Concurrent Requests submitted by a particular User:

SELECT
    request_id,
    request_date,
    user_concurrent_program_name,
    responsibility_name,   
    argument_text,   
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper('&USERNAME')
ORDER BY REQUEST_DATE DESC;

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)

Query to find runtime of a concurrent program

The following query finds total run-time in minutes for a concurrent program.

SELECT /*+ rule */
       rq.parent_request_id                   "Parent Req. ID",
       rq.request_id                          "Req. ID",
       tl.user_concurrent_program_name        "Program Name",
       rq.actual_start_date                   "Start Date",
       rq.actual_completion_date              "Completion Date",
       ROUND((rq.actual_completion_date -
           rq.actual_start_date) * 1440, 2)   "Runtime (in Minutes)"      
  FROM applsys.fnd_concurrent_programs_tl  tl,
       applsys.fnd_concurrent_requests     rq
 WHERE tl.application_id        = rq.program_application_id
   AND tl.concurrent_program_id = rq.concurrent_program_id
   AND tl.LANGUAGE              = USERENV('LANG')
   AND rq.actual_start_date IS NOT NULL
   AND rq.actual_completion_date IS NOT NULL
   AND tl.user_concurrent_program_name = 'PRC: Generate Draft Revenue for a Range of Projects'  -- <change it>
   -- AND TRUNC(rq.actual_start_date) = '&start_date'  -- uncomment this for a specific date
 ORDER BY rq.request_id DESC;

Kill all inactive sessions in a Database.

If you want to kill all inactive sessions in the database, it is hard to find and kill one by one.  Use below statement to create kill statements at a time and run them to kill all inactive session.

select 'alter system kill session '||sid||','||serial#|| ';' from v$session where status='INACTIVE';

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Issue Details:

When I'm trying to update a row in a table got the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Solution:

Use below query to find who is accessing the table.

SELECT a.object, a.type, a.sid,s.serial#, s.username,s.program, s.logon_time, s.osuser
FROM v$access a, v$session s
WHERE a.sid = s.sid
AND a.owner = '&OWNER'
AND a.object = '&Object_Name';

Check the session details and contact user to close his session or wait for the job complete.  If user requested to cancel his session, kill the session using below command.

SQL> alter system kill session '&SID,&Serial';

Then we tried our update statement, it works fine.

ORA-28007: the password cannot be reused

Issue:

SQL> alter user user1 identified by password1;
alter user user1 identified by password1
*
ERROR at line 1:
ORA-28007: the password cannot be reused


Solution:

1. Find the profile name:

SQL> SQL> select username,profile,account_status from dba_users where username='USER1';

USERNAME   PROFILE         ACCOUNT_STATUS
---------- --------------- ---------------
USER1     ADMINISTRATOR   OPEN

2. Find the profile values:

SQL> select * from dba_profiles where profile='ADMINISTRATOR';

PROFILE              RESOURCE_NAME                    RESOURCE LIMIT                COM
-------------------- -------------------------------- -------- -------------------- ---
ADMINISTRATOR        COMPOSITE_LIMIT                  KERNEL   DEFAULT              NO
ADMINISTRATOR        SESSIONS_PER_USER                KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_SESSION                  KERNEL   DEFAULT              NO
ADMINISTRATOR        CPU_PER_CALL                     KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT              NO
ADMINISTRATOR        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT              NO
ADMINISTRATOR        IDLE_TIME                        KERNEL   DEFAULT              NO
ADMINISTRATOR        CONNECT_TIME                     KERNEL   DEFAULT              NO
ADMINISTRATOR        PRIVATE_SGA                      KERNEL   DEFAULT              NO
ADMINISTRATOR        FAILED_LOGIN_ATTEMPTS            PASSWORD 5                    NO
ADMINISTRATOR        PASSWORD_LIFE_TIME               PASSWORD 90                   NO
ADMINISTRATOR        PASSWORD_REUSE_TIME              PASSWORD 180                  NO
ADMINISTRATOR        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED            NO
ADMINISTRATOR        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G  NO
ADMINISTRATOR        PASSWORD_LOCK_TIME               PASSWORD 7                    NO
ADMINISTRATOR        PASSWORD_GRACE_TIME              PASSWORD 14                   NO

16 rows selected.

SQL>

3. Alter the profile values:

SQL> alter profile ADMINISTRATOR limit password_verify_function NULL;

Profile altered.

SQL> alter profile ADMINISTRATOR limit PASSWORD_REUSE_TIME UNLIMITED;

Profile altered.

4. Change the password:

SQL> alter user user1 identified by password1;

User altered.

SQL>


5. After changing the password restore the default/old settings:

SQL> alter profile ADMINISTRATOR  limit password_verify_function VERIFY_FUNCTION_11G;

Profile altered.

SQL> alter profile ADMINISTRATOR  limit PASSWORD_REUSE_TIME 180;

Profile altered.

SQL> 

ORA-28003: password verification for the specified password failed ORA-20009: Password must be 8 characters in length

Issue:

SQL> alter user USER1 identified by password1;
alter user USER1 identified by password1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Password must be 8 characters in length

Solution:

SQL> select profile from dba_users where username='USER1';

PROFILE
------------------------------
APPLICATION_PASSWORD_EXP

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE='APPLICATION_PASSWORD_EXP' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION         PASSWORD PROD_PWD_LIMIT

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

SQL> alter user USER1 identified by password1;

User altered.

SQL> ALTER PROFILE APPLICATION_PASSWORD_EXP LIMIT PASSWORD_VERIFY_FUNCTION PROD_PWD_LIMIT;

Profile altered.

How to open a .JNLP file (Launch forms using Java Web Start) ?

Assumptions:
1. Java is already installed on your machine.
2. Your EBS form sessions are JWS enabled.

How to open a JNLP file:

1. Login to E-Business Suite.
2. Open a form.
3. It downloads frmservlet.jnlp file into your downloads folder.
4. Right-click on the file and choose Open with then click Choose another app.
5. If you see Java (TM) Web Start Launcher in this list (you may need to click More apps to expand the list), select it and check the box that says Always use this app to open .jnlp files then click the OK button.
6. If Java (TM) Web Start Launcher is not in the list, check the box that says Always use this app to open .jnlp files then click Look for another app on this PC.
    Navigate to the following location:
    C:\Program Files (x86)\Java\jreXXXX\bin
    XXXX represents a number that will vary based on the version of Java installed on the computer, for example: jre1.8.0_181.
    Select the file named javaws.exe and click Open.
7. Going forward, all .jnlp files will open using Java Web Start.

adoacorectl.sh start, failed with 'ERROR : Timed out( 100000 ): Interrupted Exception'

Issue Details:

$INST_TOP/admin/scripts/adoacorectl.sh start
Timeout specified in context file: 100 second(s)


ERROR : Timed out( 100000 ): Interrupted Exception
You are running adoacorectl.sh version 120.13


--> Upon checking logs, found the issue.

Logs:
$LOG_HOME/appl/admin/log/adoacorectl.txt
$LOG_HOME/ora/10.1.3/opmn/default_group~oacore~default_group~1.log


Issue:
Java HotSpot(TM) Server VM warning: CodeCache is full. Compiler has been disabled.
Java HotSpot(TM) Server VM warning: Try increasing the code cache size using -XX:ReservedCodeCacheSize=
Code Cache  [0xf3e00000, 0xf6e00000, 0xf6e00000)
total_blobs=6685 nmethods=6295 adapters=331 free_code_cache=1843Kb


Solution:

Java HotSpot(TM) 64-Bit Server VM Warning: CodeCache Is Full (Doc ID 2579830.1)

Reboot the server on which OVM Manager is installed.

This activity will not have any effect on the running VMs.

Oracle Database 19c: [INFO] [INS-32183] Use of clone.pl is deprecated in this release.

I was trying to clone oracle 19c ORACLE_HOME binaries from one server to another server.  Though it says deprecated, clone.pl worked end of the day.

Source the Environment on Target Host:

export ORACLE_SID=ORCL
export ORACLE_BASE=/ora/db/ORCL
export ORACLE_HOME=/ora/db/19.0.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:.

Run Clone Script:

$ORACLE_HOME/perl/bin/perl \
$ORACLE_HOME/clone/bin/clone.pl \
ORACLE_BASE=$ORACLE_BASE \
ORACLE_HOME=$ORACLE_HOME \
OSDBA_GROUP=oinstall \
OSOPER_GROUP=oinstall \
ORACLE_HOME_NAME=ORCL_HOME

Command Line output for clone script:

[oracle@mydbserver ~]$ $ORACLE_HOME/perl/bin/perl \
> $ORACLE_HOME/clone/bin/clone.pl \
> ORACLE_BASE=$ORACLE_BASE \
> ORACLE_HOME=$ORACLE_HOME \
> OSDBA_GROUP=oinstall \
> OSOPER_GROUP=oinstall \
> ORACLE_HOME_NAME=ORCL_HOME


[INFO] [INS-32183] Use of clone.pl is deprecated in this release. Clone operation is equivalent to performing a Software Only installation from the image.
You must use /ora/db/19.0.0/runInstaller script available to perform the Software Only install. For more details on image based installation, refer to help documentation.

Starting Oracle Universal Installer...

You can find the log of this install session at:
 /ora/db/oraInventory/logs/cloneActions2020-02-23_06-56-11AM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........
Copy files in progress.

Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..........
Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.
..........
Finish Setup successful.
The cloning of O19CAPEX_HOME was successful.
Please check '/ora/db/oraInventory/logs/cloneActions2020-02-23_06-56-11AM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /ora/db/19.0.0/root.sh



..................................................   100% Done.
[oracle@mydbserver ~]$

FRM-92095: Oracle JInitiator version is too low. Please install version 1.1.8.2 or higher.

Issue Details:

When ever you launch forms, it may end up with below error.

FRM-92095: Oracle JInitiator version is too low.  Please install version 1.1.8.2 or higher.

Solution:

Window 7 Users:

(Windows Key > Right click on My Computer > Properties > Advanced System Settings > Environment Variables > Add either a User or System attribute > Click on the New button > Enter "JAVA_TOOL_OPTIONS" under Parameter Name and -Djava.vendor="Sun Microsystems Inc." under Parameter Values)

IE issue when launching Java forms for EBS R12.1

Issue Details:

We installed EBS 12.1 and when we tried to open forms in IE11, it threw an error message "In order to access this application, you must install the J2SE Plugin version 1.5.0_13. To install this plugin, click here to download the oaj2se.exe executable. Once the download is complete, double-click the oaj2se.exe file to install the plugin. You will be prompted to restart your browser when the installation ".

Solution:

The steps we performed to resolve the issue are:

1. Go to tools>compatibility settings>and add the domain. My domain was oracle.com so i added it.

2. Tools>internet options>security>internet>custom level>disable xss filter

3. Under internet options>security>Local Intranet>sites>advance option>added the site http://myebs.myappserver.com

4. internet options>security>Local Intranet>custom level>disable xss filter

5. The final step was to apply the RPM-openmotif21-2.1.30-11.EL5.i386.rpm, Shut down ebs services including database and install the RPM. Once installed, start the services and test it.

OCI Notes & FAQs

Oracle Cloud Infrastructure Modules and Topics:

Compute:



Storage:



Networking:



Databases:



Identity & Security:



Management Tools:



Analytics, Big Data & AI/ML:



Migration:



Developer Tools:



Miscellaneous: