awk: Find & Replace

1. Single file – Replaces Oracle with Naikh
awk ‘{gsub(“Oracle”,”Naikh”,$0); print > FILENAME }’ *.txt

2. Multiple Files – Replaces Oracle with Naikh
awk ‘{gsub(“Oracle”,”Naikh”,$0); print > FILENAME }’ *.txt

3. All the file which contains a pattern
awk ‘{gsub(“Oracle”,”Naikh”,$0); print > FILENAME }’ `find . exec grep -l “Oracle” {} \;

Note: Below command will not work
find . exec grep -l “Oracle” {} \; awk ‘{gsub(“Oracle”,”Naikh”,$0); print > FILENAME }’

Transportable TBS – Part 2

Assumptions
* Tablespace name: TTBS
* Source platform: Linux (32 bit)
* Target platform: Solaris (64 bit)

To Identify the endian format
select d.platform_name, endian_format
from v$transfortable_platform tp, v$database d
where tp.platform_name=d.platform_name;

List of all platform and respective endian format
select * from v$transfortable_platform
order by platform _id;

Source Machine

1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=(‘ttbs’,true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name=’TTBS’;

4. Export tablespace’s metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to target machine
cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Convert the datafile
rman> convert datafile ‘/temp/ttbs1.dbf’
to platform ‘Solaris [tm] (64-bit)’
from platform ‘Linux (32-bit)’
db_file_name_convert=”/temp/”,”/home/oracle/testdb/ttbs1.dbf”;

2. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/home/oracle/testdb/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

3. Make tablespace read write
alter tablespace ttbs read write;

Transportable TBS – Part 1

Assumptions
* Tablespace name=TTBS
* Same platform (source and target)

Source Machine
1. check tablespace is transprtable
a. execute this procedure
exec dbms_tts.transport_set_check=(‘ttbs’,true);

b. check for any violations
select * from transport_set_violations;

2. Make tablespace read only
alter tablspace ttbs read only;

3. Identify the datafiles
select file_name
from dba_data_files
where tablespace_name=’TTBS’;

4. Export tablespace’s metadata
expdp directory=dump1
dumpfile=expdp_ttbs.dmp
transportable_tablespace=ttbs
logfile=expdp_ttbs.log
job_name=j1

5. Copy datafile and export dump file to new location cp /home/oracle/dba01/ttbs1.dbf /temp/ttbs1.dbf cp /home/oracle/dpump1/expdp_ttbs.dmp /temp/expdp_ttbs.dmp

6. Make the tablespace read write
alter tablespace ttbs read write;

Target Machine

1. Import metadata
impdp directory=dump2
dumpfile=expdp_ttbs.dmp
transport_datafiles=/temp/ttbs1.dbf
logfile=impdp_ttbs.log
job_name=j2

2. Make tablespace read write
alter tablespace ttbs read write;

Managing Standby Database

Start the standby database

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect;

Shutdown the standby database

alter database recover managed standby database cancel;

shutdown immediate;


Registering the archive logs manualy

alter database register or replace logfile ‘/opt/oracle/archive/oradb_1_1234_48540.arc’;

Opening standby database in READ ONLY mode

alter database recover managed standby database cancel;

alter database open read only;

From READ ONLY mode to Managed recovery
a. terminate all the active user sessions on the standby database

b.
alter database recover managed standby database disconnect;

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

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 archive log from sequence 2503 until sequence 2150;
}

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

restore archive log 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;