Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1

Main “steps” documents id:

362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1: Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07

Other doc_id: 881505.1, 1367654.1

Troubleshooting:

Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]



Contents

Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1

Before the Database Installation and Upgrade. 3

I.       Interoperability “Pre”: 3

Database Upgrade: 4

II.      Pre-Export the database: 4

III.         Pre-Import the database: 6

IV-         Export the database: 9

V-     Import the database: 10

IV-         Post-Import and database configuration: 11

Before the Database Installation and Upgrade

       I.            Interoperability “Pre”:

From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)

1)   Verify S/W and system’s versions:

a)      Ebs 11.5.10.2

b)      ATG Rollup Patch 6

c)       Developer 6i Patch set 18

d)      AutoConfig Latest version with 9835302

2)   Nothing to do

3)   Nothing to do

4)   Deregister the current database server

To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:

$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl

appspass=[APPS db user password]

contextfile=$CONTEXT_FILE -removeserver

5)   Update application tier context file with new database listener port number

If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.

s_dbhost New database hostname
s_dbdomain New database domain name
s_db_serv_sid New database SID
s_dbport New database listener port
s_apps_jdbc_connect_descriptor NULL
6)   Upgrade the database instance

362205.1:   10g Release 2 Export/Import Process for Oracle Applications Release 11i

557738.1:   Export/import notes on Applications 11i Database 11g

1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)

165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i

Oracle Database Upgrade Guide 11g Release 2 (11.2)  part no. E23633-07

According to previous documents the upgrade will be using import/export method.

Database Upgrade:

    II.            Pre-Export the database:

From doc_id: 362205.1 -> Section1: Prepare the source system

From doc_id: 557738.1 -> Section1: Prepare the source system

7)   Apply the Applications consolidated export/import utility patch

Apply both patches for exporting 10 (4872830) and 11g (1235353).

8)   Apply latest Applications database preparation scripts patch

Apply Patch 7225862 to every application tier server node in the source system.

9)   Create a working directory on source database node

Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

10)       Generate target database instance creation script adcrdb.sql (10g),  aucrdb.sql (11g)

11g $sqlplus system/manager  @$AU_TOP/patch/115/sql/auclondb.sql 11

10g $sqlplus system/manager  @$AU_TOP/patch/115/sql/adclondb.sql

Then edit this script to reflect the new environment.

“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.

Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.

11)       Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql in the current directory.

12)       Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type=’DOMAIN’ and

upper (parameters) like ‘%REBUILD%’;

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters (‘parameters’)

Where [parameters] is the original parameter set without the rebuild_index parameter.

13)       Synchronize Text indexes

$ sqlplus ‘/ as sysdba’

SQL> select pnd_index_owner,pnd_index_name,count(*)

from ctxsys.ctx_pending

group by pnd_index_owner,pnd_index_name;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]‘);

14)       Drop SYS.ENABLED$INDEXES (conditional)

SQL> drop table sys.enabled$indexes;

15)       Shut down Applications server processes and database listener



 III.            Pre-Import the database:

16)       Prepare to create the 11.2.0 Oracle home

The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home

a) Finish OS prerequisites for new Oracle home “new host or exist host”.

b) Oracle OS owner profile option and environment variables.

17)       Install the base 11.2.0 software: install  software only

18)       Install Oracle Database 11g Products from the 11g Examples CD

19)       Create nls/data/9idata directory

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

20)       Apply additional 11.2.0.1 RDBMS patches  “9 patches using opatch”

Append Opatch tool to PATH,

$export PATH=$PATH:$ORACLE_HOME/OPatch

and run the following patches:

8570322

8685327

8761974

8771297

12672969-> instead of 8772028

8796511

8897784

8964142

8405205

21)       Create working directory on target database server.

Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,

$ mkdir /u01/expimp

22)       Copy scripts needed

Copy the following from Source environment “database or application servers” to the working directory on target database:

        I.            Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
      II.            Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
    III.            Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
    IV.            Copy auque2.sql generated file on step 18 from source database server working directory.
      V.            Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
    VI.            Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
  VII.            Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
Create appsutil.zip file
perl /bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

Then copy it to new ORACLE_HOME then

unzip -o appsutil.zip

23)       Create the target database instance

Use modified adcrdb.sql script, generated from step 17

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool adcrdb.log;

