Upgrade Oracle from Standard Edition to Enterprise Edition

Procedure for migration Oracle from standard edition to enterprise edition

For unix platform:

1. Take the cold backup of database and Oracle Home.

2. Spool output of following queries:

select * from v$version
select * from v$datafile
select * from v$tablespace
select * from dba_objects where status='INVALID';
alter database backup controlfile to trace as '\u01\controlfile.txt';

3. Take backup of listener.ora  tnsnames.ora   intifile.ora
copy network folder from dbhome.

4. Stop current listener.

lsnrctl stop

5. Shut down the database.

Shutdown immediate;

6. Install new Enterprise Oracle home of same version.

7. Copy all files(Listener.ora, tnsnames.ora, intifile.ora and initsid.ora) into new ORACLE_HOME (Enterprise Edition)

8. change in .profile for ORACLE_HOME
From $ORACLE_HOME/bin   relink all  — for tar extraction only

9. Startup the upgrade database and run the catalog or catproc scripts

sqlplus / as sysdba
sql> STARTUP UPGRADE;
sql> @$ORACLE_HOME/rdbms/admin/catalog.sql
sql> @$ORACLE_HOME/rdbms/admin/catproc.sql
sql> shutdown immediate
sql> startup
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql

10. Check the object status:

select * from dba_objects where status='INVALID';

11. Start the listener from new oracle home:

lsnrctl start

12. Check for connection from local:

sqlplus sys@dbname as sysdba

Steps for windows platform from SE to EE
1. Take backup of database and Oracle home.

2. Spool the following sql output:

select * from v$version
select * from v$datafile
select * from v$tablespace
select * from dba_objects where status='INVALID'
alter database backup controlfile to trace as 'D:\oracle\ctl.txt';

3. Take backup of listener.ora,tnsnames.ora ,intifile.ora and sqlnet.ora file

4. Stop the current listener and Oracle database
Stop all oracle services from services.msc (including listener)

5. Install the New enterprise edition software

6. Copy all files(Listener.ora, tnsnames.ora, intifile.ora and initsid.ora) into new ORACLE_HOME (Enterprise Edition)

7. Drop services for oracle instance with oradim command:

oradim -delete -sid test -pfile "D:\oracle\product\10.2.0\db_1\database\inittest.ora"
oradim -new -sid test -pfile "D:\oracle\product\10.2.0\db_1\database\inittest.ora"  (logoff and logon if required)

8. Change ORACLE_HOME in environment variable

Logoff and login
sqlplus / as sysdba
sql> STARTUP UPGRADE;
sql> @$ORACLE_HOME/rdbms/admin/catalog.sql
sql> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> shutdown immediate
SQL> startup
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

9. Verify the invalid objects:

select * from dba_objects where status 'VALID'

10. Create and start listener from new oracle home with netca utility.

lsnrctl start

11. Delete old listener with netca from old home.

12. Check for connection from local PC.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s