Archive for August, 2007

Staging CD/DVD’s for Installation

Posted on August 22, 2007. Filed under: Installation |


There are two ways of staging the CD/DVD’s.

1. Traditional Way

In this way we invoke located at </mountpoint>/Stage11i/startCD/Disk1/rapidwiz, which is part of startCD which invokes below screen wherein we input


a. Staging Directory

b. CD Drive Mounting Command

c. CD Drive Unmounting Command

d. Components to be staged

e. NLS Languages


Stage Screen 1


2. Single Line Command


The other way of staging the CD/DVD’s is to use the same script ( but by providing all the information in one single line command. The options can be viewed by running the command


$ perl -help << this command invokes below screen


Stage Help


Example Commands


To stage only iAS for Language English


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”ias” -dvdnum=1


To stage only APPL_TOP for Language English


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”apps” -dvdnum=1


Now the interesting question is where to find the dvdnum value. In every DVD there is a file called dvd.label, which consists of the dvd information.




[root@appstest oraApps]# cat dvd.label
DVD1 Disk1-Disk6
DVD2 Disk7-Disk8

The disadvantage in using the single line command is we need to enter the same command with change in the value for dvdnum for every individual component to be staged. So for staging APPL_TOP we need to execute above command again with a change in the dvdnum to stage the second DVD ie.,


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”apps” -dvdnum=2

To overcome this we can assign the value as “all” for -component which behaves simillar to running the script in traditional way.




perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”all”


To stage only DB for Language English


[root@appstest oraAppDB]# pwd
[root@appstest oraAppDB]# ls
Disk1 Disk11 Disk13 Disk15 Disk17 Disk19 Disk20 Disk22 Disk24 Disk4 Disk6 Disk8 dvd.label
Disk10 Disk12 Disk14 Disk16 Disk18 Disk2 Disk21 Disk23 Disk3 Disk5 Disk7 Disk9
[root@appstest oraAppDB]# cat dvd.label
DVD1 Disk1-Disk7
DVD2 Disk8-Disk14
DVD3 Disk15-Disk17
DVD4 Disk18-Disk24
[root@appstest oraAppDB]#


As you can see there are 4 DVD’s mentioned, which means we have to execute the command 4 times with different dvdnum value.


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”appdb” -dvdnum=1


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”appdb” -dvdnum=2


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”appdb” -dvdnum=3


perl </mountpoint>/Stage11i/startCD/Disk1/rapidwiz/ -stage=/<staging directory> -mount=”mount -t iso9660 /dev/cdrom /mnt/cdrom” -umount=”umount /mnt/cdrom” -component=”appdb” -dvdnum=4


Issues faced while Staging


Iam not sure if iam the only guy on earth faced this strange issue or you also had encountered this before. Anyways, the startCD got staged without any issues but the remaining DVD’s failed to get staged with the error message in the screenshot.


Staging Issue





The above error was due to the logic built inside the script.


sub stageSingleDVD

if (-f “$MOUNTPOINT/”.$dirsep.”dvd.label”)
$CD_DVD = “DVD”;
print “\nERROR: Invalid DVD is inserted\n”;
print “Please insert the DVD labeled $CD_MAIN_LABEL”.” – “.”$CDLabel{$components[0]} Disk $opt_dvdnum \n”;

stageSingleDVD gets invoked if -dvdnum parameter is used. You must be thinking why on earth i used the command line option instead of going the normal (traditional way), let me tell you, i faced the same problem when i used the traditional way too 😦 .


Before jumping into the problem, a little background on the variables mentioned in the function.


$MOUNTPIONT => stores the mount point value entered by user – (both traditional way or using -mount option)

$CD_DVD => Variable used to determine if CD/DVD is used.

$dirsep => stores the directory separator value ie., /


CD Mounting Command: mount -t iso9660 /dev/cdrom /mnt/cdrom


Hency, my mountpoint value => /mnt/cdrom


Ok coming to the problem, as you can see from the function, it is looking for the file “dvd.label” under $MOUNTPOINT directory which translates to “/mnt/cdrom/dvd.label”.  But every DVD has a base directory viz.,


oraapps01, oraapps02 for APPL_TOP(oraapps)

oraias for iAS

oradb01,oradb02,oradb03,oradb04 for appdb

orardb for DB


So in reality the file dvd.label is available under /mnt/cdrom/oraapps01/dvd.label (for APPL_TOP) but the script is looking for /mnt/cdrom/dvd.label which resulted in error message “ERROR: Invalid DVD is inserted”.




Since the script is part of startCD (which was successfully staged) i edited the script under /<mount point>/Stage11i/startCD/Disk1/rapidwiz as below:


1. Changed occurrences of $MOUNTPOINT variable with $MOUNTPOINT/<base directory> under various functions – 7 changes to be precise.

Note: Variable $MOUNTPOINT is used throughout the script and not all references are changed.


eg: From $MOUNTPOINT to $MOUNTPOINT/oraapps01


No For Error Inside Function Original Changed To
1 Invalid DVD is inserted. stageSingleDVD if (-f “$MOUNTPOINT/”.$dirsep.”dvd.label”) if (-f “$MOUNTPOINT/<base_directory>/”.$dirsep.”dvd.label”)
2 Could not open the dvd.label. No such file or directory getStartDiskNum open ( FH, “$MOUNTPOINT/”.$dirsep.”dvd.label” ) open ( FH, “$MOUNTPOINT/<base_directory>/”.$dirsep.”dvd.label” )
3 disk.label not found under /mnt/cdrom/Disk1 validateDiskLabel if(-f “$MOUNTPOINT/”.$dirsep.”Disk$diskNum”.$dirsep.”disk.label”)
open(FH,”$MOUNTPOINT/”.$dirsep.”Disk$diskNum”.$dirsep.”disk.label”) || die “Could not open disk.label: $!”;
if(-f “$MOUNTPOINT/<base_directory>/”.$dirsep.”Disk$diskNum”.$dirsep.”disk.label”)
open(FH,”$MOUNTPOINT/<base_directory>/”.$dirsep.”Disk$diskNum”.$dirsep.”disk.label”) || die “Could not open disk.label: $!”;
4 Staging /mnt/cdrom/Disk1 … Failed to Copy Disk1, No such file or directory stageSingleDVD #copy the dvd.label
$fc->copy(“$MOUNTPOINT/dvd.label”, $STAGEDIR.”/Stage11i/”.$CDArea{$components[0]} );
 for ($i=$startCDNum; $i <= $endCDNum; $i++)
print2logStdio( “Staging $MOUNTPOINT/”.$dirsep.”Disk$i …”);
$fc->copy(“$MOUNTPOINT/Disk$i”, $STAGEDIR.”/Stage11i/”.$CDArea{$components[0]} ) || die “Failed to copy Disk$i, $!\n”;
#copy the dvd.label
$fc->copy(“$MOUNTPOINT/<base_directory>/dvd.label”, $STAGEDIR.”/Stage11i/”.$CDArea{$components[0]} );
 for ($i=$startCDNum; $i <= $endCDNum; $i++)
print2logStdio( “Staging $MOUNTPOINT/<base_directory>/”.$dirsep.”Disk$i …”);
$fc->copy(“$MOUNTPOINT/<base_directory>/Disk$i”, $STAGEDIR.”/Stage11i/”.$CDArea{$components[0]} ) || die “Failed to copy Disk$i, $!\n”;


The above table can be inferred as For Error in Column 2, Column 4 was changed to Column 5 in Column 3 function.


Guess What, after making above changes Staging the DVD’s was peace of cake. 🙂




Read Full Post | Make a Comment ( 1 so far )

Beware of costly typos “crontab -r”

Posted on August 14, 2007. Filed under: Uncategorized |


Today, i made a costly mistake in my Production definitely worth sharing !!!

On our HP-UX machine, as ‘oracle’ user , instead of issuing ‘crontab -e’,

i mistyped as ‘crontab -r’ and guess what, ALL MY CRON JOBS VANISHED !!!


/home/oracle> crontab -r


/home/oracle> crontab -l



LUCKily i had saved the “crontab -l” listing sometime. So, i edited again

and scheduled everything.  So, beware of such costly typos and do remember

this generally happens when you are in Prod box !!!

Hope this tip was helpful.


Arun (OraclePitStop)

Read Full Post | Make a Comment ( None so far )

ORA-01194: File # needs media recovery to be consistent

Posted on August 13, 2007. Filed under: Database Recovery |

ORA-01194: File 5 needs media recovery to be consistent

When you get the above error if you decide that you have to recover the datafile (or database), think twice. Always you may not have to recover the file. I had faced this scenario in one of the client places. Though the initial plan was to restore the datafile from the backup and recover, I had to change my mind.

Now the scenario…I was informed that a guy at the client site had restarted the database (on windows) and they could not open the database because of the datafile inconsistency. After some searching I could find that the problem was with the backup process!!

It happened so that when the hot backup (user-managed) of the database was being taken, that guy had shutdown the database. Now, while restarting the database a particular datafile was asking for media recovery. When checked I found that the datafile was still in backup mode. i.e. the database was shutdown when the backup was going on. Now, the solution is simple. Mount the database and use  alter database datafile 5 end backup command and open the database. There is absolutely no need to recover the datafile!! 🙂

So what exactly happened here?!? Well, nothing strange happened. It is a normal oracle behavior, nothing else. When the instance was restarted, the datafile which was in the backup mode will look old because the datafile header is freezed with older SCN. So it will (and it should :-)) ask for recovery. That’s it!