SQL> startup nomount;

SQL> @adcrdb_db.sql

SQL> @adcrdb_tbs.sql

SQL> @ adpostcrdb.sql

SQL> exit;

24)     Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.

$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql

25)     Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system

$ sqlplus system/manager @/u01/expimp/ausy1120.sql

26)     Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.

$ sqlplus system/manager @/u01/expimp/aujv1120.sql

27)     Install other required components

the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.

$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

28)     Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

29)     Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

30)       Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.

IV-             Export the database:

31)       Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

filesize=1G

full=y

logfile=full_expdpapps.log

32)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

33)       Shut down Applications server processes

34)       Grant privilege to source system schema

$ sqlplus / as sysdba

SQL> grant EXEMPT ACCESS POLICY to system;

35)            Export the Applications database instance

If the source database is 10.2.0 or 11.1.0, use the following command:

$ expdp system/[system password] \

parfile=[export parameter file name]

If the source database is 11.2.0, use the following command:

$ expdp “‘/ as sysdba’” parfile=[export parameter file name]

36)       Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

V-                  Import the database:

37)       Create the import parameter file

Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:

Remove the exclude parameters.
Remove the filesize parameter.
Change the name of the log file.
Uncomment the transform parameter.
It will be like this:

directory=DUMP_DIR

dumpfile=full_exp%U.dmp

full=y

transform=oid:n

logfile=impdpapps.log

38)       Create export directory

$ sqlplus system/manager

SQL> create directory DUMP_DIR as ‘/u01/expimp’;

39)       Copy the export dump files from source to target

40)       Set Oracle Text parameter

if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);

41)            Import the Applications database instance

Start the import session on the target database server node using the customized import parameter file.

If the target database is 11.1.0, use the following command:

$ impdp system/manager parfile=[import parameter file name]

If the target database is 11.2.0, use the following command:

$ impdp “‘/ as sysdba’” parfile=[import parameter file name]

42)       Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> revoke EXEMPT ACCESS POLICY from system;

IV-             Post-Import and database configuration:

From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]

And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]

43)       Reset Advanced Queues

Run file created in step:11.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

44)       Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.

45)       Run adgrants.sql

$ sqlplus “/ as sysdba” @adgrants.sql APPS

46)       Grant create procedure privilege on CTXSYS

$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS

47)       Set CTXSYS parameter

$ sqlplus “/ as sysdba”

SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);

48)       Deregister the current database server

$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

49)       Some errors will be shown in the autoconfig

To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps
Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]

enable applsys.wf_java_defered queue


some of grants may be lost so run the following list:
Compile the invalid objects.
$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql

50)       Generate your Database Context File

Ensure that shell reflect the oracle_home and bin.

From extracted appsutil.zip

cd /appsutil/bin
perl adbldxml.pl tier=db appsuser=apps

answer the question to generate XML file.

51)       Generate and Apply AutoConfig Configuration files in DB tier.

Execute the following commands:

cd /appsutil/bin
adconfig.sh contextfile=XML file created in step 50

52)       Re-create custom database links
if the Oracle Net listener in the 11.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:

$ sqlplus apps/[apps password]

SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:

$ sqlplus apps/[apps password]

SQL> drop database link [custom database link];

SQL> create database link [custom database link] connect to

[user] identified by [password] using

‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])

(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))’;

where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect

the new Oracle Net listener for the database instance.

Drop database link “PRODUCTION.HQ.ASQ.COM”;

create database link “PRODUCTION.HQ.ASQ.COM”

using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;

Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;

create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;

create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;

create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;

create database link “APPS_TO_APPS.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;

create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “APPS_TO_APPS.LOCAL”;

create database link “APPS_TO_APPS.LOCAL” using ’192.168.1.245:1521/UPGRD’;

Drop database link “EDW_APPS_TO_WH.LOCAL”;

create database link “EDW_APPS_TO_WH.LOCAL” using ’192.168.1.245:1521/UPGRD’;

53)       Generate and Apply AutoConfig Configuration files in APPS tier.

54)       Apply Oracle Human Resources (HRMS) patch 7721754

55)       Use adadmin to:

Compile invalid object
Re-create grants and synonyms using adadmin
Compile flexfield

DBA interview questions

