How to Change the Existing Undo Tablespace to a New Undo Tablespace

How to Change the Existing Undo Tablespace to a New Undo Tablespace

Before doing any action, please perform a Backup of the database, just in case something doesn't works we will have a point to go back.

1. Determine the size of the datafile(s) for your current undo tablespace "UNDOTBS1":

SQL> select bytes, name from v$datafile where name like '%UNDO%';
Or)
SQL> select FILE_NAME,BYTES from dba_data_files where FILE_NAME like '%undo%';
BYTES                   NAME
--------------          -------------------------------
314572800             /u02/orada/PROD/UNDOTBS01.DBF'

2. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> create undo tablespace UNDOTBS2 datafile '/u02/orada/PROD/UNDOTBS02.DBF' size 500M;Tablespace created.

3. Edit your init.ora file and change the parameter "undo_tablespace=" so it points to the newly created tablespace. You may need to create a pfile first:

SQL> create pfile='/d02/oracle/proddb/11.2.0.3/pfileprod.ora' from spfile;
File created.

Change undo_tablespace=UNDOTBS2
Change undo_management=MANUAL

Setting undo_management now ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again in Step 7.

4. Arrange a time when the database can be shutdown cleanly and perform a shutdown immediate.

5. Startup the database (specify the pfile if you created one in step 3.)

SQL> startup pfile='/d02/oracle/proddb/11.2.0.3/pfileprod.ora'

6. Confirm the new tablespace is in use:

SQL> show parameter undo_tablespace;

NAME                 TYPE              VALUE
-------------         --------------  -----------------
undo_tablespace   string               UNDOTBS2

7. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline.

SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER    SEGMENT_NAME   TABLESPACE_NAME     STATUS
------         ----------------------  ------------------------  -----------
PUBLIC     _SYSSMU3$          UNDOTBS1              OFFLINE
PUBLIC     _SYSSMU2$          UNDOTBS1              OFFLINE
PUBLIC     _SYSSMU19$        UNDOTBS2              OFFLINE

....etc.

If the old segments are online, then they must be taken offline:

SQL>alter rollback segment "_SYSSMU3$" offline;
SQL>alter rollback segment "_SYSSMU2$" offline;

This should be executed for all online rollback segments in the old tablespace.

8. Provided all the segments in the old undo tablespace are offline, you can now drop the old undo tablespace:

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

9. Recreate your spfile with the new undo_tablespace value and change undo_management to AUTO:

undo_management='AUTO'
undo_tablespace='UNDOTBS2'

SQL> create spfile from pfile;

File created.

10. Shutdown the database (shutdown immediate) and restart it with the spfile.

+++++++++++++++++++++

ID 431652.1

+++++++++++++++++++++

SQL> show parameter undo;

NAME                       TYPE        VALUE

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

undo_management        string       AUTO

undo_retention             integer      18000   // 900*20

undo_tablespace           string       APPS_UNDOTS1

SQL> SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024, COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name = 'APPS_UNDOTS1' GROUP BY STATUS;

STATUS    SUM(BYTES)/1024/1024   COUNT(*)

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

ACTIVE                  126442      23652

EXPIRED              1797.5625       1723

UNEXPIRED             6403.125       9231

To Know the how much space is occupied :

SQL> SELECT creation_time, metric_value, message_type, reason, suggested_action FROM dba_outstanding_alerts WHERE object_name='APPS_UNDOTS3';

To know the all data files  increastd by size:

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name

from dba_data_files where autoextensible = 'YES';

+++++++++++++++++++++++++++++++

Oracle Database Privileges and Roles Concepts :

Oracle Database Privileges and Roles Concepts :

A privilege is the right to execute a particular type of SQL statement. It can allow a user to access database objects or execute stored programs that are owned by another user or to perform system level actions.

There are two types of privileges:

system privileges

schema object privileges

Step 1: system privileges

The system privileges are not related to a certain object. They control the ability of an user to perform system level actions such as connecting to the database(creating a session), creating a table, altering a user, etc. or to run a certain type of SQL statement on any schema(select any table, create any procedure).

All the system privileges are listed in the SYSTEM_PRIVILEGE_MAP table:

SQL> select name from SYSTEM_PRIVILEGE_MAP;

Object Privileges

The object privileges control the access to a certain objects. For different object types there are different privileges( for a procedure we do have an execute object privilege but do not have a select privilege).

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';

To Find User Default TableSapce :

SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username like '%HR%';

To Find a list of all privilege to a particular User :

