Powered By Blogger

Wednesday, January 26, 2011

Using Database Verify command on the Datafiles for corruption

Below is the script file for using DBverify command against the datafiles.

Save the below file as Shell file using extension .sh in unix or Batch file as .bat in windows and execute

dbv file=/NVAR/oracle/oradata/NAVADEMO/system01.dbf blocksize=8192 logfile=system01.dbf.1.log dbv file=/NVAR/oracle/oradata/NAVADEMO/sysaux01.dbf blocksize=8192 logfile=sysaux01.dbf.2.log dbv file=/NVAR/oracle/oradata/NAVADEMO/undotbs01.dbf blocksize=8192 logfile=undotbs01.dbf.3.log dbv file=/NVAR/oracle/oradata/NAVADEMO/users01.dbf blocksize=8192 logfile=users01.dbf.4.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data01.dbf blocksize=8192 logfile=qliveprod_data01.dbf.5.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_indx.dbf blocksize=8192 logfile=qliveprod_indx.dbf.6.log dbv file=/NVAR/oracle/oradata/NAVADEMO/tiger_data01.dbf blocksize=8192 logfile=tiger_data01.dbf.7.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data02.dbf blocksize=8192 logfile=qliveprod_data02.dbf.8.log dbv file=/NVAR/oracle/oradata/NAVADEMO/qliveprod_data03.dbf blocksize=8192 logfile=qliveprod_data03.dbf.9.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data01.dbf blocksize=8192 logfile=NVAR_data01.dbf.10.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_idx01.dbf blocksize=8192 logfile=NVAR_idx01.dbf.11.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data02.dbf blocksize=8192 logfile=NVAR_data02.dbf.12.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR_data03.dbf blocksize=8192 logfile=NVAR_data03.dbf.13.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02d02.dbf blocksize=8192 logfile=NVAR02d02.dbf.14.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR01d01.dbf blocksize=8192 logfile=NVAR01d01.dbf.15.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR01i01.dbf blocksize=8192 logfile=NVAR01i01.dbf.16.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02d01.dbf blocksize=8192 logfile=NVAR02d01.dbf.17.log dbv file=/NVAR/oracle/oradata/NAVADEMO/NVAR02i01.dbf blocksize=8192 logfile=NVAR02i01.dbf.18.log

Below is the logfile created for a file which has no corruption ,by the above script file.
DBVERIFY: Release 11.1.0.6.0 - Production on Thu Apr 29 13:07:27 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY-Verification starting : FILE = /nvar/oracle/oradata/NAVADEMO/nvar_data01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 513280
Total Pages Processed (Data) : 372277
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 103009
Total Pages Failing (Index): 0
Total Pages Processed (Other): 35162
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2832
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3358059738 (1685.3358059738)

Below is the logfile created for a file which has corruptions ,by the above script file.

DBVERIFY: Release 11.1.0.6.0 - Production on Thu Apr 29 13:08:07 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.

