How to recover corrupted UNDO tablespace

Dear All,

Yesterday our development system went down because of CORRUPTED UNDO TABLESPACE.

the following error reported in the alert.log file

Sun Jul 31 09:27:35 2011

Flush retried for xcb 0x333b8e78, pmd 0x32898888

Doing block recovery for file 2 block 330

Block recovery from logseq 1462, block 70 to scn 42078052

Sun Jul 31 09:27:36 2011

Recovery of Online Redo Log: Thread 1 Group 3 Seq 1462 Reading mem 0

  Mem# 0 errs 0: C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

Block recovery completed at rba 1462.408.16, scn 0.42078054

Sun Jul 31 09:27:38 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:41 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

Sun Jul 31 09:27:45 2011

DEBUG: Replaying xcb 0x333ad298, pmd 0x33bc9e54 for failed op 8

Doing block recovery for file 2 block 227

Sun Jul 31 09:27:45 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j006_4020.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

ORA-00600: internal error code, arguments: [4194], [18], [24], [], [], [], [], []

No block recovery was needed

Sun Jul 31 09:27:49 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_948.trc:

ORA-00600: internal error code, arguments: [4194], [13], [7], [], [], [], [], []

PMON: terminating instance due to error 472

Sun Jul 31 09:27:53 2011

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j009_1608.trc:

ORA-00472: PMON  process terminated with error

Kindly use the following solution if you encountered with the exact above situation.

Step 1

--------

SQL> Startup nomount ; --> using spfile

SQL> Create pfile='/tmp/corrupt.ora' from spfile ;

SQL> Shutdown immediate;

Step 2

-------

Modify the corrupt.ora and set Undo_managment=Manual

SQL> Startup mount pfile='/tmp/corrupt.ora'

SQL> Show parameter undo

it should show manual

SQL> Alter database open ;

If it comes up

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile '<>' size <> M ;

Drop the Old undo tablespace

SQL> Drop tablespace including contents and datafiles

Step 3

-------

SQL> Shutdown immediate;

SQL> Startup nomount ; ---> Using spfile

SQL>Alter system set undo_tablespace= scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported

Live Example:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 31 10:52:25 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> create pfile = 'c:\corrupt.ora' from spfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount pfile='c:\corrupt.ora';

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> create rollback segment r01;

Rollback segment created.

SQL> alter rollback segment r01 online;

Rollback segment altered.

SQL> create undo tablespace undotbs01 datafile 'C:\oracle\product\10.2.0\oradata

\ORCL\undotbs001.dbf' size 100m;

Tablespace created.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

SQL> alter system set undo_tablespace=undotbs01 scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             100666244 bytes

Database Buffers          503316480 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL>

ANY QUERIES ASKTAJ AT ASKDBATAJ

ORA-600 [4194] "Undo Record Number Mismatch While Adding Undo Record"

My Signature Article

Today i come across with the following ORA-00600 error on our DEVELOPMENT instance.

As we all knows ORA-00600 = Oracle BUG

but lets find out more about this particular error

ERROR:              
  ORA-600 [4194] [a] [b]
 
VERSIONS:           
10.2.0.1.0

DESCRIPTION:

  A mismatch has been detected between Redo records and rollback (Undo) 
  records.

  We are validating the Undo record number relating to the change being 
  applied against the maximum undo record number recorded in the undo block.

  This error is reported when the validation fails.

ARGUMENTS:
  Arg [a] Maximum Undo record number in Undo block
  Arg [b] Undo record number from Redo block

FUNCTIONALITY:      
  Kernel Transaction Undo called from Cache layer
 
IMPACT:             
  PROCESS FAILURE
  POSSIBLE ROLLBACK SEGMENT CORRUPTION

SUGGESTIONS:

  This error may indicate a rollback segment corruption.

  This may require a recovery from a database backup depending on 
  the situation.

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle
  Support Services for further analysis.

I raised TAR with Oracle Support and they are working on it.

Oracle Metalink Support

How to Apply Application Patch - Oracle Application

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Patching is the part of Oracle Application DBA duties.  And this task we have to perform on frequently basis.

I will try to cover all the major and simple step for application patch procedure.

Application: R12

OS: Linux 32 bit.

--- This article will cover only APPLICATION PATCH ---

Eg: There is a bug in the Receivable Module, Oracle support provide patch to apply"  

Step:

1. Don't shutdown the services of APPLICATION & DATABASE Tier

2. Enable application maintenance mode

3. Download patch from support.oracle.com and unzip on application tier

4. run adpatch utility and apply the needful patch

5. review the patchlogfile for any kind of error message 

6. Disable application maintenance mode

For any doubt asktaj

Details:

1. self explanatory 

2. login with application user on application tier and run adadmin utility and follow the instruction

3. self explanatory

4. with application user on application tier run adpatch utility and follow the instruction

NOTE: application must be in maintenance mode otherwise you will receive the error.

5. must review the patch logfile (location of the patchlogfile you can find end of the patch in last bottom lines)

6. login with application user on application tier run adadmin utility and disable the maintenance mode.

NOTE: application must be disable after patch installation otherwise end user will not able to login to application and they will get the warning message something like "Application in maintenance mode contact your system administrator".

For any doubt asktaj

E-Mail Notification Configuration

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Database Version : 10.1.0.2.0
OS Platform : Windows XP sp-2


First Configure SYS AS SYSDBA(super user account)


Database Home Page
---> Administration
--------> Enterprise Manager Administration
------------->ADMINISTRATOR (option)


select SYS user and click on "NOTIFICATION METHODS" and configure SMTP(outgoing mail server)

Note : Add click on "APPLY".

Sign out from SYS user.

and Sign In to NORMAL USER.

If we receive below error because TAJ is not super user privs. we have to first make TAJ as a super user.


Again Sign In to SYS AS SYSDBA user

and on Administrator page click on "CREATE"
select TAJ user and click on finish.

then sign out from SYS user and sign in to TAJ user


