Concurrent requests scheduled by a User

Connect to database as apps user and run the below query:

=>It prompt for user name, Enter username in caps

set lines 200

col CONC_PROG for a40

col start_date for a12

col end_date for a12

col Next_run for a12

col last_run for a12

select r.request_id,

 p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,

-- r.argument_text arguments,

 r.requested_start_date next_run,

 r.last_update_date LAST_RUN,

 r.hold_flag on_hold,

 c.date1 start_date,

 c.date2 end_date

 from fnd_concurrent_requests r,

 fnd_conc_release_classes c,

 fnd_concurrent_programs_tl p,

 fnd_user s,

 (with date_schedules as (

 select release_class_id,

 rank() over(partition by release_class_id order by s) a, s

 from (select c.class_info, l,

 c.release_class_id,

 decode(substr(c.class_info, l, 1), '1', to_char(l)) s

 from (select level l from dual connect by level <= 31),

 fnd_conc_release_classes c

 where c.class_type = 'S'

 and instr(substr(c.class_info, 1, 31), '1') > 0)

 where s is not null)

 SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates

 FROM date_schedules

 START WITH a = 1

 CONNECT BY nocycle PRIOR a = a - 1

 group by release_class_id) dates

 where s.user_name = '&1'

 and r.phase_code = 'P'

 and c.application_id = r.release_class_app_id

 and c.release_class_id = r.release_class_id

 and nvl(c.date2, sysdate + 1) > sysdate

 and c.class_type is not null

 and p.concurrent_program_id = r.concurrent_program_id

 and p.language = 'US'

 and dates.release_class_id(+) = r.release_class_id

 and r.requested_by = s.user_id

 order by conc_prog, on_hold, next_run;

FND User Information

=>Connect as apps user to database and run below query to retrieve User information.
=>This query prompt for user name, Enter user name twice.(Must in Caps letters)

set lines 200
col USER_NAME for a15
col DESCRIPTION for a20
col EMAIL_ADDRESS for a25
col RESPONSIBILITY_NAME for a30
col END_DATE for a17

select  fu.user_name ,  fu.description ,
fu.EMAIL_ADDRESS ,
frt.RESPONSIBILITY_NAME , fu.end_date user_end_date, fur.end_date resp_end_date
 from fnd_user fu,
(select distinct u.user_id, u.user_name, WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID ,
(select application_id
          from fnd_application
         where application_short_name =/* Val between 1st and 2nd separator */
                 replace(substr(WURA.ROLE_NAME,INSTR(WURA.ROLE_NAME, '|', 1, 1)+1,( INSTR(WURA.ROLE_NAME,
 '|', 1, 2)-INSTR(WURA.ROLE_NAME, '|', 1, 1)-1)),'%col', ':')) RESPONSIBILITY_APPLICATION_ID,
       (select security_group_id
          from fnd_security_groups
         where security_group_key =/* Val after 3rd separator */
                 replace(substr(WURA.ROLE_NAME,INSTR(WURA.ROLE_NAME, '|', 1, 3)+1),'%col', ':')
       ) SECURITY_GROUP_ID,
       WURA.START_DATE,
       WURA.END_DATE END_DATE,
wura.relationship_id,
wur.role_orig_system,
wura.role_name,
wura.ASSIGNING_ROLE
from fnd_user u, wf_user_role_assignments wura,
     wf_all_user_roles wur
where u.user_name = wura.user_name
  and wura.relationship_id = -1
  and wur.role_orig_system = 'FND_RESP'
  and not wura.role_name like 'FND_RESP|%|ANY'
  and wura.role_name = wur.role_name
  and wura.user_name = wur.user_name) fur,
fnd_responsibility_tl frt, fnd_responsibility fr
where (fu.end_date is null or fu.end_date>sysdate)
and fu.USER_ID=fur.USER_ID
and frt.language = USERENV('LANG')
and fur.RESPONSIBILITY_APPLICATION_ID=fr.APPLICATION_ID
and fur.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and fr.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
and fr.APPLICATION_ID=frt.APPLICATION_ID
and fu.user_name = '&1'
UNION
select  fu.user_name , fu.description ,
fu.EMAIL_ADDRESS ,
NULL , fu.end_date user_end_date, fur.end_date resp_end_date
 from fnd_user fu,