1. How can I Export 9i data in 8i ? I mean when I Export from 9i and Import in 8i, I face errors. what sould i do?
2. How do you verify and validate the Datafile which is backed up?
3. how much time will it take a hotbackup if the batabase size is 60Gb?
4. What is mean live dead lock? How to prevent it.?
5. what is the difference between recovery and restoring of the database?
6. What are the different tools available for hot backups?
7. what is the difference between media recovery and crash recovery..?

Media recovery is a process to recover database from backup when physical disk failure occur.
Cash recovery is a automated process take care by oracle when instance failure occur.

8. what do you mean by MEDIA RECOVERY..?

Media recovery is a process of restoring the physical backups and making all the 
restored datafiles consistent with each other by having same SCN’s in their header’s.


9. what is the disk migration?what isthe steps involved in diskmigration?
10. What are the steps involved in Database Startup ?
11. What are the steps involved in Database Shutdown?
12. What is Restricted Mode of Instance Startup ?
13. What are the different modes of mounting a Database with the Parallel Server ?
14. What is Full Backup ?
15. Can Full Backup be performed when the database is open ?
16. What is Partial Backup ?
17. What is On-line Redo Log?
18. What is Mirrored on-line Redo Log ?
19. What is Archived Redo Log ?
20. What are the advantages of operating a database in ARCHIVELOG mode over operating it
21. What are the steps involved in Instance Recovery ?

Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;

Oracle: .profile sample file for oracle environment

ORACLE_BASE=/fmstest;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/ora1024;export ORACLE_HOME
export CLASSPATH=$ORACLE_BASE/jre/1.1.8,$ORACLE_HOME/product/jlib
export  LD_RUN_PATH=$ORACLE_HOME/lib64:/usr/lib:/lib
export AIXTHREAD_SCOPE=S
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:$ORACLE_HOME/perl/bin
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1;export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;export ORA_NLS33
ORACLE_SID=wrklght;export ORACLE_SID
export LIBPATH=$ORACLE_HOME/lib
set -o vi
export ORACLE_LIB=$ORACLE_HOME/lib:/usr/lib:/lib:/usr/ucblib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/lib
export PS1="`hostname`:"'$PWD>'
echo "################################################"  >> .sh_history
who am i | awk ' { print $6 }' >> .sh_history
echo "################################################"   >> .sh_history
######################################################################

Oracle: Configure the .oraenv file

1. If you are creating a database manually you have to put the entry in ORATAB file.
If you are creating database using DBCA then the database entry will automatically
filed in oratab file.
2- Location of oratab file is /etc/oratab

3. Then edit the oratab file. It will look like below:
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
*:/u01/app/oracle/product/9.2.0:N
TSH1:/u01/app/oracle/product/9.2.0:N
juli:/u01/app/oracle/product/9.2.0:N
orcl:/u01/app/oracle/product/10.2.0/db_1:N
+ASM:/u01/app/oracle/product/10.2.0/db_1:N
rmanrep:/u01/app/oracle/product/10.2.0/db_1:N
mom:/u01/app/oracle/product/10.2.0/db_1:N
preetha:/u01/app/oracle/product/10.2.0/db_1:N

4. Then run the file: . .bash_profile or .profile
5. Then run . oraenv
6. Then enter the name of the database(instance name) you wanna open
7. This is all about running a oraenv file

ORA-00018 maximum number of sessions exceeded

ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

Reference: Oracle Documentation

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated 

by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba
 sqlplus / as sysdba
 
2. Check Current Setting of Parameters
 sql> show parameter sessions
 sql> show parameter processes
 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase 
"processes and "transactions" parameters.
A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1
  
4. These paramters can't be modified in memory. You have to modify the spfile only 
(scope=spfile) and bounce the instance.
 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup 

Creating an oracle database 10g manually

Creating oracle database 10g manually

We supposed Oracle already installed(i.e. ORACLE_HOME=/fmstest/fmsclone/fmsclonedb) and
we are creating another database on the same server:
1- Create the required directories:
mkdir -p /fmstest/AEMEDB
mkdir -p /fmstest/AEMEDB/datafiles
mkdir -p /fmstest/AEMEDB/admin/bdump
mkdir -p /fmstest/AEMEDB/admin/cdump
mkdir -p /fmstest/AEMEDB/admin/udump
export ORACLE_HOME=/fmstest/fmsclone/fmsclonedb
export ORACLE_BASE=/fmstest
export ORACLE_SID=AEMEDB