Database Home Page
---> Administration
--------> Enterprise Manager Administration
------------->ADMINISTRATION
---------------------> click on PREFERENCES (bottom on current page) (option)


Click on
--->NOTIFICATION
------->Rules

Create New rules for notification

Note: Click on APPLY


Click on
--->NOTIFICATION
------->Schedule

Log Miner Example

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



Minimum Privs Required

DBA Role.

Archive log Mode : ENABLE

If we want to analyze ARCHIVELOG files also with ONLINE REDO LOG FILES

Analyzing RedoLog Files

1. Create New User --- Not require.


SQL> create user mtaj identified by taj;

User created.


Grant DBA role to user


SQL> grant dba to mtaj;

Grant succeeded.


Conn with new user and issue some DDL, DML statement


SQL> conn mtaj/taj
Connected.
SQL> create table taj ( no number, name varchar2(20));

Table created.

SQL> insert into taj values ( 1,'test');

1 row created.

SQL> insert into taj values ( 2,'test2\');

1 row created.

SQL> commit;

Commit complete.


Enable Supplemental Log



SQL> --Enable Supplemental log
SQL> alter database add supplemental log data;

Database altered.


Select Redo log file for analyze



SQL> column member format a50
SQL> select a.member,b.status
2 from v$logfile a , v$log b
3 where a.group# = b.group#;

MEMBER STATUS
-------------------------------------------------- ---------------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG CURRENT
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG ACTIVE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG INACTIVE

Note: here we anaylze current redo log files.

SQL> execute sys.dbms_logmnr.add_logfile(-
> logfilename => 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG',-
> options => sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

YOu can also analyze ARCHIVELOG file.

Query for Most recently archived File

SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2007_06_28\O1_MF_1_

66_386GL2YW_.ARC

Start Logmnr Session

SQL> execute sys.dbms_logmnr.start_logmnr (-
> options => sys.dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

Query in V$LOGMNR_CONTENTS view for REDO,UNDO data

SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name = 'TEST';

no rows selected

End Logmnr session

SQL> execute sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Load Profile

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Load Profile


1. Redo size: The amount of redo generated during this report.

2. Logical Reads: Calculated as (Consistent Gets XE "Consistent Gets" + DB Block Gets XE "DB Block Gets" = Logical Reads).
Block changes: The number of blocks modified during the sample interval.
Physical Reads: The number of requests for a block that caused a physical I/O operation.

3. Physical Writes: Number of physical writes performed.
User Calls: Number of user queries generated.

4. Parses: The total of all parses; both hard and soft.

5. Hard Parses: The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.

6. Soft Parses: Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes far fewer resources.

7. Sorts, Logons, Executes and Transactions: All self-explanatory.


The following information is also available in the workload section:




1. % Blocks changed per Read:

The % Blocks changed per Read statistic indicates all blocks are retrieved for update.
Blocks Changed per Read % = (Block Changes + 100/ Logical Reads)

2. Recursive Call %:

Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.

3. Rollback per transaction %:

4. Rows per Sort

Redo Log Recovery

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips


Oracle Version: 10.1.0.5.0
OS: Windows
Archivelog ENABLE
Backup Method RMAN

The method of recovery from loss of all members of an online log group depends on a number of factors, such as:


1. The state of the database (open, crashed, closed consistently, and so on)



If you lose the current group, and the database is not closed consistently (either it is open, or it has crashed), then you will have to restore an old backup and perform point-in-time recovery, followed by OPEN RESETLOGS. You will lose all transactions that were in the lost log. You should take a new full database backup immediately after the OPEN RESETLOGS. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.



Error found in ALERT.LOG file

Mon Aug 13 16:45:58 2007
Errors in file c:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_4044.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


First check LOST redo group is current or non-current.


SQL> select group#,status from v$Log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT ----> lost group


Note: Lost group is related to current.


Logon to RMAN

RMAN>run
{
restore database;
recover database;
}



Log on to sqlplus prompt

SQL>recover database until cancel;
cancel;

SQL>alter database open resetlogs;

2. whether the lost redo log group was current



If you lose the current redo log group, and if the database is closed consistently, then you can perform OPEN RESETLOGS with no transaction loss. However, you should take a new full database backup. Backups from before the OPEN RESETLOGS will not be recoverable because of the lost log.



3. Whether the lost redo log group was archived



If you lose a noncurrent redo log group, then you can use the ALTER DATABASE CLEAR LOGFILE statement to re-create all members in the group. No transactions are lost. If the lost redo log group was archived before it was lost, then nothing further is required. Otherwise, you should immediately take a new full backup of your database. Backups from before the log was lost will not be recoverable because of the lost log.


Below Error message found in ALERT.LOG file and database is stop(SHUTDOWN ABORT) ...


Mon Aug 13 12:50:50 2007
Errors in file c:\oracle\product\10.1.0\admin\orcl\bdump\orcl_lgwr_3904.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


How to recover redo log files.

Note: We have last valid backup and all archivelog files.

Logon to SQL prompt with SYS user


SQL>conn / as sysdba
connected to idle instance

SQL>startup mount


SQL>--First check which REDO group is lost.


SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INVALIDATED
2 UNUSED------>lost group
3 INACTIVE

SQL> --Note current REDOGRP is 1 and lost REDOLOG GRP 2.
Note: NON-Current group is LOST.


SQL> --Note current REDOGRP is 1 and lost REDOLOG GRP 2.


SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database open;

Database altered.



Logon to RMAN


RMAN> run
2> {
3> restore database;
4> recover database;
5> }

Tips: Improve performance with Oracle Streams

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



Tips: Improve performance with Oracle Streams
Source Database
1. set capture process parallelism
SQL> exec dbms_capture_adm.set_parameter
(capture_name => ‘name’,parameter => ‘parallelism’,value => 16);
NOTE: Default value is 1.

Target Database
2. set apply process parallelism
SQL> exec dbms_apply_adm.set_parameter
(apply_name => ‘name’,parameter => ‘parallelism’,value => 16);

3. Increase SHARED_POOL_SIZE (prior 10g) & STREAM_POOL_SIZE (10g later)
4. Increase the SDU (Session data unit) in a WAN (wide area network)
5. Implement “heartbeat” table for monitor changes on target database
6. Increase “log_archive_max_processes” parameter
5. Use Frequent Commit Interval in Source database.

Oracle DBA responsibilities

Oracle DBA
Is a person who highly respectable and knowledgable in Oracle Technology.
Below same are the responsibities for Oracle DBA
Oracle DBA responsibilities
1. Installation of the Oracle Database on various OS with different hardware platforms.
Mostly used OS are Windows, Linux/Unix/Solaris
Mostly Used hardware are HP/IBM
2. Create Oracle database according of the requiements through Manually & DBCA tools

continue...

Best Practices: Oracle 9i User Managed Backup & Recovery

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips




In this article i will descripe some good scnerios of backup and recovery option with Oracle 9i.

Backup and Recovery is a primary work for Oracle DBA. so every DBA must know every possible way to recover and backup their database.

There is mainly two option for taking HOT backup of Oracle Database.
1. RMAN
2. User Managed

NOTE: Both method required ARCHIVELOG mode.

1. How to enable Archivelog Mode

2. What is HOT backup?

In this article I will show you following point regarding backup and recovery in practical view.

1. How to take Online User Managed backup in Oracle 9ir1

After taking Proper database backup it is always recommended backup testing, becuase if backup is not valid then it is useless.

Through below two method we can use for TEST database backup which taken by User Managed.

1. Best Practices is to restore and recover backup on different host

2. Using DBVERFIY utility check physical corruption of backup

Creating a Standby Database with RMAN

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Database : 10.2.0.1.0
OS Linux
Archive log : ENABLE

Primary Database OR Standby Database on SOME HOST

Step :
1. Create Recoverable backup of PRIMARY DATABASE

[oracle@alwarid-taj ~]$ rman target=sys


Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 5 20:30:02 2007


Copyright (c) 1982, 2005, Oracle. All rights reserved.


target database Password:
connected to target database: ORACLE (DBID=1488579947)


RMAN> configure controlfile autobackup on;


using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


RMAN> run
2> {
3> backup database;
4> sql 'alter system switch logfile';
5> backup database archivelog all;
6> }


[output cut]

2.Creating the Standby Control File with RMAN

1.connect to primary database.
[oracle@alwarid-taj ~]$ export ORACLE_SID=oracle
[oracle@alwarid-taj ~]$ rman target=sys

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 5 20:39:40 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:
connected to target database: ORACLE (DBID=1488579947)

RMAN> startup mount;

database is already started

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 281018368 bytes

Fixed Size 1218944 bytes
Variable Size 83887744 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Starting backup at 05-APR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 05-APR-07
channel ORA_DISK_1: finished piece 1 at 05-APR-07
piece handle=/home/oracle/oracle/ORACLE/backupset/2007_04_05/o1_mf_ncnnf_TAG20070405T204434_31b9vlws_.bkp tag=TAG20070405T204434 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-APR-07

Starting Control File and SPFILE Autobackup at 05-APR-07
piece handle=/home/oracle/oracle/ORACLE/autobackup/2007_04_05/o1_mf_s_619130563_31b9vnj3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-APR-07

RMAN> run
2> {
3> sql 'alter database open';
4> sql 'alter system archive log current';
5> }

sql statement: alter database open

sql statement: alter system archive log current

ORA-27001 on Database Startup, Machines Resources are Fine

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Database: 10.1.0.5.0 / Win2003Sp1 standard edition

We have total 7 gb ram on server , but PAE (physical address extention) is not enabled so i can't increase our SGA memory size more than ONE gb. when ever i try to increase i am getting "O/S Error: (OS 8) Not enough storage is available to process this command "

So I followed the below suggested solution and our db got up :)

Metalink Note:
Doc ID:
208285.1

fact: Oracle Server - Enterprise Edition
fact: MS Windows Platform
symptom: Database startup fails
symptom: ORA-27100: shared memory realm already exists
symptom: DB_BLOCK_BUFFERS parameter is not set too high for the machine resources


cause:

Operating system has a duplicate Oracle memory segment still in memory.

fix:

Stop and start the database service. If this does not help, then a re-boot of the server will flush the memory

Check this metalink note also:
Doc ID:
370469.1

How to enable PAE on windows platform

Windows 2003 service pack1 standard edition

step:

1. open boot.ini file ( location: c:\ drive (it is hidden system file)

2. add the "/PAE /3GB" lines as following format

[boot loader]timeout=30default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS[operating systems]multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect /PAE /3GB

3. restart the windows server

4. after restarting you can see the following line in the my computer properties.

Export Failed after upgrade 11.2.0.1 to 11.2.0.2 with 'DMSYS.DBMS_DM_MODEL_EXP'

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

After upgraded 11.2.0.1 to 11.2.0.2 export failed with the following errors

About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_MODEL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_MODEL_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp

Cause:
unpublished Bug 9694190 "11.2.0.1 UPGRADE TO 11.2.0.2 ODM BECOMES INVALID WITH A FEW INVALID PACKAGE BODIES"

Solution:
SQL> CONNECT / AS SYSDBA;
SQL> DROP USER DMSYS CASCASE;
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = 'DMSYS';
SQL> SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';

If the above SQL returns non-zero rows, create and run a SQL script as shown in the following example:

SQL> SET HEAD OFF
SQL> SPOOL /DROP_DMSYS_SYNONYMS.SQL
SQL> SELECT 'Drop public synonym ' || SYNONYM_NAME || ' ;'
FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';
SQL> SPOOL OFF
SQL> @/DROP_DMSYS_SYNONYMS.SQL
SQL> EXIT;

Oracle Application Printer Setup FAQ

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Oracle Application one more cool feature is that direct printing from Oracle Application.


Q1: It is possible to print concurrent request output directly to PRINT?
A1: Yes, it is possible.

Q2: How to SETUP PRINTER CONFIGURATION?
A2: There are few step which needs to follow
1. Desired printer must be configured on ORACLE APPLICATION (CONC MGR) SERVER
eg: we are able to print from OS.

Once we can able to print from OS then login to ORACLE APPLICATION and select "SYSTEM ADMINISTRATOR" responsibility.

continue ...

Oracle Application Cloning 1 2 3 step

Oracle Application Cloning is just about 1 2 3 step

STEP 1
1. run adpreclone.pl script on dbTier/appsTier

location:
Login with db user for dbTier : $ORACLE_HOME/appsutil/scripts/[context-value]/adpreclone.pl
Login with apps user for for appsTier: $INST_TOP/admin/scripts/adpreclone.pl

eg: ./adpreclone.pl dbTier
eg: ./adpreclone.pl appsTier

STEP 2
1. shutdown all application & database tier
2. copy "apps, inst and db folder to clone directory (you need to create clone directory first)

STEP 3
run adcfgclone.pl scripts

location:
Login with db user for dbTier : $ORACLE_HOME/appsutil/clone/bin/adcfgclone.pl
Login with apps user for for appsTier: /apps/apps_st/comn/clone/bin/adcfgclone.pl

eg: ./adcfgclone.pl dbTier
eg: ./adcfgclone.pl appsTier

Get Notified for A failed concurrent request

Oracle Application provides a wonderful options for Database Administrator as well as the person who is responsible for day to day activities related to oracle application.

This is something PRO-ACTIVE kind of feature available in the application.

This feature notify administrator if there is any concurrent request completed with error ( failed) by end user.

Q1: How to setup Notification
A1: go to system administrator responsibility
--- open OAM (Oracle Application Manager)
------ SITE
-----------MONITORING (TAB)
-------------CURRENT ACTIVITY (SECTION)
-----------------SYSTEM ALERTS (OPTION)
----------------------NOTIFICATION SETUP ( AT RIGHT HAND SITE YOU CAN SEE "CREATE" BUTTON")
CLICK ON CREATE BUTTON
--- SELECT SINGLE APPLICATION (FROM SELECTION)
-------------FIND CONCURRENT TYPE AND SELECT CONCURRENT PROGRAM THEN PRESS CONTINUE
-------------------SELECT "CONCURRENT REQUEST NAME"
-----------------------SELECT PERSON TO NOTIFY
-------------------------PRESS SUBMIT


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

Don't Delete OUTLN Schema

Oracle 11g or later.

Installation of Oracle 11g database created few schemas based on the PRODUCT INSTALLED.
like
1. sysman
2. dbsnmp
3. outln

Schemas depands on PRODUCT INSTALLED but beside that there are few SCHEMAS is required by SYS user and MUST NOT BE DELETED
LIKE:
OUTLN Schema

Suppose if you deleted by MISTAKE OUTLN schema then database will not start and startup process ended with following error.

Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-18008: cannot find OUTLN schema
Process ID: 5046
Session ID: 218 Serial number: 5

Solution:
restore last valid backup ( which included OUTLN schema)
Or
Oracle Support

View Concurrent Request output/logfile via browser (outside apps)

There are certain requirement when we need to access concurrent output/logfile via browser outside oracle application.

View Concurrent Request via browser outside Oracle Apps

Oracle Application Purge's Programs

To improve Oracle Application Performance and as a part of maintenance we should regularly run purge program.

The following purge program we can schedule (daily/weekly/monthly) basis.

1) The Purge Concurrent Requests and/or Manager Data concurrent program
2) The Purge Obsolete Workflow Runtime Data concurrent program
3) The Purge Signon Login Data concurrent program
4) Purge Inactive Sessions
5) http://dbataj.blogspot.com/2011/07/housekeeping-of-r12-application.html

