Thursday, April 25, 2013

Resume failed rman duplicate

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.

Monday, April 8, 2013

Cloning Oracle Database Without Connecting Catalog and Target using rman

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;

Tuesday, August 21, 2012

11g Release 2 RMAN Backup Compression

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
followed by ..
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

Thursday, August 5, 2010


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.
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
$ 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.
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.
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
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.

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 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.
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.
multiple options can be given at one time.
-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
-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.
$netstat -rn
Routing Table: IPv4
    Destination           Gateway               Flags  Ref   Use   Interface
-------------------- -------------------- ----- ----- ------ ---------          U        1   1444      le0           U        1   0            le0
default              UG       1   68276              UH       1   10497     lo0
This shows the output on a Solaris machine who’s IP address is with a default router at
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
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..
#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  41992  0  24616  0  ESTABLISHED  38912  0  24616  0  ESTABLISHED  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.

Thursday, July 29, 2010

Oracle DataBase Cloning

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:
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
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
# Database can now be opened normally.

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:

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.
# Database can now be opened normally.

STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.

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
STEP 10: Place the new database in archivelog mode

IBM AIX Commands for Oracle DBAs

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
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:
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

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 –nDisplays 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

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.