简体中文版经机器翻译而成,仅供参考。如与英语版出现任何冲突,应以英语版为准。
Oracle迁移操作步骤示例脚本
贡献者
建议更改
提供的脚本是如何为各种操作系统和数据库任务编写脚本的示例。它们按原样提供。如果特定操作步骤需要支持、请联系NetApp或NetApp经销商。
数据库关闭
以下Perl脚本仅使用Oracle SID的一个参数、并关闭数据库。它可以作为Oracle用户或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;}
数据库启动
以下Perl脚本仅使用Oracle SID的一个参数、并关闭数据库。它可以作为Oracle用户或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;}
将文件系统转换为只读
以下脚本采用文件系统参数、并尝试卸载它、然后将其重新挂载为只读。在迁移过程中、这样做非常有用、因为在迁移过程中、文件系统必须保持可用性以复制数据、同时必须防止意外损坏。
#! /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;
替换文件系统
以下脚本示例用于将一个文件系统替换为另一个文件系统。由于它会编辑`/etc/fstab `文件、因此必须以root用户身份运行。它接受新旧文件系统的一个逗号分隔参数。
-
要替换文件系统、请运行以下脚本:
#! /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;
作为此脚本用法的示例、假设中的数据
/oradata
将迁移到/neworadata
和/logs
将迁移到/newlogs
。执行此任务的最简单方法之一是、使用简单的文件复制操作将新设备重新定位回原始装载点。 -
假设中存在新旧文件系统
/etc/fstab
文件、如下所示: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
-
运行时、此脚本会卸载当前文件系统并将其替换为新的:
[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
-
该脚本还会更新
/etc/fstab
相应地归档。在此处所示的示例中、它包括以下更改:#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
自动化数据库迁移
此示例说明了如何使用关闭、启动和文件系统替换脚本来完全自动执行迁移。
#! /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;
显示文件位置
此脚本会收集大量关键数据库参数、并以易于阅读的格式打印这些参数。此脚本在查看数据布局时非常有用。此外、还可以修改此脚本以供其他用途。
#! /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迁移清理
#! /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到文件系统名称转换
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; /
重放数据库上的日志
此脚本接受处于挂载模式的数据库的Oracle SID的一个参数、并尝试重放所有当前可用的归档日志。
#! /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;
重放备用数据库上的日志
此脚本与前面的脚本相同、只是它是为备用数据库设计的。
#! /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;