Oracle存儲數(shù)據(jù)遷移方案v1

1、Oracle 11gR2 RAC ASM存儲數(shù)據(jù)遷移一、需求背景客戶數(shù)據(jù)庫存儲空間接近存滿，購置一臺新的存儲，需要進(jìn)行數(shù)據(jù)遷移，客戶允許少量停機(jī)時間二、實(shí)施方案討論1利用 ASM rebalance 進(jìn)行遷移可以實(shí)現(xiàn) 0 宕機(jī)進(jìn)行遷移，過程簡單，但遷移時間周期太長。2利用 rman copy 進(jìn)行遷移需要少量停機(jī)時間，遷移時間短，過程復(fù)雜。結(jié)論：根據(jù)客戶的需求，最終采用 rman copy 進(jìn)行存儲遷移。三、遷移實(shí)施步驟添加新 ASM磁盤備份集群及數(shù)據(jù)庫數(shù)據(jù)遷移刪除原存儲3.1 添加新 ASM 盤查詢原 ASM 磁盤信息SET PAGES 1000SET LINES 1000COL PATH

2、FOR A30SELECT A.GROUP_NUMBER,B.NAME ASM_NAME,A.NAME DISK_NAME,A.FAILGROUP,A.PATH,B.TYPE,A.TOTAL_MB,A.FREE_MBFROM V$ASM_DISK A, V$ASM_DISKGROUP BWHERE A.GROUP_NUMBER = B.GROUP_NUMBER;Eg: 當(dāng)前 ASM磁盤組狀態(tài)劃分磁盤（略）存儲工程師安照需求劃分存儲，并采用多路徑軟件。綁定 ASM 盤（略）根據(jù)不同的操作系統(tǒng)，使用不同的方法進(jìn)行綁定。Aixchdev -l hdisk* -a pv=clear（只對需要清除的 p

3、v 做此操作，注：做 ASM的磁盤是不能有 PVID的） 檢測和設(shè)置保持策略lsattr -E -l hdisk6 | grep reserve_lsattr -E -l hdisk7 | grep reserve_chdev -l hdisk6-7 -a reserve_policy=no_reserve 如（ 果是 reserve_policy）chdev -l hdisk6-7 -a reserve_lock=no 如（ 果是 reserve_lock）當(dāng) LUN 劃給操作后，我們用 /dev/rhdiskpowerXXX,賦予磁盤權(quán)限：chown grid:oinstall /dev/

4、rhdiskpower*chmod 660 /dev/rhdiskpower*4.1 備份 grid 集群備份包括 ocr 、votedisk備份 ocr 與 votediskocrconfig -manualbackup ocrvotedisk.bak -手工備份 （需 root 用戶） root 用戶導(dǎo)出/u01/app/11.2.0/grid/bin/ocrconfig -export ocrvoting0219.bak ocrconfig -showbackup-查看備份# ocrconfig -restore - 恢復(fù) ocr ocrconfig restore ocrvoting0

5、219.bak11G 中只需只需上面的命令，就能同時備份 ocr 與 vote5.1 數(shù)據(jù)遷移創(chuàng)建 ASM Diskgroup利用 asmca 命令創(chuàng)建以下 diskgroup ：CRSDG1 -存放 ocr 與 voteDATADG1-存放數(shù)據(jù)文件HISDG1 - - 存放歷史數(shù)據(jù)文件ARCHDG1-存放歸檔數(shù)據(jù)文件其中 crsdg 和 datadg 是正在使用的磁盤組； ocrvoting 和 demodatadg 是新添加的磁盤 組；遷移 ocr,vote添加 ocr 信息至 ocrdg 上( root 用戶)# ocrconfig -add +CRSDG1rootnode1 # /u0

6、1/app/11.2.0/grid/bin/ocrconfig -add +ocrvoting檢查 ocr# ocrcheckrootnode1 # /u01/app/11.2.0/grid/bin/ocrcheckStatus of Oracle Cluster Registry is as follows :Version : 3Total space (kbytes) : 262120Used space (kbytes) : 3132Available space (kbytes) : 258988ID : 54696065Device/File Name : +crsdgDevice

