How to restore db2 database to specified date and restore the deleted records from certain table

Jephe Wu - http://linuxtechres.blogspot.com

Scenario: single DB2 instance and database db1, database transaction log is enabled, partial data of some tables in schema 'jephe' were deleted accidently. As this is a production database used by many different clients/schemas, we cannot restore back as the client only realized this accident one week later after deletion.

Solution: restore monthly database full backup plus the transaction logs just before the time which deletion happened on another server(DR?). Then use db2 export and db2 import to import back those deleted data.

Environment: RHEL5, IBM DB2 UDB 9.1 fixpack 3.


Steps:

1. restore the monthly full online database backup to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"

note: /data is the directory where the database full backup image exists.

2. get all the transaction log files after that full backup and before the deletion time

3. copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2011-04-19-11.30.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"

PLease refer to my last time blog at http://linuxtechres.blogspot.com/2010/07/how-to-onlineoffline-backup-and-restore.html

4. backup those tables on production database first in case they are destroyed during import process


# more backup.sh
cd /db2/db2inst1/scripts/20110420_restore/backup
db2 connect to db1
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * from table1"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * from table2"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * from table3"
db2 terminate

5. extract those deleted data first from restored DR database server


# more extract.sh
cd /home/db2inst1
db2 connect to db1dr
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * FROM table1 WHERE balabala-same statement used during deletion"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * FROM table2 WHERE balabala-same statement used during deletion"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * FROM table3 where balabala-same statement used during deletion"   

6. import back to the production database (the sequence for importing might be different from the original deletion sequence as it might depends on foreign key or something)

# more import.sh
cd /db2/db2inst1/scripts/20110420_restore/
db2 connect to db1
db2 set schema = jephe
db2 "import from \"./table3\" of del messages \"./table1.imp\" insert into table1"
db2 "import from \"./table2\" of del messages \"./table2.imp\" insert into table2"
db2 "import from \"./table1\" of del messages \"./table3.imp\" insert into table3"
db2 terminate

How to make sqlplus more friendly

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to solve some issues with sqlplus such as backspace and command history
Environment: CentOS 5.5 64bit, Oracle 11g 64bit, putty or Xshell ssh client


Steps:
1. History command
Original sqlpuls doesn't support history commands, you can use rlwrap.

a. download rlwrap from http://utopia.knoware.nl/~hlub/rlwrap/
b. tar xvpfz rlwrap-0.37.tar.gz
c. ./configure; make; make install

2. put the following into ~oracle/.bash_profile
stty erase ^H  (ctrl + V, then H)
# for some ssh client, you might need to use stty erase '^?' (that's shift+6+?)
alias sqlplus='rlwrap sqlplus'

3. run sqlplus normally like sqlpluls / as sysdba, now you can easily use history commands and move cursors

How to use bash script to send out Oracle AWR report automatically

Jephe Wu - http://linuxtechres.blogspot.com
Objective: write bash shell script as cronjob to send out Oracle AWR report every 2 hours automatically
Environment: Oracle 11g 11.1.0.6 64bit default installation, CentOS 5.5 64bit
Concept: use base number and current unix seconds to get the latest snap id, use the latest snap id to get the begin snap id(current snap id -2). Use template awrrpt.sql file to generate the predefined filename, num_days, begin snap id and end snap id.

Steps:
1. preparing the template files
cd $ORACLE_HOME/rdbms/admin
# do not touch the original file awrrpt.sql and awrrpti.sql, use awr.sql and awri.sql instead.
cp awrrpt.sql awrtemp.sql
cp awrrpti.sql awri.sql

vi awri.sql --> to append 'exit;' at the end of the file so that sqlplus will exit automatically during script run
vi awrtemp.sql to modify it like this:
---------------
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;


define num_days = '1';
define report_type = 'html';
define report_name = 'TEMPLATE0';
define begin_snap = 'TEMPLATE1';
define end_snap = 'TEMPLATE2';

@@awri
--
-- End of file
-------------------------

2. get the latest snap id unix timestamp(seconds)
sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql
----------
Enter value for num_days: 1

Listing the last day's Completed Snapshots

                            Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL          12953 06 Apr 2011 00:00       1
                  12954 06 Apr 2011 01:00       1
                  12955 06 Apr 2011 02:00       1
                  12956 06 Apr 2011 03:00       1
                  12957 06 Apr 2011 04:00       1
                  12958 06 Apr 2011 05:00       1
                  12959 06 Apr 2011 06:00       1
                  12960 06 Apr 2011 07:00       1
                  12961 06 Apr 2011 08:00       1
                  12962 06 Apr 2011 09:00       1
                  12963 06 Apr 2011 10:00       1
                  12964 06 Apr 2011 11:00       1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
----------------

we use the latest snap id 12964 as base number, let's convert it to unix timestamp.
date --date="Wed Apr  6 11:00:00 SGT 2011" +%s
1302058800

3. preparing shell script
[oracle@hpaydb admin]$ more ~/bin/awr.sh
#!/bin/sh

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin

