Create database (10.2.0.4) for moving an 11i instance

This task is part of moving an EBS 11i instance from outside hosting Linux servers to in-house servers. There are certain steps for creating a new database for using datapump Export / Import to move an Oracle EBS (11i) database.

1. Install 10gR2 software (note 362203.1)
- Install the base 10.2.0.1 software
- Install 10g products from the 10g Companion CD (Guide B19079-01, p. 3-6)
- Perform 10.2.0.4 patch set (6810189) installation
- Create nls/data/9idata directory by a Perl script

2. Download two EBS 11i patches (note 362205.1)
- 4873830. It includes a script for generating the SQL of creating the target database and tablespaces.
- 7225862. It provides necessary SQL scripts for target database preparation.

3. Get the init file from the source database server
- Comment out UNDO_TABLESPACES and UNDO_MANAGEMENT
- Do not use below PLSQL parameters (so they will be the defaults)
PLSQL_V2_COMPATIBILITY
PLSQL_CODE_TYPE
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

4. Modify adcrdb.sql to meet the file system layout. Below sample does not include all tablespace statements.

CREATE DATABASE
MAXDATAFILES 1301
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 (
'/u03/oracle/oradata/dpulti/log01a.log' , '/u02/oracle/oradata/dpulti/log01b.log'
) SIZE 300M ,
GROUP 2 (
'/u03/oracle/oradata/dpulti/log02a.log' , '/u02/oracle/oradata/dpulti/log02b.log'
) SIZE 300M ,
GROUP 3 (
'/u03/oracle/oradata/dpulti/log03a.log' , '/u02/oracle/oradata/dpulti/log03b.log'
) SIZE 300M ,
GROUP 4 (
'/u03/oracle/oradata/dpulti/log04a.log' , '/u02/oracle/oradata/dpulti/log04b.log'
) SIZE 300M
DATAFILE
'/u03/oracle/path/to/system01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M,
'/u03/oracle/path/to/system02.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
SYSAUX DATAFILE
'/u03/oracle/path/to/sysaux01.dbf' SIZE 1800M AUTOEXTEND ON NEXT 1024K MAXSIZE 32767M
/

5. Restart the new database after un-commenting out UNDO_TABLESPACES and UNDO_MANAGEMENT

6. To insure the database will have all necessary components for EBS 11i apps, run a series of SQL scripts detailed in 362205.1
- if get issue on adpostcrdb.sql, check note 236583.1

7. Make sure temp tablespace has temp datafiles.
8. Run @?/rdbms/admin/utlrp.sql to compile any invalid objects. Here are the counts by each owner in 10.2.0.4 after all finish:

SQL> select owner, count(*) from all_objects group by owner order by owner;

OWNER COUNT(*)
------------------------------ ----------
CTXSYS   312
DBSNMP   46
DMSYS   189
EXFSYS    176
MDSYS   906
OLAPSYS   718
ORACLE_OCM    8
ORDPLUGINS   10
ORDSYS    1721
OUTLN    8
PUBLIC   19889
SI_INFORMTN_SCHEMA    8
SYS    23047
SYSTEM   426
TSMSYS    2
XDB    338

16 rows selected.

SQL> select count(*) from all_objects where status = 'INVALID';
COUNT(*)
----------
0

9. Back up the database.
10. If PLSQL_NATIVE_LIBRARY_DIR will be used, create subdirectories.

After the the database is ready, run datapump to import the database.

~~~~~~~~~~~~ export from source database in hosting Linux server ~~~~~~~~~~~~~~
1. Identify and purge some materialized view logs. They can be very big and waste time in exporting and importing.
2. Remove rebuild index parameter in spatial indexes (See Note 362205.1, Section 1).
Login as MST, and execute:
SQL> alter index MST_MD_HYDROS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
SQL> alter index MST_MD_ADM_BNDS_N1 rebuild parameters 'sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX';
3. Connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
4. Create directory dpump_dir1 as SYSTEM
SQL> connect system/*******
SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR1 AS '/path/to/backup/export';

5. Shutdown all 11i Application servers
6. Run export

$ cat expfile.dat
directory=dpump_dir1
PARALLEL=2
dumpfile=dba%U.dmp
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
logfile=expdpapps.log

$ nohup expdp system/****** parfile=expfile.dat > ${LOGFILE} 2>&1 &

~~~~~~~~~~ import to the new database in in-house AIX server~~~~~~~~~~~~~
$ export ORACLE_SID=db_id
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus system/PWD
SQL> create directory dmpdir as '/mnt/usb/export';

$ cat impdp.dat
#$Header: auexpdp.dat 115.4 2005/12/20 16:02:25 apydimar noship $
directory=dmpdir
parallel=2
dumpfile=dba%U.dmp
# full=y
transform=oid:n
logfile=impdpapps.log

$ nohup impdp system/PWD parfile=impdp.dat &