For those of you using Oracle and Maximo 6 or above, backing up and restoring a Maximo schema can be a pain.  If you were using SQL Server, it would take only a few minutes, depending on the size of your database.  But with Oracle, you have to jump through a few more hoops to get it just right.  Here are the steps courtesy of IBM.

NOTE: You must use the same version of the export utility as the version of the source database, and you must use the same version of the import utility as the version of the target database. You must also import to a version of Oracle which is the same or higher version as the source database. For example, you must use export version 9.2.0.6 to export Maximo from a 9.2.0.6 version database. Then you must use import version 10.1.0.3 in order to import into a version 10.1.0.3 database.

WARNING: Before exporting the schema you should stop the Oracle Text index synchronization job so that you do not export text indexes which are in mid-update. If you do not stop this job, the export file will contain invalid text index data for some of these indexes which will cause errors in Maximo after importing. After the synchronization job has stopped, run the synchronization procedure one time by hand to ensure that the text indexes are updated:

exec maximo_ts_job();

Run the Oracle export utility according to the Oracle10g (or Oracle9i) Utilities Manual. Export only the Maximo schema (userid). The following is a sample export parameter file (for export run on Windows):

userid=system/systempassword@connectstring
file=C:\TEMP\Maximo.DMP
log=C:\TEMP\Maximo.LOG
owner=MAXIMO
rows=Y
compress=y
buffer=32767

Importing the Maximo Schema

Since the target instance must have the same tablespace names for any tables containing CLOB or BLOB datatypes, you can run the following statement in the source instance to list the tablespace names use by tables that contain BLOB or CLOB columns:

select distinct tablespace_name from user_tables where table_name in
(select table_name from user_tab_columns where data_type in ('BLOB','CLOB'))

Before importing the schema you must create the MAXIMO userid. If you are refreshing a test schema from production, you need to ensure that all the old objects have been dropped/deleted. The only way to guarantee that this has been done correctly is to drop the schema and then recreate it.

Run the following commands to create the Maximo schema in the target instance. This sequence of commands below is taken from CREATEMaximoES.SQL and is documented in the Maximo Installation Guide. If you use a schema name or tablespace name other than MAXIMO then the installation guide explains what to change in the script.

create user maximo identified by maximo;
alter user maximo default tablespace maximo quota unlimited on maximo;
alter user maximo temporary tablespace temp;
grant create trigger to maximo;
grant create session to maximo;
grant create sequence to maximo;
grant create synonym to maximo;
grant create table to maximo;
grant create view to maximo;
grant create procedure to maximo;
grant alter session to maximo;
grant execute on ctxsys.ctx_ddl to maximo;

You may need to give the schema owner quota on additional tablespaces. Copy and change the following command for each additional tablespace

alter user maximo quota unlimited on second_tablespace;

Starting with Maximo Patch 3 on Oracle 10g only, the schema will also need CREATE JOB privilege because the text index synchronization job will be run by DBMS_SCHEDULE instead of DBMS_JOB. (This privilege is not needed and does not exist on Oracle 9i.) On Oracle 10g also execute:

grant create job to maximo;

The schema export will not export the Oracle Text preference and sub-lexer definitions. You must login as MAXIMO with SQLPLUS and execute the following to set these up before you run the import.

