Thursday, May 23, 2013

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 **