Solved

oracle migration using export/import

Posted on 2009-05-04
57
1,819 Views
Last Modified: 2012-05-06
I was doing export/import --- from 816 to 10202

i took the dump of the 816 database:

i installed the 10202 database.

  1.. As a DBA user, perform a full export from the source database, for example:

> exp system/manager FULL=y FILE=myfullexp.dmp

   3. Move the dump file to the target database server by scp
   4. Create a database on the target server. -- create database [database name]

i have also run 6.  Run catalog and catproc
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

   5. Before importing the dump file, you must first create your tablespaces -- i did not create tablespace because the filesystem is same.
   6. As a DBA user, perform a full import with the IGNORE parameter enabled:

> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp

Using IGNORE=y instructs Oracle to ignore any creation errors during the import and permit the import to complete.
 
-- i got so many errors -- am i wrong at any step ?



0
Comment
Question by:ramavenkatesa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 32
  • 22
  • 2
  • +1
57 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24297992
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24298026
Post the errors?
0
 

Author Comment

by:ramavenkatesa
ID: 24298040
errors file attacghed
errors.txt
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 48

Expert Comment

by:schwertner
ID: 24298050
When you do FULL import Oracle will reject to change the SYS and other system shemas. This is the main cause of thet errors. You can ignore them and begin to check the application.

But often there are also errors linked with thenonsystem users and these errors are really important
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24298073
>>ORA-01654: unable to extend index SYS.I_ACCESS1 by 13 in tablespace SYSTEM

Looks like you created too smal of a SYSTEM tablespace. You need to re-create your database, or add another datafile to SYSTEM, or enable auto-extend.
0
 

Author Comment

by:ramavenkatesa
ID: 24298082
. importing SYSTEM's objects into SYSTEM
. importing VECTRDBA's objects into VECTRDBA
 "ALTER SESSION SET CURRENT_SCHEMA= "VECTRDBA""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully

-- whole import was less than 10 minutes..

0
 

Author Comment

by:ramavenkatesa
ID: 24298089
i did not create any tablespace -- shall i create TS?
0
 

Author Comment

by:ramavenkatesa
ID: 24298124
which all TS shd i create?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24298130
Before retrying the export, you need to fix errors related to your SYSTEM tablespace.

Try:

alter database datafile '/oracle/oradata/ORA1/system01.dbf' resize 1g;
0
 

Author Comment

by:ramavenkatesa
ID: 24298825
i re-ran the import -- now , i am having the problem in RBS.

in the 816 source db -- i have RBS

in the 10202 target db -- i have undo TS

i am unable to import the tables.
0
 

Author Comment

by:ramavenkatesa
ID: 24298834
new errors are attached
errors.txt
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24299278
Have you created an undo tablespace and enabled automatic undo?
0
 

Author Comment

by:ramavenkatesa
ID: 24299282
yes
0
 

Author Comment

by:ramavenkatesa
ID: 24299291
select TABLESPACE_NAME from dba_rollback_segs ;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01
UNDOTBS_01



----------------

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TOOLS
RBS
TEMP
USERS
UNDOTBS_01
TEMP1




0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24299330
show parameter undo_management
0
 

Author Comment

by:ramavenkatesa
ID: 24299338
show parameter undo_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
SQL>
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24299353
Actually show all undo parameters

show parameter undo
0
 

Author Comment

by:ramavenkatesa
ID: 24299361
show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string
SQL>
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 300 total points
ID: 24299362
Ok, your undo mgmt is in wrong mode, set it to auto

alter system set undo_management=auto scope=spfile;
 
alter system set undo_tablespace=UNDOTBS_01 scope=spfile;
 
shutdown and restart database

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24299370
Make sure you have an spfile. If not, you have to do init.ora
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24299398
After all that, you can drop your RBS tablespace, there is no need for it anymore with automatic UNDO. You cannot drop the system rollback segment, it is required.

Welcome to 10g and no more RBS management. :)
0
 

Author Comment

by:ramavenkatesa
ID: 24303379
i started the database with -- startup upgrade

when  i am trying to execute the command imp sample/sample  FULL=y IGNORE=y FILE=/bu1/sample_1_050409.dmp

i got this ....
IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername:

i created one user -- called hello

i tried to grant sysdba priv to hello -- it is asking me to create passwd file. can u please help me how to create using orapwd utlity?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24303555
There is no need to use STARTUP UPGRADE in this case, you did not upgrade an existing database, you created a new one!

Shutdown and restart in normal mode.
0
 

Author Comment

by:ramavenkatesa
ID: 24303593
startup
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  2030104 bytes
Variable Size             509478376 bytes
Database Buffers           81920000 bytes
Redo Buffers                2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced


---------------

Successful open of redo thread 1
Tue May  5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May  5 11:47:46 2009
SMON: enabling cache recovery
Tue May  5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectru/udump/vectru_ora_6758.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May  5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
0
 

