Home » RDBMS Server » Server Utilities » impdp errors when moving database to new server
impdp errors when moving database to new server [message #208900] Tue, 12 December 2006 08:43 Go to next message
bbeers
Messages: 3
Registered: December 2006
Junior Member
Hi all,

I have two servers both running 10g. I have been tasked with moving an entire database from server 1 to server 2. In going through the manuals, etc. it seems like using the expdp/impdp utility using the full=Y option to be the best solution. I pre-created the database on server 2 (just generic - basically the same ORACLE_SID - I didn't create tables or users or anything mirroring that on server 1), then I exported the database from server 1, ftp'd it over to server 2 and ran the impdp using full=y. However I get a bunch of errors mostly the ora-39112 error, and then it terminates with a fatal error. Here's a snip from the import log:

ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WC3_PDAADMIN"."FINPOCS" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WC3_PDAADMIN"."LEVELS" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WC3_PDAADMIN"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WC3_PDAADMIN"."USERS" creation failed

It seems like it does this for practically everything. My question is, do I need to have anything else (i.e. tables, users, etc.) pre-created before I do the import? What am I missing?

Any help, suggestions would be greatly appreciated.

Thanks,

Brian
Re: impdp errors when moving database to new server [message #208903 is a reply to message #208900] Tue, 12 December 2006 09:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,


ORA-39112: Dependent object type string skipped, base object type string creation failed

Cause: During a Data Pump import job, a dependent object is being skipped because its base object creation failed.

Action: If the object from the dump file is wanted, drop the base and dependent objects and try to import again using desired filters

You can also used Tranasportable_tablespace option.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#i1021683


Or if you can shutdown ur database then
1.take complete cold backup of ur database.
( control file, redo log files, datafiles, spfile )

2. on another server create new database.
3. on new database create pwd file.
4. on new database create services for database
5.restore backup
6.startup ur database.

http://forums.oracle.com/forums/click.jspa?searchID=4246558&messageID=1590891


hope this helps
Taj
Re: impdp errors when moving database to new server [message #208909 is a reply to message #208903] Tue, 12 December 2006 09:44 Go to previous messageGo to next message
bbeers
Messages: 3
Registered: December 2006
Junior Member
Do you mean take everything that is in the oradata/pdaott directory (i.e. control files, redo files, sysauxX.dbf, systemX.dbf, usersX.dbf, etc)? Is that all the files I need or are there more? And where do I put them on the new server?

Thanks,

Brian
Re: impdp errors when moving database to new server [message #208921 is a reply to message #208909] Tue, 12 December 2006 10:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
go below procedure.

1.oraclehome/<sid>/oradata---->all *.dbf files.
all *.log files
all *.ctl files

2.oraclehome/db_1/database---->spfile<sid>.ora
init<sid>.ora

3.oraclehome/flash_recovery_area---> all *.arc files.
( normally no need to by duplicate database but in ur case you transfer complete database server then you do.)


Above all files copy and restore ur destination server.
with identical location ( some location like old Server.)

and
new server.

First create password file
create oracle services.
startup ur database.
go through above posted link and check it.


hope this helps
Taj


Re: impdp errors when moving database to new server [message #208938 is a reply to message #208921] Tue, 12 December 2006 11:39 Go to previous messageGo to next message
bbeers
Messages: 3
Registered: December 2006
Junior Member
Thanks Taj. This has been extremely helpful. I just have a couple more questions:

1. What is meant by "create oracle services"? What exactly needs to be created?

2. The directory structure on the new server has to be exactly like on the old server? What if the structure is different? Will this not work?

(obviously I am fairly new to Oracle. As part of my job I inherited the old server and they want me to move it to a new one. Having very little experience with Oracle I just want to make sure that I completely understand all of the steps and what needs to be moved, etc.)

Once again, thanks for your time and assistance.

Brian
Re: impdp errors when moving database to new server [message #209006 is a reply to message #208938] Tue, 12 December 2006 22:33 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
1. What is meant by "create oracle services"? What exactly needs to be created?

Through ORADIM.EXE utility we can create oracle services and start.

like
start---->run----->cmd <<enter>>

C:\>d:\oracle\product\10.1.0\db_2\bin\oradim.exe -new -sid ORCL -startmode manual -spfile

C:\>d:\oracle\product\10.1.0\db_2\bin\oradim.exe -edit -sid ORCL -startmode auto


Note: change ur <sid> for me is ORCL.

The directory structure on the new server has to be exactly like on the old server? What if the structure is different? Will this not work?

Yes it should be some.
of if it different you have problem with database mount stage.
bcoz we using old server controlfiles and controlfile content location of datafiles and redofiles if it is different then file not found error.


hope this helps
Taj
Previous Topic: exporting data from oracle to excel worksheet
Next Topic: Update through SQL*loader
Goto Forum:
  


Current Time: Mon Oct 20 00:19:55 CDT 2014

Total time taken to generate the page: 0.10913 seconds