1、一、 環境角色主機名IP數據文件版本數據庫名源庫db61192.168.2.61/63192.168.2.65ASM11.2.0.4erpdbdb62192.168.2.62/64目的庫db52192.168.2.52文件系統11.2.0.4erpdb檢查目的庫服務器的hosts文件、oracle用戶的bash_profile里的數據庫SIDoracledb52:/backup/rman$env |grep ORACLEORACLE_UNQNAME=erpdbORACLE_SID=erpdbORACLE_BASE=/oracle/app/oracleORACLE_TERM=xtermORACL

2、E_HOME=/oracle/app/oracle/product/11.2.0/db_1二、 RAC備份數據1. 關閉2個實例，都啟動到mount（非歸檔狀態）SQL> shutdown immediateSQL> startup mount設置RMAN自動備份控制文件RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;開啟備份優化功能RMAN> CONFIGURE BACKUP OPTIMIZATION ON;2. RAMN里備份數據庫RMAN> run2> 3> allocate channel d1 type d

3、isk;生產環境中，如果數據量較大，這里可以分配多個通道4> backup format '/backup/rman/erpdb_%U' database include current controlfile;5> release channel d1;6> allocated channel: d1channel d1: SID=1528 instance=erpdb1 device type=DISKStarting backup at 2019-09-26 04:52:24channel d1: starting full datafile backu

4、p setchannel d1: specifying datafile(s) in backup setinput datafile file number=00003 name=+DGSYSTEM/erpdb/datafile/undotbs1.264.1008033327input datafile file number=00001 name=+DGSYSTEM/erpdb/datafile/system.262.1008033311input datafile file number=00002 name=+DGSYSTEM/erpdb/datafile/sysaux.263.100

5、8033321input datafile file number=00004 name=+DGSYSTEM/erpdb/datafile/undotbs2.266.1008033337input datafile file number=00005 name=+DGSYSTEM/erpdb/datafile/users.267.1008033339input datafile file number=00006 name=+DGDATA01/erpdb/datafile/erpdata01.267.1008107239channel d1: starting piece 1 at 2019-

6、09-26 04:52:25channel d1: finished piece 1 at 2019-09-26 04:53:20piece handle=/backup/rman/erpdb_01ucn009_1_1 tag=TAG20190926T045225 comment=NONE這個備份片包含了全部的數據文件channel d1: backup set complete, elapsed time: 00:00:55channel d1: starting full datafile backup setchannel d1: specifying datafile(s) in ba

7、ckup setincluding current control file in backup setchannel d1: starting piece 1 at 2019-09-26 04:53:23channel d1: finished piece 1 at 2019-09-26 04:53:24piece handle=/backup/rman/erpdb_02ucn020_1_1 tag=TAG20190926T045225 comment=NONE這個備份片包含了控制文件channel d1: backup set complete, elapsed time: 00:00:0

8、1Finished backup at 2019-09-26 04:53:24Starting Control File and SPFILE Autobackup at 2019-09-26 04:53:24piece handle=+DGRECOVERY/erpdb/autobackup/2019_09_26/s_1019969023.257.1019969605這個備份片包含了控制文件和參數文件 comment=NONEFinished Control File and SPFILE Autobackup at 2019-09-26 04:53:25released channel: d

9、1拷貝參數文件到備份目錄griddb61:/home/grid$asmcmdASMCMD> cd DGRECOVERY/erpdb/autobackup/2019_09_26/ASMCMD> cp s_1019969023.257.1019969605 /tmp/oracledb61:/home/oracle$cp /tmp/ s_1019969023.257.1019969605 /backup/rman/3. 傳輸備份文件到目的庫服務器目的庫創建/backup/rman目錄這個目錄和源庫的目錄保持一致，這樣目的庫的目錄就不用注冊到控制文件、RMAN里默認就能識別這個目錄orac