7、/File integrity check succeededDevice/File Name : +ocrvotingDevice/File integrity check succeededDevice/File not configuredDevice/File not configuredDevice/File not configuredCluster registry integrity check succeededLogical corruption check succeeded查看 votedisk 信息( grid 用戶)$ crsctl query css votedi

8、skgridnode1 $ crsctl query css votedisk# STATE File Universal IdFile Name Disk groupONLINE 6a0cbf6fdd394fd6bf097cceabec6083 (/dev/raw/raw1) CRSDGONLINE 6b16d31757564f02bf75a6366fafad52 (/dev/raw/raw2) CRSDGONLINE ac98fe0a715d4f37bf0fbf0f215db80f (/dev/raw/raw3) CRSDGLocated 3 voting disk(s).遷移 voted

9、isk ( grid 用戶) ( 后面步驟再做 )$ crsctl replace votedisk +CRSDG1gridnode1 $ crsctl replace votedisk +ocrvotingSuccessful addition of voting disk 22ecc96f37494f7cbf67b7098c7eb859.Successful addition of voting disk 8f6a5f4bdb094f1abfe9a88412252f6b.Successful addition of voting disk 74c4fe02aab14fe5bf6869d3f

10、dc97f38.Successful deletion of voting disk 6a0cbf6fdd394fd6bf097cceabec6083.Successful deletion of voting disk 6b16d31757564f02bf75a6366fafad52.Successful deletion of voting disk ac98fe0a715d4f37bf0fbf0f215db80f. Successfully replaced voting disk group with +ocrvoting.CRS-4266: Voting file(s) succes

11、sfully replacedgridnode1 $ crsctl query css votedisk# STATE File Universal IdFile Name Disk groupONLINE 22ecc96f37494f7cbf67b7098c7eb859 (/dev/raw/raw4) OCRVOTINGONLINE 8f6a5f4bdb094f1abfe9a88412252f6b (/dev/raw/raw5) OCRVOTINGONLINE 74c4fe02aab14fe5bf6869d3fdc97f38 (/dev/raw/raw6) OCRVOTING Located

12、 3 voting disk(s).創(chuàng)建 asm 實(shí)例 spfile 至 crsdg1 上SQL create pfile=/home/grid/asmspfile.ora from spfile;SQL create spfile=+CRSDG1 from pfile=/home/grid/asmspfile.ora; create spfile=+ocrvoting from pfile=/home/grid/asmspfile.ora;遷移數(shù)據(jù)庫文件至新磁盤上 創(chuàng)建數(shù)據(jù)庫 spfile至 newdata 上SQL create pfile=/home/oracle/orcl

13、_pfile.ora from spfile=+DATADG/orcl/spfileorcl.ora;SQL create spfile=+NEWDATA from pfile=/home/oracle/orcl_pfile.ora;create pfile=/home/oracle/initdemo.ora from spfile=+DATADG/demo/spfiledemo.ora; create spfile=+demodatadg from pfile=/home/oracle/initdemo.ora;修改$ORACLE_HOME/dbs參/ 數(shù)文件 啟動文件 查詢數(shù)

14、據(jù)文件信息SQL select |file_name| from dba_data_files;SQL select |file_name| from dba_data_files;|FILE_NAME|+DATADG/demo/datafile/system.268.1000595461+DATADG/demo/datafile/sysaux.269.1000595467+DATADG/demo/datafile/undotbs1.270.1000595471+DATADG/demo/datafile/undotbs2.272.1000595491+DATADG/demo/datafile/

15、users.273.1000595493SQL select |file_name| from dba_temp_files;|FILE_NAME|+DEMODATADG/demo/tempfile/user_temp.300.1000639535SQL 利用 rman copy備份數(shù)據(jù)庫$ vi rman_copy.shexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export ORACLE_SID=orcl1export PATH=$ORACLE_HOM

