ORACLE

Resume failed rman duplicate

2013-04-25T19:28:00.003-07:00

RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which datafiles were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with or without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.

If a DUPLICATE operation fails, you need only run the DUPLICATE comand again, using the same parameters contained in the original DUPLICATEcommand.

The second DUPLICATE operation locates the datafiles that were successfully duplicated by the initial DUPLICATE command. Restores only the missing or incomplete datafiles, thereby avoiding re-copying and restoring all the datafiles.

Before you attempt to resume a failed DUPLICATE operation, you must reset the auxiliary instance to NOMOUNT mode. One way to do this is to exit RMAN, use SQL*Plus to reset the auxiliary instance to NOMOUNT mode, start RMAN and then repeat the DUPLICATE command. If you do not want RMAN to automatically recover from a failed DUPLICATE operation, specify the keyword NORESUME to disable the functionality. Using the keywordNORESUME in the first invocation of DUPLICATE prevents a subsequentDUPLICATE command for the new database from using this automatic optimization.

Cloning Oracle Database Without Connecting Catalog and Target using rman

2013-04-08T04:51:00.000-07:00

Cloning oracle database without accessing target database or rman catalog. Only we need is a full rman backup including controlfile and parameter file. Below given is the step to achieve this.

Step 1:Take Backup of Target database to a specific destination.

run {
allocate channel t1 type disk;
backup current controlfile format '/tem/bck/ctl_%d_%u_%s';
backup database format '/tem/bck/data_%d_%u_%s';
backup archivelog all format '/temp/bck/arch_%d_%u_%s';
release channel t1;
}

11g Release 2 RMAN Backup Compression

2012-08-21T01:04:00.001-07:00

Oracle 11g Release 2 introduced compression algorithm levels which can be used for compressing table data, Data Pump exports and RMAN backups as well.
The compression levels are BASIC, LOW, MEDIUM and HIGH and each affords a trade off related to backup througput and the degree of compression afforded.
To use this option, we can run the following RMAN commands
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
followed by ..
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘HIGH’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘MEDIUM’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘LOW’;
or
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘BASIC’;
Tests carried out on a small 1.5 GB database returned compression ratios of about 4.7 at best case. However, it should be noted that use of LOW, MEDIUM and HIGH requires the Advanced Compression license. The backupset size and backup durations are shown below.
Compression Level ‘HIGH’
backupset size: 226.18M
time: 00:02:21
Compression Level ‘Medium’
backupset size: 293.80M
time: 00:00:30
Compression Level ‘Low’
backupset size: 352.59M
time: 00:00:20
Compression Level ‘Basic’
backupset size: 276.55M
time: 00:00:50
To summarise we can conclude:
LOW – corresponds to LZO (11gR2) – smallest compression ratio, fastest
MEDIUM – corresponds to ZLIB (11gR1) – good compression ratio, slower than LOW
HIGH – corresponds to unmodified BZIP2 (11gR2) – highest compression ratio, slowest
BASIC (which is free) – corresponds to BZIP2 (10g style compression) – compression ratio in the range of MEDIUM, but slower

iostat,vmstat,netstat

2010-08-05T23:01:00.000-07:00

iostat,vmstat,netstat

iostat , vmstat and netstat are three most commonly used tools for performance monitoring . These comes built in with the operating system and are easy to use .iostat stands for input output statistics and reports statistics for i/o devices such as disk drives . vmstat gives the statistics for virtual Memory and netstat gives the network statstics .

Following paragraphs describes these tools and their usage for performance monitoring.

Table of content :
1. Iostat
* Syntax
* example
* Result and Solutions

2. vmstat
* syntax
* example
* Result and Solutions

3. netstat
* syntax
* example
* Result and Solutions

Input Output statistics ( iostat )
iostat reports terminal and disk I/O activity and CPU utilization. The first line of output is for the time period since boot & each subsequent line is for the prior interval . Kernel maintains a number of counters to keep track of the values.

iostat’s activity class options default to tdc (terminal, disk, and CPU). If any other option/s are specified, this default is completely overridden i.e. iostat -d will report only statistics about the disks.

syntax:
Basic synctax is iostat interval count
option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t or -tdc ) . x options gives the extended statistics .

interval – is time period in seconds between two samples . iostat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . iostat 4 5 will give data at 4 seconds interval 5 times

Example

