Concurrent Processing - Command-Line Utility OR Recovery Wizard | cpadmin.sh

TROUBLESHOOTING STEPS

Concurrent Processing Command-Line Utility

The command-line utility cpadmin consolidates various existing utilities for concurrent processing into a single menu-based utility. This adadmin-style utility can be used for multiple tasks, including:
• Manager Status: Use this option to display the statuses of all managers.
• Clean CP Tables: Use this option to clean up the concurrent processing tables. This utility replaces cmclean.sql.
• Set Manager Diagnostics: Turn diagnostics on or off for individual managers with this option.
• Manager Control: Use this option to send a request such as start, stop, or verify to an individual manager.
• Rebuild Manager Views: Use this option to rebuild the FND_CONCURRENT_WORKER_REQUEST and FND_CONCURRENT_CRM_REQUESTS views.
• Move Request Files: Change request log and output file locations with this option.
• Analyze Request: Use this option to analyze a concurrent request.

Complete the steps below to run the command-line utility and its maintenance tasks.
1. Set the environment.
You must set the environment in order to apply the environment variables that define your system. This task is common to many utilities. See Setting the Environment, Oracle E-Business Suite Maintenance Guide for the preparatory steps.
2. From any directory, start cpadmin with this command:
$ cpadmin.sh
The utility starts and prompts you for the APPS password (required).
3. Respond to prompts.
Supply the information requested by cpadmin prompts. Prompts unique to an option are described with the option.
When you complete the prompts, the Main Menu appears.
4. Choose one of the tasks listed above.
5. Exit the cpadmin utility.

This is documented here: http://docs.oracle.com/cd/E26401_01/doc.122/e22953/T174296T575591.htm#8159578

Concurrent Manager Recovery Wizard

NOTE: The Concurrent Managers have to be stopped before the Wizard is run. Also insure that listeners are not stopped before the managers are stopped.

Please run the Concurrent Manager Recovery wizard to address Concurrent Manager failures within the Oracle Applications Manager.

Using the Concurrent Manager Recovery wizard is the method to clear the errors upon bringing the Internal Manager back up.

For Concurrent Internal Manager failures, it is recommended to run the Concurrent Manager Recovery feature using the Oracle Applications Manager. This feature should be used for recovering from when the Internal Manager won't start. This is accessed from the Troubleshooting wizards available within Oracle Applications Manager logged in as the sysadmin userid.

Navigate:

Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery

For information on the Concurrent Manager Recovery feature, please reference the Oracle Applications System Administrator's Guide - Maintenance provides information for frequent tasks such as monitoring your system with Oracle Applications Manager, administering Oracle E-Business Suite Secure Enterprise.  Search, managing concurrent managers and reports, using diagnostic utilities including logging, managing profile options, and using alerts.

 

Concurrent Processing - Command-Line Utility OR Recovery Wizard (Doc ID 134007.1)

Viewing Concurrent Request Output and Log Files in HTML/Browser

If the ouput and log files of the concurrent needs to be viewed in Browser, then we can perform the below action item. 1. Goto System Administrator responsibility and select Profile>System 2. Search for the 'Viewer:%' profile. 3. Set the profile Viewer: Application for Text to Browser. If this has to be set for all users then it has to be set at Site Level or for a particular user we can set it at user level. https://www.funoracleapps.com/2013/05/viewing-request-output-and-log-files-in.html

ORA-31626: job does not exist

dsvmofs> expdp system/XXXXX parfile=auexpfulltts.dat Export: Release 11.2.0.4.0 - Production on Wed Aug 17 05:29:16 2022 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1038 ORA-00372: file 7 cannot be modified at this time ORA-01110: data file 7: '/OFSSITDATA2/oradata/owad01.dbf' dsvmofs> cat auexpfulltts.dat directory=dmpdir metrics=y dumpfile=xttsfulltts%U.dmp filesize=1048576000 full=y exclude=STATISTICS logfile=expfulltts.log parallel=8 transportable=always version=12 dsvmofs> Root cause : Customer has SYS related tables in OWAPUB Tablespace, We made OWAPUB readonly as pert of TTS Activity. Solution : Moved the SYS related tables from OWAPUB tablespace to SYS. Other alternative solutions 1. Before restarting the expdp check if there are orphan Data Pump jobs left in database. Use << Note 336014.1 >> 2. After clearing the orphaned jobs if you still see the errors, Upload output of below queries. connect as sysdba spool boxa.html set pagesize 9999 set markup html on select name from v$database; SELECT * from v$version ; SELECT distinct(length(addr)*4) "Word Size" from v$process; SELECT owner,object_name,object_type,status from dba_objects where status = 'INVALID'; select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type; SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10) version, modified, SUBSTR(comp_name,1,30) comp_name, substr(schema,1,15) as schema FROM dba_registry order by comp_id; select * from registry$history;

How To Drop A NOT RUNNING DataPump Job? (Doc ID 1456784.1)

