Monday, October 28, 2013

How to Uninstall 11g Agent software

Get into the agent home.
aix_tst01 {oracle}:tstdb #cd $AGENT_HOME
aix_tst01 {oracle}:tstdb #pwd
/tstdb/app/oracle/omsagent/agent11g/oui/bin
Now we need to run the below command from the same directory.
aix_tst01 {oracle}:tstdb #./runInstaller -silent -deinstall "REMOVE_HOMES={/tstdb/app/oracle/omsagent/agent11g}" -removeallfiles

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16384 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-10-28_12-32-10PM. Please wait ...aix_tst01 {oracle}:tstdb #.Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Starting deinstall


Deinstall in progress (Monday, October 28, 2013 12:32:40 PM GMT+08:00)
Configuration assistant "Agent Deinstall Assistant" succeeded
Configuration assistant "Oracle Configuration Manager Deinstall" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, October 28, 2013 12:34:05 PM GMT+08:00)
End of deinstallations
Please check '/tstdb/app/oracle/oraInventory/logs/silentInstall2013-10-28_12-32-10PM.log' for more details.

ksh: .: 0403-004 Specify a parameter with this command.
aix_tst01 {oracle}:tstdb #
This will remove all the agent related bianries also it will remove the entry from Inventory too.

Fyi,

From 11g onwards Agent installatin in GUI mode is deprecated, we need to do this only with Response file.

Friday, August 16, 2013

Table Defragmentation

In this post I am going to explain the easiest method to eliminate the fragmentation on the table.

Usually in a OLTP environment, tables are often get fragmented due to many DML activities on the table. When the table are fragmented, queries on those tables will automatically get slow due to scanning many blocks which has no data on it! so how do we eliminate the fragmentation.

There are two ways to eliminate table fragmentation on a table.

 1. Shrink space compact
 2. DBMS_REDEFINITION

If the fragmented table is small in size we can go for the first option else the second option would be perfect.

In this post we are going to discuss using the SHRINK method.
consider the below table it had a huge fragmentation upto 90%!
When we use SHRINK method,
  
OWNER    SEGMENT_NAME             SEGMENT_TYPE       TBS_NAME       MBs  WASTED_MB   Wasted %
-------- ------------------------ ------------------ ------------ ----- ---------- ----------
SPSDD    KDM_SUPPLY_DEMAND_QUERY  TABLE              SPSDDDAT       290        258         90
Above output says KDM_SUPPLY_DEMAND_QUERY table sized 290M out of which 258M got wasted!

When we run any querys against this table it has to scan all the (both data & emmpty) blocks on the table i.e the whole 290M which is time consuming task, which leads to increase in execution time on the queries.

Since this is a samall table we can use SHRINK command to eleminate the fragmentation on the table.

the first step is to check whether row movement is enabled on the table which is going to reorganized, if not we need to enable it, like below
SQL> select row_movement from dba_tables where table_name='KDM_SUPPLY_DEMAND_QUERY' and owner='SPSDD';

ROW_MOVE
--------
DISABLED

SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY enable row movement;

Table altered.
Now we can rearrange used and empty blocks using below command.
  
SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space compact;

Table altered.

Elapsed: 00:04:42.77
Above command does segregating the data and empty blocks. At this time High Water Mark of this table still remains the same. DMLs are allowed in this stage.

Now, we just need to reset the HWM of this table. Be very caution in this stage because if the table being reorganized is heavily used (i.e concurrent DML activities) then Locks will be occurred which will impact the DB (if its in Prod)s. As a best practice while running this command parallely check any blocking on this session if any blocking occurred for a period of time then it should be terminated.
  
SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space;

Table altered.

Elapsed: 00:00:14.66
Shrink space command will get complete in a short period of time for smaller tables.
At this stage HWM will get reset. Now we need to rebuild indexes for this table if any.
  
