Simple Perl Script

#
# Simple perl script to connect to Oracle database using DBI::Oracle
#

#!/usr/bin/perl -w

# Get the count of the parameters
my $count=@ARGV;

# if paramters are not 3 exit
if ($count!=3) {
print "Usage: ./dbcon.pl [dbname] [user] [password]\n";
exit ;
}

use strict;
use DBI;

# Assign the parameter values to variables
my $db=$ARGV[0];
my $usr=$ARGV[1];
my $pass=$ARGV[2];

# Connect to database
my $dbh=DBI->connect("dbi:Oracle:$db",$usr,$pass)
or die "Connection failed DBI::errstr";


# Prepare the sql statement
my $sql="select name,open_mode,log_mode,database_role from v\$database";
my $sth=$dbh->prepare($sql);

# Execute the sql statement
$sth->execute();

# Fetch the data and dilpay
my @row=$sth->fetchrow_array;
print "Database Details\n";
print "Database Name: $row[0]\n";
print "Database Role: $row[3]\n";
print "Open Mode : $row[1]\n";
print "Log Mode : $row[2]\n";

# Disconnect from the database
$sth->finish();
$dbh->disconnect();

# Output

[oracle@db10g]$ chmod +x dbcon.pl
[oracle@db10g]$ ./dbcon.pl
Usage: ./4.pl [dbname] [user] [password]

[oracle@db10g]$ ./dbcon.pl db10g naikh naikh
Database DetailsDatabase
Name: DUPDB
Database Role: PRIMARY
Open Mode : READ WRITE
Log Mode : ARCHIVELOG

RMAN Backup Restoration Drill

This post explains the procedure to test the RMAN backups on a test box. Here I am doing only partial restoration (not full database) due to space constraints.

1. Copy the pfile from prod box to test box and edit it.
$ scp init.ora oracle@TestBox:/restore_path

2. Create password file
$ orapwd file=orapw password=oracle

3. Create necessary folders in /restore_path
$ cd /restore_path
$ mkdir adump bdump cdump udump redo data archive

4. Restore the control file
$ export ORACLE_SID=
$ sqlplus "/ as sysdba"
sql> startup nomount pfile=/restore_path/init.ora
sql> exit
$ rman target /
rman> run {
allocate channel c1 type disk;
restore controlfile from '/restore_path/rman_controlfile_bkp.ctl';
}

5. Catalog all the backup files
rman> alter database mount;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s5_p1;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s4_p2;
rman> catalog backuppiece '/restore_path/ORADB_rman_full_bkp_s3_p3;
...

6. Restore selected tablespace
rman> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
set newname for datafile 1 to '/restore_path/data/system01.dbf';
set newname for datafile 3 to '/restore_path/data/sysaux01.dbf';
set newname for datafile 42 to '/restore_path/data/audit_data01.dbf';
set newname for datafile 2 to '/restore_path/data/audit_data.dbf';
set newname for datafile 10 to '/restore_path/data/undotbs01.dbf';
restore tablespace system;
restore tablespace syasaux;
restore tablespace undotbs01;
restore tablespace audit_data;
switch datafile all;
}

7. Drop all the remaining datafiles which are not included in restore drill
alter database datafile 5 offline off;
alter database datafile 15 offline off;
alter database datafile 25 offline off;
alter database datafile 35 offline off;
.....

8. Recover database
$ export ORACLE_SID=
$ sqlplus "/ as sysdba"
sql> recover database using backup contrlfile until cancel;

9. Restore archive logs
$ rman target /
rman> run {
set archivelog destination to '/restore_path/archive/';
allocate channel c1 type disk;
allocate channel c2 type disk;
restore archivelog from sequence 2503 until sequence 2150;
}

9.1 For RAC
restore archivelog from sequence 2503 until sequence 2150 thread=1;

restore archivelog from sequence 2503 until sequence 2150 thread=2;

10. Rename Redo log files
sql> alter database rename file '/prod_path/redo/reado01.log' to
'/restore_path/redo/reado01.log';

11. Cleare all redo log groups
sql> alter database cleare logfile group 1;
sql> alter database cleare logfile group 2;
sql> alter database cleare logfile group 3;
.....

12. Open the database with openrestlog option
sql> alter database open resetlogs;

13. Query the database
sql> select name,open_mode,log_mode,database_role from v$database;

14. Drop the database
sql> shutdown abort;
sql> startup nomount;
sql> alter database mount exclusive;
sql> alter system enable restricted session;
sql> drop database;

15. Drop all the directories created for restoration
$ rmdir adump bdump cdump udump redo data archive

