How to update "maxphys" on Solaris 8 ......
Dear List, Could someone tell me how to update the parameter "maxphys" on sun solaris 8 system. At present it is having a default value of 128K. I would like to change it to 1MB. There is no such parameter in /etc/system. I need this parameter to be updated as it seems to be controlling the MAX_IO_SIZE on the platform. -- Janardhana
RE: RMAN restore on another server
Title: Message May be you try the following: If you get errors restoring controlfile, You may ftp the controlfiles manually to the new server and startup mount the database first. Then, Try your restore database. -- Janardhana -Original Message- From: Spears, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Subject: RMAN restore on another server Goal: To restore the database from RMAN backup on a different server by means of moving the backup pieces and logs over to the new machine and use Rman to unpack the database files. Strategy: To restore the database from the RMAN backup pieces into a new directory locations on the machine and extract the control file and startup the database. Some of the steps to setup the new machine. 1) Install oracle 8i 2) install the patch 4.0 3) copy .profile over 4) duplicated the Admin directories for the database to be restored 5) created a big mount /u02/vssppln/ point for all the datafiles and controlfiles and so on 6) Created a backup mount point to store the RMAN backup pieces and archivelogs 7) moved the backup pieces and archivelogs to the new machine 8) Setup and confirm connectivity to Rman catalog 9) No mount the database to be on the new machine 10) Launch the Rman command rman cmdfile=restore_dbsid.rman trace=restore_dbsid.log Problem... I run this restore from Rman backup...but it gets to processing the command and gets to the "RMAN-03022: compiling command: set " and just hangs...adding another line every 1/2 hour or so... Anybody seen this or have ideas? I talked to one guy who did have this but couldn't remember the solution. This is the first time doing this so I might be missing something simple.. Here is the command in operation ==> rman cmdfile=restore_dbsid.rman trace=restore_dbsid.nohup_log Recovery Manager: Release 8.1.7.4.0 - Production RMAN> RMAN> connect catalog rman81740/[EMAIL PROTECTED] 2> 3> connect target / 4> 5> 6> 7> run 8> 9> { 10> 11> allocate channel disk_channel1 type disk ; 12> 13> sql 'alter session set NLS_DATE_FORMAT="-MM-DD HH24:MI:SS"'; 14> set until time = '2003-12-02 05:50:00'; 15> 16> set newname for datafile 1 to 17> '/u02/vssppln/system01.dbf'; 18> 19> set newname for datafile 2 to 20> '/u02/vssppln/rbs01.dbf'; 21> 22> set newname for datafile 3 to 23> '/u02/vssppln/rbs02.dbf'; 24> 25> set newname for datafile 4 to 26> '/u02/vssppln/AIMFACT01.dbf'; 27> 28> set newname for datafile 5 to 29> '/u02/vssppln/AIMFACT_INDEX01.dbf'; 30> 31> set newname for datafile 6 to 32> '/u02/vssppln/AIMFACT101.dbf'; 33> 34> set newname for datafile 7 to 35> '/u02/vssppln/AIMFACT102.dbf'; 36> 37> set newname for datafile 8 to 38> '/u02/vssppln/aimfact1_index01.dbf'; 39> 40> set newname for datafile 9 to 41> '/u02/vssppln/aimfact1_index02.dbf'; 42> 43> set newname for datafile 10 to 44> '/u02/vssppln/aimfact1_index03.dbf'; 45> 46> set newname for datafile 11 to 47> '/u02/vssppln/aimfact1_index04.dbf'; 48> 49> set newname for datafile 12 to 50> '/u02/vssppln/aimfact201.dbf'; 51> 52> set newname for datafile 13 to 53> '/u02/vssppln/aimfact202.dbf'; 54> 55> set newname for datafile 14 to 56> '/u02/vssppln/aimfact2_index01.dbf'; 57> 58> set newname for datafile 15 to 59> '/u02/vssppln/aimfact2_index02.dbf'; 60> 61> set newname for datafile 16 to 62> '/u02/vssppln/aimfact2_index03.dbf'; 63> 64> set newname for datafile 17 to 65> '/u02/vssppln/aimfact2_index04.dbf'; 66> 67> set newname for datafile 18 to 68> '/u02/vssppln/aimstruct01.dbf'; 69> 70> set newname for datafile 19 to 71> '/u02/vssppln/aimstruct_index01.dbf'; 72> 73> set newname for datafile 20 to 74> '/u02/vssppln/aimstruct101.dbf'; 75> 76> set newname for datafile 21 to 77> '/u02/vssppln/aimstruct1_index01.dbf'; 78> 79> set newname for datafile 22 to 80> '/u02/vssppln/aimwork01.dbf'; 81> 82> set newname for datafile 23 to 83> '/u02/vssppln/mipsdata01.dbf'; 84> 85> set newname for datafile 24 to 86> '/u02/vssppln/mipsindex01.dbf'; 87> 88> set newname for datafile 25 to 89> '/u02/vssppln/mipsdata101.dbf'; 90> 91> set newname for datafile 26 to 92> '/u02/vssppln/mipsdata1_index01.dbf'; 93> 94> set newname for datafile 27 to 95> '/u02/vssppln/mipsdata201.dbf'; 96> 97> set newname for datafile 28 to 98> '/u02/vssppln/nipsdata2_index01.dbf'; 99> 100> set newname for datafile 29 to 101> '/u02/vssppln/tools01.dbf'; 102> 103> set newname for datafile 30 to 104> '/u02/vssppln/users01.dbf'; 105> 106> set newname for datafile 31 to 107> '/u02/vssppln/AIMINDEX01.dbf'; 108> 109> set newname for datafile 32 to 110> '/u02/vssppln/aimfact1_index05.dbf'; 111> 112> set newname for datafile 33 to 113> '/u02/vssppln/aimfact2_index05.dbf'; 114> 115> 116> restore da
RE: RMAN restore on another server
Title: Message I am sorry, I thought you are restoring from Tape. In either case, you connect to target and catalog database on the new server and see if you can access the backups that were backed up on the original server. -- Janardhana -Original Message- From: Spears, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Subject: RMAN restore on another server Goal: To restore the database from RMAN backup on a different server by means of moving the backup pieces and logs over to the new machine and use Rman to unpack the database files. Strategy: To restore the database from the RMAN backup pieces into a new directory locations on the machine and extract the control file and startup the database. Some of the steps to setup the new machine. 1) Install oracle 8i 2) install the patch 4.0 3) copy .profile over 4) duplicated the Admin directories for the database to be restored 5) created a big mount /u02/vssppln/ point for all the datafiles and controlfiles and so on 6) Created a backup mount point to store the RMAN backup pieces and archivelogs 7) moved the backup pieces and archivelogs to the new machine 8) Setup and confirm connectivity to Rman catalog 9) No mount the database to be on the new machine 10) Launch the Rman command rman cmdfile=restore_dbsid.rman trace=restore_dbsid.log Problem... I run this restore from Rman backup...but it gets to processing the command and gets to the "RMAN-03022: compiling command: set " and just hangs...adding another line every 1/2 hour or so... Anybody seen this or have ideas? I talked to one guy who did have this but couldn't remember the solution. This is the first time doing this so I might be missing something simple.. Here is the command in operation ==> rman cmdfile=restore_dbsid.rman trace=restore_dbsid.nohup_log Recovery Manager: Release 8.1.7.4.0 - Production RMAN> RMAN> connect catalog rman81740/[EMAIL PROTECTED] 2> 3> connect target / 4> 5> 6> 7> run 8> 9> { 10> 11> allocate channel disk_channel1 type disk ; 12> 13> sql 'alter session set NLS_DATE_FORMAT="-MM-DD HH24:MI:SS"'; 14> set until time = '2003-12-02 05:50:00'; 15> 16> set newname for datafile 1 to 17> '/u02/vssppln/system01.dbf'; 18> 19> set newname for datafile 2 to 20> '/u02/vssppln/rbs01.dbf'; 21> 22> set newname for datafile 3 to 23> '/u02/vssppln/rbs02.dbf'; 24> 25> set newname for datafile 4 to 26> '/u02/vssppln/AIMFACT01.dbf'; 27> 28> set newname for datafile 5 to 29> '/u02/vssppln/AIMFACT_INDEX01.dbf'; 30> 31> set newname for datafile 6 to 32> '/u02/vssppln/AIMFACT101.dbf'; 33> 34> set newname for datafile 7 to 35> '/u02/vssppln/AIMFACT102.dbf'; 36> 37> set newname for datafile 8 to 38> '/u02/vssppln/aimfact1_index01.dbf'; 39> 40> set newname for datafile 9 to 41> '/u02/vssppln/aimfact1_index02.dbf'; 42> 43> set newname for datafile 10 to 44> '/u02/vssppln/aimfact1_index03.dbf'; 45> 46> set newname for datafile 11 to 47> '/u02/vssppln/aimfact1_index04.dbf'; 48> 49> set newname for datafile 12 to 50> '/u02/vssppln/aimfact201.dbf'; 51> 52> set newname for datafile 13 to 53> '/u02/vssppln/aimfact202.dbf'; 54> 55> set newname for datafile 14 to 56> '/u02/vssppln/aimfact2_index01.dbf'; 57> 58> set newname for datafile 15 to 59> '/u02/vssppln/aimfact2_index02.dbf'; 60> 61> set newname for datafile 16 to 62> '/u02/vssppln/aimfact2_index03.dbf'; 63> 64> set newname for datafile 17 to 65> '/u02/vssppln/aimfact2_index04.dbf'; 66> 67> set newname for datafile 18 to 68> '/u02/vssppln/aimstruct01.dbf'; 69> 70> set newname for datafile 19 to 71> '/u02/vssppln/aimstruct_index01.dbf'; 72> 73> set newname for datafile 20 to 74> '/u02/vssppln/aimstruct101.dbf'; 75> 76> set newname for datafile 21 to 77> '/u02/vssppln/aimstruct1_index01.dbf'; 78> 79> set newname for datafile 22 to 80> '/u02/vssppln/aimwork01.dbf'; 81> 82> set newname for datafile 23 to 83> '/u02/vssppln/mipsdata01.dbf'; 84> 85> set newname for datafile 24 to 86> '/u02/vssppln/mipsindex01.dbf'; 87> 88> set newname for datafile 25 to 89> '/u02/vssppln/mipsdata101.dbf'; 90> 91> set newname for datafile 26 to 92> '/u02/vssppln/mipsdata1_index01.dbf'; 93> 94> set newname for datafile 27 to 95> '/u02/vssppln/mipsdata201.dbf'; 96> 97> set newname for datafile 28 to 98> '/u02/vssppln/nipsdata2_index01.dbf'; 99> 100> set newname for datafile 29 to 101> '/u02/vssppln/tools01.dbf'; 102> 103> set newname for datafile 30 to 104> '/u02/vssppln/users01.dbf'; 105> 106> set newname for datafile 31 to 107> '/u02/vssppln/AIMINDEX01.dbf'; 108> 109> set newname for datafile 32 to 110> '/u02/vssppln/aimfact1_index05.dbf'; 111> 112> set newname for datafile 33 to 113> '/u02/vssppln/aimfact2_index05.dbf'; 114> 115> 116> re
RE: RMAN restore on another server
Title: Message By default RMAN restores the backup to the machine from where it is backedup. If you need to restore the backup on to alternate client, your netbackup admin has to setup the access. You have not mentioned whether you are using Netbackup or legato or something else. Once the access is set up, you can test it by connecting to rman target / catalog rman/[EMAIL PROTECTED] on the new machine ,and issuing the command list backup at RMAN prompt on the new machine. If you see the backups, you can restore the backup. If you are using NETBACKUP, use the var NB_ORA_CLIENT, If not substitute with the corresponding variable. Startup nomount; Run { Allocate channel t1 type 'sbt_tape' parms="ENV=(NB_ORA_CLIENT=backedupclient; export NB_ORA_CLIENT); Restore controlfile; Alter database mount; Restore database; Alter database open; } -- Janardhana -Original Message- From: Spears, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 2:14 PM To: Multiple recipients of list ORACLE-L Subject: RMAN restore on another server Goal: To restore the database from RMAN backup on a different server by means of moving the backup pieces and logs over to the new machine and use Rman to unpack the database files. Strategy: To restore the database from the RMAN backup pieces into a new directory locations on the machine and extract the control file and startup the database. Some of the steps to setup the new machine. 1) Install oracle 8i 2) install the patch 4.0 3) copy .profile over 4) duplicated the Admin directories for the database to be restored 5) created a big mount /u02/vssppln/ point for all the datafiles and controlfiles and so on 6) Created a backup mount point to store the RMAN backup pieces and archivelogs 7) moved the backup pieces and archivelogs to the new machine 8) Setup and confirm connectivity to Rman catalog 9) No mount the database to be on the new machine 10) Launch the Rman command rman cmdfile=restore_dbsid.rman trace=restore_dbsid.log Problem... I run this restore from Rman backup...but it gets to processing the command and gets to the "RMAN-03022: compiling command: set " and just hangs...adding another line every 1/2 hour or so... Anybody seen this or have ideas? I talked to one guy who did have this but couldn't remember the solution. This is the first time doing this so I might be missing something simple.. Here is the command in operation ==> rman cmdfile=restore_dbsid.rman trace=restore_dbsid.nohup_log Recovery Manager: Release 8.1.7.4.0 - Production RMAN> RMAN> connect catalog rman81740/[EMAIL PROTECTED] 2> 3> connect target / 4> 5> 6> 7> run 8> 9> { 10> 11> allocate channel disk_channel1 type disk ; 12> 13> sql 'alter session set NLS_DATE_FORMAT="-MM-DD HH24:MI:SS"'; 14> set until time = '2003-12-02 05:50:00'; 15> 16> set newname for datafile 1 to 17> '/u02/vssppln/system01.dbf'; 18> 19> set newname for datafile 2 to 20> '/u02/vssppln/rbs01.dbf'; 21> 22> set newname for datafile 3 to 23> '/u02/vssppln/rbs02.dbf'; 24> 25> set newname for datafile 4 to 26> '/u02/vssppln/AIMFACT01.dbf'; 27> 28> set newname for datafile 5 to 29> '/u02/vssppln/AIMFACT_INDEX01.dbf'; 30> 31> set newname for datafile 6 to 32> '/u02/vssppln/AIMFACT101.dbf'; 33> 34> set newname for datafile 7 to 35> '/u02/vssppln/AIMFACT102.dbf'; 36> 37> set newname for datafile 8 to 38> '/u02/vssppln/aimfact1_index01.dbf'; 39> 40> set newname for datafile 9 to 41> '/u02/vssppln/aimfact1_index02.dbf'; 42> 43> set newname for datafile 10 to 44> '/u02/vssppln/aimfact1_index03.dbf'; 45> 46> set newname for datafile 11 to 47> '/u02/vssppln/aimfact1_index04.dbf'; 48> 49> set newname for datafile 12 to 50> '/u02/vssppln/aimfact201.dbf'; 51> 52> set newname for datafile 13 to 53> '/u02/vssppln/aimfact202.dbf'; 54> 55> set newname for datafile 14 to 56> '/u02/vssppln/aimfact2_index01.dbf'; 57> 58> set newname for datafile 15 to 59> '/u02/vssppln/aimfact2_index02.dbf'; 60> 61> set newname for datafile 16 to 62> '/u02/vssppln/aimfact2_index03.dbf'; 63> 64> set newname for datafile 17 to 65> '/u02/vssppln/aimfact2_index04.dbf'; 66> 67> set newname for datafile 18 to 68> '/u02/vssppln/aimstruct01.dbf'; 69> 70> set newname for datafile 19 to 71> '/u02/vssppln/aimstruct_index01.dbf'; 72> 73> set newname for datafile 20 to 74> '/u02/vssppln/aimstruct101.dbf'; 75> 76> set newname for datafile 21 to 77> '/u02/vssppln/aimstruct1_index01.dbf'; 78> 79> set newname for datafile 22 to 80> '/u02/vssppln/aimwork01.dbf'; 81> 82> set newname for datafile 23 to 83> '/u02/vssppln/mipsdata01.dbf'; 84> 85> set newname for datafile 24 to 86> '/u02/vssppln/mipsindex01.dbf'; 87> 88> set newname for datafile 25 to 89> '/u02/vssppln/mipsdata101.dbf'; 90> 91> set newname for datafile
How to Find Client OS type from Oracle 8174......
Dear List, We have TXN processing system with as many as 900 users log in at any point of time. We have the necessity to find what OS type the client is using (Windows 95, NT 2000 etc). I tried thru V$SESSION, V$SESSION_CONNECT_INFO and V$PROCESS, but couldn't find any useful info to get the client OS type. I also looked at the NET8 documentation to see if I can get any such info. I am not successful in getting such information from any source. I tried setting LOG_DIRECTORY_CLIENT and LOG_FILE_CLIENT in SQLNET.ORA, but it is not generating any logfiles. Could some one help me how I should get such info. Appreciate a reply. -- Janardhana
How to Find Client OS type from Oracle 8174......
Dear List, We have TXN processing system with as many as 900 users log in at any point of time. We have the necessity to find what OS type the client is using (Windows 95, NT 2000 etc). I tried thru V$SESSION, V$SESSION_CONNECT_INFO and V$PROCESS, but couldn't find any useful info to get the client OS type. I also looked at the NET8 documentation to see if I can get any such info. I am not successful in getting such information from any source. Could some one help me how I should get such info. Appreciate a reply. -- Janardhana
RE: Large Export Problem ......
Ron, It seems to be the correct answer I believe. If the package is lost I should ask the developer to get it from the version control system and I should compile it. This seems to be the right approach rather than importing the dump file and get a big list of errors. After seeing the responses from the list members, I got a clear idea how to handle the "Large Export Problem". I will break up the export into 4 types(full export with norows, static, non-static and the rest), schedule them to fit our schedule, and use the version control system approach for the packages/stored procs. Importing the dumpfile to restore the package as last resort. Thanks for all those who replied. -- Janardhana -Original Message- Sent: Wednesday, March 26, 2003 10:39 AM To: Multiple recipients of list ORACLE-L Babu, It may not be THE correct answer but I keep all of the packages created by the development community on a disk as the txt or sql statemants used to create them. It helps to have them handy when they need to be modified because of a table structure change, etc. I just give them back to the deveopers to fix and then I recompile on the production server. Also it help to keep them up to date as we are currently migration to a newer version of Oracle while changing the structure of some tables. With the current sql used to create the package that was distroyed you can recreate it easily. Ron >>> [EMAIL PROTECTED] 03/25/03 07:33PM >>> Thanks for the caution. Does any one know if I export with owner= rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > I have a large unarchived decission support database of > size 270gig. We do > take coldbackup of database files every sunday. We also > take export backup > to suplement the coldbackup. Export is taking too much > time which we can't > afford now. I need to reduce the export time to fit the > weekend schedule. In > the last few weeks it is failing as the database is down > for coldbackups > while the export is running. > > The database structure is as follows: > > Partitioned tables size: 200gig > [static partitions(prior years) size 150 gig, and > non-static > partitions(current yr) size 50gig] > non-partitioned tables: 70gig > > I don't need to export static partitions every week. Once > in 3/6months is > OK. I don't think I can eliminate static partitions in > one full export > script/parameter file. Iam thinking of eliminating the > static partitions by > taking export in TABLE mode, which includes only > NON-STATIC partitions and > the remaining NON-PARTITION tables. I may have to > hardcode the table names. > > The database has lots of packages/stored procs which will > be stored in the > dictionary I believe. > > My questions are: > [1] How can I reconstruct a database using this type of > export if needed? > [2] How can I simulate full export in this type (Table > Mode) of export? > [3] How can I export packages/stored procs and import to > new DB if > necessary? > [4] Is there any other way to export the full database > and eliminate the > static partitions in a single step? > [5] What is the best way to solve my export problem?? > > Any ideas are appreciated. > > Thanks, > -- Babu > __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORAC
RE: Large Export Problem ......
Title: RE: Large Export Problem .. Jacques, Your response is helpful. Thanks for your suggestions. I think I should import with IGNORE=N and not "Y". I think it is typo in your response. If not, please clarify. I would utilize your script as an extra precaution. Thanks, -- Babu -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]Sent: Tuesday, March 25, 2003 6:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Large Export Problem .. (see comments below) > -Original Message- > From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]] > > Thanks for the caution. Does any one know if I export with > owner= > Name> rows=N, then drop a package and import from the export file with > IGNORE=N (which is default), will it restore the dropped > package and error > out on all other objects as they are already exist? > > Is this the right way of restoring the dropped package or > will it have any > problems? There are plenty of the tables in the database and > error list will > be too long in this case. Any side effects? or Is there any > other way to > import a dropped pakage? > > A little bit uncomfortable seeing big error list for > restoring one package. > Any ideas or will I have to live with this in case the need > arises? Iam > afraid of any side effects. Answering your question: if you import a schema with IGNORE=Y after dropping only one package, I would think it should work (meaning only import the missing package and show errors for every other object), but I haven't tried it. However, have you considered the following? Are these packages written in-house or part of some third-party software? If they are written in-house then I would suggest some version control system to keep track of which version in the source code is in the database. Then you should be able to recreate any dropped package from the latest version in your version control system. If they are part of some third-party software and you want to back them up, you could always try a custom "select ... from dba_source" script to back them up. example: save_source.sql set linesize 4000 set pagesize 0 set echo off set feedback off set verify off set trimspool on column sort_column noprint column owner noprint column name noprint column type noprint column line noprint accept username char prompt "Enter schema name (case sensitive) " set termout off spool c:\mydir\db_source_&username..sql select 1 as sort_column, object_name as name, object_type as type, to_number (null) as line, 'create ' as sql_text from dba_objects where owner = '&&username' and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE') union all select 2 as sort_column, name as name, type as type, line as line, text as sql_text from dba_source where owner = '&&username' union all select 3 as sort_column, object_name as name, object_type as type, to_number (null) as line, '/' as sql_text from dba_objects where owner = '&&username' and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TYPE') order by name, type, sort_column, line ; spool off exit
RE: Large Export Problem ......
Thanks for the caution. Does any one know if I export with owner= rows=N, then drop a package and import from the export file with IGNORE=N (which is default), will it restore the dropped package and error out on all other objects as they are already exist? Is this the right way of restoring the dropped package or will it have any problems? There are plenty of the tables in the database and error list will be too long in this case. Any side effects? or Is there any other way to import a dropped pakage? A little bit uncomfortable seeing big error list for restoring one package. Any ideas or will I have to live with this in case the need arises? Iam afraid of any side effects. --Babu -Original Message- Sent: Tuesday, March 25, 2003 3:54 PM To: Multiple recipients of list ORACLE-L The problem with direct=y is that imports done from these dumps are then very version dependent. Jared Govindan K <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 03/25/2003 02:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: Large Export Problem .. Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > I have a large unarchived decission support database of > size 270gig. We do > take coldbackup of database files every sunday. We also > take export backup > to suplement the coldbackup. Export is taking too much > time which we can't > afford now. I need to reduce the export time to fit the > weekend schedule. In > the last few weeks it is failing as the database is down > for coldbackups > while the export is running. > > The database structure is as follows: > > Partitioned tables size: 200gig > [static partitions(prior years) size 150 gig, and > non-static > partitions(current yr) size 50gig] > non-partitioned tables: 70gig > > I don't need to export static partitions every week. Once > in 3/6months is > OK. I don't think I can eliminate static partitions in > one full export > script/parameter file. Iam thinking of eliminating the > static partitions by > taking export in TABLE mode, which includes only > NON-STATIC partitions and > the remaining NON-PARTITION tables. I may have to > hardcode the table names. > > The database has lots of packages/stored procs which will > be stored in the > dictionary I believe. > > My questions are: > [1] How can I reconstruct a database using this type of > export if needed? > [2] How can I simulate full export in this type (Table > Mode) of export? > [3] How can I export packages/stored procs and import to > new DB if > necessary? > [4] Is there any other way to export the full database > and eliminate the > static partitions in a single step? > [5] What is the best way to solve my export problem?? > > Any ideas are appreciated. > > Thanks, > -- Babu > __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, Cali
RE: Large Export Problem ......
Thanks for your reply. I encounterd lots of bugs earliar and since then not been using DIRECT=Y option. However, exporting 150gig of static data every week will be of no use either way. -- Babu -Original Message- Sent: Tuesday, March 25, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Babu Why not try 'direct=y' option. This has limitations regarding the platform. Not 100% sure of it. Check with Documentation. It does export very fast. HTH GovindanK --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > I have a large unarchived decission support database of > size 270gig. We do > take coldbackup of database files every sunday. We also > take export backup > to suplement the coldbackup. Export is taking too much > time which we can't > afford now. I need to reduce the export time to fit the > weekend schedule. In > the last few weeks it is failing as the database is down > for coldbackups > while the export is running. > > The database structure is as follows: > > Partitioned tables size: 200gig > [static partitions(prior years) size 150 gig, and > non-static > partitions(current yr) size 50gig] > non-partitioned tables: 70gig > > I don't need to export static partitions every week. Once > in 3/6months is > OK. I don't think I can eliminate static partitions in > one full export > script/parameter file. Iam thinking of eliminating the > static partitions by > taking export in TABLE mode, which includes only > NON-STATIC partitions and > the remaining NON-PARTITION tables. I may have to > hardcode the table names. > > The database has lots of packages/stored procs which will > be stored in the > dictionary I believe. > > My questions are: > [1] How can I reconstruct a database using this type of > export if needed? > [2] How can I simulate full export in this type (Table > Mode) of export? > [3] How can I export packages/stored procs and import to > new DB if > necessary? > [4] Is there any other way to export the full database > and eliminate the > static partitions in a single step? > [5] What is the best way to solve my export problem?? > > Any ideas are appreciated. > > Thanks, > -- Babu > __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= 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: Janardhana Babu Donga 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: Large Export Problem ......
Dennis, Thanks for your reply. Iam not clear about exporting/importing packages/stored procs, specifically importing them. If I need to export packages, I could use owner=FINANCE and rows=N. It will export the structure of the complete schema tables also. If I need to import one package (lost/dropped for some reason) owned by say FINANCE schema, how can I import it? Thanks, -- Babu -Original Message- Sent: Tuesday, March 25, 2003 2:02 PM To: Multiple recipients of list ORACLE-L Babu Another thing to consider. Have you tried to import one of these monster tables? A recovery that takes days may not be acceptable. [1] How can I reconstruct a database using this type of export if needed? Consider the real purpose of a logical backup to restore selected tables or other objects, not an entire database. If your system tablespace gets nailed you will probably use your cold backup to recover. [2] How can I simulate full export in this type (Table Mode) of export? Export to /dev/null? [3] How can I export packages/stored procs and import to new DB if necessary? USER export with rows=n [4] Is there any other way to export the full database and eliminate the static partitions in a single step? You'll need to list tables and omit certain tables. You could export based on a SQL statement such that you omit the data you don't want to export. [5] What is the best way to solve my export problem?? I think you're on the right track. You might also want to look at TRANSPORTABLE TABLESPACES. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 25, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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
RE: Large Export Problem ......
Darrel, Thanks for your response. It is DSS database and so we would like to maintain unarchived mode. If something goes wrong, we will restore the DB from previous weeks coldbackup and apply the transactions to make the DB current. Archived log DB is not necessary in this case. I don't gain anything by using incremental export. Nightly loads will touch every partitioned table and so incremental export will export the complete tables and there won't be any difference between full export and incremental export in this case. I need additional help in resolving my large export problem. Thanks, -- Babu -Original Message- Sent: Tuesday, March 25, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Babu, First, if it were me, I'd put this thing in archive log mode. If we may need to recover between full backups, that is the tried and true means. But, on to your question. I'd look at a plan utilizing incremental exports. You start with a 'base' full export (weekly, monthly, whichever), and do daily incremental or cumulative exports. I'm not going to offer too much detail here because I've never actually used this and because you really should read all of the oracle documentation on this before implementing it ... http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/toc. htm Two things I'll point out from that document: 1) A quote which directly addresses one of your issues... "You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). " 2) A caveat of using this method: "Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release" Please let us know how this turns out for you or if additional help is needed. Thanks, Darrell >>> [EMAIL PROTECTED] 03/25/03 01:59PM >>> Dear List, I have a large unarchived decission support database of size 270gig. We do take coldbackup of database files every sunday. We also take export backup to suplement the coldbackup. Export is taking too much time which we can't afford now. I need to reduce the export time to fit the weekend schedule. In the last few weeks it is failing as the database is down for coldbackups while the export is running. The database structure is as follows: Partitioned tables size: 200gig [static partitions(prior years) size 150 gig, and non-static partitions(current yr) size 50gig] non-partitioned tables: 70gig I don't need to export static partitions every week. Once in 3/6months is OK. I don't think I can eliminate static partitions in one full export script/parameter file. Iam thinking of eliminating the static partitions by taking export in TABLE mode, which includes only NON-STATIC partitions and the remaining NON-PARTITION tables. I may have to hardcode the table names. The database has lots of packages/stored procs which will be stored in the dictionary I believe. My questions are: [1] How can I reconstruct a database using this type of export if needed? [2] How can I simulate full export in this type (Table Mode) of export? [3] How can I export packages/stored procs and import to new DB if necessary? [4] Is there any other way to export the full database and eliminate the static partitions in a single step? [5] What is the best way to solve my export problem?? Any ideas are appreciated. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Darrell Landrum 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Se
RE: Is range partitioning possible on part of varchar2 column ???
This seems to be a good idea. I will see if this is acceptable to my people. Earliar I suggested to change to date field, and was not acceptable for them as there seems plenty of code needs to be changed. I will see if this change is acceptable for them. One thing I could understand clearly from the LIST MEMBERS is that it is not at all possible to range partition without changing the column type/contents. I have two options now, one with what you suggested. Thanks for your help and thanks for all those who replied. -- Babu -Original Message- Sent: Thursday, March 13, 2003 8:34 PM To: Multiple recipients of list ORACLE-L ??? Babu, On a slightly different approach, is it possible to update the column to the format MON, from the present MON? If so, then there is hope. You could create the partitions like this PARTITIONING BY RANGE (REPORT_CYCLE_CD) ( PARTITION P1998 VALUES LESS THAN ('1999%'), PARTITION P1999 VALUES LESS THAN ('2000%'), PARTITION P2000 VALUES LESS THAN ('2001%'), . PARTITION PMAX VALUES LESS THAN (maxvalue) ) Hope this helps. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, March 13, 2003 5:19 PM > Babu > I don't think partitions are clearly documented anywhere. Here is some SQL > that works so you can see how to use a date function. It partitions on two > columns, but I wanted you to see something that works. > >add partition sum_fy_28 > values less than ('FY', to_date('02012003','mmdd')) > tablespace data_fy_28 > > -Original Message- > Sent: Thursday, March 13, 2003 3:14 PM > To: Multiple recipients of list ORACLE-L > ?? > > > Dear List, > > I have a table of size approx 10gig, and I need to partition based on the > YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The > data in the column of format "MON" . I need to partition the table based > on the year , that is, substr(report_cycle_cd, 4,4). > > Substr function doesn't seem to be permitted in the partitioning syntax and > so am getting errors. Only TO_DATE function seems to be permitted. Since it > is not a date column, I would like to know if there is a way to RANGE > partition the table, instead of HASH partitioning. > > Appreciate any suggestions. > > Thanks, > -- Babu > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Janardhana Babu Donga > 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: DENNIS 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: Arup Nanda 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
RE: Is range partitioning possible on part of varchar2 column ???
Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Khedr, Waleed 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: Janardhana Babu Donga 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).
Is range partitioning possible on part of varchar2 column ??????
Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format "MON" . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: RMAN backup with MULTIPLE CHANNELS ......
Mandar, Thanks for your reply. Iam in the process of checking with my Netbackup Administrator about the parameter: Max_jobs_per_client. After seeing your E-Mail, I too looked into the online manual and it seems the value should be = Number of streams X No.Of Classes. In my case No.Of classes equals to 1, and since I would like to use 3 tape drives thru 3 channels, I think Max_Jobs_per_client must be set to 3. I forwarded the info to my Netbackup Admin. Thanks for the info. -- Babu -Original Message- Sent: Thursday, February 20, 2003 2:52 PM To: Multiple recipients of list ORACLE-L also check "Maximum Jobs per client". you will find this setting in the Netbackup preferences window (Netbackup Java console). -Mandar > -Original Message- > From: Janardhana Babu Donga [mailto:[EMAIL PROTECTED]] > Sent: Thursday, February 20, 2003 8:44 AM > To: Multiple recipients of list ORACLE-L > Subject: RMAN backup with MULTIPLE CHANNELS .. > > > Dear List, > > I have changed my RMAN backup script to use multiple > channels(3) as we have > plenty of tape drives. It is taking the same amount of time > as it used to > with single channel. The DB size is 120gig. The output > indicates it is using > three channels while backing up. Anyone has any idea why the multiple > channels not reduced the backup time. Iam using Veritas Netbackup. > Thanks in advance for any help. > > -- Babu > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Janardhana Babu Donga > 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: Mandar A. Ghosalkar 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: Janardhana Babu Donga 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: RMAN backup with MULTIPLE CHANNELS ......
Jared, Please let me know what info you need. I would collect it from my Netbackup administrator and let you know. Thanks, -- Babu -Original Message- Sent: Thursday, February 20, 2003 1:02 PM To: Multiple recipients of list ORACLE-L One possibility is that the backup job is already maxing out the network connection between the client system and the Veritas NetBackup master server and/or media server. If you have a 10megabit network and LTO drives, the network will never be able to keep the tapes streaming. Adding 3 channels won't help if your network can't keep up. I'm speculating here, as you don't really supply enough info about your environment. Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 02/20/2003 10:47 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: RMAN backup with MULTIPLE CHANNELS .. Dennis, Thanks for your reply. Iam taking tape backup with 3 channels as we have multiple tape drives. I don't know where the bottleneck is. In the RMAN output I see it is using all the three channels simultaneously with 10 datafiles per channel. But Iam not getting the throuput. It is taking the same amount of time as it used to take for single channel. I don't know where to look, and don't know how to fix the problem and take advantage of multiple channels. -- Babu -Original Message- Sent: Thursday, February 20, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Babu - We allocated multiple channels on an RMAN disk backup and the backup time was reduced. I would suggest you try to figure out where your bottleneck is. Are you writing to multiple tapes? Actually, RMAN has several features to keep the tape streaming, you may want to look at these. If your tape isn't streaming, you might get more throughput from a single tape drive than you are now. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 20, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Dear List, I have changed my RMAN backup script to use multiple channels(3) as we have plenty of tape drives. It is taking the same amount of time as it used to with single channel. The DB size is 120gig. The output indicates it is using three channels while backing up. Anyone has any idea why the multiple channels not reduced the backup time. Iam using Veritas Netbackup. Thanks in advance for any help. -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message
RE: RMAN backup with MULTIPLE CHANNELS ......
Dennis, Thanks for your reply. Iam taking tape backup with 3 channels as we have multiple tape drives. I don't know where the bottleneck is. In the RMAN output I see it is using all the three channels simultaneously with 10 datafiles per channel. But Iam not getting the throuput. It is taking the same amount of time as it used to take for single channel. I don't know where to look, and don't know how to fix the problem and take advantage of multiple channels. -- Babu -Original Message- Sent: Thursday, February 20, 2003 9:39 AM To: Multiple recipients of list ORACLE-L Babu - We allocated multiple channels on an RMAN disk backup and the backup time was reduced. I would suggest you try to figure out where your bottleneck is. Are you writing to multiple tapes? Actually, RMAN has several features to keep the tape streaming, you may want to look at these. If your tape isn't streaming, you might get more throughput from a single tape drive than you are now. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 20, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Dear List, I have changed my RMAN backup script to use multiple channels(3) as we have plenty of tape drives. It is taking the same amount of time as it used to with single channel. The DB size is 120gig. The output indicates it is using three channels while backing up. Anyone has any idea why the multiple channels not reduced the backup time. Iam using Veritas Netbackup. Thanks in advance for any help. -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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).
RMAN backup with MULTIPLE CHANNELS ......
Dear List, I have changed my RMAN backup script to use multiple channels(3) as we have plenty of tape drives. It is taking the same amount of time as it used to with single channel. The DB size is 120gig. The output indicates it is using three channels while backing up. Anyone has any idea why the multiple channels not reduced the backup time. Iam using Veritas Netbackup. Thanks in advance for any help. -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Programming languages that make DBA's lives easier
What are the good books you guys recommend for Shell Programming? I have old edition of UNIX shells by example (by Ellie Quigley). I need to replace this with some good book. Do I need to learn perl before reading PERL for DBAs? If so, what are the good perl books do you guys recommend? -- Babu -Original Message- Sent: Tuesday, February 18, 2003 1:04 PM To: Multiple recipients of list ORACLE-L I thought it might be worthwhile to say WHY I prefer ksh for most scripting. As one writer put it, use a language you know. I've used ksh for years, so that is certainly one big factor in preferring it. But, that aside, if you use mostly the Bourne Shell syntax and stay away from the some of the Korn Shell syntactical candy that doesn't do anything extra, then your scripts are very easy to read, and are mostly self-documenting. For me, that is a big advantage. If you use ksh, then you need to become reasonably proficient with a few of the more important utilities such as sed and awk/nawk/gawk and become familiar with good old regular expressions (wonderful stuff!). One area where ksh is weak is its inability to set up a two-way socket with a child process like sqlplus. Perl can do this (for example, by using IPC::Open2). With ksh, you can send to the child, and you can receive from the child, but you can't converse with the child. Even with the coprocess (which is a pain to use), you still have one-way communication at any given time. However, 99% of the time, ksh will work fine. For that other 1% (OK, maybe 2%), you either figure out a workable compromise with ksh, or you use perl. If you want some reading material, my recommendation is: Unix System V: A Practical Guide by Mark G. Sobel (There is also a version for BSD) This is a outstanding overview of Unix including sh, ksh, and csh programming; as well as good introductions to sed, awk, and regular expressions. It also has chapters on emacs and vi ("Remember, vi is your friend."). Amazon has the 3rd edition listed with a publish date of 1995. So there probably is some rather outdated info in the book on stuff like gopher, archie, possibly Mosaic (Oh hey, cool!). Just ignore this. All the other stuff is still completely relevant and presented in what I think is the perfect level of detail -- not too verbose; not too terse -- with good examples. Eventually, to get seriously cooking with sed and awk, you will need to get something like the O'Reilly sed and awk book. But, for ksh programming: If you work through the Bourne Shell and Korn Shell chapters of the Sobel book, it is likely that this book is all you will ever need. Another wonderful thing about this book is that it doesn't use the current trend of big fonts with big margins and thick paper to create a monster sized book. Instead, you get lots of information in a book that takes up little shelf space. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Janardhana Babu Donga 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: Partition and primary key
Title: Partition and primary key You need to have 15 to 20gig temp space to do this, assuming you are equipartitioning the primary key index. -Original Message-From: NGUYEN Philippe (Cetelem) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 6:53 AMTo: Multiple recipients of list ORACLE-LSubject: Partition and primary key Hi List, We are using Oracle 8.1.7 (64 bits) with Solaris 8 I have a big table with over 300 millions rows (10 Go), We have partinionned this table by range of date. When we try to create a constraint primary key (with dedicated tablespace), we fail each time because the database was unable to extend temp segment. 1/ Instead of enlarging the temp tablespace undefinatly , is it possible to create this constraint for each partition so that it would reduce the amount of temp segment required ? 2/ If not, creating an unique will also be efficient for join ? Thanks in advance! Philippe
RE: Size of a Long Field
Try this, Select table_name,column_name,data_length,data_type from dba_tab_columns where owner = '' and data_type='LONG' ; -- Babu -Original Message-From: Ed Bittel [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 6:53 AMTo: Multiple recipients of list ORACLE-LSubject: Size of a Long Field How do you determine the size, in bytes, of a long field? Please. No tape measure jokes. Ed
RE: index hint ignored?
Title: Message Try Analyzing the table first and issue the select stmt. -- Babu -Original Message-From: Cunningham, Gerald [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 11:56 AMTo: Multiple recipients of list ORACLE-LSubject: RE: index hint ignored? I've tried it both ways, with the comma and without - same result. -Original Message-From: K Gopalakrishnan [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 2:13 PMTo: Multiple recipients of list ORACLE-LSubject: RE: index hint ignored? Hi, THe syntax is incorrect. Incorrect HINTs are treated as comments. replace the comma with blank space and your hint will work as expected. Best Regards,K Gopalakrishnan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham, GeraldSent: Friday, January 31, 2003 10:31 AMTo: Multiple recipients of list ORACLE-LSubject: index hint ignored? Hi there. I have a non-unique index on a table, and I'm trying to force Oracle to use the index - but it always does a FTS. Why? (I've tried it with and without the alias) SQL> set autotrace traceonlySQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year 2 FROM TIREADVISOR.vehicle A 3 ORDER BY veh_year DESC; 20 rows selected. Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=20 Bytes=80) 1 0 SORT (ORDER BY) (Cost=118 Card=20 Bytes=80) 2 1 SORT (UNIQUE) (Cost=67 Card=20 Bytes=80) 3 2 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 Card=19607 Bytes=78428) === select TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER = 'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by 1,2,4,3TABLE_NAME INDEX_NAME-- --COLUMN_NAME COLUMN_POSITION ---VEHICLE VEHICLE_PKVEH_ID 1 VEHICLE VEHICLE_VEH_YEAR_INDXVEH_YEAR 1 Thanks for any help! - Jerry
RE: RMAN - problems restoring to clone server
Contd It looks to me that you are trying to restore to Ariel from Delenn (where catalog database resides). You must log in to the Ariel Box, switch to the DB using "oraenv", then connect to DB on Ariel( which is in nomount state) and the catalog DB (Delenn) as: rman target / catalog rman/rman@. then follow the instructions below. Make sure you have permission to restore to ARIEL from the backup taken by BRAIN. -- Babu I do this many times with Veritas NetBackup. The steps may be similar for legato with slight variation. [1] By Default Netbackup or Legato restores files only to the client from which the files were backed up(Brain). Your Netbackup administrator should configure to let you restore to an alternate client(Ariel). There may be similar steps for Legato. [2] Startup nomount [3] run { allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=brain..com; export NB_ORA_CLIENT)"; restore controlfile; alter database mount; } Look for similar parameter for Legato, may be NSR_CLIENT in place of NB_ORA_CLIENT. [4] run { allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=brain..com; export NB_ORA_CLIENT)"; restore database; } Hope this helps. -- Babu -Original Message- Sent: Friday, January 31, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Legato Networker version 6.1.1.Build.238 OS: SunOS 5.6 Database: Oracle8 Enterprise Edition Release 8.0.4.4.0 TIA! >From: Jared Still <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]> >Subject: Re: RMAN - problems restoring to clone server >Date: Fri, 31 Jan 2003 07:02:06 -0800 > > >What is your MML? > >Jared > >On Thursday 30 January 2003 11:56, Gary Jackson wrote: > > The support analyst handling my TAR gave up, perhaps someone on here can > > help me out... > > > > I have an 8.0.4.4.0 database. I used RMAN to perform a full backup of >my > > database on server A (Brain). The RMAN recovery catalog database is on > > server B (Delenn). I am attempting to restore it to a 3rd instance which >is > > on server C (Ariel). On server C I created a new instance and it is in > > startup nomount currently. From server B I am attempting to restore to > > server C. > > > > run { > > allocate channel t1 type 'SBT_TAPE' parms > > 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)'; > > set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf'; > > set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf'; > > set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf'; > > set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf'; > > restore database; > > release channel t1; > > } > > > > > > > > > > RMAN-10032: unhandled exception during execution of job step 1: >ORA-06512: > > at line 137 > > RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry > > possible > > ORA-19507: failed to retrieve sequential file, > > handle="Incr_level0_db_DB1_151_1.hot", parms="" > > ORA-27007: failed to open file > > Additional information: 7009 > > Additional information: 2 > > ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925 > > RMAN-10031: ORA-19624 occurred during call to > > DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE > > > > > > > > _ > > Help STOP SPAM with the new MSN 8 and get 2 months FREE* > > http://join.msn.com/?page=features/junkmail _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson 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: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www
RE: RMAN - problems restoring to clone server
I do this many times with Veritas NetBackup. The steps may be similar for legato with slight variation. [1] By Default Netbackup or Legato restores files only to the client from which the files were backed up(Brain). Your Netbackup administrator should configure to let you restore to an alternate client(Ariel). There may be similar steps for Legato. [2] Startup nomount [3] run { allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=brain..com; export NB_ORA_CLIENT)"; restore controlfile; alter database mount; } Look for similar parameter for Legato, may be NSR_CLIENT in place of NB_ORA_CLIENT. [4] run { allocate channel t1 type 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=brain..com; export NB_ORA_CLIENT)"; restore database; } Hope this helps. -- Babu -Original Message- Sent: Friday, January 31, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Legato Networker version 6.1.1.Build.238 OS: SunOS 5.6 Database: Oracle8 Enterprise Edition Release 8.0.4.4.0 TIA! >From: Jared Still <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED], "Gary Jackson" <[EMAIL PROTECTED]> >Subject: Re: RMAN - problems restoring to clone server >Date: Fri, 31 Jan 2003 07:02:06 -0800 > > >What is your MML? > >Jared > >On Thursday 30 January 2003 11:56, Gary Jackson wrote: > > The support analyst handling my TAR gave up, perhaps someone on here can > > help me out... > > > > I have an 8.0.4.4.0 database. I used RMAN to perform a full backup of >my > > database on server A (Brain). The RMAN recovery catalog database is on > > server B (Delenn). I am attempting to restore it to a 3rd instance which >is > > on server C (Ariel). On server C I created a new instance and it is in > > startup nomount currently. From server B I am attempting to restore to > > server C. > > > > run { > > allocate channel t1 type 'SBT_TAPE' parms > > 'ENV=(NSR_SERVER=delenn,NSR_CLIENT=ariel)'; > > set newname for datafile 1 to '/usr/oracle/DB1/datfiles/system01.dbf'; > > set newname for datafile 2 to '/usr/oracle/DB1/datfiles/rbs01.dbf'; > > set newname for datafile 3 to '/usr/oracle/DB1/datfiles/temp01.dbf'; > > set newname for datafile 4 to '/usr/oracle/DB1/datfiles/user01.dbf'; > > restore database; > > release channel t1; > > } > > > > > > > > > > RMAN-10032: unhandled exception during execution of job step 1: >ORA-06512: > > at line 137 > > RMAN-10035: exception raised in RPC: ORA-19624: operation failed, retry > > possible > > ORA-19507: failed to retrieve sequential file, > > handle="Incr_level0_db_DB1_151_1.hot", parms="" > > ORA-27007: failed to open file > > Additional information: 7009 > > Additional information: 2 > > ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 925 > > RMAN-10031: ORA-19624 occurred during call to > > DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE > > > > > > > > _ > > Help STOP SPAM with the new MSN 8 and get 2 months FREE* > > http://join.msn.com/?page=features/junkmail _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gary Jackson 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: Janardhana Babu Donga 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: Restoring RMAN backups to different host ......solved
Dear List, Thanks for all those who replied. One of my collegue helped me in resolving the issue yesterday. Iam using Veritas Netbackup software. If I set the UNIX env variable NB_ORA_CLIENT=host_A and also in RMAN script parms, then it is restoring the backups from host_A. run { allocate channel ch1 type 'sbt_tape'; restore controlfile parms="ENV=(NB_ORA_CLIENT=host_A; export NB_ORA_CLIENT)"; } I also set CLIENT_NAME=host_A in /usr/openv/netbackup/bp.conf file. As per Netbackup manual, this should work, but not sure why it is not working unless I set NB_ORA_CLIENT. Thanks, -- Babu -Original Message- Sent: Thursday, September 05, 2002 7:18 PM To: Multiple recipients of list ORACLE-L Janardhana Babu Donga, hi, which backup software are u using? as far as i know, you must setup something in the second node so that MML software can THINK it is the node that is backed up, so it can restore back. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] === 2002-09-05 16:13:00 ,you wrote£º=== >Dear List, > >Iam following the procedure to restore database from RMAN backup from HOST_A >to HOST_B with a recovery catalog. > >1. I copied the init.ora file to HOST_B >2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to >'rtest' database using . oraenv >3. issued: rman target / catalog rman/rman@ >4. startup nomount; >5. run { > allocate channel ch1 type 'sbt_tape'; > restore controlfile; > } > >It generates the following error. "list backup of controlfile" is showing up >the backup entries. > >RMAN-00571: === >RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === >RMAN-00571: === >RMAN-03002: failure during compilation of command >RMAN-03013: command type: restore >RMAN-03007: retryable error occurred during execution of command: IRESTORE >RMAN-07004: unhandled exception during command execution on channel ch1 >RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve >sequential file, handle="nre1u1kk_1_1", parms="" >ORA-27029: skgfrtrv: sbtrestore returned error >ORA-19511: sbtrestore: Backup file not found. >RMAN-10031: ORA-19624 occurred during call to >DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE > >Could someone help me in resolving the issue. > >Thanks, >-- Babu >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Janardhana Babu Donga > 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). = = = = = = = = = = = = = = = = = = = = zhu chao [EMAIL PROTECTED] 2002-09-06 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao 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: Janardhana Babu Donga 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).
Restoring RMAN backups to different host ......
Dear List, Iam following the procedure to restore database from RMAN backup from HOST_A to HOST_B with a recovery catalog. 1. I copied the init.ora file to HOST_B 2. on HOST_B, I made an entry in oratab for 'rtest' database and switch to 'rtest' database using . oraenv 3. issued: rman target / catalog rman/rman@ 4. startup nomount; 5. run { allocate channel ch1 type 'sbt_tape'; restore controlfile; } It generates the following error. "list backup of controlfile" is showing up the backup entries. RMAN-00571: === RMAN-00569: === ERROR MESSAGE STACK FOLLOWS === RMAN-00571: === RMAN-03002: failure during compilation of command RMAN-03013: command type: restore RMAN-03007: retryable error occurred during execution of command: IRESTORE RMAN-07004: unhandled exception during command execution on channel ch1 RMAN-10035: exception raised in RPC: ORA-19507: failed to retrieve sequential file, handle="nre1u1kk_1_1", parms="" ORA-27029: skgfrtrv: sbtrestore returned error ORA-19511: sbtrestore: Backup file not found. RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE Could someone help me in resolving the issue. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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).
Restoring RMAN backups to different host ....
Dear List, I have a database 'rtest' and catalog database 'rcat' on machine: A and Iam taking regular RMAN TAPE backups of 'rtest' database using the catalog database 'rcat'. I need to restore the latest RMAN tape backup of 'rtest' database onto another brand new box: B. Is there a way to restore other than using the "duplicate database feature" ?? I need to use RMAN backup only in order to restore onto Machine B. Copying the datafiles on to tape and retoring on to machine B is not feasible (as the original database I would like to restore ultimately is a very large production database and downtime is not acceptable). I have already setup access to RMAN backups from HOST B using netbackup. Please suggest me a simplified solution for restoring RMAN backups on to a different host. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: runInstaller Error ....
Thanks for the reply. I searched metalink and found couple of relevant articles, (not the exact ones), but they didn't help much. I raised Tar with oracle and submitted the error message from rda.sh, .profile, .login, .cshrc and .kshrc files. I would post the solution if I get any from Oracle. -- Babu -Original Message- Sent: Sunday, July 28, 2002 3:18 PM To: Multiple recipients of list ORACLE-L Babu This problem is confusing to me. Perhaps someone else on the list has an idea. It seems your problem may have to do with LD_LIBRARY_PATH. I can't think why /lib would be getting added to the first of your LD_LIBRARY_PATH. I am not familiar with rda.sh. Usually, in a path like this, the software simply searches each directory in turn and if it searches all of them without finding what it is searching for, then it returns an error. A wild idea would be for you to create an empty /lib directory on your system in the hope that it would get searched and then the code will move on to the next (correct) library. Maybe someone Monday morning will see this and have a better idea. Have you searched Metalink for this error? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, July 26, 2002 5:23 PM To: Multiple recipients of list ORACLE-L I did the setting for LD_LIBRARY_PATH and is not working. I also executed rda.sh script (REMOTE DIAGNOSTIC AGENT for UNIX), It came out with an error as follows: Argument LD_LIBRARY_PATH=/lib:/usr/local/oracle/817/lib:/usr/lib not recognized and was forced to exit. I don't know how /lib was picked up. When issued echo $LD_LIBRARY_PATH, it displayed as follows: /usr/local/oracle/817/lib:/usr/lib Please let me know how I should proceed with the error. Thanks, -- Babu -Original Message- Sent: Friday, July 26, 2002 2:04 PM To: Multiple recipients of list ORACLE-L Babu Try setting LD_LIBRARY_PATH to $ORACLE_HOME/lib -Original Message- Sent: Friday, July 26, 2002 3:40 PM To: Multiple recipients of list ORACLE-L Dear list, When I execute runInstaller from /cdrom/disk1, I get the following error message: Initializing java virtual machine from /tmp/OraInstall/jre/bin/jre. Please wait... Class not found: LD_LIBRARY_PATH=/lib:/usr/lib Iam unable to start the installer. I unset LD_LIBRARY_PATH, and still getting the error. Iam installing 8.1.7.0.0(64bit) on Sun Sparc Solaris 2.8 (64bit). Please let me know if anyone has any ideas. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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: DENNIS 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 mess
RE: runInstaller Error ....
I did the setting for LD_LIBRARY_PATH and is not working. I also executed rda.sh script (REMOTE DIAGNOSTIC AGENT for UNIX), It came out with an error as follows: Argument LD_LIBRARY_PATH=/lib:/usr/local/oracle/817/lib:/usr/lib not recognized and was forced to exit. I don't know how /lib was picked up. When issued echo $LD_LIBRARY_PATH, it displayed as follows: /usr/local/oracle/817/lib:/usr/lib Please let me know how I should proceed with the error. Thanks, -- Babu -Original Message- Sent: Friday, July 26, 2002 2:04 PM To: Multiple recipients of list ORACLE-L Babu Try setting LD_LIBRARY_PATH to $ORACLE_HOME/lib -Original Message- Sent: Friday, July 26, 2002 3:40 PM To: Multiple recipients of list ORACLE-L Dear list, When I execute runInstaller from /cdrom/disk1, I get the following error message: Initializing java virtual machine from /tmp/OraInstall/jre/bin/jre. Please wait... Class not found: LD_LIBRARY_PATH=/lib:/usr/lib Iam unable to start the installer. I unset LD_LIBRARY_PATH, and still getting the error. Iam installing 8.1.7.0.0(64bit) on Sun Sparc Solaris 2.8 (64bit). Please let me know if anyone has any ideas. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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 successfully execute "dmesg" command from Oracle accou
Suzy, Thanks for your reply. I needed it because Oracle has supplied a script: InstallPrep.sh on Metalink which check the server to ensure that it has adequate resources to successfully install Oracle9i database software. It should be run as "oracle" user only, but it executes "dmesg" command. It is unable to execute "dmesg" and so Iam not getting proper output. Someone has replied and advised me to have the SUID bit set on dmesg. I would try that and see if it works. The script can't be run as "root" either. -- Babu -Original Message- Sent: Monday, June 03, 2002 12:39 PM To: Multiple recipients of list ORACLE-L account?? To read kernel memory requires root privs, so don't think there is a way to do that from the oracle account without some undesirable hack. Janardhana Babu Donga wrote: > > Dear List, > > When I run "dmesg" command from oracle account on HP-UX 11.0, it responds as > "can't read kernel memory". The "dmesg" has the permissions: -r-xr-xr-x. > Could someone tell me what needs to be done in order to execute "dmesg" > command successfully from "oracle" account. > > Thanks, > --Babu > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Janardhana Babu Donga > 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: Suzy Vordos 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: Janardhana Babu Donga 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 successfully execute "dmesg" command from Oracle account??
Dear List, When I run "dmesg" command from oracle account on HP-UX 11.0, it responds as "can't read kernel memory". The "dmesg" has the permissions: -r-xr-xr-x. Could someone tell me what needs to be done in order to execute "dmesg" command successfully from "oracle" account. Thanks, --Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DBA_EXTENTS problem .... Solved
Thanks for all those who helped me in fixing the problem. I deleted the SYS schema statistics using dbms_stats.delete_schema_stats('SYS') and I need to restart the database. Iam changing my analyze script from database analyze to schema analyze. Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 2:06 PM To: Multiple recipients of list ORACLE-L Try this: dbms_stats.delete_database_stats(); Janardhana Babu Donga wrote: > > I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy > and made sure that they were gone by issuing the query: > > select table_name,owner > from dba_tables > where owner in ('SYS','SYSTEM') > and last_analyzed is not null; > > It returned no rows this time. But why is my query: select count(*) from > dba_extents is still sitting there? Is there anything else I should do? > Restart the DB/something?? > > Thanks for any response. > -- Babu > > -Original Message- > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 1:19 PM > To: Multiple recipients of list ORACLE-L > > I knew someone on the list would have it :) thanks Suzy. > > actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to > fix > the problem and then change the dbms_stats command to > dbms_stats.gather_schema_stats instead of database > > |+---> > || | > || | > || lvordos@qwest| > || .com | > || | > || 05/09/2002 | > || 03:44 PM | > || Please | > || respond to | > || ORACLE-L | > || | > |+---> > >| > || > | To: [EMAIL PROTECTED] | > | cc: (bcc: Rachel Carmichael) | > | Subject: Re: DBA_EXTENTS problem | > >| > > dbms_stats.gather_database_stats has a bug, it analyzes SYS objects. > > From Metalink: > Bug:1422285 is a severity 3 bug that was logged for the > dbms_stats.gather_database_stats collecting statistics on the data > dictionary objects (FET$ was the example). This bug is fixed in 9i. A > workaround is to execute dbms_stats.gather_database_statistics + > dbms_stats.delete_schema_stats('SYS'). > > Janardhana Babu Donga wrote: > > > > I have recently changed the analyze script. Earliar it was > > dbms_utility.analyze_schema(...) statement, It is now changed to > > dbms_stats.gather_database_stats(); > > > > Is this a problem? > > > > The response time for select count(*) from dba_extents is also 30 minutes. > > It is not specific to any table. > > Thanks, > > -- Babu > > > > -Original Message- > > [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, May 09, 2002 11:36 AM > > To: Multiple recipients of list ORACLE-L > > > > that's interesting... since the data dictionary is NOT analyzed, setting > > optimizer_mode=choose would force the query against dba_extents to RULE > > which it > > what it is supposed to be doing anyway, Hm. The question now is, what is > the > > optimizer_mode set to when the problem happens? Did any of the data > > dictionary > > tables get accidentally analyzed? And -- how many extents are in use in > the > > database? Could it just be a symptom of missized tables and indexes so > that > > the > > number of extents is way high? > > > > Although Oracle is *supposed* to allow unlimited extents, in practice > > anything > > higher than 4096 extents in an object (at least in 8i) tends to slow > things > > down. > > > > |+---> > > || | > > || | > > || jack_silvey@y| > > || ahoo.com | > > || | > > || 05/09/2002 | > > || 02:18 PM | > > || Please | > > || respond to | > > || ORACLE-L | > > || | > > |+---> > > >| > > |
RE: DBA_EXTENTS problem
I have deleted the statistics from 'SYS' and SYSTEM' as suggested by Suzy and made sure that they were gone by issuing the query: select table_name,owner from dba_tables where owner in ('SYS','SYSTEM') and last_analyzed is not null; It returned no rows this time. But why is my query: select count(*) from dba_extents is still sitting there? Is there anything else I should do? Restart the DB/something?? Thanks for any response. -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 1:19 PM To: Multiple recipients of list ORACLE-L I knew someone on the list would have it :) thanks Suzy. actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to fix the problem and then change the dbms_stats command to dbms_stats.gather_schema_stats instead of database |+---> || | || | || lvordos@qwest| || .com | || | || 05/09/2002 | || 03:44 PM | || Please | || respond to | || ORACLE-L | || | |+---> >| || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | >| dbms_stats.gather_database_stats has a bug, it analyzes SYS objects. >From Metalink: Bug:1422285 is a severity 3 bug that was logged for the dbms_stats.gather_database_stats collecting statistics on the data dictionary objects (FET$ was the example). This bug is fixed in 9i. A workaround is to execute dbms_stats.gather_database_statistics + dbms_stats.delete_schema_stats('SYS'). Janardhana Babu Donga wrote: > > I have recently changed the analyze script. Earliar it was > dbms_utility.analyze_schema(...) statement, It is now changed to > dbms_stats.gather_database_stats(); > > Is this a problem? > > The response time for select count(*) from dba_extents is also 30 minutes. > It is not specific to any table. > Thanks, > -- Babu > > -Original Message- > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 11:36 AM > To: Multiple recipients of list ORACLE-L > > that's interesting... since the data dictionary is NOT analyzed, setting > optimizer_mode=choose would force the query against dba_extents to RULE > which it > what it is supposed to be doing anyway, Hm. The question now is, what is the > optimizer_mode set to when the problem happens? Did any of the data > dictionary > tables get accidentally analyzed? And -- how many extents are in use in the > database? Could it just be a symptom of missized tables and indexes so that > the > number of extents is way high? > > Although Oracle is *supposed* to allow unlimited extents, in practice > anything > higher than 4096 extents in an object (at least in 8i) tends to slow things > down. > > |+---> > || | > || | > || jack_silvey@y| > || ahoo.com | > || | > || 05/09/2002 | > || 02:18 PM | > || Please | > || respond to | > || ORACLE-L | > || | > |+---> > >| > || > | To: [EMAIL PROTECTED] | > | cc: (bcc: Rachel Carmichael) | > | Subject: Re: DBA_EXTENTS problem | > >| > > Babu, > > We had a similiar problem, and setting optimizer_mode > = choose in our session solved it. Something to do > with optimizer and DD access. Give that a try. I had > the same problem with DBA_INDEXES and that fixed it. > > hth, > > Jack > > --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > > Dear List, > > > > When I query dba_extents , Iam getting response > > after half an hour to 40 > > minutes, but when I query any other dictionary view > > it is spontaneous. > > Everything else is fine in the database and there > > are no problems, except > > the above problem. Iam not getting any
RE: DBA_EXTENTS problem
It is waiting on the event: db file scattered read Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 12:41 PM To: Multiple recipients of list ORACLE-L Babu, While you're waiting, run this query to see what you're waiting on. Jared select s.username username, e.event event, s.sid, e.p1text, e.p1, e.p2text, e.p2, e.wait_time, e.seconds_in_wait, e.state from v$session s, v$session_wait e where s.username is not null and s.sid = e.sid and s.username like upper('&uusername') -- skip sqlnet idle session messages and e.event not like '%message%client' order by s.username, upper(e.event); Janardhana Babu Donga <[EMAIL PROTECTED]> 05/09/2002 11:03 AM To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject:RE: DBA_EXTENTS problem Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. I would E-Mail after getting the response. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
I have just now deleted the 'SYS' schema statistics and executed select count(*) from dba_extents, and there is no improvement. It is still sitting there. Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 12:45 PM To: Multiple recipients of list ORACLE-L dbms_stats.gather_database_stats has a bug, it analyzes SYS objects. >From Metalink: Bug:1422285 is a severity 3 bug that was logged for the dbms_stats.gather_database_stats collecting statistics on the data dictionary objects (FET$ was the example). This bug is fixed in 9i. A workaround is to execute dbms_stats.gather_database_statistics + dbms_stats.delete_schema_stats('SYS'). Janardhana Babu Donga wrote: > > I have recently changed the analyze script. Earliar it was > dbms_utility.analyze_schema(...) statement, It is now changed to > dbms_stats.gather_database_stats(); > > Is this a problem? > > The response time for select count(*) from dba_extents is also 30 minutes. > It is not specific to any table. > Thanks, > -- Babu > > -Original Message- > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 11:36 AM > To: Multiple recipients of list ORACLE-L > > that's interesting... since the data dictionary is NOT analyzed, setting > optimizer_mode=choose would force the query against dba_extents to RULE > which it > what it is supposed to be doing anyway, Hm. The question now is, what is the > optimizer_mode set to when the problem happens? Did any of the data > dictionary > tables get accidentally analyzed? And -- how many extents are in use in the > database? Could it just be a symptom of missized tables and indexes so that > the > number of extents is way high? > > Although Oracle is *supposed* to allow unlimited extents, in practice > anything > higher than 4096 extents in an object (at least in 8i) tends to slow things > down. > > |+---> > || | > || | > || jack_silvey@y| > || ahoo.com | > || | > || 05/09/2002 | > || 02:18 PM | > || Please | > || respond to | > || ORACLE-L | > || | > |+---> > >| > || > | To: [EMAIL PROTECTED] | > | cc: (bcc: Rachel Carmichael) | > | Subject: Re: DBA_EXTENTS problem | > >| > > Babu, > > We had a similiar problem, and setting optimizer_mode > = choose in our session solved it. Something to do > with optimizer and DD access. Give that a try. I had > the same problem with DBA_INDEXES and that fixed it. > > hth, > > Jack > > --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > > Dear List, > > > > When I query dba_extents , Iam getting response > > after half an hour to 40 > > minutes, but when I query any other dictionary view > > it is spontaneous. > > Everything else is fine in the database and there > > are no problems, except > > the above problem. Iam not getting any clue how to > > fix this. Iam thinking of > > running catalog.sql and catproc.sql as a last > > resort. Iam not sure it would > > fix the problem. Please let me know if there is any > > other way to fix this > > problem. > > > > Thanks, > > Babu > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Janardhana Babu Donga > > 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 Yahoo!? > Yahoo! Shopping - Mother's Day is May 12th! > http://shopping.yahoo.com > -- &
RE: DBA_EXTENTS problem
It is waiting on the event: db file scattered read. Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 11:33 AM To: [EMAIL PROTECTED] Cc: Janardhana Babu Donga Babu, While you're waiting, run this query to see what you're waiting on. Jared select s.username username, e.event event, s.sid, e.p1text, e.p1, e.p2text, e.p2, e.wait_time, e.seconds_in_wait, e.state from v$session s, v$session_wait e where s.username is not null and s.sid = e.sid and s.username like upper('&uusername') -- skip sqlnet idle session messages and e.event not like '%message%client' order by s.username, upper(e.event); Janardhana Babu Donga <[EMAIL PROTECTED]> 05/09/2002 11:03 AM To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject:RE: DBA_EXTENTS problem Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. I would E-Mail after getting the response. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
Yes, I ran the query from the SYS schema and it returned the same number:4855, the same as count(*) from dba_extents. -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:14 PM To: Multiple recipients of list ORACLE-L or more accurately - select count(*) from sys.uet$; -Original Message- Sent: Thursday, May 09, 2002 2:03 PM To: '[EMAIL PROTECTED]' what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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: Seefelt, Beth 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
RE: DBA_EXTENTS problem
Jack, Output from show parameter optim: Optimizer_cache_optimal_size 102400 optimizer_features_enable 8.1.7 optimizer_index_caching 0 optimizer_index_cost_adj 100 optimizer_max_permutations8 optimizer_modechoose optimizer_percent_parallel0 Immediate response for select /*+RULE*/ count(1) from dba_extents Count(1) - 4855 select count(1) from dba_extents is still sitting there and has not yet responded. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:41 PM To: Multiple recipients of list ORACLE-L Babu, issue this in your sqlplus session: show parameter optim and then this: alter session set optimizer_mode=choose; and then this: select /*+ RULE */ count(1) from dba_extents; and then this: select count(1) from dba_extents; and let us know what happens. Rachel: we were running all_rows in our dwh and it was slowing down dd access. not sure why, choose and all_rows are kissing cousins. true, dd is supposed to be rule and with no stats. Have heard of cases where dbms_statistics analyzed the sys schema while doing a analyze_database, which is one reason I stopped using it. Might be the problem here? We will find out. hth, jack --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > I have recently changed the analyze script. Earliar > it was > dbms_utility.analyze_schema(...) statement, It is > now changed to > dbms_stats.gather_database_stats(); > > Is this a problem? > > The response time for select count(*) from > dba_extents is also 30 minutes. > It is not specific to any table. > Thanks, > -- Babu > > -Original Message- > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 11:36 AM > To: Multiple recipients of list ORACLE-L > > > > > that's interesting... since the data dictionary is > NOT analyzed, setting > optimizer_mode=choose would force the query against > dba_extents to RULE > which it > what it is supposed to be doing anyway, Hm. The > question now is, what is the > optimizer_mode set to when the problem happens? Did > any of the data > dictionary > tables get accidentally analyzed? And -- how many > extents are in use in the > database? Could it just be a symptom of missized > tables and indexes so that > the > number of extents is way high? > > Although Oracle is *supposed* to allow unlimited > extents, in practice > anything > higher than 4096 extents in an object (at least in > 8i) tends to slow things > down. > > > > > |+---> > || | > || | > || jack_silvey@y| > || ahoo.com | > || | > || 05/09/2002 | > || 02:18 PM | > || Please | > || respond to | > || ORACLE-L | > || | > |+---> > > >| > | > | > | To: [EMAIL PROTECTED] > | > | cc: (bcc: Rachel Carmichael) > | > | Subject: Re: DBA_EXTENTS problem > | > > >| > > > > > Babu, > > We had a similiar problem, and setting > optimizer_mode > = choose in our session solved it. Something to do > with optimizer and DD access. Give that a try. I had > the same problem with DBA_INDEXES and that fixed it. > > hth, > > Jack > > > --- Janardhana Babu Donga <[EMAIL PROTECTED]> > wrote: > > Dear List, > > > > When I query dba_extents , Iam getting response > > after half an hour to 40 > > minutes, but when I query any other dictionary > view > > it is spontaneous. > > Everything else is fine in the database and there > > are no problems, except > > the above problem. Iam not getting any clue how to > > fix this. Iam thinking of > > running catalog.sql and catproc.sql as a last > > resort. Iam not sure it would > > fix the problem. Please let me know if there is > any > > other way to fix this > > problem. > > > > Thanks, > > Babu > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Janardhana Babu Donga > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: > > (858) 538-5051 > > San Diego, Californi
RE: DBA_EXTENTS problem
It responded with 197 tables. Is it not the correct way to analyze? dbms_stats.gather_database_stats(); I have recently been using the above statement to analyze the database. Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 12:24 PM To: Multiple recipients of list ORACLE-L Is it possible that some of the system owned tables were accidently analyzed? You might try running: select table_name from dba_tables where owner='SYS' and last_analyzed is not null; -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: Miller, Jay 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
I thought using DBMS_STATS is much more efficient than the old method. I have recently read an article about it in Oracle magazine, Jan/Feb 2002 Page 32, then I changed the analyze script. I would appreciate if anyone sends me a script to delete the SYS/SYSTEM statistics. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:20 AM To: [EMAIL PROTECTED] I *think* there was a bug where it would also analyze SYS when you gathered database stats... why did you go from analyzing at the schema level to analyzing the entire database? You will need to delete the stats if they are there for any object owned by SYS. since I've never done this, I don't have a script to delete the stats... I know I've seen it posted to the list.. anyone have a copy? Rachel |+---> || | || | || jbdonga@ucdav| || is.edu | || | || 05/09/2002 | || 02:01 PM | || | |+---> >| || | To: [EMAIL PROTECTED] | | cc: Rachel Carmichael@Sony_Music | | Subject: RE: DBA_EXTENTS problem | >| I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+---> || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+---> >| || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | >| Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > When I query dba_extents , Iam getting response > after half an hour to 40 > minutes, but when I query any other dictionary view > it is spontaneous. > Everything else is fine in the database and there > are no problems, except > the above problem. Iam not getting any clue how to > fix this. Iam thinking of > running catalog.sql and catproc.sql as a last > resort. Iam not sure it would > fix the problem. Please let me know if there is any > other way to fix this > problem. > > Thanks, > Babu > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Janardhana Babu Donga > 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 t
RE: DBA_EXTENTS problem
It returned 4855. The response is immediate. Count(*) from dba_extents also returned 4855 but took nearly 30 minutes. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 12:14 PM To: Multiple recipients of list ORACLE-L what does select count(*) from uet$ return? -Original Message- Sent: Thursday, May 09, 2002 2:54 PM To: Multiple recipients of list ORACLE-L Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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: Seefelt, Beth 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 s
RE: DBA_EXTENTS problem
I used to run the following analyze every week: dbms_utility.analyze_schema(...) statement which included SYSTEM schema, Two weeks back it was changed to dbms_stats.gather_database_stats(); Is there anyway to de-analyze SYSTEM schema? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:04 AM To: 'Janardhana Babu Donga' Babu - Among the suggestions you have received, the one that seems to have the most merit is whether any of your system tables have been analyzed. Were I in your position, I would check that out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:51 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
I got the response from the count(*) query. It has returned 4855 and took nearly 30 minutes to respond. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 10:45 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
I have recently changed the analyze script. Earliar it was dbms_utility.analyze_schema(...) statement, It is now changed to dbms_stats.gather_database_stats(); Is this a problem? The response time for select count(*) from dba_extents is also 30 minutes. It is not specific to any table. Thanks, -- Babu -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 11:36 AM To: Multiple recipients of list ORACLE-L that's interesting... since the data dictionary is NOT analyzed, setting optimizer_mode=choose would force the query against dba_extents to RULE which it what it is supposed to be doing anyway, Hm. The question now is, what is the optimizer_mode set to when the problem happens? Did any of the data dictionary tables get accidentally analyzed? And -- how many extents are in use in the database? Could it just be a symptom of missized tables and indexes so that the number of extents is way high? Although Oracle is *supposed* to allow unlimited extents, in practice anything higher than 4096 extents in an object (at least in 8i) tends to slow things down. |+---> || | || | || jack_silvey@y| || ahoo.com | || | || 05/09/2002 | || 02:18 PM | || Please | || respond to | || ORACLE-L | || | |+---> >| || | To: [EMAIL PROTECTED] | | cc: (bcc: Rachel Carmichael) | | Subject: Re: DBA_EXTENTS problem | >| Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > When I query dba_extents , Iam getting response > after half an hour to 40 > minutes, but when I query any other dictionary view > it is spontaneous. > Everything else is fine in the database and there > are no problems, except > the above problem. Iam not getting any clue how to > fix this. Iam thinking of > running catalog.sql and catproc.sql as a last > resort. Iam not sure it would > fix the problem. Please let me know if there is any > other way to fix this > problem. > > Thanks, > Babu > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Janardhana Babu Donga > 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 Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for othe
RE: DBA_EXTENTS problem
Dennis, It is happenning for any table in the database. I have just created a new table: create table x1 (col1 number) tablespace data_ts, inserted one row and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. The response to any other dictionary view is spontaneous except this dba_extents, which is confusing me. I issued: select * from dba_objects where ..., select * from dba_segments where etc. They are all responding fine. There are not many users at the moment. System tablespace is 500M size and 300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0 (64bit) database. Any other ideas? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu - Wow, never had that one. Of course, I don't use that table much. How many rows are in this table? Mine has 12,937 rows, and is kinda slow to respond compared to the other system tables. Do you have a test database to compare with? Is it possible that your system tablespace has become badly fragmented? What is your system load, is it possibly a contention problem of some type. Why not run STATSPACK (or utlbstat) while you do this query and see what is being waited for? Why don't I shut up and send this? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 12:58 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: DENNIS 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
Jared, It is taking 30 to 40 minutes to respond. I just issued again and is sitting there. Iam sure it would respond after 30 minutes as happenned many times. I would E-Mail after getting the response. Thanks, -- Janardhana Babu -Original Message- Sent: Thursday, May 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Babu, What does this query return? select count(*) from dba_extents; Jared Janardhana Babu Donga <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 05/09/2002 10:58 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:DBA_EXTENTS problem Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
This is definitely not a fragmentation problem. I have just created a new table create table x1 (col1 number) tablespace data_ts; and run: select * from dba_extents where segment_name = 'X1'; It is still sitting there. Hope to get response after 30 minutes. Any other ideas?? Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:15 AM To: Multiple recipients of list ORACLE-L Sounds like a fragmentation problem. This will help you identify the segments with a large number of fragments. They are good candidates for reorganization. HTH, Beth select a.tablespace_name as tablespace, cast(a.segment_name as char(30)) as segment, a.partition_name as partition, count(1) as count, decode(b.extent_management,'LOCAL','Y','N') as LMT from dba_extents a, dba_tablespaces b where a.tablespace_name = b.tablespace_name having count(1) > 1 group by a.tablespace_name, a.segment_name, a.partition_name, decode(b.extent_management,'LOCAL','Y','N') order by count(1) desc -Original Message- Sent: Thursday, May 09, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: Seefelt, Beth 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
There are no LMTs in the database. Pl. let me know if you have any other ideas. Thanks, -- Babu -Original Message- Sent: Thursday, May 09, 2002 11:29 AM To: Multiple recipients of list ORACLE-L Babu, Do you have any locally managed tablespaces? I'm wondering if this could be caused by scanning for extents within LMTs. Regards, Mike Hately -Original Message- Sent: 09 May 2002 18:58 To: Multiple recipients of list ORACLE-L Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike 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: Janardhana Babu Donga 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: DBA_EXTENTS problem
The Optimizer_mode is already set to CHOOSE. Any other ideas? Thanks, --Babu -Original Message- Sent: Thursday, May 09, 2002 11:18 AM To: Multiple recipients of list ORACLE-L Babu, We had a similiar problem, and setting optimizer_mode = choose in our session solved it. Something to do with optimizer and DD access. Give that a try. I had the same problem with DBA_INDEXES and that fixed it. hth, Jack --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote: > Dear List, > > When I query dba_extents , Iam getting response > after half an hour to 40 > minutes, but when I query any other dictionary view > it is spontaneous. > Everything else is fine in the database and there > are no problems, except > the above problem. Iam not getting any clue how to > fix this. Iam thinking of > running catalog.sql and catproc.sql as a last > resort. Iam not sure it would > fix the problem. Please let me know if there is any > other way to fix this > problem. > > Thanks, > Babu > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Janardhana Babu Donga > 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 Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: Janardhana Babu Donga 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).
DBA_EXTENTS problem
Dear List, When I query dba_extents , Iam getting response after half an hour to 40 minutes, but when I query any other dictionary view it is spontaneous. Everything else is fine in the database and there are no problems, except the above problem. Iam not getting any clue how to fix this. Iam thinking of running catalog.sql and catproc.sql as a last resort. Iam not sure it would fix the problem. Please let me know if there is any other way to fix this problem. Thanks, Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu Donga 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: Brainbench Oracle certifications
OCP has $125 more value than Brainbench. -Original Message- Sent: Thursday, April 11, 2002 2:24 PM To: Multiple recipients of list ORACLE-L Dear gurus ! May i ask for your opinions regarding the value of the Brainbench Oracle 8i Administration certifications. I understand that it can not be compared t othe OCP, but is there any value at all for it ? Thanks a lot in advance. Andrey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin 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: Janardhana Babu Donga 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 find the view names where a particular table is used
Try querying dba_dependencies... select referenced_name,referenced_type from dba_dependencies where name = 'your view name'; -- Janardhana Babu -Original Message- Sent: Friday, March 15, 2002 2:34 PM To: Multiple recipients of list ORACLE-L Hi, Is there a way of finding out what views are using a given table? Shashank Sinha __ Phone: 262-317-9354 Cell: 262-424-1101 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinha, Shashank (MED, TCS) 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: Janardhana Babu Donga 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).