SQL>  select index_name,index_type from dba_indexes where table_name='KDM_SUPPLY_DEMAND_QUERY';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
ODM_SUPPLY_DEMAND_QUERY_I1     NORMAL

SQL> alter index SPSDD.ODM_SUPPLY_DEMAND_QUERY_I1 rebuild online;

Index altered.

Elapsed: 00:00:09.27
Now gather stats on this table.
  
SQL> EXEC DBMS_STATS.gather_table_stats('SPSDD', 'KDM_SUPPLY_DEMAND_QUERY', estimate_percent => 55, cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.02
Thats it! we have successfully defragmented KDM_SUPPLY_DEMAND_QUERY table!

Below is the table info after defragment.
  
OWNER     SEGMENT_NAME             SEGMENT_TYPE  TBS_NAME      MBs  WASTED_MB   Wasted %
--------- ------------------------ ------------- ------------ ---- ---------- ----------
SPSDD    KDM_SUPPLY_DEMAND_QUERY  TABLE         SPSDDDAT       41          8         20
Table size got reduced from 290M to 41M!

All queries running against this table definitely will give better performance.

In the next post we will discuss about using DBMS_REDEFINITION method.

Monday, March 18, 2013

Upgrading ASMLIB

To upgrade ASMLIB, the system administrator can take the following steps,

Generally system administrator will do this upgrade, because DBA's will not be having any control over to perform action as a root user, so this needs to be done with the help of system admin.

The reason why I am publishing this information here is every DBA should know how ASMLIB is getting upgraded and whats happening behind the scene and to understand which circumstances this API should get upgraded.

Kindly note that this activity is just a software upgrade and no disk labels are manipulated  Also this is a local node upgrade, so in an RAC environment each node should be upgraded individually

How to do:
  1. Shutdown the ASM
  2. Execute /etc/init.d/oracleasm stop
  3. Execute rpm -Uvh oracleasm-*.rpm
  4. Execute /etc/init.d/oracleasm start
  5. Startup ASM
Whats Happening during the upgrade:

Actual upgrade happening during the 3rd step, rpm -U will perform the upgrade of all oracleasm* rpm's. Once its done you can start the service and ASM instance as well.

When to do :

All oracleasm and its dependent rpm should get upgraded to the new version, when we perform Operating system upgrade.

For example you configured ASMLIB when your O/S was in Linux 5.5 and if you go for Linux 5.8 then after the O/S upgrade sys admin should also upgrade all ASMLIB rpms to match the current O/S version otherwise you will not be able to even startup the service.

I faced the same situation couple of months back. Actually we configured ASM on one host later that host was upgraded to Linux version 5.8 once the O/S upgrade has been done system admin failed to upgrade the ASMLIB rpms, and he asked me to start the service and while starting up the service I faced below error.
oracle:+ASM@myhost /dev/oracleasm>ls -lrt
total 0

oracle:+ASM@myhost /oracle> sudo oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ARCHLOG"
Unable to instantiate disk "ARCHLOG"
Instantiating disk "LOG2"
Unable to instantiate disk "LOG2"
Instantiating disk "DATAFILE"
Unable to instantiate disk "DATAFILE"
Instantiating disk "LOG1"
Unable to instantiate disk "LOG1"
since oracleasm rpms are not upgraded I was not able to startup the service, upon checking further found none of ASM disk are visible in its default directory i.e /dev/oracleasm/disks/* when I tried to scan the disks it couldn't even initialize those disks due to its unavailability!!

Finally found rpms for ASMLIB was still in older version even after the O/S upgrade. Then sys admin upgraded those rpm then the issue got fixed.

You can use below command to find the list of rpms and its version for oracleasm utility.
oracle:+ASM@myhost /oracle>rpm -qa | grep -i oracleasm
oracleasm-support-2.1.7-1.el5
oracleasm-2.6.18-194.32.1.el5-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5

I Hope this article helped to you. I am expecting your suggestions/feedback.

Interpreting Oracleasm Utility


Overview of ASMLIB


Early in the design of ASM, Oracle decided an alternative to the standard operating system interface for device management, disk discovery and this mechanism needed to be easily integraded into ASM. The core concept was to prduce a storage management interface API, called ASMLIB.

The main objective of ASMLIB is to provide a more efficient mechanism for managing disk and I/O processing of ASM storage, it performs below functions,

  1. Device discovery 
  2. I/O processing 
  3. Performance and reliability 

Configuring ASMLIB

Once the appropriate Linux rpm files are installed on the host. The ASMLIB installs a utility in the /etc/init.d directory called oracleasm, with the help of this utility we can perform all device management activities. Now we need to configure the ASMLIB, this should be done as a root user.

In order to configure oracleasm we need to make sure below rpm are installed on the host and these rpm's are designed and developed by Oracle.

oracleasm-support-2.1.7-1.el5
oracleasm-2.6.18-194.32.1.el5-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5

Execute /etc/init.d/oracleasm script along with configure option this will setup and configure ASMLIB for the host. In RAC environment we need to execute this script in each node.

[root@myhost ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]: oracle
Default group to own the driver interface [dba]: dba
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@myhost ~]#
At the end of the installation a virtual filesystem (specific to ASMLIB) called /dev/oracleasm is created and mounted.

Managing Disk in ASMLIB 

After the storage administrator mapped new Lun to the server, it becomes available to the server and can be seen in /proc/partitions on Linux. The system admin then partitions this device using the linux fdisk utility. Once its been partitioned the new disk is sys admin handover the new disk to DBA. Then DBA can stamp the given disk into ASM using "createdisk"

In RAC environment, it allows "createdisk" command to be run on only one node and all other nodes of the RAC cluster simply need to pickup the list of disk through ASMLIB scan. This is because RAC uses shared storage architecture.

oracle:@myhost /oracle>sudo /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk:                       [  OK  ]

oracle:@myhost /oracle>sudo /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

Each disk successfully created is considered a marked ASMLIB disk and is listed in the oracleasm filesystem i.e /dev/oracleasm/disks/* this is the default disk discovery path for ASM.
User can query the disk to determine whether they are valid ASM disks.
For this use the following "oracleasm querydisk" command.

The querydisk command is applicable to both the raw device and the ASM disk.

oracle:@myhost /oracle>sudo /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk:                       [  OK  ]

oracle:@myhost /oracle>sudo /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

The disk which you are quering is not a valid ASM disk then following error will occur.
oracle:@myhost /oracle>sudo /etc/init.d/oracleasm querydisk /dev/sda1
Device "/dev/sda1" is not marked as an ASM disk

All the disks stamped using ASMLIB can be listed using the following "oracleasm listdisks" command.
oracle:@myhost /oracle>sudo /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3

Disks that are no longer needed by ASM can be dropped using "oracleasm deletedisk" command.
oracle:@myhost /oracle>sudo /etc/init.d/oracleasm deletedisk DISK1
Removing ASM disk "DISK1":                                 [  OK  ]

See also :

Upgrade ASMLIB - http://www.sivakumardba.com/2013/03/upgrading-asmlib.html

I hope this article helped to you. I am expecting your suggestions/feedback.

Monday, February 4, 2013

Adding new ASM disk into existing diskgroup.

Note : My Below demonstrations is based on SOLARIS box.

Adding a new disk into existing Diskgroup is quite simple but before that we need to check the consistency of that new disk.
Get the New ASM disk information like below. A newly created ASM disk header status will be of CANDIDATE in all unix box and PROVISONED on Linux.
Since my host is based on Unix box the header status is on CANDIDATE.

SQL> select path,os_mb/1024,header_Status from v$asm_Disk where header_Status!='MEMBER';

PATH                                               OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0  99.9941406 CANDIDATE
/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0  99.9941406 CANDIDATE
Before to add any new disk into existing diskgroup or while creating a new diskgroup, we need create a dummy diskgroup with that new disks and check If we are able to mount it successfully if we don't get any issue then the is no issue with these disk and it can be added into diskgroup.

To create dummy disk group do the following:

oracle:+ASM@soltst01 /oracle > sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 30 01:55:50 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL>select name Diskgroup,round(total_mb/1024,2) "Total_TB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup;

DISKGROUP    Total_GB    Free_GB Available%
---------- ---------- ---------- ----------
ARCHLOG            50      49.88      99.76
LOG2               10       9.68      96.87
LOG1               10       9.68      96.87
DATAFILE       299.98      41.17      13.73

SQL> select path,os_mb/1024,header_Status from v$asm_Disk where header_Status!='MEMBER';

PATH                                               OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0  99.9941406 CANDIDATE
/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0  99.9941406 CANDIDATE

SQL> create diskgroup siva_test_DG external redundancy disk '/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0','/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0';

Diskgroup created.

SQL> select name,state from v$asm_Diskgroup;

NAME          STATE
------------- --------
ARCHLOG       MOUNTED
LOG2          MOUNTED
LOG1          MOUNTED
DATAFILE      MOUNTED
SIVA_TEST_DG  MOUNTED


SQL> drop diskgroup SIVA_TEST_DG;

Diskgroup dropped.

SQL> select path,os_mb/1024,header_Status from v$asm_Disk where header_Status!='MEMBER';

PATH                                               OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0  99.9941406 FORMER
/dev/rdsk/c5t600601606F402D00BE1B9579B244E211d0s0  99.9941406 FORMER
As you can see after removing above two disks from diskgroup the disk's header status has changed to FORMER.
ASM disk with the header status of Former indicates that those disk were already part of the diskgroup and now it can be added into existing diskgroup or New diskgroup.

Now we can add that new disk into existing diskgroup with appropriate rebalance power limit.

Note: Please be caution while using value for power limit parameter. It should be given based on number of CPU available on the host.
SQL> alter diskgroup DATAFILE add disk '/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0' rebalance power 6;

Diskgroup altered.

After we issued above command we can monitor the rebalance operation by queriyng v$asm_operation view.

SQL> Select operation, state, power, est_rate, est_minutes, round((sofar/EST_WORK)*100,2) pct_comp  From v$asm_operation;

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       4912           2      16.72

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       4119           3      19.81

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       4280           2       27.3

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       4418           2       31.4

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       4477           2       37.4

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       5080           1      41.26

SQL> /

OPERA STAT      POWER   EST_RATE EST_MINUTES   PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN           6       5014           1      55.99

SQL> /

no rows selected
When it returns "no rows selected" which means that there is no re-balance activity currently in progress.
SQL>select name Diskgroup,round(total_mb/1024,2) "Total_TB",round(free_mb/1024,2) "Free_GB",round(((free_mb/total_mb)*100),2) "Available%" from v$asm_diskgroup;

DISKGROUP    Total_GB    Free_GB Available%
---------- ---------- ---------- ----------
ARCHLOG            50      49.88      99.76
LOG2               10       9.68      96.87
LOG1               10       9.68      96.87
DATAFILE       399.97     141.16      35.29

SQL> select path,os_mb/1024,header_Status from v$asm_Disk where group_number=4;

PATH                                               OS_MB/1024 HEADER_STATU
-------------------------------------------------- ---------- ------------
/dev/rdsk/c5t600601606F402D001E157973B244E211d0s0  99.9941406 MEMBER
/dev/rdsk/c5t600601606F402D0020157973B244E211d0s0  99.9941406 MEMBER
/dev/rdsk/c5t600601606F402D0022157973B244E211d0s0  99.9941406 MEMBER
/dev/rdsk/c5t600601606F402D00C01B9579B244E211d0s0  99.9941406 MEMBER
After we add the new disk DATAFILE diskgroup capacity got increased and header status of that new disk also got changed to MEMBER!

I Hope this article helped to you. I am expecting your suggestions/feedback.