We help IT Professionals succeed at work.

oracle migration using export/import

ramavenkatesa
on
2,116 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 ?



Comment
Watch Question

Top Expert 2009

Commented:
Post the errors?

Author

Commented:
errors file attacghed
errors.txt
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
Top Expert 2009

Commented:
>>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.

Author

Commented:
. 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..

Author

Commented:
i did not create any tablespace -- shall i create TS?

Author

Commented:
which all TS shd i create?
Top Expert 2009

Commented:
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;

Author

Commented:
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.

Author

Commented:
new errors are attached
errors.txt
Top Expert 2009

Commented:
Have you created an undo tablespace and enabled automatic undo?

Author

Commented:
yes

Author

Commented:
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




Top Expert 2009

Commented:
show parameter undo_management

Author

Commented:
show parameter undo_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
SQL>
Top Expert 2009

Commented:
Actually show all undo parameters

show parameter undo

Author

Commented:
show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string
SQL>
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2009

Commented:
Make sure you have an spfile. If not, you have to do init.ora
Top Expert 2009

Commented:
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. :)

Author

Commented:
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?
Top Expert 2009

Commented:
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.

Author

Commented:
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...

Author

Commented:
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...
Top Expert 2009

Commented:
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.

Author

Commented:
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 ..

Author

Commented:
previously, i could not complete the import properly - -- i mean , import was not completed.

Author

Commented:
shall i delete the oracle software -- and do all over again ?

Author

Commented:
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

Author

Commented:
can i use -- export dump in 816 and use the import in 10202 or 10204 ?

Author

Commented:
i am using this script to give me the dump.
backup-db.txt
Top Expert 2009

Commented:
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

Author

Commented:
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

Author

Commented:
i did not install any patch

Author

Commented:

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
Top Expert 2009

Commented:
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.

Author

Commented:
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

Author

Commented:

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

Top Expert 2009

Commented:
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.

Author

Commented:

CREATE DATABASE vectru
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
 -- i am getting this error
Top Expert 2009

Commented:
You need to check the trace files / alert log to determine what is going on.
Top Expert 2009

Commented:
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
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Top Expert 2009

Commented:
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.
Top Expert 2009

Commented:
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.

Author

Commented:
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?
Top Expert 2009

Commented:
>>- 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

Author

Commented:
shd i run utlrp.sql in open mode?

Author

Commented:
@/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.

Author

Commented:
am i fine? -- shd i run any other scripts?
Top Expert 2009

Commented:
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. ;)
Top Expert 2009

Commented:
>>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.

Author

Commented:
those errors are created, when i did not complie functions

Author

Commented:
i am learning daily -- i am reading oracle 10g by Sam Alapati.

thanks a lot for all guys who are helping me
Top Expert 2009

Commented:
>>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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.