Monday, October 27, 2014

Introduction to mongoDB

Introduction to mongoDB
MongoDB is a document database that provides high performance, high availability, and easy scalability.
or
MongoDB is an open-source document database that provides high performance, high availability, and automatic scaling.

-1-Document Database
A record in MongoDB is a document, which is a data structure composed of field and value pairs. MongoDB documents are similar to JSON objects. The values of fields may include other documents, arrays, and arrays of documents.
A MongoDB document.
A MongoDB document.
--The advantages of using documents are:
Documents (objects) map nicely to programming language data types.
Embedded documents and arrays reduce need for joins.
Dynamic schema makes polymorphism easier.


-2-High Performance
Embedding makes reads and writes fast.
Indexes can include keys from embedded documents and arrays.
Optional streaming writes (no acknowledgments).

i.e MongoDB provides high performance data persistence. In particular,
-Support for embedded data models reduces I/O activity on database system.
-Indexes support faster queries and can include keys from embedded documents and arrays.


-3-High Availability
Replicated servers with automatic master failover, 
i.e To provide high availability, MongoDB’s replication facility, called replica sets, provide:
--automatic failover.
--data redundancy.
A replica set is a group of MongoDB servers that maintain the same data set, providing redundancy and increasing data availability.

-4-Easy Scalability( or automatic scaling)
Automatic sharding distributes collection data across machines.
MongoDB provides horizontal scalability as part of its core functionality.
Eventually-consistent reads can be distributed over replicated servers. i.e Replica sets can provide eventually-consistent reads for low-latency high throughput deployments.

-5-Advanced Operation
With MongoDB Management Service (MMS) MongoDB supports a complete backup solution and full deployment monitoring.


#~~#Key MongoDB Features
MongoDB focuses on flexibility, power, speed, and ease of use:
--Flexibility
MongoDB stores data in JSON documents (which we serialize to BSON). JSON provides a rich data model that seamlessly maps to native programming language types, and the dynamic schema makes it easier to evolve your data model than with a system with enforced schemas such as a RDBMS.
--Power
MongoDB provides a lot of the features of a traditional RDBMS such as secondary indexes, dynamic queries, sorting, rich updates, upserts (update if document exists, insert if it doesn’t), and easy aggregation. This gives you the breadth of functionality that you are used to from an RDBMS, with the flexibility and scaling capability that the non-relational model allows.
--Speed/Scaling
By keeping related data together in documents, queries can be much faster than in a relational database where related data is separated into multiple tables and then needs to be joined later. MongoDB also makes it easy to scale out your database. Autosharding allows you to scale your cluster linearly by adding more machines. It is possible to increase capacity without any downtime, which is very important on the web when load can increase suddenly and bringing down the website for extended maintenance can cost your business large amounts of revenue.
--Ease of use
MongoDB works hard to be very easy to install, configure, maintain, and use. To this end, MongoDB provides few configuration options, and instead tries to automatically do the “right thing” whenever possible. This means that MongoDB works right out of the box, and you can dive right into developing your application, instead of spending a lot of time fine-tuning obscure database configurations.

#~~#Operations:
--MongoDB is a server process that runs on Linux, Windows and OS X. It can be run both as a 32 or 64-bit application. We recommend running in 64-bit mode, since MongoDB is limited to a total data size of about 2GB for all databases in 32-bit mode.
--The MongoDB process listens on port 27017 by default (note that this can be set at start time - please seemongod options for more information).
--Clients connect to the MongoDB process, optionally authenticate themselves if security is turned on, and perform a sequence of actions, such as inserts, queries and updates.
--MongoDB stores its data in files (default location is /data/db/), and uses memory mapped files for data management for efficiency.
--MongoDB can also be configured for data replication.
--Additionally the MongoDB Management Service (MMS) application for managing MongoDB clusters using a simple user interface. MMS provides backup and monitoring. MMS is available to all users in the cloud and on-premises as part of MongoDB Standard and Enterprise Subscriptions.

Wednesday, August 20, 2014

MongoDB Binary Installation on Linux(Red Hat Enterprise Linux, CentOS Linux, Fedora Linux, or a related system)

MongoDB Binary Installation on Linux(Red Hat Enterprise Linux, CentOS Linux, Fedora Linux, or a related system)
                Created By : Amit Gera DBA(MongoDB & MySQL & ORACLE)
Goal:MongoDB Standard Binary Installation on Linux 64 bit through rpm package.

Partition Requirement:-
/mongo_admin:-   Will contains mongo binaries and confirguration file
/mongo_backup:- Will contains mongo backup [i.e dumpfile or export files or import files]
/mongo_data:-     Will contains data files
/mongo_home:-   Will contains profiles , scripts etc
/mongo_logs:-     Will contains log files [i.e trace ,alert & audit logfiles]

Note:-Here I have use name mongoservice_script wrt service name ,As per guidelines we must set it to meaningful name i.e product service name

Prerequisites:-http://myora-dba-notes.blogspot.com/2014/08/mongodb-distribution-formats-different.html

