Sharing my experiences in Oracle + Linux + Perl + Java + Networking + High-Availability + Clusters

High-Availability/Cluster Configurations...

2012-03-22T21:25:00.001-07:00

Please visit www.intercloudzone.com

Configuring OpenStack SWIFT Storage Service on Cent OS 6.4 ( 3 Storage Nodes / 1 Swift Proxy Node)

Configuring DNS Master/Slave on RHEL/CentOS 6.5

Configuring Oracle11gR2 RAC (Real Application Clusters) on CentOS 5.8

Configuring Oracle Data Guard – Physical Standby with Fast-Start Fail Over using SuSE 11.2 and Oracle 11g.

Oracle RMAN Backup Solution (Open Source)

Configuring Teradata STANDBY Database.

Configuring SHMMAX and SHMALL for Oracle in Linux.

Configuring DRBD on Suse Linux 11.4 (x86_64) [Single Primary Mode]

Configuring DRBD with OCFS on Suse Linux 11.4 (x86_64) [Dual Primary Mode]

Bugs/Errors & Problems/Workarounds/Solutions & Scripts

Generating RSA SecurID Software Token for iPhone Devices/Windows Mobile/Android

2011-09-20T12:05:00.000-07:00

Home | Profile

So if your company decided to discontinue the RSA Hardtoken and moved on with SoftTokens; that requires installation and configuration of the softtoken on a PC. Although it's a onetime configuration, the issue occurs if you are outside at a friends/relatives place where in you had a need to login remotely, same installation+configuration will need to be done at your guest PC before you can generate a soft token and login!

But if you have an iPhone/Android or Windows Mobile, you can install the RSA app and with just few steps you can generate token code off your phone.

Here are the steps in nutshell, you can find detailed steps from RSA website.

1. Search and install the "RSA" app in your phone.
2. Click here to download the TokenConverter from RSA website.
3. Extract the contents to a directory.
4. Copy the .sdtid file that you recieved from your RSA support/company to this directory. Along with the
.sdtid file the directory will contain a sdti2tsf.dll and TokenConverter.exe
5. Open up a cmd (command shell), cd to this directory and run the below command.
TokenConverter user2-passwordtoken_file.sdtid -p password_for_the_token -mobile -o token.txt
6. Copy the contents from the token.txt file and copy to your gmail/yahoo/hotmail account(configured on your phone).
The CTF string would like something like below
com.rsa.securid.iphone://ctf?ctfData=20000206816472066313601117043277446107647716463
2456201026172115044046062716712650
After pasting it to the browser, select the string and click on hyper link and send yourself an e-mail
7. Login to your e-mail from your phone, click on this hyper link and it will prompt of the same password for token.

And you are good to go.

Hope this helps. Raj

Helpful Linux Commands/Linux tips-tricks

2011-09-06T12:58:00.000-07:00

Home | Profile

-- To list file with line number
nl <filename>

-- Saves man pages to text
man cat | col -b > less.txt

-- Mounting as a loop device

mount -o loop -t iso9660 /FC2-i386-DVD.iso /iso

-- To split a large file into multiple files
split --bytes 1m bigfile 1_

-- List of open files opened by raj under /dev/hda1

lsof -u raj -a /dev/hda1

-- Find who owns the port number

fuser -v -n tcp 992345