DBVERIFY-Verification starting : FILE = /nvar/oracle/oradata/NAVADEMO/nvar_data03.dbf
Page 38576 is marked corrupt
Corrupt block relative dba: 0x034096b0 (file 13, block 38576)
Bad header found during dbv:
Data in bad block:
type: 97 format: 0 rdba: 0x74616369
last change scn: 0x6e76.2f6e6f69 seq: 0x64 flg: 0x2e
spare1: 0x70 spare2: 0x6c spare3: 0x652d
consistency value in tail: 0x794d0602
check value in block header: 0x736d
computed block checksum: 0x60a
Page 48062 is marked corrupt
Corrupt block relative dba: 0x0340bbbe (file 13, block 48062)
Bad header found during dbv:
Data in bad block:
type: 83 format: 5 rdba: 0x5b203230
last change scn: 0x7270.412f3432 seq: 0x2f flg: 0x32
spare1: 0x52 spare2: 0x30 spare3: 0x3a30
consistency value in tail: 0x80c30601
check value in block header: 0x3130
block checksum disabled
Page 49482 is marked corrupt
Corrupt block relative dba: 0x0340c14a (file 13, block 49482)
Bad header found during dbv:
Data in bad block:
type: 58 format: 4 rdba: 0x2b203732
last change scn: 0x205d.30333530 seq: 0x22 flg: 0x47
spare1: 0x33 spare2: 0x3a spare3: 0x2f20
consistency value in tail: 0x80c40601
check value in block header: 0x5445
computed block checksum: 0x3bbd
Page 70259 is influx - most likely media corrupt
Corrupt block relative dba: 0x03411273 (file 13, block 70259)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03411273
last change scn: 0x0695.a7c6e680 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x2563
computed block checksum: 0xe719
Page 81382 is marked corrupt
Corrupt block relative dba: 0x03413de6 (file 13, block 81382)
Bad header found during dbv:
Data in bad block:
type: 49 format: 0 rdba: 0x32303a38
last change scn: 0x3033.35302b20 seq: 0x5d flg: 0x20
spare1: 0x3a spare2: 0x35 spare3: 0x5445
consistency value in tail: 0x47e1060d
check value in block header: 0x4722
block checksum disabled
Page 101964 is influx - most likely media corrupt
Corrupt block relative dba: 0x03418e4c (file 13, block 101964)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03418e4c
last change scn: 0x0695.a78be66c seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x6f18
computed block checksum: 0xafef
Page 109891 is marked corrupt
Corrupt block relative dba: 0x0341ad43 (file 13, block 109891)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x000a.000a6b00 seq: 0x5f flg: 0x09
spare1: 0xba spare2: 0xbe spare3: 0x6000
consistency value in tail: 0xcac70624
check value in block header: 0x900
block checksum disabled
Page 111680 is marked corrupt
Corrupt block relative dba: 0x0341b440 (file 13, block 111680)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x0720.00072200 seq: 0x0 flg: 0x21
spare1: 0xba spare2: 0xbe spare3: 0x7513
consistency value in tail: 0x410e0602
check value in block header: 0x1
block checksum disabled
Page 133669 is influx - most likely media corrupt
Corrupt block relative dba: 0x03420a25 (file 13, block 133669)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x03420a25
last change scn: 0x0695.a7cf9b37 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0x9232
computed block checksum: 0xfa1e
Page 134914 is marked corrupt
Corrupt block relative dba: 0x03420f02 (file 13, block 134914)
Bad header found during dbv:
Data in bad block:
type: 202 format: 6 rdba: 0x32000000
last change scn: 0x0715.00071800 seq: 0x0 flg: 0x16
spare1: 0xba spare2: 0xbe spare3: 0x730e
consistency value in tail: 0xa8750601
check value in block header: 0x1
computed block checksum: 0x1167
Page 169859 is marked corrupt
Corrupt block relative dba: 0x03429783 (file 13, block 169859)
Bad header found during dbv:
Data in bad block:
type: 2 format: 0 rdba: 0x00140000
last change scn: 0x0000.03e80000 seq: 0x0 flg: 0x00
spare1: 0x1 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xf9f82802
check value in block header: 0x0
block checksum disabled
Page 238087 is influx - most likely media corrupt
Corrupt block relative dba: 0x0343a207 (file 13, block 238087)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0343a207
last change scn: 0x0695.a7dc8aa6 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00000000
check value in block header: 0xa4f7
computed block checksum: 0x6dd4

DBVERIFY - Verification complete
Total Pages Examined : 256000
Total Pages Processed (Data) : 192131
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 46117
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14782
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2958
Total Pages Marked Corrupt : 12
Total Pages Influx : 4
Total Pages Encrypted : 0
Highest block SCN : 3358059738 (1685.3358059738)

avoiding pls-00436 with forall

FORALL: Bulk update. - Check the above link for more information.

Workaround Queries(11g) While Monitoring

------------------------ check session information -----------------------

select * form v$fixed_table;
select * from v$session where osuser='501200I127';
select * from v$sysstat
select * from v$session_longops where sid in (130,138)
select * from v$session where osuser='50120C1008';
select * from v$session where type!= 'BACKGROUND';
select 'alter system kill session ' '''' sid ',' serial#'''' ' immediate;' as script from v$session where osuser='501200I127';
select * from v$session where sid in (147);
alter system kill session '138,8217' immediate;

-------------find which session is using a particular object --

select * from v$access where object like 'RPT_TREASURY_REPORT_FILTERS%';

---------- enable trace for a particular session ------

exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(53 ,5083,true);

---------- find the trace files for a session -------

select b.username, c.value '\' lower(d.value) '_ora_'
to_char(a.spid, 'fm00000') '.trc' "TRACE_FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr and c.name = 'user_dump_dest'
and d.name = 'db_name' and b.username is not null and b.sid=53;
alter system kill session '191,19465' immediate;

----------------- find the undo space usage in UNDO tablespace for a database ---------------------------

select sum(decode(status,'EXPIRED',bytes,0))/sum(bytes) EXPIRED_PERCENT,
sum(decode(status,'UNEXPIRED',bytes,0))/sum(bytes) UNEXPIRED_PERCENT,
sum(decode(status,'ACTIVE',bytes,0))/sum(bytes) ACTIVE_PERCENT
from dba_undo_extents;

