Upgrading Oracle 10g XE to Oracle 11g XE

Task:

The task is to upgrade Oracle 10g XE to 11g XE to support our growing application. The application tracks patient information for a government agency. The growing data has been causing performance issue for the present 10g XE. So, management decided to upgrade for 3 reasons, namely:

1. Government requirement that databases for any of its agencies must be operating in 11g or above;
2. For better performance as 10g XE is already dragging due to the explosion of the data; and
3. Oracle will stop supporting 10g XE, so it makes sense to upgrade.

Hardware:

Operating system is Windows XP while database is Oracle 10g XE. Database has 2 main schemas (patient & Doctor). These schemas must be migrated to the new database (11g XE) with all the database objects intact.


Step-by-step guide to Upgrade
 

1. Export schemas

exp   system/*****   owner=patient    file=PATIENT.IMP

exp   system/*****   owner=doctor   file=DOCTOR.IMP

 

2. Uninstall ORACLE XE 10g

go to "control Panel"

uninstall Oraclexe 10g

Delete folder c:\oraclexe\.....\BIN  (If this was not deleted by the uninstall operation)

 

3. INSTALL ORACLE 11g

Click on "Next" to start the installation



Accept the Licensing agreement

 

Enter password for SYS and SYSTEM users



Choose the location of the binary files. Use "browse" to control location



Let the installer finish installation

 

 

4. Create tablespaces for the user accounts

create tablespace doctor

datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\DOCTOR01.DBF' size 500m autoextend on next 50m maxsize unlimited;

create tablespace patient

datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\PATIENT01.DBF' size 500m autoextend on next 50m maxsize unlimited;

create temporary tablespace doctortemp

tempfile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\DOCTORTEMP01.DBF' size 100m autoextend on next 10m maxsize unlimited;

create temporary tablespace patienttemp

tempfile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\PATIENTTEMP01.DBF' size 100m autoextend on next 10m maxsize unlimited;

commit;

5. Create user accounts 

create user doctor identified by "oracle23678"

default tablespace doctor
temporary tablespace doctortemp
quota unlimited on doctor;

create user patient identified by "oracle23679"

default tablespace patient
temporary tablespace patienttemp
quota unlimited on patient;
commit;

6. Grant privileges to user accounts  

grant create session to patient;

grant create any table to patient;
grant create any sequence to patient;
grant create any procedure to patient;
grant create any synonym to patient;
grant create any index to patient;
grant create any trigger to patient;

grant create session to doctor;

grant create any table to doctor;
grant create any sequence to doctor;
grant create any procedure to doctor;
grant create any synonym to doctor;
grant create any index to doctor;
grant create any trigger to doctor;

commit;

 

7. Change password settings

 

You may need to set password parameter since Oracle 10g XE is not case sensitive. Please note that this stage is optional in the upgrade process.
As SYS, run this command:

alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;

commit;

 

8. Create user accounts 

Import schema into Oracle 11g XE by using the import command.

imp system/***** file=patient.dmp fromuser=patient touser=patient

imp system/***** file=doctor.dmp fromuser=doctor touser=doctor

 

Once the import is complete, check if the schema objects are correctly imported from the dumpfile. You will then need to take backup of your Oracle 11g XE database using RMAN or Export.

Thanks for reading.