To calculate the Datafile Growth on a yearly basis:
select to_char(CREATION_TIME,’RRRR’) year,to_char(CREATION_TIME,’MM’) month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,’RRRR’),to_char(CREATION_TIME,’MM’) order by 1,2;
Through OEM :
Here are the steps to know the Database growth pattern for the last month/year using OEM
1) log in to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on the Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for the last month.
4) Also we can get one year of Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and clicking on the Oracle Database Tablespace Monthly Space Usage link.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
1. How to Determine Which Manager Ran a Specific Concurrent Request?
col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';
2. Concurrent manager status for a given sid?
col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';
3. Find out request-id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
4. To find sid, serial# for a given concurrent request id?
set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
5. To find the concurrent program name, phase code, and status code for a given request id?
6. To find the SQL query for a given concurrent request sid?
select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/
7. To find child requests
set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
8. Cancelling Concurrent requests:
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;
9. Kill sessions program-wise
select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';
10 . Concurrent Request running by SID
SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;
11. Find out request-id from Oracle_Process Id:
select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';
12. Oracle Concurrent Request Error Script (requests which were errored out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;
13. Request submitted by User
SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;
14. Concurrent Program enables trace
col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Below i face an error when i was running adop session.
When i was running adop phase=cleanup i was hit with the below error
[applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Wed May 24 2021 03:34:53
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 62)
Cleanup is not done in earlier session
[START 2021/05/24 03:35:27] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
[START 2021/05/24 03:35:48] cleanup phase
[EVENT] [START 2021/05/24 03:35:52] Performing Cleanup steps
[EVENT] [START 2021/05/24 03:35:57] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/log
[EVENT] [END 2021/05/24 03:39:43] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/05/24 03:39:54] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/adzdallddls_20210524_033957.out
[END 2021/05/24 03:39:58] Generating All DDL Report [EVENT] Calling cleanup in QUICK mode [WARNING] Cleanup may take a while. Please wait. [ERROR] Failed to execute sql statement : declare result varchar2(10); begin ad_zd.cleanup(‘QUICK’); exception when others then raise_application_error(-20001,’Error while calling ad_zd.cleanup’ || sqlerrm); end; [ERROR] SQLPLUS error: buffer=
SQL*Plus: Release 10.1.0.5.0 – Production on Wed May 24 03:40:01 2021
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> declare ERROR at line 1: ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old: rollback segment number 2 with name “_SYSSMU2_735814084$” too small
ORA-06512: at line 7
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[UNEXPECTED]Error occurred while calling cleanup plsql API
[UNEXPECTED]Cleanup phase completed with errors/warnings. Please check logfiles
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
adop exiting with status = 1 (Fail)
[applmgr@r122 ~]$
To overcome this issue.I have extended the undotablespace using below:
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME like ‘APPS_UNDOTS1’;
TABLESPACE_NAME FILE_NAME
APPS_UNDOTS1 /u01/oracle/PROD/data/undo01.dbf
SQL> ALTER DATABASE DATAFILE ‘/u01/oracle/PROD/data/undo01.dbf’ RESIZE 10240M;
Database altered.
SQL>
Then again i executed the adop phase=cleanup
applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admi
n/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/ad
min/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Fri Feb 28 2021 04:11:18
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 59)
Cleanup is not done in earlier session
[START 2021/02/28 04:11:45] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
[START 2021/02/28 04:12:03] cleanup phase
[EVENT] [START 2021/02/28 04:12:06] Performing Cleanup steps
[EVENT] [START 2021/02/28 04:12:11] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/log
[EVENT] [END 2021/02/28 04:13:26] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/02/28 04:13:32] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdallddls_20210228_041335.out
[END 2021/02/28 04:13:36] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[EVENT] [END 2021/02/28 04:57:56] Performing Cleanup steps
[END 2021/02/28 04:58:05] cleanup phase
[START 2021/02/28 04:58:11] Generating AD_ZD_LOGS Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdshowlog.out
[END 2021/02/28 04:58:24] Generating AD_ZD_LOGS Report
[END 2021/02/28 04:58:25] adzdoptl.pl run
adop phase=cleanup – Completed Successfully
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
adop exiting with status = 0 (Success)
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