Perl DBD::Oracle

Installing Perl DBD::Oracle on linux

1. Download DBD::Oracle from the link
http://search.cpan.org/
search for DBD-Oracle in modules

2. Uncompress the downloaded file
$ gunzip DBD-Oracle-1.19.tat[1].gz

3. Untar the file
$ tar -xvf DBD-Oracle-1.19.tat[1]
$ cd DBD-Oracle-1.19

4. Login as root
$ su - root
password: *****

5. Go to DBD-Oracle-1.19 directory
$cd DBD-Oracle-1.19

6. Install the module
$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
$ perl MakeFile.PL
$ make
$ make test
$ make intall
$ exit [exit from the root]

STANDBY DB ISSUES

These are some of the issues which i have faced while managing the physical standby database:

1. ORA-00368: Checksum error
2. ORA-01119: Error in creating database file


1. ORA-00368: Checksum error
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 10241 change 2700 time 11/22/2007
ORA-00334: archived log: '/archivelog/oradb1_arc_10444_596803436.arc'

Reason for the above error:
If checksum of the archive file at the production and standby
database is different. Compare the checksum using md5sum.

Solution:
a. If archive log file is present at the production box.
Then move the file to standby box.
b. Else Restore the archive log file from RMAN backup.
RMAN> restore archivelog logseq=42603 thread=1;
Transferred the file to standby box.
c. Make sure the checksum of the file ar prod and standbby box is same.
Use md5sum command.
d. Register the logfile:
SQL> alter database register or replace logfile '/archivelog/oradb1_arc_42603_596803436.arc';

2. ORA-01119: Error in creating database file
ORA-01119: error in creating database file '/opt/oraprod/tbs2.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Linux-x86_64 Error: 13: Permission denied
Some recovered datafiles maybe left media fuzzy

Reason for the above error:
File system of production and standby database were different. So when New datafile '/opt/oraprod/tbs2.dbf'
was added to production db at the standby it could not create the same file as of production.

Solution:
At the standby database:
sql> recover managed standby database cancel;
sql> select file#,name from v$datafile where name like '%UNNAMED%';
sql> alter database create datafile '/opt/oracle/product/10.2.0/DB/dbs/UNNAMED00049' as '/opt/orastd/tbs2.dbf';
sql> recover managed standby database disconnect from session;

To avoid the above error just execute this statement:
sql> alter system set STANDBY_FILE_MANAGEMENT=auto scope=both;

RMAN Resoter and Recover

1. The whole database
RMAN> startup force mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

2. Tablespace
RMAN> sql 'alter tablespace tbs1 offline';
RMAN> restore tablespace tbs1;
RMAN> recover tablespace tbs1;
RMAN> sql 'alter tablespace tbs1 online';

3. Datafile
RMAN> sql 'alter database datafile 5 offline';
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> sql 'alter database datafile 5 online';

4. Block of a Datafile
RMAN> blockrecover datafile 5 block 126;

SQL stetment of a USER

Below SQL query is used to find the SQL statement of a USER:

select sess.sid, sqltext.piece, sqltext.sql_text, sqltext.command_type
from v$sqltext sqltext, v$session sess
where sqltext.address = sess.sql_address
and sqltext.hash_value = sess.sql_hash_value
and sess.username = 'NAIKH'
order by 2;

Bloking Locks

SQL query to identify blocking locks:

1.
select s.username "Blocking Username",l1.sid "Blocking SID", l2.sid "Blocked SID",s2.username "Blocked Username"
from v$lock l1, v$lock l2,v$session s,v$session s2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2
and l1.sid=s.sid
and l2.sid=s2.sid;

2.
select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive',l.request) Lock_Req_In, l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
from v$lock l
where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc;


Kill The Blocking Session

Find the 'serial' number of bloking session:
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status
from v$process p, v$session s
where s.paddr = p.addr (+) and s.sid = '&sid';

Use alter system command to kill the session:
Alter system kill session '115,10366' immediate;

Links Part 1

Some useful links:

Oracle
Oracle 9i funda1,funda2 and PT PPTs

Shell Scripting
1. Bash Guide for Beginners
2. Easy Shell Scripting
3. Advanced Bash-Scripting Guide

One Liners
1. AWK
2. AWK2
3. SED
4. Perl
5. Solaris

Linux
1. Linux in a Nutshell
2. Red Hat Linux Unleashed
3. Linux Documentation Collection

Solaris
1. System Administration Commands
2. Solaris 10 Documentation collection
3. Solaris Tips & Tricks

