Oracle 10g:Create/Migrate Database from Exported Datapump

Database Creation/Migration By Datapump Utility.

 

This is implemented on test environment.

For this,Database creation with same server and all related mount points and directory created before import.

1.Use the Utility of Estimate for size of dump files and all table info.

expdp system/manager directory=expdp logfile=***_exp.log FULL=Y ESTIMATE_ONLY=Y PARALLEL=4 ESTIMATE=BLOCKS JOB_NAME=ESTIMATE_ONLY_FULL_JOB

2.Take Full export dump.

expdp system/manager directory=expdp  dumpfile=***_full.dmp logfile=****_exp.log FULL=Y  PARALLEL=4 JOB_NAME=full_export STATUS=100

Check the status of job FULL_EXPORT

Worker 4 Status:
  State: WORK WAITING
Job "SYSTEM"."FULL_EXPORT" successfully completed at 11:23:35

3.Check all previous database files and control file completely removed from the server.

4.Test Server purpose remove the database and files .drop the whole database.

5.Start Database in mount restrict mode.

startup mount reastrinct

Before that check all location of Datafiles,log and control file location.

select File_name from dba_data_files;

Select File_name from dba_temp_files;

Select MEMBER from v$logfile;

Select Name from v$controlfile;

6.Drop Database

SQL> select name from v$database;

NAME

---------------------------

****

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

7.Check the file exist on step 5.

If any file exist remove it manually because it will trouble to create new database.

8.Create New Blank Database with default users and Tablespaces.

Add the entry in oratab or use oradim utility.

9.Remove  or backup all old dump logs and trace files.

10.Start Database in nomount mode.

  Startup nomount restrict

11.Create Database with same directory structure as previous.

CREATE DATABASE *****

   USER SYS IDENTIFIED BY oracle

   USER SYSTEM IDENTIFIED BY manager

   LOGFILE GROUP 1 ('/oralogs/oradata/*****/redo01.log') SIZE 50M,

           GROUP 2 ('/oralogs/oradata/***/redo02.log') SIZE 50M,

           GROUP 3 ('/oralogs/oradata/*****/redo03.log') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/oralogs/oradata/****/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/oralogs/oradata/****/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE testuser

      DATAFILE '/oralogs/oradata/****/testuser01.dbf'

      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE temp

      TEMPFILE '/oralogs/oradata/*****/temp0001.dbf'

      SIZE 200M REUSE

   UNDO TABLESPACE undotbs1

      DATAFILE '/oralogs/oradata/*****/undotbs01.dbf'

      SIZE 200M;

Check the status

Database created.

SQL> SQL> SQL> SQL>

Execute the procedure of catlog,catproc,catexp,urlrp

SQL>@$ORACLE_HOME/rdbms/admin/catalog

SQL>@$ORACLE_HOME/rdbms/admin/catproc

SQL>@$ORACLE_HOME/rdbms/admin/catexp

SQL>@$ORACLE_HOME/rdbms/admin/utlrp

Take Tea for 15 minutes.

12.Its time to full import the datapump export dump.Note that if you ignore indexes.It will much faster.Datapump much faster so no need to worry about down time when migration by datapump utility.

Create Directory for dump location.

connect as sysdba to access it without grant the privileges.

Create directory impdp as '/oracle7/impdp/';

Copy the full export dump this location.

 cp  *****_full.dmp /oracle7/impdp/

Execute this command to full import.It will take time longer then export.

impdp system/manager dumpfile=****_full.dmp directory=impdp logfile=*****_import.log full=y

13.Check Logs.There are number of errors come it.

Ignore errors like Object already exist.

Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name
Failing sql is:
E_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists

ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists

ORA-31684: Object type ROLE:"OEM_MONITOR" already exists


ORA-39151: Table "SYSTEM"."DEF$_ERROR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SYSTEM"."DEF$_DESTINATION" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SYSTEM"."DEF$_CALLDEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SYSTEM"."DEF$_DEFAULTDEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip


These are the errors of already created system and sysaux tablespaces that objects already resides in database.

Mainly Check the mandatory records of old count of particular schema.

You may query of dba_objects where all objects come in it.

Check export log where rows count displayed and also match with import logs.

Now, You may ready  to work on newly exported database with reset of high water marks and indexes.

Take whole database statistics immediately.

***All the best **