10、ledb52:/home/oracle$cd /backup/oracledb52:/backup$mkdir rman從源庫把備份文件復制過去oracledb61:/backup/rman$scp erpdb_0* pfileerpdb.ora 192.168.2.52:/backup/rman/三、 目的庫的數據庫恢復1. 手工創建參數文件源庫創建pfile然后傳輸到目的庫oracledb61:/backup/rman$sqlplus / as sysdbaSQL> create pfile='/backup/rman/racpfile.ora' from spfil

11、e;oracledb61:/backup/rman$scp racpfile.ora 192.168.2.52:/backup/rman/singpfile.ora備庫修改參數文件oracledb52:/backup/rman$vi singpfile.ora刪除參數文件中跟集群相關的參數：erpdb2.*erpdb1.*.cluster_database=TRUE*.remote_listener=修改控制文件路徑：*.control_files='/oradata/erpdb/control01.ctl','/oradata/erpdb/control02.ctl&

12、#39;修改UNDO表空間名*.undo_tablespace='UNDOTBS1'提前創建好audit目錄*.audit_file_dest='/oracle/app/oracle/admin/erpdb/adump'沒有這個目錄的話，啟動到nomount就會提示ORA-09925: Unable to create audit trail file2. 創建密碼文件n 手動創建oracledb52:/backup/rman$cd $ORACLE_HOME/dbsoracledb52:/oracle/app/oracle/product/11.2.0/db_1

13、/dbs$orapwd file='orapwerpdb' password=oraclen 或者從源庫拷貝一個過來重命名oracledb61:/oracle/app/oracle/product/11.2.0/db_1/dbs$scp orapwerpdb1 192.168.2.52:$ORACLE_HOME/dbs/orapwerpdb3. 創建監聽文件oracledb52:/oracle/app/oracle/product/11.2.0/db_1/dbs$cd $ORACLE_HOME/network/adminoracledb52:/oracle/app/oracle

14、/product/11.2.0/db_1/network/admin$vi listener.ora增加如下NeriSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = erpdb) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1) (GLOBAL_DBNAME = fperpdb) ) )創建tnsnames.ora文件，可以從源庫復制oracledb52:/oracle/app/oracle/product/11.2.0/db_1/network/admin$vi tnsn

15、ames.oraERPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db52復制過來后，要更改里面的主機名)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = erpdb) ) )啟動監聽并測試成功了oracledb52:/oracle/app/oracle/product/11.2.0/db_1/network/admin$lsnrctl startoracledb52:/oracle/app/oracle/product/11.2.0/db_1/

16、network/admin$tnsping erpdb4. 用剛才修改過的pfile創建spfileoracledb52:/backup/rman$sqlplus / as sysdbaSQL> startup pfile='/backup/rman/singpfile.ora' nomount;SQL> create spfile from pfile='/backup/rman/singpfile.ora'會在/oracle/app/oracle/product/11.2.0/db_1/dbs創建一個spfilefgerpdb.ora參數文件重新

17、啟動數據庫，用spfile到nomount狀態SQL> shutdown immediateSQL> startup nomount;5. 恢復控制文件oracledb52:/backup/rman$rman target /RMAN> restore controlfile from '/backup/rman/erpdb_02ucn020_1_1'Starting restore at 25-SEP-19using target database control file instead of recovery catalogallocated chann

18、el: ORA_DISK_1channel ORA_DISK_1: SID=1521 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/oradata/erpdb/control01.ctloutput file name=/oradata/erpdb/control02.ctlFinished restore at 25-SEP-19啟動數據庫到mountRMAN>

19、alter database mount;database mountedreleased channel: ORA_DISK_1從控制文件讀取備份信息6. 恢復數據文件RMAN>runallocate channel d1 type disk;allocate channel d2 type disk;set newname for datafile 1 to '/oradata/erpdb/system.dbf'set newname for datafile 2 to '/oradata/erpdb/sysaux.dbf'set newname fo

