Why there is no controlfile in Informix like Oracle ?

Dear Fellow Informixers, Wondering why there is no concept of controlfile in Informix like Oracle does. How are various system details kept track of ? Simply in sysmaster tables ? Prashant

PK wrote: > Dear Fellow Informixers, > > Wondering why there is no concept of controlfile in Informix like > Oracle does. > > How are various system details kept track of ? Simply in sysmaster > tables ? Assuming that none of us know what's in the Oracle control file (a fairly safe assumption) can you please describe what you expect to find in it?

"Andrew Hamm" <ahamm@mail.com> wrote in message news:2p5f7jFgcicsU1@uni-berlin.de... > PK wrote: > > Dear Fellow Informixers, > > > > Wondering why there is no concept of controlfile in Informix like > > Oracle does. > > > > How are various system details kept track of ? Simply in sysmaster > > tables ? > > Assuming that none of us know what's in the Oracle control file (a fairly > safe assumption) can you please describe what you expect to find in it? Don't tar us all with the brush of your own ignorance, Andrew :-) Oracle control files contain fundamental stuff like names and locations of tablespace disks, online redo log files and checkpoint information. Because the file is human-readable it is quite easy to make duplicates of Oracle databases simply by copying tablespace files (or raw devices(*)) then altering the control file to reflect a new database name and these new locations. The answer to the question is that Informix does has an ONCONFIG file that points to the initial location of the root dbspace (tablespace in Oracle-ese), but that all subsequent locations, and other things you would find in an Oracle control file, are indeed held in reserved pages and system tables. Which is better? I find some advatages in the Oracle method - multiple control files can be made and they can be physically isolated; it is key to the re-directed restore functionality whose absence until 9.40 has been a big negative for Informix IMNSHO. There's no logical argument against it. But in an abstract way I just believe that this info somehow "belongs" within the database itself ... (*) We are setting up two new Sun StoreEdge arrays for a new Informix installation. These devices come pre-configured as RAID-5. Obviously we love and fear Art so much that we wouldn't dare not change it! However, reading through the performance guide there is a section specifically dealing with raw devices, in which it states that raw vs file system is now a non-issue and that large and increasing numbers of data centres are now just using file system "for very good reason". It goes on to say that the Sun UFS type has been optimised in recent years and more-or-less implies that you shouldn't use raw. Strangely, the text doesn't mention any particular DBMS but the headline does mention Oracle. Any lessons for Informix from this ....

Neil Truby wrote: > > Don't tar us all with the brush of your own ignorance, Andrew :-) I proudly wear my ignorance of Oracle, printed on the back of a T-shirt.

Andrew Hamm wrote: > Neil Truby wrote: > >>Don't tar us all with the brush of your own ignorance, Andrew :-) > > > I proudly wear my ignorance of Oracle, printed on the back of a T-shirt. > > Vice Versa.

"Mark Townsend" <markbtownsend@comcast.net> wrote in message news:412DAD0D.6010308@comcast.net... > Andrew Hamm wrote: > > Neil Truby wrote: > > > >>Don't tar us all with the brush of your own ignorance, Andrew :-) > > > > > > I proudly wear my ignorance of Oracle, printed on the back of a T-shirt. > > > > > Vice Versa. You proudly wear your ignorance of T-shirts, printed on the back of an oracle?

You are pretty much right. Most of the Information is kept in sysmaster. The backup information is kept in sysutils. i.e Onbar backup utility similar to RMAN. Also the backup info found in sysutils is in a flat file found in $INFORMIXDIR/etc/ixbar.<servernum>. Contains the logical log info similar to the archive logs and dbspaces&chunk info similar to the Tablespace & datafiles in Oracle. The above is a high level general view. Once into detail things work differently for the DB's though. Whats the reason for the question ? Traveller2003

Well, I don't know everything about the controlfiles but I'll add what I know, and perhaps others can tell me if I'm full of s**t or have other appropriate comments. Just about all of the system details are kept in various Informix system databases: sysmaster, sysutils, syscdr, catalog tables. If using onbar for backup/restore, external information is kept in the $INFORMIXDIR/etc/ixbar*, $INFORMIXDIR/etc/oncfg* and $INFORMIXDIR/etc/$ONCONFIG files. If using ontape for backup/restore the external information is kept in the tape header and $INFORMIXDIR/etc/$ONCONFIG file. When restoring an Informix instance, this external information is used just long enough to get the instance up to the point where the system information in the instance can take precident. Each approach has their advantages/disadvantages, but I personally like the Informix approach; It's much simpler. I've seen Oracle restores fail because the control file were not backed-up, or some ill-advised Oracle DBA didn't backup their control files using RMAN, etc. Hope this information helps. Brice Avila Minneapolis, Minnesota pk26au@yahoo.com (PK) wrote in message news:<667bf0c6.0408252226.914e94b@posting.google.com>... > Dear Fellow Informixers, > > Wondering why there is no concept of controlfile in Informix like Oracle does. > > How are various system details kept track of ? Simply in sysmaster tables ? > > > > Prashant