Oracle Login Failed Message

In oracle application r12 LOGIN PAGE
when we enter wrong password then we get below message.

Error
Login failed. Please verify your login information or contact the system administrator.

And suppose if we set ""Signon Password Failure Limit "" = 2 , mean after two failed attempt account get locks. but the difficulty with the message.
In the First attempt with wrong password we get above mentioned error msg.
In the Second attempt with wrong password same msg as above
But in the 3rd attempt it should show and say "Account Locked" Or something like.

but that functionality at the moment is not available in the oracle application.

even though we can edit the current display message with the following SQL statement.
SQL> select *
from fnd_new_messages
where message_name = 'FND_APPL_LOGIN_FAILED';

Related Info:
How To Add An Informational Tip Icon Next To An Item On A Framework Page? [ID 973389.1]

How to check Monthly Growth of database (tablespace)

As an DATABASE ADMINISTRATOR we always keep our eyes on FILE SYSTEM FREE SPACE. because we never want database got down because of SPACE ON SERVER.

with the below Statement we can monitor monthly database growth to forecast file system space

SQL Statement:
[code]
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
[/code]
NOTE: USER REQUIRE ACCESS ON SYS.V_$DATAFILE to run the above statement / And this statement only show database (tablespace) growth.

How to force application users to change their password

