Refreshing development environment using expdp/impdp

Introduction

This note describes the procedure of refreshing a schema in development environment with latest production data.

Environment

Production Database

  • machine: 192.168.1.46
  • OS user: glporacle
  • DB name: GOLDPROD
  • user to perform expdp: expuser/expuser
  • export directory: /gold/GLP/archive/expdp

Development Database

  • Test machine: 192.168.1.42
  • OS user: gltoracle
  • DB name: GOLDTEST
  • user to perform impdp: expuser/expuser
  • import directory: /gold/GLT/archive/impdp

Export schema CENPROD from production using expdp

ssh 192.168.70.205
su - glporacle
cd /gold/GLP/archive/expdp

— Verify if there is enough space to hold dump files

The schema CENPROD is currently estimated to occupy10G; ensure there is enough space in current directory to save export dump files.


df -k /gold/GLP/archive

Filesystem    1024-blocks      Free %Used    Iused %Iused Mounted on
/dev/goldglparclv    73400320  <strong>46348548</strong> 37%      961     1% /gold/GLP/archive

— Verify if there is no existing any old dump files *.dmp

if there is any then delete them before executing export pump process.

rm *.dmp

— Run export pump as oracle user ‘expuser’

nohup expdp parfile==expdp_user_cenprod.par &

where parfile has the following contents:

userid="expuser/expuser"
schemas=CENPROD
directory=EXPDP_DIR
parallel=4
dumpfile=user_CENPROD_%U.dmp
logfile=expdp_CENPROD.log

If any errors or warnings shown up, refer to the log file “expdp_CENPROD.log” in current directory for details.

Move dump files to testing environment

Transfer all dump files to development machine.

Import into CENTEST on development database

ssh 192.168.1.42
su - gltoracle
cd /gold/GLT/archive/impdp

$ ls -l

total 19990928
-rw-r--r--    1 gltoracl gloinst         307 Feb 11 15:13 02_grant_java_permission_to_centest.sql
-rw-r--r--    1 gltoracl gloinst        1728 Feb 04 17:02 generate_recreate_script.sql
-rw-r--r--    1 gltoracl gloinst         207 Feb 04 17:31 impdp_user_CENTEST.par
-rw-r--r--    1 gltoracl gloinst         424 Feb 14 10:32 build_replace_synonyms_script.sql
-rw-r-----    1 gltoracl gloinst    23924736 Feb 04 16:56 user_CENPROD_01.dmp
-rw-r-----    1 gltoracl gloinst  3872714752 Feb 04 16:57 user_CENPROD_02.dmp
-rw-r-----    1 gltoracl gloinst  2738532352 Feb 04 16:57 user_CENPROD_03.dmp
-rw-r-----    1 gltoracl gloinst  3600035840 Feb 04 16:58 user_CENPROD_04.dmp

Generate recreation script of schema CENTEST

The file “build_recreate_centest_script.sql” is used to generates a recreation script

set echo off
define username='CENTEST'
set lines 132
set head off
set feedback off
set verify off
set termout on
set long 10000
REM pause "Please give the user that you're going to recreate"
ACCEPT user_name CHAR default &username prompt 'Insert user name:[&username]'

spool 01_recreate_user_&&username\.sql

prompt spool 01_recreate_user_&&username\.log