$ iostat -xtc 5 2
                          extended disk statistics       tty         cpu
     disk r/s  w/s Kr/s Kw/s wait actv svc_t  %w  %b  tin tout us sy wt id
     sd0   2.6 3.0 20.7 22.7 0.1  0.2  59.2   6   19   0   84  3  85 11 0
     sd1   4.2 1.0 33.5  8.0 0.0  0.2  47.2   2   23
     sd2   0.0 0.0  0.0  0.0 0.0  0.0   0.0   0    0
     sd3  10.2 1.6 51.4 12.8 0.1  0.3  31.2   3   31

The fields have the following meanings:
      disk    name of the disk
      r/s     reads per second
      w/s     writes per second
      Kr/s    kilobytes read per second
      Kw/s    kilobytes written per second
      wait    average number of transactions waiting for service (Q length)
      actv    average number of transactions  actively being serviced
(removed  from  the  queue but not yet completed)
      %w      percent of time there are transactions  waiting
              for service (queue non-empty)
      %b      percent of time the disk is busy  (transactions
                  in progress)

Results and Solutions
The values to look from the iostat output are:
* Reads/writes per second (r/s , w/s)
* Percentage busy (%b)
* Service time (svc_t)

If a disk shows consistently high reads/writes along with , the percentage busy (%b) of the disks is greater than 5 percent, and the average service time (svc_t) is greater than 30 milliseconds, then one of the following action needs to be taken

1.) Tune the application to use disk i/o more efficiently by modifying the disk queries and using available cache facilities of application servers .

2.) Spread the file system of the disk on to two or more disk using disk striping feature of volume manager /disksuite etc.

3.) Increase the system parameter values for inode cache , ufs_ninode , which is Number of inodes to be held in memory. Inodes are cached globally (for UFS), not on a per-file system basis

4.) Move the file system to another faster disk /controller or replace existing disk/controller to a faster one.

Virtual Memory Statistics ( vmstat )

vmstat – vmstat reports virtual memory statistics of process, virtual memory, disk, trap, and CPU activity.

On multicpu systems , vmstat averages the number of CPUs into the output. For per-process statistics .Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted.

syntax
Basic synctax is vmstat interval count

option – let you specify the type of information needed such as paging -p , cache -c ,.interrupt -i etc.

if no option is specified information about process , memory , paging , disk ,interrupts & cpu is displayed .

interval – is time period in seconds between two samples . vmstat 4 will give data at each 4 seconds interval.

count – is the number of times the data is needed . vmstat 4 5 will give data at 4 seconds interval 5 times.

Example
The following command displays a summary of what the system
is doing every five seconds.

example% vmstat 5

procs  memory          page             disk      faults        cpu
     r b w swap  free re mf pi p fr de sr s0 s1 s2 s3  in  sy  cs us sy id
     0 0 0 11456 4120 1  41 19 1  3  0  2  0  4  0  0  48 112 130  4 14 82
     0 0 1 10132 4280 0   4 44 0  0  0  0  0 23  0  0 211 230 144  3 35 62
     0 0 1 10132 4616 0   0 20 0  0  0  0  0 19  0  0 150 172 146  3 33 64
     0 0 1 10132 5292 0   0  9 0  0  0  0  0 21  0  0 165 105 130  1 21 78

The fields of vmstat's display are
procs
r     in run queue
b     blocked for resources I/O, paging etc.
w     swapped
memory (in Kbytes)
swap -  amount  of  swap   space   currently   available
free   - size of the free list

page ( in units per second).
re    page reclaims -  see  -S  option  for  how  this
field is modified.
mf    minor faults -  see  -S  option  for  how    this
field is modified.
pi    kilobytes paged in
po    kilobytes paged out
fr    kilobytes freed
de    anticipated short-term memory shortfall (Kbytes)
sr    pages scanned by clock algorithm
disk  ( operations per second )
There are  slots for up to four disks,
 labeled with a single letter and number.
The letter indicates  the  type  of disk
 (s = SCSI, i = IPI, etc).
The number is  the logical unit number.

faults
in    (non clock) device interrupts
sy    system calls
cs    CPU context switches

cpu  -   breakdown of percentage usage of CPU  time.
 On multiprocessors  this is an a
 average across all processors.
us    user time
sy    system time
id    idle time

Results and Solution from iostat

A. CPU issues
Following columns has to be watched to determine if there is any cpu issue

1. Processes in the run queue (procs r)
2. User time (cpu us)
3. System time (cpu sy)
4. Idle time (cpu id)