--------------------- find the Database components features usage ----------------------------------

select * from DBA_FEATURE_USAGE_STATISTICS;

------------------------ find current running query using OS process id -----------------

SELECT s.sid, s.serial#, q.SQL_TEXT ,s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p,v$sqltext q
WHERE s.paddr = p.addr and q.address = s.sql_address
AND q.hash_value = s.sql_hash_value AND p.spid = 22737;
---------- 1 row

SELECT s.sid,s.osuser,q.SQL_FULLTEXT
FROM v$session s, v$process p,v$sqlarea q
WHERE s.paddr = p.addr and s.sql_address= q.address AND p.spid = 24510;

exec dbms_stats.GATHER_TABLE_STATS('STARREP','DISCOUNT_FACTOR_DATA');

--------------------- find the OS process id -----------------------

SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (488);

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24);

--------- cpu usage information ---------

select a.sid, a.value cpu_usage, a.value/b.value cpu_bycall from v$sesstat a, v$sesstat b
where a.sid=b.sid and a.statistic#=12 and b.statistic#=6
and a.value>0 and b.value>0 order by a.value desc;

-------------------------- find the top session ------------------

Select sid,username,round(100 * total_user_io/total_io,2) tot_io_pct
from (select
b.sid sid,nvl(b.username,p.name) username,sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by b.sid, nvl(b.username,p.name)),
(select sum(value) total_io from
sys.v_$statname c,
sys.v_$sesstat a
where a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by 3 desc;

-------------------- find the blocking sessions --------------------------

select s1.username '@' s1.machine
' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session WHERE blocking_session IS NOT NULL;

------------- identify locked object ----------------------------

select object_name from dba_objects where object_id = 88519;
select * from v$lock;

--------------- SQL run time monitor -----------------------------

select * from v$sql_monitor where sid = 344;

---------------- find top SQL queries ------------------------------

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/01/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('09/09/2008','MM/DD/YYYY'))
GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC;

------------------------ resources used by a session --------------

select s.osuser osuser,s.serial# serial,se.sid,n.name,max(se.value) maxmem
from v$sesstat se,v$statname n,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
group by s.osuser, s.serial#, se.sid, n.name
order by 2;

--- check local session

select a.name,b.value from v$statname a ,v$mystat b where a.statistic#=b.statistic# and b.sid in (474,475,477,478,481,494,499) ;

-------------------check number of cursors opened -----------------------------------------------------

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.username, s.machine
from v$sesstat a,v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;

How to recover the deleted mails in outlook

>> Click Start,
>> Click Run, type regedit, and then click OK.
>> Locate and then click the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Client\Options
>> Right-click the Options subkey, point to New, and then click DWORD Value.
>> Type DumpsterAlwaysOn for the DWORD name, and then press ENTER.
>> Double-click DumpsterAlwaysOn. In the Value data box, type 1, and then click OK.
>> Quit Registry Editor.

To enable hard-deleted items recovery in Outlook follow these steps:

>> Open Registry Editor.
>> In Registry Editor, navigate to the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Client\Options
>> Create the following value (DWORD):
DumpsterAlwaysOn
and give it a value of 1.

Note: As always, before making changes to your registry you should always make sure you have a valid backup. In cases where you're supposed to delete or modify keys or values from the registry it is possible to first export that key or value(s) to a .REG file before performing the changes.

>> Close Registry Editor, re-open Outlook for settings to take place.
>> After you add this value, the Recover Deleted Items option becomes available for the Sent Items folder, the Drafts folder, the Outbox folder, and the Inbox folder.
>> You can recover deleted items from these folders.
/images/ol_recover_del6.gif/images/ol_recover_del6.gif
>> As you can clearly see, the Inbox has the Recover Deleted Items option enabled, and so does each and every folder in the user's mailbox.

Statspack Report scheduling based on the Requirment timings

The below steps are to schedule the statspack collection to run on the specific timings. ie it runs in the weeksdays apart from monday by the interval 3 hours, and from sunday 12 pm to monday 12 pm it runs every 1 hour (ie during the peak time).


STEP 1: Remove the existing statspack job schedule:
EXECUTE DBMS_JOB.REMOVE(1);

STEP 2 : Function that will return the date to the job:

