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. 1.3

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

From: <TCarlson_at_bunge.com>
Date: Mon, 18 Jun 2001 11:35:04 -0700
Message-ID: <F001.0032D900.20010618110610@fatcity.com>

What version of Oracle? What version of Windows?

Todd Carlson
Oracle 8i Certified DBA
Bunge North America

                                                                                       
                   
                                                                                       
                   
                       "Boivin,              To:     Multiple recipients of list 
ORACLE-L                 
                    Patrice J"               <ORACLE-L_at_fatcity.com>                    
                   
                    <BoivinP_at_mar.dfo-        cc:                                       
                   
                    mpo.gc.ca>               Subject:     RE: transfer of large 
datafile Oracle7.3.4.4    
                       Sent by:              databases to 8.1.7.    1.3                
                   
                    root_at_fatcity.com                                                   
                   
                                                                                       
                   
                                                                                       
                   
                       06/18/2001                                                      
                   
                    12:50 PM                                                           
                   
                       Please respond                                                  
                   
                    to ORACLE-L                                                        
                   
                                                                                       
                   
                                                                                       
                   




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: INET: TCarlson_at_bunge.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).
Received on Mon Jun 18 2001 - 13:35:04 CDT

Original text of this message

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