Sujeet APPS DBA

ora-39087 directory name data_pump_dir is invalid

Error :-

ora-39002 invalid operation

ora-39070 unable to open the log file

ora-39087 directory name DATAPUMP is invalid

Solution :-

create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Make sure that the /u01/EXPDP_IMPDP directory has 660 permission.

create or replace your directory and grant the following privileges' to user(system).

[oracle@prod u01]$ mkdir EXPDP_IMPDP

[oracle@prod u01]$ chmod 660 EXPDP_IMPDP

[oracle@QA u01]$ sqlplus sys/*****@PDB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 7 11:30:58 2022

Last Successful login time: Sat Jun 25 2022 14:49:08 -04:00

Connected to:

Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production

SQL> create directory EXPDP_IMPDP as '/u01/EXPDP_IMPDP';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_IMPDP TO system;

Grant succeeded.

SQL>

How to change Oracle Applications R12.1.3 Port

How to change Oracle Applications R12.1.3 Port-8002 to 8050

Step:-1

Dowm Application Tier

[applprod@prd-erp01 ~]$ cd $ADMIN_SCRIPTS_HOME

[applprod@prd-erp01 scripts]$ ./adstpall.sh apps/apps

Step:-2

Open your Context XML file for edit it.

Go to Context XML path :- $INST_TOP/appl/admin/

Take backup of .XML file :- cp Oracle_prd-erp01.xml Oracle_prd-erp01.xml-bkp10MAY22

Edit Oracle_prd-erp01.xml file with below parameter port value replace from 8002 to 8050.

Change the value from 8002 to be 8050 on the following lines:

<web_port oa_var="s_webport" oa_type="PORT" base="8000" step="1" range="-1" label="Web Listener Port">8050</web_port>

<login_page oa_var="s_login_page">http://<host>.<domain>:8050/OA_HTML/AppsLogin</login_page>

<chronosURL oa_var="s_chronosURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</chronosURL>

<oa_var="s_endUserMonitoringURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</EndUserMonitoringURL>

Save .XML file>>>>>>

Step:-3

Run Autoconfig on apps_tier for changes to take place.

[applprod@Oracle-prd-erp01 scripts]$ ./adautocfg.sh

Enter the APPS user password: *****

Step:-4

Start Application services.

[applprod@Oracle-prd-erp01 scripts]$ ./adstrtal.sh apps/apps

Step:-5

Verify new URL.

SQL> select HOME_URL from icx_parameters;

HOME_URL

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

http://Oracle-prd-erp01.oracle.net:8050/OA_HTML/AppsLogin

FDPSTP failed due to ORA-20100: Error: FND_FILE failure

I got recently a issue with Concurrent program, All program completed with below error.

**Starts**09-MAY-2022 21:07:39

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o5819484.tmp in the directory, /usr/tmp.

You will find more information in the request log.

ORA-06512: at "APPS.FND_FILE", line 417

ORA-06

Solution :-

change Directory file permssion to 777. so that others can write a file into it.

# chmod 777 /usr/tmp

Retest issue.

ORA-30091: secondary translation table not loaded

ORA-31693: Table data object "DEV1"."TABLE_CUST" failed to load/unload and is being skipped due to error:

ORA-30091: secondary translation table not loaded

Data Pump Import Reports ORA-31693,ORA-30091: Secondary Translation Table Not Loaded (Doc ID 2736570.1)

SYMPTOMS:

Performing a Data Pump Import the following errors occur:

ORA-31693: Table data object "USER"."TABLE" failed to load/unload and is being skipped due to error:

ORA-30091: secondary translation table not loaded

CAUSE:

The source database has a newer timezone version than the target database:

Target database timezone version:

SQL> select * from v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_26.dat 26 0

Source database timezone version :-

SQL> select * from v$timezone_file;

FILENAME VERSION CON_ID

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

timezlrg_28.dat 28 0

SOLUTION :-

Install the newer Timezone Version on the Target Database.

SQL> shut immediate

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2926472 bytes

Variable Size 889194616 bytes

Database Buffers 1241513984 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (28);

PL/SQL procedure successfully completed.

SQL> shut immediate

SQL> startup

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

Retest issue.

ORA-01172: recovery of thread 1 stuck at block 3024 of file 19

ERROR :

SQL> startup

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size 12170960 bytes

Variable Size 3019901232 bytes

Database Buffers 1.8388E+10 bytes

Redo Buffers 54935552 bytes

Database mounted.

ORA-01172: recovery of thread 1 stuck at block 3024 of file 19

ORA-01151: use media recovery to recover block, restore backup if needed

Cause:

This error usually means that blocks of file 19 are corrupted, In this case we need to recover file 19.

Crash recovery or instance recovery could not apply a change to a block because it was not the next change.

This can happen if the block was corrupted and then repaired during recovery.

SOLUTION :-

We need to recover all of corrupted block by using data file recovery or Database recovery.

In my case I have recover datafile 19.

SQL> recover datafile 19;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME OPEN_MODE

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

TEST READ WRITE

ORA-19563: header validation failed for file

Encountered while restoring the server on test database.

Restore log file :-

Finished restore at 03-MAR-22

released channel: c1

released channel: c2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of switch command at 03/03/2022 22:58:55

ORA-19563: header validation failed for file

RMAN>

CAUSE :-

Check alerts log in target and get the file number/name for which throwing this error .

SQL > SELECT FILE#, NAME FROM V$DATAFILE where NAME like '%system%';

FILE# NAME

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

92 /u01/test/db/apps_st/data/system17.dbf

139 /u01/test/db/apps_st/data/system17.dbf

Solution :-

Rename the file like (system17.dbf to system18.dbf)

SQL > SELECT FILE#, NAME FROM V$DATAFILE where NAME like '%system%';

FILE# NAME

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

92 /u01/test/db/apps_st/data/system17.dbf

139 /u01/test/db/apps_st/data/system18.dbf

NOW try to restore again.

REP-1804: Unable to open printer definition file HPL.prt

REP-0069: Internal error

REP-57054: In-process job terminated:Terminated with error:

REP-1804: Unable to open printer definition file '/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt'.

Solution :-

Verify HPL.prt file location and copy HPL.prt file to ('/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt') path.

[applmgr@oracle ]$ locate HPL.prt

/apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt

[applmgr@oracle ]$ cp /apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt /apps/apps_st/appl/CUST_TOP/12.0.0/reports/

Now retest issue.