Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

Delete Expired Or Obsolete Archvielog Files By RMAN

August 10th, 2025, posted in Oracle Queries
Share

RMAN provided two commands for deleting archive log.

  1. Delete expired archivelog
  2. Backup archive log all delete input

These command can delete archive logs based on the input provided. There were two things that RMAN made sure every time it attempted to delete an archive log. The first is that specified archive log is backed up to all the locations specified under log_archived_dest_n parameters. The value under these parameters can be flash recovery area or non flash recovery area. Second is whether the archive logs to be deleted are obsolete or not. This in turn depends on your backup retention policy. An archive redo log is considered obsolete if it is not required in any recovery scenario.

Using RMAN we can remove the old archvielogs or old backup file using report obsolete or delete obsolete by setting rman rman retention policy :

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\USER\PRODUCT\12.1.0\DBHOME_1\DATA
BASE\SNCFDEV.ORA'; # default
RMAN>

Above Configuration show  RMAN  retention policy is 1, based on this retention policy only report obsolete or delete obsolete will work.

Manually we can remove the expired archive log using below 2 options:

1. delete .. expired archivelog;
2. backup archivelog all delete input;

1. delete .. expired archivelog;

By using this command we can remove the old archive-log manually.
Using OS command remove the old archive-log files from the archivelog log location.
Move to archivelog location then issue (to find the achive-log location , connect sqlplus / as sysdba )
execute below command to find the archive log.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     46
Next log sequence to archive   48
Current log sequence           48
SQL> 
Archvielog location using Flash revoerey area,

SQL> show parameter recover
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\user\fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL>

ARchivelog location is “E:\app\user\fast_recovery_area\dev\ARCHIVELOG”.
move to this location and issue command below with 2 options.
Recommended options is move the old archivelog file to some other directory wait for 2 days then remove that files. or directly reomove like below
rm -rf  *.arc or
rm -rf  (specified archivelog files)

Then issue the command connecting RMAN.

C:\Users\user>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 11 17:19:15 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEV (DBID=4027486540)
RMAN>
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>
Before crosscheck its shows like this.
RMAN> crosscheck archivelog all;

It will crosscheck the archivelog files with RMAN repository deleted files status changed to expired in RMAN repository.

RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
39      1    48      X 11-OCT-17
        Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_
8_DXW2ZZFW_.ARC
RMAN>

Then issue below command to remove from the RMAN repository.

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
39      1    48      X 11-OCT-17
        Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_4
8_DXW2ZZFW_.ARC
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\
O1_MF_1_48_DXW2ZZFW_.ARC RECID=39 STAMP=957116888
Deleted 1 EXPIRED objects
RMAN>
RMAN> delete noprompt expired archivelog all;

2. Backup archive-log all delete input:
Above command will remove the all archive-log file after taking the backup of all archvielog available in current RMAN repository.
We have options also
It will take backup 2 days before archive-log file and then remove it.

RMAN> backup archivelog until time 'sysdate-2' delete input;

Crosscheck commands :

To crosscheck all backups use:
RMAN> CROSSCHECK BACKUP ;
To list any expired backups detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED BACKUP ;
To delete any expired backups detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED BACKUP ;
To crosscheck all archive logs use:
RMAN> CROSSCHECK ARCHIVELOG ALL ;
To list all expired archive logs detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED ARCHIVELOG ALL ;
To delete all expired archive logs detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED ARCHIVELOG ALL ;
To crosscheck all datafile image copies use:
RMAN> CROSSCHECK DATAFILECOPY ALL ;
To list expired datafile copies use:
RMAN> LIST EXPIRED DATAFILECOPY ALL ;
To delete expired datafile copies use:
RMAN> DELETE EXPIRED DATAFILECOPY ALL ;
To crosscheck all backups of the USERS tablespace use:
RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS ;
To list expired backups of the USERS tablespace:
RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS ;
To delete expired backups of the USERS tablespace:
RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS ;
Share
read more 1 comment

Query To List Scheduled Jobs in Oracle Database

July 12th, 2025, posted in Oracle EBS Application, Oracle Queries
Share

Query : 

select owner as schema_name,
       job_name,
       job_style,
       case when job_type is null 
                 then 'PROGRAM'
            else job_type end as job_type,  
       case when job_type is null
                 then program_name
                 else job_action end as job_action,
       start_date,
       case when repeat_interval is null
            then schedule_name
            else repeat_interval end as schedule,
       last_start_date,
       next_run_date,
       state
from sys.all_scheduler_jobs
order by owner,
         job_name;

Columns :

  • schema_name – name of the schema
  • job_name – name of the job
  • job_style:
    • REGULAR
    • LIGHTWEIGHT
  • job_type – inline job action type
    • PLSQL_BLOCK
    • STORED_PROCEDURE
    • EXECUTABLE
    • CHAIN
    • SQL_SCRIPT
    • BACKUP_SCRIPT
    • EXTERNAL_SCRIPT
    • PROGRAM
  • job_action – PL/SQL code or program/routine name to call
  • start_date – when job will be launched first time
  • schedule – inline schedule PL/SQL expression, calendar string or name of the schedule
  • last_start_date – last date on which the job started running
  • next_run_date: next date on which the job is scheduled to run
  • state:
    • Disabled
    • Scheduled