create or replace function statspack_schedule
return date
as
N_date date;
begin
select case
when ( to_char(dt,'dy') = 'sun' and to_char(dt,'hh24:mi')>'12:00')
or ( to_char(dt,'dy') = 'mon' and to_char(dt,'hh24:mi')<'12:00') then d1 else d2 end into N_date from ( select sysdate dt , trunc(sysdate, 'hh24') +1/24 d1 , trunc(sysdate, 'hh24') +3/24 d2 from dual ); return N_date; end; /

STEP 3: statspack Job scheduling :

BEGIN
DBMS_JOB.isubmit (
job => 1,
what => 'statspack.snap;',
next_date => sysdate,
interval => '( select statspack_schedule from dual )');
COMMIT;
END;
/

STEP 4: Purge the old data with the retention of 1 month (Manually do this activity every month):

CONNECT perfstat/perfstat
@?\rdbms\admin\sppurge.sql
(specified the snap id with retention of 1 month)
to identified the begining and end snap id:
select snap_id, snap_level "level", to_char(snap_time,' dd/mm/YYYY HH24:mi:ss') snap_date from stats$snapshot
where snap_timeorder by 1;

Tuesday, January 18, 2011

Components still show 9.2.0.1 version after Applying 9.2.0.7 patchset also

There is an issue while applying patchset 9.2.0.7 on 9.2.0.1. Please see the below steps and explanation for more information.

Step 1 : I installed 9.2.0.1 software on a windows xp professional machine for testing purpose. But no database was created at the time.

Step 2 : And later i applied 9.2.0.7 patch on that 9.2.0.1 software, the patch installation went fine without any issues or errors.

Step 3 : And after the sucessfull patch insallation i created a database using DBCA. it was sucessfull.

Step 4 : I connected the database as sys user and checked the version using V$VERSION view. it displayed

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - ProductionPL/SQL Release 9.2.0.7.0 - ProductionCORE 9.2.0.7.0 ProductionTNS for 32-bit Windows: Version 9.2.0.7.0 - ProductionNLSRTL Version 9.2.0.7.0 - Production

Step 5: As a precautionary measure i thought of checking the components version from
dba_registry and it showed the old version that is 9.2.0.1. Please see below

SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ---------------------------
Oracle9i Catalog Views 9.2.0.1.0
Oracle9i Packages and Types 9.2.0.1.0
Oracle Workspace Manager 9.2.0.1.0
JServer JAVA Virtual Machine 9.2.0.1.0
Oracle XDK for Java 9.2.0.1.0
Oracle9i Java Packages 9.2.0.1.0
Oracle interMedia 9.2.0.1.0
Spatial 9.2.0.1.0
Oracle Text 9.2.0.1.0
Oracle XML Database 9.2.0.1.0
Oracle Ultra Search 9.2.0.1.0
Oracle Data Mining 9.2.0.1.0
OLAP Analytic Workspace 9.2.0.1.0
Oracle OLAP API 9.2.0.1.0
OLAP Catalog 9.2.0.1.0
15 rows selected.

So its a problem. So we have to manualy upgrade the components by running scripts which is mentioned in the README file which came with the patch.

So i ran the scripts by connecting as a SYS user to the database as below

