Restore Spfile and Controlfile

Different ways of restoring spfile and controlfile

1. Restore SPFILE

restore spfile to '/bkp/spfile.ora';

restore spfile to '/bkp/spfile.ora' from autobackup;

restore spfile to '/bkp/spfile.ora' from tag=man_bkp;

restore spfile to '/bkp/spfile.ora' from /home/oracle/bkp/c-543432-24342-01 ;

restore spfile to '/bkp/spfile.ora' until time 'sysdate-31;

2. Restore Controlfile

restore controlfile to '/bkp/controlfile.ctl';

restore controlfile to '/bkp/controlfile.ctl' from autobackup;

restore controlfile to '/bkp/controlfile.ctl' from tag=man_bkp;

restore controlfile to '/bkp/controlfile.ctl' from /bkp/c-543432-24342-01 ;

restore controlfile to '/bkp/controlfile.ctl' until time 'sysdate-31;

Transportable TBS - Part 3

Transportable Tablespace from RMAN backup.

Assumptions
* Tablespace name=TTBS
* Same platform (source and target)
* We have RMAN backup of the database

RMAN> transport tablespace TTBS
tablespace destination '/opt/oracle/ttbs_dir'
auxiliary destination '/opt/oracle/aux_ttbs'
datapump directory DATA_PUMP_DIR
dump file 'expdp_ttbs_01.dmp'
import script 'impdp_ttbs_01.sql'
export log 'expdp_ttbs.log';


Explanation:
tablespace destination - in this location RMAN stores datafiles of the TTBS
auxiliary destination - in this location RMAN creates temporary database and later deletes it.
datapump directory - in this export dump of metadata of TTBS is stored
dump file - Name of the datapump dump file
import script - RMAN creates import file which can be used to import the metadata

Possible Error:
While importing i got the below error. i just created the user_name in the database.

ORA-29342: user user_name does not exist in the database

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;

Useful SQL statements

1. To create a script to copy all the datafiles to new location:

select 'cp 'name' /newpath/'substr(name,instr(name,'/',-1,1)+1))' &'

from v$database;

2. To create a script to copy all the datafiles, controlfiles and redolog files to new location:

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
)

3. To get the sql statements to Rename all datafiles and redolog files:

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
)

4. Copy datafile

select 'cp ' file_name

decode(substr(file_name,1,instr(file_name,'/',-1,1)),

'/Old_path1/','/New_path1/',

'/Old_path2/','/New_path2/'

)substr(file_name,instr(file_name,'/',-1,1)+1)

' &' file_name

from dba_data_files;

5. Copy redolog file

select 'cp ' member ' '

decode(substr(member,1,instr(member,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(member,instr(member,'/',-1,1)+1)' &' member

from v$logfile;

6. Rename data file

select ' Alter database rename file '''file_name ''' to '''

decode(substr(file_name,1,instr(file_name,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(file_name,instr(file_name,'/',-1,1)+1) ' ;' file_name
from dba_data_files;

7. Rename redolog file

select 'alter database rename file ''' member ''' to '''
decode(substr(member,1,instr(member,'/',-1,1)),

'/Old_path1/','/New_path1/'

,'/Old_path2/','/New_path2/')

substr(member,instr(member,'/',-1,1)+1) ''' ;' member
from v$logfile;

8. Datafile Offline drop

select 'alter database datafile 'file_id' offline drop;'

from dba_data_files;

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

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]