Recreate database using Full export backup

1 Summary:

This document gives a brief idea about recreating a database (exact replica) from existing
database using Import-Export Backup.

Export, Import are complementary utilities which allow to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files. EXPORT, IMPORT are used for the following tasks:
• backup ORACLE data in operating system files
• restore tables that where dropped
• save space or reduce fragmentation in the database
• move data from one owner to another
• Create replica of existing database.














2 How to Do:


Steps:

Information gathering from Source Database:

1. Connect to source database as a SYS user and collect information about

a. Global database name
b. Db_link details
c. control file , data file , redo log file and temp file details.
d. generate the querry to recreate db link in new database.
e. Collect information about synonyms,views,index details
f. Collect information about user account details and profile details.
g. Collect information about Oracle version.

Take a full export backup of database (Daily export backup is available frpm HP Openview).


2. Set up new server with required amont of RAM and hard disk capacity.
3. Install Operating system.
4. Install Oracle server software (version is same as of existing production).
5. Create a database manually with structure same as that of production database setup.(tablespace name must be identical).
6. create user accounts in new database.
7. Grant permissions to newly created user. Assign profile to user.
8. Import the data to new database.
9. Recreate synonyms, Db links, Views and Indexes in new database.
10. Manually check and confirm that data in new database is same as that of Old one.













Flowchart

















3. At Source Database

Information Gathering from source DB

Global DB Name:

SQL> select * from global_name;

DB Links:


SQL> select * from dba_db_links;


Control file details:

SQL> select name from v$controlfile;

Datafile details:

SQL> select file_name from dba_data_files;


SQL> select member from v$logfile;

Script to Recreate DB Link in new database after import (recreated DB)

SQL> select 'create DATABASE LINK '||owner#||'.'||NAME|| ' connect to '|| userid || ' identified by '|| password || ' using '||''''|| host ||''''||'; ' FROM sys.link$ order by owner#;


Collect User information from production db:

SQL> select ' alter user '||username||' identified by values ' || chr(39)||password||chr(39) || ';' from dba_users;


Get user profile information from production db :

SQL> select ' alter user '||username||' profile '||PROFILE||';' from dba_users;


Synonym details from Pproduction db:

SQL> Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_owner||'.'||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is not null;

Export backup:

Take a full export backup of Source database.

Create directory backup

Sql > create directory backup as '/DISK01/backup';

$ expdp sys/test directory=backup dumpfile=exp01.dmp logfile=exp01.log full=y


At Target Database

recreate user account in recreated database after import :


SQL> create user MGMT_VIEW identified by values 'F25A184809D6458D';

Reset user profile :

SQL> select ' alter user '||username||' profile '||PROFILE||';' from dba_users;

SQL>alter user MGMT_VIEW profile DEFAULT;
.
Recreate synonym in rebuilded database after import:


SQL> Select 'CREATE SYNONYM '||owner||'.'||synonym_name||' FOR '||table_name||'@'||db_link||';' from dba_synonyms where db_link is not null and table_owner is null;

SQL>CREATE SYNONYM EXTTPS.PS_MAS_CHK_VW_HDR FOR PS_MAS_CHK_VW_HDR@FSPROD.WORLD;




Import Data in new database:

Import the data to newly created database.


$ impdp sys/test directory=backup dumpfile=exp01.dmp logfile=imp01.log full=y