Friday, 29 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

Monday, 25 August 2014

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.


 



Saturday, 23 August 2014

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)



Friday, 22 August 2014

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.


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

Monday, 11 August 2014

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;

Friday, 1 August 2014

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