-- List of users accessing the /mnt file, -k option to kill all using the FS
fuser -v /mnt/*

-- File/File System status

stat afile

-- Trace System calls
strace -aef cat afile

-- Run Top in batch file

top -b -d 5 -n 3 >> top.out

b=batch, d=delay in seconds , n=number of iterations

-- To read headers of binary files (ELF)

readelf --file-header /bin/ls

-- Provides file system statistics

stat <filename>

-- List all network services running

netstat -tanup

-- List listening interfaces
netstat -l

-- Shread and remove a file

shred -n 50 -zuv afile

n=shred the file "n" times, -z will zero out the file in the last pass (extra protection), u=truncate it every time, v=verbose.

-- Create a function Calculator - calc 3+45/10

function calc { echo "{$1}" | bc -l ; }

-- Command line calculator
echo "10/2*10" | bc -l

-- Search man pages for string

man -k string

-- Merge pdf files into one
gs -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=Merged.pdf -dBATCH img*.pdf

-- Create pdf file from man page

man -t cat | ps2pdf - > cat.pdf

-- Colors the string while grepping

grep --color string filename

-- Copy files between Servers using netcat (Alternate option to consider if ftp is disabled )

On Server (in my case host graphics) run
tar -cvjf - dummy.dat | netcat -l -p 3333
On the Client ( in my case it was silicon) run
netcat graphics 3333 | pv -b | tar -xjvf -On the server listening port 3333 is created and dummy.dat is tar'd. When netcat is ran with no default options, it basically connects to the server/port specified.

-- Rsync files between servers
silicon~> rsync -avrz . --include "init*.ora" --rsh='ssh' graphics:/usr/oracle/admin
Above syncs all init.ora files from silicon to graphics a=archive,v=verbose,r=recursive,z=compress

-- List all network services running on the system
netstat -tanup

-- Test FTP Speed. Use dd to create a large file, but instead of sending it to the disk send it to null device. Here
/dev/zero is the input and output of the dd is /dev/null.
put "|dd if=/dev/zero bs=1024 count=1000000" /dev/null

-- Find all files with group owner is "oracle" and change it to "dba" group.
find . -group oracle -exec chgrp dba '{}' \ ;

--Show files greater than a specific size
find ./ -size +100M -type f -print0 | xargs -0 ls -Ssh1 --color

-- Send e-mail attachments with mutt
mutt -a file.txt -s "A Text File" raj.anju@gmail.com < /tmp/junk

-- Kill multiple processes in one shot
ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9

-- Copy directory & files under remotely.
scp -r RMANREPO/ oracle@eccas1004:/usr/oracle/admin/RMANREPO/

-- Improve ftp speed with jumbo frames.
Note: Read more on the Jumbo frames, as its applicable only on certain situations. On a 10/100 data the max mtu can only be upto 1500, but for a GigaBit Ethernet connection it can go upto 9000. The best throughput I got while doing an XO (Cross-Over) connection between two systems is when the mtu was 6000.
ifconfig eth0 mtu 6000
For a permanent change you have to edit the below file to include MTU 6000
/etc/sysconfig/network-script/ifcfg-eth0

java.sql.SQLException: Internal - Unexpected value

2011-09-06T10:20:00.000-07:00

Home | Profile

Below is an error that we got while trying to run a JDBC Java Program from a Mainframe zOS client against Oracle 11g Database. SR was logged and finally oracle concluded it as a bug - 12851493 (Refer Metalink/Oracle Support site for more details). While hunting for some leads about this error, couldn't find any helpful sites, so thought to post it here and I hope it will be of help to someone.

Problem

java.sql.SQLException: Internal - Unexpected value
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOsesskey(T4CTTIoauthenticate.java:347)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:364)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:508)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:203)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:510)
at java.sql.DriverManager.getConnection(DriverManager.java:322)
at java.sql.DriverManager.getConnection(DriverManager.java:358)
at TestJDBC.main(TestJDBC.java:11)

Workaround ( Suggested by Oracle Support)

Is to use the file.encoding=UTF-8 in the JVM startup or the program itself.

java -Dfile.encoding=UTF-8 TestJDBC

Environment Details

=== JDBC / JDK Driver Environment ===

Version:

java -version
java version "1.6.0"
Java(TM) SE Runtime Environment (build pmz3160sr9fp1-20110303_01(SR9 FP1))
IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 z/OS s390-31 jvmmz3160sr9-20110203_74623 (JIT enabled, AOT enabled)
J9VM - 20110203_074623
JIT - r9_20101028_17488ifx3
GC - 20101027_AA)
JCL - 20110203_01

Client Operating System

uname -a
OS/390 SYG1 22.00 03 2817

Database Operating System

Linux eccdb675 2.6.16.60-0.39.3-smp #1 SMP Mon May 11 11:46:34 UTC 2009 x86_64 x86_64 x86_64 GNU/Linux

Database Version

11.2.0.2

Bugs-Errors-Problems-Workarounds-Solutions-Scripts

2011-09-02T11:52:00.000-07:00

Home | Profile

Some of the information compiled below includes some errors/bugs that I encountered at work and on home projects and solution/work arounds or fixes you may call and few scripts and commands that I gathered from different sources over the course of my career. Hopefully this will serve as a good reference point. Thanks.

Helpful Linux Commands/Linux tips-tricks

java.sql.SQLException: Internal - Unexpected value

Generating RSA SecurID Software Token for iPhone Devices/Windows Mobile/Android

Configuring DRBD with OCFS2 on Suse Linux 11.4 - Dual Primary Mode

2011-07-08T09:46:00.000-07:00

Home | Profile

Configuring DRBD with OCFS2 on Suse Linux 11.4 - Dual Primary Mode

I started with the single primary mode configuration, as I explained in my previous article. This way, I get the DRBD configuration right and tested out before moving to oracle cluster configuration (OCFS2 + DRBD). First thing is to get OCFS2 and O2CB installed, so go to software.opensuse.org and search for ocfs2. You will need to install the ocfs2* and *o2cb* rpm packages.

-- Download and Install the Software

silicon:/home/raj/downloads/ocfs2 # wget http://software.opensuse.org/search/download?base=openSUSE%3A11.4&file=openSUSE%3A%2F11.4%2Fstandard%2Fx86_64%2Focfs2console-1.6.3-5.3.x86_64.rpm

silicon:/home/raj/downloads/ocfs2 # wget http://software.opensuse.org/search/download?base=openSUSE%3A11.4&file=openSUSE%3A%2F11.4%2Fstandard%2Fx86_64%2Focfs2-tools-o2cb-1.6.3-5.3.x86_64.rpm

silicon:/home/raj/downloads/ocfs2 # wget http://software.opensuse.org/search/download?base=openSUSE%3A11.4&file=openSUSE%3A%2F11.4%2Fstandard%2Fx86_64%2Focfs2-tools-1.6.3-5.3.x86_64.rpm

silicon:/home/raj/downloads/ocfs2 # zypper in ocfs2console-1.6.3-5.3.x86_64.rpm

silicon:/home/raj/downloads/ocfs2 # zypper in ocfs2-tools-o2cb-1.6.3-5.3.x86_64.rpm

--Create a directory called ocfs2 under /etc/

silicon:/home/raj/downloads/ocfs2 # mkdir -p /etc/ocfs2/

--Edit cluster.conf file with below entries. You can choose any randpom ports that are not in use. Refering back to my previous article (DRBD - Single Primary Mode) , silicon (192.168.5.100) is my primary host and graphics (192.168.5.101) is my secondary one.

silicon:/etc/ocfs2 # cat cluster.conf

node:

ip_port = 7777

ip_address = 192.168.5.100

number = 0

name = silicon

cluster = ocfs2

node:

ip_port = 7777

ip_address = 192.168.5.101

number = 1

name = graphics

cluster = ocfs2

cluster:

node_count = 2

name = ocfs2

-- Configure the o2cb service.

Remember to give the cluster name as "ocfs2" on the prompt, or the name you have given in the cluster.conf file.

silicon:/etc/drbd.d # /etc/init.d/o2cb configure

Configuring the O2CB driver.

This will configure the on-boot properties of the O2CB driver.

The following questions will determine whether the driver is loaded on

boot. The current values will be shown in brackets ('[]'). Hitting

<ENTER> without typing an answer will keep that current value. Ctrl-C

will abort.

Load O2CB driver on boot (y/n) [y]:

Cluster stack backing O2CB [o2cb]:

Cluster to start on boot (Enter "none" to clear) []: ocfs2

Specify heartbeat dead threshold (>=7) [31]:

Specify network idle timeout in ms (>=5000) [30000]:

Specify network keepalive delay in ms (>=1000) [2000]:

Specify network reconnect delay in ms (>=2000) [2000]:

Writing O2CB configuration: OK

Setting cluster stack "o2cb": OK

Starting O2CB cluster ocfs2: OK

-- Add below to resource.res

…

resource data01 {

on silicon {

device /dev/drbd01;

disk /dev/vgPQ1/lvPQ1_dfs06;

address 192.168.5.100:7790;

meta-disk internal;

}

on graphics {

device /dev/drbd01;

disk /dev/vgPQ1/lvPQ1_dfs06;

address 192.168.5.101:7790;

meta-disk internal;

}

…

-- scp the cluster.conf and resource.res to the 2nd node - graphics.

silicon:/etc/ocfs2 # scp cluster.conf graphics:/etc/ocfs2/

cluster.conf 100% 269 0.3KB/s 00:00

silicon:/etc/drbd.d # scp resource.res graphics:/etc/drbd.d/

resource.res 100% 795 0.8KB/s 00:00

-- Create a LVM Partition. You can use the device partitions instead of LVM's.

silicon:/etc/drbd.d # lvcreate -L 10G -n lvPQ1_dfs06 vgPQ1

Logical volume "lvPQ1_dfs06" created

-- Create metadata, sync and attach.

silicon:/etc/drbd.d # drbdadm create-md data01

Writing meta data...

initializing activity log

NOT initialized bitmap

New drbd meta data block successfully created.

Success

-- Check the status.
Note: "data00" is what I configured for single-primary-mode. "data01" is the one we currently should pay attention to - the dual-primay-mode.

silicon:/etc/drbd.d # drbd-overview.pl

0:data00 Connected Primary/Secondary UpToDate/UpToDate C r-----

1:data01

silicon:/etc/drbd.d # drbdadm syncer data01

silicon:/etc/drbd.d # drbdadm attach data01

-- Check the status after connect

silicon:/etc/drbd.d # drbd-overview.pl

0:data00 Connected Primary/Secondary UpToDate/UpToDate C r-----

1:data01 StandAlone Secondary/Unknown Inconsistent/DUnknown r----s

-- Repeat same steps on the 2nd node - graphics.

graphics:/etc/drbd.d # drbdadm create-md data01

Writing meta data...

initializing activity log

NOT initialized bitmap

New drbd meta data block successfully created.

Success

graphics:/etc/drbd.d # drbdadm syncer data01

graphics:/etc/drbd.d # drbdadm attach data01

graphics:/etc/drbd.d # drbd-overview.pl

0:data00 Connected Secondary/Primary UpToDate/UpToDate C r-----

1:data01 StandAlone Secondary/Unknown Inconsistent/DUnknown r----s

-- Connect the device on Primary - silicon.

silicon:/etc/drbd.d # drbdadm connect data01

Monitor the progress. Pay attention to the changes that occurs on the "cs" (Connection Status), "ds" (Disk Status), "ns" Netowrk Status.

silicon:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:76 nr:0 dw:116132 dr:2189 al:36 bm:15 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:WFConnection ro:Secondary/Unknown ds:Inconsistent/DUnknown C r----s

ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:10485404

-- Connect the device on Secondary - graphics.

graphics:/etc/drbd.d # drbdadm connect data01

graphics:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----

ns:0 nr:76 dw:116304 dr:1485 al:4 bm:7 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r-----

ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:10485404

-- Run the below on one of the nodes.

silicon:/etc/drbd.d # drbdadm -- --overwrite-data-of-peer primary data01

silicon:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:76 nr:0 dw:116132 dr:2189 al:36 bm:15 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----

ns:3072 nr:0 dw:0 dr:3744 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:10482332

[>....................] sync'ed: 0.1% (10236/10236)M

finish: 1:49:11 speed: 1,536 (1,536) K/sec

graphics:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----

ns:0 nr:76 dw:116304 dr:1485 al:4 bm:7 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:SyncTarget ro:Secondary/Primary ds:Inconsistent/UpToDate C r-----

ns:0 nr:261760 dw:261760 dr:0 al:0 bm:15 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:10223644

[>....................] sync'ed: 2.5% (9984/10236)M

finish: 2:06:27 speed: 1,324 (1,280) want: 250 K/sec

You can see the progress of the sync by running cat /proc/drbd. Once the sync is complete, you can see the change of status in "ds" for both primary and secondary nodes. It will show up as "UpToDate/UpToDate".

graphics:/etc/drbd.d # cat /proc/drbd Thu Jun 30 07:16:01 2011

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:76 nr:0 dw:116132 dr:2189 al:36 bm:15 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:10618904 nr:0 dw:134792 dr:10486946 al:120 bm:640 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

-- Once the sync is done, create the file system on the drbd device. /dev/drbd01

silicon:/etc/drbd.d # mkfs -t ocfs2 -N 2 -L ocfs2_drbd01 /dev/drbd01

mkfs.ocfs2 1.6.3

Cluster stack: classic o2cb

Label: ocfs2_drbd01

Features: sparse backup-super unwritten inline-data strict-journal-super xattr

Block size: 4096 (12 bits)

Cluster size: 4096 (12 bits)

Volume size: 10737053696 (2621351 clusters) (2621351 blocks)

Cluster groups: 82 (tail covers 8615 clusters, rest cover 32256 clusters)

Extent allocator size: 8388608 (2 groups)

Journal size: 67108864

Node slots: 2

Creating bitmaps: done

Initializing superblock: done

Writing system files: done

Writing superblock: done

Writing backup superblock: 2 block(s)

Formatting Journals: done

Growing extent allocator: done

Formatting slot map: done

Formatting quota files: done

Writing lost+found: done

mkfs.ocfs2 successful

-- Add the "startup" and "net" portions to the resource file on both the nodes (silicon & graphics).

startup {
    become-primary-on both;
}
net {
    allow-two-primaries;
    after-sb-0pri discard-zero-changes;
    after-sb-1pri discard-secondary;
    after-sb-2pri disconnect;
}

resource data01 {

on silicon {

device /dev/drbd01;

disk /dev/vgPQ1/lvPQ1_dfs06;

address 192.168.5.100:7790;

meta-disk internal;

}

on graphics {

device /dev/drbd01;

disk /dev/vgPQ1/lvPQ1_dfs06;

address 192.168.5.101:7790;

meta-disk internal;

}

-- Bounce nodes on both primary and secondary.

silicon:/etc/drbd.d # drbdadm down data01

silicon:/etc/drbd.d # drbdadm up data01

silicon:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:0 nr:0 dw:0 dr:672 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:SyncTarget ro:Secondary/Secondary ds:Inconsistent/UpToDate C r-----

ns:0 nr:2832 dw:2832 dr:0 al:0 bm:17 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:872

[===================>] sync'ed:100.0% (872/3704)K

finish: 0:00:00 speed: 1,416 (1,416) want: 250 K/sec

-- Make the first node primary.

silicon:/etc/drbd.d # drbdadm primary data01

silicon:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r-----

ns:0 nr:0 dw:0 dr:672 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:Connected ro:Primary/Primary ds:UpToDate/UpToDate C r-----

ns:0 nr:3704 dw:3704 dr:672 al:0 bm:81 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

-- Bounce the secondary node.

graphics:/etc/drbd.d # drbdadm down data01

graphics:/etc/drbd.d # drbdadm up data01

graphics:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----

ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----

ns:3704 nr:0 dw:0 dr:3704 al:0 bm:81 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

-- Convert the 2nd node to primary.

graphics:/etc/drbd.d # drbdadm primary data01

graphics:/etc/drbd.d # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r-----

ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:Connected ro:Primary/Primary ds:UpToDate/UpToDate C r-----

ns:3704 nr:0 dw:0 dr:4376 al:0 bm:81 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

-- Mount the device to a directory on both nodes.

silicon:/etc/drbd.d # mount /dev/drbd01 /u06 -t ocfs2

graphics:/etc/drbd.d # mount /dev/drbd01 /u06 -t ocfs2

-- Touch a test file, you can see the file appearing on the secondary node. You can run a "tail -f afile" command on one of the nodes on a different ssh/telnet session and redirect some data to this file on the other node where in you can monitor a live transfer of the data.

silicon:/u06 # touch afile

graphics:/u06 # ls -ltr

total 388

drwxr-xr-x 2 root root 3896 Jun 30 22:32 lost+found

-rw-r--r-- 1 root root 394065 Jun 30 2011 afile

Run the tail -f afile first and do the redirect on the other node. As this is dual primary mode you can swap these commands between both nodes.

graphics:/u06 # tail -f afile

silicon:/u06 # cat /var/log/messages > afile

If you get the configuration right, you will be able to see output right away as and when you redirect the output to this file on the silicon.

Below are some additional commands with which you can admin the o2cb service.

"/etc/init.d/o2cb offline" - Takes the clusters offline.
"/etc/init.d/o2cb online" - Brings the clusters online.
"/etc/init.d/o2cb load" - Loads all OCFS2 modules.
"/etc/init.d/o2cb unload" - Unloads all OCFS2 modules.

References:

OCFS2 Documentation - http://oss.oracle.com/projects/ocfs2/dist/documentation/v1.2/ocfs2_users_guide.pdf

Here's one of the issues I ran into - a likely split-brain scenario where I couldn't get both nodes to talk. I ran drbdadm up data01 and here's the output I got.

silicon:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:WFConnection ro:Secondary/Unknown ds:UpToDate/Outdated C r----s
ns:0 nr:0 dw:0 dr:1680 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4194140

Same on the secondary node too..

graphics:~ # cat /proc/drbd

version: 8.3.9 (api:88/proto:86-95)

srcversion: A67EB2D25C5AFBFF3D8B788

0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----

ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

1: cs:StandAlone ro:Primary/Unknown ds:UpToDate/Outdated r----s

ns:0 nr:0 dw:0 dr:672 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4194140

I haven't dig deeper into how to resolve this issue, but here's how I got both back. Ran the disconnect on the primary (one of the nodes) and discarded the data, which put the connection status (cs) to "WFConnection"

silicon:~ # drbdadm secondary data01

silicon:~ # drbdadm disconnect data01
silicon:~ # drbdadm -- --discard-my-data connect data01
silicon:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:WFConnection ro:Secondary/Unknown ds:UpToDate/Outdated C r----s
ns:0 nr:0 dw:0 dr:1680 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4194140
silicon:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:WFConnection ro:Secondary/Unknown ds:UpToDate/Outdated C r----s
ns:0 nr:0 dw:0 dr:1680 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4194140

Once done, I ran the connect on the 2nd node, i.e secondary.

graphics:~ # drbdadm connect data01
graphics:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
ns:2176 nr:0 dw:0 dr:3856 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4191964
[>....................] sync'ed: 0.1% (4191964/4194140)K
finish: 0:29:06 speed: 2,176 (2,176) K/sec

silicon:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:SyncTarget ro:Secondary/Primary ds:Inconsistent/UpToDate C r-----
ns:0 nr:8320 dw:8320 dr:1680 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:4185820
[>....................] sync'ed: 0.3% (4185820/4194140)K
finish: 0:49:49 speed: 1,384 (1,384) want: 250 K/sec

silicon:~ # drbdadm primary data01
silicon:~ # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:Connected ro:Primary/Primary ds:UpToDate/UpToDate C r-----
ns:0 nr:4194140 dw:4194140 dr:2352 al:0 bm:256 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

silicon:/u06 # service o2cb status
Driver for "configfs": Loaded
Filesystem "configfs": Mounted
Stack glue driver: Loaded
Stack plugin "o2cb": Loaded
Driver for "ocfs2_dlmfs": Loaded
Filesystem "ocfs2_dlmfs": Mounted
Checking O2CB cluster ocfs2: Online
Heartbeat dead threshold = 31
Network idle timeout: 30000
Network keepalive delay: 2000
Network reconnect delay: 2000
Checking O2CB heartbeat: Not active

silicon:/u06 # mounted.ocfs2 -f

Device FS Nodes

/dev/mapper/vgPQ1-lvPQ1_dfs06 ocfs2 Not mounted

/dev/drbd1 ocfs2 Not mounted

silicon:/u06 # mount /dev/drbd01 /u06 -t ocfs2

silicon:/u06 # mounted.ocfs2 -f

Device FS Nodes

/dev/mapper/vgPQ1-lvPQ1_dfs06 ocfs2 silicon

/dev/drbd1 ocfs2 silicon

graphics:/u06 # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:UpToDate/UpToDate C r-----
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0
1: cs:Connected ro:Primary/Primary ds:UpToDate/UpToDate C r-----
ns:4194140 nr:0 dw:0 dr:4195852 al:0 bm:256 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

graphics:/u06 # mounted.ocfs2 -f

Device FS Nodes

/dev/mapper/vgPQ1-lvPQ1_dfs06 ocfs2 Not mounted

/dev/drbd1 ocfs2 Not mounted

graphics:/u06 # mount /dev/drbd01 /u06 -t ocfs2

graphics:/u06 # mounted.ocfs2 -f

Device FS Nodes

/dev/mapper/vgPQ1-lvPQ1_dfs06 ocfs2 silicon, graphics

/dev/drbd1 ocfs2 silicon, graphics

silicon:/u06 # service o2cb status

Driver for "configfs": Loaded

Filesystem "configfs": Mounted

Stack glue driver: Loaded

Stack plugin "o2cb": Loaded

Driver for "ocfs2_dlmfs": Loaded

Filesystem "ocfs2_dlmfs": Mounted

Checking O2CB cluster ocfs2: Online

Heartbeat dead threshold = 31

Network idle timeout: 30000

Network keepalive delay: 2000

Network reconnect delay: 2000

Checking O2CB heartbeat: Active

graphics:/u06 # service o2cb status

Driver for "configfs": Loaded

Filesystem "configfs": Mounted

Stack glue driver: Loaded

Stack plugin "o2cb": Loaded

Driver for "ocfs2_dlmfs": Loaded

Filesystem "ocfs2_dlmfs": Mounted

Checking O2CB cluster ocfs2: Online

Heartbeat dead threshold = 31

Network idle timeout: 30000

Network keepalive delay: 2000

Network reconnect delay: 2000

Checking O2CB heartbeat: Active

graphics:/u06 # touch file_created_at_graphics

graphics:/u06 # ls -ltr

total 0

drwxr-xr-x 2 root root 3896 Jun 30 22:32 lost+found

drwxr-xr-x 6 oracle oinstall 3896 Jul 17 18:07 oracle

-rw-r--r-- 1 root root 0 Aug 21 23:07 file_created_at_graphics

silicon:/u06 # ls -ltr

total 0

drwxr-xr-x 2 root root 3896 Jun 30 22:32 lost+found

drwxr-xr-x 6 oracle dba 3896 Jul 17 18:07 oracle

-rw-r--r-- 1 root root 0 Aug 21 23:07 file_created_at_graphics

Hope this helps.

Raj...

Configuring DRBD on Suse Linux - Distributed Replicated Block Device [Single Primary Node]

2011-07-05T13:41:00.000-07:00

Home | Profile

Here's the step-by-step approach I took to setup DRBD (single-primary node) between two identical servers with Suse 11.4 (x86_64) installed on each. The website www.drbd.org is very organized and has very detailed documentation and so setup was easy to follow and understand.

My goal is to try the single-primary mode first and move on to the HA (High Availability) cluster setup using OCFS2 (Oracle Cluster File System ) and try to setup two Oracle Instances on this HA cluster and see if I can get two regular instances to be configured like a "DataGuard", without having to do a real Oracle specific DataGuard Configuration.

Primary Host is named as "silicon" and secondary as "graphics".

Download and Install the Software

Go to software.opensuse.org and search for the package "drbd". You can find both 32 and 64 bit packages and download the one that's compatible to your OS.

silicon: # cd /home/raj/downloads/drbd

silicon:/home/raj/downloads/drbd # wget "http://software.opensuse.org/search/download?base=openSUSE%3A11.4&file=openSUSE%3A%2F11.4%2Fstandard%2Fx86_64%2Fdrbd-8.3.8.1-4.3.x86_64.rpm"

silicon:/home/raj/downloads/drbd # rpm -Uvh drbd-8.3.8.1-4.3.x86_64.rpm
Preparing... ########################################### [100%]
   1:drbd ########################################### [100%]

silicon:/home/raj/downloads/drbd # rpm -qa | grep drbd | xargs rpm -qi
Name : drbd Relocations: (not relocatable)
Version : 8.3.8.1 Vendor: openSUSE
Release : 4.3 Build Date: Tue Feb 22 18:54:32 2011
Install Date: Sat Jun 25 16:47:05 2011 Build Host: build35
Group : Productivity/Clustering/HA Source RPM: drbd-8.3.8.1-4.3.src.rpm
Size : 461140 License: GPLv2+
Signature : RSA/8, Tue Feb 22 18:54:58 2011, Key ID b88b2fd43dbdc284
Packager : http://bugs.opensuse.org
URL : http://www.drbd.org/
Summary : Distributed Replicated Block Device
Description :
Drbd is a distributed replicated block device. It mirrors a block
device over the network to another machine. Think of it as networked
raid 1. It is a building block for setting up clusters.

Authors: --------
   Philipp Reisner <philipp.reisner@linbit.com>
   Lars Ellenberg <lars.ellenberg@linbit.com>
Distribution: openSUSE 11.4

Upgrade DRBD version to 8.3.9

At some point I ran into an error below, and on searching for the solution I found this article "How To Upgrade DRBD Userland Version To 8.3.9 Under OpenSUSE 11.4" that clearly explains the reason for the error and steps to fix it.

"Starting DRBD resources: DRBD module version: 8.3.9
  userland version: 8.3.8
   you should upgrade your drbd tools!"

Below are the steps that I had to do to get the drbd module compiled to 8.3.9 with the existing kernel. It's no different from the steps detailed in the document listed above, I just decided to include these for continuity reasons.

-- Install dependencies first.
silicon: # zypper install kernel-source gcc flex make ncurses-devel

-- Create .config file of the current running kernel
silicon:/usr/src/linux # cd /usr/src/linux
silicon:/usr/src/linux # cp /boot/config-2.6.37.6-0.5-desktop ./.config

-- Run menuconfig
silicon:/usr/src/linux # make menuconfig
silicon:/usr/src/linux # cd ../
silicon:/usr/src # wget http://oss.linbit.com/drbd/8.3/drbd-8.3.9.tar.gz
asking libproxy about url 'http://oss.linbit.com/drbd/8.3/drbd-8.3.9.tar.gz'
libproxy suggest to use 'direct://'
--2011-06-25 23:56:12-- http://oss.linbit.com/drbd/8.3/drbd-8.3.9.tar.gz
Resolving oss.linbit.com... 212.69.161.111
Connecting to oss.linbit.com|212.69.161.111|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 497886 (486K) [application/x-gzip]
Saving to: `drbd-8.3.9.tar.gz'

100%[=========================================================================>] 497,886 454K/s in 1.1s

2011-06-25 23:56:14 (454 KB/s) - `drbd-8.3.9.tar.gz' saved [497886/497886]

-- Unzip the downloaded tar file
silicon:/usr/src # cd drbd-8.3.9/
silicon:/usr/src/drbd-8.3.9 # ./configure --prefix=/usr --localstatedir=/var --sysconfdir=/etc --with-utils --with-pacemaker
silicon:/usr/src/drbd-8.3.9 # make KDIR=/usr/src/linux
silicon:/usr/src/drbd-8.3.9 # make install

By now you got the drbd version compiled to version 8.3.9 with the current kernel module.

Configuration of DRBD

You don't necessarily have to use LVM's, instead can use partitions created like /dev/sda1 , /dev/sdb1 etc. I decided to go with LVM's since all my free space is already added to a volume group.

-- Create a Logical Volume
silicon:/etc/drbd.d # lvcreate -L 3G -n lvPQ1_dfs05 vgPQ1
  Logical volume "lvPQ1_dfs05" created

-- Include below in /etc/drbd.conf
include "/etc/drbd.d/global_common.conf";
include "/etc/drbd.d/*.res";

-- Edit resource.res
Device name /dev/drbd00 is an arbitrary name, but is the one that DRBD will use as the device name. On my initial setup I want to use the host - "silicon" as primary and the IP Address of the same is 192.168.5.100 and my secondary node - "graphics" has got the IP Address 192.168.5.101.

silicon:/etc/drbd.d # cat resource.res
resource data00 {
  on silicon {
   device /dev/drbd00;
   disk /dev/vgPQ1/lvPQ1_dfs05;
   address 192.168.5.100:7789;
   meta-disk internal;
  }
  on graphics {
   device /dev/drbd00;
   disk /dev/vgPQ1/lvPQ1_dfs05;
   address 192.168.5.101:7789;
   meta-disk internal;
  }
}

-- Now scp both resource.res and drbd.conf files to the secondary node - graphics

silicon:/etc/drbd.d # scp resource.res 192.168.2.101:/etc/drbd.d/
resource.res 100% 305 0.3KB/s 00:00

silicon:/etc # scp drbd.conf 192.168.2.101:/etc/
drbd.conf 100% 177 0.2KB/s 00:00

-- Create the LVM on the secondary node.
graphics:~ # lvcreate -L 3G -n lvPQ1_dfs05 vgPQ1
  Logical volume "lvPQ1_dfs05" created

Create, Load, Sync, Attach and Run DRBD module.

-- Below will create the metadata on the device.
silicon:/etc/drbd.d # drbdadm create-md data00

  --== Thank you for participating in the global usage survey ==--
The server's response is:
you are the 3752th user to install this version
Writing meta data...
initializing activity log
NOT initialized bitmap
New drbd meta data block successfully created.
success

-- Load the module to kernel.
silicon:/etc/drbd.d # modprobe drbd

-- Verify if the module is loaded.
silicon:/etc/drbd.d # lsmod | grep drbd
drbd 272649 0
lru_cache 8007 1 drbd

-- Sync and Connect the device
silicon:/etc/drbd.d # drbdadm syncer data00
silicon:/etc/drbd.d # drbdadm connect data00

-- By running cat /proc/drbd you can monitor the progress of the sync.
silicon:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r-----
   ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:3145596

-- Repeat all the above steps on the secondary node. (graphics)
graphics:/etc/drbd.d # drbdadm create-md data00
graphics:/etc/drbd.d # drbdadm attach data00
graphics:/etc/drbd.d # drbdadm syncer data00
graphics:/etc/drbd.d # drbdadm connect data00
graphics:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r-----
   ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:3145596

-- Once both devices are connected, now its' time to sync both devices.

Run the below only the node which you want to make as primary. I choose "silicon"

silicon:/etc/drbd.d # drbdadm -- --overwrite-data-of-peer primary data00

silicon:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r-----
   ns:6016 nr:0 dw:0 dr:6688 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:3139580
   [>....................] sync'ed: 0.3% (3139580/3145596)K
   finish: 0:32:42 speed: 1,504 (1,504) K/sec

In my case a 2 GB partition took around ~30mts. Both the nodes are connected through a XO (Cross Over Cable Cat 5e), the max speed I got on this connection was around ~70MBps, although the drbd sync speed was around 250KB/sec (see below). I yet have to figure out how to get a better throughput.

graphics:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:SyncTarget ro:Secondary/Primary ds:Inconsistent/UpToDate C r-----
   ns:0 nr:48640 dw:48640 dr:0 al:0 bm:2 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:3096956
   [>....................] sync'ed: 1.6% (3096956/3145596)K
   finish: 0:38:18 speed: 1,332 (1,312) want: 250 K/sec

You can watch the progress in real time by running the cat /proc/drbd with "watch" command.

watch cat /proc/drbd

Once the sync is complete, status would show the ro as "Primary/Secondary". Here's another way to check the status.
silicon:/etc/drbd.d # /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
m:res cs ro ds p mounted fstype
0:data00 Connected Primary/Secondary UpToDate/UpToDate C

Now create the File System on the DRBD device, not on the LVM or the disk partition .

silicon:~ # mkfs -t ext4 /dev/drbd00
mke2fs 1.41.14 (22-Dec-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
196608 inodes, 786399 blocks
39319 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=805306368
24 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
   32768, 98304, 163840, 229376, 294912

Writing inode tables: done
Creating journal (16384 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 39 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

After the file system is created mount the device.

silicon:~ # mount /dev/drbd00 /u05
silicon:~ # mount | grep drbd
/dev/drbd00 on /u05 type ext4 (rw,relatime,barrier=1,data=ordered)

Create a file under the DRBD device, where the device is mounted. This is to verify/test role switch over to see if the files came over after after the primary is switched to secondary and vice versa.
silicon:/u05 # cat /var/log/messages > afile

Switch roles between Primary and Secondary.

Disconnect devices on both primary and secondary hosts.You can run drbdadm down data00 which will disconnect and detach the devices all in once.
silicon:/etc/drbd.d # drbdadm disconnect data00
silicon:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:StandAlone ro:Primary/Unknown ds:UpToDate/DUnknown r-----
   ns:116108 nr:8 dw:116116 dr:1441 al:36 bm:2 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

graphics:/etc/drbd.d # drbdadm disconnect data00
graphics:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:StandAlone ro:Secondary/Unknown ds:UpToDate/DUnknown r-----
   ns:0 nr:116108 dw:116108 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

Now change the role on the graphics to primary and connect the device.
graphics:/etc/drbd.d # drbdadm primary data00

graphics:/etc/drbd.d # drbdadm connect data00
graphics:/etc/drbd.d # cat /proc/drbd
version: 8.3.9 (api:88/proto:86-95)
srcversion: A67EB2D25C5AFBFF3D8B788
0: cs:WFConnection ro:Primary/Unknown ds:UpToDate/DUnknown C r-----
   ns:0 nr:0 dw:116108 dr:1008 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:f oos:0

Run the below on the now secondary node - silicon
silicon # drbdadm -- --discard-my-data connect data00
silicon # drbd-overview.pl
  0:data00 Connected Secondary/Primary UpToDate/UpToDate C r-----

Mount the file system and look for the file created on the silicon.
graphics:/etc/drbd.d # mount /dev/drbd00 /u05
graphics:/etc/drbd.d # cd /u05
graphics:/u05 # ls -ltr
total 288
drwx------ 2 root root 16384 Jun 29 20:47 lost+found
-rw-r--r-- 1 root root 275360 Jun 29 20:47 messages

Hope this helps. Regards, Raj.

Configuring SHMMAX and SHMALL for Oracle in Linux

2010-12-05T16:28:00.000-08:00

Home | Profile

SHMMAX and SHMALL are two key shared memory parameters that directly impact’s the way by which Oracle creates an SGA. Shared memory is nothing but part of Unix IPC System (Inter Process Communication) maintained by kernel where multiple processes share a single chunk of memory to communicate with each other.

While trying to create an SGA during a database startup, Oracle chooses from one of the 3 memory management models a) one-segment or b) contiguous-multi segment or c) non-contiguous multi segment. Adoption of any of these models is dependent on the size of SGA and values defined for the shared memory parameters in the linux kernel, most importantly SHMMAX.

So what are these parameters - SHMMAX and SHMALL?

SHMMAX is the maximum size of a single shared memory segment set in “bytes”.

silicon:~ # cat /proc/sys/kernel/shmmax

536870912

SHMALL is the total size of Shared Memory Segments System wide set in “pages”.

silicon:~ # cat /proc/sys/kernel/shmall

1415577

The key thing to note here is the value of SHMMAX is set in "bytes" but the value of SHMMALL is set in "pages".

What’s the optimal value for SHMALL?

As SHMALL is the total size of Shard Memory Segments System wide, it should always be less than the Physical Memory on the System and should be greater than sum of SGA’s of all the oracle databases on the server. Once this value (sum of SGA’s) hit the limit, i.e. the value of shmall, then any attempt to start a new database (or even an existing database with a resized SGA) will result in an “out of memory” error (below). This is because there won’t be any more shared memory segments that Linux can allocate for SGA.

ORA-27102: out of memory

Linux-x86_64 Error: 28: No space left on device.

So above can happen for two reasons. Either the value of shmall is not set to an optimal value or you have reached the threshold on this server.

Setting the value for SHMALL to optimal is straight forward. All you want to know is how much “Physical Memory” (excluding Cache/Swap) you have on the system and how much of it should be set aside for Linux Kernel and to be dedicated to Oracle Databases.

For e.g. Let say the Physical Memory of a system is 6GB, out of which you want to set aside 1GB for Linux Kernel for OS Operations and dedicate the rest of 5GB to Oracle Databases. Then here’s how you will get the value for SHMALL.

Convert this 5GB to bytes and divide by page size. Remember SHMALL should be set in “pages” not “bytes”.

So here goes the calculation.

Determine Page Size first, can be done in two ways. In my case it’s 4096 and that’s the recommended and default in most cases which you can keep the same.

silicon:~ # getconf PAGE_SIZE

4096

silicon:~ # cat /proc/sys/kernel/shmmni

4096

Convert 5GB into bytes and divide by page size, I used the linux calc to do the math.

silicon:~ # echo "( 5 * 1024 * 1024 * 1024 ) / 4096 " | bc -l

1310720.00000000000000000000

Reset shmall and load it dynamically into kernel

silicon:~ # echo "1310720" > /proc/sys/kernel/shmall

silicon:~ # sysctl –p

Verify if the value has been taken into effect.

silicon:~ # sysctl -a | grep shmall

kernel.shmall = 1310720

Another way to look this up is

silicon:~ # ipcs -lm

------ Shared Memory Limits --------

max number of segments = 4096 /* SHMMNI */

