RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7. 1.3
More info on this, the error occurs during the export. The bug was fixed in 8.0.5, says Oracle Support, but because I am using an Oracle 7.3. export file we get the oversized file errors during the import, even if we are using the 8.1.7 import utility.. 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 18, 2001 2:51 PM To: Multiple recipients of list ORACLE-L Subject:RE: transfer of large datafile Oracle7.3.4.4 databases to 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] 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/xx/users01.dbf' SI ZE 1844674407360320 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
RE: transfer of large datafile Oracle7.3.4.4 databases to 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] 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/xx/users01.dbf' SI ZE 1844674407360320 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
RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7. 1.3
Question, What were the problems you ran into with this? I have a 4 gig datafile on NT 4.0 with Oracle 816 SE. I don't believe we're experiencing any problems. kev -Original Message- Patrice J Sent: Monday, June 18, 2001 1: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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] 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/xx/users01.dbf' SI ZE 1844674407360320 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
RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7. 1.3
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 [EMAIL PROTECTED] [EMAIL PROTECTED]cc: mpo.gc.ca Subject: RE: transfer of large datafile Oracle7.3.4.4 Sent by: databases to 8.1.7.1.3 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] 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/xx/users01.dbf' SI ZE 1844674407360320 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,
RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7. 1.3
Oracle 8.1.7. on NT, and NT 4. : ) 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 18, 2001 4:06 PM To: Multiple recipients of list ORACLE-L Subject:RE: transfer of large datafile Oracle7.3.4.4 databases to 8.1.7. 1.3 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 [EMAIL PROTECTED] [EMAIL PROTECTED]cc: mpo.gc.ca Subject: RE: transfer of large datafile Oracle7.3.4.4 Sent by: databases to 8.1.7. 1.3 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [SMTP:[EMAIL PROTECTED]] 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