On Thu, 26 Aug 2004 06:05:28 -0400, Captain Pedantic wrote: > "Mark Townsend" <markbtownsend@comcast.net> wrote in message > news:412DAD0D.6010308@comcast.net... >> Andrew Hamm wrote: >> > Neil Truby wrote: >> > >> >>Don't tar us all with the brush of your own ignorance, Andrew :-) >> > >> > >> > I proudly wear my ignorance of Oracle, printed on the back of a T-shirt. >> > >> > >> Vice Versa. > > You proudly wear your ignorance of T-shirts, printed on the back of an > oracle? OH, and I thought ... nevermind - it will take me several hours to decode the logic of what I thought. I won't burden the rest of you. Art S. Kagel

There's in interesting ongoing disscussion going on in comp.databases.oracle.server about the role of controlfiles. "PK" <pk26au@yahoo.com> wrote in message news:667bf0c6.0408252226.914e94b@posting.google.com... > Dear Fellow Informixers, > > Wondering why there is no concept of controlfile in Informix like Oracle does. > > How are various system details kept track of ? Simply in sysmaster tables ? > > > > Prashant

Neil Truby wrote: [ stuff snipped & continue to answer the OT part ] > > (*) We are setting up two new Sun StoreEdge arrays for a new Informix > installation. These devices come pre-configured as RAID-5. Obviously we > love and fear Art so much that we wouldn't dare not change it! However, > reading through the performance guide there is a section specifically > dealing with raw devices, in which it states that raw vs file system is now > a non-issue and that large and increasing numbers of data centres are now > just using file system "for very good reason". It goes on to say that the > Sun UFS type has been optimised in recent years and more-or-less implies > that you shouldn't use raw. Strangely, the text doesn't mention any > particular DBMS but the headline does mention Oracle. > > Any lessons for Informix from this .... The lesson of the day is: Many of those poor souls, who believe in colorful marketing hype brochures and think A) RAID-5 on any type of SAN is fast enuff for database stuff B) speed of raw !>> speed of anything which has to go thru the operatings buffer cache end up buying Veritas Direct-I/O. This thingy was made for Oracle. Go and test! bonnie++ will be your best friend. And to give you some hint where you should end up when you need I/O performance, you should show your bonnie++ benchies and ask your vendor: Why can I not have readspeed of 72000 2 KB pages / second writespeed of 61000 2 KB pages / second writespeed of 56000 8 KB pages (chunk writes) per second? It is not necessary to pretend to be happy with less thruput. It won't be necessary to ask the more hurting, painful questions like: Why do I get only 1.5 TB usable disk on you RAID 5 for 100 K Euro, when I can get 2 TB for 20 K Euro and the performance I need on a RAID 1+0 when I buy elsewhere .. www.storagesearch.com has it all and more. dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Also sprach pk26au@yahoo.com (PK) : :Dear Fellow Informixers, : :Wondering why there is no concept of controlfile in Informix like Oracle does. : :How are various system details kept track of ? Simply in sysmaster tables ? In most cases, the sysmaster tables are not real tables at all, but rather pseudo-tables that provide an sql interface into internal data structures maintained in shared memory. There are some "real" tables in sysmaster, related to utilities such as onbar. Oracle has a similar concept to these pseudo tables in the V$ tables. The REAL analog in informix to oracle's control files are the RESERVED pages found at the very beginning of the first chunk of the root dbspace. Originally 12 in number, they became a bit more dynamic in the newer versions of the server (eliminating some of the limitations present in those earlier version of the server, e.g. maximum number of chunks). Along the same lines of oracle's multiple copies of the control file, informix maintains multiple copies (2, actually) of the critical pages. For example, there are 2 copies of the page that contains info on all the dbspaces that exist in your server instance, another 2 for logical log info, etc. One pair of these pages contains info used for fast recovery (automatic recovery at server startup, just in case the server was not shut down cleanly) including such items as the time of the last checkpoint, the location in the logical log of the last checkpoint record, etc. Dave

Neil Truby wrote: > "Andrew Hamm" <ahamm@mail.com> wrote in message > news:2p5f7jFgcicsU1@uni-berlin.de... > >>PK wrote: >> >>>Dear Fellow Informixers, >>> >>>Wondering why there is no concept of controlfile in Informix like >>>Oracle does. >>> >>>How are various system details kept track of ? Simply in sysmaster >>>tables ? >> >>Assuming that none of us know what's in the Oracle control file (a fairly >>safe assumption) can you please describe what you expect to find in it? > > > Don't tar us all with the brush of your own ignorance, Andrew :-) > > Oracle control files contain fundamental stuff like names and locations of > tablespace disks, online redo log files and checkpoint information. Because > the file is human-readable it is quite easy to make duplicates of Oracle > databases simply by copying tablespace files (or raw devices(*)) then > altering the control file to reflect a new database name and these new > locations. The day you "alter" a control file I want to be there to watch. ;-) Other than that I agree with what you've said. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