A DataPump import job created through DBMS_DATAPUMP API is listed as NOT RUNNING when querying DBA_DATAPUMP_JOBS: SQL> connect / as sysdba SQL> SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT ------- ---------- ------------------- -------------------------------------------------------- VALID 1015301 TABLE SYS.daily_import When trying to drop the master table it fails with: SQL> DROP TABLE SYS.daily_import; DROP TABLE SYS.daily_import * ERROR at line 1: ORA-00942: table or view does not exist You need to specify double quotes around the schema owner and the table name when dropping it: SQL> DROP TABLE "SYS"."daily_import"; SQL> purge recyclebin;

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)

SOLUTION The jobs used in this example: - Export job .EXPDP_20051121 is a schema level export that is running - Export job .SYS_EXPORT_TABLE_01 is an orphaned table level export job - Export job .SYS_EXPORT_TABLE_02 is a table level export job that was stopped - Export job SYSTEM.SYS_EXPORT_FULL_01 is a full database export job that is temporary stopped Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database: %sqlplus /nolog CONNECT / as sysdba SET lines 200 COL owner_name FORMAT a10; COL job_name FORMAT a20 COL state FORMAT a12 COL operation LIKE state COL job_mode LIKE state COL owner.object for a50 -- locate Data Pump jobs: SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED ---------- ------------------- --------- --------- ----------- -------- EXPDP_20051121 EXPORT SCHEMA EXECUTING 1 SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 SYS_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0 SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'. Step 3. Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed. (E.g. the full database export job by SYSTEM is not a job that failed, but was deliberately paused with STOP_JOB). Step 4. Identify orphan DataPump external tables. To do this, run the following as SYSDBA in SQL*Plus: set linesize 200 trimspool on set pagesize 2000 col owner form a30 col created form a25 col last_ddl_time form a25 col object_name form a30 col object_type form a25 select OWNER,OBJECT_NAME,OBJECT_TYPE, status, to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time from dba_objects where object_name like 'ET$%' / select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE from dba_external_tables order by 1,2 / Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump orphaned jobs. Drop the temporary external tables that belong to the DataPump orphaned job. eg: SQL> drop table system.&1 purge; Enter value for 1: ET$00654E1E0001 old 1: drop table system.&1 purge new 1: drop table system.ET$00654E1E0001 purge Step 5. Determine in SQL*Plus the related master tables: -- locate Data Pump master tables: COL owner.object FORMAT a50 SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT ------- ---------- ------------ ------------------------- VALID 85283 TABLE .EXPDP_20051121 VALID 85215 TABLE .SYS_EXPORT_TABLE_02 VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01 select table_name, owner from dba_external_tables; Step 6. For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.: DROP TABLE .sys_export_table_02; -- For systems with recycle bin additionally run: purge dba_recyclebin; Note: ===== Following statement can be used to generate the drop table statement for the master table: SELECT 'DROP TABLE '||o.owner||'.'||object_name||' PURGE;' FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%'; NOTE: In case the table name is mixed case, you can get errors on the drop, e.g.: SQL> drop table SYSTEM.impdp_schema_TEST_10202014_0; drop table SYSTEM.impdp_schema_TEST_10202014_0 * ERROR at line 1: ORA-00942: table or view does not exist Because the table has a mixed case, try using these statements with double quotes around the table name, for instance: drop table SYSTEM."impdp_SCHEMA_TEST_04102015_1"; drop table SYSTEM."impdp_schema_TEST_10202014_0"; Step 7. Re-run the query on dba_datapump_jobs and dba_objects (step 1 and 4). If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.: CONNECT / SET serveroutput on SET lines 100 DECLARE h1 NUMBER; BEGIN h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01',''); DBMS_DATAPUMP.STOP_JOB (h1); END; / Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view user_datapump_jobs to check whether the job has been removed: CONNECT / SELECT * FROM user_datapump_jobs; Step 8. Confirm that the job has been removed: CONNECT / as sysdba SET lines 200 COL owner_name FORMAT a10; COL job_name FORMAT a20 COL state FORMAT a12 COL operation LIKE state COL job_mode LIKE state COL owner.object for a50 -- locate Data Pump jobs: SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED ---------- ------------------- --------- --------- ----------- -------- EXPDP_20051121 EXPORT SCHEMA EXECUTING 1 SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0 -- locate Data Pump master tables: SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT ------- ---------- ------------ ------------------------- VALID 85283 TABLE .EXPDP_20051121 VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01 Remarks: 1. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. The view dba_datapump_jobs is a view, based on gv$datapump_job, obj$, com$, and user$. The view shows the Data Pump jobs that are still running, or jobs for which the master table was kept in the database, or in case of an abnormal end of the Data Pump job (the orphaned job). If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs. 2. When starting the new Data Pump job and using a system generated name, we check the names of existing Data Pump jobs in the dba_datapump_job in order to obtain a unique new system generated jobname. Naturally, there needs to be enough free space for the new master table to be created in the schema that started the new Data Pump job. 3. A Data Pump job is not the same as a job that is defined with DBMS_JOBS. Jobs created with DBMS_JOBS use there own processes. Data Pump jobs use a master process and worker process(es). In case a Data Pump still is temporary stopped (STOP_JOB while in interactive command mode), the Data Pump job still exists in the database (status: NOT RUNNING), while the master and worker process(es) are stopped and do not exist anymore. The client can attach to the job at a later time, and continue the job execution (START_JOB). 4. The possibility of corruption when the master table of an active Data Pump job is deleted, depends on the Data Pump job. 4.a. If the job is an export job, corruption is unlikely as the drop of the master table will only cause the Data Pump master and worker processes to abort. This situation is similar to aborting an export of the original export client. 4.b. If the job is an import job then the situation is different. When dropping the master table, the Data Pump worker and master processes will abort. This will probably lead to an incomplete import: e.g. not all table data was imported, and/or table was imported incomplete, and indexes, views, etc. are missing. This situation is similar to aborting an import of the original import client. The drop of the master table itself, does not lead to any data dictionary corruption. If you keep the master table after the job completes (using the undocumented parameter: KEEP_MASTER=Y), then a drop of the master table afterwards, will not cause any corruption. 5. Instead of the status 'NOT RUNNING' the status of a failed job could also be 'DEFINING'. When trying to attach to such a job, this would fail with: $ expdp system/ attach=system.sys_export_schema_01 Export: Release 11.2.0.4.0 - Production on Tue Jan 27 10:14:27 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.KUPV$FT", line 405 ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user SYSTEM ORA-31632: master table "SYSTEM.SYS_EXPORT_SCHEMA_01" not found, invalid, or inaccessible ORA-00942: table or view does not exist The steps to cleanup these failed/orphaned jobs are the same as mentioned above.

