RE: Why does it take so long to write archive logs
the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Carlson 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: Rodrigues, Bryan 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: John Carlson 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: Why does it take so long to write archive logs
I had a similar problem on 9.2 and, just as you describe, I could watch the file on the remote location and the byte count would show the whole file was there but the alert log file would not show the archive complete for as long an one hour. When it did finally complete, the byte count on the remote file did not change. I worked with Oracle for several month before they finally convinced me it was a network problem. I do not really know anything about networks so I cannot really help you but our Unix admin guys made some changes in the network and the problem went away. Sorry, I cannot give you more info because we were also in the process of changing out a lot of hardware including network hardware so there is no magic parameter I can tell you to change. Just talk to your network gurus and hopefully, they can figure it out. HTH, John -Original Message- Sent: Monday, July 07, 2003 3:34 PM To: Multiple recipients of list ORACLE-L Hello everyone, We have an 8.1.7.4 on HP-UX 11.0 OPS database that uses 200mb archive logs with 3 archive log destinations, the first destination is local to the machine which is mandatory, the second destination is a filesystem accessible via nfs which is optional and the third destination is a remote standby database accessible via a vpn which is also optional. We have 10 archive processes to take care of the writing of the archives. Both the local destination and nfs mounted filesystems are on a HP XP256 storage device. This morning there was a process running that would update a table that is used for a catalog of parts. The process was producing archive logs, but the archives were taking around 10 minutes to write. During the process we would see the file created with the expected byte count of the file within the first minute, but the timestamp of the file would indicate access for the next ten minutes. We have two other 8.1.7 databases that are setup in a similar manner (that are not OPS) that have 2 destinations, one local and the other being sent to a remote standy database. We are not seeing the same type of issue with the archive logs. Can someone point me in the direction of either information or advice about why the archives may be taking so long to write? Is it that we have 3 destinations and it waits until all 3 destinations are taken care of? Why does the archive log file appear to be modified even though the byte count hasn't changed? TIA, Bryan Rodrigues Elcom, Inc. Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodrigues, Bryan 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: John Carlson 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: HP-UX 11i/8.1.7.4/login.sql
The way I do it is to create another login.sql script for batch jobs only and place that in another directory. I have also created a file I call .cronenv under $HOME. This sets environment variables needed for cron jobs. In it, I set SQLPATH so the first place it looks is the directory I placed my new login.sql file. SQLPATH=$HOME/cron_out:.:$HOME/dbacommon/tools/sqlscripts:$SQLPATH Then I put: . $HOME/.cronenv /dev/null 21 into all my batch jobs. This works for me. HTH, John -Original Message- Sent: Tuesday, March 25, 2003 9:29 AM To: Multiple recipients of list ORACLE-L Greetings Everyone! I have a LOGIN.SQL script that I've customized to fit my preferences. However, when I use SQLPLUS /NOLOG, it fails miserably since there is no connection to the database. Is there a way - other than undefining ORACLE_PATH - that the LOGIN.SQL script can be skipped or ignored when using the /NOLOG parameter? Thanks, Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) 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: John Carlson 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: lsnrctl status
') 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: Henry Poras 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: John Carlson 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: SQLPlus Substitution Variables
To expand on your description of define, I thought Erik may be looking for a way to pass the variables in. Maybe this will be of some use. sqlplus id/pwd @test 'a b c' 'd e f' Where test.sql is: define field1 = '1' define field2 = '2' select 'field1', 'field2' from dual; HTH, John [EMAIL PROTECTED] 01/15/03 07:43AM I'm not sure this is what you wnat, but here is a short example of the use of DEFINE / UNDEFINE and (Accept once but use many times inside script without re-prompt) -- define testtyp = Repair / select * from buy_type where buy_typ = 'testtyp'; select buy_typ||'---' from buy_type where buy_typ = 'testtyp'; undefine testtyp / HTH D. Phillips - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 15, 2003 10:04 AM I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams 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: David L Phillips 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: John Carlson 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: Purging Managed Standby Database Archive Logs
Keep it simple. If you are on unix put this in your crontab.
30 * * * * find /archive/oradata/YOURSID -type f -name 1_*.arc
-mtime +0 -exec rm -f {} \;
This will run every hour on the half hour and purge everything over 24
hours old.
Modify as you like. (-mtime +1 gives you 48 hours) Yes, I know this
could delete files that haven't been applied but I figure if your
standby is 24 hours behind, it will need to be re-established anyway.
HTH,
John
[EMAIL PROTECTED] 12/12/02 08:04AM
Any one have a ready-made routine to purge the unneeded archives which
have
been automagically applied to a managed standby database?
I figure it needs to:
1. Query v$archived_log and v$log_history to get a list of the archive
logs
(v$archived_log.name) where sequence# [the max number you purged the
last
time];
2. Cycle through the above list and remove the files;
3. Record the max(sequence#) from v$log_history for the next purge.
Any other ideas/suggestions?
AtDhVaAnNkCsE!!!
Steve Orr
Standing by in Bozeman, Montana
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Carlson
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: LGWR using lots of CPU time, low CPU usage
I see there has already been a lot of discussion on this topic. I would like to throw out one more possibility. It could be related to bug 2564886. If you read the bug on metalink, it probably won't make any sense because it is written for a specific customer. However, I have a similar problem and Oracle has classified my tar as related to this bug. Basically, if you use more than one log_arch_dest occasionally one of the archive process will just take forever. You didn't mention if you were using that parameter or if you are using a standby database so it may not apply to you. While oracle is working on this bug, we have disabled the second log_arch_dest and we have a script to manually check every minute and copy the archive logs to the other destination. This has helped us. Maybe it can help you to. We are on Sun Solaris 7 with 9.2.0.1 but the bug goes back to 9.0.1.3 so it probably applies to 9.2.0.2 also. HTH, John [EMAIL PROTECTED] 11/26/02 10:00AM We are on 9.2.0.2, Solaris 8 on Sunfire 3800 with 16 GB memory and 128 MB on a hardware-controlled, mirrored RAID5 StorEdge T-3 Array. Periodically throughout the day the LGWR background process clocks 20+ minutes of CPU time while actual CPU usage is quite low. I ran a statspack report and for a 45-minute period that included the slow LGWR process. The top 5 timed events in my 45-minute report are: CPU time 1,295 60.41 db file sequential read 392,516 341 15.91 db file scattered read 70,245 168 7.85 log file sync 26,916 133 6.22 library cache pin 22 59 2.76 (Now that the top 5 is timed events, 3 spots almost always include CPU and the db file reads, so I only get two other events, usually log file sync, sometimes enqueue or latch free.) Statspack also shows the log file parallel write had 28,589 timeouts in that 45 minute period--rather typical for us. I have session_cached_cursors set to 150. I am considering the following: 1. Removing my own redo log duplexing (mirroring) since redo logs are on the mirrored, hardware-controlled RAID5 disk array. (I know, I know) My sysadmin talked to the sun engineer yesterday and he said this is old school thinking that redo logs should not be on RAID5. He said because the RAID controller caches to memory all IO requests from the CPUs, all physical writes to disk are done behind the scenes (known as writebehind). He says the system is NOT waiting for IO. 2. Increasing redo log size (again). For the most part, log switches average 2.5 per day, although there were 20 times in the last month of 3-7 switches in a half hour. My logs are about 100 MB in 2 groups of 20 members each. 3. Upping the session_cached_cursors to ? (in response to the library cache pin event). Or is there a better option I'm overlooking? I would appreciate some advise on the best approach to resolve the slow LGWR process, especially your thoughts on option 1. Thanks, Debi Deborah Lorraine, DBA University of California, Davis [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deborah Lorraine 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.com -- Author: John Carlson 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: Partitioning
You can do the same thing in Oracle as you did in Informix. Create range partitions for 0-9 and use your mod 10 on the key. I believe you will have to add a column in the table to hold the mod number and make that the partitioning column. HTH, John [EMAIL PROTECTED] 11/19/02 11:38AM We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Carlson 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: Unix Q: Substring-ing an output.
Try :
ps -ef|grep pmon|grep -v grep | awk '{ print $NF }' |awk -F_ '{ print $3 }'
The 'NF' is number of fields and will give you the last field regardless of the
number. I use this technique all the time. If you don't want the last field, you can
use: awk '{ i=NF-1; print $i }' where -1 is the next to last field.
HTH,
John Carlson
www.cj.com
[EMAIL PROTECTED] 10/28/02 03:24PM
Very clever! But unfortunately, -F accepts only 1 character. I tried that
though...
Ross
-Original Message-
From: Henry Poras [mailto:hporas;etal.URI.EDU]
Sent: Tuesday, 29 October 2002 3:43
To: Multiple recipients of list ORACLE-L
Subject: RE: Unix Q: Substring-ing an output.
Quick thought. What about just setting awk -F_ to awk -Fpmon_ ???
Henry
-Original Message-
Bobak
Sent: Sunday, October 27, 2002 11:49 PM
To: Multiple recipients of list ORACLE-L
Hmm...good point. You know, I use this technique in various
places. I
never noticed that bug before, cause it will only appear if
the database
has been up less than 1 day. Guess it's time to do a bit of script
auditing.
Ok, try this instead:
ps -ef|grep pmon|grep -v grep|cut -c49- |awk -F_ '{ 'print $3 }'
I replaced the first awk w/ cut. the -c option says to cut
that output
from position 49 to the end of the line. You may have to adjust the
value from 49 to something else.
That ought to work for you.
-Mark
On Sun, 2002-10-27 at 22:53, Ross Collado wrote:
Thanks Mark.
Yes that helps, in a way. I could use good ol' awk to parse the last
field!
However, a slight problem in the ps -ef output. If I do
what you have
suggested on my 'ps -ef' output below, I would only get
RMAN as it is the
only one in field #8. All the rest are in field #9. All I
need now is
figure out how to cut the last field then pipe it to your
awk command.
Ok I'm getting somewhere!
Rgds,
Ross
-Original Message-
From: Mark J. Bobak [mailto:mark;bobak.net]
Sent: Monday, 28 October 2002 13:54
To: Multiple recipients of list ORACLE-L
Subject: Re: Unix Q: Substring-ing an output.
As is often the case, there are a million ways to do
this. Given the
output listed, I'd do something like:
ps -ef|grep pmon|grep -v grep|awk '{ print $8 }'|awk -F_ '{
print $3 }'
ps -ef -- get the programs that are running
grep pmon -- get only those lines that have 'pmon' in them
grep -v grep -- drop out the line that has 'grep pmon'
awk '{ print $8 }' -- get the eighth column, the program name
awk -F_ '{ print $3 }' -- parse the program name on the
'_' and return
the third field, the database name.
Hope that helps,
-Mark
On Sun, 2002-10-27 at 20:43, Ross Collado wrote:
Hi All,
I want to feed my shell script with the names of
currently running
databases. I thought of using ps -ef|grep [p]mon. What I
got was the
following:
oracle 20113 1 0 Oct 25 ?0:01 ora_pmon_TLDEV
oracle 898 1 0 Jul 22 ?0:06 ora_pmon_TLQA
oracle 944 1 0 Jul 22 ?0:07 ora_pmon_TLQAVAR
oracle 19588 1 0 Oct 25 ?0:00 ora_pmon_DBMON
oracle 13509 1 0 12:16:13 ?0:00 ora_pmon_RMAN
oracle 20450 1 0 Oct 25 ?0:00 ora_pmon_PRDINF
oracle 13026 1 0 Oct 26 ?0:00 ora_pmon_TLDVVAR
What I wanted is get only the db name part eg. TLDEV, RMAN,
DBMON,etc. I
don't want to rely on oratab file.
I was thinking of using 'cut' to cut out the last field and
do some ${X##}
(variable pattern substitution) to get to the dbname bit.
The trouble is
the number of fields in a ps -ef output is not consistent.
As you can see
I've just restarted RMAN and now it only has 8 fields as
compared to 9 for
the others.
Any suggestions? Or another way of doing it?
Using KSH on Solaris 8.
Thanks.
Ross
--
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
It is not enough to have a good mind. The main thing is
to use it
well.
-- Rene Descartes
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark J. Bobak
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).
--
--
Mark J. Bobak
Oracle DBA
[EMAIL PROTECTED]
It is not enough to have a good
Re: Listener Log Aging Script
I posted this answer once before. How about wrapping this is a script of your choice: ARCHIVE_LISTENER_FILENAME=listener`date +%Y%m%d%H%M`.log lsnrctl set log_file listener2.log mv listener.log $ARCHIVE_LISTENER_FILENAME mv listener2.log listener.log lsnrctl set log_file listener.log gzip $ARCHIVE_LISTENER_FILENAME This way, you don't have to stop the listener and you don't loose anything. Remember, in Unix, when you rename a file, any program that has it open still points to it. HTH, John [EMAIL PROTECTED] 07/24/02 08:50AM Could anyone share their Unix script to age the listener log file? I know that this has been posted to the group in the past, but I was unable to find it in the archives at FatCity. Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams 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: John Carlson 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 Trigger to control PK change
I am trying to do something similar. I want to trap when new tables and procedures are created so I can issue grants and synonyms. I found in the manual triggers at the schema level but I haven't been able to make them work. From the manual CREATE OR REPLACE TRIGGER On_DDL after DDL ON XYZ.Schema BEGIN ... If anyone has an example of using this type of trigger, I would appreciate some help. I am using 8.1.6.3 and the above is taken from the 8.1.6 manual. Thanks, John [EMAIL PROTECTED] 04/03/02 01:33PM Hi List, I want to create a trigger that insert into a table anytime a user change or compile a package doesn't matter who create it or compile it. Is there a trigger at DB level, like the on logon, that I can create to keep this kind of control. I did some FM but couldn't find nothing. TIA Ramon E. Estevez -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Carlson 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 to calculate time using SQL
Here is a script I recently made to help me understand how the date worked.
REM Elapsed Time
define start_time = '03-25-02 11:17:12'
define stop_time = '03-27-02 12:36:30'
define date_fmt = 'mm-dd-yy hh24:mi:ss'
select trunc(to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')) Elapsed: Days,
trunc(mod((to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')),1) * 24) Hours,
trunc(mod( 24 * (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')),1) * 60) Minutes,
mod( (24*60) * (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')),1) * 60 Seconds
from dual
/
-- The following show how the above query is broken down.
-- The mod or remainder of each calc is multipled to get time.
select (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')) Elapsed Days
from dual
/
select (24) * (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')) Elapsed Hours
from dual
/
select (60*24) * (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')) Elapsed Minutes
from dual
/
select (60*60*24) * (to_date('stop_time', 'date_fmt')
- to_date('start_time', 'date_fmt')) Elapsed Seconds
from dual
/
Run it and see how it works and then experiment yourself. One thing I haven't tried
is 's'. The book says it is 'seconds past midnight'. I guess if you had a job
that never ran past midnight, you could just subtract the begining time from the end
time and get seconds that way.
Of course, you can 'set timing on' but that is only good for each step. If you have a
job that has several sql statements and you want to get the total time, you need
something like what I have done.
HTH,
John
[EMAIL PROTECTED] 04/01/02 07:48AM
Is it possible to calculate seconds using SQL?
For example, I'd like to subtract these two time to get difference in
seconds:
10:20:32 - 10:25:29
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 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: John Carlson
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: KSH Scripting Questions
I use this in one of my scripts. Is this what you are looking for? # Make sure temp file get removed. trap rm -f $file; exit 0 1 2 3 4 5 6 7 8 10 11 12 13 15 16 17 HTH, John [EMAIL PROTECTED] 12/10/01 08:55AM I have a kshell script that I am trying to perform an oracle operation. Everything works fine in the script excpet cleanup. One section of the script spools off a monitor job that watches the script for any failures by use of flag files and file checking. This all works well UNTIL the main program ends before the monitor programs. What happens is , that when the main program ends, the monitor closes down because it was spawned by the main program. Because of this, no all the flag files are getting deleted. I need to know the proper trap to set and its syntax to catch an ending program so that I can get it to delete the flag files. Anyone have any ideas ?? Thanks Kevin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange 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: John Carlson 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: KSH Scripting Questions
Yes, you can create a function in your program and call it or call another program.
my_function ()
{
clean this
clean that
}
trap my_function 0 1 2 ...
You can have different cleanup routines for different events or one cleanup routine
and pass it the event type as:
trap my_function 1 1
trap my_function 2 2
.
.
.
Good Luck,
John
[EMAIL PROTECTED] 12/10/01 11:30AM
Yes. This should remove the files if any problems occure.
Can you call a subprogram in the trap line or does all the code need to be
in-line ?? It would be great if I can just call 1 cleanup routine.
-Original Message-
Sent: Monday, December 10, 2001 12:30 PM
To: Multiple recipients of list ORACLE-L
I use this in one of my scripts. Is this what you are looking for?
# Make sure temp file get removed.
trap rm -f $file; exit 0 1 2 3 4 5 6 7 8 10 11 12 13 15 16 17
HTH,
John
[EMAIL PROTECTED] 12/10/01 08:55AM
I have a kshell script that I am trying to perform an oracle operation.
Everything works fine in the script excpet cleanup.
One section of the script spools off a monitor job that watches the script
for any failures by use of flag files and file checking. This all works
well UNTIL the main program ends before the monitor programs.
What happens is , that when the main program ends, the monitor closes down
because it was spawned by the main program. Because of this, no all the
flag files are getting deleted.
I need to know the proper trap to set and its syntax to catch an ending
program so that I can get it to delete the flag files.
Anyone have any ideas ??
Thanks
Kevin
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Lange
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: John Carlson
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: Kevin Lange
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: John Carlson
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: sanity check
Yes you can move or copy the init and config (if you have one) to the new version directory and start the db. Don't forget your environment variables. I would suggest changing your oratab file to point to the 817 directory. That way you can use oraenv, coraenv and dbhome to ensure your environment is set. Also if you have any home grown scripts which set environments, check them out. Don't forget about sqlnet/net8. Check your listener.ora file, or if you run multiple listeners, check your tnsnames file for the correct port. After it is up and running, don't forget to run any conversion scripts required to migrate from one version to the other. Disclaimer: my advice is for Unix only, if you are on NT, I haven't a clue. Good luck, John [EMAIL PROTECTED] 11/16/01 10:00AM i have a development server with 815, 816, 817 installed [don't ask.]. we have suspended development at this point in time and shut down the server. AFAIK, if i move the correct initSID.ora files into the dbs directory for 817, it should read the control files for the other versions and open the databases. am i missing anything here? and yes i know i could just try it and i would if i was given the chance. [again don't ask.] -- -- Bill Shrek Thater ORACLE DBA Telergy,Inc. [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. This login session: $13.76, but for you: $11.88. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William 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: John Carlson 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
Here is a script I use all the time to run jobs for other users. It saves the current
password, sets the password to temp, connects to user, and sets it back to original
setting. Now you are logged in as the new user but their password was only changed
for less than a second. The script also uses an idea I borrowed from Steve Adam's
Ixora site to save and restore the sqlplus settings. (Thanks Steve).
Cut Here
@save_sqlplus_settings
set termout off echo off pause off
REM connect_as.sql
REM If you are currently connected as a user with 'alter any user'
REM privilege, this will connect you as any other user.
REM usage: @connect_as new_user_id
REM 11/07/1998 - John Carlson
REM 11/16/2000 - John Carlson (Oracle V8.1.6)
REM Added save and restore settings scripts which use new
REM sqlplus 'store set' command.
whenever sqlerror exit sql.sqlerror
col password NEW_VALUE save_pass
define new_user=1
SELECT password
FROM dba_users
WHERE username=upper('new_user');
whenever sqlerror continue
ALTER user new_user identified by temp;
CONNECT new_user/temp
ALTER user new_user identified by values 'save_pass';
undef 1
undef save_pass
undef new_user
set termout on
show user
@restore_sqlplus_settings
Cut Here
HTH,
John
[EMAIL PROTECTED] 10/24/01 07:10AM
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Carlson
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: database link questions
Can you be more specific? I am curious also. When I create a link to a database on 'xxx' it comes out 'xxx.cj.com' which is correct but I began looking around and I don't see how it is working. In our initxxx.ora file we have 'db_domain = WORLD'. In the sqlnet.ora file we have #NAMES.DEFAULT_DOMAIN = world# NAMES.DEFAULT_DOMAIN = cj.com As you can see, both are commented out. We are on Sun Solaris and I did find /etc/resolve.conf with: domain cj.com.search cj.com. Is that where it is coming from or is there some other place to look. Also, if I change the global name like: alter database rename global_name to test; Then is comes out as 'TEST.CJ.COM'. Since it is clearly ignoring my oracle parameters, it must be an OS level thing. And, since it has always worked for me, I never really questioned it. Now that Kathy has asked about it, I am sure there are many of us who are curious about it. One more thing, I couldn't find any reference to it in the installation guide. TIA, John [EMAIL PROTECTED] 09/24/01 06:30AM The extension on a database link is always the domain of the database it iscreated in.HTH,Ruth- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Friday, September 21, 2001 6:16 PM I try to create a database link named Custom, it ends up as: Custom.us.oracle.com when I query the dba_db_links table. I don't want the .us.oracle.com extension. What can I do, where does this extension come from? Global Names? I see that my global name is TEST.US.ORACLE.COM Do I need to alter global names so I don't get this extension? Is there something else I can do? Thanks, Kathy Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret 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: Ruth Gramolini INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: database link questions
As I stated, the initxxx.ora contains 'db_domain = WORLD'. In fact, all our databases init.ora files contain the same 'db_domain = WORLD' and yet all links and global names come out with 'cj.com' on 8 different physical boxes. Is there someplace else you are asking about? I don't know where else to look. John [EMAIL PROTECTED] 09/24/01 12:20PM What is the domain of the database in which you are creating the link?Ruth- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Monday, September 24, 2001 2:55 PMCan you be more specific? I am curious also. When I create a link to adatabase on 'xxx' it comes out 'xxx.cj.com' which is correct but I beganlooking around and I don't see how it is working. In our initxxx.ora filewe have 'db_domain = WORLD'. In the sqlnet.ora file we have#NAMES.DEFAULT_DOMAIN = world# NAMES.DEFAULT_DOMAIN = cj.comAs you can see, both are commented out. We are on Sun Solaris and I didfind /etc/resolve.conf with:domain cj.com.search cj.com.Is that where it is coming from or is there some other place to look.Also, if I change the global name like:alter database rename global_name to test;Then is comes out as 'TEST.CJ.COM'.Since it is clearly ignoring my oracle parameters, it must be an OS levelthing. And, since it has always worked for me, I never really questionedit. Now that Kathy has asked about it, I am sure there are many of us whoare curious about it.One more thing, I couldn't find any reference to it in the installationguide.TIA,John [EMAIL PROTECTED] 09/24/01 06:30AM The extension on a database link is always the domain of the database it iscreated in.HTH,Ruth- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Friday, September 21, 2001 6:16 PM I try to create a database link named Custom, it ends up as: Custom.us.oracle.com when I query the dba_db_links table. I don't want the .us.oracle.com extension. What can I do, where does this extension come from? Global Names? I see that my global name is TEST.US.ORACLE.COM Do I need to alter global names so I don't get this extension? Is there something else I can do? Thanks, Kathy Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret 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: Ruth Gramolini INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ruth Gramolini INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: time synchronization
Oracle is designed to handle time changes. For example switching to and from daylight savings. This is because the archive logging is not dependent on a date. It has its own sequence. Therefore changing the date and time even while oracle is up is no problem. I have done this many times for testing. The only thing to watch for is if you are using DBMS_JOBS. Changing the time may cause a job not to execute or to run when you don't want it to. Just make sure there is no time conflict with any of these jobs and also 'cron' jobs and go ahead and change the time. I believe your backup strategy is an overkill which I personally would not do but you decide if you would feel safer with the backups, then go ahead with your plan. HTH, John [EMAIL PROTECTED] 09/18/01 08:46AM Hi List,we have about 2 min difference between the time on our db server and therest boxes in our network.Actually, the time on DB server is 2min slow than the network time.It causesreal pain to us.I think about the following order of time synchronization:1. Normal shutdown the DB and listener2. immediate cold backup2. setup time synchronization on the unix level3. startup the DB and listener4. Normal shutdown DB and listener5. immediate cold backup6. startup DB and listenerIs this correct ? Any possible troubles? Any good links?Thanks in advance,Ed-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Shevtsov, Eduard INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: !! *Very* important Oracle-L message !!
As I see it, he needs $500 per month for 4 or 5 months. Lets just round that to 6 months times $500 or $3000. I don't know how many people are on his 60+ mailing list but just guessing, I believe if only 1/4th the people contributed $1 per month for 6 months or just $6 that would cover it. I am willing to donate up to $5 per month for up to 6 months paid either monthly or a lump sum depending on what he needs. Just like in Public Television or Radio, I challenge everyone on the list to join me with an equal pledge. Regards, John Carlson [EMAIL PROTECTED] http://www.cj.com [EMAIL PROTECTED] 08/28/01 03:28PM Ok, now that I have your attention, please read the following message fromBruce Bergman,owner of fatcity.com. It concerns the future of this list.Please read the entire message before replying.Thank you.Jared Still ( List Owner )---Hello ORACLE-L list members --I would like to take a few minutes of your time for a "State of the Union"address, if you can call it that. This is a rather serious message, and Irequest that you take the time to read this message entirely. I've got aproblem that needs resolution this week, and I'm hoping you folks can helpme come to a conclusion. This message turned out to be fairlylong-winded, so please excuse me for that. :-)Fat City (fatcity.com) is my company, and I am the provider of the listservices that you receive ORACLE-L through. I've been hosting Internetmailing lists for over 10 years, and carry quite a few lists, including abig selection of Oracle-related lists (14, in fact). Fat City has alwaysbeen a labor of love for me. Over the years it has rarely generated anyincome at all, and it usually does not cover my expenses -- most of thetimeit is a monthly loss for me. The vast majority of my 60+ mailing listshavebeen hosted for free, at my expense (as is the ORACLE-L list, for example).Yet I have absorbed these costs because I enjoy hosting discussion listsand I know a lot of lists can't afford to pay for quality service.I am soon to be one of the Rhythms Orphans. Rhythms, my DSL provider, hasgone bankrupt and will be out of business by 10-Sep-2001. I need to findanother provider of high-speed Internet in order to continue operations. Iam at the point of either folding shop completely and going out of businessmyself, or trying to make Fat City a viable, money-making endeavor. I needto make this decision by this Friday (for financial and planning reasons).After Friday, I am committed one way or the other.My upstream ISP and I are on very good terms, and in fact, I've been acustomer of theirs since 1985! As a result, they have graciously offeredto help me out of this situation at considerable expense to themselves.I'm not at liberty to discuss the terms of the contract I would engage inwith them, but suffice to say that is one of those once-in-a-lifetimeopportunities that I'd be a fool to pass up. I have this offer personallyfrom the President of that company, if I want it. Simply said, my abilityto take them up on their offer and switch Fat City over to a dedicated T1orbetter solution revolves around being able to generate quite a bit ofadditionalincome per month, and very soon. In an effort to be open and up-front withyou folks about this, I basically need to generate an additional $500 morepermonth. Right now that seems daunting, but I've been working hard to line upthatadditional income, and I believe I can do it. Just not right away. Myestimates are that it will take me four or five months to come up to thatlevel. I need to get out there and sell website hosting and list hostingpackages, and get new customers. That takes time, unfortunately. I need abuffer to enable me to get past this startup phase.After some discussions with Jared (the list owner), and some encouragementfrom him, I am coming to you to ask for you help in keeping Fat Cityfinancially afloat until I can be self sufficient. I'm not asking for aspecific dollar amount from anyone, nor am I *expecting* anything from youfolks. But if it is within your power to contribute some money towardsthisend (and yes, I know these are hard financial times for everyone), I amhoping you can help. If I can get sufficient promise of funds that willseeme through the next few months, I am willing to jump in with both feet andmake Fat City a viable business. If it looks like the contributions arenotsufficient to enable me to do that, I will cease operations within the next45-60 days.Let me be clear about a few things: If I *do* cease operations, I will giveall of my lists time to find new list providers, and will make every effortto aid the shift over to the new provider, provide list dumps, archives,mail forwarding, etc. Even though Rhythms goes out of business on10-Sep-2001, I have secured service until at least 10-Oct-2001 to enablethis to happen. So don't worry about having to rush
Re: alert log is not capturing all errors
The alert log does not capture anything it considers to be a user error. HTH, John [EMAIL PROTECTED] 07/12/01 12:35PM Hi List,I have encountered a little strange incident. Here is a brief:I ran a package which runs every day early in the morning. I create a logof the process through the spool command.Today my package didn't run. It had an error saying 'ERROR at line 1: ORA-04068: existing state of packages has been discarded' . Anyway, not abig deal. I think I eliminated the error.The thing that puzzles me is that error IS NOT in the alert log. I had animpression that alert log would capture ALL of the errors occurring on thesystem. Isn't it the case?Thank you for your support.Lyuda Hoska-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Perl DBA Tools
If you are keeping any kind of statistics on connections using the listener.log, you have the potential of missing records between the time you copy it andempty it to /dev/null. With my method, you won't loose any records. The choice is yours. HTH, John [EMAIL PROTECTED] 04/25/01 12:55AM John, Im my script for managing the listener.log, I copy the listener to an archive area and then cat /dev/null to to current one. Will this have the same effect as moving as in your example ? Do I need also to force the listener to point to a different log file ? Maybe I need to test some more !! Regards Lee Robertson -Original Message-From: John Carlson [mailto:[EMAIL PROTECTED]]Sent: 24 April 2001 19:47To: Multiple recipients of list ORACLE-LSubject: Re: Perl DBA Tools How about wrapping this is a script of your choice: ARCHIVE_LISTENER_FILENAME=listener`date +%Y%m%d%H%M`.log lsnrctl set log_file listener2.logmv listener.log $ARCHIVE_LISTENER_FILENAMEmv listener2.log listener.loglsnrctl set log_file listener.loggzip $ARCHIVE_LISTENER_FILENAME This way, you don't have to stop the listener and you don't loose anything. Remember, in Unix, when you rename a file, any program that has it open still points to it. Regards, John [EMAIL PROTECTED] 04/24/01 10:20AM On Tuesday 24 April 2001 06:15, Rachel Carmichael wrote: truncating listener logs -- or even starting a secondary listener, stopping the one with the log that is too large and removing the log, then restarting the original listenerGood idea. Thanks RachelJared-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).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: Perl DBA Tools
How about wrapping this is a script of your choice: ARCHIVE_LISTENER_FILENAME=listener`date +%Y%m%d%H%M`.log lsnrctl set log_file listener2.logmv listener.log $ARCHIVE_LISTENER_FILENAMEmv listener2.log listener.loglsnrctl set log_file listener.loggzip $ARCHIVE_LISTENER_FILENAME This way, you don't have to stop the listener and you don't loose anything. Remember, in Unix, when you rename a file, any program that has it open still points to it. Regards, John [EMAIL PROTECTED] 04/24/01 10:20AM On Tuesday 24 April 2001 06:15, Rachel Carmichael wrote: truncating listener logs -- or even starting a secondary listener, stopping the one with the log that is too large and removing the log, then restarting the original listenerGood idea. Thanks RachelJared-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Listener.log
For those of you who did not see my response in the thread of "Perl DBA Tools" I will repeat it here, it answers this thread as well. Also, there is no need to shutdown the database to bounce the listener. How about wrapping this in a script of your choice: ARCHIVE_LISTENER_FILENAME=listener`date +%Y%m%d%H%M`.log lsnrctl set log_file listener2.logmv listener.log $ARCHIVE_LISTENER_FILENAMEmv listener2.log listener.loglsnrctl set log_file listener.loggzip $ARCHIVE_LISTENER_FILENAME This way, you don't have to stop the listener and you don't loose anything. Remember, in Unix, when you rename a file, any program that has it open still points to it. Regards, John [EMAIL PROTECTED] 04/24/01 02:28PM You can shutdown/start the Database and start listener would solve ur problem.-SeemaFrom: "Adams, Matthew (GEA, 088130)" [EMAIL PROTECTED]Reply-To: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Subject: RE: Listener.logDate: Tue, 24 Apr 2001 11:16:49 -0800No, you do not have to restart the listener.touch the listener.log file (we do a 'cp /dev/null$ORACLE_HOME/network/log/listener.log)and the listener will start writing to it again.R. Matt Adams - GE Appliances - [EMAIL PROTECTED] Meddle not in the affairs of troff, for it is subtle and quick to anger.-Original Message-Sent: Tuesday, April 24, 2001 12:28 PMTo: Multiple recipients of list ORACLE-LHello,You have to restart your listener. It continues to write to "invisible" file(inode without any reference from directories). Welcome to UNIX.HTHVadim GorbounovOracle DBA-Original Message-Sent: Tuesday, April 24, 2001 11:47 AMTo: Multiple recipients of list ORACLE-LHello everyone,After moving the listener.log and compressed, there is no more log for thelistener. Could anyone help me please ? Thanks.Nguyen_Get your FREE download of MSN Explorer at http://explorer.msn.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Seema Singh INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Unix Cron job help
If you use "whenever sqlerror exit 9", then you can test the return code for either "0" or "9" just as you described. ( You can use any number you choose, 9 is just an example. ) HTH John Carlson http://www.cj.com [EMAIL PROTECTED] 02/27/01 06:46AM Can you send me shell script you are using.The success status from unix shellwhen it is executing sqlplus or svrmgrl will always be ZERO.So youcan't simply say if [ $? -eq 0 ] or something like that.The success dependson inner oracle processes.That won't give you exit status to shellSo you please send me the script.I'll see what i can doCheersGANTI-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Email/Paging/Mobile Phone alerts
I will make the assumption this is on unix and you can write a shell script. Here is a skeleton of the code. You can put your own checks it. EXAMPLE #!/bin/ksh tail -1f alert_x.log | while read linedo any other code you want here. echo $line | grep ORA- if [ $? -eq 0 ] ; then ( error=`echo $line | sed 's=:.*=='` echo "To: [EMAIL PROTECTED]" echo "Subject: `uname -n` $error" echo "`uname -n` alert_log" echo $prevline | sed 's=:=-=' echo $line | sed 's=:=-=' ) | mail [EMAIL PROTECTED] fi prevline=$line done This will run perpetually and check every line the alert log writes. You can test for any messages you want and send messages to whatever. Note the sed command changes ':' to '-' because mail has a problem with colons in the text body sometimes. Also, some mail servers cannot handle it properly without the extra "To:". Obviously, this is just a snippet of code. You need to customize it for your own needs. You may need to write other scripts to query the database to get number of connections and active connections if you need that. HTH, John Carlson http://www.cj.com [EMAIL PROTECTED] 02/22/01 03:15PM I am looking at a solution of sending a email/paging/Mobilephone alertsin case of a problem on the database that is reported in the alert.logfile. like tablespace full,no. of processes exceeded,instance going downetc.I want a solution other than OEM.What are the other ways of sending suchalert to notify the person who takes care of the database.Thanks-Ravindra-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Ravindra Basavaraja INET: [EMAIL PROTECTED]
