Tuesday, June 30, 2009

PL/SQL script for finding invalid oracle directories

-- Script for Finding Invalid Directories.
-- Create a test file and check for the status

declare
f utl_file.file_type;
BEGIN

FOR X IN ( SELECT * FROM DBA_DIRECTORIES WHERE OWNER= 'SYS' ) LOOP
BEGIN
f := utl_file.fopen(x.directory_name,'madhu_test.txt','W');
IF utl_file.is_open(f) THEN
UTL_FILE.fclose(f);
UTL_FILE.fremove(x.directory_name,'madhu_test.txt');
end if;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(x.directory_name || ' -- ' || x.directory_path || ' is invalid');
END;
END LOOP;

END;
/
Posted by at No comments:

Perl Script to shutdown/startup all dbs on a server. oratab_shutdown_startup.pl

#!/usr/bin/perl

#########################################################################
# Shutdown or Startup all the databases from oratab file with a Y flag.
# pass shutdown or startup parameter
#########################################################################

use strict;
my $ORATAB;
my @fields;
my $SPOOL_FILE;
my $cmd;
my $result;


# Validate Input Parameter. STARTUP or SHUTDOWN
if ( lc($ARGV[0]) eq "startup") { $cmd = "startup" ; }
elsif ( lc($ARGV[0]) eq "shutdown") { $cmd = "shutdown immediate" ; }
else { print "Invalid Argument. Pass either shutdown or startup" . "\n"; exit; }
print $cmd . "\n";

# Get ORATAB file
if (-e '/etc/oratab') { $ORATAB='/etc/oratab'; }
elsif (-e '/var/opt/oracle/oratab') { $ORATAB='/var/opt/oracle/oratab'; }
else { print "oratab not found, please check manually", "\n"; exit; }

# Loop thu each entry in the oratab
open FILE, $ORATAB || die("Could not ORATAB!");
while () {

chomp; # chops off what is in $/
next if /^#/; # discard comments
next if /^\s+$/; # discard blank lines
@fields=split(/:/,$_); # Split the oratab entry into 3 fields
next if (@fields[2] ne "Y"); # discard if the 3rd variable is not Y
print "Processing " . @fields[0] . " " . @fields[1] . "\n";

$ENV{'ORACLE_SID'} = @fields[0];
$ENV{'ORACLE_HOME'} = @fields[1];
$SPOOL_FILE=@fields[0] . "_list.out";

$result = `@fields[1]/bin/sqlplus "/as sysdba" <$cmd
exit
END
`;

print $result,"\n";

}

close FILE;
exit;
Posted by at No comments:

Tuesday, May 12, 2009

Restoring db to a Different Host using RMAN

PURPOSE:
Restoring saprod on 10.65.81.7(2 node rac using asm) to saprod on 10.65.79.11 (non-rac non-asm)
Shouldn't connect to saprod on 10.65.81.7 or the Catalog database.
You should know the dbid of saprod on 10.65.81.7 ( select dbid, name from v$database)

Both servers should be at the same patch level 10.2.0.3

REFERENCES:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1007814
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10734/rcmrecov.htm
http://arjudba.blogspot.com/2008/05/restore-and-recover-database-to-new.html


STEP 1: NOMOUNT saprod on 10.65.79.11 server
======================================
mkdir /u01/app/oracle/admin/saprod and subdirs like adump, bdump, cdump, pfile, udump
export ORACLE_SID=saprod
export ORACLE_HOME=/u01/app/oracle/product/10.2.0
startup nomount pfile='/u02/app/oracle/admin/saprod/pfile/initsaprod.ora' -- you should set undotablespace=UNDOTS1

mv /u02/app/oracle/admin/sagat/pfile/initsaprod.ora /u02/app/oracle/admin/saprod/pfile/.

