Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7.

RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7.

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Tue, 19 Jun 2001 04:51:37 -0700
Message-ID: <F001.0032E6EE.20010619040528@fatcity.com>

Then I strongly encourage you NOT to use the EXTEND clause or the Storage Manager to extend the size of those big files.

Instead, if you have to extend them, create a second file.

: )

Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique 
Maritimes Region, DFO      | Région des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>

        -----Original Message-----
        From:   Cale, Rick T (Richard) [SMTP:RICHARD.T.CALE_at_saic.com]
        Sent:   Monday, June 18, 2001 5:55 PM
        To:     Multiple recipients of list ORACLE-L
        Subject:        RE: transfer of large datafile Oracle7.3.4.4
databases to 8.1.7.

        On NT 4.0, Oracle 8.0.5 2gig filesize is not a limitation of either NTFS or

        Oracle. I have several datafiles in
        the 3-5gig size.

        Rick

        -----Original Message-----
        Sent: Monday, June 18, 2001 4:31 PM
        To: Multiple recipients of list ORACLE-L
        8.1.7.


        Just make sure , when you do this, that your max_datafile parameter
on the
        Database is set high enough.

        -----Original Message-----
        Sent: Monday, June 18, 2001 12:51 PM
        To: Multiple recipients of list ORACLE-L
        8.1.7. 1.3


        FYI,

        Oracle Support confirmed that I hit a 2G file size limit for Oracle
        databases on NT.  This "probably" led to data dictionary corruption.

        I don't know if this is an NTFS limitation or Oracle on NT problem,
but at
        this point I don't care, I can fix this by creating multiple smaller
        datafiles per tablespace.

        Regards,
        Patrice Boivin
        Systems Analyst (Oracle Certified DBA)

        Systems Admin & Operations | Admin. et Exploit. des systèmes
        Technology Services        | Services technologiques
        Informatics Branch         | Direction de l'informatique 
        Maritimes Region, DFO      | Région des Maritimes, MPO

        E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>



                -----Original Message-----
                From:   Boivin, Patrice J [SMTP:BoivinP_at_mar.dfo-mpo.gc.ca]
                Sent:   Monday, June 18, 2001 1:01 PM
                To:     Multiple recipients of list ORACLE-L
                Subject:        transfer of large datafile Oracle7.3.4.4
databases
        to 8.1.7.1.3

                Has anyone successfully transferred large datafile Oracle
databases
        from
                Oracle7.3. to 8.1.7?  By large datafile database I mean a
database
        that has
                files over 2G in size.  This may not apply to all, it may
apply only
        to
                those who extended the files beyond 2G.  Just curious, since
many of
        you
                appear to have made thet move from Oracle 7.3.4. to 8.1.6.
or
        8.1.7..

                Here we did a full export of the db (Tru64 UNIX), ftp'ed it
to
        another
                server (NT 4), then ran the 8.1.7 import to re-create the
users and
        other
                global information.  I aborted the import when the import
started to
        create
                tables.  Then I deleted user accounts I didn't need on the
        development
                database, and did a user import for the schemas that I
needed.
        Using SQL I
                then re-created all the public synonyms, since the import
utility
        did not
                re-create those.  

                However the Change Manager tells me that the SYSTEM
tablespace
        doesn't exist
                in the new database.  Meanwhile the new database is open,
and we can
        query
                from it.  All the accounts appear to be accessible.  Some
objects

(packages,
procedures, views) are invalid, but not many. The developers are now going through twelve packages and one procedure that did not compile successfully, probably due to tightening of the code standards. Anyway when I run the import utility in show=y mode, I see in the import SQL code something that I saw last year: create tablespace statements with datafile sizes that are 1.7 billion Gigabytes. <grin> We don't have enough disk to hold that much data, and besides I don't think that NT can support files that size. I know that UNIX can't. e.g. "CREATE TABLESPACE "USERS" DATAFILE '/oracle2/oradata/xxxxxx/users01.dbf' SI" "ZE 18446744073608888320 DEFAULT STORAGE (INITIAL 40960 NEXT 40960 MIN" "EXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0) ONLINE PERMANENT" Last year Oracle Support told me to pre-create the tablespaces, do the full import, and ignore the error reports during the import. They said that because the tablespaces do exist, import will produce an error but it will move on and do its thing. Given that I am creating new databases and we wish to migrate our major production databases, I would much prefer it if there were no errors anywhere. Another issue with this bug is that when the import utility goes berserk, it also imports SYS objects during full imports. Maybe that wasn't a big problem when the data dictionary was of the same version and we imported a full database into an empty one, but in this case the Oracle version is different. What a mess this could become. Change Manager does report differences between SYS objects in the older Oracle 7.3. database and the new 8.1.7 database, but I haven't gone through them all one by one to compare the columns, etc.. Neither have I gone through the list of data dictionary views to ensure that those that are different from Oracle7 DO show up as different in Change
Manager.
                We did find some Designer structures in the new database's
SYS
        schema
                however.  This tells me that import may have tried to
overwrite
        other SYS
                schema structures (? Not sure).

                I wonder if the error is caused by the Oracle7.3. export
utility, by
        the
                rdbms engine on that old version, or if it is still a bug in
8.1.7..
                I logged a TAR with Oracle, but haven't heard back from them
yet.
        They
                asked me to do a database-to-database comparison in Change
Manager,
        instead
                of doing a database-to-baseline or baseline-to-baseline
comparison

(which I
have done, both report "missing" objects and tablespaces). We are considering what our options are at this point. Pre-creating all the objects and then importing user by user doesn't sound good to me. Likewise with the migrate utility, if the problem is with the rdbms engine, it won't work either. I could do a full import in rows=n mode I suppose, to see what would happen then. The error appears to be in the import code, however. Oracle no longer fixes bugs in Oracle 7.3.4., they will not fix this problem in the older version. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: boivinp_at_mar.dfo-mpo.gc.ca
<mailto:boivinp_at_mar.dfo-mpo.gc.ca>
                -- 
                Please see the official ORACLE-L FAQ: http://www.orafaq.com
                -- 
                Author: Boivin, Patrice J
                  INET: BoivinP_at_mar.dfo-mpo.gc.ca

                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: ListGuru_at_fatcity.com (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: Boivin, Patrice J
          INET: BoivinP_at_mar.dfo-mpo.gc.ca

        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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
        the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: kgel_at_ppoone.com 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: ListGuru_at_fatcity.com (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: Cale, Rick T (Richard) INET: RICHARD.T.CALE_at_saic.com 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: ListGuru_at_fatcity.com (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: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

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: ListGuru_at_fatcity.com (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).
Received on Tue Jun 19 2001 - 06:51:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US