(  select distinct u.user_id, u.user_name, WUR.ROLE_ORIG_SYSTEM_ID RESPONSIBILITY_ID ,
(select application_id
          from fnd_application
         where application_short_name =/* Val between 1st and 2nd separator */
                 replace(substr(WURA.ROLE_NAME,INSTR(WURA.ROLE_NAME, '|', 1, 1)+1,( INSTR(WURA.ROLE_NAME,
 '|', 1, 2)-INSTR(WURA.ROLE_NAME, '|', 1, 1)-1)),'%col', ':')) RESPONSIBILITY_APPLICATION_ID,
       (select security_group_id
          from fnd_security_groups
         where security_group_key =/* Val after 3rd separator */
                 replace(substr(WURA.ROLE_NAME,INSTR(WURA.ROLE_NAME, '|', 1, 3)+1),'%col', ':')
       ) SECURITY_GROUP_ID,
       WURA.START_DATE,
       WURA.END_DATE END_DATE,
wura.relationship_id,
wur.role_orig_system,
wura.role_name,
wura.ASSIGNING_ROLE
from fnd_user u, wf_user_role_assignments wura,
     wf_all_user_roles wur
where u.user_name = wura.user_name
  and wura.relationship_id <> -1
  and wur.role_orig_system = 'FND_RESP'
  and not wura.role_name like 'FND_RESP|%|ANY'
  and wura.role_name = wur.role_name
  and wura.user_name = wur.user_name) fur,
fnd_responsibility_tl frt, fnd_responsibility fr
where (fu.end_date is null or fu.end_date>sysdate)
and fu.USER_ID=fur.USER_ID
and frt.language = USERENV('LANG')
and fur.RESPONSIBILITY_APPLICATION_ID=fr.APPLICATION_ID
and fur.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and fr.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
and fr.APPLICATION_ID=frt.APPLICATION_ID
and fu.user_name = '&1'
order by 1;

How to find out which users are having a particular responsibility

There is a concurrent program called "Users of a responsibility"

When we submit this request, it Prompt for

                      1.Application name
                      2. Responsibility name.

To find Application  name, Goto Responsibility =>Define

search for Responsibility, you will get the Application name.


Now provide Application name and Responsibility name. it will give out put.

RMAN Backup Monitoring

Here is a simple query to monitor the RMAN progress.

select

 SID||'-'||OPNAME SID_OPNAME,

 100-round((sofar/totalwork) * 100,2) pct_left

from 

   v$session_longops

where 

   totalwork > sofar

AND 

   opname NOT LIKE '%aggregate%'

AND 

   opname like 'RMAN%';

SID_OPNAME                             PCT_LEFT               

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

3119-RMAN: incremental datafile backup 79.77                  

3135-RMAN: incremental datafile backup 79.87                  

3123-RMAN: incremental datafile backup 79.79                  

3143-RMAN: incremental datafile backup 79.92                  

3127-RMAN: incremental datafile backup 79.82                  

3159-RMAN: incremental datafile backup 78.28                  

3120-RMAN: incremental datafile backup 79.91                  

3136-RMAN: incremental datafile backup 78.16                  

3146-RMAN: incremental datafile backup 79.86                  

3183-RMAN: incremental datafile backup 85.29                  

3152-RMAN: incremental datafile backup 78.10

3151-RMAN: incremental datafile backup 79.74                  

3111-RMAN: incremental datafile backup 80.00                     

3124-RMAN: incremental datafile backup 79.74                  

3139-RMAN: incremental datafile backup 79.68                  

15 rows selected

Database Instance failed to turn on Flashback Database

ORA-38760:This database instance failed to turn on flashback database

Recently starting up a database reported ORA-38760. On this database I had flashback on. The error message indicates that the database flashback is on but the database failed to start generating flashback data. Checking the alert log found that there was no space in the disk group.

ORA-38701: Flashback database log 1442 seq 1519 thread 1: "+FB1"
ORA-17502: ksfdcre:4 Failed to create file +FB1
ORA-15041: diskgroup space exhausted
Can not open flashback thread because there is no more space in flash recovery area

Measures would be to add more disk space or free some space in the Disk Group so that the database can start.

I decided not to use the flashback any more and so determined to stop the flashback.

SQL>aler database flashback off;
SQL>shutdown immediate
SQL>startup

Again it resulted with the same message "ORA-38760:This database instance failed to turn on flashback database" which means the flashback is still not switched off.

Reason was flashback was not opened to close it. It still reported "Can not open flashback thread because there is no more space in flash recovery area" in the alert.log.

