Sujeet APPS DBA: August 2014

How to find Active or Inactive users in r12

How to find R12 Active User list?

Login With Apps User
SQL> set line 1000
SQL> SELECT user_id, user_name, full_name

FROM fnd_user fu, per_all_people_f papf

WHERE papf.person_id = fu.employee_id

AND SYSDATE BETWEEN fu.start_date AND NVL (fu.end_date, SYSDATE)

AND SYSDATE BETWEEN papf.effective_start_date

AND NVL (papf.effective_end_date, SYSDATE ) ORDER BY 2;

USER_ID USER_NAME                                                                                            FULL_NAME
----------------------------------------------------------------------------------------------------       1130 ACCOUNT USER                                                                                         Tiwari, Mr. Kamlesh
      1170 CEO                                                                                                  D, Mr. Vasudevan
      1110 IT_SUPERUSER                                                                                Nabi, Mr. Gulzar
      1191 PREM KUMAR                                                                                           Lamchhanea, Mr. Prem Prasad
      1150 PROJECT USER                                                                                         Prasad, Mr. Ranjay
      1190 SANTOSH KUMAR                                                                                    Jha, Mr. Santosh Kumar
      1131 SCM USER                                                                                             Chawla, Mr. Sachin
      1214 TS.KRISHNA                                                                                           Krishna, Mr. T S
      1192 VIVEK KUMAR                                                                                          Gupta, Mr. Vivek Kumar

9 rows selected.

---------------------------------------------------------------------------------------------
How to find inactive users in Oracle???

# enable session audit

sqlplus '/ as sysdba'
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> audit session;

# after a few months of normal database operation find the users who have not logged in.

SQL> select username, created from dba_users where account_status = 'OPEN' and created < sysdate -90 and not exists
(select distinct userid from aud$ where userid = username and LOGOFF$TIME > sysdate -90)
order by username;

USERNAME                       CREATED
------------------------------ ---------
ABM                            14-MAY-00
AHL                            30-MAY-02
AHM                            30-MAY-02
AK                             14-MAY-00
ALR                            14-MAY-00

ORA-12547: TNS:lost contact

I was schedule RMAN Backup through root user,
root> crontab -e
After that Facing below error.

Recovery Manager: Release 11.1.0.7.0 - Production on Mon Aug 25 02:50:02 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12547: TNS:lost contact

Solution:-

Use Database user to schedule Rman backup scripts.
ora> crontab -e

Edit below file:-
root> vi /etc/cron.allow

In this file enter your db_user name like below after that save it.
root
orauser

check cron.deny file.
root>cat /etc/cron.deny
if any entry in this file related to your user name remove it.

save it.
After that you can schedule Rman backup scripts throught Database user.

ora> crontab -e

its working fine.

ORA-19566: exceeded limit of 0 corrupt blocks for file

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t3 channel at 08/23/2014 03:20:55
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/sujeetdb/db/apps_st/data/system09.dbf

Solution:-

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
       352     165657          1                  0             ALL ZERO
       352     165656          1                  0              CORRUPT


RMAN> blockrecover datafile 352 block 165656;

Retry.......i hope issue resolved...............

Check block corruption:-

[orachintels@sujeet]$ dbv file=/u01/sujeetdb/db/apps_st/data/system09.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 192128
Total Pages Processed (Data) : 65567
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 30690
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1880
Total Pages Processed (Seg) : 0
Total Pages Failing   (Seg) : 0
Total Pages Empty            : 67518
Total Pages Marked Corrupt   : 26473
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3757655288 (1388.3757655288)

How to change archive destination folder on oralce 11g

old Archive destination /u01/ora/db/tech_st/11.1.0/dbs/arch
New Archive destination /u01/Arch_BKP

Note:- Check your Database running with SPfile or Pfile.

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
   FROM sys.v_$parameter WHERE name = 'spfile';

SQL> show parameter spfile;

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM v$parameter WHERE name = 'spfile';

If Database using SPfile than directly use below statement or restart Archive-log.

SQL> alter system set log_archive_dest='/u01/Arch_BKP' scope=spfile;

Steps:-1
Connect to SQL as sysdba

[ora@sujeet 11.1.0]$ sqlplus / as sysdba

Steps:-2
Down Database
SQL> shutdown immediate;

Steps:-3
Start with Mount
SQL> startup mount

Steps:-4
If your Database in Archive mode:-

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/ora/db/tech_st/11.1.0/dbs/arch
Oldest online log sequence     8
Next log sequence to archive   9
Current log sequence           9

