Oracle DB Quick Reference

Setting Oracle Environment :


#export ORACLE_BASE=/usr/lib/oracle/xe/app/oracle  
#export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
#export ORACLE_SID=XE
#export PATH=$PATH:$ORACLE_HOME/bin


ORACLE_BASE:  You can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.


Log in :
SQL> connect sys as sysdba;
Enter password:
Connected.


Start/Stop a DB;

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size            1260268 bytes
Variable Size          163579156 bytes
Database Buffers      432013312 bytes
Redo Buffers            2932736 bytes
Database mounted.
Database opened.
SQL>


How to check complete version of oracle :




SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

Create a schema:

A schema is the set of objects (tables, views, indexes, etc) that belongs to an user account.

Create a Table Space:

SQL> create tablespace sriram_ts datafile '/home/sriram/Oracle_db/sriram_tablespace.dbf' size 300M;

Tablespace created.

SQL>


Create a Temporary Table Space:

SQL> create temporary tablespace sriram_temp_ts TEMPFILE '/home/sriram/Oracle_db/sriram_temp_tablespace.dbf' size 100M;

Tablespace created.

SQL>

Create a DB user :

 SQL> CREATE USER sriram IDENTIFIED BY sriram321 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

Note: a temporary tablespace has tempfiles, not datafiles.


Current user logged in


SQL> show user;
USER is "SYS"
SQL>


To show all users on Oracle DB:


SQL> select * from all_users;

Better :
SQL>SELECT username FROM all_users ORDER BY username; 

To check which version of DB is this :



SQL> SELECT * FROM global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
XE

SQL>


To List all Tablespace that are created :



SQL> select name from v$tablespace;

NAME
--------------------------------------------------------------------------------
SYSTEM
UNDO
SYSAUX
USERS
TEMP
SRIRAM_TS
SRIRAM_TEMP_TS

7 rows selected.

SQL>

To List Free Space for all Table Space:


SQL> select * from dba_free_space;

To List Free Space for specific Table Space:

SQL> select * from dba_free_space where TABLESPACE_NAME ='USERS';

TABLESPACE_NAME

--------------------------------------------------------------------------------

   FILE_ID   BLOCK_ID   BYTES     BLOCKS RELATIVE_FNO

---------- ---------- ---------- ---------- ------------

USERS

4  209  103153664      12592       4

SQL> 

How To Display SGA (System Global Area) DATA ?



SQL> show SGA;

Total System Global Area  599785472 bytes
Fixed Size    1260268 bytes
Variable Size  163579156 bytes
Database Buffers  432013312 bytes
Redo Buffers    2932736 bytes
SQL> 

The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM). In Oracle Database 10g you only need to define two parameters (sga_target andsga_max_size) to configure your SGA. If these parameters are configured, Oracle will calculate how much memory to allocate to the different areas of the SGA using a feature called Automatic Memory Management (AMM). As you gain experience you may want to manually allocate memory to each individual area of the SGA with the initialization parameters.

How to check what is your ORACLE_SID if you are logged in ?


SQL> show parameter db_name;


NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_name     string
XE
SQL> 

What is the Purpose of ORACLE_SID ?

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

You can have multiple database instance on same system with different ORACLE_SID, so same database name may not clash.

SID is defined in file :

#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin



#cat /etc/oratab 
XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N
#


#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/ tnsnames.ora






Where is TNS Listener Port Defined ?


By Default Oracle DB listens on 1521 Port, but you can change it

#cat  /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora


To check what are the System Privileges  available in oracle db: ?
SQL>select * from system_privilege_map;

Granting admin Priviledge to User "sriram": ?

SQL>grant sysdba to sriram;



Oracle v$ View List : ?

Oracle creates the v$ views from the x$ structures.  The actual v$ view create view definitions are located in $ORACLE_HOME/rdbms/admin directory so you can see how the v$ views are built on the x$ structures.

#cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/admin$

Creating a Swap space for VM guest

 In vm.cfg file, I added the below line : 


'file:/FMW/vms/fscmapps2/swap,sdd,w',