Unix
UNIX Unleashed

Create Read-Write copy from Standby database.

Step by step procedure to Create a Read-Write copy from the Standby database on the same box.

Steps are devided into three parts

Part 1: Create new database

Part 2 : Rename the database

Part 3 : Start the standby database

Standby database name : stbydb

Read-Write copy database name : rwdb


Part 1: Create new database

1. Identify all the datafiles, redo log file and control files to be copied.

2. Create a SQL script to rename the datafiles and redo log files.

3. Create pfile from stbydb's spfile

4. Create pfile for rwdb

[oracle@naikh] cp initstbydb.ora initrwdb.ora

5. Shutdown the stbydb database

[oracle@naikh] export ORACLE_SID=stbydb

[oracle@naikh] sqlplus "/ as sysdba'

sql> alter database recover managed standby database cancel;

sql> shutdown immediate

6. Copy all the datafiles, redo log file and control files to new location

7. Edit the initrwdb.ora file

a. Change the CONTROL_FILE parameter path to new controlfile path

b. Remove followig parameters

i. standby_file_management

ii. log_archive_dest_1

iii. log_archive_format

c. Change the value of the following parameters with new value

i. audit_file_dest

ii. background_dump_dest

iii. core_dump_dest

iv. user_dump_dest

8. Create dump directories

[oracle@naikh] mkdir -p admin/rwdb/{adump,bdump,cdump,udump}

9. Rename the datafiles and redo log files

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] sqlplus "/ as sysdba'

sql> startup nomount pfile='initstbydb.ora'

sql> alter database mount;

sql> alter database rename file ''/old_path/system01.dbf' to
'/new_path/system01.dbf'; ;

.

.

.

10. Activate the standby database

sql> alter database activate standby database;

11. Open the database

sql> alter database open;

12. Create new temp tablespace, make this default and drop the old one.

sql> create temporary tablespace temp1
tempfile '/new_path/temp1.dbf' size 2048m
autoextend off
extent menagement local uniform size 1m;

sql> alter database default temporary tablespace temp1;

sql> drop tablespace temp;

13. Disable archive log mode

sql> shutdown immediate;
sql> startup mount pfile='initrwdb.ora';
sql> alter database noarchivelog;
sql> alter database open;


Part 2 : Rename the database

1. Mount the database

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] export ORACLE_SID=stbydb
[oracle@naikh] sqlplus "/ as sysdba'
sql> startup mount pfile='initrwdb.ora'

2. Run the NID utolity

[oracle@naikh] nid target=sys/oracle dbname=rwdb logfile=nid_rwdb.log

3. Edit the initrwdb.ora file

i. Change the DB_NAME parameter value to rwdb

4. Create new password file

[oracle@naikh] orapwd file=orapwrwdb password=oracle entries=5

5. Start the rwdb in reserlog mode

sql> alter dabase open resetlogs;

6. Create spfile

sql> create pfile from pfile;

7. Setup Listener and TNS


Part 3 : Start the standby database

1. Start the stdby database

[oracle@naikh] cd $ORAACLE_HOME/dbs
[oracle@naikh] export ORACLE_SID=stbydb

[oracle@naikh] sqlplus "/ as sysdba'

sql> startup nomount

sql> alter database mount standby database;

sql> alter database recover managed standby database disconnect;


Part 4 : Useful SQL statements

1. To get the command to copy all the datafiles,redolog files and controlfiles to new location use the following sql statement:

select 'cp '||name||'/new_path/'||
substr(name,instr(name,'/',-1,1)+1))||' &'
from (
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile
)

2. To get the sql statements to Rename all datafiles and redolog files use the following sql statement:

select 'alter database rename file '''||name|| ''' to
''/new_path/'||substr(name,instr(name,'/',-1,1)+1)||''';'
from (
select name from v$datafile
union all
select member from v$logfile
)

Download VMware Server Now

Here is the link to download Free VMWARE : http://www.vmware.com/download/server/
You have to register before downloading.

Managing RAC Instances

1. Status of all RAC instaces
[oracle@naikh] srvctl status database -d testdb

2. Start all RAC instances
[oracle@naikh] srvctl start database -d testdb

3. Startup one RAC instance
[oracle@naikh] srvctl start instance -d testdb -i testdb1

4. Shutdown one RAC instance
[oracle@naikh] srvctl stop instance -d testdb -i testdb1

More info: Using srvctl to Manage your 10g RAC Database

Oracle Documentations

For Oracle database related inforamtion use:

• Oracle 11g
• Oracle 10g
• Oracle 9i
• Oracle 8i