Author Comment

by:ramavenkatesa
ID: 24303594
startup
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  2030104 bytes
Variable Size             509478376 bytes
Database Buffers           81920000 bytes
Redo Buffers                2162688 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced


---------------

Successful open of redo thread 1
Tue May  5 11:47:46 2009
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May  5 11:47:46 2009
SMON: enabling cache recovery
Tue May  5 11:47:46 2009
Errors in file /BA/opt/oracle/admin/vectru/udump/vectru_ora_6758.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Tue May  5 11:47:46 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 6758
ORA-1092 signalled during: ALTER DATABASE OPEN...
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24303642
Can you explain what you did between the steps where we were discussing your UNDO configuration, and your latest problem? Obviously you did something?

Did you actually start an upgrade? It appears you did but did not finish it. If so, RESTART IN UPGRADE mode and finish running the upgrade scripts (catupgrd.sql) before continuing.
0
 

Author Comment

by:ramavenkatesa
ID: 24303775
initially, i tried to import --- i was getting errors.
after changing the parameters in the init file , i tried to startup -- i could not. i started with upgrade  option.
then i tried to import again,. then i am IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE modeUsername: -- other than this i did nothing ..
0
 

Author Comment

by:ramavenkatesa
ID: 24303791
previously, i could not complete the import properly - -- i mean , import was not completed.
0
 

Author Comment

by:ramavenkatesa
ID: 24304216
shall i delete the oracle software -- and do all over again ?
0
 

Author Comment

by:ramavenkatesa
ID: 24304487
when i ran catupgrd.sql -- i got this

No errors.
SELECT version_script AS file_name FROM DUAL
       *
ERROR at line 1:
ORA-20000: Upgrade not supported from version
ORA-06512: at "SYS.VERSION_SCRIPT", line 52


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
0
 

Author Comment

by:ramavenkatesa
ID: 24304516
can i use -- export dump in 816 and use the import in 10202 or 10204 ?
0
 

Author Comment

by:ramavenkatesa
ID: 24304570
i am using this script to give me the dump.
backup-db.txt
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24305152
I am curious of 2 things:

1) What binary version is installed? Did you install a patch?

2) startup nomount, issue: select * from v$version
0
 

Author Comment

by:ramavenkatesa
ID: 24305206
i installed 10202 .

startup nomount
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  2030104 bytes
Variable Size             509478376 bytes
Database Buffers           81920000 bytes
Redo Buffers                2162688 bytes
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

0
 

Author Comment

by:ramavenkatesa
ID: 24305214
i did not install any patch
0
 

Author Comment

by:ramavenkatesa
ID: 24305319

i created orapwd file and granted sysdba privilge to that user -- when i am executing
 imp raja/raja FULL=y IGNORE=y FILE=/bu1/sample_1_050409.dmp  --> i am getting
IMP-00058: ORACLE error 39710 encountered
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24305395
You should not have used startup upgrade, and I am unsure what else happened, so I recommend you drop the database and create a brand new one with DBCA, as it will take no longer than trying to fix this by running catalog.sql, etc.

During DBCA
1) Make sure automatic UNDO is enabled, per directions above (DBCA should let you select that option).
2) Make sure to create a large enough SYSTEM tablespace this time

After DBCA

I recommend only importing the custom schemas, not a full import.
0
 

Author Comment

by:ramavenkatesa
ID: 24305514
i can not use DBCA -- is this fine:
create database vectru
logfile GROUP 1 '/u01/oradata/vectru/redo01.log'  SIZE 20M,
  GROUP 2 '/u02/oradata/vectru/redo02.log'  SIZE 20M,
  GROUP 3 '/u03/oradata/vectru/redo03.log'  SIZE 20M
character set US7ASCII
national character set utf8
datafile '/u01/oradata/vectru/system01.dbf', size 500M  extent management local
sysaux datafile '/u01/oradata/vectru/sysaux.dbf' size 500M 
undo tablespace undotbs1 datafile '/u04/oradata/vectru/undotbs1.dbf' size 800M
default temporary tablespace temp tempfile 'u03/oradata/vectru/temp01.dbf' size 1000M;
 

Open in new window

0
 

Author Comment

by:ramavenkatesa
ID: 24305729

CREATE DATABASE vectru
DATAFILE 
  '/u01/oradata/vectru/system_01.dbf' size 500M
SYSAUX DATAFILE
  '/u01/oradata/vectru/sysaux_01.dbf' size 500M
UNDO TABLESPACE vectru_undo DATAFILE
  '/u01/oradata/undo_01.dbf' size 500M
LOGFILE
  '/u02/oradata/vectru/redo_01.dbf' size 20M,
  '/u02/oradata/vectru/redo_02.dbf' size 20M,
  '/u03/oradata/vectru/redo_03.dbf' size 20M
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "UTF8"
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 
  '/u04/oradata/vectru/temp_01.dbf' SIZE 500M
