Oracle Advanced Compression

Oracle Advanced Compression and Oracle Database 11g Release 2 helps manage more data in a cost-effective manner. With data volumes, on average, tripling every two years, Oracle Advanced Compression delivers compression rates of 2-4x across all types of data and applications.storage savings from compression will cascade throughout the data center, reducing network traffic and data backups as well. And by reading fewer blocks off disk, Oracle Advanced Compression also improves query performance.

Oracle Advanced Compression is an option of the Oracle 11g database (separately licensed) that allows data in the database to be compressed. Oracle Advanced Compression offers the following advantages:

1.) OLTP Compression  : It allows structured and unstructured data to be compressed on insert,update and delete  operations.The following are features : 

• New compression algorithm uses deferred or batched approach
• Data is inserted as is without compression until  PCTFREE  value is reached.
• Compression of data starts once PCTFREE threshold is reached
• Can be enabled at  table, partition or tablespace level
• No need of decompressing the data during reads
• Recommended for low update activity tables

2.) Data Pump Compression   : In Data Pump, the compression of metadata was introduced in 10g and compression of "data" was introduced in 11g.This covers the following features : 

• Both are Inline operation
• Save on storage allocation
• No need to uncompress before Import
• Implemented with COMPRESSION attribute, Values supported are ALL, DATA_ONLY, METADATA_ONLY . 

3.) Data guard Compression : It includes the following features : 

• Redo is compressed as it is transmitted over a network .
• Helps efficiently utilize network bandwidth when data guard  is across data centers
• Faster re-synchronization of Data guard  during gap resolution.
• Recommended for low network bandwidth .
• Implemented with attribute “COMPRESSION”  of initialization parameter log_archive_dest_n

.4.) RMAN Backup Compression  : It compresses the RMAN backups.The followinf features are

• Supports compression of backups using "ZLIB"  algorithm .
• Faster compression and low CPU utilization compared to default BZIP2 (10g) .
• Low compression ratio  compared to BZIP2 .
• Implement  with  CONFIGURE COMPRESSION ALGORITHM  ‘value’ command where value can be High , Medium(ZLIB) and Low(LZO) .

The Oracle Database 11g Advanced Compression option introduces a comprehensive set of compression capabilities to help customers maximize resource utilization and reduce costs. It allows IT administrators to significantly reduce their overall database storage footprint by enabling compression for all types of data – be it relational (table), unstructured (file), or backup data . Although storage cost savings are often seen as the most tangible benefit of compression, innovative technologies included in the Advanced Compression Option are designed to reduce resource requirements and technology costs for all components of our IT infrastructure, including memory and network bandwidth .

The benefits of compression are manyfold
1.) Reduction of disk space used for storage .
2.) Reduction in I/O bandwidth requirements .
3.) Faster full table scans .
4.) Lower server memory usage.


Enjoy    J J J

Change Database Character Set using CSSCAN

CSSCAN (Database Character Set Scanner) is a SCAN tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls_characterset setup. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

Before altering the character set of a database, check the convertibility of the data before converting. Character set conversions can cause data loss or data corruption. The Character Set Scanner utility provides the below two  features: 

1.) Convertibility check of existing data and potential issues. The Scanner checks all character data in the database including the data dictionary and tests for the effects and problems of changing the character set encoding (characterset). At the end of the scan, it generates a summary and exception report of the database scan.

2.) Csscan allows  also us to do a check if there is no data in the database that is  incorrectly stored.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. Each and every character in the current character set is available in the new character set and has the same code point value in the new character set.

With the strict superset criteria in mind, only the metadata is converted to the new character set by the CSALTER script, with the following exception: the CSALTER script performs data conversion only on CLOB columns in the data dictionary and sample schemas that have been created by Oracle. CLOB columns that users have created may need to be handled separately

Note :  it's possible to run Csscan from a client, but this client needs to be the same base version as the database home.(i.e, oracle 11g server need oracle 11g client) .

To change the database character set, perform the following steps :


STEP 1 : Remove the invalid objects and purge the recyclebin , then take a  full backup of the database.

STEP 2 : Install the CSS utility if not install . We will get error css-00107 if css utility is not install .Install the CSS utility by running the csminst.sql script which is found in $ORACLE_HOME\rdbms\admin .

STEP 3 : Run the Database Character Set Scanner utility as
set the oracle_sid and run as
CCSSCAN  /AS SYSDBA  FULL=Y