16、E/bin:$ORACLE_HOME/OPatch:$PATHrman target / nocatalog log=/tmp/rman_copy.logEOFrunallocate channel c1 type disk;allocate channel c2 type disk;copydatafile +DATADG/orcl/datafile/system.259.874699969to +NEWDATA,datafile +DATADG/orcl/datafile/sysaux.260.874699995to +NEWDATA,datafile +DATADG/orcl/dataf

17、ile/undotbs1.261.874700013 to +NEWDATA, datafile +DATADG/orcl/datafile/undotbs2.263.874700041 to +NEWDATA,to +NEWDATA;datafile +DATADG/orcl/datafile/users.264.874700049 EOFrman target / nocatalog log=/tmp/rman_copy.log append EOFcopy current controlfile to +NEWDATA;EOF注：需要開啟歸檔模式alter system set log_

18、archive_dest_1=LOCATION=+DATADG scope=spfile; shutdown immediate;start mount;alter database archivelog;alter database open;vi rman_copy.shORACLE_SID=demo1;export ORACLE_SID ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1;export ORACLE_HOME PATH=.:$JAVA_

19、HOME/bin:$PATH:$HOME/bin:$ORACLE_HOME/bin rman target / nocatalog log=/home/oracle/rman_copy.logEOF runallocate channel c1 type disk;allocate channel c2 type disk;copydatafile +DATADG/demo/datafile/system.268.1000595461to +demodatadg,datafile +DATADG/demo/datafile/sysaux.269.1000595467to +demodatadg

20、,datafile +DATADG/demo/datafile/undotbs1.270.1000595471 to +demodatadg, datafile +DATADG/demo/datafile/undotbs2.272.1000595491 to +demodatadg, datafile +DATADG/demo/datafile/users.273.1000595493to +demodatadg;EOFrman target / nocatalog log=/home/oracle/rman_copy.log append cd +newdata/orcl/CONTROLFI

21、LE ASMCMD lsBackup.261.960651757ASMCMD cp Backup.261.960651757 control01.dbfASMCMD cp Backup.261.960651757 control02.dbfASMCMD lsBackup.257.1000634979Backup.263.1000635675ASMCMD pwd +DEMODATADG/demo/CONTROLFILEASMCMDcp Backup. 263.1000635675 control01.dbfcp Backup.263.1000635675 control02.dbf 選擇高版本的

22、 control修改 spfile參數(shù)#control_files ：SQL alter system set control_files=+newdata/orcl/CONTROLFILE/control01.dbf,+newdata/orcl/CONTROLFILE/control02.dbf scope=spfile;#db_create_file_destSQL alter system set db_create_file_dest=+NEWDATA SCOPE=SPFILE;#archive：SQL alter system set log_archive_dest_1=LOCAT

23、ION=+NEWDATAalter system set control_files=+DEMODATADG/demo/CONTROLFILE/control01.dbf,+DEMODATADG/demo/CONTROLFILE/control02.dbf scope=spfile;alter system set db_create_file_dest=+DEMODATADG SCOPE=SPFILE;alter system set log_archive_dest_1=LOCATION=+DEMODATADG; 利用集群命令，更改數(shù)據(jù)庫狀態(tài)（ oracle用戶）停止數(shù)據(jù)庫$

24、 srvctl stop database -d orcl啟動至 mount 狀態(tài)$ srvctl start database -d orcl -o mountSQL show parameter control_filesrename數(shù)據(jù)庫數(shù)據(jù)文件SQL alter database rename file +DATADG/orcl/datafile/sysaux.260.874699995 to +newdata/orcl/datafile/SYSAUX.258.960651703;SQL alter database rename file +DATADG/orcl/da

25、tafile/system.259.874699969 to +newdata/orcl/datafile/SYSTEM.256.960651621;SQL alter database rename file +DATADG/orcl/datafile/undotbs1.261.874700013 to +newdata/orcl/datafile/UNDOTBS1.257.960651621;SQL alter database rename file +DATADG/orcl/datafile/undotbs2.263.874700041 to +newdata/orcl/datafil