max seg size (kbytes) = 524288 /* SHMMAX */

max total shared memory (kbytes) = 5242880 /* SHMALL */

min seg size (bytes) = 1

To keep the value effective after every reboot, add the following line to /etc/sysctl.conf

echo “kernel.shmall = 1310720” >> /etc/sysctl.conf

Also verify if sysctl.conf is enabled or will be read during boot.

silicon:~ # chkconfig boot.sysctl

boot.sysctl on

If returns “off”, means it’s disabled. Turn it on by running

silicon:~ # chkconfig boot.sysctl on

boot.sysctl on

What’s the optimal value for SHMMAX?

Oracle makes use of one of the 3 memory management models to create the SGA during database startup and it does this in following sequence. First Oracle attempts to use the one-segment model and if this fails, it proceeds with the next one which's the contiguous multi-segment model and if that fails too, it goes with the last option which is the non-contiguous multi-segment model.

So during startup it looks for shmmax parameter and compares it with the initialization parameter *.sga_target. If shmmax > *.sga_target, then oracle goes with one-segment model approach where the entire SGA is created within a single shared memory segment.

But the above attempt (one-segment) fails if SGA size otherwise *.sga_target > shmmax, then Oracle proceeds with the 2^nd option – contiguous multi-segment model. Contiguous allocations, as the name indicates are a set of shared memory segments which are contiguous within the memory and if it can find such a set of segments then entire SGA is created to fit in within this set.

But if cannot find a set of contiguous allocations then last of the 3 option’s is chosen – non-contiguous multi-segment allocation and in this Oracle has to grab the free memory segments fragmented between used spaces.

So let’s say if you know the max size of SGA of any database on the server stays below 1GB, you can set shmmax to 1 GB. But say if you have SGA sizes for different databases spread between 512MB to 2GB, then set shmmax to 2Gigs and so on.

Like SHMALL, SHMMAX can be defined by one of these methods..

Dynamically reset and reload it to the kernel..

silicon:~ # echo "536870912" > /proc/sys/kernel/shmmax

silicon:~ # sysctl –p -- Dynamically reload the parameters.

Or use sysctl to reload and reset ..

silicon:~ # sysctl -w kernel.shmmax=536870912

To permanently set so it’s effective in reboots…

silicon:~ # echo "kernel.shmmax=536870912" >> /etc/systctl.conf