#~~#Step 1:-Download mogodb packages for linux
a.Create downloading destination directory
cmd:-mkdir -p /mongo_admin/mongo_binaries/download
b.Start Downloading at destination directory
If internet is available then use this cmd:-
cd /mongo_admin/mongo_binaries/download/
curl -O  http://downloads.mongodb.org/linux/mongodb-linux-x86_64-2.6.3.tgz
wget -O /mongo_admin/mongo_binaries/download/mongodb-linux-x86_64-2.6.3.tgz  http://downloads.mongodb.org/linux/mongodb-linux-x86_64-2.6.3.tgz

Else download this from other sources and copy here

#~~#Step 2:-
a.Create group and create user in that group for mongo
cmds:-
groupadd mongo_grp
useradd -r -g mongo_grp mongo_user
b.Create directory mention in below configuration file of step 6
cmds:-
mkdir -p /mongo_data/datadir/
mkdir -p /mongo_logs/error_log
chown mongo_user.mongo_grp -R /mongo_data/
chown mongo_user.mongo_grp -R /mongo_logs/
c.Create configuration & binary directories
cmds:-
mkdir -p /mongo_admin/configuration_file
touch /mongo_admin/configuration_file/mongod.conf


#~~#Step 3:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "myadmin="/mongo_admin/"" >> /etc/environment
echo "basedir="/mongo_admin/mongo_binaries/"" >> /etc/environment
echo "myconf="/mongo_admin/configuration_file"" >> /etc/environment
echo "mongo_HOME="/mongo_home/homedir"" >> /etc/environment
echo "myadmin_download="/mongo_admin/mongo_binaries/download"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 4:- Extract binaries 
cmd:-cd $basedir
cmd:- tar zxvf $myadmin_download/mongodb-linux-x86_64-2.6.3.tgz -C /mongo_admin/mongo_binaries/
cmd:- ln -s /mongo_admin/mongo_binaries/mongodb-linux-x86_64-2.6.3 binaries

#~~#Step 5:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "PATH="$PATH:/mongo_admin/mongo_binaries/binaries/bin"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 6: Create configuration file
a. vi /mongo_admin/configuration_file/mongod.conf
b. write below listed information into configuration file and save
# mongod.conf
#where to log
logpath=/var/log/mongodb/mongod.log
logappend=true
# fork and run in background
fork=true
port=27017
dbpath=/mongo_data/datadir

#~~#Step 7:- Now we have to initializes the mongo data directory and creates the system tables that it should contains.
cmd:-cd $basedir/binaries/bin/
cmd:- mongod --dbpath /mongo_data/datadir/

#~~#Step8: There are many ways by which we can start mongo and here we invoke mongo.server script method that use System V-style run directories (that is, /etc/init.d and run-level specific directories),
##Generally, you start the mongod server in one of these ways:
a.Invoke mongod directly. This works on any platform.
b.Invoke mongod , which also tries to determine the proper options for mongod and then runs it with those options. This script is used on Unix and Unix-like systems.
c.Invoke mongod . This script is used primarily at system startup and shutdown on systems that use System V-style run directories (that is, /etc/init.d and run-level specific directories), where it usually is installed under the name mongo. The mongo.server script starts the server by invoking mongod_safe.
d.On Mac OS X, install a separate mongo Startup Item package to enable the automatic startup of mongo on system startup. The Startup Item starts the server by invoking mongo.server.

After initializing db ,Now we are proceding to add service in System V-style run directories

Execution steps:-
-i- First create mongod service start/stop/restartup script
a. vi $basedir/binaries/bin/mongoservice_script
b. write below listed information into mongoservice_script file and save

#!/bin/bash

# mongod - Startup script for mongod

# chkconfig: 35 85 15
# description: Mongo is a scalable, document-oriented database.
# processname: mongod
# config: /etc/mongod.conf
# pidfile: /var/run/mongodb/mongod.pid

. /etc/rc.d/init.d/functions

# things from mongod.conf get there by mongod reading it


# NOTE: if you change any OPTIONS here, you get what you pay for:
# this script assumes all options are in the config file.
CONFIGFILE="/mongo_admin/configuration_file/mongod.conf"
OPTIONS=" -f $CONFIGFILE"
SYSCONFIG="/etc/sysconfig/mongod"

# FIXME: 1.9.x has a --shutdown flag that parses the config file and
# shuts down the correct running pid, but that's unavailable in 1.8
# for now.  This can go away when this script stops supporting 1.8.
DBPATH=`awk -F= '/^dbpath[[:blank:]]*=[[:blank:]]*/{print $2}' "$CONFIGFILE"`
PIDFILE=`awk -F= '/^pidfilepath[[:blank:]]*=[[:blank:]]*/{print $2}' "$CONFIGFILE"`
mongod=${MONGOD-/mongo_admin/mongo_binaries/binaries/bin/mongod}

MONGO_USER=mongo_user
MONGO_GROUP=mongo_grp

if [ -f "$SYSCONFIG" ]; then
    . "$SYSCONFIG"
fi