NOARCHIVELOG
MAXDATAFILES 1000
MAXLOGFILES 10;

Open in new window

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24305863
Yes, that looks good, unless this will be a production DB, then you should use log mirroring by adding a 2nd member to each logfile group.
0
 

Author Comment

by:ramavenkatesa
ID: 24306681

CREATE DATABASE vectru
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
 -- i am getting this error
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24307291
You need to check the trace files / alert log to determine what is going on.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24307492
Most likely you did not clean up the environment. Make sure there are no files left over from prior. Make sure no shared memory from unclean instance exit

(use ipcs -m to list, and ipcm to remove the orphaned segments if any)

Again, check the alert / trace
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 24308157
This is tooooooooooo much!
Talks, ideas, details ....
Bad practice!

Your mai error is that you try to mograte from 8i to 10g using FULL Export/Import.
You have to use schemas (schemata) level Export/Import.

I mean your application schemas, nothing else.
8i and 10g are totally different architectutes.
Trying to load FULL 8i Db you also import obsolete structures, etc.
This should be avoided.
0
 

Author Comment

by:ramavenkatesa
ID: 24308337
You have to use schemas (schemata) level Export/Import. -- can u please give me the command for this one?

Trying to load FULL 8i Db you also import obsolete structures, etc. --  can u please give me an example. i did not get u? -- the data will be the same right?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24308354
Agreed.

And also I think it is a good idea to post your steps here if you are doing things in between posts, because I cannot track how you went from having undo tablespace issues to having a database in upgrade mode that will not open.

It is good that this is development, and you are learning, because you should not take this approach in production environments (try, get an ORA error, ask a question...) you really need to follow the upgrade documents and/or a specific set of steps that you create. I prefer tried and true scripts of the whole process.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24308388
schema level is per user. You _can_ take a full export with system, but don't use full import.

Use fromuser/touser on the import step to selectively import the schemas.

fromuser=(user1,user2,user3) touser=(user1,user2,user3)

Make sure they are in the same order.
0
 

Author Comment

by:ramavenkatesa
ID: 24308441
what is did was --

installed the 10202 bianries
startup nomount
created a database  -- i created the undo tablespace
created non-system tablespaces
created users

ftp'ed the dump -- and imported (all tables are imported)

now, both the undo & RBs are there

show parameter gives me -- show parameter undo ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO_1


select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO_1                         ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TOOLS                          ONLINE
RBS                            ONLINE
-- now both undo_1 & RBS are online -- shd i drop the RBS?  
it gave me one warning in 1 table -- ROWID, other than that everything is ok.

please correct me if i am wrong anywhere?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24308490
>>- now both undo_1 & RBS are online -- shd i drop the RBS?  

That is due to full import. So yes, take RBS offline / drop it. No need for it now.

Make sure to run verification scripts, verify all objects are valid  / compiled (utlrp.sql). Those are listed in upgrade guides

0
 

Author Comment

by:ramavenkatesa
ID: 24308544
shd i run utlrp.sql in open mode?
0
 

Author Comment

by:ramavenkatesa
ID: 24308580
@/apps/opt/oracle/product/10.2.0/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2009-05-05 19:58:13

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2009-05-05 19:58:26


PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                 11

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


PL/SQL procedure successfully completed.
0
 

Author Comment

by:ramavenkatesa
ID: 24308582
am i fine? -- shd i run any other scripts?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24308624
utlrp must be run with database open, otherwise it cannot make any changes. Learn the difference between the Oracle modes (nomount, mount and open / restricted)

Have you an Oracle DBA guide or do you spend some time reading the online documentation? If not, you should spend some time, all of this is covered in the documentation and google.

I am happy helping you but I recommend you buy this book:

http://www.amazon.com/Oracle-Database-Certification-Guide-Handbook/dp/0072257903

And take a couple of chapters per week and actually read them and do the practice exam. It will help you for the future, although I might not get so many EE points afterwards. ;)
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24308635
>>OBJECTS WITH ERRORS
>>-------------------
>>                 11

You should check those and find out what they are.  Surely they are invalid dependencies. Verify on your old database to see if those were also in error.
0
 

Author Comment

by:ramavenkatesa
ID: 24308733
those errors are created, when i did not complie functions
0
 

Author Comment

by:ramavenkatesa
ID: 24308754
i am learning daily -- i am reading oracle 10g by Sam Alapati.

thanks a lot for all guys who are helping me
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24309487
>>i am learning daily -- i am reading oracle 10g by Sam Alapati.

I have that one too. At least consider my recommendation on the OCP book, it is really about the best preparation you can get, because it presents it in classroom format, and if you take the quizzes, and check your wrong answers, you will learn. Good luck!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

751 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