Take database schema dump and export

Today I need to migrate my schema from oracle xe in my system to another system which is a Linux box. I had to revisit a number of command, which is quite helpful.

I used the export import utility provided by oracle. Its quite easy and straight forward.

Step 1 Exporting schema to a dump file

D:\Oracle\db\app\oracle\product\10.2.0\server\BIN>expdp system/**** schemas=
hr directory=C:\Temp dumpfile=hrdump.dmp logfile=rexp.log

Export: Release 10.2.0.1.0 - Production on Monday, 31 January, 2011 5:51:24

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
n
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name C:\TEMP is invalid

Then l reliesed that oracle does not know my directory. Then create this directory by login to database with user name and password system/******

sqlplus> create directory hrdump as 'C:\Temp\hrdump';
sqlplus> commit;

After that re-run the export command.

expdp system/******* schemas=
hr directory=hrdump dumpfile=hrdump.dmp logfile=rexp.log

Now its finish successfully, which also give certain output in the log
===================================
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=hr directory=
hrdump dumpfile=hrdump.dmp logfile=rexp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 640 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."PS_TXN" 14.60 KB 6 rows
. . exported "HR"."COUNTRIES" 6.085 KB 25 rows
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.77 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
. . exported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . exported "HR"."LOCATIONS" 7.710 KB 23 rows
. . exported "HR"."OPTIONAL_REQUIRED_TEST" 5.867 KB 2 rows
. . exported "HR"."REGIONS" 5.289 KB 4 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
C:\TEMP\HRDUMP\HRDUMP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:57:02

=============================



Step 2 -- Importing to the database.

While importing I used the export utility provide by oracle.

1. Create the directory in oracle where I have copied my dump files.

$sqlplus system/******
sqlplus> create directory hrdump as 'C:\Temp\hrdump';
sqlplus> commit;

2. Run the command to import the schema, with data.
impdp system/******* schemas=hr directory=hrdum dumpfile=HRDUMP.DMP logfile=hrisimp.log

it failed with the errors
ORA-39083: Object type TABLE:"HR"."DEPARTMENTS" failed to create with error:
ORA-00959: tablespace 'USERS' does not exist

*** Actually same table space I have not created here. So created the users table space by connecting to my db with system user.

sqlplus > create tablespace users datafile '/scratch/oradba/db/apps_st/data/users.dbf' size 10M autoextend on maxsize 200M extent management local uniform size 64K;

in between if you get any issue like temp file does not exists then use this command to crate the temp file directory location you need to modify.

CREATE TEMPORARY TABLESPACE temp TEMPFILE
'/scratch/oradba/db/apps_st/data/temp01.dbf' SIZE 500M autoextend off extent management local UNIFORM SIZE 1m;

Now run the import command again

$ impdp system/****** schemas=hr directory=hrdum dumpfile=HRDUMP.DMP logfile=hrisimp.log

===========================LOG======================================
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** schemas=hr directory=hrdum dumpfile=HRDUMP.DMP logfile=hrisimp.log


Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"HR"."DEPARTMENTS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR"."EMPLOYEES_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR"."LOCATIONS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR"."PS_TXN_SEQ" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."PS_TXN" 14.60 KB 6 rows
. . imported "HR"."COUNTRIES" 6.085 KB 25 rows
. . imported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . imported "HR"."EMPLOYEES" 15.77 KB 107 rows
. . imported "HR"."JOBS" 6.609 KB 19 rows
. . imported "HR"."JOB_HISTORY" 6.585 KB 10 rows
. . imported "HR"."LOCATIONS" 7.710 KB 23 rows
. . imported "HR"."OPTIONAL_REQUIRED_TEST" 5.867 KB 2 rows
. . imported "HR"."REGIONS" 5.289 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"HR"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"HR"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"HR"."EMP_DETAILS_VIEW" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 8 error(s) at 06:21:03
===================================================
-bash-3.2$


It completed successfully but due to some of the objects which does not require users tablespace was created before it gives some error which I can ignore now.

if you need to create the backup only then you can use the first command to create the dump and then use the file as backup.

Comments

Renjith R Chandran said…
Hi, Good Morning

I am working as planning engineer and I use Oracle primavera software.It uses oracle 10g express edition database. In my company we dont have any DBA or IT dept. So I am really worried because sometimes when i open my software it says unable to connect to database or some errors like that. So can you help me to study HOW TO EXPORT complete database to another machine AND how to make a backup of the database and to import in the same machine. As I am not an expert in these things, if you can give me some detailed steps, it will be very much helpful for me. Thanks in advance
9 February 2014 at 20:00
Post a Comment

Popular posts from this blog

Executing a method bindings In Bean method.

Image
Many time you be in a situation where there is a AM method exposed as a client and requirement is to execute that AM method from the backing bean. Its quite simple first you have to register that AM method in the page definition file as method bindings. Now choose the method you want to add to the page definition and executed by a bean method. for the parameter either you can use the bindings if your parameter clearly coming from bindings else leave them blank. Now in the bean method first you have to catch the handle of the DCBindingContainer and then using this binding container you can get the operation binding for the AM method and then can add the parameter. If you are have already bind the parameter with the bindings then can skip that. then you just need to call the execute method of your operation binding.
Read more

Using Java Decompiler(JD) with Oracle Jdeveloper (Jdev)

Image
  Java Decompiler(JD) with Oracle Jdeveloper (Jdev) Java Decompiler is a good tool to check the code, when we just have complied code.  There are ways by which you can check the code, which is been in form of jar with you.   Let say you got ADF Jar file for a project and now want to check, what is the code inside of an entity object IMpl or how this code is structured.  There are some simple steps to it. 1. Create an ADF application with mode and UI project. If you have more project, try to keep the name of the model same as jar. Here I have two jars to check. employee Model.jar and employeeUI.jar.   2. Now go to you jars and then unzip these jars using the unzip or 7 zip prog. 3. Once you done with the unzip then place these directries under the application and project you have created. Please have model code paste under src directory only. 4. Refresh the application now and you will be able to see ...
Read more

Current Logged in User and Roles in ADF and Fusion Apps

A) Using the groovy Expression. We can set default expression in view object attribute as below  adf.context.securityContext.getUserPrincipal().getName()  adf.context.securityContext.getUserName()    For Roles use  adf.context.securityContext.getUserRoles() B) Java Code You can get User Name in Java code also using the following code. ADFContext adfCtx = ADFContext.getCurrent(); SecurityContext secCntx = adfCtx.getSecurityContext(); String user = secCntx.getUserPrincipal().getName(); String userName = secCntx.getUserName(); String[] Roles = secCntx.getUserRoles(); C) Expression  Language You can bind ADF Faces componenets with the following EL to get logged User Name. #{securityContext.userName} #{securityContext.userRoles} D) In Fusion Applications (On Premise Implementation) ApplSession session = ApplSessionUtil.getSession(); String guid1 = session.getUse...
Read more