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';
Chen Guang's Blog 