Generally on unix servers if you issue normal shutdown commands (except abort!!) on the database when the hot backup is going on, shutdown wont happen. It will throw an error saying that the datafile is in backup mode. So there is very less chance of facing this scenario on unix servers.

But in windows, it is general practice to shutdown the database using the OracleService on the services window. In this case even if the backup is happening database goes down with shutdown abort command internally. You won’t come to know that shutdown abort has happened.

You can even face this scenario when the instance crashes during backup (hot backup).You try to restart the instance without knowing that backup was active during the instance crash and end up with the same error.

So, don’t panic… coz Sometimes somethings goes unnoticed…… Take it easy!!!


OraclePitStop (contribued by Yadu)

Read Full Post | Make a Comment ( 3 so far )

How to recover a table using RMAN backup

Posted on August 13, 2007. Filed under: Database Recovery |

This article is about recovering a particular table (which was dropped or truncated) using RMAN backup. I assume that logical backup is not planned as the size of the database is in Terabytes (TB) and RMAN is used for backing up the database. Here tablespace point in time recovery (TSPITR) does not come into picture because we want to recover a single table and not all the objects in the tablespace.

It is assumed that

 – The target database is on host A and the RMAN full backup was taken before the table TEST which is to be recovered was dropped.

 – The database is to be restored onto host B

 – The directory structure of host B is different to host A

 – The ORACLE_SID will not change for the restored database

 – The backups were carried out to disk

 – TEST table to be recovered is in the tablespace TEST_DATA