# Handle NUMA access to CPUs (SERVER-3574)
# This verifies the existence of numactl as well as testing that the command works
NUMACTL_ARGS="--interleave=all"
if which numactl >/dev/null 2>/dev/null && numactl $NUMACTL_ARGS ls / >/dev/null 2>/dev/null
then
    NUMACTL="numactl $NUMACTL_ARGS"
else
    NUMACTL=""
fi

start()
{
  # Recommended ulimit values for mongod or mongos
  # See http://docs.mongodb.org/manual/reference/ulimit/#recommended-settings
  #
  ulimit -f unlimited
  ulimit -t unlimited
  ulimit -v unlimited
  ulimit -n 64000
  ulimit -m unlimited
  ulimit -u 32000

  echo -n $"Starting mongod: "
  daemon --user "$MONGO_USER" "$NUMACTL $mongod $OPTIONS >/dev/null 2>&1"
  RETVAL=$?
  echo
  [ $RETVAL -eq 0 ] && touch /var/lock/subsys/mongod
}

stop()
{
  echo -n $"Stopping mongod: "
  killproc -p "$PIDFILE" -d 300 /usr/bin/mongod
  RETVAL=$?
  echo
  [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/mongod
}

restart () {
        stop
        start
}


RETVAL=0

case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  restart|reload|force-reload)
    restart
    ;;
  condrestart)
    [ -f /var/lock/subsys/mongod ] && restart || :
    ;;
  status)
    status $mongod
    RETVAL=$?
    ;;
  *)
    echo "Usage: $0 {start|stop|status|restart|reload|force-reload|condrestart}"
    RETVAL=1
esac

exit $RETVAL


-ii) Copy server script and add in system V-style run directories
cmd:-cp $basedir/binaries/bin/mongoservice_script /etc/init.d/mongoservice_script
-iii) add in chmod +x /etc/init.d/mongoservice_script

-iv) Optional if there is any old services which we need to remove :-
cmd:-chkconfig --list | grep old_mongo_servicename
if present then remove with below command
cmd:- chkconfig –del old_mongo_servicename
-v) Adding system startup for /etc/init.d/mongoservice_script ...
cmd:- chkconfig --add mongoservice_script or chkconfig mongoservice_script on

#~~#Step 9: Now you can start mongo with
/etc/init.d/mongoservice_script start
or
/etc/init.d/mongoservice_script start

Note: We can shutdown with
/etc/init.d/mongoservice_script stop
or
/etc/init.d/mongoservice_script stop

MongoDB Distribution Formats / Different types for MongoDB Installation Methods

      MongoDB Distribution Formats / Different types for MongoDB Installation Methods

#~~#MongoDB Distribution Formats / Different types for MongoDB Installation Methods 
There are Two types of Binary Distribution Format for MongoDB Installation
a). Generic Binary Format(Zip arcives or compressed tar )
b). Native Format(RPM packages for Linux, DMG packages for Mac OS X or Ubuntu or Debian system, and PKG packages for Solaris.)


Tuesday, August 19, 2014

MariaDB Audit Plugin for MySQL

                                                      MariaDB Audit Plugin for MySQL
MariaDB Audit Plugin for MySQL:- The library is included in a tarball (server_audit-VERSION.tar.gz ) as 32-bit and 64-bit version for Linux for both, debug and non-debug versions of MariaDB and MySQL. I am using server_audit-1.1.7.tar.gz here.

Step1: Download MariaDB Audit Plugin for MySQL & untar the compressed tarball.
After untar we will get package of 32-bit and 64-bit version for Linux for both, debug and non-debug versions of MySQL.

Step2: Copy file that's meet the requirement and place at MySQL plugin dir mentioned in MySQL variable:-SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
Note:Make sure for assign required ownership & privileges

Step 3: load plugin in MySQL:-
#~a~#The plugin can be loaded from the command-line as a start-up parameter, or it can be set in the configuration file (i.e.,
my.cnf or my.ini). Below is an excerpt from a configuration file, showing the relavent line to load this plugin. To use
this option from the command-line at start-up, just add a double-dash (e.g., pluginload).

[mysqld]
...
pluginload=server_audit=server_audit.so
...

#~b~#Another way to install this plug-in is to execute the INSTALL PLUGIN statement from within MySQL. You would need to
use an administrative account which has INSERT privilege for the mysql.plugin table. To do this, you would execute the
following within the mysql client or an equivalent client:
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Note:
--The variables that will be used by the plugin (see the Configuration section) will be unknown to the
server until the plugin has been loaded the first time. The database server will not start successfully if
these variables are set in the configuration file before the audit plugin has been loaded at least once
before.

--The UNINSTALL PLUGIN statement may be used to uninstall a plugin. For the auditing plugin, you might want to disable
this possibility. To do this, you could add the following line to the configuration file after the plugin is loaded once:
[mysqld]
...
pluginload=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
...

--Once you've added the option above to the server's configuration file and restarted the server, if someone tries then to
uninstall the audit plugin, an error message will be returned. Below is an example of this with the error message:
UNINSTALL PLUGIN server_audit;
ERROR 1702 (HY000):Plugin 'server_audit' is force_plus_permanent and can not be unloaded