SQL> SELECT grantee, privilege FROM dba_sys_privs WHERE grantee = '&User' ORDER BY privilege;

SQL> select grantee, privilege, admin_option from dba_sys_privs where grantee='&USer';

SQL> SELECT grantee , COUNT(privilege)  FROM dba_sys_privs GROUP BY grantee;

All the object privileges granted to a certain user can be viewed in DBA_TAB_PIVS:

SQL> select owner, table_name, privilege from dba_tab_privs where grantee='SYSTEM';

++++++++++++++++++++++++++++++++++

CREATE USER XXX  IDENTIFIED BY PWDXXX   DEFAULT TABLESPACE XXX_DATA

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;

                    

  Tablespace Quotas for XXX

  ALTER USER XXX  QUOTA UNLIMITED ON XXX_DATA;

  ALTER USER XXX  QUOTA UNLIMITED ON XXX_DATA;

 ALTER USER XXX  QUOTA UNLIMITED ON TEMP;

  To Grant a Role, System Privileges for XXX user :

  GRANT CONNECT TO XXX;

  ALTER USER XXX DEFAULT ROLE ALL;

  GRANT CREATE SYNONYM TO XXX;  //TABLE,VIEW, SESSION,SEQUENCE

  GRANT ALTER SESSION TO XXX;

+++++++++++++++++++++++++++++++++++++

180028.1,1016552.102,1347470.1

+++++++++++++++++++++++++++++++++++++

Automatic Storage Management ASM Instance :

Managing an Automatic Storage Management Instance: 
Startup and shutdown ASM Instance: 
Starting Up ASM Instance: 

An ASM instance is managed in much the same way as a database instance, with a few exceptions. The major difference is that an ASM instance is never opened like a regular Oracle instance is opened, only mounted; therefore, we can issue the STARTUP NOMOUNT; command to start just the ASM instance's memory processes, and then finish mounting the database with the ALTER DATABASE MOUNT; command. We can also open the ASM instance immediately by issuing the STARTUP MOUNT; command 

$> export ORACLE_SID=+ASM 
$> sqlplus "sys as sysdba" 

SQL> startup mount; 
ASM instance started 
Total System Global Area 100663296 bytes 
Fixed Size 787648 bytes 
Variable Size 99875648 bytes 
Database Buffers 0 bytes 
Redo Buffers 0 bytes 
ASM diskgroups mounted. 

Shutdown ASM Instance: 
 
To shut down this ASM instance, Set the ORACLE_SID environment variable and then simply issue the SHUTDOWN IMMEDIATE; command: 
$> export ORACLE_SID=+ASM 
$> sqlplus "sys as sysdba" 
SQL> shutdown immediate; 
ASM diskgroups dismounted 
ASM instance shutdown 

Implications of Shutting Down an Active ASM Instance: When an ASM instance is shut down, it is important to be aware of the implications for any regular Oracle database instance that is using ASM files stored on that ASM instance. The ASM files will not be accessible to those regular Oracle databases until the ASM instance is restarted. 

Create Disk Group: 
NORMAL REDUNDANCY: (Two – Way Mirroring) 
create diskgroup +DATA normal redundancy 
2 failgroup f1 disk '/dev/raw/raw5' name disk5 
3 failgroup f2 disk '/dev/raw/raw6' name disk6; 

HIGH REDUNDANCY: (Three – Way Mirroring) 
create diskgroup +DATA normal redundancy 
2 failgroup f1 disk '/dev/raw/raw5' name disk5 
3 failgroup f2 disk '/dev/raw/raw6' name disk6; 
4 failgroup f2 disk '/dev/raw/raw6' name disk7; 

EXTERNAL REDUNDANCY: (If we want no mirroring by ASM) 
CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2'; 

Adding Disks into Disk Group: 
 
alter diskgroup +DATA add disk '/dev/raw/raw7' name disk7; 

Dropping Disk 
 
alter diskgroup +DATA drop disk disk7; 
Note: 
When we drop the last disk from the FAILGROUPS, the FAILGROUP automatically drops. 

Resize Disk Group: 
 
alter diskgroup +DATA resize disk raw5 size 800M; 
Mounting and Dismounting Disk Groups 
Disk groups that are specified in the ASM_DISKGROUPS initialization parameter are mounted automatically at ASM instance startup. We can do this manually using below. 

Mounting Disk Group: 

ALTER DISKGROUP +DATA MOUNT; 

Dismounting Disk Group: 

