Re: Table recovery from RMAN backup
You can use export and flashback to recover a dropped table. The only time I needed it, it gave a warning on the export but still worked OK. Simon Anderson SciSys Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/12/2003 17:49 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Table recovery from RMAN backup Hi! I believe flashback query works only on existing tables, you can query deleted data from it for example, but you can't see dropped tables with it. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Create Table..As Select: Number formats
I'm trying to create a table using 'Create Table...As Select...' The contents will then be exchanged into a partitioned table, so they need to have the same names and datatypes. Some of the columns in the created table are populated with zeroes will be updated after the partition exchange, one of the columns is populated with a decode that returns a single digit number. The problem is that I need particular number formats in these columns - the one-digit column should be a number(1,0) and the others should be number(12,4). I can't specify column types in create table...as select, so how else can I force the columns to a particular format? Any ideas much appreciated. Cheers Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Create Table..As Select: Number formats
I'm trying to create a table using 'Create Table...As Select...' ... I don't think that there is any problem here. Specifying the number of digits is largely cosmetic - consider it asa default mask. It doesn't affect how data is stored inside the tables AFAIK. Regards, Stephane Faroult Oriole The problem is when I try to exchange the newly created table into the partitioned table - the designers (in their infinite wisdom) have specified number formats for that table. alter table daily_total exchange partition jun_02 with table dt_temp including indexes gives me the error: * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION Unless I get the column types to match exectly - I can't ask for a change in table design to remove the awkward formatting until I've at least tried to get the format to work in the 'Create Table...As Select..' Cheers Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: RE: Create Table..As Select: Number formats
Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: backwards export
I'm not sure of the export compatibility, but you should know that 9i clients won't talk to a 7.3.4 database, and 7.3.4 clients won't talk to a 9.2 database, which could be a problem. At least, they're not certified to talk to each other, which isn't quite the same thing. Cheers Simon Anderson Jeroen van Sluisdam [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 23/07/2003 12:49 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:backwards export Hi, We are investigating possibilities for migration of an oracle 7.3.4 to oracle 9i. We want to separate our production DB from the app-logic such that we can migrate from oracle 7.3.4 on hp-ux 10.20 to oracle 9i on hp-ux 11. Problem is our development and test-environment because we cannot separate the logic at the same time here also from the db (costs). Does anyone have any experience with exporting from oracle 9i to oracle 7.3.4 ? I found the underlying note on metalink about the same problem but with oracle 8. I have limited experience on oracle8 and none on oracle 9. So I hope you can give some tips. .. You need to run the 7.3 version of export against your 8.0 database via sqlnet/net8. Beforehand, you need to run rdbms/admin/catexp7.sql against your 8.0 database. This creates the 7.3 export views required. Note that if you have used any 8.0 specific features (e.g. objects) these will not be exported for obvious reasons. Tia, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: OUI download
I think it's patch 2878462 for the OUI placeholder for the latest version - Metalink patch search, by patch number. They don't make it easy to find... Cheers Simon Anderson Is there another location for downloads? (I don't think there is...) I am looking for a simple package that installs the Oracle Universal Installer, nothing fancy. begin rant I don't understand why they don't have that under Software Downloads, they have one page that lists all the ODBC drivers, why not do this for the Installer. /end rand Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Restricting access via sqlplus
We used a similar idea on an in-house application - hard-coded the role password into the application. Neither approach is perfect, if someone wanted to try hard enough they could reverse-engineer the app, or try and pick up the password by spying on the sql*net connection. For practical security, you need to back these schemes up with auditing failed logons, audit triggers to record who made changes to data, etc. Simon Anderson Jacques Kilchoer [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/07/2003 01:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Restricting access via sqlplus To prevent these kinds of problems, the only solution I've ever thought was feasible was how we implemented it at a previous company of mine. Our solution: Each user would be granted a role, but the role would not be a default role. The role was protected by a password. The application would connect to the database, read the encrypted password for the role from a database table, use a two-way decryption scheme to decrypt the password, and then use the set role command to give the user the role that would allow insert/update/delete to be done from inside the application. The same could not be accomplished from inside SQL*Plus or Toad because the user would not know the password to use for the set role command. The role password could be changed as often as deemed necessary, except that the new password would have to be stored in the database in an encrypted form. This scheme has an obvious flaw, that anyone able to crack the encryption scheme could decrypt the role password, do a set role and then issue DML commands from inside a SQL client. And it would only work if you are building and deploying your own in-house application. But at the time we could not think of anything better. This was using Oracle 8.0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Partitions of table read only
You're missing something, but I couldn't say what... I tried toying with the same thing, but I'm having a lazy afternoon, so I used Enterprise Manager: Set up a new tablespace, created the table, sounds the same so far... put a few rows into it, again using the nice graphical interface that avoids having to think or know what you're doing ;-) Made the tablespace read only, checked that I couldn't update the table any more. Dropped the table - gone, no complaints from the database. The tablespace map shows the segment that had been being used by the table as a temporary segment. Your suggestion about the metadata makes sense, the bitmap held in the LMT can't be the only information about that segment. When I get my brain back in gear, I'll try and work out where else to look for a better idea of what's happening. Cheers Simon Anderson Rachel Carmichael [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 18/06/2003 18:29 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Partitions of table read only okay, am I missing something? I created an LMT. Created a table in it. Gave no one quota on the tablespace. did (both as system and sysdba) alter tablespace test_drop read only; and hung what did I forget to do? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: AUDITs
It's been a while since I used auditing in anger (And explained to a novice developer that he wasn't as clever as he thought he was...) but my references say it goes something like this under 8i - I'm afraid I don't have a 7.3.4 instance to check the syntax against 'All audit information is stored in the SYS.AUD$ table' 'Oracle retrieves information on what is being audited from three data dictionary views: DBA_PRIV_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS, DBA_OBJ_AUDIT_OPTS' (Oracle SQLPL/SQL Annotated Archives) NOAUDIT will reverse the various audit options (DBA Handbook) So if something like 'audit session by access whenever not successful;' has turned auditing on, you can look at it using 'select * from dba_stmt_audit_opts;' and turn it off with 'noaudit session;' Hope that helps Simon Anderson Solaris V2.6 Oracle V7.3.4.5 For the last hour or more I've tried to RTFM, but I have been unable to find anything which gives me a clue. Below are some AUDIT_ACTIONS their counts from the AUD$ view. 100 LOGON 3279 102 LOGOFF BY CLEANUP 213771 55 SET ROLE634706 103 SESSION REC1417453 101 LOGOFF 6226129 When an AUDIT command is issued which table/view gets updated/inserted? How do I turn off/disable the LOGOFF auditing? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Oracle 9i hanging when linking during install !!!
There's a note on metalink that covers some of the relevant parameters: 68105.1 'Commonly Misconfigured HP-UX Kernel Parameters'. 9i is a lot more resource hungry than 8i, and you need to make sure you have the right HP-UX patchsets. Cheers Simon Anderson Deshpande, Kirti [EMAIL PROTECTED]@fatcity.com on 21/02/2003 13:58:49 Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Review and increase kernel parameters maxtsiz, maxdsiz, maxssiz (maxtsiz_64bit, maxdsiz_64bit, maxssiz_64bit). It is possible that maxssiz (process stack space) is too low, and hence 'Out of Memory'. Refer to HP-UX Oracle Installation Guide for some guidelines to select proper values for these parameters. Kernel rebuilding and rebooting of the server will be needed :( - Kirti -Original Message- Sent: Friday, February 21, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Hi All We are having this major problem when we are trying to install Oracle 9i software on HP-Unix 11. It just hangs when it's linking, please help as we need to upgrade our production databases to 9i. Check here : Installing oracle 920. On linking getting the ff error : Error in invoking ioracle of makefile /$ORACLE_HOME/rdbms/lib/ins_rdbms.mk. I have checked on metalink and seen a suggestion of increasing swap space.My swap space is currently 6Gb. Pls assist. $ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ioracle - Linking Oracle rm -f /opt/oracle/920/rdbms/lib/oracle cc -Wl,+s -Wl,+n +DA2.0W +DS2.0 -o /opt/oracle/920/rdbms/lib/oracle -L/opt/oracle/920/rdbms/lib/ -L/opt/oracle/92 0/lib/ `if /usr/bin/getconf KERNEL_BITS | grep 64 /dev/null ; \ then echo -Wl,+pi 64M -Wl,+pd L -Wl,+padtext 16M -Wl,+paddata 1M ; \ else echo ; fi` -Wl,-PF,linkorderfile -Wl,+Ostaticprediction +O2 +Omultiprocessor +Oentrysched +Onolimit +ESlit + Olibcalls /opt/oracle/920/rdbms/lib/opimai.o /opt/oracle/920/rdbms/lib/ssoraed.o /opt/oracle/920/rdbms/lib/ttcsoi.o /opt/or acle/920/lib/nautab.o /opt/oracle/920/lib/naeet.o /opt/oracle/920/lib/naect.o /opt/oracle/920/lib/naedhs.o /opt/oracle/920/r dbms/lib/config.o -lserver9 -lodm9 -lskgxp9 -lskgxn9 -lclient9 -lvsn9 -lwtcserver9 -lcommon9 -lgeneric9 /opt/oracle/920/ rdbms/lib/defopt.o -lknlopt `if ar tv /opt/oracle/920/rdbms/lib/libknlopt.a | grep xsyeolap.o /dev/null 21 ; then echo -loraolap9 ; fi` -lslax9 -lpls9 -lplp9 -ljox9 -lwwg9 `cat /opt/oracle/920/lib/ldflags`-lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -ln9 -lnl9 -lnro9 -lmm -lnls9 -lcore9 -lxml9 -lunls9 -ltrace9 `if ar tv /opt/oracle/920/rdbms/lib/libknlopt.a | grep kxmnsd.o /dev/null 21 ; then echo ; else echo -lordsdo9; fi` -lctxc9 -lctx9 -lzx9 -lgx9 -lordimt9 -lsnls 9 `cat /opt/oracle/920/lib/sysliblist` -lm `if ar t /opt/oracle/920/lib/libskgxp9.a | grep '^'skcsi.o /dev/null 21 ; then echo /opt/clic/lib/pa20_64/libclic_csi.a;fi` /opt/oracle/920/lib/libocijdbc9.a -lxsd9 -lcres Out of Memory. *** Error exit code 12 Stop. Total VM : 735.2mb Sys Mem : 354.9mb User Mem: 1.99gb Phys Mem: 6.00gb Active VM: 301.1mb Buf Cache: 3.00gb Free Mem: 674.4mb ld: (Warning) Can't open the fdp output file linkorderfile Pid 27501 received a SIGSEGV for stack growth failure. Possible causes: insufficient memory or swap space, or stack size exceeded maxssiz. cc: error 1405: /usr/ccs/bin/ld terminated abnormally with signal 11. *** Error exit code 11 Stop. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Methods to get DDL
Well I'm always happy to plug good books... But I did just that a couple of years ago on a different Oracle mailing list, and suddenly no-one else seemed to be seeing my posts - it made me wary of posting anything that could possibly be interpreted as a commercial plug. I've been using 'Annotated Archives' for years, I'll have to take a look at 'Instant PL/SQL Scripts' as well. Simon Anderson To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: The original is SQL PL/SQL Annotated Archives --- Bob Metelsky [EMAIL PROTECTED] wrote: Simon, thanks for the plug, although it helps if you tell them the name of the book :) The suspense has been killing me since I posted I'm about to go to Amazon.com and search for ddl ... ;-) Wait... Rachel, what is the name of the book? bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Methods to get DDL
There are scripts out there that will do the job, though most of them start to fall down if the indexes are at all unusual. I bought a book a couple of years ago with a whole selection of scripts to create ddl for database objects - It should still be available on Amazon. Cheers Simon Anderson Please respond to [EMAIL PROTECTED] I finally have a bit of time and was wondering what are different methods to get DDL other than Export rows=n Using Quest SQL navigator Using The Databee tool Ideally Id like to get it from a sql plus command but Im pretty sure that's not available. DDL dosnt seem to be available from DBA studio either... Im wondering what methods people are using? I'd like a quick way to get the ddl for say... One index Thanks in advance bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Upgrade from 9.0.1.2 - NOPE!
[EMAIL PROTECTED] on 07/03/2002 04:17:56 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Does anyone have the bug number on this security bug? Thanks, Cherie Machler Oracle DBA Gelco Information Network Connor McDonald To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hamcdc@yahoo. cc: co.uk Subject: RE: Upgrade from 9.0.1.2 - NOPE! Sent by: [EMAIL PROTECTED] om 07/02/02 01:04 PM Please respond to ORACLE-L I'm pretty sure this has not been done... NOBODY(!) should be on 9012 in production - unless you're happy with zero security. As well as the reading-anything as Ian points out, with a little bit of playing around its also possible to destroy any data in the database (since a view is updatable if it is formed correctly) Can't break it, can't break in Hmm :-) hth Connor --- MacGregor, Ian A. [EMAIL PROTECTED] wrote: Did Oracle back port the patch to fix the ANSI-style jonn bug to 9.0.1.2? This bug allows one to read any table in the database despite the permission set. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 02, 2002 8:23 AM To: Multiple recipients of list ORACLE-L Hi, We are currently testing our application on 9.0.1.2 and it looks stable except some bugs with function based indexes. Is it advisable to move to 9.0.1.3 (as it is terminal release of 9.0) or 9.2 which is just released 2 months back (and may have some bugs) OR 9.0.1.2 is stable enough and we can ship our product on this release. Any feedback on 9.0.1.2 is highly appreciated Function based index bugs are not fixes in either of release. Platform are sun 2.8 and WIN2K We have to make decision this week to give QA enough time for testing Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net Remember amateurs built the ark - Professionals built the Titanic __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Upgrade from 9.0.1.2 - NOPE!
Cutting and pasting from an old post: by Jonathan Lewis: The upshot of it seems to be that anyone who can get an sql session can look at any data, and given 'create view' as well can change data at will. You may be able to use the database in production, but only if your users can't access it directly. I've not tested it myself, but it should still be on metalink. _ This just in from comp.databases.oracle.server. See metalink bug 2121935. Using ANSI syntax joins (CROSS JOIN, LEFT OUTER etc) allows you to view data from tables on which you have no privilege. For example, try this COMPLETE script: connect / as sysdba create user us1 identified by us1; grant create session to us1; connect us1/us1 select userid, password from sys.link$ cross join dual ; Worse still, if you have the privilege to create views then this loophole allows you to seek and destroy ANY DATA in the database that you might want to. The bug is fixed in 9iR2. I didn't see any note about a backport, or a security alert on OTN. Conclusion: 9.0.1 should not be in use on production system until Oracle supplies a fix. _ With apologies for the mangled quick quote. Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Oracle 9i, can't break it?
For those of you in the Oracle Partner program, here's what looks like another little bug to add to the 'Unbreakable' list... Log into the Oracle partner network at http://oraclepartnernetwork.oracle.com/ and look for the Product Query Tool under 'Pricing' - It gives the cost of the various Oracle options in various currencies. Choose Enterprise Edition, try to get a price in Italy and it completly fails to cope with the currency conversion, resulting in a price of 0. for a two-year user license, with the same for support and upgrade. I can only assume that nobody in Italy is an Oracle Partner, or they're keeping very quiet about getting the Database for free... Somebody tell me this stuff doesn't run on Oracle software. Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: the ora certified masters cert, yet again
I don't know - took me ages to figure out why 'afiedt.buf' sounded familiar, good thing I'm not job hunting... The 'Worst Oracle Nightmare' and Oracle Likes/Dislikes are sort of on my revision list when I'm jobhunting, even when it's just practice I find it focuses my mind on what I know and what I need to find out (And what I've forgotten) better than the multiple-choice exam questions Oracle offer. Most of us don't give presentations at the various OUG's, etc, but getting a good result at that interview is a good thing to aim for. Simon Anderson .. weaknesses and which way to go. One such was If you find a file named 'afiedt.buf', how was it probably created? That particular question caught two very confident applicants without the foggiest clue a few years ago. .. You GOTTA be kidding right? I consider this rank with the What is dual phenonmom recently.. Well, maybe there's hope for me after all! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: script for comparing two schemas in two databases
The Oracle Enterprise Manager 'Change Manager' does a good job of schema comparisons, and has an easy point-and-click interface that shields you from having to know what you're doing ;-) The downside is that it can be a pain to get oem set up and working. I've never tried it's more advanced functions to propagate changes between databases, but capturing baselines for later comparison is nice. I found the earlier versions buggy, slow and painfull to use, but version 2.2.0 under Oracle 8.1.7 seems to work OK. Hope this helps. Simon Anderson. Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Years ago, I found a script on Metalink that worked decently after a few bug fixes. It was written by Ottar Sorland for use with Oracle 7.3. I still occasionally use it and can send my patched (but still not perfect) version if you need it. Lately, I've been using a TOAD feature (version 7.2 with DBA option) that works much better. I haven't tried the OEM tool. Kevin Kennedy First Point Energy -Original Message- Sent: Wednesday, May 29, 2002 2:18 AM To: Multiple recipients of list ORACLE-L Hi! Does anybody out there have a script that allows me to compare two schemas in two different databases (i.e. development and production db)? This is 8.1.7 on Sun Solaris. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: I have a black hole in my database
You have my sympathy, as I've been in a similar situation after getting a designer install thrown at me. I can only offer general advice, as we hit a series of different bugs that eventually led to abandoning designer, you've probably heard it all from Oracle support already... Make sure you're using the latest patch release of 6i, you didn't specify which one you're on. Half of our problems were in finding the right release to work with our 8.1.7.0 database. It really does need all those separate tablespaces, on separate disks if you've got them (I hadn't appreciated quite how big the product was until it was too late to turn down the job). Keep chasing Oracle support without mercy - don't let them park the call if you can avoid it, keep their clock ticking. I don't know how responsive their support is where you are, but it takes a while to get past the front line in the UK... You could try creating an explain plan of the Select that the view uses, and running the statements themselves without making them into a view might get you something traceable, but it's a long shot. Hope This Helps Simon Anderson. Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Oracle Version 8.1.7.3 64-bit OS Solaris 8 10/1 Server platform Sunfire 38000 Client Platform WIN2k I have somehow created a black hole in my database. While installing designer 6i my process freezes while creating the RM_ELEMENTS view. I do not have any errors in my alert logs. If I create a new SQL*PLUS session it hangs before logging in. If I bring up DBA Studios it hangs. My only recover so far has been to shutdown abort the instance. If I run the 'cdapirvw.sql' script (this creates the views) it goes to end of job successfully. No errors. If I then run something simple like 'select count(*) from rm_elements' the instance hangs. If I attempt to grant rm_elements to a role the instance hangs. I can descibe rm_elements successfully. I can drop it. The create view statement for rm_elements is incredibly long. here is an example CREATE OR REPLACE FORCE VIEW rm_elements AS SELECT sdd_a1.changed_by ,sdd_a1.created_by ,sdd_a1.date_changed ,sdd_a1.date_created ,sdd_a1.irid id ,sdd_a1.notm number_of_times_modified ,sdd_a1.types FROM sdd_a1 sdd_a1 UNION ALL SELECT sdd_a2.changed_by ,sdd_a2.created_by ,sdd_a2.date_changed ,sdd_a2.date_created ,sdd_a2.irid id ,sdd_a2.notm number_of_times_modified ,sdd_a2.types FROM sdd_a10 sdd_a2 UNION ALL SELECT sdd_a3.changed_by ,sdd_a3.created_by ,sdd_a3.date_changed ,sdd_a3.date_created ,sdd_a3.irid id ,sdd_a3.notm number_of_times_modified ,sdd_a3.types FROM sdd_a11 sdd_a3 There are about 30 or 40 unions at least in this view. The command is a couple of 100 lines long. Has anyone had a problem like this or a suggestion I could pursue? I am working with Oracle support but have not had a resolution yet. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: DB Size
A good question for a friday afternoon before a bank-holiday weekend... Query the data dictionary to get the names and locations of the data files: select file_name, tablespace_name, bytes from dba_data_files; That will give you the size of all the files for data, indexes, rollback segments, the temporary area, the system tablespace, etc. There are other bits it won't tell you about (Config files, online redologs, archived logs) but they're another story. This doesn't tell you how much of that space actually has data in it if that's what you meant, just how much space things are taking on the disks. What all these numbers actually mean is a much longer and more difficult question, best left 'till next week. Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Hi all, How could one collect data from an Oracle Server to respond to the question: How big is (what is the size of your) Database ? Thaking you, --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OT: VIRUS!!! VIRUS!!! VIRUS!!!!! BE CAREFUL!!!
Hmm - this seems to be a particularly powerful and dangerous infection, three times more powerful than the 'standard'. Luckily, the 'V-word' virus causes no damage other than slightly clogging up mailboxes. The fact that this example uses the word 3 times would make it particularly bad, but multiple exclamation marks are a dead giveaway, and most of us can delete the message manually. Although this 'V-Word' virus is one of the most common email problems (along with other spam), anti-virus programs *still* refuse to recognise it and delete any message that uses the 'V-Word' with multiple exclamation marks. Don't panic - just follow these simple steps: 1) Trust no-one 2) Prevent this insidious virus from spreading any further by not posting any replies containing the 'V-Word' virus. Simon Anderson (Note to the humour impaired - just ignore me, it's been a slow week ;-) Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) HELP -Original Message- Sent: Tuesday, April 30, 2002 5:48 AM To: Multiple recipients of list ORACLE-L virus info.html Sandeep Kurliye Certified Oracle DBA Almoayyed International Group Almoayyed Computers, PO Box 26259, Manama, Bahrain. Ph. 973-700777 Fax.973-701211 Email. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: How come I don't get postings
.jpgs ?!*! Luckily for those of us of a sensitive disposition, I think jpgs (and other picture formats) would be filtered out by the list software, so we are safe from the threat of being suddenly confronted by images of our less-photogenic colleagues. The best we could manage would be ascii representations, I'm sure someone could convert photo-to ascii if needed.-) Seriously though, I seem to remember a set of helpfull commands available from the other list address (not the posting address, the list-control one you get sent in your welcome message) - You could be stuck in 'Digest' mode, which would mean you have to wait a week for the whole list to arrive at once. Sorry that this is a RTFM, but it's been so long since I turned off digest mode I can't remember exactly how I did it... Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Please send jpgs. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Khedr, Waleed [SMTP:[EMAIL PROTECTED]] Sent: Thursday, April 25, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Subject: RE: How come I don't get postings I can not see you on the list! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 25, 2002 8:43 PM To: Multiple recipients of list ORACLE-L Subject: How come I don't get postings Guys, Please someone tell me if you can see me on this list. I just subscribed and got the welcome message but no messages from this list. Thanks, Paula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: User_product_profile and sqlplusw
I haven't tried it for a long time either, but I remember a couple of things
that might help:
You can check the name of the program the users are connecting with using SELECT
PROGRAM FROM V$SESSION;
The 'security' is built into sqlplus, not into Oracle itself, so any curious
user can circumvent it very easily.
It's only usefull if you trust all your users not to mess with their desktops -
if you're serious about security, you might want to look at using
password-protected roles to enable access. I have got it to work with
sqlplusw.exe, but it was a long time ago.
Hope this helps.
Simon Anderson
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Simon Anderson/SSplc)
I haven't tried this for a while, but I think it's case sensitive. try
'sqlplusw.exe'
-Original Message-
Sent: Monday, April 15, 2002 4:13 PM
To: Multiple recipients of list ORACLE-L
Hi,
I have successfully restricted insert,update,delete using
product_user_profile if the user users sqlplus at the command prompt.
However I cannot
get it to work if they use sqlplusw.exe. Any ideas what entry I have to
use to enforce this.
This is what I used to restrict product access with sqlplus.exe
This works for command prompt sqlplus
insert into
SYSTEM.PRODUCT_USER_PROFILE(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE)
values ('SQLPlus','USER_ID','INSERT','DISABLED') ;
This does not work for windows sqlplus
insert into
SYSTEM.PRODUCT_USER_PROFILE(PRODUCT,USERID,ATTRIBUTE,CHAR_VALUE)
values ('SQLPlusw','USER_ID','INSERT','DISABLED') ;
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: subtract minute from date/time
I'm glad its not just me thats having a bad morning making the numbers work... My understanding of it goes something like this: When working with date/times, oracle considers 1 to mean One day. There are 24 hours in every day, so to get the figure for one hour you use 1/24 eg sysdate - 1/24 would give you the time one hour ago. There are 60 minutes in each hour, so one minute is 1/(24*60) Which is 24 hours times 60 minutes Similarly, if you wanted seconds there are 60 in each minute, so 1 second is 1/(24*60*60) Thats one day divided by (number of hours in a day * number of minutes in an hour * number of seconds in a minute) You can probably do the same thing with decimals, but the numbers are messy. If Oracle handled anything smaller than a second, we'd probably start seeing issues with rounding errors... Hope thats clear and I haven't made any obvious mistakes. Simon Anderson -Original Message- Sent: Thursday, April 11, 2002 14:08 To: Multiple recipients of list ORACLE-L I want to subtract a minute from a date/time How can I do this? John -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Stanby Database- Archive Transfer Problem
The quick answer to this one is : Try using more online redologs, adding them is a fairly simple RTFM. From looking at the setup you describe, I think you have two issues with your standby database. Which one you fix will depend on how you want the system to behave. It looks like you are using the 'default' standby database config, where you let Oracle take care of copying the archived redo logs to the standby database. I'd also guess that you have set the remote archive destination to OPTIONAL, so the database will abandon the attempt to copy the file if it fails. What seems to be happening is that your system is switching through the redo logs faster than it can transmit them to the standby database. You are only using 4 redologs, so when it finishes writing to the fourth one and tries to start writing to the first one again, it has not finished copying it to the standby database. With the archive destination for the standby database set to OPTIONAL, it will abandon the attempt to copy the file rather than stopping the production database until the transfer is complete. That would explain the missed files and give you two possible solutions: You could make the archive_log_dest_2 MANDATORY - this would avoid missed files, but your production database will stop dead until the transfer is complete. This is probably a bad idea on its own. You can make it more likely that the archived redo log will be copied across before the system needs to reuse that online redo log. Ideally, you do this by getting a better network, but thats probably not an option. The other answer is to have more than four online redologs, so it takes longer to wrap round to the first one again. More online redologs will compensate for short holdups in the network, but it isn't guaranteed - if you keep switching logs faster than you can transfer them, then sooner or later you will run into the same problem. Increasing the number of Archive processes helped the first time because each file to be transferred got picked up as soon as it was ready, but I don't think theres any point having more archive processes than you have redo logs - theres nothing for the extra processes to do. It could be some deeper problem with the network, but its probably just slow - you can check by measuring the time it takes to send a 1Mb file between the two computers when the system gets busy. Hope this helps, theres much more info on Metalink Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) If you find a solution for this, I would be interested in it. We have the same problem, same platform. Somatimes SAP starts some kind of processing and the network load increases enormously. And the archive log transfer fails after a while. Tamas Szecsy -Original Message- Sent: Thursday, April 04, 2002 10:28 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.7 and WINNT4 Our Standby database was running smoothly but due to some reasons we had to reconfigure it. We have used the same init parameter file which we used earlier(to have the exact parameters). The standby database was configured alright but this time logs are not properly getting transfered to the standby database, I mean every 3rd or 4th archived log file doesn;t get transfered and is missed. Our 4 redo logs are 1mb and block size is 8192, Log_checkpoint_interval = 1 (the default) and log_checkpoit_timeout = 600 Archive processes are 10(this we had increased to 5 the last time to counter the missin files on our standby database,and it worked fine,but this time we have increased from 5 to 10 and its not) and the view v$Archive_dest shows that both primary and standby database locations are valid. Average time for log switch, under normal conditions, is roughly 12-15 mins, but for some reasons it starts getting missed after a regular transfer of 3-4 archive files. Our network is 10Mbps and is sllow off and on. What could be the reason for the delay. Hussain Ahmed Qadri Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Standby Databse Problem
Hi I can't be certain from your description of the probelm, but have you tried increasing the number of redolog groups? I'm guessing that the problem is with Oracle reusing the online redo logs before they have been archived to the standby - The first half of the answer is to make the standby destination for archive logs mandatory to prevent this, but it does it by stopping the database until the log gets archived, and that's not a good answer by itself on a production system. If I'm right, the issue is the speed of the link to the standby database - it just can't transmit the archived logs as fast as the database produces them at peak load, so the redo logs get reused before they have been archived and the standby is left hanging. As a reminder, Oracle switches between its redo log groups, writing to each in turn and going back to the first one again once the last one has been written to - it is these online redo logs that are copied by the archiver processes to become archive logs. The other half of the answer is to increase the number of redolog groups, so it takes longer for Oracle to cycle back to the first one and therefore gives you longer to finish archiving the first one to the standby database. This works as long as the system slows down the switching and catches up with the archiving before it's run out of unused redo log groups. The slower your link, and the longer your high-activity periods, the more redolog groups you need. Increasing the number of archiver processes may have helped by starting to copy the archivelogs sooner, and I seem to remember Oracle suggesting 4 was a good number of archivers, but it doesn't deal with the underlying problem, which is producing archivelogs faster than you can archive them. The other answer to all this is to get a faster link between the two systems, but that's another issue entirely. Hope this helps - I'm currently working on a standby database project, so I've been looking at the same problems. Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) Hi, --Winnt 4.0, Oracle 8.1.7.--- We have slight problem with our Standby Database Configuration. All the archives from our Primary Database are not being copied to the Standby Database. When the archives are generated at a normal time, i.e after after a gap of 15-20 minutes ( which is normal, as our logfile size is 500K and it works fine with our normal database behaviour) , the archives get copied completely to the standby site. But due to a few jobs that run over night, the log switch happens very fast,a few times per minute. This kind of fast switching also happens incase of bulk insert or delete. Its in such cases that some of the archive logs are not copied to the standby site. To counter that, we increased the number of archiver processes from 1 to 4 on the Primary site, and when it didn't entirely solve our problem, we increased it to the maximum, i.e is 10. Even now all the archives are not being copied because of the quick switching of logs. Keeping the archiver processes to 10 is also a burden on the resources. Moreover, the second destination for writing archives(standby DB) is optional, and the value for log_archive_min_succeed_dest is set to 0. I am not sure increasing this or changing the site from optional to mandatory would solve the problem. Or would that have any effect? The other thing is that since the problem lies with the files not being copied from one site to the other, so it should not directly have anything to do with the Archiver processes, as server is generating archives properly. But on increasing the number of archiver processes, less number of files were not copied to the standby site, so it did help. Is there something we are missing here. Any help would be appreciated. Regards, Hussain Ahmed Qadri Database Administrator Shaukat Khanum Memorial Cancer Hospital Research Centre [EMAIL PROTECTED] www.shaukatkhanum.org.pk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Standby Instance questions and HA
I've seen a similar idea running on Silicon Graphics kit : 2 servers; heartbeat check between them; Drives mount on the other box when the 'live' system fails It was nice when it worked...Testing the failover caused barely a ripple, although it did disconnect any open sessions. Alas, it rarely worked that well when there was an actual problem. As with the Sun solution, no extra charge from Oracle, but added costs from SG. Still, who in their right mind would choose to run a production database on discontinued SGI machines? Simon Anderson Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) We have this in place on Sun as well. It is similar to a cluster in that it has a separate box (Ultra 2) monitoring a heartbeat between both database servers. You will have a significant impact during failover. All drives common to both boxes will be unmounted on the primary and remounted on the secondary (which then becomes the primary). Since a fsck is run for each file system it can take upwards of 30 minutes for the failover. The database is shut down and brought back up so all connections are severed which means an interruption of service. If I wanted H/A on Sun I would move to 9i and implement a RAC. No application changes are necessary with this new version of OPS. Good luck. --Michael -Original Message- Sent: Thursday, January 17, 2002 11:03 AM To: Multiple recipients of list ORACLE-L Jim: Sorry, you're not gonna like this answer. HA is a Sun product, not an Oracle product. Under Sun's High Availability, you can configure several modules like Sybase and Oracle. (The Oracle product is Sun Cluster HA-DBMS for Oracle.) It does require what I believe Sun calls a cluster but (IMHO) is a bastardization of the term. It truly is failover, not cluster. We've had lots of problems with it. It's caused us lots of grief, and only in a few instances gained us anything. It is NOT OPS, as the database does not run in parallel, but only on 1 box at a time. (Everything is double cabled, and so the drives are re-mounted on the 2nd box if a failover occurs.) Your users still get disconnected. You'd probably lose less data than with a standby (since you pick up with the same drives mounted on the other box), but it depends on how you have the standby implemented. There's no additional cost from Oracle to run this crap, but you'll be paying Sun great sums of money. The Sun web site has more info on HA. Let me know if you need more info. Good luck! Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: designer6i vs. designer2000
Alas, the last time I looked, the OTN download for Designer was missing. The latest is Des6i release 4, which it said was 'coming soon' Simon Anderson Andrea, Designer 6i is the newest one - release 3. You can actually download it from OTN. Designer2000 was renamed to 6i after the Y2K hysteria calmed down (and Uncle Larry got on his i bandwagon). Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, January 16, 2002 1:05 AM To: Multiple recipients of list ORACLE-L Hi, Is Designer 2000 and Designer 6i similar? They are about data modeling, aren't they. If I'd like to learn one, which one do you recommended? Thanks! Andrea __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: 9i install on Win2K
Not sure if it's the same fix for 9i as 8i, but patch 1507768 seems to work for 8.1.7 - I'm running it as I type. If you've not got metalink, I'm not sure how Oracle expects you to use their fine product on just about any NT machine produced in the last year or so. The note doesn't mention 9i, just : Any platform that uses the Intel Pentium 4 processor, with the Sun JRE and Symmantec JIT, OUI version 1.7.1.9.0 and earlier, will encounter this problem. Without the patch, it doesn't look like you're going anywhere. HTH Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Help with passwords
It's just ALTER USER username IDENTIFIED BY VALUES 'hex-key-whatever'; Substituting the username and password value from dba_users as appropriate. There are scripts that save it in a file with the commands to switch it back, but it's easy enough to do manually. Simon Anderson Hi all, I've got a little question, which i can't find simply on metalink. I've got one user with a not know password and i want to temporary change it;s password. I know it is possible to save the hex-key in dba_users. But how can i change it back to that hex-key? I need temporary this user account and can change the password, but i want to change it back to the original. Thx anyway, Marco Marco Alink Systeem- en databasebeheerder, Centrum voor Informatievoorziening, Universiteit Twente, Postbus 217, 7500 AE Enschede telefoon: 053 - 489 2628, fax:053 - 489 2383, http://www.utwente.nl/civ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Client install from hell
Farnsworth, Dave [EMAIL PROTECTED] on 10/19/2001 07:15:24 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Simon Anderson/SSplc) I think I've seen this before, it certainly sounds familiar... I've no idea what causes it, but I found a workaround was to only install a small number of components at a time - for some reason, that seems to work. Let us all know if you come up with a better solution. Simon Anderson I am having a problem on a couple of client installs. I have Oracle 8.1.7 to be installed on NT 4.0 SP6. The install starts out fine, I select a Orahome and then it starts to install. When it gets to 90% all of a sudden the install wizard screen just disappears without any error messages. I can see that it created a new directory and copied a bunch of files to the hard drive. I can even double click on the SQLPlus icon and it launches asking me for a username, password, and database. Then if I put the client CD back in and click on the show installed components, it tells me that there are no Oracle components installed. I look in the registry and a registry for Oracle has been created. I have done this about twenty times and get the exact same results. I even tried a different CD but that does not change things. During the install Oracle will write to a log but this log tells me nothing except what it installed, without one stinking clue as to what is causing this to puke. I get this just on two PC's with identical results. Has anyone seen this before? I can go to other PC's and install without a hitch. These PC's do work and have plenty of disk space. Any ideas, ,,,anything? Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Replacing the pesky '
Could be worse - could be raining,
Replacing those awkward characters comes down to distinguishing between them
used as quotes and used to search for that character.
The sql functions ASCII() and chr() both help here:
SQL select ascii() from dual;
ASCII()
---
39
The four single quotes explain that you're trying to quote a single quote. I
have to admit it's awkward, but it works. Once you know that the ascii value of
the ' character is 39, you can use it instead:
SQL select 'O'||chr(39)||'Reilly' from dual;
'O'||CHR
O'Reilly
(You could use the four-single-quotes thing, but it's clumsy.)
Finally, you can plug the ascii character value into your REPLACE statement,
something like:
SQL select replace('O'||chr(39)||'Reilly', chr(39), '*') from dual;
REPLACE(
O*Reilly
And this all works just fine, until someone decides to change their name to four
single quotes...
Anyone know a better way of coping with this kind of 'problem' data ?
Simon Anderson
Gotta love Mondays,
We've been through this before, and I usually don't have a problem, but
today my brain must think it's the weekend.
Hey, I've got a bunch of names like O'Brien and O'Reilley where I need to
replace the ' symbol. I've tried every type of combination of single
quotes and slashes(escape sequence) I can think of, but I must have missed
one, because I can't get it to replace.
Any Ideas? Here's the progenitor of the whole mess.
1* select replace(lastname,' ' ') from adst where lastname like 'O%'
SQL /
ERROR:
ORA-01756: quoted string not properly terminated
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: 8.1.7/HP-UX 11/PRODUCT_USER_PROFILE
The behavior you describe is normal for PRODUCT_USER_PROFILE. Once you understand how it works, it all becomes clear ;-) This is my understanding of it, hopefully someone will correct me if I'm wrong... When a user connects to the database via ORACLE's SQL+, the *application* checks the PRODUCT_USER_PROFILE table to see what SQL commands it should allow. It places no constraints on the users commands in the datbase itself, so 'forbidden' commands can still be executed via PL/SQL, or by connecting with another application (eg SQL Worksheet) that does not check PRODUCT_USER_PROFILE. PRODUCT_USER_PROFILE is useful only where users can't get round it by installing their own client software, and don't know enough PL/SQL to be dangerous. Even then, you should think about backing it up with audit trails, just in case. Hope this helps. Simon Anderson I have entries in the PRODUCT_USER_PROFILE to inhibit a user from executing the DROP command. However, if I embed a DROP command inside a PL/SQL procedure, and execute it with Dynamic SQL, the DROP is executed. Is this normal? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: User access within/outside of app
One of the ways round this is to have the priv's needed for anything other than select granted to a role, rather than the generic userid. Make the role non-default, and require a password to enable it - that way, using the ODBC connection won't give users any godlike powers. The issue is how to implement it to work with the application...Without changing the code, you could try setting up a logon trigger to enable the role, but I've not tried it so I won't guarantee it would work. Hope this helps. Simon Anderson We have purchased a system where users login through an ODBC connection using a generic Oracle userid. This userid has full rights to do insert, update, delete, select on any table in the schema. The app asks for another username and password which checks the application security table, which limits what areas of the apps they can access. Although this may work fine for the app, the users also have the ability to use Access and other ODBC compliant programs to look at the data. When doing so, they use the same ODBC DSN and, what do you know, they have capabilities beyond their wildest imagination. This is obviously not a situation I want to implement. I am looking for a way to allow a user into the app to do their normal work, but only allow read access for anything outside the app. Any suggestions or ideas would be more than welcome. Thanks, Larry Hahn Journal Sentinel, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Is it possible???
The question of restricting users to the 'approved' applications only comes up every couple of months... Yes, you can use PRODUCT_USER_PROFILE but remember: It only works Oracle SQL*Plus connections - If you can control what your users can run on their PCs, why do you let them have SQL*Plus? If you can't control what they can install and run on their own PCs, then it's a waste of your time to block SQL+ when there are so many other toolsthat will get round the restriction. If you want security for your data, you may be able to use non-default Roles, with passwords if needed,to provide it. If you can't write the roles into the application, you can use logon triggers and auditing to provide some protection. IMHO, PRODUCT_USER_PROFILE gives you a false sense of security, when what you need is enough understanding of Oracle to have a *real* sense of security. Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Steps for removing jobs
There's a note on Metalink that outlines it and states that it's not supported, not documented in the Oracle manuals and that the 'supported' method of manipulating jobs is as the jobs owner. Seems strange to me, but Ours is not to reason why... Simon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Steps for removing jobs
Usefull article, but it missed one useful bit... * You can only manage your own jobs, even if you're a DBA user. -Not quite true, check out the DBMS_IJOB package. Does the same things as DBMS_JOB (submit, change delete jobs, etc) but lets you specify the 'owning' user. I found it in the O'Reilly Oracle Built-in Packages book It saves messing around with password-swapping and becoming other users. Simon Anderson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
