Database 8.21 - 8.3 Migration for Oracle XE

Here are instructions how you can migrate database from version 8.21 to version 8.3. It isn't usual database upgrade procedure, because you only need to run upgrade script prm_db_patch_8.3.0.sql and upgrade will be performed. Here we are talking about different matter. Database 8.21 and 8.3 architecture differs a lot. Version 8.21 has over 40 tablespaces in it and version 8.3 have only 4. This isn't important for Oracle 10g Enterprise version, because that Oracle version doesn't have any kind of software/hardware. However Oracle 10g Express version has a dozen limitations and one of them is database size which is limited to 4GB. Because of this limitation it could be very important to remove unused tablespaces and move all objects into few new tablespaces. This approach we begun to use from version 8.3, because it is much more easier to handle and administer less tablespaces, they consume less spaces on harddisk (this is the main problem with Oracle XE) etc. There are a number of advantages.
Here we have a list of actions which need to be performed in order to move all database objects into new tablespaces and delete old ones.

• Export data from database
• Create new tablespace for data (we will need to create one more for indexes, but it is possible in Oracle XE that we can't do it because of database size limitation)
• Add privileges to pnet user for new tablespace
• Create SQL scripts for tables replacement.
• Replace tables into new tablespace
• Delete all tablespaces used by tables
• Create SQL scripts for indexes replacement.
• Add privileges to pnet user for new tablespace
• Replace indexes into new tablespace
• Delete all tablespaces used by indexes
• Perform database upgrade script.

Export data from database

First and most important step is to backup your data. Migrating the database is very sensitive operation and requires a lot of steps that can lead to data loss if not performed properly. Therefore, it is critical that you have a back-up if you need to restore the data.

To back up your database on Oracle Enterprise you need to open console windows and to run following two commands:

exp userid=system/<system password>@<database name> file=pnet_dump_file_name.dmp owner=pnet
exp userid=system/<system password>@<database name> file=pnet_user_dump_file_name.dmp owner=pnet_user

Usernames used for those two commands are PNET/PNET_USER database users. You need to place proper passwords as well as database name.

Create new tablespace for tables

First action is to create tablespace for tables. For example:

CREATE TABLESPACE data01 
DATAFILE 'ABSOLUTE_PATH\data01.dbf' SIZE 300M
AUTOEXTEND ON MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ABSOLUTE_PATH represents absolute path to the datafiles location for the current database. For example, for Windows operating system and database named TEST this location will be: %ORACLE_HOME%\product\10.2.0\oradata\test etc. It is the same for Linux environment. You just need to take care about path separators ("\" for Windows and "/" for Linux). You need to do this as database admin user (SYSTEM, SYS).

Add privileges to pnet user for new tablespace

After new tablespace creation it is necessary to modify pnet/pnet_user users default tablespace and to add privileges for them.

alter user pnet
default tablespace data01
quota unlimited on data01
/

alter user pnet_user
default tablespace data01
quota unlimited on data01
/ 

You need to do this as database admin user (SYSTEM, SYS).

Create SQL scripts for tables replacement

Now it is necessary to create script for tables replacement. But before you run this script you should purge all objects from recyclebin in the following way. Login as PNET database user in SQL*Plus and execute fo9llowing command:

purge recyclebin
/

Script for tables replacement is as follows:

set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set linesize 200
set verify off
set feedback off
set echo off

spool c:\move_tables.sql

 select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'PROMPT Altering '||segment_type || ' ' || segment_name ||chr(10)||
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
      ' tablespace ' ||decode( segment_type, 'TABLE', 'DATA01', 'INDEX01')|| chr(10) ||
      ' storage ( initial ' || initial_extent || 
      decode(next_extent, null, '', ' next ' ||  next_extent )|| chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' || max_extents || chr(10) ||
      decode(pct_increase, null, '', ' pctincrease ' ||  pct_increase )|| 
      decode(freelists, null, '', ' freelists ' ||  freelists )||');'
 from user_segments,
       (select table_name, index_name from user_indexes )
 where segment_type in ( 'TABLE') and
       segment_name = index_name (+) and
       upper(tablespace_name) not in ('DATA01', 'INDEX01', 'FORMS_DATA', 'FORMS_INDEX')
 order by 1, 2
/
spool off
set heading on
set verify on
set feedback on
set echo on

Depending of operating system you need to modify this line

spool c:\move_tables.sql

and to add appropriate path where you want generated script to be placed. This script generates SQL script like this:

alter TABLE CLIENT_DATABASE_VERSION move
tablespace data01
storage ( initial 106496 next 106496
minextents 1 maxextents 2147483645
pctincrease 0 freelists );

alter TABLE DATABASE_VERSION move
tablespace data01
storage ( initial 106496 next 106496
minextents 1 maxextents 2147483645
pctincrease 0 freelists );

alter TABLE DATABASE_VERSION_UPDATE move
tablespace data01
storage ( initial 106496 next 106496
minextents 1 maxextents 2147483645
pctincrease 0 freelists ); 

alter TABLE HELP_FEEDBACK move
tablespace data01 
storage ( initial 106496 next 106496
minextents 1 maxextents 2147483645
pctincrease 0 freelists );  

...

It is important that you execute this script as pnet user. One also important thing is to make sure that all lines in one SQL statement are in place and there are no empty lines between them, otherwise error will occur during the SQL script execution.

Replace tables into new tablespace

In order to replace all tables from existing tablespaces into new tablespace you need to run SQL script you just created in previous step. You need to login in SQL*Plus as pnet user and then to execute SQL script in the following way:

SQL> @ABSOLUTE_PATH\move_tables.sql

ABSOLUTE_PATH is path to the move_tables.sql SQL script.

Drop unused table tablespaces

In this step you should delete all tablespaces where there are no database objects like tables or indexes. With this step you should make enough space to create the other tablespace for indexes. So now we should delete only empty tablespaces. We will achieve this with the following script.

set echo off
set heading off
set linesize 200
set verify off
set feedback off
set echo off

spool c:\drop_empty_table_tablespaces.sql

select 'DROP TABLESPACE '||tablespace_name||' INCLUDING CONTENTS AND DATAFILES'||chr(10)||'/' 
from user_tablespaces
where tablespace_name not in (select distinct tablespace_name 
                             from user_indexes 
                             where tablespace_name like '%DATA') and
      tablespace_name not like '%INDEX'
order by tablespace_name
/

spool off
set heading on
set verify on
set feedback on
set echo on

Of course like in previous step you should check file path where we will create "drop tablespace" SQL statements, so modify next line

spool c:\drop_empty_table_tablespaces.sql

with the value you need. You should execute this script as pnet database user. When script is created you have to execute it as SYSTEM/SYS user in the following way:

SQL> @ABSOLUTE_PATH\drop_empty_table_tablespaces.sql

where ABSOLUTE_PATH is path to the drop_empty_table_tablespaces.sql script.

Create tablespace for indexes

When you cleaned up a bit database space you can create new tablespace for indexes. You need to do this as SYSTEM/SYS database user. The following script will create new tablespace for indexes:

CREATE tablespace index01
DATAFILE 'ABSOLUTE_PATH\index01.dbf' SIZE 300M
AUTOEXTEND ON MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ABSOLUTE_PATH represents absolute path to the datafiles location for the current database. For example, for Windows operating system and database named TEST this location will be: %ORACLE_HOME%\product\10.2.0\oradata\test etc. It is the same for Linux environment. You just need to take care about path separators ("\" for Windows and "/" for Linux). You need to do this as database admin user (SYSTEM, SYS).

Add privileges for new tablespace

For new tablespace you need to add appropriate tablespace privileges to PNET/PNET_USER database users with a following script:

alter user pnet
quota unlimited on index01
/

alter user pnet_user
quota unlimited on index01
/ 

You need to do this as SYSTEM/SYS database user.

Create replacement script for indexes

Now it is necessary to create script for indexes replacement. But before you run this script you should purge all objects from recyclebin in the following way. Login as PNET database user in SQL*Plus and execute fo9llowing command:

purge recyclebin
/

After that you should execute similar script as previous for tables:

set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set linesize 200
set verify off
set feedback off
set echo off

spool c:\move_indexes.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
       decode( segment_type, 'TABLE', 1, 2 ) order_col2,
      'PROMPT Altering '||segment_type || ' ' || segment_name ||chr(10)||
      'alter ' || segment_type || ' ' || segment_name ||
      decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
      ' tablespace ' ||decode( segment_type, 'TABLE', 'DATA01', 'INDEX01')|| chr(10) ||
      ' storage ( initial ' || initial_extent || 
      decode(next_extent, null, '', ' next ' ||  next_extent )|| chr(10) ||
      ' minextents ' || min_extents || ' maxextents ' || max_extents || chr(10) ||
      decode(pct_increase, null, '', ' pctincrease ' ||  pct_increase )|| 
      decode(freelists, null, '', ' freelists ' ||  freelists )||');'
  from user_segments,
       (select table_name, index_name from user_indexes )
 where segment_type = 'INDEX' and
       segment_name = index_name (+) and
       upper(tablespace_name) not in ('DATA01', 'INDEX01', 'FORMS_DATA', 'FORMS_INDEX')
 order by 1, 2
/

spool off
set heading on
set verify on
set feedback on
set echo on

Of course like in previous step you should check file path where SQL file will be created, so modify next line

spool c:\move_indexes.sql

and place right path.

Replace indexes into new tablespace

In order to replace all indexes from existing tablespaces into new tablespace you need to run SQL script you just created in previous step. You need to login in SQL*Plus as PNET user and then to execute SQL script in the following way:

SQL> @ABSOLUTE_PATH\move_indexes.sql

ABSOLUTE_PATH is path to the move_indexes.sql SQL script.

Drop unused tablespaces

In this step you should delete all tablespaces where there are no database objects like tables or indexes. The following script needs to be executed with PNET database user account:

set echo off
set heading off
set linesize 200
set verify off
set feedback off
set echo off

spool c:\drop_empty_tablespaces.sql

select 'DROP TABLESPACE '||tablespace_name||' INCLUDING CONTENTS AND DATAFILES'||chr(10)||'/' 
from user_tablespaces
where tablespace_name not in ('DATA01', 'INDEX01', 'FORMS_DATA', 'FORMS_INDEX')
order by tablespace_name
/

spool off
set heading on
set verify on
set feedback on
set echo on

Of course like in previous step you should check file path where we will create "drop tablespace" SQL statements, so modify next line

spool c:\drop_empty_tablespaces.sql

After creating this SQL script you meed to execute it as SYSTEM/SYS database user in the following way:

SQL> @ABSOLUTE_PATH\drop_empty_tablespaces.sql

ABSOLUTE_PATH is path to the drop_empty_tablespaces.sql SQL script.

Perform database version upgrade

Before performing database version upgrade script we need to gant few user privileges to PNET/PNET_USER users. We'll do that with following SQL sripts. It is necessary to execute them as SYSTEM/SYS database user:

grant drop public synonym to pnet 
/

grant create any synonym to pnet_user
/

Now, exit the SQL*Plus and open console window. Goto this folder: /8_3_branch/core/db/oracle/create-scripts and login in SQL*Plus from console in the following way:

sqlplus pnet/pnet_user_password@database_name

After that you need to execute upgrade script in the following way:

@versions\8.3.0\prm_db_patch_8.3.0.sql pnet_user pnet_user_password database_name

In previous SQL commands only PNET/PNET_USER are considered as default usernames. You should place appropriate passwords for those users as well as database name.