ALTER DISKGROUP +DATA DISMOUNT; 


Create Tablespace with ASM: 
Create tablespace TBS_NAME datafile ‘+DATA’ Size 1024m; 

Viewing ASM Instance Information Via SQL Queries: 
 
There are several dynamic and data dictionary views available to view an ASM configuration from within the ASM instance itself: 

V$ASM_ALIAS - Shows every alias for every disk group mounted by the ASM instance 
V$ASM_CLIENT - Shows which database instance(s) are using any ASM disk groups that are being mounted by this ASM instance 
V$ASM_DISK - Lists each disk discovered by the ASM instance, including disks that are not part of any ASM disk group 
V$ASM_DISKGROUP - Describes information about ASM disk groups mounted by the ASM instance 
V$ASM_FILE - Lists each ASM file in every ASM disk group mounted by the ASM instance
V$ASM_OPERATION - Like its counterpart, V$SESSION_LONGOPS, it shows each long-running ASM operation in the ASM instance 
V$ASM_TEMPLATE - Lists each template present in every ASM disk group mounted by the ASM instance 

We can also able to query the following dynamic views against database instance to view the related ASM storage components of that instance: 

V$ASM_DISKGROUP - Shows one row per each ASM disk group that's mounted by the local ASM instance 
V$ASM_DISK - Displays one row per each disk in each ASM disk group that are in use by the database instance 
V$ASM_CLIENT - Lists one row per each ASM instance for which the database instance has any open ASM files 


The benefits of ASM: 
• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system 
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance 
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage 
• Uses redundancy features available in intelligent storage arrays 
• The storage system can store all types of database files 
• Using disk group makes configuration easier, as files are placed into disk groups 
• ASM provides stripping and mirroring 
• ASM and non-ASM oracle files can coexist 

ASM Operations 
1. Instance name 
select instance_name from v$instance; 

2. Create disk group 

create diskgroup +DATA normal redundancy 
2 failgroup f1 disk '/dev/raw/raw5' name disk5 
3 failgroup f2 disk '/dev/raw/raw6' name disk6; 
4 failgroup f2 disk '/dev/raw/raw6' name disk7; 
CREATE DISKGROUP +DATA EXTERNAL REDUNDANCY DISK '/dev/raw/raw1','/dev/raw/raw2'; 
Note: force is used if disk has been in a previous diskgroup, external redundancy uses third party mirroring i.e SAN 

3. Add disks to a group alter diskgroup +DATA add disk 
 
'/dev/raw/raw7' name disk7; 
/dev/raw/raw8' name disk8; 

4. Remove disks from a group 
 
alter diskgroup diskgrpA drop disk disk6; 

5. Remove disk group 
 
drop diskgroup diskgrpA including contents 

6. Resizing disk group 
 
alter diskgroup diskgrpA resize disk 'disk3' size 500M; 

7. Undo remove disk group 

alter database diskgrpA undrop disks; 

8. Display diskgroup info 
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup; 
select group_number, disk_number, name, failgroup, create_date, path, total_mb from v$asm_disk; 
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation; 

9. Rebalance a diskgroup 
(after disk failure and disk has been replaced) 
alter diskgroup diskgrpA rebalance power 8; 
Note: to speed up rebalancing increase the level upto 11, remember that this will also decrease performance, you can also use the wait parameter this will hold the commandline until it is finished. 

10. Dismount or mount a diskgroup 
alter diskgroup diskgrpA dismount; 
alter diskgroup diskgrpA mount; 

11. Check a diskgroups integrity 
alter diskgroup diskgrpA check all; 

12. Diskgroup Directory 
alter diskgroup diskgrpA add directory '+diskgrpA/dir1' 
Note: this is required if you use aliases when creating databse files 
i.e '+diskgrpA/dir/control_file1' 

13. Adding and drop aliases 
 
alter diskgroup diskgrpA add alias '+diskgrpA/dir/second.dbf' for '+diskgrpB/datafile/table.763.1'; 
alter diskgroup diskgrpA drop alias '+diskgrpA/dir/second.dbf' 

14. Drop files from a diskgroup 
alter diskgroup diskgrpA drop file '+diskgrpA/payroll/payroll.dbf'; 

15. Using ASM Disks 
Examples of using ASM disks 
create tablespace test datafile ‘+diskgrpA’ size 100m; 
alter tablespace test add datafile ‘+diskgrpA’ size 100m; 
alter database add logfile group 4 ‘+dg_log1’,’+dg_log2’ size 100m; 
alter system set log_archive_dest_1=’location=+dg_arch1’; 
alter system set db_recovery_file_dest=’+dg_flash’; 