Some time there is a requirement to force application users to change their password.
for eg: to implement new validation password policy (role).

There is two ways to achieve this
1. through sql script
2. oracle application "expire day" setting at system administration responsibility --> user ---> there is a option to set expiration no of days.

1. through sql script

there is a script called 'AFCPEXPIRE.sql' located under $FND_TOP/patch/115/sql
eg: sqlplus -a APPS/APPS @AFCPEXPIRE.sql
Or
concurrent program called 'CP SQL*Plus Expire FND_USER Passwords'

Local & Global Inventory FAQ's

Q1. What is Inventory?
A1. The inventory is a very important part of the Oracle Universal Installer. This is where OUI keeps all information regarding the products installed on a specific machine.

The inventory in the ORACLE_HOME (Local Inventory)
The central inventory directory outside the ORACLE_HOME (Global Inventory)

Q2. Location of the INVENTORY ?
A2.
Local Inventory located based upon OS platform
WIN: C:\->PROGRAM_FILES->ORACLE->INVENTORY
UNIT:<-ORACLE_BASE->\ORAINVENTORY

NOTE: If ORACLE_BASE is not defined then INVENTORY WILL CREATE THE ORACLE_HOME base directory
for eg: \data1\(ORACLE_BASE) so INVENTORY will create under (\data1\) directory.

