Madhu's Oracle Blog

Saturday, September 10, 2011

ADDING / RESIZING LOGS in DATAGUARD Environment

THINGS TO NOTE BEFORE DOING:
I did this on 10.2.0.4
Primary 2 node RAC
Standby 2 node RAC

The group number must be between 1 and the value of the MAXLOGFILES clause. Do not skip log file group numbers (that is, do not number groups 10, 20, 30, and so on), or you will use additional space in the standby database control file.

Although the STANDBY LOGs are only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.

You will need one more STANDBY LOGFILE group than the number of LOGILE groups on the primary database ( for each thread).

STEP 1: Check existing LOG files and STANDBY LOG files
do this on both PRIMARY and STANDBY dbs
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$LOG order by group#;
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$STANDBY_LOG order by group#;

STEP 2: Cancel Recovery
do this on standby db.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

STEP 3: Set STANDBY_FILE_MANAGEMENT to manual
do this on both PRIMARY and STANDBY dbs
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=both sid='*';

STEP 4: DELETE and ADD LOG and STANDBY LOG files
This is usual process
do this on both PRIMARY and STANDBY dbs
alter system switch logfile; -- this on primary to make the GROUP ready for deletion
ALTER DATABASE CLEAR LOGFILE GROUP 6; -- this on standby to make the GROUP ready for deletion
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DISKG1','+DISKG2') size 150M;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 07;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+DISKG1','+DISKG2') size 150M;

STEP 5: WINDUP and VERIFY
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$LOG order by group#; -- on both PRIMARY and STANDBY dbs
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$STANDBY_LOG order by group#; -- on both PRIMARY and STANDBY dbs
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*'; -- on both PRIMARY and STANDBY dbs
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- on STANDBY
alter system switch logfile; -- on PRIMARY and verify standby is recovering the new logs

STEP 6: Make sure Standby is not too far behind PRIMARY
do this on PRIMARY. This forces a logswitch every 1800 secs.
We needed this because during off-peak hours log switch is happing once in 10+ hours.
alter system set ARCHIVE_LAG_TARGET=1800 scope=both sid='*';

Sunday, March 27, 2011

Script to Generate AWR reports for all RAC nodes

-- This generates AWR reports from all the RAC nodes for the input start and end timings.
-- Works for 10gR2
--
-- How to execute
-- Save this script in AWR_gen_script.sql
-- sql> @AWR_gen_script.sql 20100915_1400 20100915_1500 html
-- Param 1 = start time YYYYMMDD_HH24MI format
-- Param 2 = end time YYYYMMDD_HH24MI format
-- Param 3 = html or text
--
-- All the AWR reports ( one from each node ) will be generated in output_dir variable. Defaults to c:\
-- This can be run from client or server. Just change the output_dir depending on OS. Window c:\ or Unix $HOME/
-- This create a temporary sql file AWR_run_script.sql in the current directory

SET FEED OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET ECHO OFF
SET LINESIZE 32767
SET PAGES 0
SET WRAP OFF
SET SCAN ON
SET TRIM ON
SET TRIMS ON
SET TAB OFF
SET SERVEROUTPUT ON
SET PAUSE OFF
SET TIMING OFF

spool AWR_run_script.sql

DECLARE

output_dir VARCHAR2(40) := 'c:\';
-- output must end with a slash

awr_format VARCHAR2(4);
begin_time VARCHAR2(40) ;
end_time VARCHAR2(40) ;
snap_begin_time VARCHAR2(40);
snap_end_time VARCHAR2(40);
snap_begin_snap number;
snap_end_snap number;
v_dbid number;
v_dbname VARCHAR(20);
v_extention varchar(10);

BEGIN

begin_time := '&1';
end_time := '&2';
awr_format := '&3';

select dbid, name into v_dbid, v_dbname from v$database;

if awr_format = 'text' then
v_extention := 'txt';
else
v_extention := awr_format;
end if;

for x in ( select instance_number, instance_name from gv$instance order by 1 ) loop

SELECT to_char(max(end_interval_time),'YYYYMMDD_HH24MI'), max(snap_id) INTO snap_begin_time, snap_begin_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') <= begin_time;

SELECT to_char(min(end_interval_time),'YYYYMMDD_HH24MI'), min(snap_id) INTO snap_end_time, snap_end_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') >= end_time;

DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE('spool ' || output_dir || 'AWR_' || x.instance_name ||'_'|| snap_begin_time ||'_to_' || snap_end_time ||'.' || v_extention );
DBMS_OUTPUT.PUT_LINE('SELECT output FROM TABLE (dbms_workload_repository.awr_report_' || awr_format || '(' || v_dbid||','||x.instance_number||','|| snap_begin_snap ||','|| snap_end_snap||'));');
DBMS_OUTPUT.PUT_LINE('spool off');

end loop;
DBMS_OUTPUT.PUT_LINE('exit ');

end;
/

spool off;
@AWR_run_script.sql;
exit

Script to Generate ADDM reports for all RAC nodes

-- This generates ADDM reports from all the nodes for the input start and end timings
-- Works for 10gR2
-- How to execute.
-- Save this script in ADDM_gen_script.sql
-- sql> @ADDM_gen_script.sql 20100915_1400 20100915_1500
-- Param 1 = start time YYYYMMDD_HH24MI format
-- Param 2 = end time YYYYMMDD_HH24MI format
--
-- All the ADDM reports ( one from each node ) will be created in output_dir variable. Defaults to c:\
-- This can be run from client or server. Just change the output_dir depending on OS. Window c:\ or Unix $HOME/
-- This create a temporary sql file ADDM_run_script.sql in the current directory

SET FEED OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET ECHO OFF
SET LINESIZE 32767
SET PAGES 0
SET WRAP OFF
SET SCAN ON
SET TRIM ON
SET TRIMS ON
SET TAB OFF
SET SERVEROUTPUT ON
SET PAUSE OFF
SET TIMING OFF

spool ADDM_run_script.sql
PROMPT SET FEED OFF
PROMPT SET VERIFY OFF
PROMPT SET HEADING OFF
PROMPT SET TERMOUT ON
PROMPT SET ECHO OFF
PROMPT SET LINESIZE 32767
PROMPT SET PAGES 0
PROMPT SET WRAP ON
PROMPT SET SCAN ON
PROMPT SET TRIM ON
PROMPT SET TRIMS ON
PROMPT SET TAB ON
PROMPT SET SERVEROUTPUT ON
PROMPT SET PAUSE ON
PROMPT SET TIMING OFF

DECLARE

-- output must end with a slash. CHANGE THIS IF NEEDED.
output_dir VARCHAR2(40) := 'c:\' ;

begin_time VARCHAR2(40);
end_time VARCHAR2(40);
snap_begin_time VARCHAR2(40);
snap_end_time VARCHAR2(40);
snap_begin_snap number;
snap_end_snap number;
v_dbid number;
v_dbname varchar(20);
v_instance number;

tid number; -- Task ID
tname varchar2(100); -- Task Name
tdesc varchar2(500); -- Task Description

BEGIN

begin_time := '&1';
end_time := '&2';
-- DBMS_OUTPUT.PUT_LINE('-- Begin Time = ' || begin_time);
-- DBMS_OUTPUT.PUT_LINE('-- End Time = ' || end_time);
select dbid,name into v_dbid, v_dbname from v$database;

for x in ( select instance_number, instance_name from gv$instance order by 1 ) loop

-- Get the snap id for the input begin_time
SELECT to_char(max(end_interval_time),'YYYYMMDD_HH24MI'), max(snap_id) INTO snap_begin_time, snap_begin_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') <= begin_time;

-- Get the snap id for the input end_time
SELECT to_char(min(end_interval_time),'YYYYMMDD_HH24MI'), min(snap_id) INTO snap_end_time, snap_end_snap
FROM DBA_HIST_SNAPSHOT
where instance_number= x.instance_number
and to_char(trunc(end_interval_time, 'MI'),'YYYYMMDD_HH24MI') >= end_time;

tname := 'ADDM:' || x.instance_name || '_' || snap_begin_snap || '_' || snap_end_snap;
tdesc := 'ADDM manual run: snapshots [' || snap_begin_snap || ',' || snap_end_snap || '], ' || x.instance_name;

DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'tid NUMBER; -- Task ID');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'tname VARCHAR2(30); -- Task Name');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'tdesc VARCHAR2(256); -- Task Description');

DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'tname := ''' || tname || ''' ;');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'tdesc := ''' || tdesc || ''';');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'DBMS_ADVISOR.CREATE_TASK( ''ADDM'', tid, tname, tdesc );');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'DBMS_ADVISOR.SET_TASK_PARAMETER( tname, ''START_SNAPSHOT'',' || snap_begin_snap || ' );' );
DBMS_OUTPUT.PUT_LINE(chr(9) || 'DBMS_ADVISOR.SET_TASK_PARAMETER( tname, ''END_SNAPSHOT'' ,' || snap_end_snap || ' );');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'dbms_advisor.set_task_parameter( tname, ''INSTANCE'',' || x.instance_number || ');');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'dbms_advisor.set_task_parameter( tname, ''DB_ID'',' || v_dbid || ');');
DBMS_OUTPUT.PUT_LINE(chr(9) || 'DBMS_ADVISOR.EXECUTE_TASK( tname );');

DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');

DBMS_OUTPUT.PUT_LINE('set long 1000000 pagesize 0 longchunksize 1000');
DBMS_OUTPUT.PUT_LINE('column get_clob format a80');
DBMS_OUTPUT.PUT_LINE('spool ' || output_dir || 'ADDM_' || x.instance_name ||'_'|| snap_begin_time ||'_to_' || snap_end_time ||'.txt' );
DBMS_OUTPUT.PUT_LINE('select dbms_advisor.get_task_report(''' || tname || ''', ''TEXT'', ''TYPICAL'') from sys.dual;');
DBMS_OUTPUT.PUT_LINE('spool off;');
DBMS_OUTPUT.PUT_LINE('exec DBMS_ADVISOR.DELETE_TASK('''|| tname ||''');');
DBMS_OUTPUT.PUT_LINE(chr(10));

end loop;

end;
/
spool off
@ADDM_run_script.sql
exit

Tuesday, January 18, 2011

RMAN Cloning (Refreshing) ASM/RAC database to ASM/RAC database

Assumptions:

Both dbs are on the same server
10.2.0.4
Source DB: SOURCE
Target DB: TARGET
Source Database can be in NOARCHIVE LOG MODE.
Target DB already exists (Okay if it doesn't. Just nomount the db )

============================================================================
STEP 1: COLD BACKUP SOURCE DATABASE.

srvctl stop database -d SOURCE

export ORACLE_SID=SOURCE1
export ORACLE_HOME=/u01/app/oracle/10gR2

rman msglog SOURCE_cold_backup.log << EOF
connect target /
startup mount;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
run {
allocate channel d1 type disk format '/u01/dumps/rman_SOURCE/%d_S_%s_P_%p_T_%t';
allocate channel d2 type disk format '/u01/dumps/rman_SOURCE/%d_S_%s_P_%p_T_%t';
allocate channel d3 type disk format '/u01/dumps/rman_SOURCE/%d_S_%s_P_%p_T_%t';
allocate channel d4 type disk format '/u01/dumps/rman_SOURCE/%d_S_%s_P_%p_T_%t';
allocate channel d5 type disk format '/u01/dumps/rman_SOURCE/%d_S_%s_P_%p_T_%t';
backup database tag=COLD_BACKUP;
}
EOF

srvctl start database -d SOURCE

============================================================================
STEP 2: TARGET DATABASE CLUSTER_DATABASE=FALSE

export ORACLE_SID=TARGET1
alter system set cluster_database=false scope=spfile sid='*';
srvctl stop database -d TARGET
startup nomount;

-- Delete datafiles in ASM diskgroup.
ASMCMD [+diskg/TARGET/datafile] > rm *

============================================================================
STEP 3: TARGET DATABASE CLUSTER_DATABASE=FALSE

startup nomount;

ORACLE_SID=TARGET1; export ORACLE_SID
rman msglog TARGET_clone.log << EOF
connect target sys/password@SOURCE1
connect auxiliary /
run
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel d1 device type disk;
allocate auxiliary channel d2 device type disk;
duplicate target database to TARGET;
}
EOF

============================================================================
STEP 4: TARGET DATABASE CLUSTER_DATABASE=TRUE

export ORACLE_SID=TARGET1
alter system set cluster_database=TRUE scope=spfile sid='*';
alter system set cluster_database_instances=2 scope=spfile sid='*';
shutdown immediate;
exit
10000 SCOPE=SPFILE sid='*';

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;
/

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;

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;