Database Migration [ Oracle to MySQL ]

                            Database Migration [ Oracle to MySQL ]

#~~#Action Plan for Database Migration with best practices:
1.Prepare to Migrate
2.Test the Migration Process
3.Test the Migrated Test Database
4.Prepare and Preserve the Source Database
5.Migrate the Production Database
6.Tune and Adjust the New Production Database

#~~#Database Migration:

-1-Schema Migration Action Plan from Oracle to MySQL:
Migrate oracle schema into MySQL either manually or you can also use software for schema http://www.convert-in.com/ora2sql.htm but here if using third party utility then please make sure it  migrated into optimal state.This one is the most important step in migration and should be properly verified in order to avoid any unnecessary service outage in future.
There are also some third party tools that can help in complete migration,Here we have shared plan for doing manual migration freely without any cost.
Tools are :
i)  Oracle-to-MySQL
link:- http://dbconvert.com/convert-oracle-to-mysql-pro.php
ii) SQLways - Oracle to MySQL data migration
link:- http://www.ispirer.com/products/oracle-to-mysql-migration
any many more third party utitiles are there.
Note:There is one blog that I have found later after migration but i would like to list it here for your help its link is http://tkurek.blogspot.in/2013/04/migrate-oracle-to-mysql.html

Imp.Note:Will soon upload datatype & functioanlity comparision for Oracle and MySQL.

-2-Procedure/Function/Triggers Migration Action Plan from Oracle to MySQL:
It includes cnversion of follwing action items
i).Syntax conversion.
ii).Conversion for Oracle built-in functionality into MySQL built-in functionality if available else create user defined funtionality for the same behaviour.
iii).User Defined Method Conversion.

-3-Data Migration Action Plan from Oracle to MySQL:
a). Take dump from oracle using below listed format into dumpfiles
SET TRIMSPOOL ON
SET PAGESIZE  0
SET ECHO OFF
SET FEEDBACK  OFF
SET HEADING   OFF
Spool /path/filename.csv
query(i.e select Col1||','||','||Col2||','||Col3 from tablename; Note:Please make sure the output format of column in query should be as per MySQL format)
quit

b). Restore dumpfiles into MySQL using load command or mysql import utilities for dumpfile that we get from step 2.

Friday, August 1, 2014

SQLPLUS Parameters


SET TERM      OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)

SET ECHO      ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.

SET TRIMOUT   ON
-- TRIMOUT = ON will remove trailing spaces from output

SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET HEADING   OFF
-- HEADING = OFF will hide column headings

SET FEEDBACK  OFF
-- FEEDBACK = ON will count rows returned

SET PAUSE     OFF
-- PAUSE = ON .. press return at end of each page

SET PAGESIZE  0  
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

SET LINESIZE  80
-- LINESIZE = width of page (80 is typical)

SET VERIFY    OFF
-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM TABLE_NAME;
SPOOL OFF

Wednesday, May 21, 2014

MySQL query to generate random text string with mixed cases and only alpha characters

                   MySQL query to generate random text string with mixed cases and only alpha characters

Select CONCAT(
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),
ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
);

Wednesday, April 16, 2014

Move MySQL tmp dir from Disk to RAM for better performance

        Move MySQL tmp dir from Disk to RAM for better performance
Cause:- If these temporary disk tables are causing serious I/O performance problems one could consider to use a RAM-disk instead of normal physical disks instead.

Goal :- Improve performance and decrease query time.

Solution:- Move MySQL's temporary files from disk to a RAM is very significant, if our server has lots of slow queries.

On Linux we have 2 possibilities to create a RAM-disk: ramfs and tmpfs.

#~~#Recommend to use tmpfs.
A RAM-disk can be created as follows:
shell> mkdir -p /db/ramdisk
shell> chown mysql:mysql /db/ramdisk
shell> mount -t tmpfs -o size=1024M tmpfs /db/ramdisk
To make this persistent we have to add it to the fstab:
#
# /etc/fstab
#
tmpfs           /db/ramdisk     tmpfs   rw,mode=1777,size=1024M    0       0

To changes and get it reflected this need to configure in my.cnf accordingly and restart the database...

Saturday, April 12, 2014

Applying CPU Patch in a Physical Standby Database Configuration

                                      Oracle patch deployment activity plan
                Applying CPU Patch in a Physical Standby Database Configuration
Activity Plan:-Activity step from 1 to 6 before downtime and Activity onward step7 will be in Downtime
1. Disable log shipping from the Primary
i)Stop recovery at standby:-
a.cmd:-alter database recover managed standby database cancel;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Defer archive shiping from production
cmd:-alter system set log_archive_dest_state_2=DEFER;

2. Shutdown Standby
a.Stop Listener
cmd:- lsnrctl stop servicename
Note:-To check servicename please find it using cmd:-lsnrctl status
b.Shutdown Database
cmd:-shut immediate