procs      cpu
     r b w    us sy  id
     0 0 0    4  14  82
     0 0 1    3  35  62
     0 0 1    3  33  64
     0 0 1    1  21  78

Problem symptoms

A.) Number of processes in run queue
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .
2.) if this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.
3.) If the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us) system is facing shortage of CPU resources.

Resolution
Resolution to these kind of issues involves tuning of application procedures to make efficient use of cpu and as a last resort increasing the cpu power or adding more cpu to the system.

B. Memory Issues
Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage.

Resolution
1. Tune the applications & servers to make efficient use of memory and cache.
2. Increase system memory .
3. Implement priority paging in s in pre solaris 8 versions by adding line “set priority paging=1″ in
/etc/system. Remove this line if upgrading from Solaris 7 to 8 & retaining old /etc/system file.

Network Statistics (netstat)

netstat displays the contents of various network-related data structures in depending on the options selected.

Syntax

netstat
multiple options can be given at one time.

Options
-a – displays the state of all sockets.
-r – shows the system routing tables
-i – gives statistics on a per-interface basis.
-m – displays information from the network memory buffers. On Solaris, this shows statistics
for STREAMS
-p [proto] – retrieves statistics for the specified protocol
-s – shows per-protocol statistics. (some implementations allow -ss to remove fileds with a value of 0 (zero) from the display.)
-D – display the status of DHCP configured interfaces.
-n do not lookup hostnames, display only IP addresses.
-d (with -i) displays dropped packets per interface.
-I [interface] retrieve information about only the specified interface.
-v be verbose

interval – number for continuous display of statictics.

Example
$netstat -rn

Routing Table: IPv4
    Destination           Gateway               Flags  Ref   Use   Interface
-------------------- -------------------- ----- ----- ------ ---------
192.168.1.0         192.168.1.11          U        1   1444      le0
224.0.0.0           192.168.1.11          U        1   0            le0
default             192.168.1.1           UG       1   68276
127.0.0.1           127.0.0.1             UH       1   10497     lo0

This shows the output on a Solaris machine who’s IP address is 192.168.1.11 with a default router at 192.168.1.1

Results and Solutions

A.) Network availability
The command as above is mostly useful in troubleshooting network accessibility issues . When outside network is not accessible from a machine check the following

1. if the default router ip address is correct
2. you can ping it from your machine.
3. If router address is incorrect it can be changed with route add command. See man route for more information.

route command examples
$route add default
$route add 192.0.2.32

If the router address is correct but still you can’t ping it there may be some network cable /hub/switch problem and you have to try and eliminate the faulty component .

B.) Network Response
$ netstat -i

Name Mtu  Net/Dest Address  Ipkts  Ierrs  Opkts Oerrs  Collis  Queue
lo0 8232  loopback localhost  77814  0  77814  0  0  0
hme0 1500  server1 server1  10658  3  48325  0  279257  0

This option is used to diagnose the network problems when the connectivity is there but it is slow in response .

Values to look at:

* Collisions (Collis)
* Output packets (Opkts)
* Input errors (Ierrs)
* Input packets (Ipkts)

The above values will give information to workout

i. Network collision rate as follows :

Network collision rate = Output collision counts / Output packets

Network-wide collision rate greater than 10 percent will indicate

* Overloaded network,
* Poorly configured network,
* Hardware problems.

ii. Input packet error rate as follows :

Input Packet Error Rate = Ierrs / Ipkts.

If the input error rate is high (over 0.25 percent), the host is dropping packets. Hub/switch cables etc needs to be checked for potential problems.

C. Network socket & TCP Connection state

Netstat gives important information about network socket and tcp state . This is very useful in
finding out the open , closed and waiting network tcp connection .

Network states returned by netstat are following

CLOSED       ----  Closed.  The socket  is  not  being used.
LISTEN       ----  Listening for incoming connections.
SYN_SENT     ----  Actively trying to  establish  connection.
SYN_RECEIVED ---- Initial synchronization of the connection under way.
ESTABLISHED  ----  Connection has been established.
CLOSE_WAIT   ----  Remote shut down; waiting  for  the socket to close.
FIN_WAIT_1   ----  Socket closed; shutting  down  connection.
CLOSING      ----  Closed,
then   remote   shutdown; awaiting acknowledgement.
LAST_ACK     ----   Remote  shut  down,  then   closed ;awaiting acknowledgement.
FIN_WAIT_2   ----  Socket closed; waiting for shutdown from remote.
TIME_WAIT    ----  Wait after close for  remote  shutdown retransmission..