Q3. How many inventory create for each ORACLE_HOME
A3. One local inventory for each ORACLE_HOME / and / ONE GLOBAL INVENTORY for EACH MACHINE

How to find Application Languages Installed r12

There are 3 ways to find Installed Application languages on r12.
1. Application

go to System Administrator responsibility > Install > Languages

2. Database
SQL> SELECT LANGUAGE_CODE, INSTALLED_FLAG
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('B','I');

3. AD utility
AD utility will also show base language and other installed languages on application.

Responding Notifications from Hotmail, Yahoo, and OWA Web Mails

Oracle Application has a wonderful features "Email Notification Approval".

As per business logic there are few process require approval from end user and if end user is travelling most of the time then that user require something email notification kind approval, so approval process will not stop and wait for their approval and that user can approve and reject through email from outside.

Responding notification works perfectly with MS-OUTLOOK but there are few other services like YAHOO/HOTMAIL which not support fully and response not generated properly.

To avoid this kind of issue Oracle provides PATCH for R12.1
Patch 8515763

for more info: check metalink note: 1179772.1

Workflow Mailer Response Time Is Slow For Email Notifications

Workflow Mailer Response Time Is Slow For Email Notifications, The Workflow Mailer is sending email notifications very slowly.

Cause:
java.io.InterruptedIOException: timeout in stream read)

it is internal bug as per oracle support.

Solution:
1. Increase the value of FRAMEWORK_URL_TIMEOUT mailer parameter using $FND_TOP/sql/afsvcpup.sql to 120 or 180 secs. By default, it should be 30 secs.

2. Bounce the Workflow Mailer component and the Apache server.

METALINK NOTE ID: 1143166.1

Critical Patch Update July 2011 for e-Business Suite r12

Oracle released CPU ( Critical Patch Update) July -2011 including E-BUSINESS SUITE.

Kindly use the following link to find out more and how to start and apply the patch on e-biz.

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1315202.1

Concurrent Manager output/logfile location FAQ

Concurrent Manager output/logfile FAQ

Q1: Concurrent Manager output/logfile generated location?
A1: The out files are located under $FND_TOP/$APPLOUT, or if $APPLCSF is set,
in $APPLCSF/$APPLOUT.

Q2: output/logfile naming convention?
A2: output/logfile name convention control by APPCPNAM PARAMETER in env file.
eg:
APPCPNAM Variable Assignment (UNIX) Output File Format
APPCPNAM="REQID" o999999.out
APPCPNAM="USER" ORAUSER.out
APPCPNAM="USER.REQID" ORAUSER.999999
APPCPNAM=(unset or unrecognized syntax) o999999.out

Q3: How to change the location /path of output/logfile of concurrent manager?
A3:
1. The Logs and Output are located in $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT. You can change the value of APPLCSF, APPLLOG and APPLOUT from the context file or you can do it from OAM. The $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT directories should exist and be accessible from the concurrent node.

2. Run autoconfig and bounce the concurrent managers for the changes to be implemented.

Q4: Can we change the PREFIX of output/logfile of concurrent manager?
A4: AFAIK, no we can't change. log SR with Oracle Support to confirm.

Workflow Administrator Responsibility Doesnot show ALL notification when it assigned to other users except SYSADMIN

Workflow administrator responsibility doesn't show ALL notification when it assigned to others user except sysadmin

Cause: it is expected behavior.

Solution:

Perform the following :

1. Login as Sysadmin user or as user who has the Workflow Administrator privilege > Navigate to Workflow Administrator Web Application Responsibility > Administrator Workflow > Administration

Workflow Configuration Tab page. Change the Workflow Administrator to '*' (or any specific role as
per the business requirements. The '*' allows all the users to act as Workflow Administrators).

2. In case if you are not able to update the value from the front end, you can use the following
SQL to update workflow administrator from the backend:
NOTE:--Back up tables before updating

SQL> Update WF_RESOURCES
SET TEXT='*'
WHERE NAME='WF_ADMIN_ROLE';

SQL> commit;

Personalizing The E-Business Suite r12 Login Page

1. Main attributes of the login page

In Release 12 the attributes of the login page are controlled thru the Framework personalizations.
To change them you can either:

Enable the Personalize Region links by setting the 'Personalize Self-Service Defn' profile option to 'Yes' at Site level (or at user level for GUEST user) then click on the desired links on the login page

or:

Go to 'Functional Administrator' responsibility => choose 'Personalizations' tab and enter the value
"/oracle/apps/fnd/sso/login/webui" in the 'Document Path' field then click on the Go button.
The document names available are:

/oracle/apps/fnd/sso/login/webui/MainLoginPG
/oracle/apps/fnd/sso/login/webui/loginRG
/oracle/apps/fnd/sso/login/webui/loginRN
/oracle/apps/fnd/sso/login/webui/LangImagesRN

Click on the pencil icon (Personalize Page) for the document you want to change. The 'Choose Personalization Context' will be displayed. Click on the 'Apply' button to display the personalization page. It will show the personalization structure and the item attributes. Select 'Complete View' radio button and click on the 'Expand All' link. At this stage you can choose the desired item attribute. Click on the pencil icon to edit.

a. Login zone
(document name: /oracle/apps/fnd/sso/login/webui/loginRN)

The following table lists the attributes that you can change:


Attribute
Image: (corporateBrandingImage)
Image: people image
Image: globalTop image
Image: global image
Message Text Input: User Name
Message Text Input: Password
Submit Button: Submit
Submit Button: Cancel
Message Styled Text: Forgot your password
Message Styled Text: Register Here
Message Choice: Accessibility