The following steps are required:

 – make the backup available to host B

 – make a copy of the init.ora available to host B

 – edit the init.ora to reflect directory structure changes

 – set up a password file for the duplicated database

 – mount the database

 – restore and rename the datafiles

 – recover and open the database

 -export and import the table to the target database

These steps are explained further below.

1.Backup the latest controlfile

sqlplus>alter database backup controlfile to ‘/oracle/control.ctl’;


Note: Backup the archived logs

Move all the archive logs to the Host B from the time backup was taken.


2. List Datafile Locations on Host A

The datafile numbers and location on host A are required. These datafile locations will change on host B

sqlplus> select file#, name from v$datafile;

  file#   name

  —–   ——————————

  1       /oracle/orcl/oradata/system01.dbf

  2       /oracle/ orcl/oradata/users..dbf

  3       /oracle/orcl/oradata/undo01.dbf

  4       /oracle/orcl/oradata/tools01.dbf

  5       /oracle/orcl/oradata/test01.dbf

  6       /oracle/orcl/oradata/test02.dbf

  7       /oracle/orcl/oradata/undo02.dbf

  8       /oracle/orcl/oradata/rcvcat.dbf

3. Make the Backups Available to Host B

During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, this can be accomplished in many ways:

   – set up an NFS directory, mounted on both host A and host B

   – use of symbolic links on host B

4. init.ora on Host B

The “init.ora” needs to be made available on host B. Any location specific

parameters must be ammended. For example,

  – ifile

  – *_dump_dest

  – log_archive_dest*

  – control_files=(control file backup taken on Host A)

5. Setup PASSWORD File

In order to allow RMAN remote connections, a password file must be setup for the duplicated database. For example,

$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

6. Recover the Database

On Host B perform the following steps.

6.1 startup nomount the database

    sqlplus> startup nomount pfile=<location of init.ora>

6.2 mount the database

    sqlplus> alter database mount;

