Installation Process: 9.2.0.1 software + 9.2.0.8 PSU patch those you can acquire from MOS. Indeed, for manuel installation process you shoud always compare the dba_registry to see the components you must install.

–Checklist I

–Old DB Server

select distinct(program) from v$session; –Also: v$option , v$version and dba_registry

–User Lists which have objects.

SELECT distinct(OBJ.owner) FROM DBA_OBJECTS OBJ, DBA_USERS USR WHERE OBJ.OWNER=USR.USERNAME

–User Lists those have not on the built-in tablespaces.

SELECT distinct(USR.username) FROM DBA_OBJECTS OBJ, DBA_USERS USR WHERE OBJ.OWNER=USR.USERNAME AND DEFAULT_TABLESPACE NOT IN (‘SYSTEM’,’XDB’,’DRSYS’,’ODM’,’CWMLITE’,’EXAMPLE’,’RMAN’, ‘TOOLS’, ‘SYS’)

–Actually , the instance has one or two core schema.If exist,  we should identify the remap cases for the exp/imp operation

select * from dba_segments where owner= ‘CORE_USER’ AND TABLESPACE_NAME NOT IN (‘CORE_TABLESPACES’, ‘USERS’, …)

select * from dba_segments where tablespace_name in (‘SYS’,’SYSTEM’) AND OWNER NOT IN (‘SYS’,’SYSTEM’,’OUTLN’,’WMSYS’,’ORDSYS’,’MDSYS’, ‘SCOTT’)

–Checklist II

–Compare the old and new init.ora, and adjust it for the appropriate  parameters.

–Create Tablespace

select ‘CREATE TABLESPACE ‘||TABLESPACE_NAME||’ DATAFILE ‘||FILE_NAME||’ SIZE ‘||BYTES||’ autoextend on ‘||’;’ from dba_data_files order by tablespace_name;

–Create Role

select ‘CREATE ROLE ‘ || ROLE || ‘ ;’ from DBA_ROLES order by ROLE

–Grant Role

select ‘ GRANT ‘|| GRANTED_ROLE || ‘ to ‘ || GRANTEE || ‘ ;’ from DBA_ROLE_PRIVS order by GRANTED_ROLE

–Grant Sys Role

SELECT ‘GRANT ‘||PRIVILEGE ||’ ON ‘||TABLE_NAME||’ TO ‘||GRANTEE||’;’ FROM DBA_TAB_PRIVS WHERE GRANTOR in(‘SYS’)

–Gether the all USERS DDL Scripts.

set heading off;

select dbms_metadata.get_ddl(‘USER’,username) from dba_users where username not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’, ‘SYSMAN’, ‘OUTLN’, ‘MDSYS’, ‘ORDSYS’, ‘EXFSYS’, ‘DMSYS’, ‘WMSYS’, ‘WKSYS’, ‘CTXSYS’, ‘ANONYMOUS’, ‘XDB’, ‘WKPROXY’, ‘ORDPLUGINS’, ‘DIP’, ‘SI_INFORMTN_SCHEMA’, ‘OLAPSYS’, ‘MDDATA’, ‘WK_TEST’, ‘MGMT_VIEW’, ‘TSMSYS’);

–Take the core users scripts explicitly.

SET HEADING OFF;

SELECT  DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’CORE_USER’) FROM DUAL;

SELECT  DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’CORE_USER’)  FROM DUAL;

SELECT  DBMS_METADATA.GET_GRANTED_DDL(‘SYSTE_GRANT’,’CORE_USER’)  FROM DUAL;

export nls_lang=…

–EXP/IMP Operation

–On the Old db server

Shutdown immedite;

–Close the listener and Be sure that it is not running due to the consistency (We dont want any session – Or open the database with restricted mode.)

Startup mount;

Alter system set job_queue_processes = 0 scope=both;

Alter database open;

export nls_lang=…

exp  system/password file=full.dmp LOG=export.log  full=y   compress=n STATISTICS=none  FEEDBACK=10000

–Copy the dmp file to the New DB Server.

export nls_lang=…

imp system/password file=FULL.dmp log=imp.log  ignore=y buffer=32000000 fromuser=CORE_USER touser=CORE_USER

exec sys.utl_recomp.recomp_parallel(4);

–Check the parameter job_queue_processes and set it accordingly.

–Gether DB Stats

–Copy the tnsnames.ora to the new DB server and change it appropriately.

–On the Domain Controller side you can use the same alias for the old & new db server thus, no need to change the clients’ or Application Servers’ connection string.

–After EXP/IMP Controls

Select count(*) from dba_objects Where status <> ‘VALID’;

–Check the objects count

select  owner,object_type,count(*) from dba_objects where  owner  in (‘CORE_USER’) group  by owner,object_type order  by owner,object_type ;