Platform: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Database Version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
>>>> PART 1 - Set Environment Variables on Source Server <<<<
:: Login to Source Server
:: Set the correct SID
[ORCLA10 /home/oracle]$ . oraenv
ORACLE_SID = [ORCLA10] ? ORCLA10
:: Set the correct NLS_LANG environment variable or else you'll encounter this message: "EXP-00091: Exporting questionable statistics."
:: Query it from SQL*Plus
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
:: Copy and execute the resulting query in Linux ConsoleSELECT 'export NLS_LANG='
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_LANGUAGE'
)
||'_'
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_TERRITORY'
)
||'.'
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET'
)
||''
FROM V$NLS_PARAMETERS
WHERE parameter IN ('NLS_LANGUAGE','NLS_CHARACTERSET','NLS_TERRITORY')
AND rownum <2;
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
:: Verify that environment variable is correct
[ORCLA10 /home/oracle]$ env |grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
>>>> PART 2 - Check Database, Tablespace or Schema Size of Source and Target Databases <<<<
:: Check the size of tablespaces on the source and target DBs. Adjust the size of the target as necessary.
:: If schema refresh, check schema size and its default tablespace. Adjust the size of the target as necessary.
col mb for 9,999,999.99
select owner,sum(bytes/1024/1024) "MB" from dba_segments group by owner order by mb desc;
>>>> PART 3 - Execute Database, Schema, Table, etc. EXPORT <<<<select username, default_tablespace from dba_users where username = 'MAXIMO';
:: Using exp
:: For more options, use: exp -help
[ORCLA10 /home/oracle]$ exp -help
Export: Release 10.2.0.5.0 - Production on Fri Jan 20 22:49:44 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
:: Export a specified schema/s
exp " '/ as sysdba' " FILE=<Dump_File> LOG=<Log_File> OWNER=<Schema1,Schema2> <Additional Parameters>
e.g.
exp " '/ as sysdba' " FILE=dumpfile.dmp LOG=logfile.log OWNER=SCOTT CONSISTENT=Y
exp " '/ as sysdba' " FILE=dumpfile.dmp LOG=logfile.log OWNER=SCOTT,HR compress=N BUFFER=65536 DIRECT=y CONSISTENT=y FEEDBACK=5000 RESUMABLE=y RESUMABLE_TIMEOUT=7200 &
:: Export by limiting each dump file to a specified file size
exp " '/ as sysdba' " FILE=(exp1.dmp exp2.dmp exp3.dmp exp4.dmp exp5.dmp exp6.dmp) LOG=SCOTT_HR.log OWNER=SCOTT,HR FILESIZE=5
:: Export Entire Database
exp " '/ as sysdba' " FULL=y FILE=FullDB.dmp LOG=DBFull.log
:: Export a specified tables/
exp " '/ as sysdba' " FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
>>>> PART 4 - Migrate Dump File <<<<
:: Copy the export file from source to target host
scp -vp DumpFile.dmp user@target.machine:/tmp
>>>> PART 5 - Set Environment Variables On Target Host <<<<
:: Login to Source Server
:: Set the correct SID
[ORCLA10 /home/oracle]$ . oraenv
RACLE_SID = [ORCLA10] ? ORCLA10
:: Set the correct NLS_LANG environment variable or else you'll encounter this message: "EXP-00091: Exporting questionable statistics."
:: Query it from SQL*Plus
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
SQL>
SELECT 'export NLS_LANG='
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_LANGUAGE'
)
||'_'
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_TERRITORY'
)
||'.'
||
(SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET'
)
||''
FROM V$NLS_PARAMETERS
WHERE parameter IN ('NLS_LANGUAGE','NLS_CHARACTERSET','NLS_TERRITORY')
AND rownum <2;
:: Copy and execute the resulting query in Linux Console
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
:: Verify that environment variable is correct
[ORCLA10 /home/oracle]$ env |grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
:: Get Metadata
[ORCLA10 /home/oracle]$ sqlplus '/ as sysdba'
SET HEAD OFF
SET PAGES 0
SET LONG 9999999
SPOOL build_user.sql
--PROMPT Enter USERNAME Name
--ACCEPT USERNAME
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
WHERE USERNAME = '&&USERNAME'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
where username ='&&USERNAME'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
WHERE USERNAME ='&&USERNAME'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
WHERE USERNAME ='&&USERNAME'
;
:: Drop the schema to be refesh on target Oracle Database
SQL> DROP USER <SCHEMA> CASCADE;
:: Recreate the schema. Run the previously generated metadata.
SQL>
CREATE USER "HR" IDENTIFIED BY VALUES '6399F3B38EDF3288'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
ACCOUNT LOCK
/
GRANT "RESOURCE" TO "HR"
/
GRANT CREATE DATABASE LINK TO "HR"
GRANT CREATE SEQUENCE TO "HR"
GRANT CREATE VIEW TO "HR"
GRANT CREATE SYNONYM TO "HR"
GRANT UNLIMITED TABLESPACE TO "HR"
GRANT ALTER SESSION TO "HR"
GRANT CREATE SESSION TO "HR"
/
GRANT EXECUTE ON "SYS"."DBMS_STATS" TO "HR"
/
:: Run import using imp
For schema import,
imp " '/ as sysdba' " file=exp_file.dmp log=imp_file.log fromuser=HR touser=test1 ignore=y
:: NOTE: Verify alert logs for status ::
:: Compare DDL of old and newly refreshed schema.
:: Query Invalid Objects
SQL> SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID' and owner = 'HR';
:: Recompile if necessary (Run as SYS)
SQL> @${ORACLE_HOME}/rdbms/admin/utlrp.sql
:: Verify the number of objects from source and target databases
SQL> select count(*) from dba_objects where OWNER='HR';
:: If the number of objects are not the same on both source and target database, perform the below:
set pages 9999
set lines 250
set heading off
spool dba_objects.sql
select distinct 'select count(*) from dba_objects where object_type = '''||object_type||''' and owner = '''||owner||''';' from dba_objects where OWNER='HR';
select distinct 'select object_name from dba_objects where object_type = '''||object_type||''' and owner = '''||owner||''' order by object_name;' from dba_objects where OWNER='HR';
spool off
set heading on
@dba_objects.sql