6.3 Rename and restore the datafiles, and perform database recovery


    RMAN can be used to change the location of the datafiles from the location  on host A to the new location on host B. Here rename the datafiles of SYSTEM,UNDOTBS1 and TEST_DATA tablespaces only.

Note: If you have two undo tablespaces in your database and you keep switching between these undo tablespaces it is necessary to restore both the undo tablespaces.

      RMAN> run {

     allocate channel c1 type disk;

     allocate channel c2 type disk;

     allocate channel c3 type disk;

     set newname for datafile 1 to ‘/oracle/datafiles/system01.dbf’;

     set newname for datafile 3 to ‘/oracle/datafiles/undo01.dbf’;

     set newname for datafile 5 to ‘/oracle/datafiles/test01.dbf’;

     set newname for datafile 6 to ‘/oracle/datafiles/test02.dbf’;

     set newname for datafile 7 to ‘/oracle/datafiles/undo02.dbf’;

     restore tablespace SYSTEM;

     restore tablespace UNDOTBS1;

     restore tablespace TEST_DATA;

     switch datafile all;


6.4 Recover and open the database

    Perform incomplete recovery and take the datafiles of the tablespaces other than SYSTEM,UNDOTBS1,TEST_DATA to offline. This makes our restore work easier. i.e. you don’t have to restore the whole database backup. When you issue offline drop command, controlfile assumes that it does not need these files for recovery(so need to restore!!).This is helpful when you have a database of say 1 TB and the tablespace in which the table to be recovered is present is of say 10 GB. By skipping the restoration of other tablespaces you save lot of time and space also.

sqlplus>alter database datafile 2 offline drop;

         alter database datafile 4 offline drop;

         alter database datafile 8 offline drop;


    sqlplus> recover database using backup controlfile until cancel; (or until time)


    Forward the database applying archived redo log files to the point just before the table was dropped and stop the recovery process by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory)



6.5 Rename the logfiles prior to opening the database

    sqlplus> alter database rename file ‘<host A location>’ to ‘<host B location>’;

    sqlplus> alter database open resetlogs;


   Now you can query the table TEST to find out the data.

Once you are confirmed that the table TEST is recovered, export the table and import to the target database.


OraclePitStop (Contributed by Yadu)

Read Full Post | Make a Comment ( 11 so far )

crontab: you are not authorized to use cron. Sorry.

Posted on August 10, 2007. Filed under: DBA/Admin Tips |

One of my applications instance is running on HP-UX 11. I was planning to schedule a hotbackup as a cronjob and realized that “oracle” user did not have proper permissions to set a cron. I got the following error issuing “crontab -e”

 “crontab: you are not authorized to use cron.  Sorry.”

Googled for it and got the following solution:

— Login as root

— Edit the /usr/lib/cron/cron.allow file

— Add “oracle” at the end.

— Logout and login as “oracle” user again.

No need to bounce any cron services for this.

Hope this tip was useful.

Oracle Pitstop team

Read Full Post | Make a Comment ( 5 so far )

More diagnostics tests with latest R12 Diagnostics Pack for Jul’07 (6044942)

Posted on August 3, 2007. Filed under: R12 |

Few days back, i had posted about Diagnostics and R12.
Basically, i had cribbed about the fact that a lot of Diagnostics tests are yet to be ported in R12. Well, the latest Diag patch has the answers. It provides all of them that you get to see in 11i and more.

As usual, make sure that you have IZU installed and registered (via patch 3636980). Apply the baseline patch (4630372) and also the latest Diag patch for Jul’07 (6044942)

Here is the list of applications and registered tests i see after this Diag patch. Thats quite a few, ain’t it?