Install doc for 11g recommends the value of shmmax to be set to "4GB – 1byte" or half the size of physical memory whichever is lower. I believe “4GB – 1byte” is related to the limitation on the 32 bit (x86) systems where the virtual address space for a user process can only be little less than 4GB. As there’s no such limitation for 64bit (x86_64) bit systems, you can define SGA’s larger than 4 Gig’s. But idea here is to let Oracle use the efficient one-segment model and for this shmmax should stay higher than SGA size of any individual database on the system.

Hope this helps. Regards, Raj

Configuring Teradata STANDBY Database.

2010-11-12T11:29:00.000-08:00

Home | Profile

A STANDBY database is nothing but a clone of Production Database existing on a Test/QA server synced periodically with transaction logs/journals so that if a DR (disaster) were to hit the Production datacenter, with minimal/no data loss, application’s can be re-pointed to this standby database, thus reducing the impact on the business.

With version 13.x, seems Teradata is coming up with “Dual Active” systems similar to what Oracle’s Data Guard or Sybase’s Replication servers or otherwise MS SQL’s cluster services are, but on any version journal restores can be considered as a low cost solution of implementing a standby database for critical applications.

Although it can be setup for OLAP applications as well, it’s more useful for OLTP (Transactional) or OLTA (Transactional/Analytical) apps. The setup requires only few steps and is easy to configure. No additional tools or system changes are required. Just arcmain and couple of shell (Linux)/JCL (Mainframe) scripts are enough. Also we have to use "Permanent Journals" instead of Transient.

Summary of Steps:

Configure the Primary database.
Configure the Standby Database.
Setup client scripts for shipping journals/transaction logs.

Break it down further, steps involved are pretty straight forward - a onetime Archive/Restore and then an ongoing process involving Checkpoint and Journal Archive/Restore, RollForward to standby.

Step 1: Configure the Primary Database

Enable JOURNALING on the Primary Database

Create the database with "AFTER JOURNAL" and provide "JOURNAL TABLE" so that we can capture the transaction logs and journals.

CREATE DATABASE RBDataGuard FROM RAJ

AS PERM=1073741824

FALLBACK

NO BEFORE JOURNAL

AFTER JOURNAL

DEFAULT JOURNAL TABLE=RBDataGuard_JOURNAL;

"FALLBACK" is for added protection and decision to enable it depends on the storage capacity of the system and size of the tables. As you know with fallback data storage is doubled.

If you plan to use an existing database you will need to modify the database to enable "AFTER JOURNAL" and as well the tables need to be altered to enable journaling.

Setup few tables to capture/create transactions

For my testing I’m creating 2 transaction/data tables, although in a real time scenario you don’t need this.

CREATE SET TABLE RBDataTabA ,FALLBACK ,

NO BEFORE JOURNAL,

DUAL AFTER JOURNAL,

WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,

MINIMUM DATABLOCKSIZE , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT

(

ColTXN INTEGER,

LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )

PRIMARY INDEX ( ColTXN );

CREATE SET TABLE RBDataTabB ,FALLBACK ,

NO BEFORE JOURNAL,

DUAL AFTER JOURNAL,

WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,

MINIMUM DATABLOCKSIZE , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT

(

ColTXN INTEGER,

LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )

PRIMARY INDEX ( ColTXN );

CREATE SET TABLE RBDual,FALLBACK ,

NO BEFORE JOURNAL,

DUAL AFTER JOURNAL,

WITH JOURNAL TABLE = RBDataGuard_JOURNAL ,

MINIMUM DATABLOCKSIZE , FREESPACE = 20 PERCENT, CHECKSUM = DEFAULT

(

SCN DECIMAL(38,0),

LastUpdatedDate DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

LastUpdatedTStamp TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

LastUpdatedUser CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL DEFAULT USER )

UNIQUE PRIMARY INDEX ( SCN ,LastUpdatedDate );

Also initialize table RBDual with one row.

INSERT INTO RBDual ( SCN, LastUpdatedUser ) VALUES ( 1, 'Raj' );

Step 2: Configure the Standby Database.

Create the standby database using the same SQL used to setup Primary

CREATE DATABASE RBDataGuard FROM RAJ

AS PERM=1073741824

FALLBACK

NO BEFORE JOURNAL

AFTER JOURNAL

DEFAULT JOURNAL TABLE=RBDataGuard_JOURNAL;

Step 3: Setup client scripts for transaction logs archive/recovery.

Run a full archive/restore (onetime)

Make sure to create the tables on the STANDBY database through Archive/Restore process. Journal restores may not work if you manually create them using SQL Assistant or BTEQ. See the scripts used below, most of it is self explanatory.

bck_db.sql

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

.logon prodsrv/raj,password;

ARCHIVE DATA TABLES

(RBDataGuard)

,RELEASE LOCK

,FILE = DBDump;

LOGOFF;

QUIT;

res_db.sql

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

.logon stdbysrv/raj,passwd;

COPY DATA TABLES ( RBDataGuard)

,RELEASE LOCK

,FILE=DBDump;

LOGOFF;

QUIT;

run_db.sh

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

#!/bin/sh

#Clean up previous logs

rm *.rlg *.log

#Archive/Restore of Prod to Standby

arcmain sessions=12 VB3 perffile=arcDB.log < bck_db.sql

arcmain sessions=12 VB3 perffile=resDB.log < res_db.sql

#End

Once you create above scripts, execute run_db.sh, which will take a full archive of Prod DB and restore it to STANDBY database.

$ sh run_db.sh

11/10/2010 15:47:16

11/10/2010 15:47:16 *** **** ****

11/10/2010 15:47:16 * * * * * PROGRAM: ARCMAIN

11/10/2010 15:47:16 ***** **** * RELEASE: 12.00.00.10

11/10/2010 15:47:16 * * * * * BUILD: 070177eLX (Oct 1 2009)

11/10/2010 15:47:16 * * * * ****

11/10/2010 15:47:16

11/10/2010 15:47:16 --- Opening Restart Log: ARCLOG101110_154716_25008.rlg

11/10/2010 15:47:16 ---

11/10/2010 15:47:16 RESTARTLOG = ARCLOG101110_154716_25008.rlg

11/10/2010 15:47:16

11/10/2010 15:47:16 PARAMETERS IN USE:

11/10/2010 15:47:16

11/10/2010 15:47:16 SESSIONS 12

11/10/2010 15:47:16 VERBOSE LEVEL 3

11/10/2010 15:47:16 PERFFILE = arcDB.log

11/10/2010 15:47:16

11/10/2010 15:47:16 CHARACTER SET IN USE: ASCII

11/10/2010 15:47:16 .LOGON PRODSRV/raj,;

11/10/2010 15:47:16 --- Connecting SQL and CONTROL sessions.

11/10/2010 15:47:17 --- Logon Sequence Number = 4496468 (0x00449c54)

11/10/2010 15:47:17 --- Maximum Parcel Size supported: 64K bytes

11/10/2010 15:47:17 --- Teradata V12 detected

11/10/2010 15:47:17 LOGGED ON 3 SESSIONS

11/10/2010 15:47:17 --- CLIv2 VERSION: 12.00.00.0

11/10/2010 15:47:18

11/10/2010 15:47:18 DBS LANGUAGE SUPPORT MODE Standard

11/10/2010 15:47:18 DBS RELEASE 12.00.03.04

11/10/2010 15:47:18 DBS VERSION 12.00.03.06d

11/10/2010 15:47:18

11/10/2010 15:47:18 STATEMENT COMPLETED

11/10/2010 15:47:18

11/10/2010 15:47:18 ARCHIVE DATA TABLES

11/10/2010 15:47:18 (RBDataGuard)

11/10/2010 15:47:18 ,RELEASE LOCK

11/10/2010 15:47:18 ,FILE = DBDump;

11/10/2010 15:47:18 --- Checking for ACTIVATE_CATALOG macro

11/10/2010 15:47:18 --- ACTIVATE_CATALOG does not exist or select accessright

--- not granted to this user

11/10/2010 15:47:18 --- Checking for ACTIVATE_LOGSKIPPED macro

11/10/2010 15:47:18 --- ACTIVATE_LOGSKIPPED does not exist or select

--- accessright not granted to this user

11/10/2010 15:47:18 --- Checking for ACTIVATE_ENCRYPTION macro

11/10/2010 15:47:18 --- ACTIVATE_ENCRYPTION does not exist or select

--- accessright not granted to this user

11/10/2010 15:47:18 --- Sending Begin Transaction request

11/10/2010 15:47:18 --- Sending Allocate Event Number request.

11/10/2010 15:47:18 UTILITY EVENT NUMBER - 2344402

11/10/2010 15:47:18 --- Sending End Transaction request

11/10/2010 15:47:18 --- Connecting data sessions.

11/10/2010 15:47:58 LOGGED ON 12 SESSIONS

11/10/2010 15:47:58 --- Opening file DBDUMP.

11/10/2010 15:47:58 --- File open done.

11/10/2010 15:47:58 --- Writing archive header record.

11/10/2010 15:47:58

11/10/2010 15:47:58 ARCHIVING DATABASE "RBDATAGUARD"

11/10/2010 15:47:58 --- Begin dictionary phase

11/10/2010 15:47:58 --- Requesting DBS to send dictionary rows.

11/10/2010 15:47:58 --- Sending Begin Transaction request

11/10/2010 15:48:01 --- Retrieving and processing configuration parcels

11/10/2010 15:48:01 DBS Configuration Information:

11/10/2010 15:48:01 Total AMPs: 572

11/10/2010 15:48:01 Down AMPs: 0

11/10/2010 15:48:01 Number of AMPs involved: 572

11/10/2010 15:48:01 System Format: 7

11/10/2010 15:48:01 Hash Function: 773

11/10/2010 15:48:01 --- Writing database header record.

11/10/2010 15:48:01 --- Writing DBS configuration records

11/10/2010 15:48:01 --- Writing table descriptor records.

11/10/2010 15:48:01 --- PARTITIONS WHERE clause not specified

11/10/2010 15:48:01 --- Set ObjHasData Flag based on object type

11/10/2010 15:48:01 --- Start writing dictionary rows

11/10/2010 15:48:01 --- Writing dictionary trailer record.

11/10/2010 15:48:01 --- Sending End Transaction request

11/10/2010 15:48:01 --- End dictionary phase

11/10/2010 15:48:01 --- Begin data phase

11/10/2010 15:48:01 --- Sending Begin Transaction request

11/10/2010 15:48:02 --- Retrieving and processing configuration parcels

11/10/2010 15:48:02 --- Inserting event row in RCEVENT table.

11/10/2010 15:48:02 --- Starting table "RBDataTabA"

11/10/2010 15:48:02 --- Table "RBDataTabA" is not a spanned object

11/10/2010 15:48:02 --- Starting AMP=478

11/10/2010 15:48:02 --- Writing table header record

11/10/2010 15:48:02 --- Writing DBS configuration records

11/10/2010 15:48:02 --- Start receiving and writing data block record(s)

11/10/2010 15:48:03 --- Data subtable: 402 bytes, 0 rows received

11/10/2010 15:48:03 --- Writing table trailer record.

11/10/2010 15:48:03 --- Table "RBDataTabA": Table Id=0x9CB100AF,Fallback=F

11/10/2010 15:48:03 TABLE "RBDataTabA" - 402 BYTES, 0 ROWS ARCHIVED

11/10/2010 15:48:03 --- Starting table "RBDataTabB"

11/10/2010 15:48:03 --- Table "RBDataTabB" is not a spanned object

11/10/2010 15:48:03 --- Starting AMP=138

11/10/2010 15:48:03 --- Writing table header record

11/10/2010 15:48:03 --- Writing DBS configuration records

11/10/2010 15:48:03 --- Start receiving and writing data block record(s)

11/10/2010 15:48:04 --- Data subtable: 402 bytes, 0 rows received

11/10/2010 15:48:04 --- Writing table trailer record.

11/10/2010 15:48:04 --- Table "RBDataTabB": Table Id=0x9CB200AF,Fallback=F

11/10/2010 15:48:04 TABLE "RBDataTabB" - 402 BYTES, 0 ROWS ARCHIVED

11/10/2010 15:48:04 --- Starting table "RBDual"

11/10/2010 15:48:04 --- Table "RBDual" is not a spanned object

11/10/2010 15:48:04 --- Starting AMP=400

11/10/2010 15:48:04 --- Writing table header record

11/10/2010 15:48:04 --- Writing DBS configuration records

11/10/2010 15:48:04 --- Start receiving and writing data block record(s)

11/10/2010 15:48:05 --- Data subtable: 489 bytes, 1 rows received

11/10/2010 15:48:05 --- Writing table trailer record.

11/10/2010 15:48:05 --- Table "RBDual": Table Id=0x9CB000AF,Fallback=F

11/10/2010 15:48:05 TABLE "RBDual" - 489 BYTES, 1 ROWS ARCHIVED

11/10/2010 15:48:05 --- Updating event row.

11/10/2010 15:48:05 --- Sending End Transaction request

11/10/2010 15:48:05 --- End data phase

11/10/2010 15:48:05 --- Writing database trailer record.

11/10/2010 15:48:05 --- Sending release lock request to DBS.

11/10/2010 15:48:05 "RBDATAGUARD" - LOCK RELEASED

11/10/2010 15:48:05 --- Writing archive trailer record.

11/10/2010 15:48:05 --- Closing archive file(s)

11/10/2010 15:48:05 DUMP COMPLETED

11/10/2010 15:48:05 STATEMENT COMPLETED

11/10/2010 15:48:05

11/10/2010 15:48:05 LOGOFF;

11/10/2010 15:48:05 --- Disconnecting all sessions.

11/10/2010 15:48:08 LOGGED OFF 15 SESSIONS

11/10/2010 15:48:08 STATEMENT COMPLETED

11/10/2010 15:48:08

11/10/2010 15:48:08 ARCMAIN TERMINATED WITH SEVERITY 0

11/10/2010 15:48:10

11/10/2010 15:48:10 *** **** ****

11/10/2010 15:48:10 * * * * * PROGRAM: ARCMAIN

11/10/2010 15:48:10 ***** **** * RELEASE: 12.00.00.10

11/10/2010 15:48:10 * * * * * BUILD: 070177eLX (Oct 1 2009)

11/10/2010 15:48:10 * * * * ****

11/10/2010 15:48:10

11/10/2010 15:48:10 --- Opening Restart Log: ARCLOG101110_154810_25358.rlg

11/10/2010 15:48:10 ---

11/10/2010 15:48:10 RESTARTLOG = ARCLOG101110_154810_25358.rlg