2- Create initial initialization file
Oracle states that it has greatly reduced the number of necessary initialization parameters
in Oracle 10g. This seems to be true. To create the database, I used that minimalistic file as:

vi $ORACLE_HOME/dbs/initAEMEDB.ora

*.user_dump_dest='/fmstest/AEMEDB/admin/udump'
*.core_dump_dest='/fmstest/AEMEDB/admin/cdump'
*.background_dump_dest='/fmstest/AEMEDB/admin/bdump'
*.compatible='10.0.0'
*.control_files=('/fmstest/AEMEDB/datafiles/control01.ctl','/fmstest/AEMEDB/datafiles/control02.ctl')
*.db_block_size=8192
*.sga_target=512M
*.db_domain='LOCAL'
*.db_file_multiblock_read_count=16
*.db_name='AEMEDB'
*.fast_start_mttr_target=600
*.instance_name='AEMEDB'
*.job_queue_processes=2
*.workarea_size_policy='AUTO'
*.pga_aggregate_target=25M
*.remote_login_passwordfile='EXCLUSIVE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=3600


3- Create the database:

sqlplus / as sysdba

startup nomount;

create spfile from pfile;

CREATE DATABASE AEMEDB
MAXINSTANCES 1
MAXLOGHISTORY 292
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/fmstest/AEMEDB/datafiles/system01.dbf'
SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/fmstest/AEMEDB/datafiles/sysaux01.dbf'
SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/fmstest/AEMEDB/datafiles/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/fmstest/AEMEDB/datafiles/undo01.dbf'
SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 '/fmstest/AEMEDB/datafiles/redo01.log'  SIZE 50M,
  GROUP 2 '/fmstest/AEMEDB/datafiles/redo02.log'  SIZE 50M,
  GROUP 3 '/fmstest/AEMEDB/datafiles/redo03.log'  SIZE 50M
USER SYS IDENTIFIED BY sys
USER system IDENTIFIED BY sys
/

@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/owminst.plb;
connect SYSTEM/sys
@/oracle/product/10.1.0.2/sqlplus/admin/pupbld.sql;
connect SYSTEM/sys
@?/sqlplus/admin/help/hlpbld.sql helpus.sql;

4- Create a custom tablespaces:
CREATE TABLESPACE ICMLFQ32 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLFQ32.dbf' SIZE 2633M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMLNF32 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLNF32.dbf' SIZE 25M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMLSNDX DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLSNDX.dbf' SIZE 286464K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMSFQ04 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMSFQ04.dbf' SIZE 150M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMVFQ04 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMVFQ04.dbf' SIZE 25M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE SYSAUX DATAFILE
  '/fmstest/AEMEDB/datafiles/sysaux.dbf' SIZE 400M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/


CREATE TABLESPACE SYSTEM DATAFILE
  '/fmstest/AEMEDB/datafiles/system01.dbf' SIZE 325M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
FLASHBACK ON
/


CREATE TABLESPACE TBS_1 DATAFILE
  '/fmstest/AEMEDB/datafiles/users.dbf' SIZE 443072K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE
  '/fmstest/AEMEDB/datafiles/temp_tbs.dbf' SIZE 120M AUTOEXTEND ON NEXT 100M MAXSIZE 8192M
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/


CREATE UNDO TABLESPACE UNDOTBS DATAFILE
  '/fmstest/AEMEDB/datafiles/undo01.dbf' SIZE 748M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON
/


CREATE TABLESPACE USERS DATAFILE
  '/fmstest/AEMEDB/datafiles/users01.dbf' SIZE 1324M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/

5- Create the listner:
vi $TNS_ADMIN/listener.ora

AEMEDB =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCAEMEDB))
        (ADDRESS= (PROTOCOL= TCP)(Host= bahix08.aemeweb.com )(Port= 1544))
  )
SID_LIST_AEMEDB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /fmstest/fmsclone/fmsclonedb)
      (SID_NAME = AEMEDB)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /fmstest/fmsclone/fmsclonedb)
      (PROGRAM = extproc)
    )
  )