# variables
SEC=`date +%s`
INIT=12964
INCR=`echo "($SEC-1302058800)/3600" | bc`
#1302058800 is  Wed Apr  6 11:00:00 SGT 2011
SNAPEND=$[$INIT+$INCR]
SNAPBEGIN=$[${SNAPEND}-2]

# program starts here , do not change anything below
sed -e "s#TEMPLATE1#$SNAPBEGIN#g" -e "s#TEMPLATE2#$SNAPEND#g" -e "s#TEMPLATE0#/tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html#g" $ORACLE_HOME/rdbms/admin/awrtemp.sql > $ORACLE_HOME/rdbms/admin/awr.sql

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awr.sql

# send out email
rm -f /tmp/prod_alert_awr_$SNAPEND.zip
zip -r /tmp/prod_alert_awr_$SNAPEND.zip /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log /tmp/prod_awr_${SNAPBEGIN}_${SNAPEND}.html
mutt -a /tmp/prod_alert_awr_$SNAPEND.zip -s "Prod alert log and AWR report between snap id $SNAPBEGIN and $SNAPEND" jephewu_email_address < /dev/null


4. put in cronjob
login as oracle, crontab -e
30 */2 * * * /home/oracle/bin/awr.sh
# every 2 hours to send out report

4. References
a. convert unix second to normal date
date -d @1187769064
b. put the following into /home/oracle/bin/awr to simplify the normal awr report generation
[oracle@hpaydb admin]$ more ~/bin/awr
#!/bin/sh

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql

chmod +x ~/bin/awr
Just run awr to generate normal awr report and awr.sh to generate awr every 2 hours automatically.

How to upgrade Oracle 11g 64bit from 11.1.0.6 to 11.1.0.7

Jephe Wu - http://linuxtechres.blogspot.com

Objective: easily upgrade Oracle 11g to 11.1.0.7
Environment: CentOS 5.5 64bit, Oracle 11g 11.1.0.6

Steps:

1. ssh into server as user 'oracle', then type in 'vncserver'
2. From windows pc, use vncviewer to connect to server.
3. at xterm windows, run Oracle installer, use the same path as what 11.1.0.6 is using. You cannot choose the different path.
4. follow the screen instruction to finish upgrading
5. use dbua command to finished database upgrading
6. lsnrctl start, sqlplus / as sysdba ; alter system register
7. database should has already been started by dbua itself.

How to enable ftp client to access ftp server behind FreeBSD firewall

Jephe Wu - http://linuxtechres.blogspot.com

Objective: allow the users on the LAN who are using Squid proxy (10.0.0.1) to be able to access ftp sites.
Environment: OpenBSD 4.5(1.2.3.4/10.0.0.2), Squid client(10.0.0.1) behind this OpenBSD firewall

Internet[1.2.3.4]OpenBSD4.5[10.0.0.2]<->[10.0.0.1]Squid/Web/DNS server
                                                              <->[10.0.0.10]sysadmin pc

Steps:
1. modify /etc/rc.conf to enable ftpproxy
vi /etc/rc.conf to change ftpproxy_flag from NO to YES

2. enable pf.conf for ftp outgoing and incoming web/dns requests

ext_if="fxp1"
int_if="fxp0"

set block-policy return
set loginterface $ext_if

set skip on lo

# scrub incoming pcakets like you cannot set both SYN and FIN
scrub in all

# ftpproxy
nat-anchor "ftp-proxy/*"
rdr-anchor "ftp-proxy/*"

# Redirect ftp traffic to proxy
rdr pass proto tcp from any to any port ftp -> 127.0.0.1 port 8021

# let squid proxy act as web server and dns server
rdr pass on $ext_if proto tcp from any to 1.2.3.4/32 port {80,443} -> 10.0.0.1
rdr pass on $ext_if proto udp from any to 1.2.3.4/32 port 53 -> 10.0.0.1

# squid proxy server can go to anywhere
nat pass on $ext_if from 10.0.0.1 to any -> 1.2.3.4

# setup a default deny policy
block in all

# activate spoofing protection for all interfaces
block in quick from urpf-failed

# anchor for ftpproxy
anchor "ftp-proxy/*"

# pass tcp, udp, and icmp out on the external (Internet) interface.
# tcp connections will be modulated, udp/icmp will be tracked statefully
pass out modulate state

antispoof quick for { lo $int_if }
pass in quick on $ext_if inet proto icmp all icmp-type { echorep, timex, unreach }

pass in quick on $ext_if proto udp to 1.2.3.4 port 53 keep state
pass in quick on $ext_if proto tcp to 1.2.3.4 port {80,443} synproxy state

# use synproxy for internal host 10.0.0.0/24
pass in quick on $int_if proto tcp from 10.0.0.0/24 to $int_if port ssh synproxy state

# allow admin pc for anything
pass in quick on $int_if from 10.0.0.10/24

3. startup ftp proxy
ftp-proxy
pfctl -f /etc/pf.conf

References:
a. http://www.cyberciti.biz/faq/freebsd-opebsd-pf-firewall-ftp-configuration/