Change noarchive mode

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/ora/db/tech_st/11.1.0/dbs/arch
Oldest online log sequence     10
Current log sequence           11

Steps:-5
You have 2 option to change Archive destination:-

1. edit the init.ora parameter file (Pfile)

OR
Set other Archive destination location.
my Location= /u01/Arch_BKP

SQL> alter system set log_archive_dest='/u01/Arch_BKP' scope=spfile;

Steps:-6
Change Archive Mode

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/Arch_BKP
Oldest online log sequence     8
Next log sequence to archive   9
Current log sequence           9

Steps:-7

Open Database:-
SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

APP-PER-52803: Your business group does not match your security profile.

Cause

The issue is caused by the following setup:

This is a setup issue - check the settings of the HR:Business Group and
HR:Security Profile profile options at the Responsibility level. The value for
the business group profile option must match the business group defined for
the security profile specified by the Security Profile profile option.

Solution

-- To implement the solution, please execute the following steps:-

Change the setup for
HR:Business Group and
HR:Security Profile profile options at the Responsibility level.
The value for the business group profile option must match the business group defined for
the security profile specified by the Security Profile profile option

How to compile apps schema in Oracle applications R12

How to compile apps schema (or invalid objects in database) in Oracle applications R12

1. Connect to Database Tier

[ora@sujeet ~]$ cd $ORACLE_HOME/rdbms/admin

[ora@sujeet admin]$ sqlplus / as sysdba

Find how many Invalid objects. (Here 182 row select).

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';

182 rows selected.

Run below scripts:-

SQL> @utlrp.sql

Again check Invalid objects. (Here 0 row select).

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';

no rows selected

2. From application tier (using adadmin)
a) Login as application tier user.
b) Set environment variable.
c) Run “adadmin”
d) option 3 “compile/reload Applications Database Entities menu
e) option 1 “Compile Apps Schema”

3. From SQL (Individual objects)
a) Identity Invalid Object

SQL> select object_name, owner, object_type from all_objects where status= 'INVALID';
b) SQL> alter [object] [object_name] compile;

How to get Client IP address in oracle apps.

Connect with APPS User.

Run below Statement.

SQL> set line 1000
SQL> select SID, PROCESS, MACHINE, CLIENT_IDENTIFIER from v$session;

SID   PROCESS      MACHINE            CLIENT_IDENTIFIER
322        19943    Sujeet.oracle.com    Sujeet jha
262    19943         Sujeet.oracle.com             Sujeet jha

Login with Application User.

[appl@sujeet]$ ps -ef|grep 19943 <User latest Process id>

502      19943 6616 0 14:35 ?        00:00:34 /u02/apps/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,192.168.0.1
502      23747 23723 0 16:38 pts/2    00:00:00 grep 19943

OUT PUT:-
Client Name:- Sujeet jha
Machine Name:- sujeet.oracle.com
IP:- 192.168.0.1

A vnc server is already running as 2 failed

I am using vnc on my Redhat 5.8 box. I used "service vncserver start" to start vncserver at startup. But when I try to connect to "myhost:2" using vncviewer on another machine, it failed.

When I type "ps -ef|grep vncserver", I couldn't see "vncserver" running

Error Detailed:-

[root@Sujeet ~]# service vncserver start

Starting VNC server: 2:root
Warning: sujeet.oracle.com:2 is taken because of /tmp/.X2-lock
Remove this file if there is no X server chintels.chintels.com:2
A VNC server is already running as :2
                                                           [FAILED]

Solution:-

remove below file.
/tmp/.X2-lock

[root@sujeet]# rm -rf /tmp/.X2-lock

[root@sujeet ~]# service vncserver start
Starting VNC server: 2:root
Warning: sujeet.oracle.com:2 is taken because of /tmp/.X11-unix/X2
Remove this file if there is no X server sujeet.oracle.com:2
A VNC server is already running as :2
                                                           [FAILED]

Solution:-

remove below file.
/tmp/.X11-unix/X2

[root@sujeet ~]# rm -rf /tmp/.X11-unix/X2

[root@sujeet ~]# service vncserver start
Starting VNC server: 2:root
New 'sujeet.oracle.com:2 (root)' desktop is sujeet.oracle.com:2

Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/sujeet.oracle.com:2.log

                                                           [ OK ]

Issue Resolve try to connect with VNC tool