Re: Reporting database
We create a Reporting database nightly from the hot backups of a Production Financials 11.0.3 database. The Reporting database is on its own server (UNIX). There is a full Oracle and Applications environment on the Reporting server. What I do as part of the hot backup script, is copy over the compressed data files to the Reporting server, uncompress them, copy over any archived redo logs that were generated during the hot backup, and do a "Create Controlfile" setting the database to a different name, and issuing an Alter Database Recover Automatic Using Backup Controlfile; Then Alter Database Recover Logfile using those logs with active transactions. I then Open the database Resetlogs. After the database comes up, a script kicks off to change some of the profile options, change sequence numbers, import certain tables from the previous day's Reporting database, etc. This is a very busy script, as I'm sure you can imagine. If you would like details on exactly how we go about setting up our Reporting environment, please email me off-list and I'll be happy to help. This all has a very minimal impact on Production - we'd have to do the hot backups anyway. Vicki Pierce Database Administration x2401 Kader Ben <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/03/2003 09:24 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc Subject Reporting database Hi Listers, I'm about producing document to my boss about different strategies to build Informational database (reporting database) and ETL. Our production database is 9i supporting Oracle Financials 11i. I'm concerned about the strategies that have a minimum impact on the overload of production database. Could you please give me your advise and experience. Any input well be very appreciated. Have nice day, Kader __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kader Ben INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 8i to 9i import
I just did one of those this weekend (from 8.1.6 to 9.2.0.1). I created an empty 9i database with all the tablespaces before starting the import. The "full=y" errored out , but it did create the users before it died. I had to go back and do schema imports for each user. Vicki Pierce Database Administration x2401 "Ehresmann, David" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/25/2003 02:54 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc Subject 8i to 9i import List, Has anybody seen this behavior or know the work around. I am doing a full import into a 9i (9.2.0.1.0) database from an Oracle 8i (8.1.7.4.0) database. The users are not pre-created and anytime it wants to import a user who owns objects it fails and gives: . importing USER's objects into USER "ALTER SESSION SET CURRENT_SCHEMA= "USER"" IMP-3: ORACLE error 1435 encountered ORA-01435: user does not exist IMP-0: Import terminated unsuccessfully If you stop, manually create the user and assign a default tablespace the import will work until it hits another user who owns objects. I have seen notes on metalink that it might be the user and/or tablespace assignment. Is there a solution for this, besides pre-creating the users in the 9i instance? One note on metalink stated: You will encounter this when using a pre-9.2 dmp file into a 9.2 database. The following should work. 1) import with show=Y log= 2) edit that script and change the tablespaces to an existing tablespace for the create statements 3) pre-create the objects using the above script 4) import with ignore = Y Are you forced to pre-create the users when going from pre-9.2 into 9.2? David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Valid Packages/Procedures/Triggers that are NOT
We had a weird situation yesterday on a database that had been upgraded from 8.1.6 to 9.2.0.1 over the weekend. Suddenly at 10:30 am, after being in production for a day and a half, 93 previously VALID packages, package bodies, triggers and procedures went INVALID. We have not been able to figure out what made them go invalid (they were all in the same application schema). Anyway, we recompiled all objects and all were then marked as VALID. However, some of the code still failed to work; (as an aside, we narrowed it down to those packages that contained ref cursors). Since the packages were apparently VALID, we did not recompile them again until after we bounced the database and generally chased our tails for a couple of hours. In a last-ditch effort, we recompiled all the objects again and the code started working. Do any of you know of a bug in 9i that would cause an INVALID package to be marked VALID? Or, when is a VALID package really INVALID? Thanks for your input. Vicki Pierce Database Administration x2401
RE: Overhead Associated with Signon Audit in Financials 11.0
Thanks for the input, John. I am primarily concerned about I/O overhead, but I guess that depends on the level of auditing selected, number of concurrent users, which apps they are logged into, time of the month, etc.etc... Vicki Pierce Database Administration x2401 John Kanagaraj <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/30/2003 07:24 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc Subject RE: Overhead Associated with Signon Audit in Financials 11.0 For all the non-APPS DBAs out there... Oracle Applications 10.4 onwards (lowest version I have seen) provides for a feature called 'Signon Auditing'. This is NOT Oracle's Auditing (which goes into SYS.AUD$). It is a parameter driven auditing that records all Users that logged in when set to USER, Application Responsibilities that they chose (upon login as well as subsequently switched to) when set to RESPONSIBILITY, in addition to recording the USER level, and the Forms that they chose to run when set to FORMS, in addition to that recorded at RESPONSIBILITY and USER levels. Thus, when set to FORMS, a user login would at best produce a minimum of three rows, etc. These rows are updated when the user logged out, so all sorts of reports about who is/was logged on, forms currently being used, etc. can be determined. In fact, for an Apps DBA to tie back a session to an actual user, at least USER level signon auditing should be turned on. The problem with Apps is that all users would login in the APPS schema using the encrypted password which is obtained using a dummy connection... Forms and further Access is then determined by 'Responsbilities' that are in turn tied to 'Organizations' and 'Datasets'. By default, almost all Applications tables record the last updated user and timestamp, so there is some inbuilt auditing, albeit not a trail. Oracle provides an additional Audit function that performs an audit trail for such datasets, and this can produce significant overhead for data storage. Thus all discussions about SYS.AUD$ are not really relevant in this particular thread, although some good ideas have been aired. Switching on Auditing without understanding what is ultimately required would be very counterproductive, whether this is on an APPS database or not, in any case. [As an aside, most of this is enabled via the AOL - Applications Object Layer (aka FND - Foundation Layer) and is a solid example of providing 'Application' infrastructure. And don't get me started on the Concurrent Processing - that's an excellent one too] I am going to stop now and let Apps gurus such as Andy R, Tanel and Tim G comment. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Mladen Gogala [mailto:[EMAIL PROTECTED] >Sent: Thursday, October 30, 2003 1:39 PM >To: Multiple recipients of list ORACLE-L >Subject: Re: Overhead Associated with Signon Audit in Financials 11.0 > > >It is true, auditing adds significant overhead, but not >session auditing. >Significant overhead is added by DML auditing because you ad >significant >amount of modified blocks to every transaction you audit, you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Overhead Associated with Signon Audit in Financials 11.0
Does anyone have any statistics about overhead associated with using the Signon Audit in an 11.0.3/ 8.1.7.4/8.0.6.3 environment. We are using full installs of AP, GL, FA and CE. Size of the production database is 100G. Can't tell you exactly what we'd be auditing; we are under siege by Internal Audit at the moment - they've raised the "database audit" flag, but have not started dictating what they want audited. I am trying to get some real-world statistics to arm myself with when the day comes . I have heard that the overhead is significant - is this true, in your experience? Vicki Pierce Database Administration x2401
Re: Financials and APPS password
In our shop, the APPS DBA runs traces as APPS. No one outside of the DBA group has the APPS password. We use Discoverer and Noetix Views for those users who have to look into the database outside of the application. Each user logs in as himself; the DBAs have created roles by business unit and the appropriate roles are granted to individual users. They then user Discoverer, ADI or Noetix Views to look at the data. Developers are able to access development using SQL*Plus or Rapid SQL (name your product), logging onto our customization accounts or their individual IDs. They are not allowed to log on to the Oracle application schemas directly, for instance, GL, or APPS or FA; these are password protected. Again, we grant roles to developers and the customization accounts, which give them the proper privileges on the Oracle tables. In QA and Production, the customization accounts are password protected, so the developers have no insert, update, delete capability in those environments. You need to spend some time thinking out a strategy that will allow your developers and end users limited access to the Applications database outside the App itself. Your company will probably have to buy some software, like Noetix Views, or install and user Discoverer. This is not something you can do in a day or two; but in order to protect the integrity of your database you need to have some controls. Vicki Pierce Database Administration x2401 April Wells <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/15/2003 08:39 AM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc Subject Financials and APPS password Okay, anyone using Financials... E-Business suite... Oracle 11i... whatever you want to call it... I am trying to apply SOME kind of security to my databases. It appears that it is critical for everyone to be able to access production using the APPS id Finance and accounting people, developers, everyone. What does everyone else do in their setups? The newest reason is the need to run the new Mass Additions Trace which apparently requires that you use the apps id. We have found a way to set up any user with a read only version of what APPS has (since they have to be able to compile reports in production and access production data live rather than a month old clone), but Oracle says that you need to run Mass Additions Trace as apps. Does anyone let the entire company have the production apps user's password? April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ >\< \ >\< \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment.
Re: Tools to Execute Stored Procedures in Debug Mode !
Try PL/SQL Developer from Allround Automations. The cost is modest and the tool was developed just for Oracle. Vicki Pierce Database Administration x2401 "Rama, Shreekantha (K.)" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/13/2003 01:19 PM Please respond to [EMAIL PROTECTED] To Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc Subject Tools to Execute Stored Procedures in Debug Mode ! Hi List, I am looking for a tool to debug the sql procedures. I tried with TOAD, but at times this is not working.. Any suggestions on any other tools would be great help.. Shreekanth -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rama, Shreekantha (K.) INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