Rows

  • One row: represents one job
  • Scope of rows: all jobs in database
  • Ordered by job schema, job name
Share
read more 2 comments

Oracle R12 E-Business Suite Output Post Processor (OPP) Fails To Pick Up Concurrent Requests With Error ‘Unable to find an Output Post Processor service to post-process request ’

November 17th, 2024, posted in Concurrent Request, Oracle Queries
Share

E-Business Suite R12 Concurrent Processing, Output Post Processing related issues
Requests fail during Post Processing with the following errors :

‘Unable to find an Output Post Processor service to post-process request xxxxx.
Check that the Output Post Processor service is running’

Cause :

Incorrect data is present in the OPP queue table AQ$FND_CP_GSM_OPP_AQTBL_S, which occurs when the concurrent managers are not shutdown correctly.
A quick sql query shows orphaned OPP subscribers in APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S, causing the select_random_subscriber routine to pick a non-running process.

Solution :

To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Find the active processes for OPP :

SELECT fcp.concurrent_process_id
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE concurrent_queue_name = ‘FNDCPOPP’
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND fcp.process_status_code = ‘A’;

Output example:

CONCURRENT_PROCESS_ID
———————
93557
93558

2. Find the OPP subscribers :

select name from APPLSYS.AQ$FND_CP_GSM_OPP_AQTBL_S ;

The name in the table is the concurrent_process_id prefixed with ‘OPP’.
The output should normally correspond to the output of first query: have the same number of records and have as name the concurrent_process_id of the running OPP processes prefixed by OPP


Output example:
NAME
——————————
OPP35562
OPP35563
OPP35564
OPP35565

3. If there are any old process which are left from previous runs of OPP processes, unsubscribe them via the following command:

exec fnd_cp_opp_ipc.unsubscribe(”);
Example:
exec fnd_cp_opp_ipc.unsubscribe(‘88933’);
exec fnd_cp_opp_ipc.unsubscribe(‘92625’);

4. Bounce the concurrent managers.
5. Retest some concurrent requests and confirm the post processing actions now complete successfully.

Share
read more no comments

Output Post Processor (OPP) – An Overview By Author – A.Kishore/Sach

October 12th, 2024, posted in Concurrent Request, Oracle EBS Application, Oracle Queries
Share

Output Post Processor (OPP) – An Overview

  • Concurrent processing uses the Output Post Processor (OPP) to enforce post-processing actions for concurrent requests.
  • Post-processing actions are actions taken on concurrent request output. An example of a post-processing action is that used in publishing concurrent requests with XML
  • Publisher.
  • The Output Post Processor (OPP) is an enhancement to Concurrent Processing and is designed to support XML Publisher as post-processing action for concurrent requests. If a request is submitted with an XML Publisher template specified as a layout for the concurrent request output, then after the concurrent manager finishes running the concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output.
  • A concurrent manager contacts an available OPP process when a running concurrent request needs an OPP processing action. Concurrent manager uses a local OPP process(that, is, on the same node) by default, but will choose a remote OPP if no local OPP process is available. There should always be at least one OPP process active in the system. If no OPP service is available, completed requests that require OPP processing will complete with a status of Warning.
  • The Output Post Processor makes use of the Oracle Streams Advanced Queuing (AQ) database feature. Every OPP service instance monitors the FND_CP_GSM_OPP_AQ queue for new messages and this queue has been created with no value specified for primary_instance (link). This implies that the queue monitor scheduling and propagation is done in any available instance. In other words, ANY OPP service instance may pick up an incoming message independent of the node on which the concurrent request ran.

Maximum Memory Usage Per Process :


The maximum amount of memory or maximum Java heap size a single OPP process can use is by default set to 512MB. This value is seeded by the Loader Data File:
$FND_TOP/patch/115/import/US/afoppsrv.ldt which specifies that the DEVELOPER_PARAMETERS is “J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m”.

Determine the current maximum Java heap size :

SELECT service_id, service_handle, developer_parameters
FROM fnd_cp_services
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');
SERVICE_ID SERVICE_HANDLE DEVELOPER_PARAMETERS

1091 FNDOPP J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

Increase the maximum Java heap size for the OPP to 1024MB (1GB) :

UPDATE fnd_cp_services
SET developer_parameters =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
WHERE service_id = (SELECT manager_type
FROM fnd_concurrent_queues
WHERE concurrent_queue_name = 'FNDCPOPP');

The OPP queue can be Recreated the using $FND_TOP/patch/115/sql/afopp002.sql file as
‘APPLSYS’ user. On running the script you will be prompted for username and password.

There are two new profiles options that can be used to control the timeouts :

Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its
request for post processing.
Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually
process the request.
Share
read more no comments
« Older Entries
Newer Entries »