You can edit the properties of an attribute. For instance, if you don't want an attribute to be displayed, set the property 'Rendered' to false (e.g. Cancel button or Accessibility field).


b. Language zone
(document name: /oracle/apps/fnd/sso/login/webui/LangImagesRN)

For instance if you have one language and don't want to display this zone, then change the property 'Rendered' to false for the 'Table Layout'.


2. Message texts

A message text is used for certain attributes. The "Tip Message Name" property allows you to change
the message name and use a customized message. Please note that is considered as customization and
may be reverted by patches delivering new messages.

To change the value of a standard message:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name (e.g. "FND_SSO_FORGOT_PASSWORD") then enter your message text in the "Current Message Text" field
4. Save changes and exit
5. Clear cache and bounce Apache to see the change


Below are the messages, with default value, used in the login page:

Message Name Value
FND_SSO_HINT_USERNAME (example: michael.james.smith)
FND_SSO_HINT_PASSWORD (example: 4u99v23)
FND_SSO_FORGOT_PASSWORD Login Assistance
FND_SSO_REGISTER_HERE Register here



3. Corporate branding logo

The profile option "Corporate Branding Image for Oracle Applications" controls the
corporate branding image "ORACLE" displayed at the top of all OA Framework pages.
If no value is set for this profile, OA Framework renders the corporate branding image by using the $OA_MEDIA/FNDSSCORP.gif file.

If you change the value of this profile with the name of your custom image you will see your logo in all pages except in the login page because the "FNDSSCORP.gif " is hard coded in MainLoginPG.xml.

Hence if you want to change the corporate branding image with your own logo and thus in all pages including the login page the simplest solution is to:

- rename the FNDSSCORP.gif file for backup
- edit the FNDSSCORP.gif with your preferred graphic software
- log out and log into the Applications
- you should see the new logo at the top of all the page (including the login page)

In case you want a different logo for the login page then the only (non supported) solution consists in editing the $FND_TOP/mds/sso/login/webui/MainLoginPG.xml file,

from:

[oa:image id="corporateBrandingImage" source="/OA_MEDIA/FNDSSCORP.gif" /]

to:

[oa:image id="corporateBrandingImage" source="/OA_MEDIA/" />]

Of course you will need to import the customized MainLoginPG.xml into the MDS repository
using the XMLImporter tool (please review Note 344204.1 for the exact command line).


4. Footer

Different elements are displayed at the bottom of the page:

About this page

- go to System Administrator responsibility => Profile => System
- change the value of 'FND: Diagnostics' profile option to "Yes" to display this link


Privacy Statement and Copyright

- login to the Applications
- click on the link "Personalize Page" at the top of the Oracle Applications Home Page
- click on "Choose Context" button
- select "OA Footer" for the scope, click on "Apply" button
(the document name should be/oracle/apps/fnd/framework/webui/OAFooter)
- in the this page "Personalize Region: Standard Footer" you can change the item Privacy (Destination URI and Text properties) and the item Copyright (Prompt property)

Note that these changes will apply on all pages.

Oracle e-biz Password Policy Security

Oracle application provides Password Policy Security Solution:

There are few PROFILE which control the password security for oracle application


This profile option you can set under "system administrator" responsibility at SITE / RESPON / USER Level.


Signon Password Failure Limit
The Signon Password Failure Limit profile option determines the maximum number of
log in attempts before the user's account is disabled.
Users cannot see or update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_FAILURE_LIMIT.

Signon Password Hard to Guess
The Signon Password Hard to Guess profile option sets rules for choosing passwords
to ensure that they will be "hard to guess." A password is considered hard-to-guess
if it follows these rules:
- The password contains at least one letter and at least one number.
- The password does not contain the user name.
- The password does not contain repeating characters.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_HARD_TO_GUESS.

Signon Password Length
Signon Password Length sets the minimum length of an Applications signon password.
If no value is entered the minimum length defaults to 5.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_LENGTH.

Signon Password No Reuse
This profile option specifies the number of days that a user must wait before being
allowed to reuse a password.
Users can see but not update this profile option.
The internal name for this profile option is SIGNON_PASSWORD_NO_REUSE.


Signon Password Case

There are two settings: 'Sensitive' and 'Insensitive'.
The default is 'Insensitive'.
Setting this profile option to 'Sensitive' will make the password case sensitive.
'Mixed' is no longer supported.

Find more in Oracle System administrator guide - Security
http://download.oracle.com/docs/cd/B34956_01/current/html/docset.html

Display Meaningful Message When Exceeding Signon Password Failure Limit

Requirement:
While login to oracle application want to display msg when signon password failure limit

Solution:
This kind of feature is not available at the moment in the oracle application ebiz

You May Not Reassign Notification Because Reassign Has Been Disabled

One of my friend getting error while reassign notification mailer to another user.

"You May Not Reassign Notification Because Reassign Has Been Disabled"

Oracle : 12.1.2

Cause: it is due to a BUG.

Solution: Apply the password protect patch #11672027

Metalink ID: ID 1323579.1]

Canceling Refund Payment Request Does Not Unapply The Refund Application In AR

Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.0 - Release: 12.0 to 12.0
Information in this document applies to any platform.
Symptoms

AP Periods : JAN-10, FEB-10 open.
AR Periods : JAN-10 open. FEB-10 never open.

1. Created a receipt for amount 100 and dated '30-JAN-2010'.
2. Created a refund against the receipt with Apply date '30-JAN-2010'.
3. AP invoice is created for the refund with date '30-JAN-2010'.
4. Canceled the invoice on date '02-FEB-2010' in AP.
5. Queried the receipt in AR. The refund application line is not unapplied and the receipt still shows as applied.

Cause

Bug 9226008

++ 1) Bug Summary
++ A. Functional
++ 1. Description
-> Refund is not being unapplied in Receivables (when AR
-> Period is closed) when
-> the corresponding AP Invoice is canceled in Payables.

fixed on ARXGREFB.pls 120.0.12000000.2
Solution