26、e/UNDOTBS2.259.960651707;SQL alter database rename file +DATADG/orcl/datafile/users.264.874700049 to +newdata/orcl/datafile/USERS.260.960651735;temp 文件SQL alter database rename file +DATADG/orcl/tempfile/temp.262.874700019 to +newdata;to +DEMODATADG/demo/datafile/SYSTEM.258.1000635661;to +DEMODATADG

27、/demo/datafile/SYSAUX.259.1000635661;to +DEMODATADG/demo/datafile/UNDOTBS1.260.1000635667 to +DEMODATADG/demo/datafile/UNDOTBS2.261.1000635669 to +DEMODATADG/demo/datafile/USERS.262.1000635671;alter database rename file +DATADG/demo/datafile/system.268.1000595461alter database rename file +DATADG/de

28、mo/datafile/sysaux.269.1000595467 alter database rename file +DATADG/demo/datafile/undotbs1.270.1000595471 alter database rename file +DATADG/demo/datafile/undotbs2.272.1000595491 alter database rename file +DATADG/demo/datafile/users.273.1000595493 注意：臨時文件沒有復(fù)制過去alter database rename file +DATADG/or

29、cl/tempfile/TEMP.271.1000595475 to +demodatadg;create temporary tablespace user_temp tempfile +demodatadg size 50m autoextend on;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE user_temp;DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;recover 數(shù)據(jù)庫$ rman target /RMAN recov

30、er database; 以 open resetlogs方式打開數(shù)據(jù)庫SQL alter database open resetlogs;0 修改 redo 日志-增加 redo 日志組alter database add logfile thread 1 group 5 +Newdata size 50m; alter database add logfile thread 1 group 6 +Newdata size 50m; alter database add logfile thread 2 group 7 +Newdata size 50m; alt

31、er database add logfile thread 2 group 8 +Newdata size 50m;alter database add logfile thread 1 group 5 +demodatadg size 50m; alter database add logfile thread 1 group 6 +demodatadg size 50m; alter database add logfile thread 2 group 7 +demodatadg size 50m; alter database add logfile thread 2 group 8

32、 +demodatadg size 50m; -刪除 redo 日志組 select GROUP#,status from v$log;當(dāng) status 狀態(tài)為 inactive 和 unused 狀態(tài)時可以刪除。alter database drop logfile group 4;alter database drop logfile group 2;alter database drop logfile group 1;alter database drop logfile group 3;1 集群修改信息$ srvctl modify database -d orcl -

33、n orcl -p +NEWDATA/orcl/spfileorcl.ora$ srvctl modify database -d orcl -n orcl -a NEWDATA 查看集群配置srvctl config database -d demo 修改數(shù)據(jù)庫集群中資源配置srvctl modify database -d demo -a DEMODATADGroot 用戶 更改：用 srvctl 修改新的 rac 數(shù)據(jù)庫 spfile 位置/u01/app/11.2.0/grid/bin/srvctlmodify database -d demo -p +demodatadg/demo/

34、spfiledemo.ora2 以集群方式啟動數(shù)據(jù)庫$ srvctl stop database -d orcl$ srvctl start database -d orcl6.1 刪除原存儲-刪除原 OCR,vote 磁盤組# ocrconfig -delete +CRSDG# ocrcheckrootnode1 # /u01/app/11.2.0/grid/bin/ocrconfig -delete +crsdg rootnode1 # /u01/app/11.2.0/grid/bin/ocrcheckStatus of Oracle Cluster Registry is as follows :Version: 3Total space (kbytes) : Used space (kbytes) : Available space (kbytes) :2621203132258988ID: 54696065Device/File Name: +ocrvotingDevice/File integrity check succeededDevice/File not configuredDevice/File not configuredDevice/File not configuredDevice/F