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
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
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 andgets
to theRMAN-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
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 andgets
to theRMAN-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 database;
117
118 restore controlfile to '/u02/vssppln/restored_cf.ctl';
119
120 mount database;
121
122 switch datafile all;
123
124 release channel disk_channel1;
125 }
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 andgets
to theRMAN-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 database;
117
118 restore controlfile to '/u02/vssppln/restored_cf.ctl';
119
120 mount database;
121
122 switch datafile all;
123
124 release channel disk_channel1;
125 }
126
127
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=schema 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. --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
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 Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
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 from this mailing list, send an E-Mail message
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 ......
Thanks for the caution. Does any one know if I export with owner=schema 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. --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, 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
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
-
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: 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).
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: 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).
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 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
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: 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: 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: Partition and primary key
Title: Partition and primary key You need tohave 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: 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: 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@connect string for catalog DB.
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.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: 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
usethe index - but it always does a FTS. Why? (I've tried it with
and withoutthe 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: 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@Connect String
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, 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).
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@Connect String
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).
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 message BODY, include a line containing: UNSUB ORACLE-L
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).
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: 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).
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: 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).
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
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
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
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 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 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
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 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
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
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 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 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
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
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, 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
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') and in the message BODY, include a line containing: UNSUB ORACLE-L
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
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
--
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
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 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 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
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 |
|| |
|+---
|
||
| 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
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).