Code Fix
=======
Please apply one-off patch <<9226008:R12.AR.B>> to have the code fix.

Data Fix
=======
Please get reverse_receipt_refund_app.sql from Oracle Support.

Metalink Note: [ID 1066223.1]

11gr2 Password Expired

in the 11gr1 and later we found new implemented password feature.

while login to database i am getting below error message


ORA-28002: the password will expire within 6 days
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Cause:
his behavior was caused because the PeopleSoft 'ConnectId' account and the 'Sysadm' account as defined in the DBA_USERS table had expired.

This happened because of a change implemented in Oracle 11g databases. In releases prior to 11g, those accounts would never expire. In 11g, they are hardcoded with an expiry date of 180 days.

Solution:
To implement the proposed solution:

1. Logon to the product database as the SYSTEM user (not the application administration user).

2. Find the profile.

SQL> select account_status, profile from dba_users where username='the PeopleSoft ConnectID';

SQL> select account_status, profile from dba_users where username='sysadm';

3. Alter the profiles with the following SQL:

SQL>ALTER PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;

4. Connect to the database via SqlPlus and change the schema password with the command

SQL>ALTER USER IDENTIFIED BY ;

SQL>ALTER USER SYSADM IDENTIFIED BY ;

Config: "Java.Lang.Exception: Exception In Sending Request :: Null"

dbconsole configuration ended with the following error


and while opening oracle enterpirse manager console
java.lang.Exception: Exception in sending Request :: null


in the /db/app/oracle/Db_1/spiora.sterling.local_orcl/sysman/log/emoms.trc

2011-07-19 14:15:06,030 [EMUI_14_15_06_/console/database/instance/sitemap] ERROR perf.sitemap initializeGeneral.400 - Failed to initialize object: ORA-20206:
ORA-06512: at "SYSMAN.EMD_DATABASE", line 1165
ORA-01403: no data found
ORA-06512: at line 1


solution:
+ Stop the dbconsole
+ Relink the agent component manually using this procedure :

1. Make a copy of $ORACLE_HOME/sysman/lib/env_sysman.mk file.

2. Edit the script $ORACLE_HOME/sysman/lib/env_sysman.mk :

- search for the section having "MK_EMDW_LIBNMEMSO_SHLIB"

- replace the line

$(LIBNMEM) $(LIBNMER) \

with

$(LIBNMEM) $(LIBNMER) $(LIBNMO) \

3. Perform the following relink:


cd $ORACLE_HOME/sysman/lib
make -f ins_sysman.mk agent
This will not affect the other database components as only the agent is being relinked.

Metalink Note

Dear All,

Below is the list of all frequently required metalink notes

1. Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12 [ID 1074326.1]

2. Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12 [ID 373634.1]

3. Is There a Software Download For the Full Discoverer Administrator/Desktop 10g
(10.1.2.48.18) or 11g (11.1.1.3.0) Release? [ID 395820.1]

4. How to Install Oracle Discoverer Administrator / Discoverer Desktop 10g (10.1.2) on Windows Vista [ID 564739.1]

5. How To Send An Email In A Simple Periodic Or Event Alert? [ID 1162153.1]

6. Using Oracle Alert with Workflow Mailer [ID 395128.1]

7. Oracle Alert Does Not Sent Any Emails After Applying Patch 4676589 11i.ATG_PF.H Rollup 4 [ID 402361.1]

8. Should Emails Sent Via Oracle Alerts Have A Workflow Notification In The Worklist? [ID 435281.1]

9. Changes Introduced in Alerts Post RUP4 and its Impact [ID 422782.1]

10. Migration of Oracle Database Instances Across OS Platforms [ID 733205.1]

11. Alert Log Messages: Private Strand Flush Not Complete [ID 372557.1]

Oracle – SQLDeveloper – Unable to locate component – The application has failed to start because MSVCR71.dll was not found.

Oracle – SQLDeveloper – Unable to locate component – The application has failed to start because MSVCR71.dll was not found.

Oracle – SQLDeveloper – Unable to locate component – The application has failed to start because MSVCR71.dll was not found.

Visited http://www.dll-files.com/pop.php?dll=msvcr71 and downloaded msvcr71.dll. Once downloaded, copied the file to c:\windows\system32 folder

Error REP-0069 and REP-50002 while Generating Reports through Concurrent Managers

Applies to:

Oracle Applications Technology Stack - Version: 12.0.2 and later [Release: 12.0 and later ]
Oracle Application Object Library - Version: 12.0.2 and later [Release: 12 and later]
Information in this document applies to any platform.
Symptoms

While generating any report through the concurrent managers, the following error occurs in the concurrent request log file :

FNDSCURS module: Active Users
-----------------------------
Enter Password:
REP-0069: Internal error
REP-50002: Server is shutting down
Program exited with status 1
Changes

The issue may be reproduced by manually run the reports from unix:

sh /oracle/PROD/inst/apps/PROD_appdemo4/ora/10.1.2/bin/appsrwrun.sh \
mode=character\report=/oracle/PROD/apps/apps_st/appl/fnd/12.0.0/reports/US/FNDSCURS.rdf\
batch=yes \
destype=file \
desname=/oracle/PROD/inst/apps/PROD_appdemo4/logs/appl/conc/out/test1.out \
desformat=/oracle/PROD/apps/apps_st/appl/fnd/12.0.0/reports/HPL \
pagesize=132x66


Report Builder: Release 10.1.2.0.2 - Production on Fri Jan 4 10:56:53 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

REP-0069: Internal error
REP-50002: Server is shutting down"

Cause

The cause of this problem has been identified in Bug 6371924.

It is caused by rwrun/rwbuilder. The engine is started in a separate thread and not spawned as a separate process.

The fix was to do the ordering proper using synchronizing objects.

Solution

The fix for the Bug 6371924 has been included in the one-off Patch 6116405.

To implement the solution, please execute the following steps:

1) Please download and review the readme for Patch 6116405.

2) Please apply Patch Patch 6116405 in a test environment.