RMAN DUPLICATE STANDBY

run { allocate channel tgt1 device type disk ; allocate channel tgt2 device type disk ; allocate channel tgt3 device type disk ; allocate channel tgt4 device type disk ; allocate channel tgt5 device type disk ; allocate channel tgt6 device type disk ; allocate channel tgt7 device type disk ; allocate channel tgt8 device type disk ; allocate channel tgt9 device type disk ; allocate channel tgt10 device type disk ; allocate channel tgt11 device type disk ; allocate channel tgt12 device type disk ; allocate channel tgt13 device type disk ; allocate channel tgt14 device type disk ; allocate channel tgt15 device type disk ; allocate channel tgt16 device type disk ; allocate auxiliary channel aux1 device type disk ; allocate auxiliary channel aux2 device type disk ; allocate auxiliary channel aux3 device type disk ; allocate auxiliary channel aux4 device type disk ; allocate auxiliary channel aux5 device type disk ; allocate auxiliary channel aux6 device type disk ; allocate auxiliary channel aux7 device type disk ; allocate auxiliary channel aux8 device type disk ; allocate auxiliary channel aux9 device type disk ; allocate auxiliary channel aux10 device type disk ; allocate auxiliary channel aux11 device type disk ; allocate auxiliary channel aux12 device type disk ; allocate auxiliary channel aux13 device type disk ; allocate auxiliary channel aux14 device type disk ; allocate auxiliary channel aux15 device type disk ; allocate auxiliary channel aux16 device type disk ; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name=‘cvs19qas_stby’ COMMENT ‘Is standby’ set DB_CREATE_FILE_DEST = ‘+DATAC1’ set DB_CREATE_ONLINE_LOG_DEST_1 = ‘+DATAC1’ set DB_RECOVERY_FILE_DEST = ‘+RECOC1’ set diagnostic_dest=‘/u02/app/oracle’ set DB_RECOVERY_FILE_DEST_SIZE=‘1500G’ set audit_file_dest=‘/u02/app/oracle/product/19.0.0.0/dbhome_1/admin/cvs19qas/adump’ SET job_queue_processes=‘0’ SET LOCAL_LISTENER=‘’ SET REMOTE_LISTENER=‘’ set cluster_database=‘false’ NOFILENAMECHECK; }

How to drop other schema’s Database link using SYS user | Drop db_link from other user

SQL> select owner,db_link from dba_db_links; OWNER DB_LINK ------------------------------ -------------------------------------- TEST TEST_DB_LINK SQL> sho user USER is "SYS" SQL> SQL> drop database link test.test_db_link; drop database link test.test_db_link * ERROR at line 1: ORA-02024: database link not found [test@test.test.com ~]$ cat drop_schema_dblink.sh username=$1 db_link=$2 sqlplus /nolog < SQL> Connected. SQL> " DB Link Before Drop" SQL> SQL> SQL> OWNER DB_LINK ------------------------------ ------------------------------ TEST TEST_DB_LINK SQL> 2 3 4 5 Procedure created. SQL> PL/SQL procedure successfully completed. SQL> Procedure dropped. SQL> " DB Link After Drop" SQL> no rows selected SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options !