How to Reconfigure OEM in Oracle 10g on Linux

We generally find that the OEM is not working due to the various reason . The best approach to solve this issue is to drop the OEM and reconfigure it . OEM may not work for various reason . In my case , I have install the oracle 10g on my system and then changed the computer host name . This leads  the OEM not working . The following are the steps to configure the OEM  : 

Step 1 :  Change IP to Machine name : 

i.)  Change the $ORACLE_HOME/network/admin/listener.ora file from an IP number to machine name.

ii.) Change the $ORACLE_HOME/network/admin/tnsnames.ora file from an IP number to a machine name.

Step 2 : Change the computer name : 

Open the network file and replace with new HOSTNAME . (in my case, I have changed from localhost to Ramtech.com) . 

[root@Ramtech ~]# vi /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=Ramtech.com


save the changes and exit vi. Now edit the /etc/hosts file and set new host name . In hosts file edit the line and replace it with new one as
[root@Ramtech ~]# vi /etc/hosts
127.0.0.1          localhost
192.168.80.131   Ramtech.com

save and exit vi. The changes to /etc/hosts and /etc/sysconfig/network are necessary to make our changes persistent . Finally, we will restart the network to apply the changes we made to above file .

[root@Ramtech ~]# service network restart
Shutting down interface eth0:                              [  OK  ]
Shutting down loopback interface:                       [  OK  ]
Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:                                   [  OK  ]

Now we use the hostname command to check the changes :
[root@Ramtech ~]# hostname
Ramtech.com

Step 3 : Drop the em configuration : 
First , we will drop the existing console and repository .

[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca  -deconfig  dbcontrol  db  -repos  drop

STARTED EMCA at Jan 8, 2012
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for SYS user:

Do you wish to continue? [yes(Y)/no(N)] :  y 
 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-52-43-PM.log.
Jan 8, 2012 5:55:32 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jan 8, 2012 5:55:33 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jan 8, 2012 5:56:52 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 8, 2012 5:56:59 PM

Step 4 :  Drop the user and dependent :
If any of the below objects exists ,then we will face error , so before creating the OEM drop the following :

SQL> drop  user  SYSMAN   cascade; 
SQL> drop  user  MGMT_VIEW ; 
SQL> drop  role MGMT_USER ; 


and finally drop all the dependent synonyms of  "sysman" schema .

SQL> spool drop_synonym.sql 
SQL> SELECT  'drop public synonym '  || synonym_name || ';'   FROM dba_synonyms  WHERE    table_owner = 'SYSMAN';
SQL> @drop_synonym.sql 

Step 5 : Re-create the OEM : 

[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Jan 8, 2012 5:58:50 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/product/10.2.0/db_1
Database hostname ................ Ramtech.com
Listener port number ................ 1521
Database SID ................ noida
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y 
Jan 8, 2012 5:59:31 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-58-49-PM.log.
Jan 08, 2012 06:01:30 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jan 08, 2012 06:17:38 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 08, 2012 06:18:50 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 08, 2012 06:23:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 08, 2012 06:23:02 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://Ramtech.com:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 08, 2012 06:23:02 PM

Step 6 :  Start and stop the dbconsole : 

$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

Note :  The same steps are used in window also. Sometimes we cannot able to recreate the oem(in case of window) in such case delete the  instance(using oradim) and again create the instance (using oradim) and then try to configure the OEM .

Enjoy     J J J

A Very Very Happy New Years To U All

Receive my simple gift of LOVE

Wrapped with SINCERITY

Tied with CARE &

Sealed with BLESSINGS

2 Keep u HAPPY & SAFE all the life long.

Have a lucky and wonderful 2012

A Happy New Year! Grant that I

May bring no tear to any eye

When this New Year in time shall end

Let it be said I’ve played the friend

Have lived and loved and labored here

And made of it a happy year.

My wishes for you, great start for Jan,

Love for Feb, peace for March,

No worries for April, fun for May,

Joy for June to Nov,happiness for Dec.

Have Fun and Enjoy  

J J J

How to Exclude Tablespace from Rman Backup ?

Sometimes we  may want to omit a  specified  tablespace  from  part of  the regular  backup schedule. Suppose  in  a tablespace  the data don't change  or  the tablespace  contains test  data only or  sometimes  a scenario may occur when we are  clonning  the database  using   the  rman backup where we  do not  need all  the schemas . In such cases, either we  might change our  backup  strategy or  skip the certain tablespace  in  the database. 

To overcome this type of issue, we can configure the exclude option to exclude the specified tablespace from the Backup Database command . Though we can generally skip the tablespace during the Backup Database command but only when the tablespace is offline or readonly .The exclusion condition applies to any data files that we add to this tablespace in the future. Below are the steps to configure the Exclude  option 

C:\> rman  target  /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 28 18:58:05 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: NOIDA (DBID=1523131116)


RMAN> configure exclude for tablespace "EXAMPLE" ;
Tablespace EXAMPLE will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

To check this parameter , use the below command 

RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name NOIDA are:
CONFIGURE EXCLUDE FOR TABLESPACE 'EXAMPLE';

If  "exclude" option is configured ,  even then we can backup the excluded tablespsace by explicitly specifying them in a Backup command or by specifying the NOEXCLUDE option on a "Backup Database" command as 

RMAN> backup database noexclude ;

or
RMAN> backup tablespace example ;
Starting backup at 29-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=C:\APP\NEERAJS\ORADATA\NOIDA\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 29-DEC-11
channel ORA_DISK_1: finished piece 1 at 29-DEC-11
piece handle=E:\RMAN_BACKUP\0UMVEC3G_1_1 tag=TAG20111229T150832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-DEC-11

We can disable the exclusion feature tablespace example as : 

RMAN> configure exclude for tablespace example clear ;
Tablespace EXAMPLE will be included in future whole database backups
old RMAN configuration parameters are successfully deleted


RMAN> show exclude ;
RMAN configuration parameters for database with db_unique_name NOIDA are :
RMAN configuration has no stored or default parameters


In order to skip  READONLY  and  OFFLINE  tablespace we can issue backup database command as,
RMAN>backup database skip readonly, skip offline ;


Enjoy       :-)

Configure Rman Backupset Compression

RMAN compresses the backup set contents before writing them to disk. No extra uncompression steps are required during recovery when we use RMAN compression. RMAN has two types of compression:  

1.) Null Compression     and
2.) Unused Block Compression