3. Take Backup of Oracle binaries on standby
cmds:-
cd $ORACLE_HOME
cd ../
tar -cvf $ORACLE_HOME/Backupstdby_db_1.09042014 db_1
cd /home/oracle/
tar -cvf $ORACLE_HOME/Backupstdby_oraInventory.09042014 oraInventory

4. Install the CPU patch on Standby server
a.check inventory & version status before proceeding for update
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory
b.Apply patch(Note:make sure we have exported $ORACLE_HOME/OPatch in PATH ENV VAR)
To apply CPU Patch we require OPatch utility available of that database version or later in order to apply that patch.
Go to path where we have placed both patches CPU Patch: p5689937_10201_Linux-x86-64.zip & Opatch ultility :- & p6880880_102000_Linux-x86-64.zip.(i.e /home/oracle/oracle_patch/)
Note:(pnumber_xxxxx_OS-Bit.zip :number defines patch number & xxxxx defines db version number for which it is valid , OS defines OS Flavour and Bit represent architecture bit of OS)
cd /home/oracle/oracle_patch/
cp -pvr p6880880_102000_Linux-x86-64.zip $ORACLE_HOME/
(Note:above patch will deploy opatch utility)
cd $ORACLE_HOME
mv OPatch OPatch_old
unzip p6880880_102000_Linux-x86-64.zip
cd /home/oracle/oracle_patch/
unzip p5689937_10201_Linux-x86-64.zip
cd 5689937
opatch apply
c.check inventory & version status after updation of patch
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory

5. Startup Standby in recovery mode (do NOT run any SQL the standby)
a.Start Database
cmds:-
sqlplus / as sysdba
Startup mount
b.Start Listener
cmd:- lsnrctl start servicename
Note:-servicename here belongs to what we have already captured in step 2

6. Re-enable log shipping on standby
i)Resume Recovery
a.cmd:-alter database recover managed standby database using current logfile disconnect;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Enable archive shiping from production
cmd:-alter system set log_archive_dest_state_2=ENABLE;
iii)Switch logfile on production
alter system switch logfile;
iv)Check both are in sync or not with daily_queries.
select DEST_ID,sequence#,archived,applied,deleted,status from v$archived_log order by 2;

7. Shutdown Primary
a.Stop Listener
cmd:- lsnrctl stop servicename
Note:-To check servicename please find it using cmd:-lsnrctl status
b.Also shutdown oem service if any
cmd:-emctl stop dbconsole
c.Shutdown Database
cmd:-shut immediate

8. Take Backup of Oracle binaries on Primary
cmds:-
cd $ORACLE_HOME
cd ../
tar -cvf $ORACLE_HOME/Backupprim_db_1.09042014 db_1
cd /home/oracle/
tar -cvf $ORACLE_HOME/Backupprim_oraInventory.09042014 oraInventory

9. Install the CPU patch on Primary
a.check inventory & version status before proceeding for update
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory
b.Apply patch(Note:make sure we have exported $ORACLE_HOME/OPatch in PATH ENV VAR)
To apply CPU Patch we require OPatch utility available of that database version or later in order to apply that patch.
Go to path where we have placed both patches CPU Patch: p5689937_10201_Linux-x86-64.zip & Opatch ultility :- & p6880880_102000_Linux-x86-64.zip.(i.e /home/oracle/oracle_patch/)
Note:(pnumber_xxxxx_OS-Bit.zip :number defines patch number & xxxxx defines db version number for which it is valid , OS defines OS Flavour and Bit represent architecture bit of OS)cd /home/oracle/oracle_patch/
cp -pvr p6880880_102000_Linux-x86-64.zip $ORACLE_HOME/
cd $ORACLE_HOME
mv OPatch OPatch_old
unzip p6880880_102000_Linux-x86-64.zip
cd /home/oracle/oracle_patch/
unzip p5689937_10201_Linux-x86-64.zip
cd 5689937
opatch apply
c.check inventory & version status after updation of patch
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory

10. Start primary
a.Start Database
cmds:-
sqlplus / as sysdba
Startup
b.Start Listener
cmd:- lsnrctl start servicename
Note:-servicename here belongs to what we have already captured in step 7

11. Run the SQL script for the patch on Primary
a.cmd:-
cd $ORACLE_HOME/cpu/CPUJan2007
sqlplus / as sysdba
@catcpu.sql

b.Use the below SQL command to see all the PSUs/CPUs applied to your database.
cmd:-select * from dba_registry_history ;

12. Re-enable log shipping
i)Resume Recovery
a.cmd:-alter database recover managed standby database using current logfile disconnect;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Enable archive shiping from production
cmd:-alter system set log_archive_dest_state_2=ENABLE;
iii)Switch logfile on production
cmd:-alter system switch logfile;
iv)Check both are in sync or not with daily_queries.
select DEST_ID,sequence#,archived,applied,deleted,status from v$archived_log order by 2;

13. Monitor the redo apply from Primary to Standby --- this will also upgrade the Standby data dictionary