16. Display performance. 
select path, reads, writes, read_time, write_time, 
read_time/decode(reads,0,1,reads) "AVGRDTIME", 
write_time/decode(writes,0,1,writes) "AVGWRTIME" 
from v$asm_disk_stat; 

 

Migrate Non-ASM to ASM

Creating ASM Instance 

1. Check CSS must be running before any ASM Instance 
Check CSS Running or Not? 
cd $ORACLE_HOME/bin 
crsctl check css 
2. If its not running, you should configure CSS process by running$ORACLE_HOME/bin/localconfig script: 
login as a root 
$ORACLE_HOME/bin/localconfig add 
Sample Output: 

/etc/oracle does not exist. Creating it now. 
Successfully accumulated necessary OCR keys. 
Creating OCR keys for user 'root', privgrp 'root'.. 
Operation successful. 
Configuration for local CSS has been initialized 

Adding to inittab 
Startup will be queued to init within 90 seconds. 
Checking the status of new Oracle init process... 
Expecting the CRS daemons to be up within 600 seconds. 

CSS is active on these nodes. 
DEV1 
CSS is active on all nodes. 
Oracle CSS service is installed and running under init(1M) 

# Default asm_diskstring values for supported platforms: 
# Solaris (32/64 bit) /dev/rdsk/* 
# Windows NT/XP \\.\orcldisk* 
# Linux (32/64 bit) /dev/raw/* 
# HPUX /dev/rdsk/* 
# HPUX(Tru 64) /dev/rdisk/* 
# AIX /dev/rhdisk/* 

ASM_DISKSTRING=/dev/raw 
INSTANCE_TYPE='ASM' 
LARGE_POOL_SIZE=40M 
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' 
USER_DUMP_DEST=$ORACLE_HOME/admin/+ASM/udump 
BACKGROUND_DUMP_DEST=$ORACLE_HOME/admin/+ASM/bdump 
CORE_DUMP_DEST=$ORACLE_HOME/admin/+ASM/cdump 
3. Create the init+ASM.ora file in $ORACLE_HOME/dbs 

# Default asm_diskstring values for supported platforms: 
# Solaris (32/64 bit) /dev/rdsk/* 
# Windows NT/XP \\.\orcldisk* 
# Linux (32/64 bit) /dev/raw/* 
# HPUX /dev/rdsk/* 
# HPUX(Tru 64) /dev/rdisk/* 
# AIX /dev/rhdisk/* 

ASM_DISKSTRING=/dev/raw 
INSTANCE_TYPE='ASM' 
LARGE_POOL_SIZE=40M 
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' 
USER_DUMP_DEST=$ORACLE_HOME/admin/+ASM/udump 
BACKGROUND_DUMP_DEST=$ORACLE_HOME/admin/+ASM/bdump 
CORE_DUMP_DEST=$ORACLE_HOME/admin/+ASM/cdump 

4. Creating ADMIN directories 

set ASM instance ORACLE_HOME 

mkdir -p $ORACLE_HOME/admin/+ASM/bdump 
mkdir -p $ORACLE_HOME/admin/+ASM/cdump 
mkdir -p $ORACLE_HOME/admin/+ASM/hdump 
mkdir -p $ORACLE_HOME/admin/+ASM/pfile 
mkdir -p $ORACLE_HOME/admin/+ASM/udump 

mkdir $ORACLE_HOME/dbs 
5. Starting the ASM Instance 

Starting ASM Instance 

# su - oracle 
$ ORACLE_SID=+ASM; export ORACLE_SID 
$ sqlplus "/ as sysdba" 
SQL> startup 
6. Create SPFILE from PFILE 

create spfile='+ASM' from pfile; 

7. For Unix platform, put an entry in the oratab file for the ASM intance 
ORATAB 

+ASM::Y 

You may get the following error on first start: 

ORA-15110: no diskgroups mounted 
This error can be safely ignored, when creating a new diskgroup, the diskgroup name will be 
added automatically to the asm_diskgroups parameter and you will not get this error again. 
Create ASM Disk group 
 
SQL> shutdown 
ASM instance shutdown 
SQL> startup 
ASM instance started 
SQL> alter system set asm_diskstring = '/dev/raw/raw1', '/dev/raw/raw2', '/dev/raw/raw3', '/dev/raw/raw4'; 
System altered. 

