資料庫遷移至＋ＡＳＭ中

zhengbao_jun發表於2015-05-21
資料庫
環境描述
RAC使用的主機3臺 os version   redhat as 6.2
cluster和database version     11.2.0.3


一.使用os的udev命令標識asm使用的磁碟檔案
vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36005076802810c1a5800000000000008", NAME="asm_a1_ocr1",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36005076802810c1a5800000000000009", NAME="asm_a1_ocr2",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36005076802810c1a580000000000000a", NAME="asm_a1_ocr3",  OWNER="grid",  GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36005076802810c1a580000000000000c", NAME="asm_a1_disk1",  OWNER="grid",  GROUP="asmadmin", MODE="0660"


--重新啟動udev
/sbin/start_udev


二.建立磁碟組
    --以sysasm身份登陸建立磁碟組
    create diskgroup ocr external redundancy disk  '/dev/asm_a1_ocr1' attribute 'compatible.asm'='11.2';
    create diskgroup pay external redundancy disk  '/dev/asm_a1_disk1';
    --修改asm的屬性(如果建立磁碟組的時候未加asm屬性)
    alter diskgroup ocr set attribute 'COMPATIBLE.ASM'='11.2';
    --檢視磁碟組
    select name,total_mb,free_mb,COMPATIBILITY,state from  v$asm_diskgroup;


    --在cluster的所有節點上啟動建立的新磁碟組
    srvctl start diskgroup -g ocr 
    srvctl start diskgroup -g pay
    --檢查cluster資源狀態(以oracle使用者執行)
    crsctl status resource -t
    crsctl status resource -t


三.以root使用者換ocr
    --先增加一個映象磁碟組,然後刪除原來的磁碟組
    /oracle/11.2.0/grid/bin/ocrconfig -add +OCR
    /oracle/11.2.0/grid/bin/ocrconfig -delete +ocr_vote_pay1
    --檢查ocr
    /oracle/11.2.0/grid/bin/ocrcheck


四.以grid使用者換votedisk
    crsctl query css votedisk
    crsctl replace votedisk +OCR


五.更換asm例項的spfile
    --整個cluster最好只啟動一個節點,將其它節點的cluster關閉(以root使用者)
    /oracle/11.2.0/grid/bin/crsctl stop crs
    --更換asm例項的spfile
    create pfile='/tmp/initasm1.ora' from spfile;
    startup pfile='/oracle/11.2.0/grid/dbs/initasm1.ora'
    create spfile='+OCR' from pfile='/tmp/initasm1.ora';
    --重新啟動本節點的高可用服務
    /oracle/11.2.0/grid/bin/crsctl stop has
    /oracle/11.2.0/grid/bin/crsctl start has


/***
如果沒有關閉其他節點的cluster,那麼更新asm的spfile檔案時會報以下錯誤
SQL> create spfile='+OCR' from pfile='/oracle/11.2.0/grid/dbs/initasm.ora';
create spfile='+OCR' from pfile='/oracle/11.2.0/grid/dbs/initasm.ora'
*
ERROR at line 1:
ORA-29783: GPnP attribute SET failed with error [CLSGPNP_NOT_FOUND]


根據metalink解釋:此問題還有另外一種解決方法:就是殺死不健康的gpnpd.bin程式,然GI自動重啟動此程式.
***/


六.更換資料庫的spfile 
    --生成臨時的pfile檔案
    create pfile='/tmp/initpay.ora' from spfile;
    --在新的磁碟組上建立spfile檔案
    create spfile ='+PAY' from pfile='/tmp/initpay.ora';
    --修改cluster配置檔案中的引數檔案位置
    srvctl config database -d pay
    --spfile的具體位置可以使用asmcmd命令檢視
    srvctl modify database -d pay -p +PAY/PAY/PARAMETERFILE/spfile.288.824166281'


七.更換資料庫的磁碟組
--1.查詢表空間及其資料檔案
set linesize 300
col fname format A60
select ts.name tname,df.file#,df.name fname from v$tablespace ts,v$datafile df
 where ts.ts#=df.ts# order by 1;


--2.更換資料庫非系統使用的磁碟組(system/sysaux/undo)[更換時,資料庫可以是open狀態]
sql 'alter tablespace USERS offline';
backup device type disk as copy datafile 5 format '+PAY';
switch tablespace USERS  to copy;
sql 'alter tablespace USERS online';


--3.更換資料庫系統使用的磁碟組(system/sysaux/undo)[更換時,資料庫不允許為open狀態]
--先建立資料檔案映像副本
backup as copy tablespace system format '+PAY';
backup as copy tablespace sysaux format '+PAY';
backup as copy tablespace UNDOTBS1 format '+PAY';
backup as copy tablespace UNDOTBS2 format '+PAY';
backup as copy tablespace UNDOTBS3 format '+PAY';
--啟動資料庫到mount
shutdown immediate; 
startup mount; 
--切換表空間資料檔案到映像副本
switch tablespace system to copy; 
switch tablespace sysaux to copy; 
switch tablespace UNDOTBS1 to copy; 
switch tablespace UNDOTBS2 to copy; 
switch tablespace UNDOTBS3 to copy; 
--恢復資料庫
recover database;
--開啟資料庫
alter database open;


--4.更換臨時檔案
alter tablespace temp add tempfile '+PAY';
alter tablespace temp drop tempfile '+PAY/pay/tempfile/temp.274.824146081';


--5.更換redo log
--檢視重做日誌組和成員
select * from v$Logfile order by group#;
select * from v$Log;
--增加重做日誌成員
alter database add logfile member '+PAY' to group 1;
alter database add logfile member '+PAY' to group 2;
alter database add logfile member '+PAY' to group 3;
alter database add logfile member '+PAY' to group 4;
alter database add logfile member '+PAY' to group 5;
alter database add logfile member '+PAY' to group 6;
alter database add logfile member '+PAY' to group 7;
alter database add logfile member '+PAY' to group 8;
alter database add logfile member '+PAY' to group 9;
--刪除舊的重做日誌成員
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_7.263.820542373';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_2.262.820542367';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_1.261.820542365';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_5.267.820542451';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_6.268.820542453';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_9.269.820542459';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_3.270.820542463';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_4.271.820542465';
alter database drop logfile member '+PAY_TMP/pay/onlinelog/group_8.272.820542467';


--6.更換control file
--更新spfile中的control_file引數
alter system set control_files='+PAY_TMP/trade/controlfile/control01.ctl','+TRADE' scope=spfile sid='*';
--重新啟動資料庫
shutdown immediate;
startup nomount;
--使用rman建立新的控制檔案
RMAN> connect target /
RMAN> restore controlfile from '+PAY_TMP/trade/controlfile/control01.ctl';
--更新spfile中的control_file引數
alter system set control_files='+TRADE/trade/controlfile/current.275.824169817' scope=spfile sid='*';


八.下線舊的磁碟組
--1.舊的磁碟足離線
srvctl stop diskgroup -g PAY_TMP
跟蹤一段時間如果確實沒有問題再進行離線操作
--2.僅僅在一個asm例項上mount磁碟組
--mount要離線的磁碟組
alter diskgroup PAY_TMP mount;
--刪除磁碟組
drop diskgroup PAY_TMP including contents;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-1664892/，如需轉載，請註明出處，否則將追究法律責任。

相關文章