--Rollback Plan:-
1. Disable log shipping from the Primary
2. Shutdown Standby
3. Take Backup of Oracle binaries on standby
4. Rollback the CPU patch on Standby server
5. Startup Standby in recovery mode (do NOT run any SQL the standby)
6. Re-enable log shipping on standby
7. Shutdown Primary
8. Take Backup of Oracle binaries on Primary
9. Rollback the CPU patch on Primary
10. Start primary
11. Run the SQL Rollback script for the patch on Primary
12. Re-enable log shipping
13. Monitor the redo apply from Primary to Standby --- this will also rollback the Standby data dictionary

Sunday, March 30, 2014

Introduction to MySQL Utilities

Introduction to MySQL Utilities:-

-1-MySQL Utilities provides a command-line set of tools for working with MySQL Servers and databases.

-2-It is a package of utilities that are used for maintenance and administration of MySQL servers. These utilities encapsulate a set of primitive commands, and bundles them so they can be used to perform macro operations with a single command. They can be installed via MySQL Workbench, or as a standalone package.

-3-The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python versions 2.6 or later and there is no support (yet) for Python v3.1.

-4-MySQL Utilities fully supports MySQL Server versions 5.1 and above. It is also compatible with MySQL Server 5.0, but not every feature of 5.0 may be supported. It does not support MySQL Server versions 4.x.

Synchronize Table or DB on Two Servers Where Neither is Up-to-date


Question:- How Can I Synchronize a Table or DB on Two Servers Where Neither is Up-to-date?

Suggestion/Recommendation :- Use MySQl Utilities Scripts(Ref link for introduction :http://myora-dba-notes.blogspot.in/2014/03/introduction-to-mysql-utilities.html)
In this example below, we connected to two servers and compare the database named mydb. 
We enabled the transformation statements using a combination of options as follows.
1.The --difftype=SQL option instructs the utility to generate the SQL statements.
2.The --show-reverse option instructs the utility to generate the differences in both direction.
That is, from the perspective of server1 as compared to server2 and server2 as compared to server1. 
3.Lastly, the --quiet option simply turns off the verbosity of print statements that normally occur for communicating progress.

Before proceeding let discuss the environment in which we are explaining this case to be tested , There is one db on both server i.e mydb and on server 1 we have 4 table out of which 3 are same as we have in server 2 but one on both server is different table and out of 3 common table we have one table which is out of sync or having different records updated differently on both servers ,So in order to synchronize both server database ,Please follow how can we found the SQL statements that needed to bring the tables into synch

Note:- 
We can also compare different db with command
cmd:-mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310  mydbserver1:mydbserver2 -a --difftype=SQL --show-reverse --quiet

Server 1:-
mysql> use mydb

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mydbtb2        |
| mydbtb3        |
| mydbtb5        |
| mydbtbl        |
+----------------+

4 rows in set (0.00 sec
mysql> select * from mydbtbl;
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| id    | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time           |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| 22809 |       2 |       6 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:26:35 |
| 22811 |       3 |       6 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:27:24 |
| 22815 |       5 |       6 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:28:15 |
| 22829 |       7 |     243 |    10.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:35:02 |
| 22839 |      12 |     243 |    10.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:40:02 |
| 22853 |      19 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:47:02 |
| 22893 |      39 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 18:07:02 |
| 22901 |      43 |     243 |     0.00 |   200.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 18:11:02 |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
8 rows in set (0.00 sec)

Server 2:-
mysql> use mydb
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mydbtb2        |
| mydbtb3        |
| mydbtb4        |
| mydbtbl        |
+----------------+
4 rows in set (0.00 sec)


mysql> select * from mydbtbl;
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| id    | snap_id | host_id | rd_ios_s | wr_ios_s | rkbs | wkbs | queue | wait | svc_t | busy | snap_time           |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
| 22811 |       3 |       6 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:27:24 |
| 22815 |       5 |       6 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:28:15 |
| 22829 |       7 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:35:02 |
| 22839 |      12 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:40:02 |
| 22845 |      15 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:43:02 |
| 22853 |      19 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 17:47:02 |
| 22893 |      39 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 18:07:02 |
| 22901 |      43 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 18:11:02 |
| 22905 |      45 |     243 |     0.00 |     0.00 | 0.00 | 0.00 |  0.00 | 0.00 |  0.00 | 0.00 | 2013-10-04 18:13:02 |
+-------+---------+---------+----------+----------+------+------+-------+------+-------+------+---------------------+
9 rows in set (0.00 sec)


Solution :-
Example 1:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310  mydb -a --difftype=SQL --show-reverse
# server1 on host1: ... connected.
# server2 on host2: ... connected.
# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
#        TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
#        TABLE: mydbtb4
#
#                                           Defn    Row     Data
# Type      Object Name                     Diff    Count   Check
# ---------------------------------------------------------------
# TABLE     mydbtb2                         pass    pass    pass
# TABLE     mydbtb3                         pass    pass    pass
# TABLE     mydbtbl                         pass    FAIL    FAIL
#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Transformation for --changes-for=server1:
#

# Data differences found among rows:
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '0.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22809'`snap_time` = '2013-10-04 17:26:35';
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22845', '15', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:43:02');
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22905', '45', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 18:13:02');

