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 ;