Oracle 9i importsfailing
Hi All,
I am using imp utility of Oracle 9i to import a dmp file. My import runs fine till it hits this big blob table and fails with below error
oracle error 603 encountered
ORACLE server session terminated oracle error 3114
ORA-03114: not connected to ORACLE
IMP-00000: Import terminated unsuccessfully
--------------------------
I have done following till now but still same error shows as above
1)Increased SYSTEM tablespace
2)SQL> conn / as sysdba
SQL> ALTER SYSTEM SET large_pool_size=50m SCOPE=SPFILE;
SQL> shutdown immediate
SQL> startup
SQL> show parameter large
NAME TYPE VALUE
-------------------- ----------- ---------
large_pool_size big integer 58720256
Restart export, e.g.:
exp system/manager@t920 full=y file=exp_full.dmp log=exp_full.log
Help will be really appreciated.
I am using imp utility of Oracle 9i to import a dmp file. My import runs fine till it hits this big blob table and fails with below error
oracle error 603 encountered
ORACLE server session terminated oracle error 3114
ORA-03114: not connected to ORACLE
IMP-00000: Import terminated unsuccessfully
--------------------------
I have done following till now but still same error shows as above
1)Increased SYSTEM tablespace
2)SQL> conn / as sysdba
SQL> ALTER SYSTEM SET large_pool_size=50m SCOPE=SPFILE;
SQL> shutdown immediate
SQL> startup
SQL> show parameter large
NAME TYPE VALUE
-------------------- ----------- ---------
large_pool_size big integer 58720256
Restart export, e.g.:
exp system/manager@t920 full=y file=exp_full.dmp log=exp_full.log
Help will be really appreciated.
Ravindran Gopinathan
Can you check the alert log and provide information from there? Entries created at the time yoe were tyring to import. May be there is not enough space or unable to extent segments. Alert log will have that information
ASKER
This is the eror that shows went import terminates inDOS prompt:
IMP-0058: ORACLE error 1034 encountered
ORA-01304: ORACLE not available
ORA-27101: shared memory realm does not exist
IMP-00005: all allowable log on attempts failed
IMP-00000: Import terminated unsuccessfully
AlertLog.txt
IMP-0058: ORACLE error 1034 encountered
ORA-01304: ORACLE not available
ORA-27101: shared memory realm does not exist
IMP-00005: all allowable log on attempts failed
IMP-00000: Import terminated unsuccessfully
AlertLog.txt
Look if the instance is running
% ps -ef | grep pmon
Check if the Listener has registered the the service
% lsnrctl services
All firewalls and antiviruses should be OFF.
% ps -ef | grep pmon
Check if the Listener has registered the the service
% lsnrctl services
All firewalls and antiviruses should be OFF.
would you please show the original metadata for TABLESPACE "IEP_LOB" from the original DB.
thanks
thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it-rex:,
How shall I upgrade my database to 9.2.0.3 from 9.2.0.1.0. Do I need to buy a CD which has version 9.2.0.3 or is it just a build update which I can freely update from Oracle site? Thanks
How shall I upgrade my database to 9.2.0.3 from 9.2.0.1.0. Do I need to buy a CD which has version 9.2.0.3 or is it just a build update which I can freely update from Oracle site? Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the import again without upgrading server, cause I thought I would try to go through some shorter route. I ran imports just for this big table that has blob data in it. Imp utility ran fine for 6 hrs then crashed with following error:
IMP-00058: Oracle error 1691 encountered
ORA-01691: unable to extend lobsegment PPP_MMMM_USER.SYS_LOB00000
partial import of table rolled back.
So I went on and ran the import again with following change:
Went on and created 4 more Datafiles under PPP_MMMM_LOB. Do you think after reaching 16G limit, the file will aautoextend to write in other data files?(Viz 1,2,3,4) so that about 30 Gs of data from that table can be imported? Thanks a bunch
IMP-00058: Oracle error 1691 encountered
ORA-01691: unable to extend lobsegment PPP_MMMM_USER.SYS_LOB00000
partial import of table rolled back.
So I went on and ran the import again with following change:
Went on and created 4 more Datafiles under PPP_MMMM_LOB. Do you think after reaching 16G limit, the file will aautoextend to write in other data files?(Viz 1,2,3,4) so that about 30 Gs of data from that table can be imported? Thanks a bunch
I have no idea u can try it
Mai I ask u why u stuck with 9I how about using 10g
ASKER
Do u think I wouldnot have faced all these problems if I was using 10G? Cause U can move a database from lower to higher version but not opposite? We have oracle in just test environments as we get oracle databases there from clients and convert to SQL databases for consolidation purposes.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still 9i
ASKER
Yup 9i for now, may be for next imports we will move to 10g.