Jephe Wu - http://linuxtechres.blogspot.com
Environment: Windows 2003 server SE SP2, Oracle 11gR2(11.2.0.2.0, source, dev db) and Oracle 10g(10.2.0.4, dest, prod db)
Objective: Migrate/merge/mix the database on 11gr2 to 10g.
Concept:
1. find out all schemas on source db.
2. create tablespaces with suffix _dev on dest db
3. rename schemas with suffix _dev after import by dmpdp/impdp
4. check compatibility level from source, make sure it's less or equal to dest db version
Steps:
1. export on source db to get tablespace and users creation script, and password of schemas
[source] exp system file=path_to_dump.exp log=path_to_log.exp full=y
only let it run for a while to get those users/tablespaces creation definition.
2. create tablespace on dest db
e.g.
create tablespace training_dev datafile 'path_to' size 100m autoextend on next 10m [flashback off]
create user training_dev identified by values 'xxxxx'(get from step1) default tablespace training_dev temporary tablespace temp;
3. check compatibility level on source db
SELECT name, value FROM v$parameter WHERE name = 'compatible';
4. dump database
expdp system dumpfile=path_to_dump.expdp logfile=path_to_log.logdp exclude=statistics schemas=training
note: if you don't specify directory, it will use default directory 'data_pump_dir'. You can get it from command
sqlplus / as sysdba
select * from dba_directories;
5. import database
impdp system dumpfile=xxxxx logfile=xxxxx remap_schema=training:training_dev remap_tablespace=training:training_dev transform=oid:n
note: you can test it if you are able to import between 10g and 11g without creating training_dev tablespace and users first.
Migrate Oracle database from 11g to 10g on another server
How to compare the 2 schemas table structures and refresh packages/procedures/functions from one schema to another
Jephe wu - http://linuxtechres.blogspot.com
Objective: to compare the 2 schemas to make sure the database structures are same. The data inside the tables might not be the same, but the table columns and data type must be the same. Also, all the programs such as packages/procedures/functions etc must be the same.
Environment: Oracle 11g 64bit on RHEL 5
Steps:
1. exporting the packages/functions/procedures from jephe1 schema
You can use sql developer database export function (tick terminator, pretty print, and include drop statement) to export them from schema jephe1 then use 'run script(F5)' to run in another schema(jephe2).
But I have encountered some issues such as the sql developer doesn't seem to understand the following multiple lines comments:
/***************
....
...
****************/
and if I use sqlplus, I encountered error like this:
SP2-0027: Input is too long (> 2499 characters) - line ignored
=> solution to this
=>
Open the file in a text editor and then resave the file as a different file
type. In Microsoft Word, you click on File, Save As, then select file type
'text only with line breaks' and save the file. Once this is done, you can
successfully run the script from SQL*Plus or Server Manager.In this article, I am using expdp/impdp tool to sync them.
$ more jephe1.par
directory=oracle
schemas=jephe1
dumpfile=jephe1.packages.dumpdp
logfile=jephe1.packages.logdp
include=PACKAGE
include=PROCEDURE
include=FUNCTION
$ sqlplus / as sysdba
create [ or replace ] directory oracle as '/home/oracle’
grant read,write on directory oracle to system
select * from dba_directories
$ expdp system parfile=jephe1.par
2. drop all packages/procedures/functions in jephe2 schema
We need to drop all of them before using impdp to import. You can use SQL developer 'database export' function to generate the drop statements, or use the following sql statements to generate it through sqlplus command line.
$ more generate_drop_obj.sql
set pagesize 0 feedback off
spool drop_obj.sql
select 'drop '||object_type||' '||object_name||';' from user_objects where object_type in ('PACKAGE','PROCEDURE','FUNCTION','VIEW') and object_name not like 'FB_%' order by object_type,object_name;
exit;
spool off;
$ sqlplus jephe1
@generate_drop_obj.sql
@drop_obj.sql
After that, you can use sql developer to check jephe2 schema to make sure all procedures/packages/functions are dropped.
3. importing the expdp dump file to jephe2 schema
$ impdp system directory=oracle dumpfile=jephe1.packages.dumpdp logfile=jephe2.packages.logdp remap_schema=jephe1:jephe2 remap_tablespace=jephe1:jephe2 transform=oid:n
4. compare table structures between jephe1 and jephe2.
$ more comparetables.sql
Set head off feedback off echo off term off pagesize 0 linesize 32767 trimsp on tab off;
Spool /tmp/jephe1;
Select table_name||','||column_name||','||data_type||','||char_length||','||data_precision||','||data_scale from user_tab_columns order by table_name||','||column_name;
Spool off;
exit;
$ sqlplus jephe1
@comparetables.sql
change spool /tmp/jephe1 line to spool /tmp/jephe2, then run it again, after that, use diff to compare:
$ diff -c /tmp/jephe1 /tmp/jephe2
5. compile the whole schema for jephe2
$ sqlplus / as sysdba
exec dbms_utility.compile_schema('JEPHE2');
How to restore objects for Oracle database
Jephe Wu - http://linuxtechres.blogspot.com
Objective: restore backup some tables, procedures and packages from daily night backup
Environment: RHEL 5 64bit, Oracle 11g 64bit
Steps:
1. daily cronjob backup job
put the following inside your cronjob shell script to backup the schema1 and schema2 excluding table JEPHE1
export PATH=/usr/bin:/usr/sbin:/bin:/sbin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=XE
ORAENV_ASK=NO
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh # or whatever path of file
unset ORAENV_ASK
#===========program starts here
DAY=`date +%w`
for i in schema1 schema2
do
expdp userid=\"/ as sysdba\" directory=cronjob dumpfile=$i.dmpdp.$DAY logfile=$i.logdp.$DAY schemas=$i exclude=statistics parfile=/path/to/excludes.par
sync;sleep 3
rm -f $i.dmpdp.$DAY.gz $i.logdp.$DAY.gz
gzip /path/to/$i.dmpdp.$DAY /path/to/$i.logdp.$DAY
done
# more excludes.par
exclude=TABLE:"in ('JEPHE1')"
2. restore preparation
use sql script to delete those procedures and packages first before actual restoration, for tables, you can use table replace action parameter, no need to delete tables first.
# more drop.sql
drop procedure procedure1;
drop package package1;
# sqlplus schema1 @drop.sql
3. create database directory if necessary
I am going to conigure /home/oracle as database directory name 'oracle and copy those backup files under /home/oracle.
create [ or replace ] directory oracle as '/home/oracle’
grant read,write on directory oracle to system
select * from dba_directories
4. actual restoration process (from schema1 objects backup to schema2 schema)
stop listener - lsnrctl stop
impdp system parfile=schema1.par logfile=schema1.logdp remap_schema=schema1:schema2 remap_tablespace=schema1:schema2 transform=oid:n
# more schema1.par
directory=oracle
dumpfile=schema1.dmpdp
include=PROCEDURE:"in ('PROC1')"
include=PACKAGE:"in ('PAC1')"
include=TABLE:"in ('TABLE1')"
Table_exists_action=replace
Note:
a. if you need to restore to the objects from the same schema. (from schema1 backup to schema1 schema)
impdp system parfile=schema1.par logfile=schema1.logdp schemas=schema1
or
5. compile all schema objects then check invalid objects
sqlplus / as sysdba
exec dbms_utility.compile_schema('SCHEMA1');
For how to check invalid objects, refer to another article at http://linuxtechres.blogspot.com/2010/06/how-to-do-deployment-for-oracle.html
6. start listener and register services
lsnrctl start
sqlplus / as sysdba
alter system register;
exit
7. Appendix: how to restore a package from dumpfile and generate sql file without actual restoration.
impdp directory=cronjob dumpfile=jephe.dmpdp.6 schemas=JEPHE include=PACKAGE:\"=\'NAMEOFOBJECT\'\" sqlfile=NAMEOFOBJECT.sql
How to copy the whole schema from one server to another in Oracle 11g
Objective: clone one schema from one Oracle 11g database to another 11g database server, schema name might change.
Environment: RHEL5, From Oracle 11g 64bit to Oracle 11g 32bit.
Steps:
1. create server side directory first before exporting schema
. oraenv
orcl
sqlplus / as sysdba
create or replace directory dmpdp as '/u01/dmpdp';
grant read,write on directory dmpdp to system;
note: you can use 'select * from dba_directories' to check after creation.
2. use expdp to dump schema
expdp system directory=dmpdp dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logdp schemas=jephe
note: if you are exporting schema for lower version of Oracle database, such as 10g, you might need to use exp instead of expdp:
exp system file=jephe.20091210.dmp log=jephe.20091210.log statistics=none owner=jephe
3. check the existing schema on destination database server
. oraenv
uatdb
sqlplus / as sysdba
select name from v$datafile;
select distinct s.owner,s.tablespace_name,d.file_name from dba_segments s,dba_data_files d where s.tablespace_name = d.tablespace_name;
note: above statement lists out tablespace names for relevant schema.
drop tablespace jephe including contents and datafiles
drop user jephe cascade;
note: if above drop user command got ORA-01940 error: cannot drop a user that is currently connected. Then you need to kill all the existing user connections first.
You can use the following scripts to do it:
# more killusersession.sql
set head off feedback off pagesize 0 echo off term off linesize 32767 trimsp on tab off define off;
spool /u01/scripts/killusersessionfinal.sql;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='JEPHE';
select 'exit;' from dual;
spool off;
exit;
cd /u01/scripts/
sqlplus / as sysdba @killusersession.sql
sqlplus / as sysdba @killusersessionfinal.sql
4. create tablespace on destination database server and import
sqlplus / as sysdba
select name from v$datafile;
create tablespace jephe datafile '/path/to/jephe.dbf' size 100m autoextend on next 10m flashback off;
exit
if you are going to use imp instead of impdp to import schema later, you will have to do this:
create tablespace jephe datafile ‘/u01/app/oracle/oradata/uatdb/jephe.dbf’ size 100m autoextend on next 10m flashback off
create user jephe identified by password default tablespace jephe temporary tablespace temp;
grant connect,resource,create view, select any table to jephe
revoke unlimited tablespace from jephe
alter user jephe quota unlimited on jephe;
5. transfer dumpfile to /home/oracle on destination server and define the /home/oracle as 'oracle'
sqlplus / as sysdba
create or replace directory oracle as '/home/oracle';
grant read,write on directory dmpdp to system;
exit;
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp schemas=jephe
if you use imp, do this:
imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=jephe;
note:
1. if you need to import to another schema, you can do this:
firstly create tablespace zhitan, then run
# impdp system directory=oracle dumpfile=jephe.20091210.dmpdp logfile=jephe.20091210.logimpdp remap_schema=jephe:zhitan remap_tablespace=jephe:zhitan transform=oid:n
or
# imp system file=/home/oracle/jephe.dmp log=/home/oracle/jephe.implog fromuser=jephe touser=zhitan;
note: if using imp to import from one user to another use, you need to
alter user zhitan default tablespace zhitan;
Revoke the unlimited tablespace and change the new user’s quota to have space only on the new tablespace:
revoke unlimited tablespace from zhitan;
alter user zhitan quota unlimited on zhitan quota 0 on jephe;
6. checking after import
see if the destination tablespace got data after import
sqlplus / as sysdba
SQL> column owner format a20
SQL> select owner,count(*) from dba_segments where tablespace_name='TEST_DATA' group by owner;
see if user jephe is using his own tablespace:
select username,default_tablespace from dba_users where username='JEPHE';
7. compile the schema
sqlplus / as sysdba
sql> exec dbms_utility.compile_schema('JEPHE');
8. check invalid objects and the number of invalid objects for schema JEPHE
# more numberofinvalidobjects.sql
select count(*) JEPHE, object_type from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' group by object_type;
# more invalidobjects.sql
column object_type format A10;
column object_name format A30;
select object_type "Invalid JEPHE",object_name from dba_objects where owner='JEPHE' and status <> 'VALID' and object_name not like 'FB_%' order by object_type,object_name;
How to use impdp/expdp to transfer schema from one database to another
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