select 'create user '||USERNAME||' identified by values '''||PASSWORD||''''||chr(10)|| -
'temporary tablespace '||TEMPORARY_TABLESPACE||chr(10)|| -
'default tablespace '||DEFAULT_TABLESPACE||chr(10)|| -
'profile '||PROFILE||';' -
from dba_users where username='&&user_name';

select 'alter user '||username||' quota '||decode(MAX_BYTES,-1,'unlimited',MAX_BYTES)||' on '||tablespace_name||';'
from dba_ts_quotas where username='&&user_name';

select 'grant '||PRIVILEGE||' to '||GRANTEE||decode(ADMIN_OPTION,'YES',' with admin option',null)||';'
from dba_sys_privs where grantee='&&user_name';

select 'grant '||GRANTED_ROLE||' to '||GRANTEE||decode(ADMIN_OPTION,'YES',' with admin option',null)||';'
from dba_role_privs where grantee='&&user_name';

select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE|| -
decode(GRANTABLE,'YES',' with grant option',null)||decode(HIERARCHY,'YES',' with hierarchy option',null)||';' -
from dba_tab_privs where grantee='&&user_name' -
 and table_name not in (select DIRECTORY_NAME from dba_directories);

select 'grant ' || PRIVILEGE || ' on directory ' || DIRECTORY_NAME || ' to CENTEST;' -
  from dba_tab_privs a,  -
       dba_directories b  -
 where a.grantee='CENTEST' -
   and a.TABLE_NAME=b.DIRECTORY_NAME;

prompt spool off
prompt quit
spool off

$sqlplus expuser/expuser @build_recreate_centest_script.sql

A script named 01_recreate_user_CENTEST.sql is generated in current directory;

drop user centest

sqlplus expuser/expuser
SQL> select sid, serial#, username, machine, program, status from v$session where username= 'CENTEST';

If it returns any sessions of ‘CENTEST’, kill them

SQL> alter system kill session 'sid, serial#';

Run the following command to drop user until there is no any session of CENTEST returned by the above query.

SQL> drop user centest cascade;

Recreate CENTEST user structure

SQL> @01_recreate_user_CENTEST.sql

Check log file “01_recreate_user_CENTEST.log” in current directory and if any errors correct them before proceeding ahead.

Import data into centest

parameter file “impdp_user_CENTEST.par” has the following contents

userid="expuser/expuser"
REMAP_SCHEMA=CENPROD:CENTEST
REMAP_TABLESPACE=CENPROD_DATA:CENTEST_DATA,CENPROD_IDX:CENTEST_IDX
DIRECTORY=IMPDP_DIR
EXCLUDE=GRANT
PARALLEL=4
DUMPFILE=user_CENPROD_%U.dmp
LOGFILE=impdp_CENPROD_to_CENTEST.log

Launch export pump import process:

nohup impdp expuser/expuser parfile=impdp_user_CENTEST.par &

Master table "EXPUSER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "EXPUSER"."SYS_IMPORT_FULL_01":  expuser/******** parfile=impdp_user_CENTEST.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CENTEST" 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/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CENTEST"."STOCOUCH"                        262.3 MB 1813316 rows
......

Check log file impdp_CENPROD_to_CENTEST.log in current directory and if any errors occurred, correct them before proceeding ahead.

Replace synonyms references

Run the following script as ‘expuser’ user to build a replacing script.

spool 03_replace_synonyms.sql
set lines 200 pages 0 echo off feedback off
prompt spool 03_replace_synonyms\.log
select 'CREATE OR REPLACE SYNONYM ' || owner ||'.' || SYNONYM_NAME || ' FOR ' ||
       decode(TABLE_OWNER,
              '&fromuser','&touser' || '.',
              null, '',
              TABLE_OWNER || '.') || TABLE_NAME || decode(TABLE_OWNER, null, '@'|| db_link || ';', ';')
from dba_synonyms where OWNER='STKTEST';
prompt spool off
prompt quit
spool off
quit

sqlplus expuser/expuser @build_replace_synonyms_script.sql

A script named “03_replace_synonyms.sql” is created in current directory. Run it as ‘expuser’ user:

sqlplus expuser/expuser @03_replace_synonyms.sql

Check log file “03_replace_synonyms.log ” for execution results.

Recompile invalid objects of centest

sqlplus expuser/expuser

SQL> exec dbms_utility.COMPILE_SCHEMA(‘CENTEST’);

After a compilation of schema CENTEST, verify if there is any remaining invalid object

select object_name, object_type, status from dba_objects where owner='CENTEST' and status ='INVALID';
Advertisements
Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: