Thursday, July 29, 2010

Migrate Oracle Database from Enterprise Edition to Standard Edition

I have heard many DBA come across a situation where they are told to down grade the Oracle Database from Enterprise to Standard Edition. It might be due to cost cutting or some times it depends on the utilization that an organization takes from that db server.

So here I would like to share some of the basic steps that will help you in migrating your database(s).

- Oracle Standard Edition needs to be installed in different ORACLE_HOME.

- Take a cold/hot backup of your databases. (recommend to go for COLD backup)

- Shutdown database the database currently in use by 'shutdown immediate' or 'shutdown normal'

- Start setting up your new ORACLE_HOME, LIBPATH, LD_LIBRARY_PATH, SHLIB_PATH, TNS_ADMIN parameter and any other if required. Make sure that all this parameter is set to the new installation made (In our example it will be Standard Edition)

- If you are using _SYSTEM_TRIG_ENABLED OR JOB_QUEUE_PROCESS then request you to turn off before moving ahead.

- Start database under new Oracle Home.

Now if you observe properly you will find that the database will be running in Oracle Standard Edition but all the dictionary tables and packages are still of Enterprise Edition. So we need to migrate these objects to Standard Edition.

Hope you remember the step that we do when we do a upgrade. Yes!! Execution of cataproc and catlog sql files.

- Execute the below commands as SYSDBA

Command Prompt>sqlplus /nolog
SQL>connect internalOR
SQL>connect /as sysdba

These scripts will re-create all the system and sys objects used by Oracle for Standard Edition.

- For RMAN Catalog upgrade we need to execute the below rman commands.
Command Prompt>rman
RMAN>connect target
RMAN>upgrade catalog;

- Shutdown the database and restart it.
- Post Migration we need to check if all the RMAN and other shell scripts are working properly because we might have entered the complete path of Oracle Home.

- As we are aware that Oracle Standard Edition supports 1 RMAN Channel so we need to make the changes to the backup and restore script as well by allocating 1 channel.

Please make sure that we update all the scripts correctly and don't forget to share your comments for this article.

1 comment:

  1. Hi there,

    this is an unsupported method, right?