Quick Reference on Database Characterset.

Recently i was upgrading the Oracle apps 11i to R12 and the customer want Arabic NLS. So i need to convert my database characterset form US7ASCII to a characterset that supports Arabic. I have confused which one should i use, UTF8 or AL32UTF8.  I did some reserch on oracle support and decided to use AL32UTF8.  Below given points helped me to make that decision.

The default UTF-8 (Unicode) characterset for 9i/10G is AL32UTF8, however this characterset is NOT recognized by any pre-9i clients/server systems.

Recommend that you use UTF8 instead of AL32UTF8 as database characterset if you have 8i (or older) servers and clients connecting to the 9i/10g system
until you can upgrade the older versions to 9i or higher.
UTF8 is Unicode revision 3.0 in 8.1.7 and up. AL32UTF8 is updated with newer Unicode versions in each major release

Besides the difference in Unicode version the "big difference" is that AL32UTF8 has build in support for "Surrogate Pairs", also known as "Surrogate characters"
or "Supplementary characters". Practically this means that in 99% of the cases you can use UTF8 instead of AL32UTF8 without any problem.
There is no performance difference between UTF8 and AL32UTF8. But seen UTF8 is NOT updated with newer Unicode versions we however suggest to use AL32UTF8 when possible.

Summary:
If you use 8.1 or 8.0 clients or servers or connect to 8.1 or 8.0 databases then use UTF8 as NLS_CHARACTERSET for the 9i (or up) databases, otherwise
(unless the application vendor explicit mentions UTF8 of course) use AL32UTF8.

For an Unicode database Oracle does not need "Unicode support" from the OS where the database is running on because the Oracle AL32UTF8 implementation is not depending on OS features
There is also no need to "install Unicode" or so for the Oracle database/client software, all character sets known in a database version , and this includes
Unicode character sets, are always installed. You simply cannot choose to not install them
If your current Oracle version is 8.1.7 or lower then it's best to upgrade first to a higher release, mainly because
a) you then can use AL32UTF8 (not possible in 8i)
b) Csscan has a few issues in 817 who might provoke confusion.
If your current Oracle version is 9i or up then both (before or after) are a good choice, it simply depends on your preference or needed application changes
Storage.
AL32UTF8 is a varying width characterset, which means that the code for 1 character can be 1 , 2 , 3 or 4 bytes long. This is a big difference with
character sets like WE8ISO8559P1 or WE8MSWIN1252 where 1 character is always 1 byte.

Create Database Documentation using Oracle Grid Control

Oracle Grid control is one of the useful tools for DBAs and System administrators.
You can generate a documentation of your database using Oracle Grid Control with two click of mouse button.
You can acheive this using the report functionality in the grind control.
Grid Control management repository stores configuration and performance information of your database and provides many useful views.
The schema SYSMAN holds information of objects such as tables,tablespaces, views, triggers, packages, procedures, synonyms etc of target databases.
It also holds information of the host server including hardware and OS

Here i will share some of the SQLs used for generating report.

Grid control provides Dynamic Parameters of target and timeperiod. You can use this by including bind variable placeholders in your SQL statement.
For example; ??EMIP_BIND_TARGET_GUID?? - this variable will bind UID of the target for the report in your SQL statement.

1. To get Server Details
========================
Below SQL provides the hardare and OS details and present in a tabular format.


select
decode(d.rn,1,'Host Name', 2,'Domain',3,'Operating System',4,'System Arch',5,'Manufacturer',6,'Memory(MB)',7,'IP Address') as col1,
decode(d.rn,1,h.host_name,2,h.domain,3, h.os_summary, 4,h.system_config,5,h.ma, 6,h.MEM,7,p1.PROPERTY_VALUE) as col2
from MGMT$OS_HW_SUMMARY h , (select level as rn from dual connect by level <= 7) d, mgmt_targets t1, mgmt_target_properties p1
where h.TARGET_GUID=(select t.TARGET_GUID from MGMT$DB_DBNINSTANCEINFO_ALL i,MGMT$TARGET t
where t.target_name=i.host_name
and i.TARGET_GUID='2AE31523681AB4BD3F1BC404F83E979C')
and p1.PROPERTY_NAME='IP_address'
and p1.target_guid=t1.target_guid
and t1.TARGET_NAME=h.host_name
2. DB Instance Details
======================
select
decode(d.rn,1,'Database Name', 2,'Characterset',3,'Instance Name',4,'National Characterset',5,'Global Name',6,'Log Mode',7,'Version',8,'Default Temp Tblspce') as Property,
decode(d.rn,1,o.database_name, 2,o.characterset,3,o.instance_name,4,o.national_characterset,5,o.global_name,6,o.log_mode,7,o.banner,8,o.default_temp_tablespace) as Value
from mgmt$db_dbninstanceinfo o, (select level as rn from dual connect by level <= 8) d
where o.target_guid = ??EMIP_BIND_TARGET_GUID??
union
select 'DB Size(GB)', to_char(sum(FILE_SIZE)/1024/1024/1024) from MGMT$DB_DATAFILES
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??

3. SGA Details
================
select
substr(sganame,1,length(sganame)-4) as Name,
decode(substr(sganame,length(sganame)-3,4),'(MB)',sgasize*1024,sgasize) as Value
from MGMT$DB_SGA
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and sganame in ('Buffered Cache (MB)','Fixed SGA (KB)','Redo Buffers (KB)','Variable SGA (MB)')
4. Variable SGA details
=======================
select
substr(sganame,1,length(sganame)-4) as Name,
decode(substr(sganame,length(sganame)-3,4),'(MB)',sgasize*1024,sgasize) as Value
from MGMT$DB_SGA
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and sganame like '%Pool%'
5. Non Default parameters
===========================
select Name,Value from MGMT$DB_INIT_PARAMS
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??
and isdefault='FALSE'
6. Control File Details
=======================
select file_name as FileName,Status from MGMT$DB_CONTROLFILES
where TARGET_GUID=??EMIP_BIND_TARGET_GUID??

7. Intance Performace
======================
select column_label,value FROM SYSMAN.MGMT$METRIC_CURRENT
Where metric_name='instance_efficiency'
and target_guid=??EMIP_BIND_TARGET_GUID??
Above are some of the SQLs used to generate report. In addition to that you can get the details of the Redo Logs, Tablespaces, Datafiles, Invalid Objects, Patches applied, Users with power privileges etc.