Oracle migration procedure sample scripts
The scripts presented are provided as examples of how to script various OS and database tasks. They are supplied as is. If support is required for a particular procedure, contact NetApp or a NetApp reseller.
Database shutdown
The following Perl script takes a single argument of the Oracle SID and shuts down a database. It can be run as the Oracle user or as root.
#! /usr/bin/perl use strict; use warnings; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; my @out; my $uid=$<; if ($uid == 0) { @out=`su - $oracleuser -c '. oraenv << EOF1 77 Migration of Oracle Databases to NetApp Storage Systems © 2021 NetApp, Inc. All rights reserved $oraclesid EOF1 sqlplus / as sysdba << EOF2 shutdown immediate; EOF2 ' `;} else { @out=`. oraenv << EOF1 $oraclesid EOF4 sqlplus / as sysdba << EOF2 shutdown immediate; EOF2 `;}; print @out; if ("@out" =~ /ORACLE instance shut down/) { print "$oraclesid shut down\n"; exit 0;} elsif ("@out" =~ /Connected to an idle instance/) { print "$oraclesid already shut down\n"; exit 0;} else { print "$oraclesid failed to shut down\n"; exit 1;}
Database startup
The following Perl script takes a single argument of the Oracle SID and shuts down a database. It can be run as the Oracle user or as root.
#! /usr/bin/perl use strict; use warnings; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; my @out; my $uid=$<; if ($uid == 0) { @out=`su - $oracleuser -c '. oraenv << EOF1 $oraclesid EOF1 sqlplus / as sysdba << EOF2 startup; EOF2 ' `;} else { @out=`. oraenv << EOF3 $oraclesid EOF1 sqlplus / as sysdba << EOF2 startup; EOF2 `;}; print @out; if ("@out" =~ /Database opened/) { print "$oraclesid started\n"; exit 0;} elsif ("@out" =~ /cannot start already-running ORACLE/) { print "$oraclesid already started\n"; exit 1;} else { 78 Migration of Oracle Databases to NetApp Storage Systems © 2021 NetApp, Inc. All rights reserved print "$oraclesid failed to start\n"; exit 1;}
Convert file system to read-only
The following script takes a file- system argument and attempts to dismount and remount it as read-only. Doing so is useful during migration processes in which a file system must be kept available to replicate data and yet must be protected against accidental damage.
#! /usr/bin/perl use strict; #use warnings; my $filesystem=$ARGV[0]; my @out=`umount '$filesystem'`; if ($? == 0) { print "$filesystem unmounted\n"; @out = `mount -o ro '$filesystem'`; if ($? == 0) { print "$filesystem mounted read-only\n"; exit 0;}} else { print "Unable to unmount $filesystem\n"; exit 1;} print @out;
Replace file system
The following script example is used to replace one file system with another. Because it edits the `/etc/fstab `file, it must run as root. It accepts a single comma-delimited argument of the old and new file systems.
-
To replace the file system, run the following script:
#! /usr/bin/perl use strict; #use warnings; my $oldfs; my $newfs; my @oldfstab; my @newfstab; my $source; my $mountpoint; my $leftover; my $oldfstabentry=''; my $newfstabentry=''; my $migratedfstabentry=''; ($oldfs, $newfs) = split (',',$ARGV[0]); open(my $filehandle, '<', '/etc/fstab') or die "Could not open /etc/fstab\n"; while (my $line = <$filehandle>) { chomp $line; ($source, $mountpoint, $leftover) = split(/[ , ]/,$line, 3); if ($mountpoint eq $oldfs) { $oldfstabentry = "#Removed by swap script $source $oldfs $leftover";} elsif ($mountpoint eq $newfs) { $newfstabentry = "#Removed by swap script $source $newfs $leftover"; $migratedfstabentry = "$source $oldfs $leftover";} else { push (@newfstab, "$line\n")}} 79 Migration of Oracle Databases to NetApp Storage Systems © 2021 NetApp, Inc. All rights reserved push (@newfstab, "$oldfstabentry\n"); push (@newfstab, "$newfstabentry\n"); push (@newfstab, "$migratedfstabentry\n"); close($filehandle); if ($oldfstabentry eq ''){ die "Could not find $oldfs in /etc/fstab\n";} if ($newfstabentry eq ''){ die "Could not find $newfs in /etc/fstab\n";} my @out=`umount '$newfs'`; if ($? == 0) { print "$newfs unmounted\n";} else { print "Unable to unmount $newfs\n"; exit 1;} @out=`umount '$oldfs'`; if ($? == 0) { print "$oldfs unmounted\n";} else { print "Unable to unmount $oldfs\n"; exit 1;} system("cp /etc/fstab /etc/fstab.bak"); open ($filehandle, ">", '/etc/fstab') or die "Could not open /etc/fstab for writing\n"; for my $line (@newfstab) { print $filehandle $line;} close($filehandle); @out=`mount '$oldfs'`; if ($? == 0) { print "Mounted updated $oldfs\n"; exit 0;} else{ print "Unable to mount updated $oldfs\n"; exit 1;} exit 0;
As an example of this script's use, assume that data in
/oradata
is migrated to/neworadata
and/logs
is migrated to/newlogs
. One of the simplest methods to perform this task is by using a simple file copy operation to relocate the new device back to the original mountpoint. -
Assume that the old and new file systems are present in the
/etc/fstab
file as follows:cluster01:/vol_oradata /oradata nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 cluster01:/vol_logs /logs nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 cluster01:/vol_neworadata /neworadata nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 cluster01:/vol_newlogs /newlogs nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0
-
When run, this script unmounts the current file system and replaces it with the new:
[root@jfsc3 scripts]# ./swap.fs.pl /oradata,/neworadata /neworadata unmounted /oradata unmounted Mounted updated /oradata [root@jfsc3 scripts]# ./swap.fs.pl /logs,/newlogs /newlogs unmounted /logs unmounted Mounted updated /logs
-
The script also updates the
/etc/fstab
file accordingly. In the example shown here, it includes the following changes:#Removed by swap script cluster01:/vol_oradata /oradata nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 #Removed by swap script cluster01:/vol_neworadata /neworadata nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 cluster01:/vol_neworadata /oradata nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 #Removed by swap script cluster01:/vol_logs /logs nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 #Removed by swap script cluster01:/vol_newlogs /newlogs nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0 cluster01:/vol_newlogs /logs nfs rw,bg,vers=3,rsize=65536,wsize=65536 0 0
Automated database migration
This example demonstrates the use of shutdown, startup, and file system replacement scripts to fully automate a migration.
#! /usr/bin/perl use strict; #use warnings; my $oraclesid=$ARGV[0]; my @oldfs; my @newfs; my $x=1; while ($x < scalar(@ARGV)) { ($oldfs[$x-1], $newfs[$x-1]) = split (',',$ARGV[$x]); $x+=1;} my @out=`./dbshut.pl '$oraclesid'`; print @out; if ($? ne 0) { print "Failed to shut down database\n"; exit 0;} $x=0; while ($x < scalar(@oldfs)) { my @out=`./mk.fs.readonly.pl '$oldfs[$x]'`; if ($? ne 0) { print "Failed to make filesystem $oldfs[$x] readonly\n"; exit 0;} $x+=1;} $x=0; while ($x < scalar(@oldfs)) { my @out=`rsync -rlpogt --stats --progress --exclude='.snapshot' '$oldfs[$x]/' '/$newfs[$x]/'`; print @out; if ($? ne 0) { print "Failed to copy filesystem $oldfs[$x] to $newfs[$x]\n"; exit 0;} else { print "Succesfully replicated filesystem $oldfs[$x] to $newfs[$x]\n";} $x+=1;} $x=0; while ($x < scalar(@oldfs)) { print "swap $x $oldfs[$x] $newfs[$x]\n"; my @out=`./swap.fs.pl '$oldfs[$x],$newfs[$x]'`; print @out; if ($? ne 0) { print "Failed to swap filesystem $oldfs[$x] for $newfs[$x]\n"; exit 1;} else { print "Swapped filesystem $oldfs[$x] for $newfs[$x]\n";} $x+=1;} my @out=`./dbstart.pl '$oraclesid'`; print @out;
Display file locations
This script collects a number of critical database parameters and prints them in an easy-to-read format. This script can be useful when reviewing data layouts. In addition, the script can be modified for other uses.
#! /usr/bin/perl #use strict; #use warnings; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; my @out; sub dosql{ my $command = @_[0]; my @lines; my $uid=$<; if ($uid == 0) { @lines=`su - $oracleuser -c "export ORAENV_ASK=NO;export ORACLE_SID=$oraclesid;. oraenv -s << EOF1 EOF1 sqlplus -S / as sysdba << EOF2 set heading off $command EOF2 " `;} else { $command=~s/\\\\\\/\\/g; @lines=`export ORAENV_ASK=NO;export ORACLE_SID=$oraclesid;. oraenv -s << EOF1 EOF1 sqlplus -S / as sysdba << EOF2 set heading off $command EOF2 `;}; return @lines} print "\n"; @out=dosql('select name from v\\\\\$datafile;'); print "$oraclesid datafiles:\n"; for $line (@out) { chomp($line); if (length($line)>0) {print "$line\n";}} print "\n"; @out=dosql('select member from v\\\\\$logfile;'); print "$oraclesid redo logs:\n"; for $line (@out) { chomp($line); if (length($line)>0) {print "$line\n";}} print "\n"; @out=dosql('select name from v\\\\\$tempfile;'); print "$oraclesid temp datafiles:\n"; for $line (@out) { chomp($line); if (length($line)>0) {print "$line\n";}} print "\n"; @out=dosql('show parameter spfile;'); print "$oraclesid spfile\n"; for $line (@out) { chomp($line); if (length($line)>0) {print "$line\n";}} print "\n"; @out=dosql('select name||\' \'||value from v\\\\\$parameter where isdefault=\'FALSE\';'); print "$oraclesid key parameters\n"; for $line (@out) { chomp($line); if ($line =~ /control_files/) {print "$line\n";} if ($line =~ /db_create/) {print "$line\n";} if ($line =~ /db_file_name_convert/) {print "$line\n";} if ($line =~ /log_archive_dest/) {print "$line\n";}} if ($line =~ /log_file_name_convert/) {print "$line\n";} if ($line =~ /pdb_file_name_convert/) {print "$line\n";} if ($line =~ /spfile/) {print "$line\n";} print "\n";
ASM migration cleanup
#! /usr/bin/perl #use strict; #use warnings; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; my @out; sub dosql{ my $command = @_[0]; my @lines; my $uid=$<; if ($uid == 0) { @lines=`su - $oracleuser -c "export ORAENV_ASK=NO;export ORACLE_SID=$oraclesid;. oraenv -s << EOF1 EOF1 sqlplus -S / as sysdba << EOF2 set heading off $command EOF2 " `;} else { $command=~s/\\\\\\/\\/g; @lines=`export ORAENV_ASK=NO;export ORACLE_SID=$oraclesid;. oraenv -s << EOF1 EOF1 sqlplus -S / as sysdba << EOF2 set heading off $command EOF2 `;} return @lines} print "\n"; @out=dosql('select name from v\\\\\$datafile;'); print @out; print "shutdown immediate;\n"; print "startup mount;\n"; print "\n"; for $line (@out) { if (length($line) > 1) { chomp($line); ($first, $second,$third,$fourth)=split('_',$line); $fourth =~ s/^TS-//; $newname=lc("$fourth.dbf"); $path2file=$line; $path2file=~ /(^.*.\/)/; print "host mv $line $1$newname\n";}} print "\n"; for $line (@out) { if (length($line) > 1) { chomp($line); ($first, $second,$third,$fourth)=split('_',$line); $fourth =~ s/^TS-//; $newname=lc("$fourth.dbf"); $path2file=$line; $path2file=~ /(^.*.\/)/; print "alter database rename file '$line' to '$1$newname';\n";}} print "alter database open;\n"; print "\n";
ASM to file system name conversion
set serveroutput on; set wrap off; declare cursor df is select file#, name from v$datafile; cursor tf is select file#, name from v$tempfile; cursor lf is select member from v$logfile; firstline boolean := true; begin dbms_output.put_line(CHR(13)); dbms_output.put_line('Parameters for log file conversion:'); dbms_output.put_line(CHR(13)); dbms_output.put('*.log_file_name_convert = '); for lfrec in lf loop if (firstline = true) then dbms_output.put('''' || lfrec.member || ''', '); dbms_output.put('''/NEW_PATH/' || regexp_replace(lfrec.member,'^.*./','') || ''''); else dbms_output.put(',''' || lfrec.member || ''', '); dbms_output.put('''/NEW_PATH/' || regexp_replace(lfrec.member,'^.*./','') || ''''); end if; firstline:=false; end loop; dbms_output.put_line(CHR(13)); dbms_output.put_line(CHR(13)); dbms_output.put_line('rman duplication script:'); dbms_output.put_line(CHR(13)); dbms_output.put_line('run'); dbms_output.put_line('{'); for dfrec in df loop dbms_output.put_line('set newname for datafile ' || dfrec.file# || ' to ''' || dfrec.name ||''';'); end loop; for tfrec in tf loop dbms_output.put_line('set newname for tempfile ' || tfrec.file# || ' to ''' || tfrec.name ||''';'); end loop; dbms_output.put_line('duplicate target database for standby backup location INSERT_PATH_HERE;'); dbms_output.put_line('}'); end; /
Replay logs on database
This script accepts a single argument of an Oracle SID for a database that is in mount mode and attempts to replay all currently available archive logs.
#! /usr/bin/perl use strict; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; 84 Migration of Oracle Databases to NetApp Storage Systems © 2021 NetApp, Inc. All rights reserved my $uid = $<; my @out; if ($uid == 0) { @out=`su - $oracleuser -c '. oraenv << EOF1 $oraclesid EOF1 sqlplus / as sysdba << EOF2 recover database until cancel; auto EOF2 ' `;} else { @out=`. oraenv << EOF1 $oraclesid EOF1 sqlplus / as sysdba << EOF2 recover database until cancel; auto EOF2 `; } print @out;
Replay logs on standby database
This script is identical to the preceding script, except that it is designed for a standby database.
#! /usr/bin/perl use strict; my $oraclesid=$ARGV[0]; my $oracleuser='oracle'; my $uid = $<; my @out; if ($uid == 0) { @out=`su - $oracleuser -c '. oraenv << EOF1 $oraclesid EOF1 sqlplus / as sysdba << EOF2 recover standby database until cancel; auto EOF2 ' `;} else { @out=`. oraenv << EOF1 $oraclesid EOF1 sqlplus / as sysdba << EOF2 recover standby database until cancel; auto EOF2 `; } print @out;