SQL> alter system set asm_diskgroups = 'DATA'; 
System altered. 

To get started, create 2 disk groups - one for data and one for recovery files. Here is an example: 

CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....; 
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....; 

Here is an example how you can enable automatic file management with such a setup: 

ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE; 
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE; 
Steps To Migrate/Move a Database From Non-ASM to ASM 

1. Edit the pfile/spfile of DB to point the new control_file location on ASM 
if your disk group name is '+DATA' 
control_files='+DATA//control01.ctl' 

2. Startup the database in nomount state 
SQL> Startup nomount 

3. From RMAN session, copy the control file from old location to new location 
Restore the controlfile from old location to new location: 

RMAN> CONNECT TARGET 
RMAN> RESTORE CONTROLFILE FROM '/u01/TST/control01.ctl'; 
Here /u01/TST/control01.ctl is the old location of control file. 

4. From SQL session, mount the database 

SQL> ALTER DATABASE MOUNT; 

5. Using RMAN, copy the datafile from NON-ASM to ASM 

Copy the controlfile from Non-ASM to ASM: 
RMAN>BACKUP AS COPY DATABASE FORMAT '+DATA'; 

run { 
allocate channel dev1 type disk; 
allocate channel dev2 type disk; 
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE 
FORMAT '+DISK' TAG 'ORA_ASM_MIGRATION; 
} 

Using RMAN, rename the datafile , using the following command 
Rename the datafile using the following command: 

RMAN> SWITCH DATABASE TO COPY 
Switch tempfile and open database. 

6. Switch tempfile and open database: 

RMAN> run { 
set newname for tempfile 1 to '+DATA'; 
set newname for tempfile 2 to '+DATA'; 
... 
switch tempfile all; 
} 

RMAN> ALTER DATABASE OPEN; 
7. Do the following maintenance 

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#; 
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 1; 
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 2; 
SQL> ALTER DATABASE DROP LOGFILE MEMBER ''; 
SQL> ALTER SYSTEM SWITCH LOGFILE; 
SQL> ALTER DATABASE DROP LOGFILE MEMBER ''; 
SQL> ALTER SYSTEM SWITCH LOGFILE; 

Note: Need some investigations for further ......

++++++++++++++++++++++++++++++++

Autoconfig Failed with Error: FsCtxFileException: Start of Root Element Expected:

Autoconfig Failed with Error: FsCtxFileException: Start of Root Element Expected [ID 876806.1]

Cause:

The issue is caused by invalid data or corruption in the fnd_oam_context_files table.

Solutions:

To implement the solution, please execute the following steps:

1. Connect to SQL*PLUS as "APPLSYS" user.

(The "APPLSYS" password is always the same as the "APPS" user.)

2. Backup the FND_OAM_CONTEXT_FILES table, for example:

SQL> CREATE TABLE fnd_oam_context_files_bak

          AS SELECT * FROM fnd_oam_context_files;

3. Truncate the FND_OAM_CONTEXT_FILES table, for example:

TRUNCATE TABLE fnd_oam_context_files;

4. Re-run Autoconfig on all nodes to repopulate the data.

++++++++ID 876806.1]++++++++

How To Audit An Oracle Applications' User? in R12

How To Audit An Oracle Applications' User?

Sign-On:Audit Level allows you to select a level at which to audit users who sign on to Oracle Applications.

 Four audit levels increase in functionality: None, User, Responsibility, and Form.

 None is the default value, and means do not audit any users who sign on to Oracle Applications.

Auditing at the User level tracks:

Who signs on to the system

The times users log on and off

Auditing at the Responsibility level performs the User level audit functions and tracks:

- The responsibilities users choose

- How much time users spend using each responsibility

Auditing at the Form level performs the Responsibility level audit functions and tracks:

The forms users choose

How long users spend using each form

System Administrator visible, revisable at all levels.

One can monitor users in the System Administrator responsibility by navigating to:

Security -> User-> Monitor form.  This is the Monitor Users Window. Use this window to monitor what the application users are currently doing.

 As well as seeing which users are signed on, one can see:

Which responsibilities, forms (windows), and terminals they are using

How long they have been logged in;

What Oracle database processes they are using.

In addition there are 5 reports that one can run that will provide user audited information:

Signon Audit Concurrent Requests - Use this report to view information about who is requesting what concurrent requests and from which responsibilities and forms.

Signon Audit Forms - Use this report to view who is navigating to what form and when they do it.

Signon Audit Responsibilities - Use this report to view who is selecting what responsibility and when they do it.