Example

#netstat -a

Local Address  Remote Address  Swind    Send-Q  Rwind  Recv-Q  State
*.*  *.*  0  0  24576  0  IDLE
*.22  *.*  0  0  24576  0  LISTEN
*.22  *.*  0  0  24576  0  LISTEN
*.*  *.*  0  0  24576  0  IDLE
*.32771  *.*  0  0  24576  0  LISTEN
*.4045  *.*  0  0  24576  0  LISTEN
*.25  *.*  0  0  24576  0  LISTEN
*.5987  *.*  0  0  24576  0  LISTEN
*.898  *.*  0  0  24576  0  LISTEN
*.32772  *.*  0  0  24576  0  LISTEN
*.32775  *.*  0  0  24576  0  LISTEN
*.32776  *.*  0  0  24576  0  LISTEN
*.*  *.*  0  0  24576  0  IDLE
192.168.1.184.22  192.168.1.186.50457  41992  0  24616  0  ESTABLISHED
192.168.1.184.22  192.168.1.186.56806  38912  0  24616  0  ESTABLISHED
192.168.1.184.22  192.168.1.183.58672  18048  0  24616  0  ESTABLISHED

if you see a lots of connections in FIN_WAIT state tcp/ip parameters have to be tuned because the
connections are not being closed and they gets accumulating . After some time system may run out of
resource . TCP parameter can be tuned to define a time out so that connections can be released and used by new connection.

Oracle DataBase Cloning

2010-07-29T11:56:00.000-07:00

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

IBM AIX Commands for Oracle DBAs

2010-07-29T11:49:00.000-07:00

Hi friends.. these are all the commands i collected for reference, hope it will be useful to you too..
i will be updating this post as soon as i come across new commands..
thanks for reading and keep in touch.

General Commands in AIX
oslevel:   Returns operating system level
bootinfo -y:To display if the hardware is 32-bit or 64-bit, type:
bootinfo –r
or

lsattr -E1 sys0 -a realmem: To display real memory in kilobytes (KB), type one of the following
bootinfo -k: To display if the kernel is 32-bit enabled or 64-bit enabled, type:
prtconf:
lscfg | grep proc:  to list the no of processors in the system

whence (program):   Returns full path of program
whereis (program): Returms full path of program
what (program):   Displays identifying info from the executable like version number, when
compiled.

lslpp -L all:   list all installed software
lslpp -L (program set name): Check if software installed
lslpp -f:   Lists filesets vs packages
lslpp -ha:   Lists installation history of filesets
instfix -ik (fix number eg IX66617): Checks id fix is installed

Uname –p : Displays the chip type of the system. For example, PowerPC
Uname –r : Displays the release number of the operating system
Uname –s : Displays the system name. For example, AIX.
Uname –n: Displays the name of the node.
Uname –a : Displays the system name, nodename, version, machine ID.
Uname –M : Displays the system model name. For example, IBM, 9114-275.
Uname –v : Displays the operating system version.
Uname –m: Displays the machine ID number of the hardware running the system.
Uname -u : Displays the system ID number.

• Examples :
instfix -ik 4330-02_AIX_ML

compress -c file.txt > file.Z: Create a compressed file.
ar -v -t (archive file):  List contents of an archive
ar -v -x (archive file): Extracts the archive.

alog -o -t boot: View the boot log
chtz (timezone eg GMT0BST): Change time zone.
chlang (language eg En_GB): Changes the language in /etc/environment file

Terminal Commands
tty:Displays what the tty/pty number of the terminal is.
chdev -l (device eg tty1) -a term=vt100: Sets tty to a vt100 terminal type
lscons: Displays the console device

Network Related Commands
host (ip or hostname): Resolves a hostname / ip address
hostname: Displays hostname
hostname (hostname): Sets the hostname until next reboot
chdev -l (device name) -a hostname=(hostname): Changes hostname permanently Examples :chdev -l inet0 -a hostname=thomas
ifconfig (device name): Displays network card settings
ifconfig (device name): up Turns on network card
ifconfig (device name): down Turns off network card
ifconfig (device name): detach Removes the network card from the network interface list
netstat –i: Displays interface statistics
traceroute (name or ipaddress): Displays all the hops from source to destination supplied.
ping -R (name or ipaddress): Same as traceroute except repeats.

