Solved

import oracle 8i fulldb data into oracle 9i

Posted on 2004-09-09
14
653 Views
Last Modified: 2008-03-17
hi,
i have the fulldb export file of oracle 8i.
i have another oracle server which is 9i
when i try to import the fulldb dmp(oracle8i) into oracle 9i using 9i client, it gives following errors

IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace

i open the dmp file but there is no where define "dictionary managed" in create tablespace command.

what should be the solutions for this.
i dont want to seperately creat all schema and then import using fromuser to touser....

pl.
Thanks
Prathesh(Prathesh@u-tosolutions.com)
0
Comment
Question by:pratheshshah
  • 3
  • 2
  • 2
  • +3
14 Comments
 
LVL 1

Expert Comment

by:peledc
Comment Utility
Hi,

In the new DB the SYSTEM tablespace was created as LOCALLY MANAGED. Therefor you will not be able to create any DICTIONARY Managed TS.

What I would do is create just the TABLESPACES in advance and then perform the full import.

Good luck.
0
 

Author Comment

by:pratheshshah
Comment Utility
hi,peledc
thanks fo replying
but my issue is that i have 12 different schema each one has 2 tablespaces, to create it manually, is very tidies.
is there a way that  change in exported file will allow import fulldb in oracle 9.2.0.1.0 from exported file.
and one more thing is oracle 9i is not allow to create dictionary managed tablespace?

thanks


0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
It is not only the problem with the tablespace definition. Also the directory structure in most cases is different.
The best approach is to precreate the nonsystem tablespaces and users.
Example:

CREATE TABLESPACE "TSDMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSDMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE TABLESPACE "TSIMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSIMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE TABLESPACE "TSTEMPORARY"
NOLOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSTEMPORARY00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT
100M   MAXSIZE 32768M
TEMPORARY   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;

CREATE USER LSMEDICAL  IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL  TEMPORARY TABLESPACE   TSTEMPORARY
QUOTA UNLIMITED ON TSDMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSIMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSTEMPORARY;
0
 
LVL 1

Expert Comment

by:peledc
Comment Utility
What you can try is to remove your new Oracle instance, which I understand is empty
and create a new instance. This time choose the custom option and make the SYSTEM tablespace Dictionary managed.

Other wise,
SELECT .... FROM DBA_TABLESPACE
all the required details for manually creating the new Tablespaces.

Good Luck
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Creating all the tablesapces are tedious, but this is something you have to LIVE with.

i.e., You have to do that for a succesfull IMPORT.

try to use a SCRIPT to automate this thing.  Peledc give you a nice direction.



Use this to generate all the tablespaces in script: just to save you from trouble,
configure OMF (oracle managed files in Oracle9i new instance) so that you don't need to specify file path when creating new tablespaces.
and assume the largest size of tablespace is 200M.


select 'create tablespace ' || tablespace_name|| 'size 200M ;' from dba_tablespaces where tablespace_name not in ('SYSTEM', 'TEMP', 'RBS');

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
If you run Import taking
show=yes
you will find there also thescripts for creating the tablespaces.
Modify it.
But blind craeting the tablespaces is not a good aproach.
Plan where to place the datafiles - otherwise you will have performance and placement problems.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
I agree with schwertner: create the tablespaces in the Oracle9 database manually, then run import with "ignore=Y".  This gives you the advantages of:
1. the data tablespaces can then be locally-managed (the default in Oracle9)
2. the data files can be in a different disk/directory than they were on the old database (this is almost always necessary when moving from one server to another anyway).
0
 
LVL 9

Expert Comment

by:konektor
Comment Utility
it's better not to do full import, but user import instead of it. u have to manually create all objects, which belonges to sys user in source database : create tablespaces, create users, roles ...
0
 

Author Comment

by:pratheshshah
Comment Utility
i did the same thing
created tablespace manuallt in oracle 9i
and then run import with ignore=y usign fulldb exported file of 8i but it give following error

IMP-00017: following statement failed with ORACLE error 1119:
 "CREATE TABLESPACE "RBS" DATAFILE  'C:\ORACLE\ORADATA\ORCL\RBS01.DBF' SIZE 5"
 "2428800       AUTOEXTEND ON NEXT 5242880  MAXSIZE 32767M DEFAULT  STORAGE(I"
 "NITIAL 524288 NEXT 524288 MINEXTENTS 8 MAXEXTENTS 4096 PCTINCREASE 50) ONLI"
 "NE PERMANENT "
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'C:\ORACLE\ORADATA\ORCL\RBS01.DBF'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file

in current oracle 9i doesnt have RBS tablespace created,
now tell me what should i do

pl.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 125 total points
Comment Utility
Oracle 9i has precreated Rollback Tablespace and you do not need to create your own rollback tablespaces.
Do not do FULL import. Import only non-system schemas.
If your import fails before new attempt recreate the schema:

DROP USER LS CASCADE;
CREATE USER LS IDENTIFIED BY ... DEFAULT TABLESPACE ....;
ALTER USER LS QUOTA UNLIMITED ON TABLESPACE  ...;
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
Comment Utility
Or, create an RBS tablespace manually, then run a full import, then drop the RBS tablespace that you don't need.  This assumes that have used the default "undo" tablespace functionality of Oracle9 instead of a "rollback" tablespace.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now