So it would be to review v$restore_point to see if there are options and I found that there was a restore point created with option guarantee which means the flashback is maintaining logs that are relevant for the guaranteed restore point. I do not need this restore point and so removed it. Database was then able to release a huge amount of space and my system was back again.

Replace 'no rows selected'

I wanted to replace the message 'no rows selected' with an alternate message.

SQL> select * from dual where 1=0;

no rows selected

SQL>

I found a good one by using NOT EXISTS clause

SQL> select * from dual where 1=0

           UNION

           select 'no data found' from dual where not exists (select * from dual where 1=0); 

DUMMY

no data found

1 row selected.

SQL>

What You need to Learn

Exploring the Oracle Database Architecture

• Oracle Database Architecture Overview
• Oracle ASM Architecture Overview
• Process Architecture
• Memory structrues
• Logical and physical storage structures
• ASM storage components

Installing your Oracle Software

• Tasks of an Oracle Database Administrator
• Tools Used to Administer an Oracle Database
• Installation: System Requirements
• Oracle Universal Installer (OUI)
• Installing Oracle Grid Infrastructure
• Installing Oracle Database Software
• Silent Install

Creating an Oracle Database

• Planning the Database
• Using the DBCA to Create a Database
• Password Management
• Creating a Database Design Template
• Using the DBCA to Delete a Database

Managing the Oracle Database Instance

• Start and stop the Oracle database and components
• Use Oracle Enterprise Manager
• Access a database with SQLPlus
• Modify database installation parameters
• Describe the stages of database startup
• Describe database shutdown options
• View the alert log
• Access dynamic performance views

Manage the ASM Instance

• Set up initialization parameter files for ASM instance
• Start up and shut down ASM instances
• Administer ASM disk groups

Configuring the Oracle Network Environment

• Use Enterprise Manager to create and configure the Listener
• Enable Oracle Restart to monitor the listener
• Use tnsping to test Oracle Net connectivity
• Identify when to use shared servers and when to use dedicated servers

Managing Database Storage Structures

• Storage Structures
• How Table Data Is Stored
• Anatomy of a Database Block
• Space Management in Tablespaces
• Tablespaces in the Preconfigured Database
• Actions with Tablespaces
• Oracle Managed Files (OMF)

Administering User Security

• Database User Accounts
• Predefined Administrative Accounts
• Benefits of Roles
• Predefined Roles
• Implementing Profiles

Managing Data Concurrency

• Data Concurrency
• Enqueue Mechanism
• Resolving Lock Conflicts
• Deadlocks

Managing Undo Data

• Data Manipulation
• Transactions and Undo Data
• Undo Data Versus Redo Data
• Configuring Undo Retention

Implementing Oracle Database Auditing

• Describe DBA responsibilities for security
• Enable standard database auditing
• Specify audit options
• Review audit information
• Maintain the audit trail

Database Maintenance

• Manage optimizer statistics
• Manage the Automatic Workload Repository (AWR)
• Use the Automatic Database Diagnostic Monitor (ADDM)
• Describe and use the advisory framework
• Set alert thresholds
• Use server-generated alerts
• Use automated tasks

Performance Management

• Performance Monitoring
• Managing Memory Components
• Enabling Automatic Memory Management (AMM)
• Automatic Shared Memory Advisor
• Using Memory Advisors
• Dynamic Performance Statistics
• Troubleshooting and Tuning Views
• Invalid and Unusable Objects

Backup and Recovery Concepts

• Part of Your Job
• Statement Failure
• User Error
• Understanding Instance Recovery
• Phases of Instance Recovery
• Using the MTTR Advisor
• Media Failure
• Archive Log Files

Performing Database Backups

• Backup Solutions: Overview
• Oracle Secure Backup
• User-Managed Backup
• Terminology
• Recovery Manager (RMAN)
• Configuring Backup Settings
• Backing Up the Control File to a Trace File
• Monitoring the Flash Recovery Area

Performing Database Recovery

• Opening a Database
• Data Recovery Advisor
• Loss of a Control File
• Loss of a Redo Log File
• Data Recovery Advisor
• Data Failures
• Listing Data Failures
• Data Recovery Advisor Views

Moving Data

• Describe ways to move data
• Create and use directory objects
• Use SQL*Loader to move data
• Use external tables to move data
• General architecture of Oracle Data Pump
• Use Data Pump export and import to move data

Working with Support

• Use the Enterprise Manager Support Workbench
• Work with Oracle Support
• Log service requests (SR)
• Manage patches