11/10/2010 15:48:10

11/10/2010 15:48:10 PARAMETERS IN USE:

11/10/2010 15:48:10

11/10/2010 15:48:10 SESSIONS 12

11/10/2010 15:48:10 VERBOSE LEVEL 3

11/10/2010 15:48:10 PERFFILE = resDB.log

11/10/2010 15:48:10

11/10/2010 15:48:10 CHARACTER SET IN USE: ASCII

11/10/2010 15:48:10 .LOGON STDBYSRV/raj,;

11/10/2010 15:48:10 --- Connecting SQL and CONTROL sessions.

11/10/2010 15:48:11 --- Logon Sequence Number = 519 (0x00000207)

11/10/2010 15:48:11 --- Maximum Parcel Size supported: 64K bytes

11/10/2010 15:48:11 --- Teradata V12 detected

11/10/2010 15:48:11 LOGGED ON 3 SESSIONS

11/10/2010 15:48:11 --- CLIv2 VERSION: 12.00.00.0

11/10/2010 15:48:11

11/10/2010 15:48:11 DBS LANGUAGE SUPPORT MODE Standard

11/10/2010 15:48:11 DBS RELEASE 12.00.03.16

11/10/2010 15:48:11 DBS VERSION 12.00.03.18

11/10/2010 15:48:11

11/10/2010 15:48:11 STATEMENT COMPLETED

11/10/2010 15:48:11

11/10/2010 15:48:11 COPY DATA TABLES ( RBDataGuard)

11/10/2010 15:48:11 ,RELEASE LOCK

11/10/2010 15:48:11 ,FILE=DBDump;

11/10/2010 15:48:11 --- RESTORE FALLBACK enabled due to COPY statement.

11/10/2010 15:48:11 --- Sending Begin Transaction request

11/10/2010 15:48:11 --- Sending Allocate Event Number request.

11/10/2010 15:48:11 UTILITY EVENT NUMBER - 2430

11/10/2010 15:48:11 --- Sending End Transaction request

11/10/2010 15:48:11 --- Connecting data sessions.

11/10/2010 15:48:12 LOGGED ON 12 SESSIONS

11/10/2010 15:48:12 --- Checking for multiple users

11/10/2010 15:48:12 --- Opening ARCHIVE file

11/10/2010 15:48:12 --- Reading Archive Header Record

11/10/2010 15:48:12 --- ARC VERSION 14

11/10/2010 15:48:12 --- ARCHIVED AT 11-10-10 15:47:58

11/10/2010 15:48:12 --- ARCHIVE CHARACTER SET: ASCII

11/10/2010 15:48:12 --- ARCHIVED FROM ALL AMP DOMAINS

11/10/2010 15:48:12 --- SYSTEM INTERNAL FORMAT 7

11/10/2010 15:48:12 --- UTILITY EVENT NUMBER IN ARCHIVE - 2344402

11/10/2010 15:48:12 --- SOURCE RDBMS VERSION: LANGUAGE SUPPORT MODE:Standard;

--- RELEASE:12.00.03.04; VERSION:12.00.03.06d;

11/10/2010 15:48:12 --- SOURCE ROWFORMAT: 1

11/10/2010 15:48:12 --- SOURCE DBSPLATFORM: 17

11/10/2010 15:48:12 --- Archive Event Number = 2344402

11/10/2010 15:48:12 --- Archive System Format = 7

11/10/2010 15:48:12 --- Archive Locale = 0

11/10/2010 15:48:12 --- Archive Version Number = 14

11/10/2010 15:48:12 --- Database header record: "RBDATAGUARD"

11/10/2010 15:48:12 --- Begin dictionary phase

11/10/2010 15:48:12 --- Build Excluded Tables List for ALL PARTITIONS

11/10/2010 15:48:12 --- Check if excluded tables exist

11/10/2010 15:48:12 --- Creating table "RBDataTabA"

11/10/2010 15:48:12 "RBDATAGUARD"."RBDataTabA" CREATED

11/10/2010 15:48:12 --- Creating table "RBDataTabB"

11/10/2010 15:48:12 "RBDATAGUARD"."RBDataTabB" CREATED

11/10/2010 15:48:12 --- Creating table "RBDual"

11/10/2010 15:48:12 "RBDATAGUARD"."RBDual" CREATED

11/10/2010 15:48:12 STARTING TO COPY DATABASE "RBDATAGUARD"

11/10/2010 15:48:12 --- Sending Begin Transaction request

11/10/2010 15:48:13 --- PARTITIONS WHERE clause not specified

11/10/2010 15:48:13 --- Retrieving and processing configuration parcels

11/10/2010 15:48:13 DBS Configuration Information:

11/10/2010 15:48:13 Total AMPs: 16

11/10/2010 15:48:13 Down AMPs: 0

11/10/2010 15:48:13 Number of AMPs involved: 16

11/10/2010 15:48:13 System Format: 7

11/10/2010 15:48:13 Hash Function: 773

11/10/2010 15:48:13 --- Sending request to delete tables

11/10/2010 15:48:13 --- Start sending Insert Dictionary Row requests.

11/10/2010 15:48:13 --- Sending End Transaction request

11/10/2010 15:48:13 "RBDataTabA" - DICTIONARY ROWS COPIED

11/10/2010 15:48:13 "RBDataTabB" - DICTIONARY ROWS COPIED

11/10/2010 15:48:13 "RBDual" - DICTIONARY ROWS COPIED

11/10/2010 15:48:13 DICTIONARY COPY COMPLETED

11/10/2010 15:48:13 --- End dictionary phase

11/10/2010 15:48:13 --- Delete Excluded Tables List for ALL PARTITIONS

11/10/2010 15:48:13 --- Begin data phase

11/10/2010 15:48:13 --- Sending Begin Transaction request

11/10/2010 15:48:13 --- Retrieving and processing configuration parcels

11/10/2010 15:48:13 --- Inserting event row in RCEVENT table.

11/10/2010 15:48:13 --- Searching for Table Header Record on ARCHIVE

11/10/2010 15:48:13 --- Table header record: "RBDataTabA"

11/10/2010 15:48:13 --- Different config/hash size. RESTORE FALLBACK disabled.

11/10/2010 15:48:13 --- Starting table "RBDataTabA"

11/10/2010 15:48:13 --- Table "RBDataTabA" is not a deferred object

11/10/2010 15:48:13 --- Table "RBDataTabA": Table Id= 0x116f0000, Fallback=F

11/10/2010 15:48:13 --- Requesting index descriptor from DBS.

11/10/2010 15:48:13 --- Requesting LOB descriptor from DBS.

11/10/2010 15:48:13 --- Restoring data for Primary Data subtable

11/10/2010 15:48:13 "RBDataTabA" - 402 BYTES, 0 ROWS COPIED

11/10/2010 15:48:13 --- Searching for Table Header Record on ARCHIVE

11/10/2010 15:48:13 --- Table header record: "RBDataTabB"

11/10/2010 15:48:13 --- Starting table "RBDataTabB"

11/10/2010 15:48:13 --- Table "RBDataTabB" is not a deferred object

11/10/2010 15:48:13 --- Table "RBDataTabB": Table Id= 0x11700000, Fallback=F

11/10/2010 15:48:13 --- Requesting index descriptor from DBS.

11/10/2010 15:48:13 --- Requesting LOB descriptor from DBS.

11/10/2010 15:48:13 --- Restoring data for Primary Data subtable

11/10/2010 15:48:13 "RBDataTabB" - 402 BYTES, 0 ROWS COPIED

11/10/2010 15:48:13 --- Searching for Table Header Record on ARCHIVE

11/10/2010 15:48:13 --- Table header record: "RBDual"

11/10/2010 15:48:13 --- Starting table "RBDual"

11/10/2010 15:48:13 --- Table "RBDual" is not a deferred object

11/10/2010 15:48:13 --- Table "RBDual": Table Id= 0x11710000, Fallback=F

11/10/2010 15:48:13 --- Requesting index descriptor from DBS.

11/10/2010 15:48:13 --- Requesting LOB descriptor from DBS.

11/10/2010 15:48:13 --- Restoring data for Primary Data subtable

11/10/2010 15:48:13 "RBDual" - 489 BYTES, 1 ROWS COPIED

11/10/2010 15:48:13 --- Sending buildsort for index 0, Primary Table.

11/10/2010 15:48:13 --- Building fallback subtable for index 0.

11/10/2010 15:48:14 --- Clearing build flag for index 0.

11/10/2010 15:48:14 --- Sending buildsort for index 0, Primary Table.

11/10/2010 15:48:14 --- Building fallback subtable for index 0.

11/10/2010 15:48:15 --- Clearing build flag for index 0.

11/10/2010 15:48:15 --- Sending buildsort for index 0, Primary Table.

11/10/2010 15:48:15 --- Building fallback subtable for index 0.

11/10/2010 15:48:16 --- Clearing build flag for index 0.

11/10/2010 15:48:16 --- Updating event row.

11/10/2010 15:48:16 --- Sending End Transaction request

11/10/2010 15:48:16 --- End data phase

11/10/2010 15:48:16 --- Sending release lock request to DBS.

11/10/2010 15:48:16 "RBDATAGUARD" - LOCK RELEASED

11/10/2010 15:48:16

11/10/2010 15:48:16 STATEMENT COMPLETED

11/10/2010 15:48:16

11/10/2010 15:48:16 LOGOFF;

11/10/2010 15:48:16 --- Disconnecting all sessions.

11/10/2010 15:48:16 LOGGED OFF 15 SESSIONS

11/10/2010 15:48:16 STATEMENT COMPLETED

11/10/2010 15:48:16

11/10/2010 15:48:16 ARCMAIN TERMINATED WITH SEVERITY 0

CHECKPOINT, JOURNAL Archive/Restore and ROLLFORWARD

Checkpoint basically creates a snapshot, so that we can capture the transactions point in time from the permanent journal table. If you cannot afford to loose more than 30mts worth of data from Production, the CHECKPOINT process should be cron'd or scheduled to run every 30mts.

Also applying JOUNRALS to STANDBY depends on the need. You can basically take archives of these journals and keep it in the file system than applying if you are concerned about CPU costs or reduce the processing time overall. This way, you will need to apply the journals as and when needed.

Cumulative vs. incremental decision depends on how size of the journals. If there is too much of DML

activity on too many rows, that increases the size of journals. Disadvantage of the incremental one's is, every snapshot increment should be applied in order and roll forwarded before the subsequent one can be applied. So depending on how often you take snapshots and how big the journals you can decide whether to go for incremental vs. cumulative.

Also why do I need a 3^rd table – RBDual?

In a real time scenario, application database will have many data tables. And the only good way to check if the journal process is working is relying on the logs. But how do you really really make sure of this- by looking at the data. This requires expertise in querying application tables to find out exactly when transactions occurred on Production and whether those got applied to the standby. As long as you know how to query the application tables, you don't need an extra table. But ideally for as an additional check for DBA's supporting the application, creating this additional table will help. Oracle's System Change Number process – SCN generation. SCN in Oracle is far superior, but the concept I used here is generating a one-up number to store it in RBDual. As you proceed further you will understand the need for it.

Below are the scripts used for journal archive/restore.

scn_prod
----------

.logon prodsrv/raj,passwd;
DATABASE RBDataGuard;
INSERT INTO RBDual (SCN)
SELECT CSUM(1,1) + DT.SCN
FROM
(SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);
SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;
LOGOFF;
QUIT;

scn_stdby.sql

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

.logon stdbysrv/raj,passwd;
DATABASE RBDataGuard;
SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;
LOGOFF;
QUIT;

res_jl.sql

----------
.logon stdbysrv/raj,passwd;

COPY JOURNAL TABLE
            (RBDataGuard.RBDataGuard_JOURNAL)
(APPLY TO(
   RBDataGuard.RBDual
,RBDataGuard.RBDataTabA
,RBDataGuard.RBDataTabB
))
,FILE = JLDump
, RELEASE LOCK;

ROLLFORWARD (RBDataGuard) ALL
    ,USE RESTORED JOURNAL
    ,RELEASE LOCK;

LOGOFF;
QUIT;

run_jl.sh

----------

#!/bin/sh

#Clean up previous logs
rm *.rlg *.log

#Generate SCN, Archive Journal and Generate SCN again.
bteq < scn_prod.sql
arcmain sessions=12 VB3 perffile=arcJL.log < bck_jl.sql
bteq < scn_prod.sql

bteq < scn_stdby.sql
arcmain sessions=12 VB3 perffile=resJL.log < res_jl.sql
bteq < scn_stdby.sql

#End

Retrieve SCN from STDBY, Apply Journal, RollForward.
Once you establish above scripts, execute run_jl.sh. To make sure I get some transactions in the data tables, I simultaneously ran a multithreaded Java Program to insert random numbers. I wanted to start the inserts before the checkpoint process and have these stay on until the whole journal process is done. So for this I used 10 threads (users) each with one connection and each of these threads inserting 150+ (approx) SQL's so the inserts stay on for the entire duration of the checkpoint.

If you scroll down to see the marked RED portion of the output from sh run_jl.sh you can see the checkpoint was done at 15:49:05 and the inserts started @15:49:01.390000 and finished @ 15:49:23.490000.

While running the run_jl.sh, I will also be inserting one row to RBDual just before and after the checkpoint by incrementing the value by one. See highlighted portion in green.

Also on the STDBY server, I'm doing a select on this same table prior to the restore of the journal and also after the journal has been restored and roll forwarded. See highlighted portion in blue. Looking at the SCN and Timestamp of the RBDual from the Test Server you can see the 2 rows prior to checkpoint
came over but the 3^rd hasn't, which's what the expected behavior is.

Script consists of actions performed in Prodserver and also on Standby. I have highlighted the line, which shows from where the standby actions are performed. Look for "logon stdbysrv/raj"

$ sh run_jl.sh

BTEQ 12.00.00.09 Wed Nov 10 15:49:02 2010

+---------+---------+---------+---------+---------+---------+---------+----

.logon prodsrv/raj,

*** Logon successfully completed.

*** Teradata Database Release is 12.00.03.04

*** Teradata Database Version is 12.00.03.06d

*** Transaction Semantics are BTET.

*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

*** New default database accepted.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

INSERT INTO RBDual (SCN)

SELECT CSUM(1,1) + DT.SCN

FROM

(SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

*** Query completed. 2 rows found. 2 columns returned.

*** Total elapsed time was 1 second.

SCN LastUpdatedTStamp

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

1. 2010-11-10 15:28:53.000000

2. 2010-11-10 15:49:02.960000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;

*** You are now logged off from the DBC.

*** Warning: You must log on before sending DBC/SQL requests.

+---------+---------+---------+---------+---------+---------+---------+----

QUIT;

*** Exiting BTEQ...

*** RC (return code) = 2

11/10/2010 15:49:03

11/10/2010 15:49:03 *** **** ****

11/10/2010 15:49:03 * * * * * PROGRAM: ARCMAIN

11/10/2010 15:49:03 ***** **** * RELEASE: 12.00.00.10

11/10/2010 15:49:03 * * * * * BUILD: 070177eLX (Oct 1 2009)

11/10/2010 15:49:03 * * * * ****

11/10/2010 15:49:03

11/10/2010 15:49:03 --- Opening Restart Log: ARCLOG101110_154903_25794.rlg

11/10/2010 15:49:03 ---

11/10/2010 15:49:03 RESTARTLOG = ARCLOG101110_154903_25794.rlg

11/10/2010 15:49:03

11/10/2010 15:49:03 PARAMETERS IN USE:

11/10/2010 15:49:03

11/10/2010 15:49:03 SESSIONS 12

11/10/2010 15:49:03 VERBOSE LEVEL 3

11/10/2010 15:49:03 PERFFILE = arcJL.log

11/10/2010 15:49:03

11/10/2010 15:49:03 CHARACTER SET IN USE: ASCII

11/10/2010 15:49:03 .LOGON PRODSRV/raj,;

11/10/2010 15:49:03 --- Connecting SQL and CONTROL sessions.

11/10/2010 15:49:04 --- Logon Sequence Number = 4496473 (0x00449c59)

11/10/2010 15:49:04 --- Maximum Parcel Size supported: 64K bytes

11/10/2010 15:49:04 --- Teradata V12 detected

11/10/2010 15:49:04 LOGGED ON 3 SESSIONS

11/10/2010 15:49:04 --- CLIv2 VERSION: 12.00.00.0

11/10/2010 15:49:04

11/10/2010 15:49:04 DBS LANGUAGE SUPPORT MODE Standard

11/10/2010 15:49:04 DBS RELEASE 12.00.03.04

11/10/2010 15:49:04 DBS VERSION 12.00.03.06d

11/10/2010 15:49:04

11/10/2010 15:49:04 STATEMENT COMPLETED

11/10/2010 15:49:04

11/10/2010 15:49:04 CHECKPOINT (RBDataGuard) ALL ,WITH SAVE;

11/10/2010 15:49:05 --- Sending Begin Transaction request

11/10/2010 15:49:05 --- Sending Allocate Event Number request.

11/10/2010 15:49:05 UTILITY EVENT NUMBER - 2344420

11/10/2010 15:49:05 --- Sending End Transaction request

11/10/2010 15:49:05 CHECKPOINT JOURNAL - "RBDATAGUARD"

11/10/2010 15:49:05 --- Sending Begin Transaction request

11/10/2010 15:49:05 --- Sending End Transaction request

11/10/2010 15:49:05 STATEMENT COMPLETED

11/10/2010 15:49:05

11/10/2010 15:49:05 ARCHIVE JOURNAL TABLES

11/10/2010 15:49:05 (RBDataGuard.RBDataGuard_JOURNAL)

11/10/2010 15:49:05 ,RELEASE LOCK

11/10/2010 15:49:05 ,FILE = JLDump;

11/10/2010 15:49:05 --- Checking for ACTIVATE_CATALOG macro

11/10/2010 15:49:05 --- ACTIVATE_CATALOG does not exist or select accessright

--- not granted to this user

11/10/2010 15:49:05 --- Checking for ACTIVATE_LOGSKIPPED macro

11/10/2010 15:49:05 --- ACTIVATE_LOGSKIPPED does not exist or select

--- accessright not granted to this user

11/10/2010 15:49:05 --- Checking for ACTIVATE_ENCRYPTION macro

11/10/2010 15:49:05 --- ACTIVATE_ENCRYPTION does not exist or select

--- accessright not granted to this user

11/10/2010 15:49:05 --- Sending Begin Transaction request

11/10/2010 15:49:05 --- Sending Allocate Event Number request.

11/10/2010 15:49:05 UTILITY EVENT NUMBER - 2344421

11/10/2010 15:49:05 --- Sending End Transaction request

11/10/2010 15:49:05 --- Connecting data sessions.

11/10/2010 15:49:13 LOGGED ON 12 SESSIONS

11/10/2010 15:49:13 --- Opening file JLDUMP.

11/10/2010 15:49:13 --- File open done.

11/10/2010 15:49:13 --- Writing archive header record.

11/10/2010 15:49:13

11/10/2010 15:49:13 ARCHIVING DATABASE "RBDATAGUARD"

11/10/2010 15:49:13 --- Begin dictionary phase

11/10/2010 15:49:13 --- Requesting DBS to send dictionary rows.

11/10/2010 15:49:13 --- Sending Begin Transaction request

11/10/2010 15:49:13 --- Retrieving and processing configuration parcels

11/10/2010 15:49:13 DBS Configuration Information:

11/10/2010 15:49:13 Total AMPs: 572

11/10/2010 15:49:13 Down AMPs: 0

11/10/2010 15:49:13 Number of AMPs involved: 572

11/10/2010 15:49:13 System Format: 7

11/10/2010 15:49:13 Hash Function: 773

11/10/2010 15:49:13 --- Writing database header record.

11/10/2010 15:49:13 --- Writing DBS configuration records

11/10/2010 15:49:13 --- Writing table descriptor records.

11/10/2010 15:49:13 --- PARTITIONS WHERE clause not specified

11/10/2010 15:49:13 --- Set ObjHasData Flag based on object type

11/10/2010 15:49:13 --- Start writing dictionary rows

11/10/2010 15:49:13 --- Writing dictionary trailer record.

11/10/2010 15:49:13 --- Sending End Transaction request

11/10/2010 15:49:13 --- End dictionary phase

11/10/2010 15:49:13 --- Begin data phase

11/10/2010 15:49:13 --- Sending Begin Transaction request

11/10/2010 15:49:14 --- Retrieving and processing configuration parcels

11/10/2010 15:49:14 --- Inserting event row in RCEVENT table.

11/10/2010 15:49:14 --- Starting table "RBDataGuard_JOURNAL"

11/10/2010 15:49:14 --- Table "RBDataGuard_JOURNAL" is not a spanned object

11/10/2010 15:49:14 --- Starting AMP=286

11/10/2010 15:49:14 --- Writing table header record

11/10/2010 15:49:14 --- Writing DBS configuration records

11/10/2010 15:49:14 --- Start receiving and writing data block record(s)

11/10/2010 15:49:14 --- Data subtable: 235,612 bytes received

11/10/2010 15:49:14 --- Writing table trailer record.

11/10/2010 15:49:14 --- Table "RBDataGuard_JOURNAL": Table

--- Id=0x9A8140AF,Fallback=N

11/10/2010 15:49:14 TABLE "RBDataGuard_JOURNAL" - 235,612 BYTES ARCHIVED

11/10/2010 15:49:14 --- Updating event row.

11/10/2010 15:49:14 --- Sending End Transaction request

11/10/2010 15:49:14 --- End data phase

11/10/2010 15:49:14 --- Writing database trailer record.

11/10/2010 15:49:14 --- Sending release lock request to DBS.

11/10/2010 15:49:14 "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED

11/10/2010 15:49:14 --- Writing archive trailer record.

11/10/2010 15:49:14 --- Closing archive file(s)

11/10/2010 15:49:14 DUMP COMPLETED

11/10/2010 15:49:14 STATEMENT COMPLETED

11/10/2010 15:49:14

11/10/2010 15:49:14 LOGOFF;

11/10/2010 15:49:14 --- Disconnecting all sessions.

11/10/2010 15:49:16 LOGGED OFF 15 SESSIONS

11/10/2010 15:49:16 STATEMENT COMPLETED

11/10/2010 15:49:16

11/10/2010 15:49:16 ARCMAIN TERMINATED WITH SEVERITY 0

BTEQ 12.00.00.09 Wed Nov 10 15:49:16 2010

+---------+---------+---------+---------+---------+---------+---------+----

.logon prodsrv/raj,

*** Logon successfully completed.

*** Teradata Database Release is 12.00.03.04

*** Teradata Database Version is 12.00.03.06d

*** Transaction Semantics are BTET.

*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

*** New default database accepted.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

INSERT INTO RBDual (SCN)

SELECT CSUM(1,1) + DT.SCN

FROM

(SELECT ZEROIFNULL(MAX(SCN)) FROM RBDual ) AS DT(SCN);

*** Insert completed. One row added.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

*** Query completed. 3 rows found. 2 columns returned.

*** Total elapsed time was 1 second.

SCN LastUpdatedTStamp

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

1. 2010-11-10 15:28:53.000000

2. 2010-11-10 15:49:02.960000

3. 2010-11-10 15:49:17.130000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;

*** You are now logged off from the DBC.

*** Warning: You must log on before sending DBC/SQL requests.

+---------+---------+---------+---------+---------+---------+---------+----

QUIT;

*** Exiting BTEQ...

*** RC (return code) = 2

BTEQ 12.00.00.09 Wed Nov 10 15:49:17 2010

+---------+---------+---------+---------+---------+---------+---------+----

.logon stdbysrv/raj,

*** Logon successfully completed.

*** Teradata Database Release is 12.00.03.16

*** Teradata Database Version is 12.00.03.18

*** Transaction Semantics are BTET.

*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

*** New default database accepted.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

*** Query completed. One row found. 2 columns returned.

*** Total elapsed time was 1 second.

SCN LastUpdatedTStamp

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

1. 2010-11-10 15:28:53.000000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;

*** You are now logged off from the DBC.

*** Warning: You must log on before sending DBC/SQL requests.

+---------+---------+---------+---------+---------+---------+---------+----

QUIT;

*** Exiting BTEQ...

*** RC (return code) = 2

11/10/2010 15:49:17

11/10/2010 15:49:17 *** **** ****

11/10/2010 15:49:17 * * * * * PROGRAM: ARCMAIN

11/10/2010 15:49:17 ***** **** * RELEASE: 12.00.00.10

11/10/2010 15:49:17 * * * * * BUILD: 070177eLX (Oct 1 2009)

11/10/2010 15:49:17 * * * * ****

11/10/2010 15:49:17

11/10/2010 15:49:17 --- Opening Restart Log: ARCLOG101110_154917_25903.rlg

11/10/2010 15:49:17 ---

11/10/2010 15:49:17 RESTARTLOG = ARCLOG101110_154917_25903.rlg

11/10/2010 15:49:17

11/10/2010 15:49:17 PARAMETERS IN USE:

11/10/2010 15:49:17

11/10/2010 15:49:17 SESSIONS 12

11/10/2010 15:49:17 VERBOSE LEVEL 3

11/10/2010 15:49:17 PERFFILE = resJL.log

11/10/2010 15:49:17

11/10/2010 15:49:17 CHARACTER SET IN USE: ASCII

11/10/2010 15:49:17 .LOGON STDBYSRV/raj,;

11/10/2010 15:49:17 --- Connecting SQL and CONTROL sessions.

11/10/2010 15:49:18 --- Logon Sequence Number = 521 (0x00000209)

11/10/2010 15:49:18 --- Maximum Parcel Size supported: 64K bytes

11/10/2010 15:49:18 --- Teradata V12 detected

11/10/2010 15:49:18 LOGGED ON 3 SESSIONS

11/10/2010 15:49:18 --- CLIv2 VERSION: 12.00.00.0

11/10/2010 15:49:18

11/10/2010 15:49:18 DBS LANGUAGE SUPPORT MODE Standard

11/10/2010 15:49:18 DBS RELEASE 12.00.03.16

11/10/2010 15:49:18 DBS VERSION 12.00.03.18

11/10/2010 15:49:18

11/10/2010 15:49:18 STATEMENT COMPLETED

11/10/2010 15:49:18

11/10/2010 15:49:18 COPY JOURNAL TABLE

11/10/2010 15:49:18 (RBDataGuard.RBDataGuard_JOURNAL)

11/10/2010 15:49:18 (APPLY TO(

11/10/2010 15:49:18 RBDataGuard.RBDual

11/10/2010 15:49:18 ,RBDataGuard.RBDataTabA

11/10/2010 15:49:18 ,RBDataGuard.RBDataTabB

11/10/2010 15:49:18 ))

11/10/2010 15:49:18 ,FILE = JLDump

11/10/2010 15:49:18 , RELEASE LOCK;

11/10/2010 15:49:18 --- RESTORE FALLBACK enabled due to COPY statement.

11/10/2010 15:49:18 --- Sending Begin Transaction request

11/10/2010 15:49:18 --- Sending Allocate Event Number request.

11/10/2010 15:49:18 UTILITY EVENT NUMBER - 2432

11/10/2010 15:49:18 --- Sending End Transaction request

11/10/2010 15:49:18 --- Connecting data sessions.

11/10/2010 15:49:28 LOGGED ON 12 SESSIONS

11/10/2010 15:49:28 --- Checking for multiple users

11/10/2010 15:49:28 --- Opening ARCHIVE file

11/10/2010 15:49:28 --- Reading Archive Header Record

11/10/2010 15:49:28 --- ARC VERSION 14

11/10/2010 15:49:28 --- ARCHIVED AT 11-10-10 15:49:13

11/10/2010 15:49:28 --- ARCHIVE CHARACTER SET: ASCII

11/10/2010 15:49:28 --- ARCHIVED FROM ALL AMP DOMAINS

11/10/2010 15:49:28 --- SYSTEM INTERNAL FORMAT 7

11/10/2010 15:49:28 --- UTILITY EVENT NUMBER IN ARCHIVE - 2344421

11/10/2010 15:49:28 --- SOURCE RDBMS VERSION: LANGUAGE SUPPORT MODE:Standard;

--- RELEASE:12.00.03.04; VERSION:12.00.03.06d;

11/10/2010 15:49:28 --- SOURCE ROWFORMAT: 1

11/10/2010 15:49:28 --- SOURCE DBSPLATFORM: 17

11/10/2010 15:49:28 --- Archive Event Number = 2344421

11/10/2010 15:49:28 --- Archive System Format = 7

11/10/2010 15:49:28 --- Archive Locale = 0

11/10/2010 15:49:28 --- Archive Version Number = 14

11/10/2010 15:49:28 --- Database header record: "RBDATAGUARD"

11/10/2010 15:49:28 --- Begin dictionary phase

11/10/2010 15:49:28 --- Build Excluded Tables List for ALL PARTITIONS

11/10/2010 15:49:28 --- Check if excluded tables exist

11/10/2010 15:49:28 STARTING TO COPY TABLE "RBDATAGUARD"."RBDataGuard_JOURNAL"

11/10/2010 15:49:28 --- Sending Begin Transaction request

11/10/2010 15:49:28 --- PARTITIONS WHERE clause not specified

11/10/2010 15:49:28 --- Retrieving and processing configuration parcels

11/10/2010 15:49:28 DBS Configuration Information:

11/10/2010 15:49:28 Total AMPs: 16

11/10/2010 15:49:28 Down AMPs: 0

11/10/2010 15:49:28 Number of AMPs involved: 16

11/10/2010 15:49:28 System Format: 7

11/10/2010 15:49:28 Hash Function: 773

11/10/2010 15:49:28 --- Start sending Insert Dictionary Row requests.

11/10/2010 15:49:28 --- Sending End Transaction request

11/10/2010 15:49:28 DICTIONARY COPY COMPLETED

11/10/2010 15:49:28 --- End dictionary phase

11/10/2010 15:49:28 --- Delete Excluded Tables List for ALL PARTITIONS

11/10/2010 15:49:28 --- Begin data phase

11/10/2010 15:49:28 --- Sending Begin Transaction request

11/10/2010 15:49:28 --- Retrieving and processing configuration parcels

11/10/2010 15:49:28 --- Inserting event row in RCEVENT table.

11/10/2010 15:49:28 --- Searching for Table Header Record on ARCHIVE

11/10/2010 15:49:28 --- Table header record: "RBDataGuard_JOURNAL"

11/10/2010 15:49:28 --- Different config/hash size. RESTORE FALLBACK disabled.

11/10/2010 15:49:28 --- Starting table "RBDATAGUARD_JOURNAL"

11/10/2010 15:49:28 --- Table "RBDATAGUARD_JOURNAL" is not a deferred object

11/10/2010 15:49:28 --- Table "RBDATAGUARD_JOURNAL": Table Id= 0x11614000,

--- Fallback=N

11/10/2010 15:49:28 --- Restoring data for Primary Data subtable

11/10/2010 15:49:29 "RBDATAGUARD_JOURNAL" - 235,612 BYTES PROCESSED, 235,612

BYTES COPIED

11/10/2010 15:49:29 --- Sending buildsort for index 0, Primary Table.

11/10/2010 15:49:29 --- Building fallback subtable for index 0.

11/10/2010 15:49:29 --- Clearing build flag for index 0.

11/10/2010 15:49:29 --- Updating event row.

11/10/2010 15:49:29 --- Sending End Transaction request

11/10/2010 15:49:29 --- End data phase

11/10/2010 15:49:29 --- Sending release lock request to DBS.

11/10/2010 15:49:29 "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED

11/10/2010 15:49:29

11/10/2010 15:49:29 STATEMENT COMPLETED

11/10/2010 15:49:29

11/10/2010 15:49:29 ROLLFORWARD (RBDataGuard) ALL

11/10/2010 15:49:29 ,USE RESTORED JOURNAL

11/10/2010 15:49:29 ,RELEASE LOCK;

11/10/2010 15:49:29 --- Sending Begin Transaction request

11/10/2010 15:49:29 --- Sending Allocate Event Number request.

11/10/2010 15:49:29 UTILITY EVENT NUMBER - 2434

11/10/2010 15:49:29 --- Sending End Transaction request

11/10/2010 15:49:29 --- Sending Begin Transaction request

11/10/2010 15:49:29 --- Retrieving and processing configuration parcels

11/10/2010 15:49:29 --- Sending End Transaction request

11/10/2010 15:49:29 --- Sending Begin Transaction request

11/10/2010 15:49:29 --- Retrieving and processing configuration parcels

11/10/2010 15:49:29 --- Inserting event row in RCEVENT table.

11/10/2010 15:49:29 --- Updating event row.

11/10/2010 15:49:30 --- Sending request to delete tables

11/10/2010 15:49:30 --- Sending End Transaction request

11/10/2010 15:49:30 --- Sending release lock request to DBS.

11/10/2010 15:49:30 "RBDATAGUARD"."RBDATATABA" - LOCK RELEASED

11/10/2010 15:49:30 --- Sending release lock request to DBS.

11/10/2010 15:49:30 "RBDATAGUARD"."RBDATATABB" - LOCK RELEASED

11/10/2010 15:49:30 --- Sending release lock request to DBS.

11/10/2010 15:49:30 "RBDATAGUARD"."RBDUAL" - LOCK RELEASED

11/10/2010 15:49:30 --- Sending release lock request to DBS.

11/10/2010 15:49:30 "RBDATAGUARD"."RBDATAGUARD_JOURNAL" - LOCK RELEASED

11/10/2010 15:49:30 'ROLLFORWARD' COMPLETED.

11/10/2010 15:49:30 STATEMENT COMPLETED

11/10/2010 15:49:30

11/10/2010 15:49:30 LOGOFF;

11/10/2010 15:49:30 --- Disconnecting all sessions.

11/10/2010 15:49:30 LOGGED OFF 15 SESSIONS

11/10/2010 15:49:30 STATEMENT COMPLETED

11/10/2010 15:49:30

11/10/2010 15:49:30 ARCMAIN TERMINATED WITH SEVERITY 0

BTEQ 12.00.00.09 Wed Nov 10 15:49:30 2010

+---------+---------+---------+---------+---------+---------+---------+----

.logon stdbysrv/raj,

*** Logon successfully completed.

*** Teradata Database Release is 12.00.03.16

*** Teradata Database Version is 12.00.03.18

*** Transaction Semantics are BTET.

*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE RBDataGuard;

*** New default database accepted.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SELECT SCN , LastUpdatedTStamp FROM RBDual ORDER BY SCN ;

*** Query completed. 2 rows found. 2 columns returned.

*** Total elapsed time was 1 second.

SCN LastUpdatedTStamp

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

1. 2010-11-10 15:28:53.000000

2. 2010-11-10 15:49:02.960000

+---------+---------+---------+---------+---------+---------+---------+----

LOGOFF;

*** You are now logged off from the DBC.

*** Warning: You must log on before sending DBC/SQL requests.

+---------+---------+---------+---------+---------+---------+---------+----

QUIT;

*** Exiting BTEQ...

*** RC (return code) = 2

Teradata's locking mechanism is not that great as Oracle. Of few test runs I did, checkpoint statement really blocked the transactions at times for more than 3-3mts, which isn't good. And if you look at standby database to see what transactions came after the journal restore, anything that started from 15:49:01.390000 until 2010-11-10 15:49:05.290000, later is the Checkpoint completion time.

On Standby Server

SELECT MIN( LastUpdatedTStamp) , MAX (LastUpdatedTStamp) FROM RBDataTabA ;

SELECT MIN( LastUpdatedTStamp) , MAX (LastUpdatedTStamp) FROM RBDataTabA

;

*** Query completed. One row found. 2 columns returned.

*** Total elapsed time was 1 second.

Minimum(LastUpdatedTStamp) Maximum(LastUpdatedTStamp)

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

2010-11-10 15:49:01.390000 2010-11-10 15:49:05.290000

BTEQ -- Enter your DBC/SQL request or BTEQ command:

SELECT MIN( LastUpdatedTStamp) , MAX (LastUpdatedTStamp) FROM RBDataTabB ;

SELECT MIN( LastUpdatedTStamp) , MAX (LastUpdatedTStamp) FROM RBDataTabB

;

*** Query completed. One row found. 2 columns returned.

*** Total elapsed time was 1 second.

Minimum(LastUpdatedTStamp) Maximum(LastUpdatedTStamp)

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

2010-11-10 15:49:01.670000 2010-11-10 15:49:05.310000

BTEQ -- Enter your DBC/SQL request or BTEQ command:

This is no way close to Oracle's Data Guard or Fast Start failover, which is far superior in terms of role transitions and failover/switchover capabilities but can get the job done depending on how critical and active your system is.

Configuring Oracle Data Guard – Physical Standby with Fast-Start Fail Over using SuSE 11 and Oracle 11g.

2010-11-04T19:44:00.000-07:00

Home | Profile

For this setup I used two systems with SuSE 11.2 and Oracle 11g installed on each. Installation binaries/versions and directories are identical on both servers.

Summary of Steps:

Configure the Primary Database.
Configure Listener/TNS on both Primary and Secondary.
Configure the Standby Database.
Configure Oracle Data Guard and Data Guard broker (DGMGRL)
Perform Failover/Switch Over tests.

I'm running the primary database on host "silicon" and standby on host "graphics". I also took out the prompt "SQL>" preceding the SQL statements, so it's easier for you to cut/paste. The database SID is RBPDB01.

1. Configure the Primary Database.

1.1 Enable FLASHBACK, ARCHIVELOG and FORCE_LOGGING for the Database:

oracle@silicon:~> sqlplus / as sysdba;

startup mount;

alter database archivelog ;

alter database flashback on;

alter database open;

shutdown immediate;

startup;

alter database force logging;

Check the status of each by running the below.

SQL> select log_mode, flashback_on, force_logging from v$database;

LOG_MODE FLASHBACK_ON FOR

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

ARCHIVELOG YES YES

1.2 Configure Standby Redo Logs (SRL’s) on Primary Database:

As LGWR reads log buffer and writes to redo logs, a new process called LNS (Log Network Server) ships these change vectors to the standby database through Oracle Net Services which is picked up by RFS (Remote File Server) writing this to Standby Redo Logs.

If you wonder why we need this on Primary DB, the reason is when primary changes its role to standby during a failover/switchover, these are then used by RFS.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/oracle/oradata/RBPDB01/onlinelog/RBPDB01_redo04.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/oracle/oradata/RBPDB01/onlinelog/RBPDB01_redo05.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/oracle/oradata/RBPDB01/onlinelog/RBPDB01_redo06.log') SIZE 50M;

1.3 Create Password File:

oracle@silicon:~> cd /u01/oracle/product/11.1.0/db_1/dbs/

oracle@silicon:~> orapwd file=orapwRBPDB01 password=oracle entries=5

1.4 Create pfile if only spfile exists.

create pfile from spfile;

If you get an ORA-01565 or ORA-27037, this indicates that you have already started the database with a pfile, ignore the error for now and proceed.

1.5 Edit the pfile ( /u01/oracle/product/11.1.0/db_1/dbs/initRBPDB01.ora ) to include Data Guard Specific Parameters.

*.archive_lag_target = 1800

*.db_unique_name='silicon_RBPDB01'

*.log_archive_config='DG_CONFIG=(silicon_RBPDB01,graphics_RBPDB01)'

*.log_archive_dest_1='LOCATION=/u01/oracle/admin/RBPDB01/arch/ mandatory VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=silicon_RBPDB01'

*.log_archive_dest_2='service="graphcis.RBPDB01"',' LGWR SYNC AFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=60 db_unique_name="graphics_RBPDB01" net_timeout=10 valid_for=(online_logfile,primary_role)'

*.fal_client='silicon.RBPDB01'

*.fal_server='graphics.RBPDB01'

*.service_names='RBPDB01','silicon.RBPDB01'

*.dg_broker_config_file1='/u01/oracle/product/11.1.0/db_1/dbs/broker_silicon_RBPDB01_1.dat'

*.dg_broker_config_file2='/u01/oracle/product/11.1.0/db_1/dbs/broker_silicon_RBPDB01_2.dat'

*.dg_broker_start=true

**NB : Once you update init.ora with above parms, make sure there are no duplicate parameters. For e.g. If you had used DBCA to create the database and copied above to edit init.ora db_unique_name will now be a duplicate parameter!

You can lookup the definition of each parameters in here.

http://www.oracle.com/pls/db111/homepage

1.6 Bounce the database with the new pfile and update the spfile.

oracle@silicon:~> sqlplus / as sysdba;

startup force nomount pfile=$ORACLE_HOME/dbs/initRBPDB01.ora;

create spfile from pfile;

shutdown immediate;

startup;

1.7 Create a STANDBY control file, update spfile again.

shutdown immediate;

startup mount;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/oracle/oradata/RBPDB01/controlfile/RBPDB01_control01_stdby.ctl';

alter database open;

create pfile from spfile;

shutdown immediate;

1.8 Copy files from Primary to Standby: You can create the standby database either using RMAN DUPLICATE (new feature) or by doing standard ftp or scp.

cd /u01/oracle/oradata

scp -P 9999 -r RBPDB01/ oracle@192.168.5.101:/u01/oracle/oradata/

cd /u01/oracle/admin

scp -P 9999 -r RBPDB01/ oracle@192.168.5.101:/u01/oracle/admin/

cd /u01/oracle/product/11.1.0/db_1/dbs

scp -P 9999 orapwRBPDB01 oracle@192.168.5.101:/u01/oracle/product/11.1.0/db_1/dbs/

** I’m running ssh on a different port which’s why you see scp –P 9999 and also used my secondary set of NIC’s configured with static IP’s bypassing router for faster transfer. So essentially (192.168.5.101 is “graphics”). You can use ftp’s, obviously faster than scp’s instead. Anyways please pay attention to the directories and files you need to copy to standby.

2. Configure TNSNAMES, SQLNET on both primary and secondary servers.

2.1 For the observer and also the dgmgrl to restart and communicate with instances after the fast-start failover and restarts of instances during broker operations, a static registration with the local listener is required for each instance’s. It is for the same we have to add the below to the listener file on each servers.

**Define GLOBAL_DBNAME as db_unique_name_DGMGRL.domain_name.

*************PRIMARY (silicon ) ********

--listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = silicon_RBPDB01_DGMGRL.chakravyoha.com)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = RBPDB01)

)