#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
# UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '200.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22845'`snap_time` = '2013-10-04 17:43:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22905'`snap_time` = '2013-10-04 18:13:02';
# INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22809', '2', '6', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:26:35');
#



# Database consistency check failed.
#
# ...done
[root@localhost ~]#


Example 2:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310  mydb -a --difftype=SQL --show-reverse --quiet
# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
#        TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
#        TABLE: mydbtb4
#

#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Transformation for --changes-for=server1:
#

# Data differences found among rows:
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '0.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '0.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22809'`snap_time` = '2013-10-04 17:26:35';
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22845', '15', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:43:02');
INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22905', '45', '243', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 18:13:02');

#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22829'`snap_time` = '2013-10-04 17:35:02';
# UPDATE `mydb`.`mydbtbl` SET `wr_ios_s` = '200.00' WHERE `id` = '22901'`snap_time` = '2013-10-04 18:11:02';
# UPDATE `mydb`.`mydbtbl` SET `rd_ios_s` = '10.00' WHERE `id` = '22839'`snap_time` = '2013-10-04 17:40:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22845'`snap_time` = '2013-10-04 17:43:02';
# DELETE FROM `mydb`.`mydbtbl` WHERE `id` = '22905'`snap_time` = '2013-10-04 18:13:02';
# INSERT INTO `mydb`.`mydbtbl` (`id`, `snap_id`, `host_id`, `rd_ios_s`, `wr_ios_s`, `rkbs`, `wkbs`, `queue`, `wait`, `svc_t`, `busy`, `snap_time`) VALUES('22809', '2', '6', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '2013-10-04 17:26:35');
#

Example 3:-
[root@localhost ~]# mysqldbcompare --server1=root:root@host1:3310 --server2=root:rrrr@host2:3310  mydb -a --quiet
# Checking databases mydb on server1 and mydb on server2
#
# WARNING: Objects in server1.mydb but not in server2.mydb:
#        TABLE: mydbtb5
#
# WARNING: Objects in server2.mydb but not in server1.mydb:
#        TABLE: mydbtb4
#

#
# Row counts are not the same among `mydb`.`mydbtbl` and `mydb`.`mydbtbl`.
#
# Data differences found among rows:
--- `mydb`.`mydbtbl`
+++ `mydb`.`mydbtbl`
@@ -1,7 +1,7 @@
 +--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
 | id     | snap_id  | host_id  | rd_ios_s  | wr_ios_s  | rkbs  | wkbs  | queue  | wait  | svc_t  | busy  | snap_time            |
 +--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
-| 22829  | 7        | 243      | 10.00     | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:35:02  |
-| 22901  | 43       | 243      | 0.00      | 200.00    | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 18:11:02  |
-| 22839  | 12       | 243      | 10.00     | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:40:02  |
+| 22829  | 7        | 243      | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:35:02  |
+| 22901  | 43       | 243      | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 18:11:02  |
+| 22839  | 12       | 243      | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:40:02  |
 +--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+

# Rows in `mydb`.`mydbtbl` not in `mydb`.`mydbtbl`
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| id     | snap_id  | host_id  | rd_ios_s  | wr_ios_s  | rkbs  | wkbs  | queue  | wait  | svc_t  | busy  | snap_time            |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| 22809  | 2        | 6        | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:26:35  |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+

# Rows in `mydb`.`mydbtbl` not in `mydb`.`mydbtbl`
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| id     | snap_id  | host_id  | rd_ios_s  | wr_ios_s  | rkbs  | wkbs  | queue  | wait  | svc_t  | busy  | snap_time            |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+
| 22845  | 15       | 243      | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 17:43:02  |
| 22905  | 45       | 243      | 0.00      | 0.00      | 0.00  | 0.00  | 0.00   | 0.00  | 0.00   | 0.00  | 2013-10-04 18:13:02  |
+--------+----------+----------+-----------+-----------+-------+-------+--------+-------+--------+-------+----------------------+

Monday, March 24, 2014

Oracle Query to check tablespace usage

Oracle Query to check tablespace usage & free details with their percentage 

1.Query:-Return Tablespace usage and free details excluding TEMP Tablespace
break on report
compute sum of FREE_SUM  FREE_MAX on report
/* FREE SPACE */
compute sum of col2 col3 col4  on report
--ttitle 'Free Space by Tablespace'
set lines 200
set pages 2000
set head on
set feed off
col col1    format a25                heading "Tablespace"
col col2    format 999,999,999,999    heading "Bytes(MB)"
col col3    format 999,999,999,999    heading "Used(MB)"
col col4    format 999,999,999,999    heading "Free(MB)"
col col6    format 999,999,999,999    heading "Free MAX(MB)"
col col5    format 999.9              heading "Free(%)"
select
    b.tablespace_name col1,
       b.bytes / 1024 / 1024 col2,
    ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024 col3,
    (sum(nvl(a.bytes,0))) / 1024 / 1024 col4,
    (sum(nvl(a.bytes,0)) / (b.bytes)) * 100 col5,
    max(a.bytes)/1024 / 1024 col6, count(*)
