Archive

Archive for March, 2012

Oracle tablespace usage and free in MB and % used

March 27, 2012 Leave a comment

select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) “Used (MB)”,
ROUND(b.BYTES/1024000) “Free (MB)”,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) “% USED”
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from    dba_free_space
group   by TABLESPACE_NAME
)
b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like ‘%’
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

If you get any errors running this script, check the ” ” quotes were properly cut and pasted.

Categories: Oracle

Perl test program for pattern matching for programmer

March 27, 2012 Leave a comment

#!/usr/bin/perl
while () {
chomp;
if (/YOUR_PATTERN_GOES_HERE/) {
print “Matched: |$`$’|\n”; # Mystery code!
} else {
print “No match.\n”;
}
}

Categories: Linux, Perl

How to add extend a vg and extend your logical volume online RHEL 5

March 26, 2012 1 comment

Using LVM is the best option for flexibility in maintenance on filesystems.

In this example, I already have vg01 created. Now, I will allocate a new LUN from the SAN storage. After it is presented to the host, you can dynamically scan for the new device. (Reference: https://willsnotes.wordpress.com/2011/01/29/dynamically-scan-your-luns-for-new-hosts-without-reboot/ )

Run fdisk to see the new disk:
# fdisk -l

Disk /dev/sda: 146.1 GB, 146163105792 bytes
255 heads, 63 sectors/track, 17769 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 1 5 40131 de Dell Utility
Partition 1 does not end on cylinder boundary.
/dev/sda2 * 6 267 2097152 b W95 FAT32
Partition 2 does not end on cylinder boundary.
/dev/sda3 268 279 96390 83 Linux
/dev/sda4 280 17769 140488425 5 Extended
/dev/sda5 280 17769 140488393+ 8e Linux LVM

Disk /dev/sdc: 322.1 GB, 322122547200 bytes
255 heads, 63 sectors/track, 39162 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn’t contain a valid partition table

Disk /dev/sde: 322.1 GB, 322122547200 bytes
255 heads, 63 sectors/track, 39162 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sde doesn’t contain a valid partition table

In this case, sde is the new device. Now, initialize the physical volume for LVM usage:
# pvcreate /dev/sde
Writing physical volume data to disk “/dev/sde”
Physical volume “/dev/sde” successfully created

Next, extend vg01 volume group with the new physical volume:
# vgextend vg01 /dev/sde
Volume group “vg01” successfully extended

Now, take a look at vginfo to see more “Free PE” physical extents available.
# vgdisplay -v vg01
Using volume group(s) on command line
Finding volume group “vg01”

— Volume group —
VG Name vg01
System ID
Format lvm2
Metadata Areas 2
Metadata Sequence No 3
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 1
Open LV 1
Max PV 0
Cur PV 2
Act PV 2
VG Size 599.94 GB
PE Size 32.00 MB
Total PE 19198
Alloc PE / Size 9599 / 299.97 GB
Free PE / Size 9599 / 299.97 GB
VG UUID KfcbNP-oT11-elBN-dqv5-g4et-0HMA-TfHK1F

— Logical volume —
LV Name /dev/vg01/lvol1
VG Name vg01
LV UUID G0Tb24-E8Le-gZfu-1vRV-SlOy-SIJF-nt0vJv
LV Write Access read/write
LV Status available
# open 1
LV Size 299.97 GB
Current LE 9599
Segments 1
Allocation inherit
Read ahead sectors auto
– currently set to 256
Block device 253:2

— Physical volumes —
PV Name /dev/sdc
PV UUID 8fNwun-9cvK-aNC0-HKrO-cBt7-O0It-46X7Lx
PV Status allocatable
Total PE / Free PE 9599 / 0

PV Name /dev/sde
PV UUID K6cbAV-XrV8-MtX7-oS0b-hkMM-3Dvc-x1jFcG
PV Status allocatable
Total PE / Free PE 9599 / 9599

Next, extend the logical volume.
# lvextend -l +100%FREE /dev/vg01/lvol1
Extending logical volume lvol1 to 599.94 GB
Logical volume lvol1 successfully resized

Finally, resize the filesystem online.
# resize2fs /dev/vg01/lvol1
resize2fs 1.39 (29-May-2006)
Filesystem at /dev/vg01/lvol1 is mounted on /oradata; on-line resizing required
Performing an on-line resize of /dev/vg01/lvol1 to 157270016 (4k) blocks.
The filesystem on /dev/vg01/lvol1 is now 157270016 blocks long.

Run “df” and you should see your mount point increase by the extended size.

Categories: Linux, Redhat, Uncategorized

How to tell if you linux server is running on a VM host

March 18, 2012 1 comment

To do this, ensure you have the package virt-what. Virt-what supports identification for most of the hypervisors, and in my example, VMware. If you don’t have the package, install it using yum or any other method.

# yum install virt-what

To use it, simply run:
# virt-what
vmware

If nothing appears, most likely you are running on a bare-metal system or the hypervisor is not supported (unlikely). Most companies nowadays use VMware, Xen, or Hyper-V.

Upgrading Oracle 11.2.0.2 Database to 11.2.0.3

March 14, 2012 1 comment

Environment: Oracle 11.2.0.2 database on RHEL 5.8 x86_64
Objective: Oracle 11.2.0.3 database

First, download 11.2.0.3 software from Oracle into a set directory. You man also download Pre-Ugrade script from support.oracle.com, utlu112i_3.sql.

Next, verify your environment prior to the upgrade, as Oracle user:
$env|grep ORACLE
ORACLE_SID=oraopt
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1
$cat /etc/oratab | grep -v ^#
oraopt:/u01/app/oracle/product/11.2.0/db1:N

The utlu112i_3.sql script may be run when the database is operational.
$sqlplus ‘/as sysdba’
SQL>spool upgrade_11.2.0.3.log
SQL>@utlu112i_3.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 03-14-2012 16:19:44
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
–> name: ORAOPT
–> version: 11.2.0.2.0
–> compatible: 11.2.0.0.0
–> blocksize: 8192
–> platform: Linux x86 64-bit
–> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 709 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 496 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
–> If Target Oracle is 32-Bit, refer here for Update Parameters:
— No update parameter changes are required.
.

–> If Target Oracle is 64-Bit, refer here for Update Parameters:
— No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
— No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> OLAP Analytic Workspace [upgrade] VALID
–> OLAP Catalog [upgrade] VALID
–> EM Repository [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Spatial [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
–> Oracle Application Express [upgrade] VALID
… APEX will only be upgraded if the version of APEX in
… the target Oracle home is higher than the current one.
–> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Your recycle bin is turned on and currently contains no objects.
…. Because it is REQUIRED that the recycle bin be empty prior to upgrading
…. and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
…. prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: –> Database contains schemas with objects dependent on DBMS_LDAP package.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),’ ‘)) FROM sys.v$parameter2
WHERE UPPER(name) =’EVENT’ AND isdefault=’FALSE’

Trace Events:
SELECT (translate(value,chr(13)||chr(10),’ ‘)) from sys.v$parameter2
WHERE UPPER(name) = ‘_TRACE_EVENTS’ AND isdefault=’FALSE’

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL> exit

Now, begin install new Oracle home, followed by upgrade. You should now be in the location of 11.2.0.3 sofware of database directory.
$./runInstaller &
When dialogue asks, specify “Install Database Software only” and change your location of installation to:
/u01/app/oracle/product/11.2.0/db2

Run through the rest of the installation till completion and run the root.sh at the end.

Now, begin Upgrade portion:
$export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db2
$export PATH=$ORACLE_HOME/bin:$PATH
$which dbua
/u01/app/oracle/product/11.2.0/db2/bin/dbua
./runInstaller

Dialogue will now ask you which db to upgrade. Select your db and click Next. There will be warnings given similar to the Pre-Ugrade script. Take care of those warnings prior to the upgrade. You should back up your db before the upgrade as well, in case there are any problems. There is an opportunity to do so in the GUI. DBUA will shutdown the database server and do what is necessary for the upgrade.

When upgrade is done, click OK, and Upgrade Results will be shown. Review the summary and click Close. The output concludes the upgrade process:
Database upgrade has been completed successfully, and the database is ready to use.
The following document describes important behavioral changes from previous database releases:
/u01/app/oracle/product/11.2.0/db2/assistants/dbua/doc/DefaultBehaviorChanges.html

Note, the db will have been restarted for you. Make sure your “opatch” is of the lastest version as well. Ensure your .bash_profile, .profile is now updated with the new ORACLE_HOME. Verify /etc/oratab also shows your new ORACLE_HOME:
oraopt:/u01/app/oracle/product/11.2.0/db2:N

$ which opatch
/u01/app/oracle/product/11.2.0/db2/OPatch/opatch

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

Categories: Linux, Oracle, Redhat