On the Source Database
==================
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
SOURCE READ WRITE
1. SQL> select owner,sum(bytes)/1024/1024 from dba_segments where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by owner;
2. SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_segments where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by tablespace_name;
3. SQL> select default_tablespace,username from dba_users where username in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
4. SQL> select owner,status,object_type,count(*) from dba_objects where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by object_type,owner,status;
5. SQL> select object_type,count(*) from dba_objects where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by object_type;
6. SQL> select count(*),status from dba_objects where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by status;
7. SQL> select object_type,count(*),status from dba_objects where owner in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5') group by status,object_type;
8. SQL> select tablespace_name,sum(bytes)/1024/1024/1000 from dba_data_files where
tablespace_name in('TS1','TS2','TS3','TS4','TS5','TS6','TS7') group by tablespace_name;
9. Export Schemas:
vi exp_SOURCE_schemas.sh
#!/bin/ksh
export NLS_LANG=AMERICAN_AMERICA.UTF8
mknod /tmp/exp_pipe1 p
gzip -cNf SOURCE_schemas.dmp.gz &
exp system/sysmic32 file=/tmp/exp_pipe1 buffer=2097152 log=exp_SOURCE_schemas.log
statistics=none owner=SCHEMA1,SCHEMA2,SCHEMA3,SCHEMA4,SCHEMA5
rm -f /tmp/exp_pipe1
unset NLS_LANG
10. nohup ./exp_SOURCE_schemas.sh > exp_SOURCE_schemas.log 2>&1
11. Copy the SOURCE_schemas.dmp.gz to Target Database server.
scp -p SOURCE_schemas.dmp.gz ganesh@target_server:/oracle/export
On the Target Database
==================
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
TARGET READ WRITE
1. Create tablespaces as per the output from Step 7 you got on Source database
2. Create users on Target database
Run the following script on the source database and execute the resultant script on the
Target database
set linesize 300 set pagesize 300
spool create_users.sql
select 'create user '||username||' identified by values '''||password||''' default tablespace
'||default_tablespace||' temporary tablespace temp profile '||profile||';' from dba_users where
username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off
3. Grant Quotas on Target database
Run the following script on the source database and execute the resultant script on the
Target database
spool create_tablespace_quota.sql
select 'alter user '||username||' quota unlimited on '||tablespace_name||';' from dba_ts_quotas
where MAX_BYTES='-1' and username in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'alter user '||username||' quota '||max_bytes||' on '||tablespace_name||';' from
dba_ts_quotas where MAX_BYTES!='-1' and username in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off
4. Grant Roles on Target database
Run the following script on the source database and execute the resultant script on the
Target database
spool create_grant_roles.sql
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';' from dba_role_privs where ADMIN_OPTION='NO' and
grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||' with admin option;' from dba_role_privs where
ADMIN_OPTION='YES' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off
5. Grant System privs on Target database spool create_sys_privs.sql
Run the following script on the source database and execute the resultant script on the
Target database
select 'grant '||PRIVILEGE||' to '||GRANTEE||';' from dba_sys_privs where
ADMIN_OPTION='NO' and grantee in ('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'grant '||PRIVILEGE||' to '||GRANTEE||' with admin option;' from dba_sys_privs
where ADMIN_OPTION='YES' and grantee in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off
6. Import Schemas
vi imp_TARGET_schemas.sh
#!/bin/ksh
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
mknod /tmp/imp_pipe1 p
gunzip -c SOURCE_schemas.dmp.gz >/tmp/imp_pipe1 &
imp system/manager99 file=/tmp/imp_pipe1 buffer=20971520
log=imp_TARGET_schemas.log full=y
rm -f /tmp/imp_pipe1
unset NLS_LANG
7. Verify the logs
8. Do step 1 to step 7 that you have done on the source database.
9. Grant Table privs on the Target
Run the following script on the source database and execute the resultant script on the
Target database
spool create_tab_privs.sql
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from
dba_tab_privs where GRANTABLE='NO' and grantee in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant
option;' from dba_tab_privs where GRANTABLE='YES' and grantee in
('SCHEMA1','SCHEMA2','SCHEMA3','SCHEMA4','SCHEMA5');
spool off
9. EXEC DBMS_UTILITY.COMPILE_SCHEMA ('SCHEMA1'); -> Do for all schemas that you have
imported
10. exec dbms_stats.gather_schema_stats('SCHEMA1', cascade=>TRUE); -> Do for all
schemas that you have imported
End of Migration
Where are the log files located in R12?
For DBA’s who mostly worked on 11i environments, finding the log files (Concurrent
manager log, apache log etc.,) on a R12 environment might pose a threat initially, because
these log files no longer reside in their old location ie., $APPLCSF/$APPLLOG or
$APACHE_TOP/Apache/logs.
In R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)
Concurrent Reqeust related logs
$LOG_HOME/appl/conc -> location for concurrent requests log and out files
$LOG_HOME/appl/admin -> location for mid tier startup scripts log files
Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home)
$LOG_HOME/ora/10.1.3/Apache -> Location for Apache Error and Access log files
$LOG_HOME/ora/10.1.3/j2ee -> location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn -> location for opmn related log files
Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)
$LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports
Related metalink notes to enable additional debugging
419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12
422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN
