Determine database character set

You can  run the following SQL to determine whether your database character set is UTF-8. 

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

It should return the value AL32UTF8. 

Another option is to run the following SQL :

SELECT * FROM NLS_DATABASE_PARAMETERS;

The value against NLS_CHARACTERSET should be UTF8.It will indicate also the national character set.

useful database upgrade notes

I was searching for useful upgrade notes and i found the following so i decided to share it:


Minimizing Downtime During Production Upgrade [ID 478308.1]
Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]
Different Upgrade Methods For Upgrading Your Database [ID 419550.1]
Migration of Oracle Database Instances Across OS Platforms[ID 733205.1]
Oracle® Database Upgrade Guide:
10g Release 2 (10.2)
http://docs.oracle.com/cd/B19306_01/server.102/b14238/upgrade.htm#i1011372

RMAN backup types

1-A whole database backup includes all datafiles and at least one control file.
Partial database backups may include zero or more tablespaces, zero or more datafiles, and
may or may not include a control file.
Full backups are non-incremental RMAN backups. You can make a full backup of only one
datafile. “Full” does not refer to how much of the database is backed up, but to the fact that the
backup is not incremental.

2-Incremental backups make a copy of all data blocks that have changed since some previous
backup. Oracle Database 10g supports two levels of incremental backup (0 and 1). A level 0 or
baseline backup is equivalent to a full backup and contains all data blocks. A level 1
cumulative incremental backs up all database blocks changed since the level 0 backup. A level
1 differential incremental backs up all database blocks changed since a level 0 or level 1
backup. To restore using incremental backups, the baseline backup must first be restored, and
then the incremental backup. You can make incremental backups with Recovery Manager
(RMAN).
Offline backups (also known as cold or consistent backups) are taken while the database is not
open. They are consistent because at the time of the backup, the system change number (SCN)
in the datafile headers matches the SCN in the control files.


Online backups (also known as hot or inconsistent backups) are taken while the database is
open. The backups are inconsistent because, with the database open, there is no guarantee that
the datafiles are synchronized with the control files.

Why Do we need to backup our data?

We need backup because of the following:
• Media failure: The failure of a read or write of a database file due to a physical problem with the file.
• User errors: Data in your database is changed or deleted incorrectly.

Responding to User Error
Possible responses to user error include:
• Re-entering the lost data manually, if a record of the changes exists.
• Importing the dropped object, if a suitable export file exists or the object is still available on a standby
database.
• Performing tablespace point-in-time recovery (TSPITR) of one or more tablespaces.
• Returning the database to a past state using database point-in-time recovery.
• Using one of the Oracle Flashback features to recover from logical corruption by returning affected objects to a past state.

Investigating ITL wait resulted from ORA-00060: Deadlock detected

Hello,
I got the following error:ORA-00060: Deadlock detected
investigating the trace logfile i found :

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-0013001c-0002687a       149     369     X            135     517           S

TX-000c0004-000283db       135     517     X             99     531           S

TX-00090003-0005c060        99     531     X            149     369           S

the session Holds wait type S made me check if it resulted from ITL wait and ehre i checked it using the following query:

select * from

(

   select 

      DECODE

      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)

   AS "Object",

sum(case when 

   a.statistic_name = 'ITL waits' 

then 

   a.value else null end) "ITL Waits",

sum(case when 

   a.statistic_name = 'buffer busy waits' 

then 

   a.value else null end) "Buffer Busy Waits",

sum(case when 

   a.statistic_name = 'row lock waits' 

then 

   a.value else null end) "Row Lock Waits",

sum(case when 

   a.statistic_name = 'physical reads' 

then 

   a.value else null end) "Physical Reads",

sum(case when 

   a.statistic_name = 'logical reads' 

then 

   a.value else null end) "Logical Reads"

from 

   v$segment_statistics a

where 

   a.owner like upper('&owner')

group by 

   rollup(a.object_name)) b

where (b."ITL Waits">0 or b."Buffer Busy Waits">0);

The owner in the above query is the schema that the statement in the trace file runs against.

Some other queries that may help:

SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
FROM v$segment_statistics t
WHERE t.STATISTIC_NAME = 'ITL waits'
AND t.VALUE > 0;

select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock                                                            
where type='TX';                                                                                            

Select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   ' ' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p
where
   s.sid = l.sid and
   s.username <> ' ' and
   s.paddr = p.addr and
   l.type <> 'TM' and
   (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   object_name object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   sys.dba_objects o
where
   s.sid = l.sid and
   o.object_id = l.id1 and
   l.type = 'TM' and
   s.username <> ' ' and
   s.paddr = p.addr
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   '(Rollback='||rtrim(r.name)||')' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   v$rollname r
where
   s.sid = l.sid and
   l.type = 'TX' and
   l.lmode = 6 and
   trunc(l.id1/65536) = r.usn and
   s.username <> ' ' and
   s.paddr = p.addr
order by 5, 6;

If you got IT 0 waits then it is not ITL wait problem but if you find that it is ITL problem then you have to refer to the following note:

How To Solve High ITL Waits For Given Segments. [ID 464041.1]