on VM guest  I Do :

# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): p

Disk /dev/sdd: 10.7 GB, 10737419264 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

# mkswap /dev/sdd1
Setting up swapspace version 1, size = 10733953 kB

# swapon

# swapon  /dev/sdd1

# free -otm
             total       used       free     shared    buffers     cached
Mem:         61440       1392      60047          0         16        136
Swap:        10236          0      10236
Total:       71676       1392      70284

VM guest booting issues.

xm console<id>

When you start a  Virtual Machine and you get the error Press Ctrl+D for
Maintenance:

Cause could be the /tmp or /swp or anyother file systems in DomU-5EL-U5-x86_64-1.0.3.img
might not be available.

Referring to a different image which might not be available :


To ignore those filesystems while booting VM, so it does not throw error:

on the Host os:

# kpartx -a DomU-5EL-U5-x86_64-1.0.3.img

# cd /dev/mapper/

(here loop0p1 could be /root filesystem), so we mount and edit fstab to edit and ignore
those /tmp /swap space filesystem.)
 
# ls
control  loop0p1  loop0p2  loop0p3  loop0p5  loop0p6  loop0p7

# mkdir /test

# mount loop0p1 /test

# ls /test
admin  boot  etc   lib    lost+found  misc  oem  proc  sbin     slot  sys  u01      u03  var
bin    dev   home  lib64  media       mnt   opt  root  selinux  srv   tmp  u01_old  usr

# vi /test/etc/fstab

#comment the line while is starting the /tmp /swap or whateer you may think.

save it,

and try starting the console.

#xm console <id> <=== now it should be fine

How Bridge Works in Xen.

How to configure XEN to use NAT or Bridge networks.

For configurig Xen Virtualisation Server to use bridge or NAT networks:


Edit File : /etc/xen/xend-config.sxp


For NAT:

(network-script 'network-nat netdev=eth0')
(vif-script     vif-nat)

For Bridge:


by default netdev is always eth0.
if you want to change it to eth1 device then,
eg:

(network-script 'network-bridge netdev=eth1')

(vif-script vif-bridge)

You can also bind Guest OS exclusively to one Bridge
Edit  /xen_home/xen_vm1/vm.cfg 

vif = ['ip=10.228.141.202','bridge=xenbr0', 'mac=00:16:3E:0B:87:2B']

ip= IP of guest OS
Bridge = xenbr0 is mapped to eth0 which is assigned ip of network 10 series
mac=mac address of Guest OS.


#brctl show 
will list the Bridge Name and device its bridged to, eg below :
# brctl show
bridge name     bridge id               STP enabled     interfaces
xenbr0          8000.001517cd0ad2       no              vif39.2
                                                        vif39.1
                                                        vif39.0
                                                        vif38.0
                                                        eth0



You will find the network-bridge script in below location :

# pwd
/etc/xen/scripts


# ls
block                    network-bridges  vtpm-common.sh
block-common.sh          network-nat      vtpm-delete
block-enbd               network-route    vtpm-hotplug-common.sh
block-nbd                vif-bridge       vtpm-impl
external-device-migrate  vif-common.sh    vtpm-migration.sh
image_domU.sh            vif-nat          xen-hotplug-cleanup
locking.sh               vif-route        xen-hotplug-common.sh
logging.sh               vscsi            xen-network-common.sh
network-bridge           vtpm             xen-script-common.sh

reload xend (/etc/init.d/xend reload)

If bridge is up default name will be like xenbr0

Take a look at file /etc/xen/xend-config.sxp to change default bridge name or for
 more directives and options.


*Make sure the gateway you have given in Guest OS is same as Host Gateway or else wont be reachable from outside.



Also theres a good tutorial on Xen networking which you can  check here

Adding a route for a particular host

Details of connectivity :
Host : host1  (runs xen)
Guest : guest1 (runs under host1 xen server).

Host :host2 (different host on the nework)

To be able to ping from host2 to guest1

On host2:
#route add -host guest1 gw host1