--tnsnames.ora

graphics.RBPDB01 =( DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=graphics)(PORT=1521))(CONNECT_DATA=(SID=RBPDB01)))

--sqlnet.ora

sqlnet.expire_time=15

*************STANDBY (graphics) ********

--listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = graphics_RBPDB01_DGMGRL.chakravyoha.com)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = RBPDB01)

)

--tnsnames.ora

silicon.RBPDB01 =( DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=silicon)(PORT=1521))(CONNECT_DATA=(SID=RBPDB01)))

--sqlnet.ora

sqlnet.expire_time=15

2.2 Start listeners on both primary and standby servers.

oracle@silicon:~> lsnrctl start

Service "silicon_RBPDB01_DGMGRL.chakravyoha.com" has 1 instance(s). Instance "RBPDB01", status UNKNOWN, has 1 handler(s) for this service...

oracle@graphics > lsnrctl start

Service "graphics_RBPDB01_DGMGRL.chakravyoha.com" has 1 instance(s). Instance

"RBPDB01", status UNKNOWN, has 1 handler(s) for this service...

3 Setup Standby Database on host “graphics”

3.1 Overlay the control file with standby control file. Make sure to copy this over to the flash recovery area as well.

cd /u01/oracle/oradata/RBPDB01/controlfile

cp RBPDB01_control01_stdby.ctl RBPDB01_control01.ctl

