select machine,count(*) from v$session group by machine;
CM-JOBS-ON-HOLD
Put jobs on hold
----------------
Put Jobs on Hold
<<< Check Putty Spool Log >>>
SQL> spool Spool_Jobs_on_hold_09JAN2021.txt;
SQL> SELECT SYSTIMESTAMP FROM DUAL;
1.Connect as apps user, Drop old table (jobs_already_on_hold)
SQL> DROP TABLE jobs_already_on_hold;
2. List out the Jobs which were already on HOLD
SQL> SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE hold_flag = 'Y';
3.Create backup of the table with the jobs which were already ON HOLD.
SQL>
CREATE TABLE jobs_already_on_hold
AS
(SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE hold_flag = 'Y');
4. Verify the count from backup table with the above query output
SQL> SELECT COUNT (*) FROM jobs_already_on_hold;
SQL> SELECT * FROM jobs_already_on_hold;
5.Now, Place the pending jobs on HOLD using the below update command.
SQL>
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P' AND hold_flag = 'N';
SQL> COMMIT;
6. After Putting the Jobs ON HOLD, now verify if any Running Requests with PAUSED Status.
If there are any Running requests with PAUSED status, please verify the child requests status of this Program.
If there were any put on HOLD as part of the putting Jobs on Hold process.
Release them from HOLD and process the requests.
7.Check again the pending scheduled program during the time frame 3 PM to 5 PM CST
8.Please ensure to check the below query for every 5 minutes until it returns 0 rows.
List the pending ,running and passed requests
SQL>
SELECT REQUEST_ID,
PHASE_CODE,
STATUS_CODE,
HAS_SUB_REQUEST,
IS_SUB_REQUEST,
hold_flag,
REQ_INFORMATION
FROM apps.fnd_concurrent_requests
WHERE (phase_code = 'P' AND hold_flag = 'N')
OR phase_code = 'R'
OR status_code = 'W';
##############################################USE THIS FOR RUNNIG REQUESTS##############################
SQL> col USER_CONCURRENT_PROGRAM_NAME for a30
SQL> col REQUESTOR for a10
SELECT request_id, --parent_request_id,
user_concurrent_program_name,
--program,
--(select responsibility_name from apps.fnd_responsibility_vl fr where fr.responsibility_id = fcs.responsibility_id) responsibility_name,
-- program_short_name,
argument_text,
requestor,
phase_code,
status_code,
-- completion_text,
to_char (actual_start_date, 'DD-MON-YY HH24:MI:SS') actual_start_date,
to_char (actual_completion_date, 'DD-MON-YY HH24:MI:SS') actual_completion_date,
NUMTODSINTERVAL (
NVL (actual_completion_date, SYSDATE) - actual_start_date,
'day')
run_time,requested_start_date
FROM apps.fnd_conc_req_summary_v fcs
WHERE 1=1
--and program like 'Requisition%Import%'
--AND FCS.REQUEST_ID = 99819130
--AND REQUESTOR = 'RMUTTA'
and PHASE_CODE = 'R' -- Request Status
--AND STATUS_CODE <> 'Q'
order by run_time desc;
** once activity is completed and start the application services and perform the sanity checks and release the on hold jobs
#####################################################################CHECK BEFORE RELEASE################
- Release Jobs from HOLD
connect as apps user and perform the below:
SQL>
UPDATE fnd_concurrent_requests
SET hold_flag = 'N'
WHERE hold_flag = 'Y'
AND request_id NOT IN (SELECT request_id FROM jobs_already_on_hold);
SQL> COMMIT;
=====Cancel requests manually============================
update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';
Release child requests
=====================
UPDATE fnd_concurrent_requests SET hold_flag = 'N' where REQUEST_ID='&REQUEST_ID';
DB OPEN TIME
SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
FROM V$INSTANCE;
REQ-HIST
REM +======================================================================+
REM
REM File Name: reqhistory.sql
REM
REM Description:
REM Query To Check Concurrent Program Run History
REM
REM Notes:
REM Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql
REM
REM Input Required :
REM Number of days and User Concurrent Program Name
REM
REM +======================================================================+
ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";
ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";
clear columns
set lines 180
set pages 100
col Parameters for a20 WORD_WRAPPED
set pages 100
col "Conc Program Name" for a30 WORD_WRAPPED
col "Started at" for a20
col "Completed at" for a20
col "Username" for a10 WORD_WRAPPED
SELECT distinct t.user_concurrent_program_name "Conc Program Name",
r.REQUEST_ID "Request ID",
to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",
u.user_name "Username",
--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
-- 2
-- ) "Runtime (in Minutes)"
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate - &NO_DAYS)
--AND r.requested_by=22378
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;
undef NO_DAYS
undef USER_CONC_PROG_NAME
FNDNODES
clear columns
set lines 180
set pages 50
col NODE_NAME for a15 WORD_WRAPPED
COL SUPPORT_DB for a12
COL SUPPORT_CP for a12
COL SUPPORT_ADMIN for a15
COL SUPPORT_FORMS for a15
COL SUPPORT_WEB for a12
COL HOST for a15 WORD_WRAPPED
Col STATUS for a10
col DOMAIN for a20 WORD_WRAPPED
select NODE_NAME,SUPPORT_DB,SUPPORT_CP,SUPPORT_ADMIN,SUPPORT_WEB,SUPPORT_FORMS,STATUS,HOST,DOMAIN
from apps.fnd_nodes;
ADOP PHASES
Prepare Phase:
- Prepares the system for patching cycle.
- Creates the Database Patch Edition
- Validates system configuration
- Check & Submit Concurrent Request 'Online Patching In Progress'(ADZDPATCH)
- Prepare is run on all nodes in a mute-node configuration
- Synchronizes the Run and Patch File System
- If cleanup was not executed in previous adop cycle it will also run Cleanup.
Syntax:
adop phase=prepareApply Phase:
- Patches are applied in this phase.
- Adop internally calls adpatch to apply the patches, but we cannot run adpatch utility as standalone in R12.2 .
- Patches are applied in the Patch Edition.
- Application user are connected to RUN edition and they are not impacted by patching cycle.
- We can apply multiple patches in a patching cycle.
adop phase=apply
Finalize Phase:
- Ready the system for Cutover.
- Compile Invalid objects.
- If we don't run finalize, then the cutover phase will call finalize automatically before doing the actual cutover. But that will increase the downtime window for the cutover.
- Computes any DDL to be executed before the cutover.
adop phase=finalize
Cutover Phase:
- Switches to the patch edition of database and file system.
- In the phase, the system actually goes down.
- All application tier services are stopped and starts after the cutover.
adop phase=cutover
Cleanup Phase:
- Cleans up old edition and objects.
- Recovers space.
Syntax:
adop phase=cleanup
Adop opttions:
https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531062.htm
{ echo systempasswd; echo appspasswd ; echo weblogicpaswd ; }|adop phase=apply patches=31883479_D:u31883479.drv,31883479_ESA:u31883479.drv,31883479_HR:u31883479.drv,31883479_PL:u31883479.drv,31883479_RU:u31883479.drv,31883479_SK:u31883479.drv apply_mode=downtime merge=yes prompt=no
GATHER SCHEMA STATS
Why sometimes gather stats runs for longer time than normal.
There are many possible causes but the most common are:
a) Database/application process or processes are running which are updating the database. When GSS runs it will invalidate all of the stats on the object it is analyzing. If a process then trying to update or use that table it will start doing full table scan which will affect the overall database performance, which in turn impact GSS.
b) Large amount of data has been added to the database. The more records you have the longer the GSS will take to complete the process.
c) Recollecting CBO stats on tables that have not changed. If a table has 100 million rows then gathering stats on that table will take a long time, however if no or little changes are made then there is no need to delete all of the old stats and regather them (which is what you are doing)
d) Gathering statistics invalidates cursors which can hamper performance.( Unless you use the ‘No Invalidate’ option)
IMPORTANT TABLES/VIEWS RELATED TO GATHER SCHEMA STATISTICS
1) FND_STATS_HIST
To record the time taken for gathering the statistics for the different types of objects.
2) FND_HISTOGRAM_COLS
Gather Schema stats create the histogram for the specified columns in the tables.
IMPORTANT SCRIPTS RELATED TO GATHER SCHEMA STATISTICS
1) If the custom schema is not registered, it will not show up in the LOV for schema selection for the mentioned concurrent programs.
You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:
select distinct(upper(oracle_username)) sname
from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id order by sname;
How to verify if the current gathered statistics are correct?
We use the Verify Stats report to determine whether the current statistics are accurate.
This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats(‘schema’, ‘object_name’);
GATHER SCHEMA STATISTICS NOT RUNNING FOR CUSTOM MODULES. WHY?
When we submit Gather Schema Stats with Parameter ALL, concurrent request will complete successfully but custom schemas may not get analyzed.
Script to check if custom schema is analyzed :
Sql > select count(table_name) from dba_tables where last_analyzed is not null and owner= <custom_schema_name>;
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;
Note : When custom schemas are created the entry is not made in FND_PRODUCT_INSTALLATIONS and hence it is not picked up in the above query.
Solution :
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
http://expertoracle.com/2013/02/01/gather-statistics-in-r12-and-11i/
why we run datapatch:
The datapatch utility will run the necessary apply scripts to load the modified SQL files into the database. Entires will be added to both DBA_REGISTRY_HISTORY and DBA_REGISTRY_SQLPATCH views automatically.
DEBAM1> SELECT patch_id, status FROM dba_registry_sqlpatch;
PATCH_ID STATUS
---------- ---------------
22139226 SUCCESS
21948354 SUCCESS
20204035 SUCCESS
22139226 SUCCESS
---------- ---------------
30805558 END
20204035 END
31219939 END
31113348 BEGIN
it will update like begin then end then sucess
Why we use open resetlogs?
Qn 1 :
A RESETLOGS is required in either of
a. Recovery using BACKUP CONTROLFILE
b. Incomplete Recovery
If you are using a Backup controlfile (whether from a Binary Backup or actually via a CREATE CONTROLFILE), the Controlfile is not current -- therefore it does not have the database SCN and LogSequenceNumbers. The RESETLOGS updates this information back to the controlfile (normally, a Recovery is the other way round with the controlfile's SCN, being the highest, driving the Recovery).
Also, a Resetlogs is required in both cases to ensure that the older Redo Logs (e.g. they might still be on disk) are not used -- the Resetlogs creates a new Incarnation of the database.
Qn 2:
There is no difference between an ALTER DATABASE OPEN; and an ALTER DATABASE OPEN NORESETLOGS;.
The NORESETLOGS is the default action in an OPEN if you do not specify RESETLOGS.
(Obviously, Oracle check to see if you have used a Backup Controlfile and/or done an Incomplete Recovery, in which case it does not allow you to OPEN without the RESETLOGS).
AUTOCONFIG IS NOT RUN FOR ALL PATCHES
###############WHY AUTOCONFIG IS NOT RUN DURING PATCH########
Skipping ...
Running AutoConfig since none of its templates were
patched during this run of adpatch.
BLOCK CORRUPTION and BMR
BLOCK CORRUPTION:
==================
DB maintaince may occur unfortunately and one of the case we can say block corruption.
whenever we find that we are unable to view the data as excepted. We can observer it is block corruption.
Block corruption may happen below scenario's:
1.Hard disk
2.Powerfailuers
3.N/W Issues.
4.OS Issues
Identify methods for block corruption:
1.DBA_BLOCKCORRUPTION
2.DBVERIFY='datafile location'(OS COMMANDS).
3.USING RMAN(Validate datafile, tablespace)
Block media recover :
To do Block Media Recover the database should be in mount or open state.
Connect to RMAN target database.
then fire Recover
CHECKPOINT AND BEGIN BACKUP MODE USES
CHECKPOINT
============
Checkpoint is mandatory background process which is requested by DBWR when its being writing data from buffer to data files.
It is used for synchronize the buffer and datafiles
Checkpoint only updates header information to data files whenever DBWR completes writing to datafiles.
Checkpoint update Highest SCN number to all Datafiles from buffer.
Checkpoint occurs from bellow scenario's:
1.DB normal shutdown
2.DBA manual triggered checkpoint.(alter system checkpoint)
3.When its reaches three seconds or PGA_TARGET parameter value.
4.if we defined fast_mttr value then if it reaches fast_mttr.
5.Alter database begin backup mode;
6.when datafile goes offline.
Subscribe to:
Posts (Atom)