STEP 2: RESTORE CONTROL FILE
=============================
-- Restore the control file from an autobackup and then mount the database.
-- RMAN restores the control file to whatever location you specified in the CONTROL_FILES initialization parameter initsaprod.ora file.
export ORACLE_SID=saprod
export ORACLE_HOME=/u01/app/oracle/product/10.2.0

rman target /

SET DBID 3401461153; -- SELECT dbid, name FROM V$DATABASE from saprod on 10.65.81.7
RUN
{
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}


STEP 3: GET DBFILE,REDOs FROM MOUNTED saprod.
================================================
sysdba to saprod

-- prepare SET NAENAME FOR DATAFILE statements
SELECT 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ' || CHR(39) ||
'/u02/app/oracle/oradata/SAGAT' || SUBSTR(
name
, LENGTH(name)-INSTR(REVERSE(name),'/',1,1)+1
, INSTR(name,'.',1) - (LENGTH(name)-INSTR(REVERSE(name),'/',1,1)+ 1 )
)
|| '_' || to_char(file#) || '.dbf' || chr(39) || ';'
FROM V$DATAFILE;

-- prepare ALTER DATABASE RENAME FILE for redo log files
SELECT 'sql ' || chr(34) || 'ALTER DATABASE RENAME FILE ' || chr(39) || chr(39) || member || chr(39) || chr(39) || ' TO '
|| chr(39) || chr(39) || '/u02/app/oracle/oradata/SAGAT/redo' || group# || decode( mod(rownum,2),1,'A','B') || '.log' || chr(39) || chr(39) || ' ' || chr(34) || ';'
FROM V$LOGFILE order by group#, rownum;



STEP 4: RUN RESTORE and RECOVER SCRIPT
====================================================
-- Submit this in nohup.

#!/bin/ksh
export ORACLE_SID=saprod
export ORACLE_HOME=/u01/app/oracle/product/10.2.0

rman target / msglog recover_saprod.log << EOF
run {
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t3 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

SET NEWNAME FOR DATAFILE 1 TO '/u02/app/oracle/oradata/SAGAT/system_1.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u02/app/oracle/oradata/SAGAT/undotbs1_2.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u02/app/oracle/oradata/SAGAT/sysaux_3.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u02/app/oracle/oradata/SAGAT/users_4.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u02/app/oracle/oradata/SAGAT/undotbs2_5.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u02/app/oracle/oradata/SAGAT/aaapp_6.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/u02/app/oracle/oradata/SAGAT/aalarge_7.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/u02/app/oracle/oradata/SAGAT/adapp_8.dbf';
SET NEWNAME FOR DATAFILE 9 TO '/u02/app/oracle/oradata/SAGAT/amapp_9.dbf';
SET NEWNAME FOR DATAFILE 10 TO '/u02/app/oracle/oradata/SAGAT/avapp_10.dbf';
SET NEWNAME FOR DATAFILE 11 TO '/u02/app/oracle/oradata/SAGAT/bdapp_11.dbf';
SET NEWNAME FOR DATAFILE 12 TO '/u02/app/oracle/oradata/SAGAT/bnapp_12.dbf';
SET NEWNAME FOR DATAFILE 13 TO '/u02/app/oracle/oradata/SAGAT/bnlarge_13.dbf';
SET NEWNAME FOR DATAFILE 14 TO '/u02/app/oracle/oradata/SAGAT/ccapp_14.dbf';
SET NEWNAME FOR DATAFILE 15 TO '/u02/app/oracle/oradata/SAGAT/cuaudit_15.dbf';
SET NEWNAME FOR DATAFILE 16 TO '/u02/app/oracle/oradata/SAGAT/cularg1_16.dbf';
SET NEWNAME FOR DATAFILE 17 TO '/u02/app/oracle/oradata/SAGAT/cularg2_17.dbf';
SET NEWNAME FOR DATAFILE 18 TO '/u02/app/oracle/oradata/SAGAT/cularg3_18.dbf';
SET NEWNAME FOR DATAFILE 19 TO '/u02/app/oracle/oradata/SAGAT/cularge_19.dbf';
SET NEWNAME FOR DATAFILE 20 TO '/u02/app/oracle/oradata/SAGAT/eoapp_20.dbf';
SET NEWNAME FOR DATAFILE 21 TO '/u02/app/oracle/oradata/SAGAT/eolarge_21.dbf';
SET NEWNAME FOR DATAFILE 22 TO '/u02/app/oracle/oradata/SAGAT/faapp_22.dbf';
SET NEWNAME FOR DATAFILE 23 TO '/u02/app/oracle/oradata/SAGAT/falarge_23.dbf';
SET NEWNAME FOR DATAFILE 24 TO '/u02/app/oracle/oradata/SAGAT/fgapp_24.dbf';
SET NEWNAME FOR DATAFILE 25 TO '/u02/app/oracle/oradata/SAGAT/fsapp_25.dbf';
SET NEWNAME FOR DATAFILE 26 TO '/u02/app/oracle/oradata/SAGAT/giapp_26.dbf';
SET NEWNAME FOR DATAFILE 27 TO '/u02/app/oracle/oradata/SAGAT/gpapp_27.dbf';
SET NEWNAME FOR DATAFILE 28 TO '/u02/app/oracle/oradata/SAGAT/hpapp_28.dbf';
SET NEWNAME FOR DATAFILE 29 TO '/u02/app/oracle/oradata/SAGAT/hrapp_29.dbf';
SET NEWNAME FOR DATAFILE 30 TO '/u02/app/oracle/oradata/SAGAT/hrapp1_30.dbf';
SET NEWNAME FOR DATAFILE 31 TO '/u02/app/oracle/oradata/SAGAT/hrapp2_31.dbf';
SET NEWNAME FOR DATAFILE 32 TO '/u02/app/oracle/oradata/SAGAT/hrapp3_32.dbf';
SET NEWNAME FOR DATAFILE 33 TO '/u02/app/oracle/oradata/SAGAT/hrapp4_33.dbf';
SET NEWNAME FOR DATAFILE 34 TO '/u02/app/oracle/oradata/SAGAT/hrapp5_34.dbf';
SET NEWNAME FOR DATAFILE 35 TO '/u02/app/oracle/oradata/SAGAT/hrapp6_35.dbf';
SET NEWNAME FOR DATAFILE 36 TO '/u02/app/oracle/oradata/SAGAT/hrlarge_36.dbf';
SET NEWNAME FOR DATAFILE 37 TO '/u02/app/oracle/oradata/SAGAT/hrwork_37.dbf';
SET NEWNAME FOR DATAFILE 38 TO '/u02/app/oracle/oradata/SAGAT/htapp_38.dbf';
SET NEWNAME FOR DATAFILE 39 TO '/u02/app/oracle/oradata/SAGAT/inapp_39.dbf';
SET NEWNAME FOR DATAFILE 40 TO '/u02/app/oracle/oradata/SAGAT/inlarge_40.dbf';
SET NEWNAME FOR DATAFILE 41 TO '/u02/app/oracle/oradata/SAGAT/paapp_41.dbf';
SET NEWNAME FOR DATAFILE 42 TO '/u02/app/oracle/oradata/SAGAT/palarge_42.dbf';
SET NEWNAME FOR DATAFILE 43 TO '/u02/app/oracle/oradata/SAGAT/patrol_43.dbf';
SET NEWNAME FOR DATAFILE 44 TO '/u02/app/oracle/oradata/SAGAT/pcapp_44.dbf';
SET NEWNAME FOR DATAFILE 45 TO '/u02/app/oracle/oradata/SAGAT/pclarge_45.dbf';
SET NEWNAME FOR DATAFILE 46 TO '/u02/app/oracle/oradata/SAGAT/piapp_46.dbf';
SET NEWNAME FOR DATAFILE 47 TO '/u02/app/oracle/oradata/SAGAT/pilarge_47.dbf';
SET NEWNAME FOR DATAFILE 48 TO '/u02/app/oracle/oradata/SAGAT/piwork_48.dbf';
SET NEWNAME FOR DATAFILE 49 TO '/u02/app/oracle/oradata/SAGAT/poapp_49.dbf';
SET NEWNAME FOR DATAFILE 50 TO '/u02/app/oracle/oradata/SAGAT/ps_large_idx_50.dbf';
SET NEWNAME FOR DATAFILE 51 TO '/u02/app/oracle/oradata/SAGAT/ps_large_tbl_51.dbf';
SET NEWNAME FOR DATAFILE 52 TO '/u02/app/oracle/oradata/SAGAT/ps_med_idx_52.dbf';
SET NEWNAME FOR DATAFILE 53 TO '/u02/app/oracle/oradata/SAGAT/ps_med_tbl_53.dbf';
SET NEWNAME FOR DATAFILE 54 TO '/u02/app/oracle/oradata/SAGAT/ps_small_idx_54.dbf';
SET NEWNAME FOR DATAFILE 55 TO '/u02/app/oracle/oradata/SAGAT/ps_small_tbl_55.dbf';
SET NEWNAME FOR DATAFILE 56 TO '/u02/app/oracle/oradata/SAGAT/ps_tiny_idx_56.dbf';
SET NEWNAME FOR DATAFILE 57 TO '/u02/app/oracle/oradata/SAGAT/ps_tiny_tbl_57.dbf';
SET NEWNAME FOR DATAFILE 58 TO '/u02/app/oracle/oradata/SAGAT/psimage_58.dbf';
SET NEWNAME FOR DATAFILE 59 TO '/u02/app/oracle/oradata/SAGAT/psimgr_59.dbf';
SET NEWNAME FOR DATAFILE 60 TO '/u02/app/oracle/oradata/SAGAT/psindex_60.dbf';
SET NEWNAME FOR DATAFILE 61 TO '/u02/app/oracle/oradata/SAGAT/psindex01_61.dbf';
SET NEWNAME FOR DATAFILE 62 TO '/u02/app/oracle/oradata/SAGAT/pstemp_62.dbf';
SET NEWNAME FOR DATAFILE 63 TO '/u02/app/oracle/oradata/SAGAT/system_63.dbf';
SET NEWNAME FOR DATAFILE 64 TO '/u02/app/oracle/oradata/SAGAT/pswork_64.dbf';
SET NEWNAME FOR DATAFILE 65 TO '/u02/app/oracle/oradata/SAGAT/ptamsg_65.dbf';
SET NEWNAME FOR DATAFILE 66 TO '/u02/app/oracle/oradata/SAGAT/ptapp_66.dbf';
SET NEWNAME FOR DATAFILE 67 TO '/u02/app/oracle/oradata/SAGAT/ptappe_67.dbf';
SET NEWNAME FOR DATAFILE 68 TO '/u02/app/oracle/oradata/SAGAT/ptaudit_68.dbf';
SET NEWNAME FOR DATAFILE 69 TO '/u02/app/oracle/oradata/SAGAT/ptcmstar_69.dbf';
SET NEWNAME FOR DATAFILE 70 TO '/u02/app/oracle/oradata/SAGAT/ptlock_70.dbf';
SET NEWNAME FOR DATAFILE 71 TO '/u02/app/oracle/oradata/SAGAT/ptprc_71.dbf';
SET NEWNAME FOR DATAFILE 72 TO '/u02/app/oracle/oradata/SAGAT/ptprjwk_72.dbf';
SET NEWNAME FOR DATAFILE 73 TO '/u02/app/oracle/oradata/SAGAT/ptrpts_73.dbf';
SET NEWNAME FOR DATAFILE 74 TO '/u02/app/oracle/oradata/SAGAT/pttbl_74.dbf';
SET NEWNAME FOR DATAFILE 75 TO '/u02/app/oracle/oradata/SAGAT/pttlrg_75.dbf';
SET NEWNAME FOR DATAFILE 76 TO '/u02/app/oracle/oradata/SAGAT/pttree_76.dbf';
SET NEWNAME FOR DATAFILE 77 TO '/u02/app/oracle/oradata/SAGAT/ptwork_77.dbf';
SET NEWNAME FOR DATAFILE 78 TO '/u02/app/oracle/oradata/SAGAT/pvapp_78.dbf';
SET NEWNAME FOR DATAFILE 79 TO '/u02/app/oracle/oradata/SAGAT/pyapp_79.dbf';
SET NEWNAME FOR DATAFILE 80 TO '/u02/app/oracle/oradata/SAGAT/pylarge_80.dbf';
SET NEWNAME FOR DATAFILE 81 TO '/u02/app/oracle/oradata/SAGAT/pywork_81.dbf';
SET NEWNAME FOR DATAFILE 82 TO '/u02/app/oracle/oradata/SAGAT/salarge_82.dbf';
SET NEWNAME FOR DATAFILE 83 TO '/u02/app/oracle/oradata/SAGAT/sfapp_83.dbf';
SET NEWNAME FOR DATAFILE 84 TO '/u02/app/oracle/oradata/SAGAT/srapp_84.dbf';
SET NEWNAME FOR DATAFILE 85 TO '/u02/app/oracle/oradata/SAGAT/stapp_85.dbf';
SET NEWNAME FOR DATAFILE 86 TO '/u02/app/oracle/oradata/SAGAT/stlarge_86.dbf';
SET NEWNAME FOR DATAFILE 87 TO '/u02/app/oracle/oradata/SAGAT/stwork_87.dbf';
SET NEWNAME FOR DATAFILE 88 TO '/u02/app/oracle/oradata/SAGAT/tlapp_88.dbf';
SET NEWNAME FOR DATAFILE 89 TO '/u02/app/oracle/oradata/SAGAT/tllarge_89.dbf';
SET NEWNAME FOR DATAFILE 90 TO '/u02/app/oracle/oradata/SAGAT/tlwork_90.dbf';
SET NEWNAME FOR DATAFILE 91 TO '/u02/app/oracle/oradata/SAGAT/waapp_91.dbf';
SET NEWNAME FOR DATAFILE 92 TO '/u02/app/oracle/oradata/SAGAT/saapp_92.dbf';
SET NEWNAME FOR DATAFILE 93 TO '/u02/app/oracle/oradata/SAGAT/saapp_1_93.dbf';
SET NEWNAME FOR DATAFILE 94 TO '/u02/app/oracle/oradata/SAGAT/temp01_94.dbf';
SET NEWNAME FOR DATAFILE 95 TO '/u02/app/oracle/oradata/SAGAT/coapp_95.dbf';
SET NEWNAME FOR DATAFILE 96 TO '/u02/app/oracle/oradata/SAGAT/eobfapp_96.dbf';
SET NEWNAME FOR DATAFILE 97 TO '/u02/app/oracle/oradata/SAGAT/eocfapp_97.dbf';
SET NEWNAME FOR DATAFILE 98 TO '/u02/app/oracle/oradata/SAGAT/eodsapp_98.dbf';
SET NEWNAME FOR DATAFILE 99 TO '/u02/app/oracle/oradata/SAGAT/eoecapp_99.dbf';
SET NEWNAME FOR DATAFILE 100 TO '/u02/app/oracle/oradata/SAGAT/eoeclrg_100.dbf';
SET NEWNAME FOR DATAFILE 101 TO '/u02/app/oracle/oradata/SAGAT/eoeiapp_101.dbf';
SET NEWNAME FOR DATAFILE 102 TO '/u02/app/oracle/oradata/SAGAT/eoewapp_102.dbf';
SET NEWNAME FOR DATAFILE 103 TO '/u02/app/oracle/oradata/SAGAT/eoiuapp_103.dbf';
SET NEWNAME FOR DATAFILE 104 TO '/u02/app/oracle/oradata/SAGAT/eoltapp_104.dbf';
SET NEWNAME FOR DATAFILE 105 TO '/u02/app/oracle/oradata/SAGAT/eoppapp_105.dbf';
SET NEWNAME FOR DATAFILE 106 TO '/u02/app/oracle/oradata/SAGAT/eopplrg_106.dbf';
SET NEWNAME FOR DATAFILE 107 TO '/u02/app/oracle/oradata/SAGAT/epapp_107.dbf';
SET NEWNAME FOR DATAFILE 108 TO '/u02/app/oracle/oradata/SAGAT/eplarge_108.dbf';
SET NEWNAME FOR DATAFILE 109 TO '/u02/app/oracle/oradata/SAGAT/erapp_109.dbf';
SET NEWNAME FOR DATAFILE 110 TO '/u02/app/oracle/oradata/SAGAT/hrapp7_110.dbf';
SET NEWNAME FOR DATAFILE 111 TO '/u02/app/oracle/oradata/SAGAT/hrsapp_111.dbf';
SET NEWNAME FOR DATAFILE 112 TO '/u02/app/oracle/oradata/SAGAT/hrslarge_112.dbf';
SET NEWNAME FOR DATAFILE 113 TO '/u02/app/oracle/oradata/SAGAT/hrswork_113.dbf';
SET NEWNAME FOR DATAFILE 114 TO '/u02/app/oracle/oradata/SAGAT/py0lrg_114.dbf';
SET NEWNAME FOR DATAFILE 115 TO '/u02/app/oracle/oradata/SAGAT/sacapp_115.dbf';
SET NEWNAME FOR DATAFILE 116 TO '/u02/app/oracle/oradata/SAGAT/psindex_116.dbf';

sql "ALTER DATABASE RENAME FILE ''+DATA/saprod/onlinelog/group_1.650.649761829'' TO ''/u02/app/oracle/oradata/SAGAT/redo1A.log'' ";
sql "ALTER DATABASE RENAME FILE ''+FLASHG/saprod/onlinelog/group_1.289.649761831'' TO ''/u02/app/oracle/oradata/SAGAT/redo1B.log'' ";
sql "ALTER DATABASE RENAME FILE ''+DATA/saprod/onlinelog/group_2.649.649761837'' TO ''/u02/app/oracle/oradata/SAGAT/redo2A.log'' ";
sql "ALTER DATABASE RENAME FILE ''+FLASHG/saprod/onlinelog/group_2.288.649761839'' TO ''/u02/app/oracle/oradata/SAGAT/redo2B.log'' ";
sql "ALTER DATABASE RENAME FILE ''+DATA/saprod/onlinelog/group_3.742.649762129'' TO ''/u02/app/oracle/oradata/SAGAT/redo3A.log'' ";
sql "ALTER DATABASE RENAME FILE ''+FLASHG/saprod/onlinelog/group_3.287.649762131'' TO ''/u02/app/oracle/oradata/SAGAT/redo3B.log'' ";
sql "ALTER DATABASE RENAME FILE ''+DATA/saprod/onlinelog/group_4.741.649762135'' TO ''/u02/app/oracle/oradata/SAGAT/redo4A.log'' ";
sql "ALTER DATABASE RENAME FILE ''+FLASHG/saprod/onlinelog/group_4.286.649762137'' TO ''/u02/app/oracle/oradata/SAGAT/redo4B.log'' ";

set until time "to_date('05-08-2009 14:30:00','mm-dd-yyyy hh24:mi:ss')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

}
EOF


STEP 5: POST UPGRADE STEPS
=========================================
setup listener
set the db to noarhivelog

alter database disable thread 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
Posted by at No comments:
Subscribe to: Posts (Atom)