cd /u01/oracle/flash_recovery_area/RBPDB01/controlfile

cp /u01/oracle/oradata/RBPDB01/controlfile/RBPDB01_control01_stdby.ctl RBPDB01_control02.ctl

3.2 Prepare the init.ora file for standby database.

*.archive_lag_target = 1800

*.db_unique_name='graphics_RBPDB01'

*.log_archive_config='DG_CONFIG=(silicon_RBPDB01,graphics_RBPDB01)'

*.log_archive_dest_1='LOCATION=/u01/oracle/admin/RBPDB01/arch/ mandatory VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=graphics_RBPDB01'

*.log_archive_dest_2='service="silicon.RBPDB01"',' LGWR SYNC AFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=60 db_unique_name="silion_RBPDB01" net_timeout=10 valid_for=(online_logfile,primary_role)'

*.fal_client='graphics.RBPDB01'

*.fal_server='silicon.RBPDB01'

*.service_names='RBPDB01','graphics.RBPDB01'

*.dg_broker_config_file1='/u01/oracle/product/11.1.0/db_1/dbs/broker_graphics_RBPDB01_1.dat'

*.dg_broker_config_file2='/u01/oracle/product/11.1.0/db_1/dbs/broker_graphics_RBPDB01_2.dat'

*.dg_broker_start=true

** Notice the difference in service name's between log_archive_dest_1 and log_archive_dest_2 for the primary and standby init.ora files. Once again, make sure the init.ora contains no duplicate entries with the addition of the above. For e.g db_unique_name etc.

3.3 Startup primary (@silicon) Database once the files are copied over.

oracle@silicon:~> sqlplus / as sysdba;

startup;

3.4 Startup Standby Database with the edited init.ora file , refresh spfile with the

updated DG parameters.

oracle@graphics > sqlplus / as sysdba;

startup mount pfile=$ORACLE_HOME/dbs/initRBPDB01.ora;

alter database flashback on;

create spfile from pfile;

shutdown immediate;

3.5 Startup Standby with the newly created spfile and put it in recovery mode.

oracle@graphics > sqlplus / as sysdba;

startup mount;

SQL> select log_mode, flashback_on, force_logging from v$database;

LOG_MODE FLASHBACK_ON FOR

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

ARCHIVELOG YES YES

alter database recover managed standby database disconnect from session;

4 Configure DGMGRL for broker communication.

4.1 On the primary server from unix/linux command prompt run dgmgrl.

oracle@silicon:~> dgmgrl

In the DGMGRLprompt run the below commands.

connect sys/oracle

create configuration RBPDB01_DG as primary database is silicon_RBPDB01 connect identifier is silicon.RBPDB01;

add database graphics_RBPDB01 as connect identifier is graphics.RBPDB01 maintained as physical ;

See the output of show configuration

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxPerformance

Databases:

silicon_rbpdb01 - Primary database

graphics_rbpdb01 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

enable configuration;

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxPerformance

Databases:

silicon_rbpdb01 - Primary database

graphics_rbpdb01 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

4.2 Enable Fast-Start failover and configure observer.

              oracle@silicon:~>     dgmgrl

                        In the DGMGRL prompt run the below commands.

connect sys/oracle

edit configuration set protection mode as maxavailability;

edit database silicon_RBPDB01 set property LogXptMode='SYNC' ;

edit database graphics_RBPDB01 set property LogXptMode='SYNC' ;

edit database silicon_RBPDB01 set property NetTimeout=30 ;

edit database graphics_RBPDB01 set property NetTimeout=30 ;

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxAvailability

Databases:

silicon_rbpdb01 - Primary database

graphics_rbpdb01 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

4.3 Start the Observer from a different dgmgrl session.

oracle@silicon:~> dgmgrl

In the DGMGRL prompt run the below commands.

connect sys/oracle

start observer

You will not be able to get to the command prompt again from this session and this is
normal. From another linux session start dgmgrl prompt for the rest of operations.

4.4 Enable FAST_START FAILOVER.

oracle@silicon:~> dgmgrl

enable fast_start failover;

DGMGRL> enable fast_start failover;

Enabled.

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxAvailability

Databases:

silicon_rbpdb01 - Primary database

graphics_rbpdb01 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

** FYI - If you were to do a tail –f on the alert.log you can see quite a few interesting messages/communications passed between the primary and standby databases.

5 Perform SwitchOver and FailOver.

5.1 Switchover is usually a planned outage so there's no data loss.

oracle@silicon:~> dgmgrl

connect sys/oracle

show configuration;

switchover to graphics_RBPDB01;

** I had to force-kill (kill -9) a sqlplus session once to get the switchover to continue, as it was hung on “CLOSING sessions” or “Killing Sessions”. You can do a tail –f on alert log on both instances to monitor the progress of swithover.

Once done, connect to dgmgrl again and do a show configuration. As you can see silicon_rbpdb01 has now become the standby database and graphics_rbpdb01 is now the primary database.

oracle@silicon:~> dgmgrl

show configuration;

DGMGRL> connect sys/oracle

Connected.

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxAvailability

Databases:

graphics_rbpdb01 - Primary database

silicon_rbpdb01 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

5.2 Failover the primary database.

After the switch over, the primary database is now running in “graphics”.

Now kill the pmon process of the primary database running in graphics, this is to simulate a database crash. Observer will initiate a failover of the primary database to secondary automatically.

Database on graphics is down and the one on silicon is taken over as

primary. Remember the prompt where you left the observer running, if you go back to that session you will be able to see the actions performed. As you can see silicon_rpbdb01 is now the primary database.

Also go to the DGMGRL command prompt and do a show configuration.

DGMGRL> start observer

Observer started

21:16:40.86 Thursday, November 04, 2010

Initiating Fast-Start Failover to database "silicon_rbpdb01"...

Performing failover NOW, please wait...

Failover succeeded, new primary is "silicon_rbpdb01"

21:16:42.43 Thursday, November 04, 2010

oracle@silicon:~> dgmgrl

show configuration;

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxAvailability

Databases:

silicon_rbpdb01 - Primary database

Warning: ORA-16817: unsynchronized fast-start failover configuration

graphics_rbpdb01 - (*) Physical standby database (disabled)

ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: ENABLED

Configuration Status:

WARNING

WARNING is because there’s no secondary database to synchronize the now primary silicon_rbpdb01.

5.3 Go to silicon (Primary) host and mount the database now.

Assuming your database crashed in the middle of night, you can see the FAST-START fail over configuration has put you back in business. Everyone is happy and now it’s time to fix the error and bring back the standby database.

oracle@graphics:~> sqlplus / as sysdba;

sqlplus / as sysdba;

startup mount;

oracle@silicon:~> dgmgrl

connect sys/oracle

reinstate database 'graphics_rbpdb01'

If you were to go back to the observer session, you can see the actions performed by database while re-instating the standby on graphics. Based on the messages/actions I believe there’s no need to mount the database (just I performed) above as the dataguard performed a shutdown.

21:27:35.99 Thursday, November 04, 2010

Initiating reinstatement for database "graphics_rbpdb01"...

Reinstating database "graphics_rbpdb01", please wait...

Operation requires shutdown of instance "RBPDB01" on database "graphics_rbpdb01"

Shutting down instance "RBPDB01"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "RBPDB01" on database "graphics_rbpdb01"

Starting instance "RBPDB01"...

ORACLE instance started.

Database mounted.

Go to the dgmgrl prompt and run the show configuration.

show configuration;

DGMGRL> show configuration;

Configuration - rbpdb01_dg

Protection Mode: MaxAvailability

Databases:

silicon_rbpdb01 - Primary database

graphics_rbpdb01 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS

Hope this helps. Regards, Raj.

Oracle RMAN Backup Solution (Open Source)

2010-11-01T19:13:00.000-07:00

Home | Profile

I have been searching for backup solution/tool for backup/recovery of Oracle Databases which is scalable enough that you can easily install and customize for your site. After spending some time on google/bing I felt it might be a good idea to work on one, which will give me an opportunity to refresh my skills in shell/perl scripting too. Finally after a good couple of months I was able to come up with a scalable product, which I have now uploaded to sourceforge.net as an open source Product. You can download the entire scripts from here https://sourceforge.net/projects/oraclerman/

Zip file consists of 3 main scripts.

a ) scRBackup.sh : Single DB RMAN Backup Script(bash)

b ) scRRecover.sh: Single DB RMAN Recover Script(bash)
c ) scMBackup.pl : Multi DB Backup Wrapper Script(perl)

Script Usage Syntax

Before you read further, here's the backup script in action, small demo of how it works - desktop recording showing backup and restore of the databases.

** If the video is not clear, a high resolution .avi file is included along with the scripts at https://sourceforge.net/projects/oraclerman/

Once you unzip the scripts are placed in folder called rbSCRIPTS and there are 3 main sub directories under this - rbLOGS, rbREPORTS, rbCONFIG, rbSQL. rbCONFIG/rbckup.ini: rbCONFIG consists of a .ini file called rbckup.ini, which contains all the initialization parameters required for the script to run. This eliminates the need to do edits or otherwise hard coding changes when you install this at your site. Instead you can do simple edits to the rbckup.ini file and customize it to work for your environment. Also makes it easy when you port the scripts from one host to another. The rbckup.ini, has been compartmentalized with comments like "Declared(DYN) meaning all Dynamic Variables", Declared (HC) meaning all Hard Coded Variables etc for readability.

First set of arguments within rbckup.ini file is for command line arguments. For e.g. To backup a database RBPDB00, below is the command that will be used. As you can see all the RMAN parameters are being supplied through command line following the script name. This includes DISK vs. TAPE.

sh scRBackup.sh disk 0c RBPDB00 4 3 30 100G 4 1 2

If you notice within the rbckup.ini file, there's a line for "SBT_TAPE". This makes it easier to switch between a DISK backup a TAPE backup. Once you define the right nodes for TSM Backup (Assuming it's a TIVOLI TSM Tape Library and a tdpo.opt defined) all that's required to switch between disk and tape is that you need to specify that in the command line. Please note the quotes around (single and double), has to be precise, else the script will error out.

export SBT_TAPE="'sbt_tape' parms

'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)'"

So if the above backup needs to be taken to the tape library, all you need to do is to change it to say "tape" instead of "disk"

sh scRBackup.sh tape 0c RBPDB00 4 3 30 100G 4 1 2

Above shows how to backup a single database "RBPDB00". So if you need to backup more than 1 database here's how it has to be done.

sh scRBackup.sh tape 0c RBPDB00 4 3 30 100G 4 1 2

sh scRBackup.sh tape 0c RBPDB01 2 5 60 200G 9 2 5

sh scRBackup.sh tape 0c RBPDB02 2 5 70 300G 8 4 3

As you can see above, the parameters (RMAN) followed by the databases can be different for different databases as you see fit. Details about these parameters has been described in the presentation (.ppt) included along with the code. For e.g, "0C" is for Incremental-Level-0-ColdBackup", "0 is for Incremental-Level-0-Open-Inconsistent Backup". Following the SID names are parameters like Channels, Recovery Window, Control File Record Keep Time, Max Set Size, Parallelism etc. I have included a Power Point Presentation (.ppt) which details each and every parameters along with the source code. You can download all these along with a demo from below link.

https://sourceforge.net/projects/oraclerman/

Since scRBackup.sh was being built to do individual Database Backups, I created a small file putting all the commands separated by ":" A perl wrapper called scMBackup.pl (Master Backup) is then used to read this file, which will just loop through and build the individual "scRBackup.sh x x x.." commands ript one by one looping through the supplied file.
rbCONFIG/db_dbf.lst
0:Y:scRBackup.sh:disk:0:RBPDB00:4:7:10:20G:2:1:1:EOL
1:Y:scRBackup.sh:disk:0:RBPDB01:6:9:15:50G:3:2:2:EOL
2:Y:scRBackup.sh:disk:0:RBPDB02:3:4:20:40G:4:1:2:EOL
3:Y:scRBackup.sh:disk:0:RBPDB03:4:2:50:90G:5:2:1:EOL
The syntax to use the scMBackup.pl is as below.

perl scMBackup.pl ./rbCONFIG/db_dbf.lst 0 3 R

The above command will scan the db_dbf.lst file and will build and run the individual scRBackup commands thus backing up each databases. The last letter "R", if substituted with "P" will give you a preview mode, instead really running the databases.

First character in each row is numbers in sequence, this is done so that instead editing the .ini file you can specify the start and end positions through the command line. For e.g, if you want to backup databases from 1 to 3, you will need to specify like below. This way the first one "0", will be skipped.

perl scMBackup.pl ./rbCONFIG/db_dbf.lst 1 3 R

Second character is provided for excluding any backups from the list. For e.g if there's list of 50 databases and you want to start from 0 to 50 but like to skip/disable the 30th one. Your .ini file would like below, substitute "Y", with "N". scMBackup.pl will thus skip this database.

rbCONFIG/db_dbf.lst
0:Y:scRBackup.sh:disk:0:RBPDB00:4:7:10:20G:2:1:1:EOL
1:Y:scRBackup.sh:disk:0:RBPDB01:6:9:15:50G:3:2:2:EOL
..................
30:N:scRBackup.sh:disk:0:RBPDBxx:6:9:15:50G:3:2:2:EOL
...................
50:Y:scRBackup.sh:disk:0:RBPDB02:3:4:20:40G:4:1:2:EOL

All these can be tested by running it with preview mode - "P" , which will show what will run.

perl scMBackup.pl ./rbCONFIG/db_dbf.lst 0 50 P

For more details you may now go through the .ppt files included along with the demo (.avi) and scripts. For convenience and benefit I'm including same slides below.

Hope this helps.

Regards,
Raj

*********************************************************************

The file is part of Open Source Oracle Backup Solution which uses RMAN+Perl+Bash script to backup Oracle Databases (11g & 10g) ready to run on any Linux Platforms by doing simple edits to an .ini file which may be required to be customized for your site.

Open Source Oracle Backup Solution is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

Open Source Oracle Backup Solution is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with Open Source Oracle Backup Solution.

If not, see http://www.gnu.org/licenses.

**********************************************************************