Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1864
  • Last Modified:

oracle migration using export/import

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
ramavenkatesa
Asked:
ramavenkatesa
  • 32
  • 22
  • 2
  • +1
2 Solutions
 
k_murli_krishnaCommented:
0
 
mrjoltcolaCommented:
Post the errors?
0
 
ramavenkatesaAuthor Commented:
errors file attacghed
errors.txt
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
schwertnerCommented:
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
 
mrjoltcolaCommented:
>>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
 
ramavenkatesaAuthor 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..

0
 
ramavenkatesaAuthor Commented:
i did not create any tablespace -- shall i create TS?
0
 
ramavenkatesaAuthor Commented:
which all TS shd i create?
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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.
0
 
ramavenkatesaAuthor Commented:
new errors are attached
errors.txt
0
 
mrjoltcolaCommented:
Have you created an undo tablespace and enabled automatic undo?
0
 
ramavenkatesaAuthor Commented:
yes
0
 
ramavenkatesaAuthor 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




0
 
mrjoltcolaCommented:
show parameter undo_management
0
 
ramavenkatesaAuthor Commented:
show parameter undo_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
SQL>
0
 
mrjoltcolaCommented:
Actually show all undo parameters

show parameter undo
0
 
ramavenkatesaAuthor Commented:
show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string
SQL>
0
 
mrjoltcolaCommented:
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
 
mrjoltcolaCommented:
Make sure you have an spfile. If not, you have to do init.ora
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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?
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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...
0
 
ramavenkatesaAuthor 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...
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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 ..
0
 
ramavenkatesaAuthor Commented:
previously, i could not complete the import properly - -- i mean , import was not completed.
0
 
ramavenkatesaAuthor Commented:
shall i delete the oracle software -- and do all over again ?
0
 
ramavenkatesaAuthor 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
0
 
ramavenkatesaAuthor Commented:
can i use -- export dump in 816 and use the import in 10202 or 10204 ?
0
 
ramavenkatesaAuthor Commented:
i am using this script to give me the dump.
backup-db.txt
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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

0
 
ramavenkatesaAuthor Commented:
i did not install any patch
0
 
ramavenkatesaAuthor 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
0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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

0
 
ramavenkatesaAuthor 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

0
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor Commented:

CREATE DATABASE vectru
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
 -- i am getting this error
0
 
mrjoltcolaCommented:
You need to check the trace files / alert log to determine what is going on.
0
 
mrjoltcolaCommented:
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
 
schwertnerCommented:
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
 
ramavenkatesaAuthor 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?
0
 
mrjoltcolaCommented:
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
 
mrjoltcolaCommented:
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
 
ramavenkatesaAuthor 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?
0
 
mrjoltcolaCommented:
>>- 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
 
ramavenkatesaAuthor Commented:
shd i run utlrp.sql in open mode?
0
 
ramavenkatesaAuthor 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.
0
 
ramavenkatesaAuthor Commented:
am i fine? -- shd i run any other scripts?
0
 
mrjoltcolaCommented:
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
 
mrjoltcolaCommented:
>>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
 
ramavenkatesaAuthor Commented:
those errors are created, when i did not complie functions
0
 
ramavenkatesaAuthor Commented:
i am learning daily -- i am reading oracle 10g by Sam Alapati.

thanks a lot for all guys who are helping me
0
 
mrjoltcolaCommented:
>>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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 32
  • 22
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now