RE: Forcing 2 Decimal Positions
Title: Forcing 2 Decimal Positions Thanks. This worked wonderfully!! -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Forcing 2 Decimal Positions select to_char(your_number, '.90') from dual / Raj - Rajendra dot Jamadagni at espn dot com Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !! -Original Message----- From: Burton, Laura L. [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 1:54 PM To: Multiple recipients of list ORACLE-L Subject: Forcing 2 Decimal Positions I know this must be extremely simple, but the only thing I can find for this is column format and that doesn't work, so could someone tell me how to force an amount field to show 2 decimal positions? I have tried format 999.99 and 990.00 but it will not print the zero if it is after the decimal ( i.e. 100.5 or 88 ). I know I have done this before but I guess I have gone brain dead. Thanks in advance! Laura
Forcing 2 Decimal Positions
Title: Forcing 2 Decimal Positions I know this must be extremely simple, but the only thing I can find for this is column format and that doesn't work, so could someone tell me how to force an amount field to show 2 decimal positions? I have tried format 999.99 and 990.00 but it will not print the zero if it is after the decimal ( i.e. 100.5 or 88 ). I know I have done this before but I guess I have gone brain dead. Thanks in advance! Laura
RE: Select Statement Gone South??
Title: Select Statement Gone South??
Never mind...I found my problem!! I had one key that had not been indexed.
Laura
-Original Message-
From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 25, 2003
7:24 PM
To: Multiple recipients of list
ORACLE-L
Subject: Select Statement Gone
South??
I am inserting records into a table based on a select
statement and it is taking way too much time. I have created indexes of the foreign
keys and tried to rearrange the where clause to omit records earlier, but to no
avail. The statement looks like this:
Insert into table test
select part_num,
nomenclature,
to_char(requisition_date,'yddd')||lpad(preq.document_serial,4,0),
2, /* '53-Purchase' */
PSA.TRANS_date,
requisition_qty,
unit_price,
4 /* 'ALMD Disapproval' */
from
part_master_catalogs pmc,
part_requisitions preq,
part_price_histories pph,
part_status_assocs psa
where
preq.pmc_id_fk = pmc.pmc_id
and
preq.preq_id = psa.preq_id_fk
and
psa.req_status_cd_fk = 'D'
AND
PSA.TRANS_DATE <= TO_DATE('&&NEWMEDATE','MM-DD-')
AND
PSA.TRANS_DATE > TO_DATE('&&LASTMEDATE','MM-DD-')
and
pmc.pre_approved_purch_flag = 'N'
and
pmc.company_reimburse_flag = 'Y'
and
pph.pph_id = (select max(pph_id) from part_price_histories
where preq.pmc_id_fk = pmc_id_fk
and requisition_date >= effective_date)
Any ideas?? Any insight would be appreciated.
Thanks,
Laura
Select Statement Gone South??
Title: Select Statement Gone South??
I am inserting records into a table based on a select statement and it is taking way too much time. I have created indexes of the foreign keys and tried to rearrange the where clause to omit records earlier, but to no avail. The statement looks like this:
Insert into table test
select part_num,
nomenclature,
to_char(requisition_date,'yddd')||lpad(preq.document_serial,4,0),
2, /* '53-Purchase' */
PSA.TRANS_date,
requisition_qty,
unit_price,
4 /* 'ALMD Disapproval' */
from part_master_catalogs pmc,
part_requisitions preq,
part_price_histories pph,
part_status_assocs psa
where preq.pmc_id_fk = pmc.pmc_id
and preq.preq_id = psa.preq_id_fk
and psa.req_status_cd_fk = 'D'
AND PSA.TRANS_DATE <= TO_DATE('&&NEWMEDATE','MM-DD-')
AND PSA.TRANS_DATE > TO_DATE('&&LASTMEDATE','MM-DD-')
and pmc.pre_approved_purch_flag = 'N'
and pmc.company_reimburse_flag = 'Y'
and pph.pph_id = (select max(pph_id) from part_price_histories
where preq.pmc_id_fk = pmc_id_fk
and requisition_date >= effective_date)
Any ideas?? Any insight would be appreciated.
Thanks,
Laura
Sort (Collating Sequence)
Title: Sort (Collating Sequence) I have a question concerning a situation with our ORDER BY clauses. We have a vendor table which allows the user to input any case. Therefore we have 'Vendor' and 'VENDOR'. When using the ORDER BY clause it sorts VENDOR first and then Vendor. I need for the names to be sorted regardless of the capitalization. I know that we could have put an UPPER function on the input of this data to alleviate this problem, but the deed is done. I had suggested using the UPPER in the ORDER BY clause to always insure true alphabetizing but the thought was to have the database handle this instead of relying on the application. I have found SQLCASE which works when I SELECT but not on the ORDER BY clause. Plus this is SQL*Plus only. I have researched the NLS parameters and read about binary sorts vs linguistic sorts as well as the different parameters available, but I did not see anything that could handle this situation systemically. I basically wanted to see how to add UPPER to an ORDER BY clause without having to actually code it. Does anyone know if there is such a creature? My boss says that SQL Server has an option to do this, which immediately puts me on the defense and retort (in good humor of course) that I was sure Oracle did if Microsoft did!! I do not mind researching but I do not know anywhere else to look. Thanks in advance for your replies, Laura
RE: To_Number
Title: RE: To_Number Thanks for the email!! I did not know about Dump so I learned something new. The record I thought I had a problem with was ok and that is why I did not see anything amiss. However the record after this one was $20041-94. Corrected that and got $145.34 EA. I just told the developer to handle the 1722 error and let someone else deal with how to correct the data. It's ridiculous! Laura -Original Message- From: Jeff Herrick [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 8:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: To_Number Did you check it with dump() ? select dump(unit_cost,16) from tablewhatever; for Hex. Check for unprintables that way. Try inserting the bad values into a look-aside table for later analysis i.e. create table look_aside (rownum rowid, bad_val varchar2(20), dump_val varchar2(200)); declare bad_num exception; numval number(10,2); pragma exception_init(bad_num,-1722); Cursor C1 is select rowid,unit_cost from yourtable; begin for x in C1 loop declare -- inner block will allow exception trap begin -- trim and change o's to zeroes numval := to_number(rtrim(replace(x.unit_cost,'O','0'))); exception when bad_num then insert into look_aside values (x.rowid,x.unit_cost, substr(dump(unit_cost,16),1,200)); end; end loop; commit; exception when others then dbms_output.put_line(sqlerrm); end; / You can use the rowids in the look-aside table to zap the bad values later. HTH Jeff Herrick On Thu, 5 Dec 2002, Burton, Laura L. wrote: > Since we don't have that many 3rd party software packages I did make the > mistake of asking 'Why??' and received 'Because!' so I too am trying to jump > in and 'fix it'. As I responded to another email earlier, the RTrim worked > because there were spaces after the amount which was causing the problem. > The only problem now is I have one record (so far) that has a unit cost that > looks like any other unit cost, yet I receive 'invalid number' for it. The > only thing I can figure is that there must be an unprintable character in > the field that I cannot see and rtrim is not deleting since it isn't a > space. > > Laura > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeff Herrick 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: To_Number
Title: RE: To_Number
Since we don't have that many 3rd party software packages I did make the mistake of asking 'Why??' and received 'Because!' so I too am trying to jump in and 'fix it'. As I responded to another email earlier, the RTrim worked because there were spaces after the amount which was causing the problem. The only problem now is I have one record (so far) that has a unit cost that looks like any other unit cost, yet I receive 'invalid number' for it. The only thing I can figure is that there must be an unprintable character in the field that I cannot see and rtrim is not deleting since it isn't a space.
Laura
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 5:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: To_Number
Rachel,
We have two Third Party apps here for Finance and Student Information that
do ridiculous stuff like this so often, I just immediately jumped into "fix
it" mode without even questioning. "Don't even ask why" is our motto. ;
-)
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Rachel
Carmichael To: Multiple recipients of list ORACLE-L
AHOO.COM> cc:
Sent by: Subject: Re: To_Number
[EMAIL PROTECTED]
om
12/05/2002
03:49 PM
Please respond
to ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored as varchar?
--- [EMAIL PROTECTED] wrote:
>
> Laura,
>
> Are those really zeros in $34,000.05 or are they letter Os? If so
> use
> Replace. (Beware of letter l being used instead of numeral 1 as
> well.)
>
> Any leading or trailing spaces? If so use Trim(unit_cost).
>
> Just a couple of quick suggestions.
>
> Jack C. Applewhite
>
>
>Burton, Laura
>
> I have a table which contains a Unit_Cost varchar2(16) which contains
> $34,000.05. I can enter select
> to_number('$34,990.08','$999,999,999.99')
> from dual; and the results is 34990.08. However when I enter select
> to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722:
> invalid number.
>
> Is there any other way to do this? I am trying to add a varchar2
> field
> that contains $ and commas. I thought the to_number function would
> convert
> the data to a number field.
>
> Thanks,
>
> Laura
--
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: To_Number
Title: To_Number
Your guess is correct !! Thank you very much. It worked even without the rtrim, but I am
leaving it in just in case.
Thanks again,
Laura
-Original Message-
From: Toepke, Kevin M
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002
2:30 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: To_Number
My guess is that you have
leading or trailing spaces. try
select to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99')
from elas.qdr
-Original Message-----
From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002
2:24 PM
To: Multiple recipients of list
ORACLE-L
Subject: To_Number
I have a table which contains a Unit_Cost
varchar2(16) which contains $34,000.05. I can enter
select
to_number('$34,990.08','$999,999,999.99') from dual; and the results is
34990.08.
However when I enter select
to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722: invalid
number.
Is there any other way to do this?
I am
trying to add a varchar2 field that contains $ and commas. I thought the
to_number function would convert the data to a number field.
Thanks,
Laura
To_Number
Title: To_Number
I have a table which contains a Unit_Cost varchar2(16) which contains $34,000.05. I can enter select to_number('$34,990.08','$999,999,999.99') from dual; and the results is 34990.08. However when I enter select to_number(unit_cost,'$999,999,999.99') from elas.qdr I get ora-01722: invalid number.
Is there any other way to do this? I am trying to add a varchar2 field that contains $ and commas. I thought the to_number function would convert the data to a number field.
Thanks,
Laura
RE: PIC 9(9)V99 ??
Title: RE: PIC 9(9)V99 ?? This represents 11 digits with an implied decimal point. We still have a mainframe. :) Laura -Original Message- From: Grabowy, Chris [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 12, 2002 7:14 PM To: Multiple recipients of list ORACLE-L Subject: OT: PIC 9(9)V99 ?? Totally off Oracle topic. I am studying the mainframe layout specs for a flat file, and came across this "datatype", and I just want to confirm my interpretation. PIC 9(9)V99 So that's 9 digits before the decimal point? And then two digits after the decimal point? And the actual data would have a decimal point? So in this case, for a total of 12 characters? 123456789.99 ? TIA!!! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris 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: Changing column format
Title: RE: Changing column format 1. Backup Table 2. ALTER TABLE user DROP PRIMARY KEY CASCADE; This will drop the constraint and delete all foreign key relationships to userid. 3. ALTER TABLE user ADD CONSTRAINT username_pk PRIMARY KEY (username) USING INDEX TABLESPACE USER_INDEX; This will create a new primary key constraint on username IF the username field is unique and does not contain nulls. The second line is optional. Laura -Original Message- From: Nguyen, David M [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 2:34 PM To: Multiple recipients of list ORACLE-L Subject: Changing column format I create a table to store user account information and set "userid" column to be primary key. I now want to set "username" to be primary key instead of "userid", how do I change it? There are couple hundreds of records in table. Please advise. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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 to Excel
Title: Oracle to Excel Thank you so much!! This works like a charm. Laura -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 07, 2002 6:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Laura, Sorry about that - instead of using chr(10), use chr(09). (10) is line feed. (09) is tab. also - don't forget to : set trimspool on - it gets rid of trailing spaces! good luck! Tom Mercadante Oracle Certified Professional -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column. Below is an example (1 record is shown): 53-041-02 0 2 AL POWER CO 51271 27-Nov-01 173.52 447-57 D412 173.52 226 DONNELL 43000 00014.04.D412 It looks like it thinks it is a carriage return instead of a tab. Any ideas? Laura -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Laura, lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format. You can do this yourself as follows: in SqlPLus select col1||chr(10)||col2||chr(10) from table. the chr(10) is the tab character. spool the results to a output.xls open the file using excel. the tab character is the default column delimiter. You should see all your data in cells. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Oracle to Excel I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura
RE: Oracle to Excel
Title: Oracle to Excel Thank you for your response Tom. I received other responses as well, and I know that I can comma delimit a .txt file but I wanted to know if I could do it all at one time, which is why I tried your suggestion first. It works except that each field is in a separate row instead of a column. Below is an example (1 record is shown): 53-041-02 0 2 AL POWER CO 51271 27-Nov-01 173.52 447-57 D412 173.52 226 DONNELL 43000 00014.04.D412 It looks like it thinks it is a carriage return instead of a tab. Any ideas? Laura -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle to Excel Laura, lots of tools do this for you automatically. Oracle discoverer will export the results of a query directly into Excel format. You can do this yourself as follows: in SqlPLus select col1||chr(10)||col2||chr(10) from table. the chr(10) is the tab character. spool the results to a output.xls open the file using excel. the tab character is the default column delimiter. You should see all your data in cells. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 06, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Subject: Oracle to Excel I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura
Oracle to Excel
Title: Oracle to Excel I think I have seen traffic concerning the extracting of data from Oracle into an Excel spreadsheet. We now have a need for this. Could anyone enlighten me? Thank you in advance. Laura
Access with ACCESS
Title: Access with ACCESS How do I stop users from accessing Oracle tables with Access? They have rights to the tables in Oracle and should have those rights, yet I do not want anyone to be able to pull the data off into an access database. Is it possible to stop this without taking their privileges to the Oracle tables away? Thank you, Laura
Driver
Title: Driver Does anyone have the executable of the MDI odbc driver? One of the .dll files is MDMDI32.dll. I have contacted ViaServ and have been told that they no longer have this driver, but instead want me to upgrade to their new driver. I already have this driver and it does not work with one of our applications, which is why I want the old driver. We have misplaced the diskette that it was on. Thank you, Laura
RE: Internal Password
Title: Internal Password Thanks for your response Kevin. We use NT. I can sign on without using a password as well, but I do not want to be able to. Today I did found that commenting out an entry in the SqlNet.ora file will force the need for a password and this is how I resolved the problem. Thanks, Laura -Original Message- From: kkennedy [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 08, 2002 4:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Internal Password What OS and version of Oracle are you using? On unix systems, the presumption is that only authorized personnel (DBAs and Sys Admins) can connect as the oracle user. Only the oracle user can connect internal. If unauthorized personnel have the oracle password, change it immediately to something obscene that includes special characters -- if it's properly obscene, you won't be tempted to write it down on a yellow sticky attached to your screen 8-) If you are on NT, this is somewhat but not significantly different (if I remember right, it's been a few years since I've done Oracle on NT). An Oracle server on NT should not allow logins by anyone except the DBA and NT administrator who should both be trusted. By the way, I use remote_login_passwordfile=exclusive on unix and connect internal works just fine without a password. Maybe the shared setting works differently and maybe this works differently between unix and NT. Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 07, 2002 10:23 PM To: Multiple recipients of list ORACLE-L Subject: Internal Password I can sign on as Internal and not key in a password. I have tried to 'edit' the password as well as 'delete' the sid and make a 'new' sid with an internal password, but I can still access the database as internal without having to input a password. Could someone shed some light on how to correct this. Thank you in advance, Laura
RE: UPDATE Results
Title: UPDATE Results Thank you. This worked great!! -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 21, 2002 12:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: UPDATE Results using sql%rowcount innediately after update statement will give you that value. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 21, 2002 1:27 PM To: Multiple recipients of list ORACLE-L Subject: UPDATE Results Is there a way to access 'something' that would denote if any rows were updated? Feedback lets you set up how many rows will be effected before a count is returned, but WHERE is this number coming from? I have found a 'Returning' clause of the update statement, but I don't really want a value returned, just how many rows were affected. We have a situation where we want to know if an update statement actually updated any rows or in fact did not find a row that met the condition. SqlPlus will return '0 rows updated.' Does anyone know where 0 is coming from? Thanks you for your help. Laura
UPDATE Results
Title: UPDATE Results Is there a way to access 'something' that would denote if any rows were updated? Feedback lets you set up how many rows will be effected before a count is returned, but WHERE is this number coming from? I have found a 'Returning' clause of the update statement, but I don't really want a value returned, just how many rows were affected. We have a situation where we want to know if an update statement actually updated any rows or in fact did not find a row that met the condition. SqlPlus will return '0 rows updated.' Does anyone know where 0 is coming from? Thanks you for your help. Laura
RE: Trigger or ????
Title: Trigger or Nope. We have a document number which consists of a serial number, date, location, etc, etc. The business rule is to have the serial number start back at one each day. -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 14, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: Trigger or Do you mind if I ask why do you want to reset the sequence? Regards, Waleed
Trigger or ????
Title: Trigger or I have a need to reset a sequence number at 00:01 everyday. I thought about creating a trigger to check the time, but thought that there might be a better way than checking the time every time a record is being added. I also thought about checking the max date on the table and comparing against the system date. When system date > max then reset the sequence number. I like this logic better and thought of holes with using the time. The only problem I have is that this seems like a lot of overhead every time I add a record, which will be often. Is a trigger the only method available to me? Thanks, Laura
Package/Procedure
Title: Package/Procedure Is there a naming convention for procedures and/or packages? Thank you, Laura
RE: Still Problem with ORA-12514
Title: RE: Still Problem with ORA-12514 I have had one instance (out of many) where using a hostname would not work. I was getting the same error and I had to use the IP address in the Listener to get it to work. There were two IP addresses and the listener was accessing the second address where the client was accessing the first IP address. Laura -Original Message- From: Danisment Gazi Unal [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 06, 2002 12:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: Still Problem with ORA-12514 Hi, If you are sure other recomendations are correct, open your tnsnames.ora with a text editor, check if there is a weird character(i.e. tabs) in it. Or remove your tnsnames.ora, then recreate it. danisment... Suzy Vordos wrote: > You can have a tnsnames.ora in each ORACLE_HOME, but not recommended > because you'd need to maintain multiple copies. > > Try stopping/restarting the listener, and be certain you're running only > the listener for the highest version of Oracle. > > On rare occasion something in sqlnet.ora causes problems. You could > temporarily rename it (or comment out the contents) and see if that > makes a difference. > > Hamid Alavi wrote: > > > > I did it and still NOT working, > > Question? Can I have one tnsnames.ora for BOTH oracle-home or not??? > > > > -Original Message- > > Sent: Friday, April 05, 2002 12:29 PM > > To: Multiple recipients of list ORACLE-L > > > > In tnsnames.ora set SERVICE_NAME=cms.hollywood, and in listener.ora > > remove SID_DESC for that database as it's really not needed for 8i. > > Here's what my config looks like: > > > > # -- > > # Filename: listener.ora > > # -- > > LSNR01 = > > (ADDRESS_LIST = > > (ADDRESS= (PROTOCOL= IPC)(KEY=lsnr01)) > > (ADDRESS= (PROTOCOL= TCP)(Host= host.domain)(Port= 1521)) > > ) > > STARTUP_WAIT_TIME_LSNR01 = 0 > > CONNECT_TIMEOUT_LSNR01 = 10 > > TRACE_LEVEL_LSNR01 = OFF > > > > LSNR02 = > > (ADDRESS_LIST = > > (ADDRESS= (PROTOCOL= IPC)(KEY=lsnr02)) > > (ADDRESS= (PROTOCOL= TCP)(Host= host.domain)(Port= 1526)) > > ) > > STARTUP_WAIT_TIME_LSNR02 = 0 > > CONNECT_TIMEOUT_LSNR02 = 10 > > TRACE_LEVEL_LSNR02 = OFF > > > > # -- > > # Filename: tnsnames.ora > > # -- > > orcl01 = > > (DESCRIPTION = > > (SDU = 8192)(TDU = 8192) > > (ADDRESS = (PROTOCOL=TCP)(HOST=host.domain)(PORT=1521)) > > (ADDRESS = (PROTOCOL=TCP)(HOST=host.domain)(PORT=1526)) > > (CONNECT_DATA = (SERVICE_NAME = orcl01.world)) > > ) > > > > Hamid Alavi wrote: > > > > > > I am really Buffled, and need your help guys. Here is my Tnsnames.ora & > > > listener.ora & sqlnet.ora in server side: > > > > > > db-name = "CMS" , db_domain="hollywood" , instance_name = CMS , > > > service_name = CMS.HOLLYWOOD > > > > > > LISTENER.ora on Server: > > > # LISTENER.ORA Network Configuration File: > > > /u04/app/oracle/network/admin/listener.ora > > > # Generated by Oracle configuration tools. > > > > > > LISTENER = > > > (DESCRIPTION_LIST = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) > > > ) > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = HOLLYWOOD)(PORT = 1521)) > > > ) > > > ) > > > (DESCRIPTION = > > > (PROTOCOL_STACK = > > > (PRESENTATION = GIOP) > > > (SESSION = RAW) > > > ) > > > (ADDRESS = (PROTOCOL = TCP)(HOST = HOLLYWOOD)(PORT = 2481)) > > > ) > > > ) > > > > > > SID_LIST_LISTENER = > > > (SID_LIST = > > > (SID_DESC = > > > (SID_NAME = PLSExtProc) > > > (ORACLE_HOME = /u04/app/oracle) > > > (PROGRAM = extproc) > > > ) > > > (SID_DESC = > > > (GLOBAL_DBNAME = CMS.HOLLYWOOD) > > > (ORACLE_HOME = /u04/app/oracle) > > > (SID_NAME = CMS) > > > ) > > > ) > > > > > > > > > > > TNSNAMES.ora on server side: > > > > > > # TNSNAMES.ORA Network Configuration File: > > > /u04/app/oracle/network/admin/tnsnames.ora > > > # Generated by Oracle configuration tools. > > > > > > CMS = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = HOLLYWOOD)(PORT = 1521)) > > > ) > > > (CONNECT_DATA = > > > (SERVICE_NAME = CMS) > > > ) > > > ) > > > > > > INST1_HTTP = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = TCP)(HOST = HOLLYWOOD)(PORT = 1521)) > > > ) > > > (CONNECT_DATA = > > > (SERVER = SHARED) > > > (SERVICE_NAME = CMS) > > > (PRESENTATION = http://admin) > > > ) > > > ) > > > > > > EXTPROC_CONNECTION_DATA = > > > (DESCRIPTION = > > > (ADDRESS_LIST = > > > (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) > > > ) > > > (CONNECT_DATA = > > > (SID = P
Dynamic SQL
Title: Dynamic SQL We have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet. We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me. Thank you, Laura
Security for Table/Procedure
Title: Security for Table/Procedure I know that you can grant access on a table whether it be select, update, delete, etc, and I know that to keep from granting access to a table you can use procedures and grant execute rights to the procedure. Our situation is that we want to use procedures for security, but the procedures select data to populate a form and then update if applicable. We had talked about granting select to the tables by using a role since anyone can view the data, and then using procedures to update the tables. The only problem is that if we grant select to the tables and the application executes the procedure within it, the procedure will not be found and even the select will not happen. Could some of you share how you handle security? Am I missing something with tables/procedures? Any ideas would be appreciated. We are just beginning development and need to have a handle on how we are going to do this. Thanks, Laura
RE: SqlLoader
Title: SqlLoader
Hey,
at this point nothing is stupid. The problem is probably something
'stupid' that I am or are not doing. Yes, I did try setting the Oracle_sid
parameter when I started trying to figure out what was
wrong.
Now
for my stupid question. What is Two_Task?
Thanks,
Laura
-Original Message-From: Robertson Lee - lerobe
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 13, 2001 4:05
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
SqlLoader
Stupid question I know but is the correct SID set
??
and
watch out for TWO_TASK !!
Regards
Lee
-Original Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 13, 2001
6:10 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: SqlLoader
Thank you for your reply. The user does have
DBA rights and also owns the table that is being referenced. If it was
a table rights issue I would think I would have gotten 'table or view does
not exist' message. I don't think it is even getting connected to the
database.
Laura
-Original Message-From: Jeff Cox
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 12, 2001 5:30
PMTo: Multiple recipients of list ORACLE-LSubject:
Re: SqlLoader
Laura,
The username that you are trying to use, does
this username own the table that is to be loaded, or does this username
have DBA privileges?
Even though you can connect via sqlplus
and/or svrmgr to a session, this does not mean that you will be able
to load data into any table that you desire.
HTH
Regards,
Jeff
Tempe, AZ
- Original Message -
From:
Burton, Laura L.
To: Multiple
recipients of list ORACLE-L
Sent: Wednesday, December 12, 2001
3:35 PM
Subject: SqlLoader
I have never had a problem with sqlloader
before, but now when I run it I get an error 'invalid name/password'
message. I can sign on svrmgr and through sqlplus with no
problems.
Any ideas?
Thanks, Laura The information contained in this communication
isconfidential, is intended only for the use of the recipientnamed
above, and may be legally privileged. If the reader of this message is not
the intended recipient, you arehereby notified that any dissemination,
distribution orcopying of this communication is strictly prohibited.
If you have received this communication in error, please re-send this
communication to the sender and delete the original message or any copy of
it from your computersystem.
RE: SqlLoader
Title: SqlLoader
Thank
you for your reply. The user does have DBA rights and also owns the table
that is being referenced. If it was a table rights issue I would think I
would have gotten 'table or view does not exist' message. I don't think it
is even getting connected to the
database.
Laura
-Original Message-From: Jeff Cox
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, December 12, 2001 5:30
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
SqlLoader
Laura,
The username that you are trying to use, does
this username own the table that is to be loaded, or does this username have
DBA privileges?
Even though you can connect via sqlplus
and/or svrmgr to a session, this does not mean that you will be able to
load data into any table that you desire.
HTH
Regards,
Jeff
Tempe, AZ
- Original Message -
From:
Burton,
Laura L.
To: Multiple
recipients of list ORACLE-L
Sent: Wednesday, December 12, 2001 3:35
PM
Subject: SqlLoader
I have never had a problem with sqlloader before,
but now when I run it I get an error 'invalid name/password' message.
I can sign on svrmgr and through sqlplus with no problems.
Any ideas?
Thanks, Laura
SqlLoader
Title: SqlLoader I have never had a problem with sqlloader before, but now when I run it I get an error 'invalid name/password' message. I can sign on svrmgr and through sqlplus with no problems. Any ideas? Thanks, Laura
Password Changes
Title: Password Changes When you alter a user's password, what table does it update? I need to 'restore' a password for a user back to what it was before I changed it, but do not know what it was. Any ideas?? Can this be done? Thanks, Laura
Privileges
Title: Privileges I have granted 'all' to a table owned by owner1 to a role. I granted this role to user1. User1 can access this table via SQL*Plus and Developer. However, when developing a procedure/package in Procedure Builder under program units, and then dragging it down to the bottom as user1 procedures, an error stating 'insufficient privileges on owner1.tablename' is received. Any ideas?? I have researched the 'grant execute on procedure' privilege but I don't see why I would need to do this for the table owner. My understanding of this privilege is to grant another user access to the procedure and maintain security in this way, instead of having to grant access to each user individually. Laura
RE: Online backup script..........
Title: RE: Online backup script.. My, my...were you afraid he wouldn't understand the 1st reply? In the vein of 'if you were the last man on earth...' you can delete my mails in the future, because you can be sure I will be deleting yours. Have a good day anyway!! Laura -Original Message- From: Hutchins, Robert [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 04, 2001 4:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: Online backup script.. Read the F--- manual. -Original Message- Sent: Wednesday, July 04, 2001 3:10 PM To: Multiple recipients of list ORACLE-L Breakdown and read the admin guide, HINT: look at alter tablespace begin backup. learning is a wonderful thing. joe jaimin wrote: > > Hello guru's, > I am using one database in nonarchive log mode, I want to change it to > archive log mode. > > Platform Windows nt 4.0 > Oracle 7.3.0 > database size 10GB. > > Can any body give me the script which will automatically take online backup > of my database? > > BFN, > Jaimin. > -- Joe Testa Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa 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: Hutchins, Robert 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: SID on sqlplus prompt?
Title: RE: SID on sqlplus prompt? I use this as well but it only works going through Sqlplus. If you want to change connections while you are in sqlplus by entering sql> Connect username@dbname, then the sid/username will not change on the sql> prompt. To make the connection change show, add a member in the Bin directory called Connect.sql and add the following code: connect &1 set termout off pause off arraysize 1 rem rem Store username rem col user_id new_value user_id col user_name new_value user_name select user_id, lower(username) user_name from user_users where username = user; rem rem Store database name rem col db_name new_value db_name select lower(substr(global_name,1,(instr(global_name,'.',1,1)-1))) db_name from global_name; set sqlprompt "&db_name:&user_name> " Then in sqlplus enter sql> @Connect username@dbname and the sid/username should change to the new connection. The glogin and connect members are very helpful when wanting to see what database you are connected too. They originated from someone on this list, but I don't remember who. Laura -Original Message- From: Gene Sais [mailto:[EMAIL PROTECTED]] Sent: Friday, June 08, 2001 5:36 PM To: Multiple recipients of list ORACLE-L Subject: Re: SID on sqlplus prompt? I use this: glogin.sql file found in $OH/sqlplus/admin on the db server. hth. gene
RE: Sqlloader on VSAM file
Title: RE: Sqlloader on VSAM file Raymond, Please forgive me, but I am not sure now if you are asking a question or stating that you're ok. Your control file looks ok except I 'define' my fields differently. I am sending you an example below. I am assuming your amount fields has 2 decimal positions. Let me know if you need anything else. Laura ___ options (errors=99)load datainfile 'pmiwkly.dat'truncate into table pmiwklytrailing nullcols(badge position(01- 06) char,wedte position(07- 12) date "MMDDYY" "decode(:WEDTE, '00', null, :WEDte)",mgmtded position( 17- 17) CHAR,unionded POSITION( 18- 18) CHAR,esophrs POSITION( 78- 82) INTEGER EXTERNal ":ESOPHRS/10",esopamt POSITION( 83- 89) INTEGER EXTERNAL ":ESOPAMT/100",mesophrs POSITION( 90- 94) INTEGER EXTERNAL ":MESOPHRS/10",mesopamt POSITION( 95-101) INTEGER EXTERNAL ":MESOPAMT/100" ) _ -Original Message-From: Raymond Lee Meng Hong [mailto:[EMAIL PROTECTED]]Sent: Sunday, June 10, 2001 9:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Sqlloader on VSAM file [Raymond] I did check on the source text file , I did't see a carriage return on this, according to the previous example , this is the 1 record A38001000307754A005516562HATIJAH BT BAKAR 199201300202 850 this is the 2nd record and so on .and for the first 6 character is the agent code , which will not be the same like A38001, might be something else F41010..or whatever .. A38001053088697A006106290ALIBABAKA R 1992013001018 50 here is my control files. . if the record is like this 1 line/record. A38001053088697A006106290ALIBABAKAR 199201300101850 LOAD DATAINFILE AAA.DATAPPEND INTOTABLE LN21PNB(FINANCE_CD position(1:6)DECIMAL EXTERNAL, MEMBERSHIP_NO position(7:15)CHAR, ICNO position(16:29)CHAR, NAME position(30:69)CHAR, CERTNO position(70:75) CHAR, TXN_DATE position(76:83)DATE 'MMDD', LOAN_AMT position(84:89)DECIMAL EXTERNAL, REPAY_AMT position(90:96) DECIMAL EXTERNAL, REASON_CD position(97:98) DECIMAL EXTERNAL, REPAY_MAG position(99:102) DECIMAL EXTERNAL) sorry for any misleading
RE: Sqlloader on VSAM file
Title: RE: Sqlloader on VSAM file I do the same thing here at my shop and the record I download from a vsam file is 3574 in length. When I view the download in the .txt format it is wrapped just like what your records look like below. However, a new record does start on a new line, and it looks like yours does the same thing. In your .ctl member just treat the record as if were not wrapped. It will work. Laura -Original Message- From: Raymond Lee Meng Hong [mailto:[EMAIL PROTECTED]] Sent: Friday, June 08, 2001 4:06 AM To: Multiple recipients of list ORACLE-L Subject: Sqlloader on VSAM file Hello , currently I have a set of data download from IBM VSAM file format such as these A38001000307754A005516562HATIJAH BT BAKAR 199201300202 850 A38001053088697A006106290ALIBABAKA R 1992013001018 50 A38001053406059A005637195SALBIAH B RAHMAT 199201300101000 0850 A38001157190166A005249952RAIDAH AB U TALIB 199201300100850 0850 Actually of the the field is fixed on the length , but for the name which it auto-carriage return down to next line , how can I configure my sqlloader to capable for this format , as well as I know ,sqlloader can only download ASCII (1 line per line on fetching record , but how about this when the line is fix on 60 per line and the remaining record will continous in next line ? how to capable for this ?? Raymond fall asleep in waiting for the Q. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond Lee Meng Hong 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).
Resolved!! Another Downed Database
Title: Resolved!! Another Downed Database I wanted to let everyone know that my (un)archived, no cold backup database is now up!! I took Oracle's advice and started with the full physical backup and did not apply the differential backup. I then entered Recover Database and when the messsage came back 'database recovered' I just stood there and stared at the screen. When I finally started breathing because I was turning blue, I opened the database, and it opened! I couldn't believe it after all the things I've read. Although I did lose one week's worth of data, in this case it is something we can live with. Needless to say I am going to put it in archivelog mode ASAP. Oh, and I did curve the urge to let everyone know until I shut the database back down and got a good backup (as one of the respondees had suggested). Many thanks to all who replied. Whether the suggestions helped or not it is always nice to know that someone knows what you're going through and are trying to help. I know you learn more 'by fire', but I think I am going to go home now and sit under the spinkler Thanks again, Laura
RE: Another Database Down
Title: Another Database Down Just to put you a little more in the picture I have recovered a couple of DBs now without archived redo log files etc where all hope had been lost and resumes were being updated and got them back by fudging a couple of issues. (Oracle would probably throw their hands up in horror and refuse to support what I did, but the database opened, I didn't lose any data (this I believe was down to good fortune rather than skill) and everyone was happy. (snip> Lee Lee, I am just glad someone has responded and wants to help! I am not sure how much I told you about my situation, so bear with me while I recite it to you. Our OS is Windows NT. We had 2 disks to crash. Two of my databases are ok because I had archive logs for one and a cold backup for the other. However, I have one that I have not done anything too as far as backup, other than a physical backup of the database datafiles, redo logs, etc. The Sysadm had a full backup of the previous Friday and a differential backup from the following Thursday which he loaded back to the server when the new disks were installed. I tried to recover the database and when it asked for the logs I entered cancel. It said that recovery was successful but gave me a warning that if I tried to open the database with the Resetlogs option it would tell me that my system datafile was too incomplete (or something to that affect). Yesterday (Thursday) I did call Oracle and this is what this first guy had me do: 1) Startup Mount 2) Select from v$datafile to check the status of my datafiles. They were all online, which per Support, that was not good. 3) Alter database datafile ??? offline. This command gave me the error 'offline immediate disallowed unless media recovery enabled (ORA 01145). So then I was referred to another Support person. He had me shutdown the database, startup mount, recover database. Of course the recover asked for a log and Support had me input each of my five redo logs, but the log with the info needed had already been overwritten so the info wasn't there and I didn't get any further. Next Support wanted me to restore my files from the Friday backup only. This is where I have stopped because now I am waiting on my Sysadm to come in and restore them for me. I tried to call the Support guy back and let him know the status, but got a message stating that because I had only 'basic or bronze' support that I could only speak to someone between certain hours, and then was cut off. I had gone through Metalink though so I just updated the tar. If you have any ideas for me I would appreciate any and all. Thanks, Laura
RE: BUNYAMIN POSTS HAVE A VIRUS
Title: RE: BUNYAMIN POSTS HAVE A VIRUS Didn't he send an email to the list yesterday apologizing for this and stated that he had no idea it was happening? Laura -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 24, 2001 9:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: BUNYAMIN POSTS HAVE A VIRUS Well, I sent him an email about this last night, and no reply yet. Or was it this morning? Anyway, it was dark. :) I guess he gets unsubscribed now. :( Jared
RE: Another Database Down
Title: Another Database Down No, I did not know about this. Could you explain or tell me where I can find info on this? Laura -Original Message-From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]]Sent: Thursday, May 24, 2001 3:51 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Another Database Down Have you tried removing/moving the lk file in the $ORACLE_HOME/dbs directory ?? Lee
Virus Warning
Title: Virus Warning I received an email from Bunyamin K. Karadeniz in response to an earlier 'Database Down' message that I had sent to the list. The email from him had an attachment to it. I opened it (I know this was not wise) and our virus scan had deleted the file and left a message stating the file had a virus called '???'. Just wanted to alert everyone. I have had 3 emails from him so far, all with the virus attached. Laura
RE: Database Down
Title: RE: Database Down Whoopsby all means...LOL -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 23, 2001 6:31 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database Down -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] Amen Sister!! I am afraid you all will have to be my virtual party people, since I am the only one around here that got excited when the recovery was done...so have a great time!! Laura :) Thank you for admitting me to the sorority. But I'm not ready yet for the operation. Can I go on being a man a while longer? Jacques R. Kilchoër x8816
Another Database Down
Title: Another Database Down Well, I think the time for jubilation has been short-lived for me. The production database was recovered successfully due to archiving and luck. I also have a small, inhouse, remedy (help desk) database that I haven't done anything with and now recovery is necessary for it as well. The bad news is I wasn't archiving, and although I do have a physical backup, the database was not down. I restored the datafiles, control files, and redo logs so that everything would be for the same time, but when I try to open the database I get the error "cannot mount database in exclusive mode." The error states that an instance already has it open in exclusive or parallel mode. I am assuming this is because the database was not closed when the backup was executed. I have tried a few things to 'trick' it, but I guess my magic tricks are not good enough to fool the crowd. I am already bracing myself for what I will probably read in response to this...but I am hopeful that someone will find that rabbit in the hat. Thank you in advance, Laura
RE: Database Down
Title: RE: Database Down Amen Sister!! I am afraid you all will have to be my virtual party people, since I am the only one around here that got excited when the recovery was done...so have a great time!! Laura :) -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 23, 2001 3:12 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database Down >-Original Message- >From: Burton, Laura L. [mailto:[EMAIL PROTECTED]] > >My database is up and with no data loss!!! Congratulations! I've done the exercises in Rama Velpuri's book, but when it comes to a live situation, it's a little more scary. So where's the party?
RE: Database Down
Title: RE: Database Down My database is up and with no data loss!!! I was able to do a complete recovery, not incomplete. After the coca-cola (for caffeine) to calm my nerves I had the sysadmin restore the datafiles and archive logs from the hot backup from Friday. The control files 1 and 2 were restored because they were on the corrupted disks, but I had a 3rd copy which I used to recover the database. It worked just like Case 4 and all is well. Just a note, I am glad I had another copy of the control file. When I tried to use the file that had been restored I received an 'old controlfile' error. I do have the Backup and Recovery book by Rama Velpuri (v7.3) and it helped. This is one of those things I have wanted to do but have never had time to do... It has been about 3 years since I took the class and the 'use it or lose it' scenario has taken over. Thanks to all who replied!! Laura -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 23, 2001 11:32 AM To: Multiple recipients of list ORACLE-L Subject: Re: Database Down On Wednesday 23 May 2001 01:00, Paul Drake wrote: > > 0. caffeine. Agreed. > 2. You *will* be performing incomplete recovery. Recover until cancel, > preferably with a backup controlfile. > Did you create a backup controlfile as part of the hot backup on May > 11th? Restore with that controlfile. Is there a particular reason for recommending the backup controlfile? Shouldn't be needed unless the controlfile has been lost. > 3. After the recovery is completed (you've opened RESETLOGS) close the > database and get a *full* cold backup. Fight the urge to *get the Some repetition for emphasis here. As Paul says, a cold backup must be completed before any work is done on the database. Some add'l advice for Laura. Read Rama Velpuri's book 'Oracle Backup and Recovery Handbook' cover to cover, and do all of the exercises. It will make an immense difference in your understanding of recovery. Backup is easy, recovery is not always so. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
Database Down
Title: Database Down I have an Oracle 8.0.5 database residing on a Windows NT operating system which uses Raid5. The 'almost never' has happened; two disks have gone bad at the same time. As fate would have it the 'complete' physical backup performed the day before the disks crashed (Friday, May 18) cancelled. Of course this happened on Saturday on a weekend in which I was out of town and did not get back in until late this afternoon. My system admin said he restored the drives on the bad disks from a physical backup created the previous Friday (May 11) and then restored the differential backup from this past Thursday (May 17). The Archive files were also on one of the disk that went bad. I have looked at the alert log and the hot backup executed on the Thursday prior to the Saturday crash completed successfully. Something that does not seem right, and I'll talk with my system admin tomorrow, is that when I looked at the datafiles and archive logs he restored they were all dated May 11, the date of the complete backup. If he applied the differential as he said, does this not add the archive logs written from May 11 through the May 17 differential backup? According to the alert log it looks like I am missing approximately 438 logs. I thought I would have to restore all datafiles and archive logs from the physical backup so that they would be in sync, and then 'recover' the database using the hot backup. This would only incur minimum data loss since Saturday is a non-work day for most employees. After reading the Backup and Recovery manual it looks like all I have to do is 'recover' the database using the hot backup. Wouldn't it matter that the datafiles would not currently be in sync since the datafiles on the disk which did not crash were ok and not restored? Since the hot backup would restore all the datafiles I would think it wouldn't matter. Correct?? I would appreciate some second and third opinions. This is my first 'live' recovery and I want to make sure as much as possible that I do what will have the least impact on data loss. It goes without saying, but I will have a complete backup of the 'good' disks before I tackle this beast. Thank you in advance, Laura