Application Registered Tests
HTML Platform 9
APAC Consulting Localizations 2
Activity Based Management (Obsolete) 2
Advanced Benefits 2
Advanced Outbound Telephony 2
Advanced Planning Foundation(obsolete) 2
Advanced Pricing 2
Advanced Product Catalog 2
Advanced Supply Chain Planning 3
Alert 2
Application Implementation 2
Application Object Library 52
Application Report Generator 2
Application Utilities 2
Applications BIS 2
Applications DBA 5
Applications Shared Technology 2
Approvals Management 2
Asia/Pacific Localizations 2
Asset Tracking 2
Assets 3
Automotive 2
Automotive Integration Kit (Obsolete) 2
Balanced Scorecard 2
Banking Center (obsolete) 2
Bill Presentment & Payment 2
Billing Connect (obsolete) 2
Bills of Material 2
Budgeting and Planning 2
Build/syncronize Contracts Repository Text index 2
CADView-3D 2
CRM Foundation 41
CRM Gateway for Mobile Devices 2
CRM Self Service Administration 2
Call Center 2
Call Center Connectors 2
Capacity 2
Capital Resource Logistics – Assets 2
Capital Resource Logistics – Financials 2
Capital Resource Logistics – Projects 2
Citizen Interaction Center 2
Clinical Transaction Base 2
Collections 2
Common Modules-AK 2
Communications Intelligence 2
Complex Maintenance Repair and Overhaul 2
Configurator 2
Constraint Based Optimization 2
Content Manager 2
Contract Commitment 2
Contracts Integration 2
Contracts Intelligence 2
Contracts for Procurement (Obsolete) 2
Contracts for Rights (Obsolete) 2
Contracts for Sales (Obsolete) 2
Contracts for Subscriptions (Obsolete) 2
Controlled Availability Product(Obsolete) 2
Customer Care 3
Customer Intelligence (obsolete) 2
Customers Online 2
Data Query 2
DateTrack 2
Demand Planning 2
Depot Repair 2
Development 2
Digital Asset Management 2
Document Managment and Collaboration 3
E-Business Tax 2
E-Records 2
EMEA Consulting Localizations 2
Email Center 8
Engineering 2
Enterprise Asset Management 2
Enterprise Performance Foundation 2
Enterprise Planning and Budgeting 2
Environment Management System 2
European Localizations 2
Exchange 2
FastFormula 2
Federal Financials 2
Field Service 2
Field Service/Laptop 8
Field Service/Palm 9
Financial Aid 2
Financial Analyzer 2
Financial Consolidation Hub 2
Financial Intelligence 2
Financials Common Modules 2
Flow Manufacturing 2
Fulfillment Services (Obsolete) 2
Genealogy Intelligence 2
General Ledger 4
Grants Proposal 2
Healthcare 2
Healthcare Intelligence 2
Healthcare Terminology Server 2
Hosting Manager(Obsolete) 2
Human Resources 7
Human Resources Intelligence 2
IVR Integrator 2
Information Technology Audit 2
Install Base 6
Install Base Intelligence (Obsolete) 2
Interaction Blending 2
Interaction Center Intelligence 2
Interaction Center Technology 2
Internal Controls Manager 2
Internet Procurement Enterprise Connector 2
Inventory Optimization 2
Japan Consulting Localizations 2
LAD Consulting Localizations 2
Labor Distribution 2
Latin America Localizations 2
Learning Management 2
Lease Management 2
Legal Entity Configurator 2
Loans 2
Manufacturing 2
Manufacturing Execution System for Process Manufacturing 2
Manufacturing Mobile Applications 2
Manufacturing Scheduling 2
Marketing 8
Marketing Encyclopedia System 2
Marketing Intelligence 2
Marketing for Communications (Obsolete) 2
Mass Market Receivables for Comms 2
Master Scheduling/MRP 2
Media Interactive 2
Mobile Application Foundation 9
Mobile Applications 2
Mobile Applications for Inventory Management 2
Mobile Quality Applications 2
Network Logistics 2
Network Logistics – Inventory 2
Network Logistics – NATS (obsolete) 2
Network Logistics – Purchasing 2
Number Portability 3
Obsolete Process Operations 2
Operations Intelligence 2
Optimize Contracts Repository Text index 2
Oracle Applications Manager 2
Oracle Clinical Data Repository 2
Oracle Imaging Process Management 2
Oracle Profitability Manager 2
Oracle Sales for Handhelds 2
Oracle Support Diagnostic Tools 2
Oracle Telecommunications Billing Integrator 2
Oracle Web Analytics 2
Order Entry 2
Partner Management 3
Patch Tracking System 2
Payables 4
Payroll 2
Predictive 2
Process Manufacturing Financials 2
Process Manufacturing Intelligence 2
Process Manufacturing Inventory 2
Process Manufacturing Logistics 2
Process Manufacturing Portal 2
Process Manufacturing Process Execution 2
Process Manufacturing Process Planning 2
Process Manufacturing Product Development 2
Process Manufacturing Regulatory Management 2
Process Manufacturing Systems 2
Product Development (obsolete) 2
Product Intelligence 2
Project Contracts 2
Project Intelligence 2
Project Manufacturing 2
Project Portfolio Analysis 2
Property Manager 2
Proposals 2
Provisioning 5
Public Sector Budgeting 2
Public Sector Financials 2
Public Sector HR 2
Public Sector Payroll 2
Public Sector Receivables 2
Purchasing Intelligence 2
Quality 2
Receivables 7
Regional Localizations 2
Regulatory Capital Manager (obsolete) 2
Release Management 2
Release Management Integration Kit (Obsolete) 2
Report Manager 2
Retail Core 2
Revenue Accounting 2
Risk Management 2
Risk Manager 2
Royalty Management 2
SEM Exchange (obsolete) 2
Sales 2
Sales Analysis 2
Sales Analyzer 2
Sales Foundation 2
Sales Intelligence 2
Sales Offline 2
Sales Online 2
Sales for Communications (Obsolete) 2
Scheduler 2
Scripting 2
Service Assurance for Communications 2
Service Contracts 2
Service Intelligence 2
Service for Communications (obsolete) 2
Shipping Execution 2
Shop Floor Management 2
Site Management 2
Sourcing 2
Spares Management 2
Student System 2
Subledger Accounting 2
Supplier Scheduling 2
Supply Chain Intelligence 2
Supply Chain Localizations 2
Supply Chain Trading Connector for RosettaNet 2
Support (obsolete) 2
System Administration 2
Systems Intelligence 2
Team 01 Order Entry Demo 2
TeleBusiness for Telecom/Utilities 2
TeleSales 19
Telephony Manager 2
Time and Labor 2
Time and Labor Engine 2
Trade Management 2
Trade Planning (Obsolete) 2
Transfer Pricing 2
Transportation Execution 2
Transportation Planning 2
Treasury 2
US Federal Human Resources 2
Universal Work Queue 19
University Curriculum 2
Utility Billing 2
Value Based Management 2
Web Applications Desktop Integrator 2
XML Gateway 2
XML Publisher 2
e-Commerce Gateway 2
eCommerce Intelligence 2
iAssets 2
iAuction 2
iClaims (Obsolete) 2
iHCConnect 2
iHCIntegrate 2
iMarketing (Obsolete) 2
iMeeting (obsolete) 2
iRecruitment 2
iSettlement 2
iSupplier Portal 2
iSupport 2
test 2