Volume Management
Lspv: To display the number of hard disks on your system, type
Lspv hdisk1: To find details about hdisk1.

Crfs: create file system
            Eg: The following command will create, within volume group testvg, a jfs2 file system of 10MB with mounting point /fs2 and having read-only permissions:
crfs -v jfs2 -g testvg -a size=10M -p ro -m /fs2

Chfs: change size of file system
                Eg: To increase the /usr file system size by 1000000 512-byte blocks, type:

chfs -a size=+1000000 /usr

Mount: command to display information about all currently mounted file systems:
Options--- examples
1.       Mount cd rom: mount -V cdrfs -o ro /dev/cd0 /cdrom
2.       Mount file system: mount /dev/fslv02 /test
3.       Unmount filesystem: umount /test
4.       Mount all filesystem:mount {–a|all}
Defragfs: defragment file system

Performance Monitoring
Lsps –a : amount of paging space allocated and in use

increase a paging space:-
You can use the chps -s command to dynamically increase the size of a paging space. For example, if you want to increase the size of hd6 with 3 logical partitions, you issue the following command:

chps -s 3 hd6


I reduce a paging space:-
You can use the chps -d command to dynamically reduce the size of a paging space. For example, if you want to decrease the size of hd6 with four logical partitions, you issue the following command:

chps -d 4 hd6

ASM Architecture

2010-07-29T11:47:00.000-07:00

In ASM Architecture, Oracle database utilizes a separate smaller database instance, which is installed in a separate oracle home and created during database setup. An ASM instance manages the metadata that is needed to make ASM files available to regular database instances. ASM instance and database instances have access to a common set of disks called disk groups.

fig show difference between conventional storage and asm storage.

ASM Instance Background Processes

There are at least two new background processes added for an ASM instance:
   - RBAL - coordinates rebalance activity for disk groups
   - ORB0, ORB1… - These perform the actual rebalance data extent movements.
     There can be many of these at a time

Database Instance ASM Background Processes

Any database instance that is using an ASM disk group will contain a background process called OSMB. The OSMB process is responsible for communicating with the ASM instance. A second additional background process, called RBAL (just like in the ASM Instance) performs a global open on ASM disks. A global open means that more than one database instance can be accessing the ASM disks at a time.

For certain database operations like file creation, ASM intervention is required and the database foreground connects directly to the ASM instance. Whenever a connection is made to the ASM instance, the OSMB process is started dynamically. Database instances are only allowed to connect to one ASM instance at a time, so they have at most one OSMB background process.

Disk Group:- A basic component of ASM is the disk group. ASM is configured by creating disk groups, which in turn database instances can use as the default location for files created in the database. Oracle provides SQL statements to create and manage disk groups, their contents, and their metadata.
For information regarding commands to administer ASM go to ASM Administration post from home menu.

Allocation Units (AU):-ASM introduces the concept of an allocation unit (AU), the smallest contiguous disk space allocated by the ASM. The typical value for an AU is 1MB and is not user configurable. ASM does not allow physical blocks to be split across allocation units.

ASM Installation

2010-07-29T11:46:00.001-07:00

Automatic Storage Management is installed by default with Oracle Universal Installer. The Database Configuration Assistance (DBCA) looks for an existing ASM instance and if it does not find one, there is the option of creating and configuring one during the installation process. It is available in both the Enterprise Edition and Standard edition Installations.

Starting Up ASM Instance

An ASM instance is started like any other database instance, except that the initialization parameter file contains the parameter instance_type=ASM.

For ASM instances, the mount option does not try to mount a database, but tries to mount the diskgroups that are specified by the asm_diskgroups parameter. ASM instance requires smaller SGA (64MB) and usually brought up automatically on server reboots.

NOMOUNT:- does not mount any disk groups, but starts up the instance.

MOUNT:- mounts the disk goups specified by asm_diskgroups.

OPEN:- not valid for ASM instance.

FORCE:-issues the command shutdown abort to the instance and starts it as in normal database instance.

Shuting down ASM Instance
NORMAL:-ASM waits for any in-progress SQL to complete before performing an orderly dismount of all of the disk groups.
ASM waits for all of the currently connected users to disconnect from the instance.
If any database instances are connected to the ASM instance, then the SHUTDOWN command returns an error.