3) Please retest the issue.

4) If the issue is resolved, please migrate the solution as appropriate to other environments.



Note :

Patch 6116405 is for Reports 10.1.2.0.2, 10.1.2.2.0.

For Reports 10.1.2.3, please apply Patch 7140392.

R12 REP-0069: Internal error REP-50002: Server is shutting down

We are getting R12 application custom concurrent report while running:

REP-0069: Internal error
REP-50002: Server is shutting down








=== Issue Clarification ===

In eBusiness Suite 12.0.6, a custom report based on a concurrent program has failed with the following errors :
REP-0069: Internal error
REP-50002: Server is shutting down

Report Builder: Release 10.1.2.2.0

=== Cause Justification ===

We can only provide specific cause & solution for reports supplied by Oracle, e.g. there is a known issue with a seeded Aged Buckets report in Receivables (Note 1321276.1), or issues relating to Reports Developer, such as Bug 12422704 (neither of which are relevant here).


=== Issue Verification ===

Verified the issue in the log file as noted below:

LOG FILE
-----------------------
Filename = Service_Charges_SoA_060711.txt
See the following error:
Enter Password:
REP-0069: Internal error
REP-50002: Server is shutting down

=== Cause Determination ===

There are a number of possible reasons why a custom report might produce the REP-0069: Internal error. They are usually data related. For example:
1. One or more tables on which report is based contains obsolete data.
2. One or more tables on which report is based contains missing data which the report requires.
3. The processing is too intensive for the CPU and/or JVM used by the Report Engine. For example complex pagination with a large amount of data.
4. Incorrect password not handled correctly by the report.

This list is not exhaustive. Other potential causes may be identified by searching our Knowledge Base in My Oracle Support for "REP-0069: Internal error".

=== Proposed Solutions ===

Ensure that :
- The report has the resources it needs (e.g. memory, CPU, JVM max heap size) in order to process the maximum amount of data it could fetch
- None of the data required by the report is obsolete or missing, and
- The report traps incorrect passwords and returns a controlled error message to the user, if it asks the user to enter a password (as appears to be the case here).

Product Support does not provide specific advice on custom report design. There are 3 options for help with custom report design, which I would try in the following order: (1) Search our Knowledge Base in My Oracle Support for "REP-0069: Internal error". (2) Discuss your report design at https://communities.oracle.com . (3) Engage an on site consultant from Advanced Customer Services.

=== Proposed Solutions Justification ===

1. For example Note 1312609.1 - Payroll Activity Report Error REP-0069.
2. For example Note 731171.1 - R12 Invoice Print - Selected Invoices Error Out With REP-0069: Internal error.
Or Note 1251774.1 - ARZCARPO Automatic Receipts/Remittances Execution Report Completes with "REP-0069: Internal error" and "REP-1401: 'b_12formattrigger'" Errors.
3. We would expect REP-0069 to be accompanied by a Java exception error if the cause is related to Java, e.g. JVM maximum heap size is too low. See Note 859067.1 - Rep-0069: Internal Error java.lang.OutOfMemoryError Running Reports with "Page X of Y" Feature.
4. In the log file, the error is preceded by "Enter Password", so this would seem to be a likely solutoin in this case. See for example Note 1270758.1 - REP-0069 / REP-58101 "Error Stopping Internal Web Server" When Exiting Reports Builder.

R12 Get APP-FND-01564 ORA-01116 ORA-01110 ORA-27041 Errors When Running Concurrent Requests

R12 Get APP-FND-01564 ORA-01116 ORA-01110 ORA-27041 Errors When Running Concurrent Requests

APP-FND-01564: ORACLE error -1116 in SUBMIT: others

Cause: SUBMIT: others failed due to ORA-01116: error in opening database file {number}
ORA-01110: data file {number}: '/{directory path}/{file}.dbf'
ORA-27041: unable to open file
Linux Error: 24: Too many open files
Additional information: 3.

The SQL statement being executed at the time of the error was:
&SQLSTMT and was executed from the file &ERRFILE.


Cause

The error would appear if the output of the lsof command is greater than the value of the ulimit.

Use the following command to check the number of open files used by applmgr:


$ lsof -u applmgr | wc -l

Compare the result to the value in ulimit:

$ ulimit -n

Solution

Modifying the Number of Open File Descriptors

Open the /etc/security/limits.conf file and change the existing values for "hard" and "soft" parameters as follows. Restart the system after making changes.

Note:
If the current value for any parameter is higher than the value listed in this document, then do not change the value of that parameter.

* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

Note:
* means all users listed in /etc/passwd would inherit the above values.

Housekeeping of R12 application & Database

Housekeeping of R12 Application & Database

There are many files created on the file system from oracle application & database.

You can normally keep 1 month files and delete the all other unwanted files. ( recommended to take backup before deleting).

1. Database side
connect with database and check location of "bdump/udump/cdump"
show parameter back (bdump)
show parameter core (cdump)
show parameter user ( udump)
There are three folder "bdump/udump/cdump".
In that folder database will create the trace files

2. Application Side
-$INST_TOP/logs/ora/10.1.3/Apache :
access*log
error*log

-$INST_TOP/logs/ora/10.1.2/reports/cache
o*.txt
o*.eps
o*.xml

-$INST_TOP/logs/ora/10.1.2/forms
em_*.rti
frmweb_dump*

-$INST_TOP/logs/ora/appltmp
OF*.t

-$INST_TOP/logs/appl/conc
*.txt
*.mgr
*.reg
*.log

with ROOT user
# /usr/tmp/*.tmp



Beside this there are few purge program available in the system.

How to Run the Purge Concurrent Request and/or Manager Data Program and Which Tables Does it Purge? [ID 154850.1]
How to Optimize the Process of Running Purge Concurrent Request and/or Manager Data (FNDCPPUR) [ID 92333.1]
Purge Concurrent Request and/or Manager Data Slow Performance [ID 789698.1]


&

Purging Strategy for eBusiness Suite 11i [ID 732713.1] <