audi extr

Sno	What to execute
1	SELECT * FROM V_$PARAMETER WHERE NAME = 'audit_trail';
2	SELECT * FROM SYS.DBA_DB_LINKS;
3	SELECT b.grantee, b.granted_role FROM  sys.dba_role_privs b, sys.dba_roles c WHERE b.granted_role = c.role and c.role ='DBA' ORDER BY b.grantee, b.granted_role;
4	SELECT owner, trigger_name, trigger_type, triggering_event, status, when_clause, description FROM dba_triggers;
5	SELECT d.username, u.account_status FROM dba_users_with_defpwd d, dba_users u WHERE u.username = d.username; What to execute /etc/crontab /etc/cron.*/* /var/spool/cron/* /etc/passwd  /etc/group  SELECT * FROM SYS.DBA_USERS; 1. (g) Please provide the EBS user listing along with the responsibilities of each users. Query for Assigned responsibility for active EBS users. 　 　 SELECT fu.user_name user_id, per.FULL_NAME user_name, per.ATTRIBUTE2 location_of_EMP ,(SELECT DESCRIPTION FROM FND_FLEX_VALUES_TL WHERE FLEX_VALUE_ID IN (SELECT FLEX_VALUE_ID FROM FND_FLEX_VALUES WHERE FLEX_VALUE = per.ATTRIBUTE1 AND FLEX_VALUE_SET_ID = 1013708)) cost_center_desc, per.ATTRIBUTE1 cost_center, frt.responsibility_name, furgd.start_date, furgd.end_date FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, hr_employees per WHERE fu.user_id = furgd.user_id and per.EMPLOYEE_ID = fu.EMPLOYEE_ID AND furgd.responsibility_id = frt.responsibility_id and furgd.end_date is null and fu.END_DATE is null order by fu.USER_NAME; 　 ------------------------------------------------------------- SELECT fu.user_name user_id, per.FULL_NAME user_name, per.ATTRIBUTE2 location_of_EMP, frt.responsibility_name FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, hr_employees per WHERE fu.user_id = furgd.user_id and per.EMPLOYEE_ID = fu.EMPLOYEE_ID AND furgd.responsibility_id = frt.responsibility_id and furgd.end_date is null and fu.END_DATE is null order by fu.USER_NAME; 　 　 　 　 　 2. List of all DB users along with created and deactivated date ( As of today) 　 select USERNAME,CREATED,LOCK_DATE,ACCOUNT_STATUS from dba_users 　 3. EBS: List of all EBS users with last modified date select USER_NAME,LAST_UPDATE_DATE from fnd_user where END_DATE IS NULL 　 3A.QUERY FOR NEW RESPONSIBILITY/ROLE ADDED IN THIS FINANCIAL YEAR 　 　 SELECT RESPONSIBILITY_NAME,CREATION_DATE FROM FND_RESPONSIBILITY_TL WHERE CREATION_DATE > '01-APR-17' 　 　 　 4. QUERY FOR AUDIT TRAIL PARAMETER: SELECT * FROM V$PARAMETER WHERE NAME = 'audit_trail' 　 5. 　 QUERY FOR DB LINKS: SELECT * FROM SYS.DBA_DB_LINKS 　 6. QUERY FOR DBA ROLES GRANTED TO OTHER USERS 　 SELECT b.grantee, b.granted_role FROM sys.dba_role_privs b, sys.dba_roles c WHERE b.granted_role = c.role and c.role ='DBA' ORDER BY b.grantee, b.granted_role 　 7. QUERY FOR TRIGGER INFORMATION: SELECT owner, trigger_name, trigger_type, triggering_event, status, when_clause, description FROM dba_triggers 　 8. 　 QUERY FOR DB USER WITH DEFAULT PASSWORD SELECT d.username, u.account_status FROM dba_users_with_defpwd d, dba_users u WHERE u.username = d.username 　 XS$NULL 　 https://jhdba.wordpress.com/2013/02/18/database-user-xsnull/ 　 9. 　 DB PASSWORD# VALUES WITH OPEN USERS. 　 select NAME,PASSWORD from sys.user$ where name IN (select USERNAME from dba_users where ACCOUNT_STATUS='OPEN'); select NAME,PASSWORD from sys.user$ where name IN ('SYS', 'SYSTEM', 'APPS_VIEW', 'TJPSDBVIEW$', 'OLAPSYS', 'APPLSYSPUB', 'CTXSYS', 'APPLSYS', 'ODM', 'APPS'); 　 　 10. 　 EXTRACT AUDIT TRAIL DATA .LOGIN/LOGOFF. -------------------------------------------------------------------------- select USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time, to_char(logoff_time,'DD MON YYYY hh24:mi') logoff, action_name from dba_audit_session order by logon_time,username,timestamp,logoff_time; --------------------------------------------------------------------------- select USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time, to_char(logoff_time,'DD MON YYYY hh24:mi') logoff,action_name from dba_audit_session WHERE timestamp>='01-MAY-2017' and timestamp<='04-MAY-2017' order by logon_time,username,timestamp,logoff_time; 　 --------------------------------------------------------------------------- 　 11. QUERY FOR FND_AUDIT_COLUMNS: 　 select * from fnd_audit_columns 　 12. QUERY FOR VALID TABLE IN APPS SCHEMA 　 SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND STATUS = 'VALID' AND OWNER LIKE 'APPS%' 　 13. QUERY FOR FND_PROFILE_OPTION_VALUES: 　 SELECT * FROM FND_PROFILE_OPTION_VALUES 　 14.OPATCH DETAILS: $ORACLE_HOME/OPatch/opatch lsinventory root> opatch lsinventory root> opatch lsinventory -display $opatch lsinventory -details 　 　 Here is an example of the SQL command to see the status of patches on a database: select * from sys.registry$history; 　 Here we use DBMS_QOPATCH to list patches: select dbms_qopatch.GET_OPATCH_LIST from dual; https://blogs.oracle.com/upgrade/how-to-find-out-if-a-psu-has-been-applied-dbmsqopatch 　 Introduction to Oracle Recommended Patches (Doc ID 756388.1) How to Find E-Business Suite Recommended Patches (Doc ID 1400757.1) 　 　 　 　 No.14/22, Third Floor, First Cross Street, Newcolony, Chrompet, Chennai- 600 044. 　 　 Mobile : 9003853466 http://www.trainingintambaram.in/oracle-apps-dba-training-in-chennai.html# http://www.oracledistilled.com/oracle-database/restore-database-to-another-host-using-rman/ 　 1. Mr. Bharath (100058TJ) 2. Mr. Karthikeyan (140834TJ) 3. Mr. Mohan Selvaraj (120326TJ) 4. Mr. Yuvaraj (161061TJ) 5. Mr. Srinivasan (120307TJ) 6. Mr. Ramesh Kumar (130408TJ) 7. Mr. Kovendan (120316TJ) 8. Mr. Dillibabu (120257TJ) 9. Mr Suresh (120324TJ) 　 　 　 http://www.oracledistilled.com/oracle-database/restore-database-to-another-host-using-rman/ 　 which is latest clone of prod,apply the patch : 27347602 /home/appldev/patch8006/27347602 u27347602.drv 　 adadmin_27347602_21feb18.log /TESTAPPS/app/appldev/apps/apps_st/appl/admin/DEV/log/adpatch_27347602_21feb18.log 　 　 DedRspu5e =============================================================== USER DELETIOIN : 　 SELECT USER_NAME, START_DATE, END_DATE, DESCRIPTION, (SELECT FULL_NAME FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) EMPLOYEE_NAME, (SELECT ATTRIBUTE1 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) COST_CENTER, (SELECT ATTRIBUTE2 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) LOC, (SELECT ATTRIBUTE3 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) DEPT, (SELECT ATTRIBUTE4 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) JOINING_DATE, (SELECT ATTRIBUTE5 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) RESIGNED_DATE --(SELECT DESCRIPTION FROM FND_USER WHERE USER_ID = A.LAST_UPDATED_BY)LAST_UPDATED_BY FROM FND_USER A WHERE END_DATE >='01-Apr-2017' ORDER BY END_DATE; 　 USER ADDITION: 　 SELECT USER_NAME, START_DATE, END_DATE, DESCRIPTION, (SELECT FULL_NAME FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) EMPLOYEE_NAME, (SELECT ATTRIBUTE1 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) COST_CENTER, (SELECT ATTRIBUTE2 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) LOC, (SELECT ATTRIBUTE3 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) DEPT, (SELECT ATTRIBUTE4 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) JOINING_DATE, (SELECT ATTRIBUTE5 FROM HR_EMPLOYEES WHERE EMPLOYEE_NUM =A.USER_NAME) RESIGNED_DATE, (SELECT DESCRIPTION FROM FND_USER WHERE USER_ID = A.CREATED_BY)CREATED_BY FROM FND_USER A WHERE START_DATE >='01-Apr-2017' ORDER BY START_DATE 　 　 Please share the list of active users with assigned responsibilities for review and quick action. 1. list of active users with assigned responsibilities /************************************************************************** *PURPOSE: To list out Active Responsibilities assigned to a Active user * *PARAMETERS: User Name * **************************************************************************/ SELECT fu.user_name, frv.responsibility_name, frv.responsibility_key, TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE", TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE" FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_vl frv WHERE fu.user_id = furgd.user_id AND furgd.responsibility_id = frv.responsibility_id AND furgd.end_date IS NULL AND fu.user_name = '&user_name' AND furgd.start_date <= sysdate AND NVL(furgd.end_date, sysdate + 1) > sysdate AND fu.start_date <= sysdate AND NVL(fu.end_date, sysdate + 1) > sysdate AND frv.start_date <= sysdate AND NVL(frv.end_date, sysdate + 1) > sysdate; 　 　 2.DBS USERS WITH LOCKDATE: select USERNAME,CREATED,LOCK_DATE,ACCOUNT_STATUS from dba_users 　 3.ACTIVE EBS USERS WITH LAST MODIFIED DATE. select USER_NAME,LAST_UPDATE_DATE from fnd_user where END_DATE IS NULL 　 4.ACTIVE EBS USERS RESPONSIBILITY: 　 SELECT fu.user_name user_id, per.FULL_NAME user_name, per.ATTRIBUTE2 location_of_EMP ,(SELECT DESCRIPTION FROM FND_FLEX_VALUES_TL WHERE FLEX_VALUE_ID IN (SELECT FLEX_VALUE_ID FROM FND_FLEX_VALUES WHERE FLEX_VALUE = per.ATTRIBUTE1 AND FLEX_VALUE_SET_ID = 1013708)) cost_center_desc, per.ATTRIBUTE1 cost_center, frt.responsibility_name, furgd.start_date, furgd.end_date FROM fnd_user fu, fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, hr_employees per WHERE fu.user_id = furgd.user_id and per.EMPLOYEE_ID = fu.EMPLOYEE_ID AND furgd.responsibility_id = frt.responsibility_id and furgd.end_date is null and fu.END_DATE is null order by fu.USER_NAME; 　 　 　 5.LIST OF ROLES IN EBS DURING > 1-APR-17. SELECT RESPONSIBILITY_NAME,CREATION_DATE FROM FND_RESPONSIBILITY_TL WHERE CREATION_DATE > '01-APR-17' 　 6.PASSWORD DB VALUE; select NAME,PASSWORD from sys.user$ where name IN (select USERNAME from dba_users where ACCOUNT_STATUS='OPEN') 　 7.TABLE VALID IN APPS USERS: SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND STATUS = 'VALID' AND OWNER LIKE 'APPS%' 　 8. select * from fnd_audit_columns