IMMEDIATE or TRANSACTIONAL:-ASM waits for any in-progress SQL to complete before performing an orderly dismount of all of the disk groups.
ASM does not wait for users currently connected to the instance to disconnect.
If any database instances are connected to the ASM instance, then the SHUTDOWN command returns an error.
Because the ASM instance does not contain any transactions, the TRANSACTIONAL mode is the same as the IMMEDIATE.ABORT:-The ASM instance immediately shuts down without the orderly dismount of disk groups.
This causes recovery to occur upon the next ASM startup.
If any database instance is connected to the ASM instance, then the database instance aborts.

Initialization Parameter Files for an ASM Instance

Below are the list of asm parameters, i'll only explain the relevant ones.

ASM_DISKGROUPS:-Name of the disk groups created.

ASM_DISKSTRING:- limits the set of disks that ASM considers for discovery.

ASM_POWER_LIMIT:- The maximum power on an ASM instance for disk rebalancing. Possible values range from 1 to 11 with 11 being the fastest.

INSTANCE_TYPE:- Must be set to ASM, this is the only required parameter. All other parameters assume defaults values for most environments.

LARGE_POOL_SIZE:- Internal packages used by ASM utilize the Large Pool. The value of large_pool_size should be set ot a value greater than 8MB.

DB_CACHE_SIZE:-
DIAGNOSTIC_DEST:-

PROCESSES:-
REMOTE_LOGIN_PASSWORDFILE:-
SHARED_POOL_SIZE:-

ASM_PREFERRED_READ_FAILURE_GROUPS:-

ASM Administration

2010-07-29T11:45:00.001-07:00

For ASM installation,startup and shutdown information go to ASM Installation post.

Disk groups are created using the CREATE DISKGROUP statement.

eg:

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY

FAILGROUP failure_group_1 DISK

'/devices/diska1' NAME diska1,

'/devices/diska2' NAME diska2,

FAILGROUP failure_group_2 DISK

'/devices/diskb1' NAME diskb1,

'/devices/diskb2' NAME diskb2;

The failgroup clause in the above example is for redundancey.

ie,

* NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.

* HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.

* EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

The above is therefore in normal redundancy.

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement.

-- Add disks.

ALTER DISKGROUP disk_group_1 ADD DISK

'/devices/disk*3',

'/devices/disk*4';

-- Drop a disk.

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement.

-- Resize a specific disk.

ALTER DISKGROUP disk_group_1

RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.

ALTER DISKGROUP disk_group_1

RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.

ALTER DISKGROUP disk_group_1

RESIZE ALL SIZE 100G;

The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.

ALTER DISKGROUP ALL DISMOUNT;

ALTER DISKGROUP ALL MOUNT;

ALTER DISKGROUP disk_group_1 DISMOUNT;

ALTER DISKGROUP disk_group_1 MOUNT;

Types of Joins

2010-07-29T11:43:00.001-07:00

join types

INNER JOIN

All of the joins that you have seen so far have used the natural join syntax—for example, to produce a list of customers and dates on which they placed orders. Remember that if this syntax is available, it will automatically pick the join attributes as those with the same name in both tables (intersection of the schemes). It will also produce only one copy of those attributes in the result table.

        SELECT cFirstName, cLastName, orderDate
        FROM customers NATURAL JOIN orders;

• The join does not consider the pk and fk attributes you have specified. If there are any non-pk/fk attributes that have the same names in the tables to be joined, they will also be included in the intersection of the schemes, and used as join attributes in the natural join. The results will certainly not be correct! This problem might be especially difficult to detect in cases where many natural joins are performed in the same query. Fortunately, you can always specify the join attributes yourself, as we describe next.

• Another keyword that produces the same results (without the potential attribute name problem) is the inner join. With this syntax, you may specify the join attributes in a USING clause. (Multiple join attributes in the USING clause are separated by commas.) This also produces only one copy of the join attributes in the result table. Like the NATURAL JOIN syntax, the USING clause is not supported by all systems.

        SELECT cFirstName, cLastName, orderDate
        FROM customers INNER JOIN orders
          USING (custID);

• The most widely-used (and most portable) syntax for the inner join substitutes an ON clause for the USING clause. This requires you to explicitly specify not only the join attribute names, but the join condition (normally equality). It also requires you to preface (qualify) the join attribute names with their table name, since both columns will be included in the result table. This is the only syntax that will let you use join attributes that have different names in the tables to be joined. Unfortunately, it also allows you to join tables on attributes other than the pk/fk pairs, which was a pre-1992 way to answer queries that can be written in better ways today.

        SELECT cFirstName, cLastName, orderDate
        FROM customers INNER JOIN orders
          ON customers.custID = orders.custID;