from sys.dba_free_space a,
     (select  tablespace_name, sum(bytes) bytes
      from sys.dba_data_files
      group by tablespace_name ) b
where a.tablespace_name(+) = b.tablespace_name
group by b.tablespace_name, b.bytes
order by 5 desc;

OUTPUT:-

Tablespace   Bytes(MB)         Used(MB)         Free(MB) Free(%)     Free MAX(MB)   COUNT(*)
-----------  --------- ---------------- ---------------- ------- ---------------- ----------
DYM             16,484                0           16,484   100.0            3,968          7
UNDOTBS1         5,280               33            5,247    99.4            3,907         19
USERS           14,084            1,710           12,374    87.9            3,968         10
SYSTEM           1,370            1,358               12      .9                9          6
SYSAUX             620              616                4      .7                4          2
            ---------- ---------------- ----------------

sum             37,838            3,717           34,121


2.Query:-Return Tablespace usage and free details including TEMP Tablespace.
col tablespace_name format a25 heading 'TableSpace|Name'
col AvailSpace format 9999990.90 heading 'Total Size|(in Mb)'
col FreeSpace format 9999990.90 heading 'FreeSpace |(in Mb)'
col UsedSpace format 9999990.90 heading 'UsedSpace |(in Mb)'
SELECT
        dts.tablespace_name,
        (ddf.bytes / 1024 / 1024) "AvailSpace",
        (ddf.bytes - (dfs.bytes))/1024/1024 "UsedSpace",

        (dfs.bytes / 1024/1024 ) "FreeSpace",

        TO_CHAR(((ddf.bytes - (dfs.bytes)) / ddf.bytes * 100),'990.00') "Used %"

        FROM

        sys.dba_tablespaces dts,

        (select tablespace_name, sum(bytes) bytes

                from dba_data_files group by tablespace_name) ddf,
                        (select tablespace_name, sum(bytes) bytes
                                from dba_free_space group by tablespace_name) dfs
                                WHERE
                                        dts.tablespace_name = ddf.tablespace_name
                                AND dts.tablespace_name = dfs.tablespace_name
                                AND NOT (dts.extent_management like 'LOCAL'
                                AND dts.contents like 'TEMPORARY')
                                UNION ALL
                                SELECT dts.tablespace_name,
                                        (dtf.bytes / 1024 / 1024) "AvailSpace",
                                        (t.bytes)/1024/1024 "UsedSpace",
                                        (dtf.bytes - (t.bytes))/1024/1024 "FreeSpace",
                                        TO_CHAR((t.bytes / dtf.bytes * 100), '990.00') "Used %"
                                FROM
                                        sys.dba_tablespaces dts,
                                        (select tablespace_name, sum(bytes) bytes
                                        from dba_temp_files group by tablespace_name) dtf,
                                                (select tablespace_name, sum(bytes_used) bytes
                                                from v$temp_space_header group by tablespace_name) t
        WHERE
        dts.tablespace_name = dtf.tablespace_name
        AND dts.tablespace_name = t.tablespace_name
        AND dts.extent_management like 'LOCAL'
        AND dts.contents like 'TEMPORARY'
/

OUTPUT:-
TableSpace                 Total Size  UsedSpace   FreeSpace
Name                          (in Mb)     (in Mb)     (in Mb) Used %
------------------------- ----------- ----------- ----------- -------
UNDOTBS1                      5280.00       33.38     5246.63    0.63
SYSAUX                         620.00      615.63        4.38   99.29
DYM                          16484.00        0.19    16483.81    0.00
USERS                        14083.75     1710.00    12373.75   12.14
SYSTEM                        1370.00     1357.75       12.25   99.11
TEMP                            45.00       45.00        0.00  100.00

Friday, March 21, 2014

Reasons to relocated installation directories of package

                             Reasons to relocated installation directories of package


#~~#RPM has the ability to give users some latitude in deciding where packages are to be installed on
their systems. However, package builders must first design their packages to give users this
freedom.
In other words, an RPM package that can be installed into a different directory is said to be relocatable. Please note that not all RPM packages can be installed into another directory.
That's all well and good, but why would the ability to ``relocate'' a package be all that important?
#~~#Why relocate packages?
1.Avoid disk space problem(e.g default installation location is small in size)
2.This is standard installation method adopted for compliance
3.Save from attacks.

#~~#To check is package relocatable or not on Generic Linux 
cmd:-rpm -qpi package-file-name.rpm | grep Relocations

#~~#rpm options for installing other then default predefined location:-
--relocate OLDPATH=NEWPATH
For relocatable binary packages, translate all file paths that start with OLDPATH in the package relocation hint(s) to NEWPATH. This option can be used repeatedly if several OLDPATHâs in the package are to be relocated.
--prefix NEWPATH
For relocatable binary packages, translate all file paths that start with the installation prefix in the package relocation hint(s) to
NEWPATH.