1.) Null Compression :  When backing up datafiles into backup sets, RMAN does not back up the contents of data blocks that have never been allocated. This means RMAN will never backup the blocks that are ever used. For example: We have a tablespace having one datafile of size 100MB and out of 100MB only 50 MB is used. Then RMAN will backup only 50MB. 

2.) Unused Block Compression :  RMAN skips the blocks that do not currently contain data and this is called Unused Block Compression. RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. No extra action is required on the part of the DBA to use this feature. 

Example :  We  have a tablespace having one datafile of size 100MB and out of 100MB, 50MB is used by the user tables. Then user dropped a table belonging to that tablespace which was of 25MB, with Unused Block Compression only 25MB of the files is backed up. In this example if null compression is used then it would have backed up 50MB because Null Compression will consider the blocks that are formatted/ever used. 

Binary Compression : Binary Compression can be done by specifying "AS COMPRESSED" clause in backup command, this compression is called as binary compression. RMAN can apply a binary compression algorithm as it writes data to backup sets. This compression is similar to the compression provided by many tape vendors when backing up data to tape. But we cannot give exact percentage of compression. This binary compression algorithm can greatly reduce the space required for disk backup storage. It is typically 2x to 4x, and greater for text-intensive databases.  The command to take the compressed backup :

RMAN> backup as compressed backupset database ;


There is no special command to restore database from the compressed backupsets. The restore command will be the same as with uncompressed backups.The restore from the compressed backpuset will take more time than uncompressed backupsets.
To use rman compression option, we can run the following RMAN commands to configure compression 


RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
followed by ..
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’ ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’ ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’ ; 
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ ; 

Oracle 11g added several compression algorithms to compress data. They can be used for compressing tables, LOBs , compressed data pump exports or even RMAN backups. Unfortunately for some compression algorithms we need to purchase the “Advanced Compression Option”. The following table lists the available RMAN compression options, the most likely compression algorithm being used and states if an additional license is required:

The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup throughput and the degree of compression afforded. If we have enabled the Oracle Database 11g Release 2 Advanced Compression Option, then we can choose from the following compression levels :

• HIGH - Best suited for backups over slower networks where the limiting factor is network speed
• MEDIUM - Recommended for most environments. Good combination of compression ratios and speed
• LOW - Least impact on backup throughput and suited for environments where CPU resources are the limiting factor.

Note:  The compression ratio generally increases from LOW to HIGH, with a trade-off of potentially consuming more CPU resources.

We can check the compression level by using the command .


SQL> select  *  from V$RMAN_COMPRESSION_ALGORITHM;
Output : 

I found a good scenario on net related to compression level having statistics about the this compression level . Here is the scenario : The environment being used was a freshly created 11g Release 2 database with some smaller tables in it. The total sum of all segments equals to 4.88 GB. All database data files excluding the temporary ones are 7.3 GB total. Excluding temporary and undo data files total size equates to 5.9 GB.


Here is the test results displays of the compression level :
Test results

As we  can see  from the  table  HIGH compression  does an  incredibly high  load on the  machine and  take extremely long but produces the smallest backup set size.Surprisingly BASIC compression (which is available without advanced compression license) does a good job as well and produces the second smallest backup set but takes nearly as long as doing uncompressed backups. But in other environment with faster CPUs this will change . 

In the test environment used either LOW or MEDIUM compression seems to be the best choice. Due to the fact MEDIUM produces a approx. 15% smaller backup set but taking only a few seconds more time to complete i would rank MEDIUM on 1st and LOW on second.

Finally we came to the conclusion that stronger the compression the smaller the backup size but the more CPU-intensive the backup is. If we do not have the advanced compression license BASIC compression will produce reasonable compression rates at moderate Load. If  we have the licence we have a lot more options to suit our needs.

If we want to test and optimize our rman backup, we basically have three major switches to play with :

• compression algorithmn
• rman parallelism and
• data transfer mechanism (SAN or Ethernet [this includes: iSCSI, NFS, CIFS, Backup to tape over Ethernet])

Enjoy     J J J

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