Please help resolving report generation performance problem...
Hi DBA's, I've been trying to isolate the bottleneck with our Oracle database. I work as an Oracle DBA for the Government of a developing country (Belize). Recently, as it is income tax time, the department has to reconcile all witholdings by the employer with their payment receipt records. This involves some data entry and a report generation by employer (witholder) which lists all witholdings by each employee. This report can generate lots of pages depending on the number of employees. In some cases, the report has to be run overnight, as it takes too long (several hours) to generate. I've tried giving more resources to Oracle. I've tried creating a copy of the production database on another machine to use only for generating reports. I've increased the size and number of rollback segments. I've tuned some parameters. However, I have not observed any significant improvement in the report generation performance. I know tuning the SQL might be required, however, I don't have much experience in this area. The SQL statemements were written by consultants who have long left. We do have the source code though. We are running Oracle 8.0.5.2.1 on NT 4.0 The NT server is a Dell 4400 with Dual CPU and 1GB RAM We are using hardware RAID 5. Our database is OLTP with reporting. It is a small database (exported data is about 150 MB). I would appreciate your recommendations and advice. Thanks in advance, Denmark Weatherburne _ Chat with friends online, try MSN Messenger: http://messenger.msn.com Report_SQL.zip Description: Zip compressed data The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. Hourly_Bstat_Estat_Reports.zip Description: Zip compressed data
ORA-12500 Listener error on Win NT
Hi DBA's Today, our production users started to get the following error at their Win NT Oracle 8.0.5. client workstations. ORA-12500 TNS Listener failed to start a dedicated server process I was not running Spotlight at the time and I could not connect to the instance using Server Manager. I had to stop the NT services and reboot the NT machine. That apparently resolved the problem. However, I'm concerned becasue this is the first time that I have seen this situation. I searched Metalink for related documents which point to the listener.ora configuration and memory resources, etc. I made no changes to the listener.ora. Perhaps someone may have encountered this error before and been able to isolate the problem. I would appreciate any feedback and advice. I'll paste the listener.ora here for your review: # E:\ORANT\NET80\ADMIN\LISTENER.ORA Configuration File:e:\orant\net80\admin\listener.ora # Generated by Oracle Net8 Assistant PASSWORDS_LISTENER= (oracle) STARTUP_WAIT_TIME_LISTENER=0 LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=oracle.world))(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))(ADDRESS=(COMMUNITY=NMP.world)(PROTOCOL=NMP)(SERVER=LAMANAI)(PIPE=ORAPIPE))(ADDRESS=(PROTOCOL=TCP)(Host=lamanai)(Port=1521))(ADDRESS=(PROTOCOL=TCP)(Host=lamanai)(Port=1526))(ADDRESS=(PROTOCOL=TCP)(Host=127.0.0.1)(Port=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=PROD))(ADDRESS=(PROTOCOL=IPC)(KEY=TEST))) CONNECT_TIMEOUT_LISTENER=10 SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=extproc)(PROGRAM=extproc))(SID_DESC=(SID_NAME=PROD))(SID_DESC=(SID_NAME=TEST))) TRACE_LEVEL_LISTENER=0 This NT server has 784MB RAM. The SGA information is as follows: SVRMGR connect internal@prod Password: Connected. SVRMGR show sga Total System Global Area 43773952 bytes Fixed Size 49152 bytes Variable Size35459072 bytes Database Buffers 8192000 bytes Redo Buffers73728 bytes SVRMGR Thanks in advance for your contributions! Regards, Denmark Weatherburne Belize _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
[no subject]
Hi DBA's Our production database has a tablespace with the following schema: CREATE TABLESPACE IRD_DATA_LARGE DATAFILE 'E:\ORANT\DATABASE\USR1PROD.ORA' SIZE 128472K AUTOEXTEND ON NEXT 2K MAXSIZE UNLIMITED LOGGING DEFAULT STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 1) ONLINE PERMANENT This tablespace has only 2MB of free space available. The fragmentation index is 58.7 The largest table is 29MB and has 5 extents. I've observed some segments with Max Extents different than 121 which is the default and how most of the segments were created. I know I need to add another datafile, however, I'd like to find out what would happen if a segment requested another extent that was larger than the available free space in the tablespace. Since AUTOEXTEND is enabled is there a possibility that this situation could cause the tablespace or instance to go offline or crash? Thanks for your comments and advice! Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
How to resolve Dr. Watson errors on Oracle clients when printing from forms...
Hi DBA's I have been visiting a client user site that is reporting frequent Dr. Watson Application Errors when submitting report generation request from Oracle Developer 6.0 forms on Oracle 8.0.5.0.0 client machines. I observed that the same user can logon to another NT workstation and generate/print the same report successfully. I don't know if it is a Windows problem or an Oracle forms problem. The Oracle RDBMS is 8.0.5.0.0 on NT 4.0. The forms were created using Oracle Developer 6.0. The clients (NT Workstations with SP6) have the Oracle client installed. Perhaps, some of you may have addressed this problem before. I would appreciate any feedback on this problem. Thanks in advance for your time. Regards, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Help tuning the SGA...
Hi DBA's/Developers, I've been monitoring our production Oracle database (8.0.5.0.0) on NT with Spotlight on Oracle. I've also read some documents relating to Oracle database tuning. I have identified some components that need to be tuned. One of them is the SGA. This is the current configuration: SVRMGR show sga Total System Global Area 26808320 bytes Fixed Size 49152 bytes Variable Size18493440 bytes Database Buffers 8192000 bytes Redo Buffers73728 bytes SVRMGR The buffer hit ratio is very poor (around 70%). I've already increased the value of DB_BLOCK_BUFFERS from 1000 to 4000, but I still have not observed any significant improvement. Of course I'll try increasing the value again to see if it has an impact. However, I believe there are other related parameters that might have to be tuned as well in order to achieve the desired results. Could you experts and Gurus please give me some advice on approaching the SGA tuning process. Our client/server application might be categorized as OLTP with some batch processing near the end of the workday. These are actually summary reports of cash collections during the day which are used for reconciling the bank deposits and for internal accounting control. However, it uses some full table scans and it reads lots of rows as a result the hit ratio drops to (4%) in some cases. This is a learning process for me, so I would appreciate some helpful advice. Thanks for your time. Regards! Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! This is after increasing _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: Info= Help tuning the SGA...
Hi Kevin, Thanks for responding! Actually, the reason I'm trying to tune is both, because some users have reported calculations that they have to abort after five minutes when normally the process takes about 5 seconds. The ratio I reported before is not accurate because it varies from 40% to 80% during normal working hours. But mostly it is on the low side of that range. I understand what you are saying though that if the users are noticing a response time degradation then something is wrong. I have been taking a Server side view of the tuning process I think. Also using the monitoring tools that I have installed and scheduled, they point to required improvement of some key RDBMS ratios. I guess, it's the case of the squeaky wheel getting the oil. I appreciate your comments. including another comment from another lister who recommended that I look into tuning the SQL statements first. I agree that this is the most challenging process because the aplication was developed by consultants who no longer are associated with the company. I am in a position to point out the SQLs that are not optimized. I have Spotlight on Oracle and Embarcadero's SQL Tuner installed. I know these are just tools. I still need to understand the key ratios and what impact they have on performance. I'll paste below the output from a script that I run which lists some key database parameters and ratios before and after I made the changes to the DB_BLOCK_BUFFERS parameter. NAME VALUE - --- shared_pool_size 1600 db_block_buffers 1000 db_block_size 2048 sort_area_size65536 Read Hit ratio -- -47.87131 GetsMisses Hit Rate - - - 324934 5009 98.481859 EXECUTIONS Execution Hits PHITRATMISSESHITRAT -- -- - - - 386838 380829 98.446637 837 99.784098 shared Pool Size Free Bytes Percent Free 16,000,000 894,784 5.5924 Free Bytes STATUS 800,000 R-free 40 R-freea 92,320 free 10,978,668 freeabl 2,348,400 perm 3,538,816 recr 6 rows selected. STATE COUNT(*) - - 1 983 3 8 4 9 Block Status COUNT(*) - 4 24 AVAILABLE 940 BEING USED 36 *** NAME VALUE - --- shared_pool_size 1600 db_block_buffers 4000 db_block_size 2048 sort_area_size65536 Read Hit ratio -- -34.79965 GetsMisses Hit Rate - - - 136726 2307 98.340682 EXECUTIONS Execution Hits PHITRATMISSESHITRAT -- -- - - - 199921 197224 98.65096728 99.985996 shared Pool Size Free Bytes Percent Free 16,000,0001,270,592 7.9412 Free Bytes STATUS 800,000 R-free 40 R-freea 154,496 free 10,635,912 freeabl 3,495,892 perm 3,395,960 recr 6 rows selected. STATE COUNT(*) - - 1 3968 332 Block Status COUNT(*) - AVAILABLE 3816 BEING USED 184 Sorry about the paste. Please comment on the negative Read Hit ratio! Concerning network topology, we are using a TCP/IP WAN over a 256K fiber backbone and 256K regular copper microwave backup. We are using 100Mbps NICs and switches. The Oracle clients however are all accessing the RDBMS over the WAN. Only the Developer 6.0 forms executables are located on a file server on their LAN. I know I need to get the big picture when it comes to tuning the Oracle database and the application. I would apprciate your feedback on this challenge as I jump into unfamiliar territory. Regards, Denmark Weatherburne From: [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Help tuning the SGA... Date: Wed, 26 Sep 2001 11:48:30 -0700 The buffer hit ratio is very poor (around 70%). I've already increased the value of DB_BLOCK_BUFFERS from 1000 to 4000, but I still have not observed any significant improvement. Of course I'll try increasing the value again to see if it has an impact. However, I believe there are other related parameters that might have to be tuned
FW:Using Oracle tools to automate hot backups...
!! Please do not post Off Topic to this List !! Hi DBA's First, as a Belizean/American, I'd like to share my sympathy for those touched by the terrorist attack on the US and anger towards the cowards who would go to such extremes to achieve their goals. Life is real. Life is a journey, we must complete it. Peace!! We are running Oracle 8.0.5 on NT in ARCHIVELOGMODE. I recently scheduled three daily hot backups to disk. The process is as follows: 1.) Perl script moves the old backup folders and creates the three daily folders that hold the hot backups to disk 2.) Batch file runs SQL script to display the Archive Log information 3.) SQL scripts called by .bat files, execute the hot backups by tablespace 4.) Batch file runs SQL script to display the Archive Log information The process is not completely automated however. Nevertheless, these scripts are called by the Windows NT Scheduler from My Computer. Of course, they only work if the machine is turned on. It appears that if I move the system date back the scheduler gets confused and submissions fail. I want to pursue the option of setting up these tasks using the Oracle OEM / RMAN or DBMS_JOB tools. Please advise me on the most efficient and effective tool for this process. Thanks in advance, Denmark Weatherburne Belize Knowledge is power, but it is only useful if it is shared! _ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
How to loop call to SQL script in PL/SQL...
Hi DBA's Developers, I first tried to call a batch file which calls SQL*Plus and executes an SQL script from within Perl, but I had no success. Perhaps I'm taking the wrong approach. I'm sure it can be done using PL/SQL. I want to run an SQL script repeatedly every 5 seconds throughout the day to capture the SQL statements that are being parsed by the Oracle engine and spool the results to a disk file. I would appreciate your help in sending me the PL/SQL code to accomplish this task. I don't have mush experience using PL/SQL, but I am prepared to learn. Thanks in advance for the help. Regards, Denmark Weatherburne Knowledge is power, but it is only usefule if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Help using find for Perl...
Hi Gurus,
I am trying to use Perl to find files with a particular extension (*.bat) on
remote machines on our network.
I'm running Windows NT 4.0. I'm using ActivePerl v5.6.1 Build 626 May
2,2001.
This is the script I got after running find2perl with the unix find
specification and redirecting the output to a file:
#! D:\Perl\bin\perl.exe -w
eval 'exec D:\Perl\bin\perl.exe -S $0 ${1+$@}'
if 0; #$running_under_some_shell
use strict;
use File::Find ();
# Set the variable $File::Find::dont_use_nlink if you're using AFS,
# since AFS cheats.
# for the convenience of wanted calls, including -eval statements:
use vars qw/*name *dir *prune/;
*name = *File::Find::name;
*dir= *File::Find::dir;
*prune = *File::Find::prune;
# Traverse desired filesystems
File::Find::find({wanted = \wanted}, 'testdbmonitor/c$');
exit;
sub wanted {
/^.*\.bat\z/s;
}
When I executed it, no output resulted and after about 30 seconds the
command prompt was returned. Definately the remote machine I was searching
has lots of .bat files.
Please help!
Thanks in advance,
Denmark Weatherburne
Knowledge is power, but it is only useful if it is shared!
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denmark Weatherburne
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).
Do you use Ora8 Perf. Mon. v8.0?
H DBA's We are running Oracle 8.0.5.0.0 on the NT platform. I'm currently collecting information about Oracle's Statspack. I found out that I can run it on Oracle 805 as well even though it was released for Oracle version 816. I was looking at the Oracle8 Performance Monitor v8.0 GUI tool that comes with Oracle for Windows NT. It has to be configured with appropriate counters to display graphs. Is this a useful tool? If anyone uses it, I would appreciate some documentaion on configuring it. Thanks in advance, Denmark Weatherburne Belize Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Efficient way to monitor table growth...
Hi DBA's Does an Oracle Stored Procedure or function exist that returns the row count by table in each tablespace? I've observed that one of our production tablespaces is has only 8% free space. Perhaps it could be fragmented. I'll have to find a script that can provide these statistics. I know I'll need to add another datafile to the tablespace soon. However, I'd like to find out which are the active tables in each tablespace and track the row insert growth on a daily basis. Any ideas? Thanks for your help. Denmark Weatherburne Belize Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
How to interpret Alert.Log Trace files...
that might provide technical resources. Unfortunately, I don't have access to Metalink. Thanks in advance, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
No Subject
Hi Again DBA's We are running Oracle 8.0.5 on NT 4. I read that Oracle is implemented as one multi-threaded process on NT. I observe two ORACLE80.EXE proceses running in the Task Manager. There are also two STRTDB80.EXE processes running as well. Does the number of ORACLE.EXE processes represent the number of databases that are open? Thanks in advance for your input! Denmark Weatherburne _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Help interpreting Oracle CPU usage results on NT 4.0...
Hi DBA's, We are running Oracle 805 on NT in ARCHIVELOG MODE. I run the following script to list CPU usage by thread: column program format a50 column username format a12 spool C:\WINNT\Profiles\aweatherburnedb\Desktop\DBA\NT_CPU_usage.out select p.spid thread, s.username, decode(nvl(p.background,0),1,bg.description, s.program) program, ss.value/100 CPU,physical_reads disk_io from v$process p, v$session s, v$sesstat ss, v$sess_io si, v$bgprocess bg where s.paddr=p.addr and ss.sid=s.sid and ss.statistic#=12 and si.sid=s.sid and bg.paddr(+)=p.addr order by ss.value desc; spool off This is the report: THREADUSERNAME PROGRAM CPU DISK_IO - -- - - 000C8 CASTILOL D:\orant\BIN\RWRBE60.exe 112.42132277 001AC CASTILOW ORACLE80.EXE (P000) 67.23 0 001C0 PEREZM D:\orant\BIN\RWRBE60.exe 60.11120613 001C2 CASTILOW ORACLE80.EXE (P001) 55.95 0 001DF CASTILOW ORACLE80.EXE (P002) 33.4 5 00175 CASTILOW ORACLE80.EXE (P003) 30.1 5 001E2 MKENZIEL C:\WINNT\Profiles\All Users\Desktop\sigtas.lnk 27.77257276 000DC CASTILOW C:\WINNT\Profiles\castillowm\Desktop\Sigtas Belize 12.69 40611 Sorry about the formatting. Nevertheless, what I'm trying to get a handle on is the CPU column. Notice the first row, thread 000C8 is using 112.42 ? of CPU. Looking at the SQL statement v$sesstat provides VALUE which are numbers from 0 to 438532196. This value is divided by 100 to get the CPU value. I still don't know what the number in v$sesstat VALUE represents? Also from which table is the value for Disk_IO (Physical Reads) retrieved? Thanks in advance for your time. Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Automating or scheduling backups from OEM on NT 4.0
Hi DBA's, I've recently completed several SQL*Plus scripts and Batch files to do Hot backups of our production Oracle 8.0.5 database at 8am, 12pm, and 5pm daily. Currently, I have to double click the batch file icon which calls SQL*Plus and executes the backup script. I tried unsuccessfully to use NT's AT and WINAT.EXE commands to schedule the execution of the batch jobs. Nothing ran when the scheduled time came. Following your advice, I recently downloaded cygwin and all the tools, but I'm currently trying to get cron working. I was advised that OEM for NT has the capability to automate and schedule backups, etc. I've started to look at RMAN, but I'm not sure if RMAN is the tool that I need to accomplish my backup/restore automation and scheduling needs. I'm comfortable at the command line and with scripts, because I've worked in the Solaris environment for a few years, but I know that a lot of tools has been built into OEM for NT. I'd like to get feedback from DBA's/SA's regarding what implementations you have in place to schedule and automate Oracle tasks such as backups, etc. I would appreciate your suggestions and recomendations. Please provide provide procedures and scripts if possible. Thanks for your help! Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: Automating or scheduling backups from OEM on NT 4.0
Thanks Chris, I did a search for *scheduler* on my drives but I found no scheduler that came with IE5.5 What is the name of the executable and where is it installed? If I need to downlaod it, what is the downloadable zip filename? Thanks, Denmark W. From: Guidry, Chris [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Automating or scheduling backups from OEM on NT 4.0 Date: Wed, 22 Aug 2001 12:40:47 -0800 Hi, I suggest you try using the Task Scheduler. I've had problems in the past using AT and/or WINAT. For some reason certain batches would not run but when I tried Task Scheduler everything worked as expected. If you have IE5+ then you should already have it installed. If you have IE4 then you can install it as an add-on. The name of the service is Task Scheduler and you can run it from Administrative Tools and/or My Computer. HTH -- Chris J. Guidry P.Eng. ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -Original Message- From: Denmark Weatherburne [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, August 22, 2001 11:12 AM To: Multiple recipients of list ORACLE-L Subject:Automating or scheduling backups from OEM on NT 4.0 Hi DBA's, I've recently completed several SQL*Plus scripts and Batch files to do Hot backups of our production Oracle 8.0.5 database at 8am, 12pm, and 5pm daily. Currently, I have to double click the batch file icon which calls SQL*Plus and executes the backup script. I tried unsuccessfully to use NT's AT and WINAT.EXE commands to schedule the execution of the batch jobs. Nothing ran when the scheduled time came. Following your advice, I recently downloaded cygwin and all the tools, but I'm currently trying to get cron working. I was advised that OEM for NT has the capability to automate and schedule backups, etc. I've started to look at RMAN, but I'm not sure if RMAN is the tool that I need to accomplish my backup/restore automation and scheduling needs. I'm comfortable at the command line and with scripts, because I've worked in the Solaris environment for a few years, but I know that a lot of tools has been built into OEM for NT. I'd like to get feedback from DBA's/SA's regarding what implementations you have in place to schedule and automate Oracle tasks such as backups, etc. I would appreciate your suggestions and recomendations. Please provide provide procedures and scripts if possible. Thanks for your help! Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: Guidry, Chris 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Help automating SQL Hot Backup script with batch file...
Hi DBA's, I know this question was asked some time ago. Although I tried the recommended solution, it did not work for me. I'll paste my scripts. Hopefully, someone can point out my error. First the background: I want to run this scripts from a remote client machine which has the Oracle DBA client (NT) installed or from another remote NT server which has Oracle 8.0.5 installed as well. The target database (Production) is located on the LAN. It is running Oracle 8.0.5 on NT 4.0 as well. I can connect start up either an SQLPLUS session or SVRMGR session and execute the Hot_Backup.sql script. However, when I try to invoke the SQLPLUS or SVRMGR program with the Hot_Backup script it fails with a message that I'm not connected to Oracle or Oracle is not available. As you can see from the commented lines in the batch file, I tried other options as well but none seems to work. Of course I want to use Winat.exe when it works. I know that there are some commands that can only be executed from the SVRMGR prompt. For other scripts, can any script that can be run in SQLPLUS also be run from the SVRMGR prompt? Here is the batch file (Hot_Backup_PROD.bat): SET ORACLE_SID=PROD L:\orant\BIN\SVRMGR30.EXE @C:\WINNT\Profiles\Administrator.000\Desktop\SQLs\DBA\Hot_Backup.sql REM \\lamanai\e$\orant\BIN\PLUS80W.EXE @C:\WINNT\Profiles\Administrator.000\Desktop\SQLs\DBA\Hot_Backup.sql REM start L:\ORANT\BIN\PLUS80W.EXE @C:\WINNT\Profiles\Administrator.000\Desktop\SQLs\DBA\Hot_Backup.sql Here is the Hot_Backup.sql script: connect internal/; spool C:\WINNT\Profiles\Administrator.000\Desktop\SQLs\DBA\Hot_Backup.out alter system switch logfile; alter system switch logfile; alter tablespace IRD_BASE begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\IRDBASEPROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace IRD_BASE end backup; alter tablespace IRD_DATA_LARGE begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\USR1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace IRD_DATA_LARGE end backup; alter tablespace IRD_INDEXES begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\INDX1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace IRD_INDEXES end backup; alter tablespace IRD_TEMP begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\TMP1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace IRD_TEMP end backup; alter tablespace RBS begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\RBS1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace RBS end backup; alter tablespace ROLLBACKSPACE1 begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\ROLL1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace ROLLBACKSPACE1 end backup; alter tablespace SYSTEM begin backup; Host L:\orant\bin\ocopy80.exe L:\ORANT\DATABASE\SYS1PROD.ORA N:\ORA_Hot_Bkup\PROD alter tablespace SYSTEM end backup; alter system switch logfile; alter system switch logfile; alter database backup controlfile to 'ctl1PROD.bak'; --Binary copy created in ORACLE_HOME\database\ctrlPROD.bak; alter database backup controlfile to trace; --SQL file appended to ORACLE_HOME\RDBMS80\Trace\(current Ora9.trc file); spool off Thanks in advance for your help, Denmark Weatherburne Knowledge is power, but It is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: RMAN cold backup?
Hi Joe,
I'm currently looking at RMAN as a backup and recovery tool to automate
backups to disk. I would appreciate it very much if you could describe what
each line of your script does.
I'd have to do Hot Backups though.
Would this script have to change?
Thanks in advance,
Denmark Weatherburne
Knowledge is power, but it is only useful if it is shared
From: JOE TESTA [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: RMAN cold backup?
Date: Tue, 14 Aug 2001 14:51:42 -0800
cold backup in the respect that the db has to be mounted but not open.
here is an example(since we're in noarchivelogmode, its our only choice).
this is an incremental level 0, which is all.
hth, joe
run {
allocate channel ch1 type disk;
set limit channel ch1 kbytes = 190;
backup incremental level 0
tag = 'dssmnt02_inc_0'
format '/ora_export/export/tmp/dssmnt02_inc_0_%U'
database;
}
[EMAIL PROTECTED] 08/14/01 05:53PM
I'm frantically R'ing TFM, but I can't find the answer to a simple
question: Can I use RMAN to perform a cold backup? If so, how? TIA!
Paul Baumgartel
MortgageSight Holdings, LLC
[EMAIL PROTECTED]
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denmark Weatherburne
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: RECOVERY
Hi DBA's here is another scenario, I'm trying to recover a database from a cold O/S backup onto another NT machine. The source database was running in NOARCHIVELOG Mode. I guesss the backup was not taken at a consistent state because when I tried to open the database, Oracle asked for Archive Log#13. I realized that I don't have the Archived Log because the source database was running in NOARCHIVELOG mode. I think I copied the online REDO logs as well though. I changed the status on the source database to ARCHIVELOG mode in an attempt to write Archive Log#13 to disk. I made a mistake at the command line and ended up overwriting the online log#13 anyway. I ended up writing Archive Log#14 to disk. Of course Log#14 is useless because the destination system requires Log#13. I did some reading over the weekend and found out the following: If the Archived REDO Log that Oracle needs to complete recovery of a database is unavailable, I believe I can use Point in Time Recovery. I've never tested this scenario before. What approach is best? How can I determine what is the appropriate time to recover to? Thanks in advance, Regards, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared. From: Raj Gopalan [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: RE: RECOVERY Date: Tue, 7 Aug 2001 10:03:00 +0100 Use recover until option and enter cancel when oracle asks for the corrupted archive log. You need to open the database with resetlogs. -Original Message- From: Brian Zelli [mailto:[EMAIL PROTECTED]] Sent: 06 August 2001 17:48 To: LazyDBA.com Discussion Subject: RECOVERY Hello all, HELP! I have a system (oracle 8.1.7 on SUN Solaris 7) that crashed. I have no backup. I can't seem to recover it. I called oracle support and they kept hammering on the fact that I needed to re-apply the last cold. As I mentioned to him, we have no backup. I tried to recover the database and it doesn't like that. I know which archive log is corrupted. How do I get to a point in time just before that log and bring up the database? Brian Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
After restoring PROD to another NT server as DEV...
Thanks DBA's, OK, I recently restored the PROD database to another NT server as DEV. Thanks for all your input. The best way to learn is to test. Before I could open the database, I had to use the RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; option. After cancelling without applying any logs, I was able to open the database with the RESETLOGS option. The PROD database in running in ARCHIVELOG mode. I am running the DEV database in NOARCHIVELOG mode. If I change the ARCHIVE MODE to ARCHIVELOG on DEV, can I apply the archived logs from PROD to DEV database daily? What are the procedures? Also could I have used the option NORESETLOGS ARCHIVELOG with the CREATE CONTROLFILE statement? What are the requirements? BTW, I observed some Archived Logs on the PROD database with sequence numbers having a greater range than the current sequence. For example, the current logs have sequence like Arc3685.1 while there are a few created at random from April to August with sequence numbers like Arc5164.1 to Arc5214.1. What controls the sequence numbering for archived log files? How can you explain the change of sequence numbers at random? Regards, Denmark W. Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: RAID or NOT to RAID? What's the diff???
Hi, From an article by Gaja Krishna Vaidyanatha: RAID 0+1 = Stripe First, Then Mirror What You Just Striped RAID 1+0 = Mirror First, Then Stripe Over What You Just Mirrored. This site was recommended: http://www.quest.com/whitepapers/ Hope this helps, Denmark W. From: Koivu, Lisa [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RAID or NOT to RAID? What's the diff??? Date: Mon, 13 Aug 2001 08:05:42 -0800 Good morning - Admitting ignorance here. What is the difference between Raid 0+1 and Raid 1+0 (is this mirroring)? If someone can forward a website that describes the difference I would really appreciate it. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Jonathan Lewis [SMTP:[EMAIL PROTECTED]] Sent: Saturday, August [Koivu, Lisa] 11, 2001 4:25 AM To: Multiple recipients of list ORACLE-L Subject:Re: Fwd: RE: RAID or NOT to RAID? You could also mention that when you have to resilver on RAID 1+0 after a disk fails, you only have to resilver one disk. On RAID 0+1 you have to resilver all the disc in the stripe. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 11 August 2001 01:13 | |For e.g, if you have a 2-way striped mirrored volume, |when you lose 1 drive in the volume, with RAID 0+1 you |will lose 50% of the logical volume's I/O capacity, as |the whole member of the logical volume is shot, |because the stripe becomes totally invalid. With a |comparable configuration using RAID 1+0, you will lose |only 25% of your logical volume's I/O capacity. | |When the striped volume is a 4-way stripe, again with |1 drive loss, with RAID 0+1 you will lose 50% of I/O |capacity versus with RAID 1+0, you will only lose 12 |1/2 %. As your stripe-width increases, the percentage |loss in the I/O capacity for RAID 1+0 decreases. | |You should (when possible) consider using RAID 1+0 any |day when compared to RAID 0+1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: RE: RAID or NOT to RAID?
Hi DBA's: I've been researching this issue as well. All the expert advice seems to point to using RAID 1+0 for OLTP implementations and RAID 5 for DSS (Read-Only) implementations. I also found out that you can use raw partitions on NT as well. Does anyone have experience with setting up raw partitions on NT? I still don't know how one would separate datafiles from indexfiles and REDOLogfiles under RAID implementations? Any ideas? I agree that it boils down to the fundamental question of cost vs performance. Nevertheless, DBA's and SA's still have to be familiar with these configurations to be efficient. Regards, Denmark Weatherburne Belize From: Berj Kacherian [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: RE: RE: RAID or NOT to RAID? Date: Thu, 9 Aug 2001 10:19:26 -0700 You've got the right idea. RAID 1+0 is the best that you can use, but also expensive. It all comes down to the age old question of money and performance. Just remember, when the system is slow, management is not going to ask the finance guy why he did not spend more for a better layout, they are going to ask the dba why the db is so slow. Always ask for the best layout that you know. When they balk at it, make sure you document in an email that they are giving up performance. When the system starts running slow, you've got yourself covered! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 09, 2001 5:25 AM To: LazyDBA.com Discussion Subject: RE: RE: RAID or NOT to RAID? Okay, Guys I know there have been discussions about this but can someone confirm my understanding of the situation. Working and researching use of RAID with Oracle it has been come clear to me that there are many views in regards to Oracle databases. I.E. stripe using RAID and don't worry at all about separating segments. Stripe using RAID but still don't put all types of Oracle segments (even striped) on the same physical disks. It seems to me that even using RAID 1+0 the optimum situation is to have enough disks to stripe and to also separate out redo logs, archives, data from index (at the least). However, this can definitely become expensive. Of course, you can use different types of RAID but then SA balk at the complexity and is the bang worth the buck. If it is a highly concurrent medium sized OLTP system with Oracle on Sun using RAID 1+0 (Veritas) what is the best way to go. Also, I have tried separating redo versus all data stripped all across available disks and the redo write waits reported by my monitoring tool were about the same and still high (supposedly should be about 20ms and was reporting at times 30-60 ms). Also, the workload I was running to test was a migration workload which means large batch writes and in my mind I can't both optimize I/O layout for both that and common random I/O of typical OLTP daily operations. So I would optimize for the typical daily operations - try to get the developer to tune the batch process - change some of the memory parameters for the migration process (as nothing else will be happening on the system) and live with any I/O constraints after that. Any advice - would appreciate info. from the Quest contributors the tool I have been using to monitor is indeed Spotlight. Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: RE: RAID or NOT to RAID?
Hi DBA's: I've been researching this issue as well. All the expert advice seems to point to using RAID 1+0 for OLTP implementations and RAID 5 for DSS (Read-Only) implementations. I also found out that you can use raw partitions on NT as well. Does anyone have experience with setting up raw partitions on NT? I still don't know how one would separate datafiles from indexfiles and REDOLogfiles under RAID implementations? Any ideas? I agree that it boils down to the fundamental question of cost vs performance. Nevertheless, DBA's and SA's still have to be familiar with these configurations to be efficient. Regards, Denmark Weatherburne Belize From: Berj Kacherian [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: RE: RE: RAID or NOT to RAID? Date: Thu, 9 Aug 2001 10:19:26 -0700 You've got the right idea. RAID 1+0 is the best that you can use, but also expensive. It all comes down to the age old question of money and performance. Just remember, when the system is slow, management is not going to ask the finance guy why he did not spend more for a better layout, they are going to ask the dba why the db is so slow. Always ask for the best layout that you know. When they balk at it, make sure you document in an email that they are giving up performance. When the system starts running slow, you've got yourself covered! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 09, 2001 5:25 AM To: LazyDBA.com Discussion Subject: RE: RE: RAID or NOT to RAID? Okay, Guys I know there have been discussions about this but can someone confirm my understanding of the situation. Working and researching use of RAID with Oracle it has been come clear to me that there are many views in regards to Oracle databases. I.E. stripe using RAID and don't worry at all about separating segments. Stripe using RAID but still don't put all types of Oracle segments (even striped) on the same physical disks. It seems to me that even using RAID 1+0 the optimum situation is to have enough disks to stripe and to also separate out redo logs, archives, data from index (at the least). However, this can definitely become expensive. Of course, you can use different types of RAID but then SA balk at the complexity and is the bang worth the buck. If it is a highly concurrent medium sized OLTP system with Oracle on Sun using RAID 1+0 (Veritas) what is the best way to go. Also, I have tried separating redo versus all data stripped all across available disks and the redo write waits reported by my monitoring tool were about the same and still high (supposedly should be about 20ms and was reporting at times 30-60 ms). Also, the workload I was running to test was a migration workload which means large batch writes and in my mind I can't both optimize I/O layout for both that and common random I/O of typical OLTP daily operations. So I would optimize for the typical daily operations - try to get the developer to tune the batch process - change some of the memory parameters for the migration process (as nothing else will be happening on the system) and live with any I/O constraints after that. Any advice - would appreciate info. from the Quest contributors the tool I have been using to monitor is indeed Spotlight. Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Fwd: RE: RAID or NOT to RAID?
Hi DBA's, I hope I'm not opening a can of worms, but I'd like your feedback on the issue of using RAID 5 on NT 4.0 with Oracle 8.0.5. I have worked with Solaris and RDBMS before switching to this job using the NT platform. So I'm having to adjust and revisit the issues that was of concern on the unix platform. The NT and RAID configuration does not allow you to locate your datafiles and logs on seperate specific disks. Also I don't know if the use of raw devices on NT is possible if RAID 5 is enabled. I've read about the performance loss inherent with RAID 5 because of the writes to the parity disk. I'd like to find out more about these issues and how you all are dealing with them on the NT 4.0 platform? Thanks in advance for your time. Regards, Knowledge is power, but it is only useful if it is shared! From: [EMAIL PROTECTED] To: LazyDBA.com Discussion [EMAIL PROTECTED] Subject: RE: RAID or NOT to RAID? Date: Tue, 7 Aug 2001 13:22:48 -0400 Can you forward me the info. you received on this? I am wondering about the issue as well - esp. as regards threads. -Original Message- From: Michael Porter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 07, 2001 8:33 AM To: LazyDBA.com Discussion Subject: RAID or NOT to RAID? I don't want to start the proverbial war over RAID use in general but, I am seriously considering using RAID-10 (stripping + mirroring) for an Oracle 11.x application (AIX 4.3.3 and at least 8.0.5.2.1 db). IF I have several (more than 2) RAID10's and put redo on non raid volumes am I likely to get nearly as good performance with this as opposed to just straight mirroring OR individual volumes, possible stripped? Any points to other documentation or threads would be appreciated. Thanks, Mike Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Help Creating a new SID on NAS drive from hot backup
Hi DBA's I am testing the recovery of the PROD SID from hot backups to a Network Attached Storage device. I followed all the prescribed procedures which completed successfully. However, after starting the instance, opening the database, and starting an SQL*Plus session I get the following error message when trying to execute an SQL statement from an SQL*Plus session connected to the newly recovered DEV2 SID: ORA-01034: Oracle Not available. I think there is a way to trick Oracle into thinking that a database is local right? However, what I'm trying to do might be breaking the rules because the ORACLE_HOME on the NT server is on local drive D:\ORANT while I have restored the Oracle physical files to N:\Ora_Database\DEV2 which is a mapped drive of a share on a DELL Network Attached Storage device. I already tried changing the ORACLE_HOME path in the listener.ora configuration file. I used both the local and remote paths. Please tell me if I'm wasting my time or give me some help. Thanks in advance for your time, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Testing posting to Oracle-L list...
Hi DBA's, I joined previously, but for some reason my membership was not activated. I've joined again and I'm checking if my membership is registered now. Regards, Denmark W. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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: Recovery Question from Denmark W. in Belize
' SIZE 51200K AUTOEXTEND ON NEXT 2K MAXSIZE UNLIMITED LOGGING DEFAULT STORAGE(INITIAL 1M NEXT 1M MINEXTENTS 2 MAXEXTENTS 121 PCTINCREASE 0) ONLINE PERMANENT / CREATE TABLESPACE ROLLBACKSPACE1 DATAFILE 'E:\ORANT\DATABASE\ROLL1PROD.ORA' SIZE 51200K AUTOEXTEND OFF LOGGING DEFAULT STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50) ONLINE PERMANENT Total System Global Area 19939328 bytes Fixed Size 49152 bytes Variable Size17768448 bytes Database Buffers 2048000 bytes Redo Buffers73728 bytes What is the function of the Online/offline REDO Logs? We are using TCP/IP over ethernet LANs and WAN with 256Kb fiber backbone and 100Mb NICs. Thanks in advance for your time, Denmark Weatherburne From: Blizzard, Michael [EMAIL PROTECTED] To: 'Denmark Weatherburne' [EMAIL PROTECTED] Subject: RE: Question from Denmark W. in Belize Date: Mon, 30 Jul 2001 14:16:50 -0400 Well, I would start by backing up your archive logs. What is the structure of the database. Do you have mirrored redo logs?, How big is the database , When was the last export done. -Original Message- From: Denmark Weatherburne [mailto:[EMAIL PROTECTED]] Sent: Monday, July 30, 2001 1:59 PM To: LazyDBA.com Discussion Subject: RE: Question from Denmark W. in Belize Hi KD etal., I'd like to ask you a technical question regarding Oracle database recovery. I wil take on the DBA role for an Oracle 8.0.5 database running on NT 4.0 with SP 6. My previous experience with Oracle has been with version 6.x on sunOS, since then I worked as a DBA with Informix on Solaris. I've started to review the configuration and of course backup and recovery is an important issue. Currently no tested recovery procedures exist. The current backup strategy are as follows: All physical files are being backed up to 4mm tape using ArcServeIT 6.61 for NT and the Oracle agent for ArcServeIT. The NT registry is also being backed up to tape. The database is running in archivelog mode, but the ARC* logs are not being up. They are still on the hard disk. The NT Server is running RAID level 5. What are my Oracle recovery options in this scenario? Thanks in advance for your advice, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne 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).
Request for advice Re: Oracle export error...
Hi Listers, (Thanks Abardeen1 for your help!) However, I need to be cautious because this problem is on our production database. This is what I found after investigating the solution details: I searched for catnoqueue.sql in CATALOG.SQL and CATEXP.SQL which are the scripts that I had executed some weeks ago in a failed attempt to enable full export of the database. I could not find the catnoqueue.sql script in either of those scripts. I'm not sure in what case the catnoqueue.sql script needs to be executed. I found out that I did not have to run those scripts, I just needed to run the full export as a DBA user. However, running the scripts did cause several packages, forms and stored procedures to become invalid due to a time stamp. I'd still like to find out more about what causes these objects to become invalid and why a recompile is necessary. I believe there are several more objects that are invalid which need to be recompiled. I would appreciate some assistance in finding out how to determine what objects are invalid and how to recompile them. I someone has a script they could share, I would appreciate it. 1.) I have two queue tables DEF$_AQCALL and DEF$_AQERROR owned by SYSTEM 2.) I have the following (6) rows in the SYS.EXPACT$ table where func_package = 'DBMS_AQ_IMPORT_INTERNAL' owned by SYSTEM NAMEFUNC_PROC DEF$_AQCALL AQ_TABLE_EXPORT_CHECK DEF$_AQCALL AQ_EXPORT_CHECK DEF$_AQCALL AQ_EXPORT_CHECK DEF$_AQERROR AQ_TABLE_EXPORT_CHECK DEF$_AQERROR AQ_EXPORT_CHECK DEF$_AQERROR AQ_EXPORT_CHECK How would I know if the catnoqueue.sql has been run already? I also searched the Oracle FAQs and found a similar case where the export failed while exporting the posttables actions; however, the solution was different. That incident was on an Oracle 7.x database though. I noticed that the first Oracle error that I got was 4045 not 4068 or 1403 as indicated by the bug fix. I'm concerned if this solution will fix my problem. Perhaps someone can clarify these issues for me. What is the relationship between the dba_queue_table and SYS.EXPACT$ table? Thanks in advance for your time! Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! From: A. Bardeen [EMAIL PROTECTED] To: Denmark Weatherburne [EMAIL PROTECTED] Subject: Re: More details re Oracle export error... (w/attachment) Date: Mon, 23 Jul 2001 18:20:08 -0700 (PDT) Denmark, I've attached the note I mentioned in the previous email. HTH, -- Anita --- Denmark Weatherburne [EMAIL PROTECTED] wrote: Hi DBA's Here is additional information for your information: We are running Oracle 8.0.5 on Windows NT 4.0 The export parameter file I used is pasted below: FULL=Y FILE=D:\Backups\fullexport.dmp LOG=D:\Backups\fullexport.log CONSISTENT=Y I ran the export from DBArtisan. The export log was as follows: . exporting posttables actions EXP-8: ORACLE error 4045 encountered ORA-04045: errors during recompilation/revalidation of SYS.DBMS_AQ_IMPORT_INTERNAL ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is invalid ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 ORA-06512: at SYS.DBMS_SYS_SQL, line 787 ORA-06512: at SYS.DBMS_SQL, line 328 ORA-06512: at SYS.DBMS_EXPORT_EXTENSION, line 82 ORA-06512: at SYS.DBMS_EXPORT_EXTENSION, line 133 ORA-06512: at line 1 EXP-0: Export terminated unsuccessfully We are using Oracle 8.0.5 on Windows NT 4.0. BTW, Regis, I would really appreciate any script you may have to make this problem resolution and learning process easier. Regards, Denmark Weatherburne Knowledge is power, but it is only useful if it is shared! _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ Article-ID: Note:1059224.6 Circulation:PUBLISHED (EXTERNAL) Folder: server.Utilities.ExportImport Topic: Draft Articles Title: EXPORT FAILS WITH EXP-8, ORA-4068/ORA-1403 ON SYS. DBMS_AQ_IMPORT_INTERNAL Problem Description: Your full database export fails when exporting posttables actions with the following errors: . exporting