Signon Audit Unsuccessful Logins - Use this report to view who unsuccessfully attempted to sign on to Oracle Applications as another user. An unsuccessful login occurs when a user enters a correct username but an incorrect password. You can generate Signon Audit Unsuccessful Logins Reports for any users, regardless of whom one is auditing.

Signon Audit Users - Use this report to view who signs on and for how long.

With System Administrator responsibility navigate to:

Profile => System

Query for profile: "Sign-On:Audit Level"

Set the site level value to "FORM" // if User Lever "USER"

     Sign-On:Audit Level = FORM   // USER

Save the record.

Logout and login again.

Retest the issue.

Note: The concurrent program "Purge Signon Audit Data" should be used regularly to purge old sign on data.

395849.1,1359749.1,104282.1

+++++++++++++++++++++++++

For user sessions since auditing was enabled, run the "Signon Audit Users" concurrent request.

 The following query should help in finding out previous login times:

1. Log into sqlplus as the apps user.

2. Perform the following select:

SQL> select to_char(creation_date,'DD-MON-YY HH:MI:SS'), disabled_flag

from icx_sessions

where user_id=(select user_id from fnd_user where user_name = 'OPERATIONS');

/

Notes:

1. The disabled_flag = N means the session is still active.

2. The sessions shown are from the time the icx_sessions table was last purged.

3. This query gives all the login times, not just logins that happened prior to enabling sign-on audit. Add a date filter to the where clause if you only want sessions prior to the date when sign-on audit was enabled.

1364259.1

++++++++++++++++++++=

Note:

First of all, auditing at the responsibility level is enabled as a system profile option called Sign-On:Audit Level. Sign-On:Audit Level can be done for the site, responsibility and user and can be done at the form, responsibility and the user level. In other words, it works like a matrix. If you decide to audit a responsibility, then you need to decide at what level: form, responsibility or user. If you audit the responsibility at the form level, then you will get the most details about who used the responsibility, what forms they accessed using this responsibility, when they accessed the responsibility and when they left the responsibility. If auditing the responsibility is done at the responsibility level, you will not get the forms that the responsibility accessed but you will get everything else. If you audit the responsibility at the user level, you will get the user that accessed the responsibility, when they started and when they stopped. Very limited data.

The second level of auditing is for the data itself. This auditing is called AuditTrail and is more involved in setup. You need to identify the data that you want to audit and then translate that to the specific table and column that you want tracked. Audit groups are then created from the identified columns and tables (note that you can audit different tables and different columns within those tables). It is not common place to audit all of the columns for all of the HR or Payroll tables given the amount of resource that will be required to create and store information in the shadow tables that are created specifically for auditing.

The reports for the Sign-On:Audit Level can be found using the System Administrator responsibility and they are:

Signon Audit Concurrent Requests

Signon Audit Forms

Signon Audit Responsibilities

Signon Audit Unsuccessful Logins

Signon Audit Users

=====================================

What Tables Are Involved In Using The System Profile 'Sign-On:Audit Level'? [ID 368260.1]

Sign-On: Audit Level can be set at 4 different levels.  The level you specify will determine which Change Tracking  information Integra Apps will capture.  

The four levels (from lowest to highest level of audit) and the information that they capture are:

1. None

Tracks no additional info.

2. User

Tracks:

    who signs on to Oracle Apps

    the time users log on and off

    the terminals in use

3. Responsibility

Tracks:

    User Info

    the responsibilities user choose

    how much time users spend using each responsibility

4. Form

Tracks:

    User Info

    Responsibility Info

    the forms users choose

    how long users spend using each form

Based on the level chosen, the information captured gets stored in the following tables:

    FND_LOGINS

    FND_LOGIN_RESPONSIBILITIES

    FND_LOGIN_RESP_FORMS

1. Least detailed level: User.

When the profile is set to User, the only table that gets updated is the table FND_LOGINS and only one record per user session.

2. Next level: Responsibility.

When the profile is set to Responsbility both FND_LOGINS and FND_LOGIN_RESPONSIBILITIES will be updated.

FND_LOGINS gets only one record per user session.

FND_LOGIN_RESPONSIBILITIES will be updated with one record for each

responsibility selected during the session.

3. Most detailed level: Form.

When the profile is set to Form all three tables are involved.

FND_LOGINS gets only one record per user session.

FND_LOGIN_RESPONSIBILITIES will be updated with one record for each

responsibility selected during the session.

FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session.

+++++++++++++++++++++++++++++++++