Looking forward to compile more posts about R12 updates !!


OraclePitStop Team

Read Full Post | Make a Comment ( None so far )


Posted on August 1, 2007. Filed under: Discoverer |

In our Production 11i environment, we have setup database
read-only roles for all the developers that has limited
access to APPS schema objects. This way, only the DBA team
can perform code migrations in PROD since they have the
APPS password.

The read-only role just has “select” on ALL of APPS’s
views and synonyms. To incorporate future views and
synonyms, there is also a trigger created
to grant select on any views/synonyms that gets created
in APPS schema to the read-only role. The trigger
would run a database job to execute this task.

Trigger looks like…

l_str := ‘execute immediate “grant select on ‘ || ora_dict_obj_name || ‘ to apps_ro”;’;
dbms_job.submit(l_job, REPLACE(l_str, ‘”‘, ””));

Given the above scenario, when i was reviewing the alert.log
i found messages like below.

Errors in file < bdump location > /prod_j000_29503.trc
ORA-12012: error on auto execute of job 1688
ORA-00942: table or view does not exist
ORA-06512: at line 1

The trace file didn’t have much info so i looked at
the dba_jobs table. The ‘what’ column had

execute immediate “grant select on EUL_DUMVW_YEEHA to apps_ro”

Initially i ignored it as a one-off incident and removed the
job from the dba_jobs table.

exec dbms_job.remove(job=>1688);

However, the job kept resubmitting itself. So,i had to fix this.
I didn’t have a clue of the object “EUL_DUMVW_YEEHA” other than the
obvious fact that it was related to discoverer. The object as such
does not exist in our APPS schema at all.
I googled for this object and didnt get any links !! Hence this post 🙂

I searched in metalink and realized it is a dummy view created
when using Disco 3i (as in our case) and hence my trigger picks this up
to grant select on my APPS read only role.
For now, i have added a line in the trigger to ignore this object.

If you have more ideas on this object, why it gets created etc, please leave a comment.

OraclePitstop team.

Read Full Post | Make a Comment ( 1 so far )

Liked it here?
Why not try sites on the blogroll...