20、r datafile 3 to '/oradata/erpdb/undotbs1.dbf'set newname for datafile 4 to '/oradata/erpdb/undotbs2.dbf'set newname for datafile 5 to '/oradata/erpdb/users.dbf'set newname for datafile 6 to '/oradata/erpdb/erpdata01.dbf'restore database;switch datafile all;recover dat

21、abase;release channel d1;release channel d2;released channel: ORA_DISK_1allocated channel: d1channel d1: SID=1521 device type=DISKallocated channel: d2channel d2: SID=10 device type=DISKexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET N

22、EWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 25-SEP-19channel d1: starting datafile backup set restorechannel d1: specifying datafile(s) to restore from backup setchannel d1: restoring datafile 00001 to /oradata/erpdb/system.dbfchannel d1: restoring datafile

23、00002 to /oradata/erpdb/sysaux.dbfchannel d1: restoring datafile 00003 to /oradata/erpdb/undotbs1.dbfchannel d1: restoring datafile 00004 to /oradata/erpdb/undotbs2.dbfchannel d1: restoring datafile 00005 to /oradata/erpdb/users.dbfchannel d1: restoring datafile 00006 to /oradata/erpdb/erpdata01.dbf

24、channel d1: reading from backup piece /backup/rman/erpdb_01ucn009_1_1channel d1: piece handle=/backup/rman/erpdb_01ucn009_1_1 tag=TAG20190926T045225channel d1: restored backup piece 1channel d1: restore complete, elapsed time: 00:00:45Finished restore at 25-SEP-19從備份片讀取原來的數據文件信息并還原到新的路徑datafile 1 sw

25、itched to datafile copyinput datafile copy RECID=7 STAMP=1019948035 file name=/oradata/erpdb/system.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=8 STAMP=1019948035 file name=/oradata/erpdb/sysaux.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=9 STAMP=1019948035

26、file name=/oradata/erpdb/undotbs1.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=10 STAMP=1019948035 file name=/oradata/erpdb/undotbs2.dbfdatafile 5 switched to datafile copyinput datafile copy RECID=11 STAMP=1019948035 file name=/oradata/erpdb/users.dbfdatafile 6 switched to dataf

27、ile copyinput datafile copy RECID=12 STAMP=1019948035 file name=/oradata/erpdb/erpdata01.dbf切換到數據文件copy的新路徑名稱Starting recover at 25-SEP-19starting media recoveryRMAN-08187: WARNING: media recovery until SCN 852916 completeFinished recover at 25-SEP-19released channel: d1released channel: d2四、 遷移之后的調

28、整與驗證1. redolog重命名oracledb52:/backup/rman$sqlplus / as sysdbaSQL> select status from v$instance;STATUS-MOUNTEDSQL> set pagesize 100SQL> select member from v$logfile;現在還是ASM的路徑信息MEMBER-+DGSYSTEM/erpdb/onlinelog/group_1.257.1008033287+DGDATA01/erpdb/onlinelog/group_1.257.1008033287+DGSYSTEM/er

29、pdb/onlinelog/group_2.258.1008033289+DGDATA01/erpdb/onlinelog/group_2.258.1008033291+DGSYSTEM/erpdb/onlinelog/group_5.259.1008033293+DGDATA01/erpdb/onlinelog/group_5.259.1008033293+DGSYSTEM/erpdb/onlinelog/group_7.260.1008033295+DGDATA01/erpdb/onlinelog/group_7.260.1008033299+DGSYSTEM/erpdb/onlinelo

30、g/group_9.261.1008033301+DGDATA01/erpdb/onlinelog/group_9.261.1008033305+DGDATA01/erpdb/onlinelog/group_3.262.1008036349+DGDATA01/erpdb/onlinelog/group_4.263.1008036355+DGDATA01/erpdb/onlinelog/group_6.264.1008036357+DGDATA01/erpdb/onlinelog/group_8.265.1008036359+DGDATA01/erpdb/onlinelog/group_10.2

