Migrating from Oracle EE to Oracle SE

Overview.


How to tell is Oracle is EE or SE:
SELECT INSTR(BANNER,'Enterprise Edition') 
FROM V$VERSION WHERE ROWNUM=1;

For EE
  The string ,'Enterprise Edition' is inside the first row of the banner, and the result is > 0.
For SE
  The returned result is 0.

Oracle Standard Edition limitations
Oracle Standard Edition is missing many options that are available in Enterprise Edition.
Some of these options:
- No Partitioning
- No option for INDEX REBUILD ONLINE
- No Bitmap Indexes
- Database Memory is limited to 2Gb
- Server CPUs limit is 4.
- RMAN backup is limited to one channel.

- Function based Indexes.

When migrating from Oracle Enterprise Edition to Oracle Standard Edition, one cannot just Export and Import, since Oracle SE does not support partitioning.

Thus need a special procedure.

This procedure would be comprised of few steps:
0. Install Oracle SE.
1. Export only the data from source DB.
2. Build schema on target DB.
3. Disable all Constraints and Triggers on target DB.
4. Import data.
5. Enable all Constraints and Triggers on Target DB.
6. Compile all objects

0. Install Oracle SE.
Verify that the environment variables are pointing to Oracle SE installation:
ORACLE_HOME
LIBPATH
LD_LIBRARY_PATH
SHLIB_PATH
TNS_ADMIN

1. Export the data from source DB.

expdp schema_user/password@sid parfile=parfile.prm

parfile.prm contents
DIRECTORY=EXP_DMP_DIR 
DUMPFILE=dmp_file.dmp 
LOGFILE=exp_file.log 
CONTENT=data_only

Note - you must use CONTENT=data_only as to avoid metadata being exported.

Optionally use EXCLUDE_TABLE option, to avoid exporting some tables, which are not the original schema tables, such as PLAN_TABLE.
EXCLUDE=TABLE:"IN('TABLE_A','TABLE_B')";

2. Build schema on target DB.
Assuming one does have all the SQLs to build schema - build schema on target DB.

3. Disable all Constraints and Triggers on target DB. Drop Existing Sequences.
Connect to target DB with sqlplus
set heading off
set feedback off
set linesize 300
set pagesize 2000

spool disable_triggers.sql
select 'ALTER TRIGGER '|| trigger_name || ' DISABLE  '  || ';' from user_triggers;
spool off

spool disable_constraints.sql
select 'ALTER TABLE '|| table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off

spool drop_sequences.sql

select 'DROP SEQUENCE '|| sequence_name || ';' from user_sequences;
spool off

@disable_triggers.sql
@disable_constraints.sql
@drop_sequences.sql
exit

4.A Import data.
Run import using Oracle Import Data Pump.
impdp schema_user/password@sid parfile=param_imp_data.prm

parfile_imp_data.prm contents
DIRECTORY=EXP_DMP_DIR 
DUMPFILE=dmp_file.dmp 
LOGFILE=imp_file.log 
CONTENT=data_only 
TABLE_EXISTS_ACTION=truncate 

Optionally, import schema to another schema:
impdp system/system_password@sid 
DIRECTORY=export_dir 
DUMPFILE=dmp_file.dmp 
LOGFILE=imp_file.log  
CONTENT=data_only  
TABLE_EXISTS_ACTION=truncate
REMAP_SCHEMA=from_user:to_user

========================
DIRECTORY
========================
DIRECTORY is the value of directory_name entry in DBA_DIRECTORIES.
It is pointing to the path in directory_path for that entry.
To create a new entry: 

CREATE DIRECTORY some_dir AS '/some/path/for/export'; 


 To view current Directory path:


 SELECT DIRECTORY_PATH 
 FROM DBA_DIRECTORIES 
 WHERE DIRECTORY_NAME = 'IG_EXP_DIR';

EXCLUDE - Do not import the listed objects

REMAP_SCHEMA - If this option is used, need to run the import as system.

4.B Import Sequences.

Run import using Oracle Import Data Pump.
impdp schema_user/password@sid parfile=param_imp_seq.prm

parfile_imp_seq.prm contents
DIRECTORY=EXP_DMP_DIR 
DUMPFILE=dmp_file.dmp 
LOGFILE=imp_seq_file.log 
INCLUDE=sequence

Optionally, import sequence to another schema:
impdp system/system_password@sid 
DIRECTORY=export_dir 
DUMPFILE=dmp_file.dmp 
LOGFILE=imp_seq_file.log 
INCLUDE=sequence
REMAP_SCHEMA=from_user:to_user

5. Enable all Constraints and Triggers on Target DB.
Connect to target DB with sqlplus

set heading off
set feedback off
set linesize 300
set pagesize 2000

spool enable_triggers.sql
select 'ALTER TRIGGER '|| trigger_name || ' ENABLE  '  || ';' from user_triggers;
spool off

spool enable_constraints.sql
select 'ALTER TABLE '|| table_name || ' ENABLE CONSTRAINT ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off

@enable_triggers.sql
@enable_constraints.sql
exit

6. Compile all objects
EXEC DBMS_UTILITY.compite_schema(schema => target_schema)

Make sure there are no invalid objects.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Appendix
============================

DBA_DATAPUMP_JOBS
============================
Query the status of datapump jobs
SELECT * FROM DBA_DATAPUMP_JOBS

To Kill a datapump job:

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

Links for Oracle documentation of expdp and impdp

- expdp

- impdp