sqlplus sys/******* as sysdba
SQL> Shutdown immediate
SQL> startup migrate
SQL> @C:\oracle\rdbms\admin\catpatch.sql

after the sucessfull execution of the above script executed the second script as mentioned in the README file.

SQL>Shutdown immediate
SQL> startup
SQL>@C:\oracle\rdbms\admin\utlrp.sql

after the sucessfull execution of both the scripts again i checked the components version and found that everything is fine, Please see below.


SQL> select comp_name,version from dba_registry;
COMP_NAME VERSION
-------------------------------------------------- ---------------------------
Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
Oracle Workspace Manager 9.2.0.1.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0
Oracle interMedia 9.2.0.7.0
Spatial 9.2.0.7.0
Oracle Text 9.2.0.7.0
Oracle XML Database 9.2.0.7.0
Oracle Ultra Search 9.2.0.7.0
Oracle Data Mining 9.2.0.7.0
OLAP Analytic Workspace 9.2.0.7.0
Oracle OLAP API 9.2.0.7.0
OLAP Catalog 9.2.0.7.0
15 rows selected.

Thanks ..
Navaneeth

Tuesday, December 7, 2010

Database Charachter set change from WE8MSWIN1252 to utf8

The case is we have a database with characterset WE8MSWIN1252, we need to migrate the schemas to different database which has different characterset.
What we did is we ran CSSCAN utility against the source database to check whether the characterset migration is possible or not.
step 1:
CSSCAN
'SYS/********@SourceDB as sysdba' user=schemaname fromchar=WE8MSWIN1252 TOCHAR=utf8 log=D:\CSS_SCAN_DETAILS\Schemaname_log
Step 2:
The above command will create three output files
1.Err output file.
2.Log file.
3.Text file
like Schemaname_log.err,schemaname_log.txt.schemaname_log.log
The text file contains the below output.

Database Scan Summary Report
Time Started : 2010-08-03 18:52:44
Time Completed: 2010-08-03 18:53:16
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2010-08-03 18:53:14 2010-08-03 18:53:15
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 1,062.25M 7.75M 1,070.00M .00K
UNDOTBS1 17.94M 7,822.06M 7,840.00M .00K
SYSAUX 749.38M 50.63M 800.00M .00K
TEMP .00K .00K .00K .00K
USERS 2,392.50M 147.50M 2,540.00M .00K
EMP 28.88M 271.13M 300.00M .00K
EMPTEST 79.75M 220.25M 300.00M .00K
EMPPROD 78.00M 222.00M 300.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 26,868.00M 19,685.06M 46,553.06M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
Some character type application data are not convertible to the new character set
[Data Dictionary Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%

[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 4,499 29 2 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 4,499 29 2 0
Total in percentage 99.316% 0.640% 0.044% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGE 2 1 0
TRAN.TBL_TASK_DETAILS 3 0 0
TRAN.TBL_TASK_DOCUMENTS 1 1 0
TRAN.TBL_TRANSLATIONS 22 0 0
TRAN.TBL_USER_MASTER 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
USER.TABLECOLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGEFIST_FILE_NAME 2 1 0
TRAN.TBL_TASK_DETAILSTADE_CUSTOMER_NAME 1 0 0
TRAN.TBL_TASK_DETAILSTADE_END_CUSTOMER_NA 1 0 0
TRAN.TBL_TASK_DETAILSTADE_INVOICE_SEND_TO 1 0 0
TRAN.TBL_TASK_DOCUMENTSTADO_SOURCE_FILENA 1 1 0
TRAN.TBL_TRANSLATIONSTRAN_VALUE 22 0 0
TRAN.TBL_USER_MASTERUSMA_PASSWORD 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------


The error output file will contain the tables list that wich we need to work on when doing the migration like below
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]

[Application data individual exceptions]
User : TRAN
Table : TBL_FILE_STORAGE
Column: FIST_FILE_NAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY62AAVAAAAHWAAU exceed column size 51 P3-FRA2028FR -ContrĂ´le des DE
------------------ ------------------ ----- ------------------------------
User : TRAN
Table : TBL_TASK_DOCUMENTS
Column: TADO_SOURCE_FILENAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY64AAVAAAAJuAAF exceed column size 51 P3-FRA2028FR -ContrĂ´le des DE
------------------ ------------------ ----- ------------------------------

The logfile will contain the below output
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Aug 3 18:52:43 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning TRAN.TBL_EMAIL_LOG[AAAY6wAAVAAAAGhAAA]
. process 1 scanning TRAN.TBL_ROLE_ACCESS[AAAY5JAAVAAAAIZAAA]
. process 1 scanning TRAN.TBL_TASK_STATUS[AAAY5UAAVAAAAJ5AAA]
. process 1 scanning TRAN.TBL_SYSTEM_VARIABLES[AAAY5MAAVAAAAIxAAA]
. process 1 scanning TRAN.TBL_USER_MASTER[AAAY5gAAVAAAALZAAA]
. process 1 scanning TRAN.TBL_TRANSLATION_CODES[AAAY5aAAVAAAAKpAAA]
. process 1 scanning TRAN.TBL_TASK_DETAILS[AAAY5PAAVAAAAJJAAA]
. process 1 scanning TRAN.TBL_TASK_DOCUMENTS[AAAY64AAVAAAAJpAAA]
. process 1 scanning TRAN.TBL_TRANSLATIONS[AAAY5XAAVAAAAKRAAA]
. process 1 scanning TRAN.TBL_USER_ROLES[AAAY5nAAVAAAANZAAA]
. process 1 scanning TRAN.TBL_FORMAT_CODES[AAAZiFAAkAAAAAZAAA]
. process 1 scanning TRAN.TBL_LIST_DETAILS[AAAY5DAAVAAAAHpAAA]
. process 1 scanning TRAN.TBL_LIST_MASTER[AAAY5GAAVAAAAIBAAA]
. process 1 scanning TRAN.TBL_FILE_STORAGE[AAAY62AAVAAAAHRAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.


From the above reports,the error file says we need to alter the column of the couple of the tables for the migration to be sucessfull.
So based on the report we altered the column size according to the report and did the migration sucessfully.