Solved

oracle migration using export/import

Posted on 2009-05-04
57
1,790 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
  • 32
  • 22
  • 2
  • +1
57 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Post the errors?
0
 

Author Comment

by:ramavenkatesa
Comment Utility
errors file attacghed
errors.txt
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
>>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
Comment Utility
. 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
Comment Utility
i did not create any tablespace -- shall i create TS?
0
 

Author Comment

by:ramavenkatesa
Comment Utility
which all TS shd i create?
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
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
Comment Utility
new errors are attached
errors.txt
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Have you created an undo tablespace and enabled automatic undo?
0
 

Author Comment

by:ramavenkatesa
Comment Utility
yes
0
 

Author Comment

by:ramavenkatesa
Comment Utility
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
Comment Utility
show parameter undo_management
0
 

Author Comment

by:ramavenkatesa
Comment Utility
show parameter undo_management;

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

Expert Comment

by:mrjoltcola
Comment Utility
Actually show all undo parameters

show parameter undo
0
 

Author Comment

by:ramavenkatesa
Comment Utility
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
Comment Utility
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
Comment Utility
Make sure you have an spfile. If not, you have to do init.ora
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
previously, i could not complete the import properly - -- i mean , import was not completed.
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.

 

Author Comment

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

Author Comment

by:ramavenkatesa
Comment Utility
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
Comment Utility
can i use -- export dump in 816 and use the import in 10202 or 10204 ?
0
 

Author Comment

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

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
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
Comment Utility
i did not install any patch
0
 

Author Comment

by:ramavenkatesa
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility

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
Comment Utility
You need to check the trace files / alert log to determine what is going on.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>- 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
Comment Utility
shd i run utlrp.sql in open mode?
0
 

Author Comment

by:ramavenkatesa
Comment Utility
@/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
Comment Utility
am i fine? -- shd i run any other scripts?
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
>>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
Comment Utility
those errors are created, when i did not complie functions
0
 

Author Comment

by:ramavenkatesa
Comment Utility
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
Comment Utility
>>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

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

12 Experts available now in Live!

Get 1:1 Help Now