STEP 4 : Run the CSALTER script.This script is in $ORACLE_HOME\rdbms\admin  folder .
i.>   shut down 
ii.>  startup restrict 
iii.> @csalter.plb
iv.> shut immediate
v.>  startup  

Note :  

i.> The CSALTER script does not perform any user data conversion. It only changes the character set metadata in the data dictionary.Thus, after the CSALTER operation, Oracle will behave as if the database was created using the new character set.

ii.> Changing the database characterset is not an easy task . It is quite tricky tasks and may face errors which need the oracle support . So,it's better to raise as SR for this task and involve the  oracle support .  

There  are no of links on meta-link .Check the below meta-links .

Note: 555823.1 – Changing US7ASCII or WE8ISO8859P1 to WE8MSWIN1252
Note: 444701.1 – Csscan output explained
Note: 745809.1 – Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner)
Note: 225912.1 – Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]




Enjoy   J J J

Append Date & time to File name in Window Batch file

Sometimes it may be required to schedule the same task twice a days . In such cases, we have to create two batch file and scheduling them separately because the generated name may overwrites or throws errors . To overcome from this issue , we can schedule the same task with single batch file. Suppose we have to take the logical backup of a schema(say scott) twice a days . We can do this by simply appending the date and time to the dump file name .

As in case of Linux /Unix systems, shell scripting is very liberal with variables and we can define according to ourself . e.g;
expdate=`date ‘+%d%m%Y’`
dat=`date ‘+%m%d%y %H:%M:%S’`

And then go onto define in our script as

./expdp system/xxxx  dumpfile=scott_$expdate.dmp logfile=scott_log_$expdate.log schemas=scott

But on windows machine ,we use the Date and Time function. The Date and Time function are as below  :

Date:  %date:~4,2%-%date:~7,2%-%date:~12,2%
Time:  %time:~0,2%-%time:~3,2%-%time:~6,2%  

This above function can be use to generate the unique dumpfile name. Below is the Demo of this function .

c:\> exp system/ramtech@noida  owner=scott  file=c:\scott_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp log=c:\scottlog_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~3,2%-%time:~6,2%.log    

The file name generated  i.e, dump file name is 'scott_12-22-11-12-04-31.dmp'  and log file name is 'scottlog_12-22-11-04-31.log'    where date is 12-22-11(12th-dec-2011) and time is 12-04-31(12hr:4min:31sec) .

Enjoy    J J J

What is CLOUD computing ??

I have read a  article on Cloud Computing and decided to post it. This article is written by one of the famous s/w engineer Gagan Kundra .

Cloud computing is a marketing term for technologies that provide computation, software, data access, and storage services that do not require end-user knowledge of the physical location and configuration of the system that delivers the services. A parallel to this concept can be drawn with the electricity grid, wherein end-users consume power without needing to understand the component devices or infrastructure required to provide the service.

Apple CEO Steve Jobs whenever someone comes on stage, the world is alarmed. Who let this time show little device that will change people's lives. Why is Apple the company, who gave us a iPod, Macintosh and iPhone are such tools. Jobs done something like this again. The only difference was that he did not have any tools. Just talked about a similar service to the people on the entire data and information from different devices can. Aiclaud Apple is the name of the service. But it's not actually Apple, but shows the power of cloud computing. Service's name may be, whether the service to be Apple, or Google, or Microsoft, but cloud computing in the coming days will sputter. Tech - pundits say that it will change our lives. Companies like Infosys have been predicted that almost all the work in the coming days the world will be on clouds. Just remember that no water in these clouds, but digital data - like - is filled with such information and other associated material. And the clouds in the sky, but on a giant computer - the server is called - are found.