Brice Avila wrote: > Well, I don't know everything about the controlfiles but I'll add what > I know, and perhaps others can tell me if I'm full of s**t or have > other appropriate comments. > > Just about all of the system details are kept in various Informix > system databases: sysmaster, sysutils, syscdr, catalog tables. If > using onbar for backup/restore, external information is kept in the > $INFORMIXDIR/etc/ixbar*, $INFORMIXDIR/etc/oncfg* and > $INFORMIXDIR/etc/$ONCONFIG files. If using ontape for backup/restore > the external information is kept in the tape header and > $INFORMIXDIR/etc/$ONCONFIG file. When restoring an Informix instance, > this external information is used just long enough to get the instance > up to the point where the system information in the instance can take > precident. > > Each approach has their advantages/disadvantages, but I personally > like the Informix approach; It's much simpler. I've seen Oracle > restores fail because the control file were not backed-up, or some > ill-advised Oracle DBA didn't backup their control files using RMAN, > etc. > > Hope this information helps. > > Brice Avila > Minneapolis, Minnesota > > pk26au@yahoo.com (PK) wrote in message news:<667bf0c6.0408252226.914e94b@posting.google.com>... > >>Dear Fellow Informixers, >> >>Wondering why there is no concept of controlfile in Informix like Oracle does. >> >>How are various system details kept track of ? Simply in sysmaster tables ? >> >> >> >>Prashant Failure to understand one's environment and back up required resources is the sign of a moron at the wheel. That is not a reflection on the product. More importantly ... anyone with even mediocre skills knows how to dynamically regenerate the control files. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

"Daniel Morgan" <damorgan@x.washington.edu> wrote in message news:1093580806.482596@yasure... > > Failure to understand one's environment and back up required resources > is the sign of a moron at the wheel. That is not a reflection on the > product. More importantly ... anyone with even mediocre skills knows how > to dynamically regenerate the control files. > > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu > (replace 'x' with 'u' to respond) > Daniel, Fundamental rule of encapsulation --- Everything that's needed for an object is to be self-contained within that object. Fundamental rule for backups --- Everything that's needed to restore the system is to be self-contained within the backup. Fundamental rule of operations --- If it can go wrong - it will. Fundamental rule of DBAs --- Most DBA's are not internalists. Fundamental rule of most businessess... The DBA is expendable. Why not hire an entry clerk to monitor the system. ;-) M.P.

Richard Kofler wrote: > Neil Truby wrote: > > [ stuff snipped & continue to answer the OT part ] > >>(*) We are setting up two new Sun StoreEdge arrays for a new Informix >>installation. These devices come pre-configured as RAID-5. Obviously we >>love and fear Art so much that we wouldn't dare not change it! However, >>reading through the performance guide there is a section specifically >>dealing with raw devices, in which it states that raw vs file system is now >>a non-issue and that large and increasing numbers of data centres are now >>just using file system "for very good reason". It goes on to say that the >>Sun UFS type has been optimised in recent years and more-or-less implies >>that you shouldn't use raw. Strangely, the text doesn't mention any >>particular DBMS but the headline does mention Oracle. >> >>Any lessons for Informix from this .... We just recently set up a large and very busy OLTP database server running Informix 9.21.UC3X6 on Sun platform, chunks on top of a pair of StorEdge 6120 disk arrays. The Sun guys tried to convince us that the UFS file system has been enhanced so much for Solaris 9 that running raw disks is totally unnecessary, provided that the file system has the right parameters set (direct i/o etc). Well, after extensive testing we still couldn't get the results we were expecting. So we decided to skip the file system layer altogether, and just use raw volumes created with Veritas VxVM. The immediate results were about 5x speed improvement on that particular type of use. I mean the system was really crawling under high load before this change. The 6120 arrays were configured to have RAID levels 0 and 1 internally, and the pair are mirrors of each other. So that makes 4-way mirroring. > The lesson of the day is: > Many of those poor souls, who believe in colorful > marketing hype brochures and think > A) RAID-5 on any type of SAN is fast enuff for database stuff > B) speed of raw !>> speed of anything which has to go thru > the operatings buffer cache > end up buying Veritas Direct-I/O. This thingy was made for > Oracle. Actually we have very good results in using the Veritas VxFS Quick I/O feature with Informix as well. Performance is comparable to raw device speed, with all the conveniences of running on top of a file system. -- Toni