31、66.100803636115 rows selected.給這些redolog日志文件進行重命名alter database rename file '+DGSYSTEM/erpdb/onlinelog/group_1.257.1008033287' to '/oradata/erpdb/redo11.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_1.257.1008033287' to '/oradata/erpdb/redo12.log'alt

32、er database rename file '+DGSYSTEM/erpdb/onlinelog/group_2.258.1008033289' to '/oradata/erpdb/redo21.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_2.258.1008033291' to '/oradata/erpdb/redo22.log'alter database rename file '+DGSYSTEM/erpdb/onlinel

33、og/group_5.259.1008033293' to '/oradata/erpdb/redo51.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_5.259.1008033293' to '/oradata/erpdb/redo52.log'alter database rename file '+DGSYSTEM/erpdb/onlinelog/group_7.260.1008033295' to '/oradata/erpd

34、b/redo71.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_7.260.1008033299' to '/oradata/erpdb/redo72.log'alter database rename file '+DGSYSTEM/erpdb/onlinelog/group_9.261.1008033301' to '/oradata/erpdb/redo91.log'alter database rename file '+DG

35、DATA01/erpdb/onlinelog/group_9.261.1008033305' to '/oradata/erpdb/redo92.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_3.262.1008036349' to '/oradata/erpdb/redo3.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_4.263.1008036355' to

36、 '/oradata/erpdb/redo4.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_6.264.1008036357' to '/oradata/erpdb/redo6.log'alter database rename file '+DGDATA01/erpdb/onlinelog/group_8.265.1008036359' to '/oradata/erpdb/redo8.log'alter database rena

37、me file '+DGDATA01/erpdb/onlinelog/group_10.266.1008036361' to '/oradata/erpdb/redo10.log'再次查詢一下redolog日志文件的路徑SQL> select member from v$logfile;MEMBER-/oradata/erpdb/redo11.log/oradata/erpdb/redo12.log/oradata/erpdb/redo21.log/oradata/erpdb/redo22.log/oradata/erpdb/redo51.log/orad

38、ata/erpdb/redo52.log/oradata/erpdb/redo71.log/oradata/erpdb/redo72.log/oradata/erpdb/redo91.log/oradata/erpdb/redo92.log/oradata/erpdb/redo3.log/oradata/erpdb/redo4.log/oradata/erpdb/redo6.log/oradata/erpdb/redo8.log/oradata/erpdb/redo10.log15 rows selected.打開數據庫SQL> alter database open resetlogs

39、;這個時候，alert日志里有很多報錯，比如無法打開臨時文件，因為還用的是ASM路徑2. redolog刪除多余線程和日志組SQL> select THREAD#,STATUS,ENABLED from v$thread; THREAD# STATUS ENABLED- - - 1 OPEN PUBLIC 2 CLOSED PRIVATE因為是單機數據庫，用不到多線程。線程2是關閉的，把這個線程禁用掉查看屬于線程2的日志組有哪些SQL> select group# from v$log where thread#=1; GROUP#- 3 4 6 8 10禁用線程2SQL>

40、alter database disable thread 1;刪除以下日志組SQL> alter database drop logfile group 1;SQL> alter database drop logfile group 2;SQL> alter database drop logfile group 3;SQL> alter database drop logfile group 4;SQL> alter database drop logfile group 5;再次查詢日志組成員SQL> select THREAD#,STATUS,EN

41、ABLED from v$thread; THREAD# STATUS ENABLED- - - 1 OPEN PUBLICSQL> select member from v$logfile;MEMBER-/oradata/erpdb/redo11.log/oradata/erpdb/redo12.log/oradata/erpdb/redo21.log/oradata/erpdb/redo22.log/oradata/erpdb/redo51.log/oradata/erpdb/redo52.log/oradata/erpdb/redo71.log/oradata/erpdb/redo72.log/oradata/erpdb/redo91.log/oradata/erpdb/redo92.log10 rows selected.3. 調整臨時表空間SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY'TABLESPA