Convert Oracle Enterprise Edition to Standard (DownGrade)

Oracle support referred me to Metalink Note 139642.1 to downgrade Enterprise Edition (EE) to the
Standard Edition (SE). They said we can not just downgrade the software, this will not get rid of EE.  I MUST export / import, in order to go from EE to SE.

The database was just 30GB so I decided to pick up old import/export mechanism

1. Shutdown current Live database (15 mins) & put database services in Manual mode.

2. Startup database in restricted mode so that only DBA users can connect (15 mins)

3. List all invalid objects and spool them (30 mins)

4. Export all individual users from this database in DMP format (probably 3 hours)

5. Install new Standard Edition at different ORACLE_HOME (1 hour)

6. Create a new blank database (1 hour) & configure listener/tnsnnames files for it.

7. Configure init parameters for running import quicker (30 mins)

8. Import Individual Users ( normally double the time of export )

9. Run utlrp.sql to compile all objects at new instance (30 mins)

10. Compare invalid list of objects with spool received at step 3 ( 1 hour)

11. Carry out Functional Testing ( 3 hours)

12. If tests go OK, delete old database instance + software ( 2 hour)

13. Rename new instance to Live i.e testers(1 hour)

Change of Plan

Initially I went for safe option import/export. I thought Oracle 10g standard edition doesn’t have transportable tablespace option enabled but to my surprise we can import transportable tablespace at standard edition but can’t export it out.

I was also worried for the dependency between objects and time it would take to export and import the 24GB data. I decided to try another method thru following plan.

The Process

1. First, Identify the self-contained tablespace(s) and alter them read only.

2. Second, you must generate the transportable tablespace set containing the datafiles associated to the tablespace and a file containing the table space metadata in the form of an export file.

3. Third, ensure that the receiving disks have sufficient room to copy the datafiles and the export files. Use operating system commands.

4. Finally, plug the tablespace into the target database(s) and alter the tablespace online.

Step by Step

1. Connect to the database as sys sysdba

2. Identify the users associated to the tablespace in question.

Select Username, Default_Tablespace, Temporary_Tablespace
From Dba_Users
Where (Default_Tablespace = <Tablespace_Name>
Or Temporary_Tablespace = <Tablespace_Name>);

3. Using Sql/Plus or Worksheet connect as sys and compile script dbmsplts.sql

4. Grant execute on dbms_tts to public;

5. Create public synonym dbms_tts for sys.dbms_tts;

6. Execute dbms_tts.transport_set_check(<tablespace_name>,TRUE);
The TRUE parameter ensures the constraints relating to the objects are all in the same tablespace in question.

7. Select * from transport_set_violations;
There should be no rows returned, which indicates the tablespace is self-contained. If rows are returned then consider transporting the tablespace where the violated object(s) reside, this due to referential constraints linked to objects not in the same tablespace in question.

8.Select the file_name from dba_data_files. ***
Select File_name From dba_data_files Where tablespace_name in (<Tablespace_Name>,<Tablespace_Name>);

9.Set the tablespace in question to read only.
Alter Tablespace <Tablespace_name> Read Only;

10. Generate the transportable tablespace set containing the datafiles associated to the tablespace and a file containing the tablespace metadata in the form of an export file. The export will be carried out interactively, and must be prompted for the username, the username must be sys as sysdba and whatever the password is set for SYS.
The following identifies the parameters used for the transportable tablespace.
File Name: Exp_TTS.par Transport_Tablespace=y Tablespaces=(testers) Triggers=y Constraints=y Grants=y File=D:\testers\tts.dmp Log=sD:\testers\tts.log

11.  Copy the datafiles using operating system commands identified from the earlier step *** to the new location of the database renaming the datafiles to ODBA standards.

12. Once the tablespaces datafiles are copied to their new location, Run the import utility to move the tablespace metadata into the new database.
Note the target database must not have the same tablespace name.
File Name: Imp_TTS.par Tablespaces=(testers) Datafiles=(D:\testers\tts.dmp ) Indicates the datafile to use associated to the Tablespace. This is the copied Datafile. tts_owners=(testers) Indicates the owners of objects in the Tablespace, These owners must be made available in the target database. File=s:\testers.dmp Log=s:\testers.log

13. Finally, alter the tablespace out of read mode.
Alter tablespace <Tablespace_Name> Read Write;