Cloud computing is getting to be too much nowadays, but it is not a new thing. If you are on the Internet, so that - unconsciously you will also have to use it. I can not believe the e-mail as simple as taking the example of an Internet service. Most people e-mail on your computer, do not download, but leave it on the internet to see. Who your computer's hard drive to the e-mail wants to fill? But you never thought that this e-mail that you have left over the Internet or the Web, Where saving is kept, so you whenever you want your e-mail account, going to see it. The answer is simple - to cloud. You probably do not know, but nearly every time you leave something on the web, the data contained on these clouds. Whether it be a picture on Facebook, you - a video on the tube, or a new article on your blog. If he left you on the web, you are using cloud computing. You will have noticed that you leave the stuff on the internet, on any computer or cell phone or a tablet-like device can see. After all, your e-mail or Facebook Most office and home computer or your phone may get. This is the greatest power of cloud computing and specialty. It kept you on the Internet, its content or data from anywhere, anytime viewing and gives the ability to change.

 A few years ago was meant to use any software to download it to your computer or cellphone. Then the software could only be played on computers or cell phones. Today, via cloud computing could use the same software, with nothing to download, virtually any device - phone, tablet, computer or even from television. Consider that the cloud has taken the place of your computer. Now, Google's Google Docs, which runs entirely on the internet and gives you almost the same features you get with Microsoft Office, there is no need to download. It will remain on the files and documents, you can look at any computer or cellphone. and Google Docs is just an example. Dozens of world power and benefits of cloud computing companies trying to use it are given. Amazon's cloud service to their customers, keep your favorite songs have the chance. 

Microsoft has put many products on the cloud and its famous office software is also put on some cloud. And now Apple has also stepped into cloud computing. Aiclaud service people like him - look at the software and information and to use the facility will offer many tools. Interestingly, many are free of cloud computing service. To use Google Docs and Aiclaud you do not have light pockets at all. like - like an increasing number of software will run on the web, anyway - so we will run the digital cloud. Our work on certain devices, but will depend on our Internet connection. The Internet will be able to do or where we will be able to satisfy yourself. Whether we are or tablet computer, or cell phone, or TV, or game console, or to connect to the Internet even if the clock. Your work and be entertained, where would the internet. He will not be tied to a device. 

Enjoy    J  J J

Estimate Tablespace Growth In Oracle

I have already posted the script for tracking the growth of the Database .Sometimes it may be requires to find the growth of  each tablespace  . I  have google and find the script for tracking the growth of  each tablespace with date-wise. I thanks Hasan Shaharear  for sharing such a good scripts. This scripts donot shows the growth of system and sysaux tablespace growth rate. Below is the script .

SQL> set heading on
SQL> set linesize 5500
SQL> SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
  2  , ts.tsname
  3  , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
  4  , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
  5  FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
  6  , DBA_HIST_TABLESPACE_STAT ts
  7  , DBA_HIST_SNAPSHOT sp
  8  , DBA_TABLESPACES dt
  9  WHERE tsu.tablespace_id= ts.ts#
 10  AND tsu.snap_id = sp.snap_id
 11  AND ts.tsname = dt.tablespace_name
 12  AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 13  GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 14  ORDER BY ts.tsname, days;

DAYS                 TSNAME        CUR_SIZE_MB       USEDSIZE_MB
-------------      ---------       -------------     ---------------
07-12-2011         SDE                         2448              1813.94
08-12-2011         SDE                         2448              1813.94
09-12-2011         SDE                         2448              1813.94
10-12-2011         SDE                         2448              1813.94
11-12-2011         SDE                         2448              1815.94
12-12-2011         SDE                         2448              1815.94
13-12-2011         SDE                         2448              1816
14-12-2011         SDE                         2448              1816
15-12-2011         SDE                         2448              1816
16-12-2011         SDE                         2448              1816
07-12-2011         UNDOTBS1                4950              196.56
08-12-2011         UNDOTBS1                4950              302.56
09-12-2011         UNDOTBS1                4950              427.63
10-12-2011         UNDOTBS1                4950              348.56
11-12-2011         UNDOTBS1                6210              5317
12-12-2011         UNDOTBS1                6210              532.5
13-12-2011         UNDOTBS1                6210              388.56
14-12-2011         UNDOTBS1                6210              422.5
15-12-2011         UNDOTBS1                6210              585.63
16-12-2011         UNDOTBS1                6210              479.5
07-12-2011         USERS                      20480           19941.88
08-12-2011         USERS                      20480           20001.5
09-12-2011         USERS                      20480           20341.56
10-12-2011         USERS                      20480           20467.25
11-12-2011         USERS                      20480           20336.69
12-12-2011         USERS                      20480           20317.13
13-12-2011         USERS                      20480           20267.31
14-12-2011         USERS                      20480           20346.13
15-12-2011         USERS                      20480           20340.06
16-12-2011         USERS                      20480           20330.81
30 rows selected.


Enjoy    :-)