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 &

Patch number (or patchset) for upgrading Oracle database version

There is a summary document on database Server patchsets from 8.1.7.4 and after. It is a good resource for identifying patch numbers of RDBMS PSU, SPU(CPU), Bundle patches and Patchsets.

The Metalink note number is 438049.1. Seems Oracle updates this document all the time.

Select MAX from a column

I tried using below function to get a sequence value for a coulmn, but kept getting NULL when when the table is empty.

begin
select max(nvl(id, 0)) + 1 INTO t_id FROM table_name;
exception when NO_DATA_FOUND then
t_id := 1;
end;

The real trick is that "select max(id) FROM table_name" always returns NULL, and does not raise NO_DATA_FOUND error.

The fix is to use below statement:

select nvl(max(id), 0) + 1 INTO t_id FROM table_name;

Note "select count(id) from table_name" always returns 0 when the table is empty.

Obfuscate (or hide) DAD's password

In Apache configuration, a password can be saved in dads.conf file to let users to view webpages with a password. dataTool.pl is the tool to hide the real password. There are a few of environment variables need to be specified before the execution on dadTool.pl works.

1. Make sure $ORACLE_HOME points to the OMS Home. Such as
export ORACLE_HOME=/app/oracle/product/oms10g

2. Here are variables I specified in Grid Control (10.2.0.1 & 10.2.0.4) installations:
export PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH=$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export LIBPATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Note: on AIX (64-bit) server, ponit LIBPATH to $ORACLE_HOME/lib32 (not to the 64 bit libraries):
export LIBPATH=$ORACLE_HOME/lib32

3. The result should be

$ cd $ORACLE_HOME/Apache/modplsql/conf
$ perl dadTool.pl -o
Information
-------------------------------------------------------------
Backed up older dads.conf as /app/oracle/product/oms10g/Apache/modplsql/conf/dads.conf.orig.2009-05-26_14-12

All passwords successfully obfuscated. New obfuscations : 1

Logon trigger to change CURSOR_SHARING=FORCE

Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.

Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.

CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID      VARCHAR2(30)                 NOT NULL,
  MACHINE     VARCHAR2(200),
  PROGRAM    VARCHAR2(200),
  CREATE_DT  DATE                            DEFAULT sysdate
)
TABLESPACE USER01;

CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
  v_db_user      VARCHAR2(30);
  v_machine     VARCHAR2(200);
  v_program     VARCHAR2(200);
  v_os_user      VARCHAR2(30);
  v_count          INTEGER;
BEGIN
  v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
  v_machine  := SYS_CONTEXT('USERENV', 'HOST');
  v_program   := SYS_CONTEXT('USERENV', 'MODULE');
  v_os_user := SYS_CONTEXT('USERENV','OS_USER');

  SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
  WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
  AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
  ;  -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases

  IF v_count >=1 THEN
     EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
     INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
               VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
  END IF;   -- LOGIN_AUDIT is another table.
 END;
/

Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;