call ctx_ddl.drop_preference('global_lexer');
call ctx_ddl.drop_preference('default_lexer');
call ctx_ddl.drop_preference('english_lexer');
call ctx_ddl.drop_preference('chinese_lexer');
call ctx_ddl.drop_preference('japanese_lexer');
call ctx_ddl.drop_preference('korean_lexer');
call ctx_ddl.drop_preference('german_lexer');
call ctx_ddl.drop_preference('dutch_lexer');
call ctx_ddl.drop_preference('swedish_lexer');
call ctx_ddl.drop_preference('french_lexer');
call ctx_ddl.drop_preference('italian_lexer');
call ctx_ddl.drop_preference('spanish_lexer');
call ctx_ddl.drop_preference('portu_lexer');
call ctx_ddl.create_preference('default_lexer','basic_lexer');
call ctx_ddl.create_preference('english_lexer','basic_lexer');
call ctx_ddl.create_preference('chinese_lexer','chinese_lexer');
call ctx_ddl.create_preference('japanese_lexer','japanese_lexer');
call ctx_ddl.create_preference('korean_lexer','korean_morph_lexer');
call ctx_ddl.create_preference('german_lexer','basic_lexer');
call ctx_ddl.create_preference('dutch_lexer','basic_lexer');
call ctx_ddl.create_preference('swedish_lexer','basic_lexer');
call ctx_ddl.create_preference('french_lexer','basic_lexer');
call ctx_ddl.create_preference('italian_lexer','basic_lexer');
call ctx_ddl.create_preference('spanish_lexer','basic_lexer');
call ctx_ddl.create_preference('portu_lexer','basic_lexer');
call ctx_ddl.create_preference('global_lexer', 'multi_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','default','default_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','english','english_lexer','en');
call ctx_ddl.add_sub_lexer('global_lexer','simplified chinese','chinese_lexer','zh');
call ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','korean','korean_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','de');
call ctx_ddl.add_sub_lexer('global_lexer','dutch','dutch_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','swedish','swedish_lexer','sv');
call ctx_ddl.add_sub_lexer('global_lexer','french','french_lexer','fr');
call ctx_ddl.add_sub_lexer('global_lexer','italian','italian_lexer','it');
call ctx_ddl.add_sub_lexer('global_lexer','spanish','spanish_lexer','es');
call ctx_ddl.add_sub_lexer('global_lexer','portuguese','portu_lexer',null);

NOTE: The above incorporates the korean_morph_lexer that is required for Oracle 10.2. This lexer is also available with Oracle 9.2 and 10.1, so the above commands can be used with all the supported versions of Oracle.

The following is a sample import parameter file. Note the use of FROMUSER/TOUSER syntax. Even if you have a full database export, do not perform a full database import. A full import will update the CTXSYS schema unnecessarily and then and cause problems with the korean_lexer.

userid=system/systempassword@connectstring
file=C:\TEMP\Maximo.DMP
log=C:\TEMP\MaximoIMP.LOG
fromuser=MAXIMO
touser=MAXIMO
rows=Y

If you import into the same instance from which you exported you will use a new Maximo schema name, and you may receive an error at the end of the import when creating the maximo_ts_job batch job.

If the import was successful, but the job which automatically updates the Oracle Text indexes has not been started. Please download Tech Note number 1262037 for an explanation of how to correct that. MAXIMO will run OK without this, but searches on description fields will not find all the records until this is fixed.

If you have imported any Maximo tables or indexes into different tablespaces than in the original database, you will need to update this information in MAXTABLE, MAXTABLECFG, and MAXSYINDEXES. Please see Knowledge Base document 1262981 for the SQL used to do this update.

Checking for Valid Text Indexes

After the import has completed, you may have on or more invalid text indexes. This can occur if a text index was being updated when the export was running. The following statement will list any invalid text indexes:

 select index_name,index_type,status,domidx_status,domidx_opstatus from user_indexes
where index_type = 'DOMAIN' and (domidx_status <> 'VALID' or domidx_opstatus <> 'VALID');

If you find any invalid indexes, you can fix them with the following command:

 alter index indexname rebuild;

If there are a large number of them, you can run SQLPLUS on Windows, login as the Maximo schema owner, and run the following to create and execute generate and run a script of rebuild commands:

set feedback off
set hea off
set pagesize 10000
spool C:\TEMP\REBUILDNDX.SQL
select 'ALTER INDEX '||index_name||' REBUILD;' from user_indexes where index_type = 'DOMAIN'
and (domidx_status <> 'VALID' or domidx_opstatus <> 'VALID');
spool off
@C:\TEMP\REBUILDNDX.SQL

This may run for a while, depending on table sizes and the number of indexes.  [IBM Support]