You can mention the IP address or host name both works.

Now  try pinging from Host2 to Guest1 !!

Oracle VM Manager Agent password

To Set Agent password (Agent runs on Oracle VM server by default)

On Oracle VM server :


# cat /etc/redhat-release
Oracle VM server release 2.2.0

Reset Agent Password:



# service ovs-agent configure
;network access control by ip --
;rules := if addr.match(allow) and not addr.match(deny): return True
;pattern items delimited by comma and could be
;219.142.73.50   #single ip
;219.142.73.*    #range
;219.142.73.0/24 #range in CIDR format
;default to allow all, deny none
allow=all
allow=all
now allow=all

deny=
deny=none
now deny=none

;share_disk_pat --
;set the directories for searching sharable block devices
;directories should be seperated by ':'
;if not set, /dev/mpath/* will be used
share_disk_pat=/dev/mpath/*
share_disk_pat=
now share_disk_pat=/dev/mpath/*

would you like to modify password to communicate with agent (local)?[y/N]y
password:xxxxx
again:xxxxx

OVS Agent configuration done.

Tell agent to reload cache ...
Agent cache reloaded.

Restart Agent service 

# service ovs-agent restart
OVSAgentServer shutdown...
OVSAgentServer stopped.
OVSAgentServer is now starting...
OVSAgentServer started.

Just to check if you are able to connect to Agent:

# /opt/ovs-agent-2.3/utils/do_rpc.py https://oracle:<agentpassword>@localhost:8899 echo hello

echo ['hello'] =>

success:echo=hello

The above means agent is working fine !!

Also read :
http://lingho.com/mediawiki/index.php/Oracle_VM_2.1.5_and_2.2.0_Installation

Basic Steps to Create a New Virtual Machine on Oracle VM Manager

Firefox issue.

Today i faced a issue in starting firefox:

Firefox launcher will give me the below error and ps will not show me the output.


"Firefox is already running, but is not responding. To open a new window, you must first close the existing Firefox process, or restart your system."

Solution:



Step 1. Find the Firefox profile directory, which will be under ~/.mozilla/firefox/[Profile name]/
(e.g) /home/sriram/.mozilla/firefox/0bk8nayy.default

Step 2. Remove the lock files (“lock” and “.parentlock”) found under the Firefox profile directory.

Step 3. Restart Firefox and it should work.

Convert Virtulabox disk(.vdi) to Oracle VM (.img)

Below is the step for converting your VirtualBox disk to Oracle Virtual Server Image.

On My Windows OS I have VirtualBox  installed and Guest OS (Ubuntu) running.

Steps:
1) Login to Guest OS on Virtual Box and create a label for filesytem and add it to /etc/fstab.
2) Reboot and check if Guest OS is ok.
3) Shutdown the Guest OS.
4) Go to VirtualBox Installation path on Windows Host OS and run the VBoxManage command to convert from (.vdi) to (.img)




1) Step one Create label on Guest OS for filesystems

a) First i Logged in to Guest OS (Ubuntu) to create a label for my filesystem.
So the file-system is know by label and not by any defined partition.

Check filesystem type

root@sriram-VirtualBox:/home/sriram# df -T
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/sda1     ext4     5903296   2120868   3482552  38% /
none      devtmpfs      248644       224    248420   1% /dev
none         tmpfs      254244       208    254036   1% /dev/shm
none         tmpfs      254244        88    254156   1% /var/run
none         tmpfs      254244         0    254244   0% /var/lock
/dev/sr0   iso9660       36238     36238         0 100% /media/VBOXADDITIONS_4.0.0_69151

b) Create Label:
 root@sriram-VirtualBox:/home/sriram# e2label /dev/sda1 sriram1

c) Verify if Label is created with mount command 

root@sriram-VirtualBox:/home/sriram# mount -l

/dev/sda1 on / type ext4 (rw,errors=remount-ro,commit=0) [sriram1]

proc on /proc type proc (rw,noexec,nosuid,nodev)

d) Edit your /etc/fstab file:

---------------------------------------------

# /etc/fstab: static file system information.

#

# Use 'blkid -o value -s UUID' to print the universally unique identifier

# for a device; this may be used with UUID= as a more robust way to name

# devices that works even if disks are added and removed. See fstab(5).

#

# <file system> <mount point>   <type>  <options>       <dump>  <pass>

proc            /proc           proc    nodev,noexec,nosuid 0       0

# / was on /dev/sda1 during installation

#commented below sriram

#UUID=f6fb2bc7-d0f9-4a91-ab2d-1d7be4cb2055 /               ext4    errors=remount-ro 0       1

LABEL=sriram1 /               ext4    errors=remount-ro 0       1

# swap was on /dev/sda5 during installation

UUID=c2583cce-307b-42c4-991c-4be900869b38 none            swap    sw              0       0

---------

#UUID=f6fb2bc7-d0f9-4a91-ab2d-1d7be4cb2055 /               ext4    errors=remount-ro 0       1

-------------------------------------------

e)  Reboot and check if you are good with the guest os (for me things were good)

f) now shut down the Guest OS




Step2 : Go to Virtualbox installed path on Windows (Host OS) and run below command:


For converting .vdi to .img  file :


C:\Program Files\Oracle\VirtualBox>


C:\Program Files\Oracle\VirtualBox>VBoxManage clonehd 
D:\Softwares\ubuntu10.vdi   ovm.img -format raw
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone hard disk created in format 'raw'. UUID: fe02c861-112f-4c29-9a78-c62f93ef5
be2

C:\Program Files\Oracle\VirtualBox>dir
02/03/2011  06:36 PM     6,477,053,952 ovm.img


When i checked both of them are of same size.


vm.cfg would be something like this for Oracle VM :
-------
acpi = 1
apic = 1
bootloader = '/usr/bin/pygrub'
disk = ['file:/vms/sample/DomU-5EL-U5-x86_64-1.0.3.img,hda,w',
'file:/vms/sample/ovm.img ,sdb,w',
#'phy:/dev/mapper/DomUVol-appohs_d2,sdc,w',
'file:/FMW/vms/sample/swap,sdd,w',
'file:/FMW/vms/sample/tmp,sde,w',
]
localtime = 0
memory = 6144
name = 'guest1'
ne2000 = 0
on_crash = 'preserve'
on_poweroff = 'destroy'
on_reboot = 'restart'
on_shutdown = 'shutdown'
pae = 1
sdl = 0
serial = 'pty'
superpages = 1
timer_mode = 2
vcpus = 16
vif = ['ip=192.168.10.10']
vif_other_config = []
vnc = 1
vncunused = 1


Also check this link for more info:

http://blogs.oracle.com/wim/2011/01/converting_an_oracle_vm_virtua.html

Kill a VNC server connection

$ vncserver -kill :3
Killing Xvnc process ID 3578

$ vncserver -kill :2
Killing Xvnc process ID 12733

Connecting to VNC on a guest without X on it.

Scenario :


Hostname

Host OS -host1
Host1 has 2 IPs 10.xx.xx.xx  and 192.xx.xx.xx

eth0:  10.222.22.2  can connect from my windows desktop
eth1:  192.168.5.2 is used to connect to Guest os

Guest OS - Guest1 
Guest has IP address 192.168.2.1

SSH Port fowarding is one one way to connect :

On host os do fowarding to Guest OS.
Flush Rules First
#iptables -t nat -F


Port forwarding from Host1:6001(host os) to 192.168.2.1:6001 (guest os)

#/sbin/iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

#iptables -t nat -A PREROUTING -p tcp -s 0.0.0.0/0  -d 10.222.22.2 --dport 6001 -j DNAT --to-destination 192168.2.1:6001


WAY 2

Export display of guest to host where you have X running:

on Guest  OS if you dont have X running and want to use a X of different server.
#export DISPLAY=host_where_x_is_running:1.0
#vncpasswd (set password)

#xterm &

From windows desktop vnc connect to  guest it will forward its display to [ host_where_x_is_running ]