Jephe Wu - http://linuxtechres.blogspot.com
Environment: Oracle 11g 32bit
Objective: copy schemas base and u_user1 from UAT Oracle DB uatdb to schemas base and d_user1 in devdb
Note: from database uatdb to devdb, the base schema is global schema, keep the name as base, but for u_user1 schema, change the schema name to d_user1
Steps:
1. stop listener - lsnrctl stop
2. drop schema base and u_user1 in uatdb
. oraenv
uatdb
sqlplus / as sysdba
drop tablespace base including contents and datafiles;
drop tablespace u_user1 including contents and datafiles;
drop user base cascade;
drop user u_user1 cascade;
If you cannot drop user, such as it's saying the user has connection to the database. You can use the following commands to kill all the session relating to this user.
sqlplus / as sysdba @killusersession.sql
the content of killusersession.sql is:
-----
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /u01/scripts/killusersessionfinal.sql;
alter user u_user1 account lock;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='u_user1';
select 'exit;' from dual;
spool off;
exit;
---------
sqlplus / as sysdba @killusersessionfinal.sql
3. create tablespace
create tablespace u_user1 datafile '/u01/app/oracle/oradata/uatdb/base.dbf' size 100m autoextend on next 10m flashback off;
note: use 'select name from v$datafile' to find the existing datafile path
4. export and import files
for base schema, since the source and destination schema name is the same, so use
. oraenv
uatdb
expdp system dumpfile=base.dmpdp logfile=base.logdp schemas=base exclude=statistics
note: this will use the default directory which is /u01/app/oracle/admin/uatdb/dpdump
. oraenv
devdb
impdp system dumpfile=base.dmpdp logfile=base.logdp schema=base
note: this will use the default directory which is /u01/app/oracle/admin/devdb/dpdump
for u_user1 schema, we use the following impdp command:
impdp system dumpfile=u_user1.dmpdp logfile=u_user1.logdp remap_schema=u_user1:d_user1 remap_tablespace=u_user1:d_user1 transform=oid:n
5. preparing the existing public synonyms
# preparing existing public synonyms and permission granting script
cat > /u01/cronjob/existing_public_synonym_generation.sql << owner="'PUBLIC';"> exec dbms_utility.compile_schema('BASE');
7. check invalid objects
column object_type format A10;
column object_name format A30;
select object_type "Invalid BASE",object_name from dba_objects where owner='BASE' and status <> 'VALID' order by object_type,object_name;
select object_type "Invalid D_USER1",object_name from dba_objects where owner='D_USER1' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;
select count(*) BASE, object_type from dba_objects where owner='BASE' and status <> 'VALID' group by object_type;
select count(*) U_USER1, object_type from dba_objects where owner='U_USER1' and status <> 'VALID' and object_name not like 'FB_%' group by object_type;
8. start listener
after starting listener, you should 'alter system register' to make database to register with listener immediately.
9. consistent=y
expdp '/ as sysdba' dumpfile=jephe.dmp logfile=jephe.log flashback_time=systimestamp
Note:
a. 11.2 introduced legacy mode, now can use the old exp and imp parameters, including 'consistent=y'.
b. by default expdp is consistent only for the table it is currently exporting.
c. flashback_time=systimestamp is same as 'consistent=y'.
Posts