• You can save a bit of typing by specifying an alias for each table name (such as c and o in this example), then using the alias instead of the full name when you refer to the attributes. This is the only syntax that will let you join a table to itself, as we will see when we discuss recursive relationships.

        SELECT cFirstName, cLastName, orderDate
        FROM customers c INNER JOIN orders o
          ON c.custID = o.custID;

• All of the join statements above are specified as part of the 1992 SQL standard, which was not widely supported for several years after that. In earlier systems, joins were done with the 1986 standard SQL syntax. Although you shouldn’t use this unless you absolutely have to, you just might get stuck working on an older database. If so, you should recognize that the join condition is placed confusingly in the WHERE clause, along with all of the tests to pick the right rows:

        SELECT cFirstName, cLastName, orderDate
        FROM customers c, orders o
        WHERE c.custID = o.custID;

OUTER JOIN

One important effect of all natural and inner joins is that any unmatched PK value simply drops out of the result. In our example, this means that any customer who didn’t place an order isn’t shown. Suppose that we want a list of all customers, along with order date(s) for those who did place orders. To include the customers who did not place orders, we will use an outer join, which may take either the USING or the ON clause syntax.

        SELECT cFirstName, cLastName, orderDate
        FROM customers c LEFT OUTER JOIN orders o
          ON c.custID = o.custID;

All customers and order dates
cfirstname	clastname	orderdate
Tom	Jewett
Alvaro	Monge	2003-07-14
Alvaro	Monge	2003-07-18
Alvaro	Monge	2003-07-20
Wayne	Dick	2003-07-14

• Notice that for customers who placed no orders, any attributes from the Orders table are simply filled with NULL values.

• The word “left” refers to the order of the tables in the FROM clause (customers on the left, orders on the right). The left table here is the one that might have unmatched join attributes—the one from which we want all rows. We could have gotten exactly the same results if the table names and outer join direction were reversed:

        SELECT cFirstName, cLastName, orderDate
        FROM orders o RIGHT OUTER JOIN customers c
          ON o.custID = c.custID;

• An outer join makes sense only if one side of the relationship has a minimum cardinality of zero (as Orders does in this example). Otherwise, the outer join will produce exactly the same result as an inner join (for example, between Orders and OrderLines).

• The SQL standard also allows a FULL outer join, in which unmatched join attributes from either side are paired with null values on the other side. You will probably not have to use this with most well-designed databases.

EVALUATION ORDER

Multiple joins in a query are evaluated left-to-right in the order that you write them, unless you use parentheses to force a different evaluation order. (Some database systems require parentheses in any case.) The schemes of the joins are also cumulative in the order that they are evaluated; in RA, this means that

r1 r2 r3 = (r1 r2) r3.

• It is especially important to remember this rule when outer joins are mixed with other joins in a query. For example, if you write:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
        FROM customers LEFT OUTER JOIN orders 
          USING (custID)
          NATURAL JOIN orderlines;

you will lose the customers who haven’t placed orders. They will be retained if you force the second join to be executed first:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
        FROM customers LEFT OUTER JOIN 
          (orders NATURAL JOIN orderlines)
          USING (custID);

OTHER JOIN TYPES

For sake of completeness, you should also know that if you try to join two tables with no join condition, the result will be that every row from one side is paired with every row from the other side. Mathematically, this is a Cartesian product of the two tables, as you have seen before. It is almost never what you want. In pre-1992 syntax, it is easy to do this accidently, by forgetting to put the join condition in the WHERE clause:

        SELECT cFirstName, cLastName, orderDate
        FROM customers, orders;

• If your system is backward-compatible (most are), you might actually try this just to prove to yourself that the result is pure nonsense. However, if you ever have an occasion to really need a Cartesian product of two tables, use the new cross join syntax to prove that you really mean it. Notice that this example still produces nonsense.

        SELECT cFirstName, cLastName, orderDate
        FROM customers CROSS JOIN orders;

• It is possible, but confusing, to specify a join condition other than equality of two attributes; this is called a non-equi-join. If you see such a thing in older code, it probably represents a WHERE clause or subquery in disguise.

• You may also hear the term self join, which is nothing but an inner or outer join between two attributes in the same table. We’ll look at these when we discuss recursive relationships.