Page last modified 16:59, 3 Feb 2015 by alang

User:alang > IT 專案工作 > Oracle DBA 筆記 > RMAN - Backup/Recovery Tool > Migrate DB with RMAN

Migrate DB with RMAN

內容表格

1. 說明
2. 備份從 DB-1
3. 還原到 DB-2
1. 3.1. 準備 RHEL 環境
2. 3.2. 備份檔
3. 3.3. 開始回復
4. [optional] 如何補上自前次完整備份後產生的 Archive Log
1. 4.1. 方法一：回復時用 recover
2. 4.2. 方法二：回復時用 restore
5. FAQ
1. 5.1. RMAN-06556: datafile 1 must be restored from backup older than scn 18438912
6. 參考網站

說明

有一台 Production DB-1，要以最短的 downtime，順利的轉移到另一台 DB-2。

備份從 DB-1

su - oracle
sqlplus / as sysdba
SQL> create pfile='/opt/oracle/oradata/orabak/win/pfilewin.ora' from spfile='/opt/oracle/product/10.2.0.5.4/db_1/dbs/spfilewin.ora';
SQL> alter system archive log current;
SQL> exit

rman target / nocatalog

RMAN> configure controlfile autobackup format for device type disk to '/opt/oracle/oradata/orabak/win/rmandata/%F';
RMAN> configure controlfile autobackup on;
RMAN> configure channel device type disk format = '/opt/oracle/oradata/orabak/win/rmandata/%U';
RMAN> crosscheck archivelog all;
RMAN> delete noprompt force expired archivelog all;
RMAN> backup database plus archivelog delete input;
RMAN> configure controlfile autobackup off;

RMAN> list backup;
...
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    6.98M      DISK        00:00:00     25-MAR-14
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20140325T181523
        Piece Name: /opt/oracle/oradata/orabak/win/rmandata/0rp433j1_1_1
  Control File Included: Ckp SCN: 18439027     Ckp time: 25-MAR-14
  SPFILE Included: Modification time: 24-MAR-14
....

RMAN> exit

cd /oracle/oradata/orabak/win/rmandata/
mv 0rp433j1_1_1 cf_0rp433j1_1_1

TIPs:

pfile 備份路徑需要修改

RMAN 備份路徑需要修改

幾個相關指令必須加上 delete，否則已備份過的 Archive log，下次仍會重新再備份。

備份檔 c-.... 這是 control file 備份檔

還原到 DB-2

準備 RHEL 環境

以 Oracle Home Clone 方式建立一個全新的 Oracle
不需要建立任何的 DB
確定環境變數是正確的，SID 必須與備份DB相同

備份檔

需要的備份檔有兩種：pfile 與 RMAN，分別複製到路徑

/opt/oracle/win_ora_backup/

開始回復

ORACLE_SID=win
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0.5.4/db_1

編輯 pfile：

SID 必須與備份DB相同
修改 Archive log 的儲存路徑
db_files 自行調整（預設是 200）

*.audit_file_dest='/opt/oracle/admin/win/adump'
*.background_dump_dest='/opt/oracle/admin/win/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/win/control01.ctl','/opt/oracle/oradata/win/control02.ctl','/opt/oracle/oradata/win/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/win/cdump'
*.db_16k_cache_size=16777216
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_file_multiblock_read_count=16
*.db_name='win'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=winXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/opt/oracle/arclog'
*.log_archive_format='win_%s_%t_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=536870912
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/win/udump'

建立 spfile
注意：路徑必須是 $ORACLE_HOME/dbs，檔名必須是 spfile<SID>.ora

sqlplus / as sysdba

SQL> create spfile='/opt/oracle/product/10.2.0.5.4/db_1/dbs/spfilewin.ora' from pfile='/opt/oracle/win_ora_backup/pfilewin.ora';
SQL> exit;

建立目錄

su - oracle
mkdir -p /opt/oracle/admin/win2/{a,b,c,u}dump
mkdir -p /opt/oracle/oradata/win2
mkdir -p /opt/oracle/flash_recovery_area/WIN2 
mkdir /opt/oracle/arclog

RMAN: 回復 control file

回復 control file 時，將路徑指向備份檔 c-........

rman target / nocatalog

RMAN> startup nomount
RMAN> restore controlfile from '/opt/oracle/win_ora_backup/rmandata/cf_17p4a9le_1_1';
RMAN> alter database mount;
RMAN> exit

找出備份檔的最後一次 Archive log 紀錄 SCN#

RMAN> list backup of archivelog all;

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
32      49.50K     DISK        00:00:01     28-MAR-14
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20140328T112713
        Piece Name: /opt/oracle/oradata/orabak/win/rmandata/10p4a8kh_1_1

  List of Archived Logs in backup set 32
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2470    18669840   28-MAR-14 18669945   28-MAR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
33      298.00K    DISK        00:00:01     28-MAR-14
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20140328T113520
        Piece Name: /opt/oracle/oradata/orabak/win/rmandata/11p4a93o_1_1

  List of Archived Logs in backup set 33
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2471    18669945   28-MAR-14 18670539   28-MAR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
36      43.50K     DISK        00:00:01     28-MAR-14
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20140328T113820
        Piece Name: /opt/oracle/oradata/orabak/win/rmandata/14p4a99c_1_1

  List of Archived Logs in backup set 36
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2472    18670539   28-MAR-14 18670638   28-MAR-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
37      38.00K     DISK        00:00:01     28-MAR-14
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20140328T114159
        Piece Name: /opt/oracle/oradata/orabak/win/rmandata/15p4a9g7_1_1

  List of Archived Logs in backup set 37
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2473    18670638   28-MAR-14 18670699   28-MAR-14
  1    2474    18670699   28-MAR-14 18670780   28-MAR-14    <=====

或者也可使用 SQL

sqlplus / as sysdba

SQL> select group#, first_change#, status, archived from v$log; 


    GROUP# FIRST_CHANGE# STATUS           ARC
---------- ------------- ---------------- ---
         1      18670699 INACTIVE         YES
         3      18670638 INACTIVE         YES
         2      18670780 CURRENT          NO         <=====

NOTE: 記住 18670780，待會需要用到。

RMAN: 用 CATALOG 讓系統知道備份檔路徑
NOTE: 備份檔目錄不要放其他舊版備份或不相關的檔案，否則 CATALOG 時可能會被引用。

RMAN> catalog start with '/opt/oracle/win_ora_backup/rmandata';

Starting implicit crosscheck backup at 28-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 28-MAR-14

Starting implicit crosscheck copy at 28-MAR-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-MAR-14

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /opt/oracle/win_ora_backup/rmandata

List of Files Unknown to the Database
=====================================
File Name: /opt/oracle/win_ora_backup/rmandata/cf_17p4a9le_1_1
File Name: /opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1
File Name: /opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/oracle/win_ora_backup/rmandata/cf_17p4a9le_1_1
File Name: /opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1
File Name: /opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1

RMAN: 作一次 CROSSCHECK

RMAN> CROSSCHECK backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/0up4a8ev_1_1 recid=20 stamp=843391455
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/0vp4a8ke_1_1 recid=21 stamp=843391632
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/10p4a8kh_1_1 recid=22 stamp=843391634
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/11p4a93o_1_1 recid=23 stamp=843392121
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/12p4a93q_1_1 recid=24 stamp=843392122
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/13p4a999_1_1 recid=25 stamp=843392299
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/14p4a99c_1_1 recid=26 stamp=843392301
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/15p4a9g7_1_1 recid=27 stamp=843392520
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/16p4a9g9_1_1 recid=28 stamp=843392521
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1 recid=30 stamp=843409037
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/win_ora_backup/rmandata/cf_17p4a9le_1_1 recid=29 stamp=843409037
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1 recid=31 stamp=843409037
Crosschecked 12 objects

RMAN> CROSSCHECK backup of database;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/0up4a8ev_1_1 recid=20 stamp=843391455
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/12p4a93q_1_1 recid=24 stamp=843392122
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/16p4a9g9_1_1 recid=28 stamp=843392521
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1 recid=30 stamp=843409037
Crosschecked 4 objects


RMAN> CROSSCHECK backup of controlfile;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/0vp4a8ke_1_1 recid=21 stamp=843391632
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/oradata/orabak/win/rmandata/13p4a999_1_1 recid=25 stamp=843392299
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/win_ora_backup/rmandata/cf_17p4a9le_1_1 recid=29 stamp=843409037
Crosschecked 3 objects

RMAN> CROSSCHECK archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
specification does not match any archive log in the recovery catalog

[optional] ]如果 datafile 的路徑與備份 DB 不同，必須執行以下步驟：
否則跳過

RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/<old>/redo01.log’ to ‘/opt/oracle/oradata/<new>/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/<old>/redo02.log’ to ‘/opt/oracle/oradata/<new>/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/<old>/redo03.log’ to ‘/opt/oracle/oradata/<new>/redo03.log’;

[optional]如果有使用 BLOCK CHANGE TRACKING，必須執行這些步驟：
否則跳過

RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;

TIPs:

This will avoid errors like ORA-19751 and ORA-19750

RMAN: 回復資料庫
[optional ]如果 datafile 路徑有變更，必須加上這幾行在前面
否則跳過

RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/<new>/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/<new>/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/<new>/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/<new>/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/<new>/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/<new>/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/<new>/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/<new>/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/<new>/index02.dbf”;
}

TIP:

如果有執行 rename，在 restore database 後，需要執行

RMAN> switch datafile all;

開始回復
回復 datafiles

RMAN> restore database;

Starting restore at 28-MAR-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/win/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/win/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/win/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/win/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/win/example01.dbf
restoring datafile 00006 to /opt/oracle/oradata/win/CDB.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/win_ora_backup/rmandata/16p4a9g9_1_1 tag=TAG20140328T114201
channel ORA_DISK_1: restore complete, elapsed time: 00:02:47
Finished restore at 28-MAR-14

TIPs:

restore database preview; 可以先預覽結果

回復 Archive Logs

RMAN> recover database;

Starting recover at 28-MAR-14
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2475
channel ORA_DISK_1: reading from backup piece /opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1 tag=TAG20140328T114449
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/opt/oracle/arclog/win_2475_1_827060640.arc thread=1 sequence=2475
unable to find archive log
archive log thread=1 sequence=2476
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/28/2014 16:23:01
RMAN-06054: media recovery requesting unknown log: thread 1 seq 2476 lowscn 18670902

RMAN> recover database until sequence 2476;

Starting recover at 28-MAR-14
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2475
channel ORA_DISK_1: reading from backup piece /opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/win_ora_backup/rmandata/18p4a9lh_1_1 tag=TAG20140328T114449
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/opt/oracle/arclog/win_2475_1_827060640.arc thread=1 sequence=2475
media recovery complete, elapsed time: 00:00:01
Finished recover at 28-MAR-14

確定所有的 Archive logs 都補上，就可以用以下指令重建 redo log 並啟動 DB。
NOTE: 一旦 resetlogs 後，就無法在補舊 DB 的 Archive Logs

RMAN> alter database open resetlogs;
建立 redo logs 並開啟 DB

[optional] 如何補上自前次完整備份後產生的 Archive Log

這技巧可用於 Production DB 需要在不同機器上做移轉時使用，此法可大大降低移轉的 downtime。

注意：要用此法，DB 在做回復時，不可以執行最後階段 'resetlogs'。

方法一：回復時用 recover

從 DB-1 備份最新 archive logs (自上次 DB 做完完整備份之後)

RMAN > backup archivelog all delete input format '/opt/oracle/oradata/orabak/win/backup_arc/arc_20140328-1729';

將備份檔 arc_20140328-1729 複製到 DB-2 的目錄 /opt/oracle/win_ora_backup/backup_arc/

在 DB-2 執行復原 Archive log

RMAN> catalog start with '/opt/oracle/win_ora_backup/backup_arc';

RMAN> list backup of archivelog all;
...
...
  List of Archived Logs in backup set 41
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2476    18670902   28-MAR-14 18682143   28-MAR-14
  1    2477    18682143   28-MAR-14 18683392   28-MAR-14
  1    2478    18683392   28-MAR-14 18683480   28-MAR-14  <====


RMAN> recover database until sequence 2478;

Starting recover at 28-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2476
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2477
channel ORA_DISK_1: reading from backup piece /opt/oracle/win_ora_backup/backup_arc/arc_20140328-1729
channel ORA_DISK_1: restored backup piece 1
piece handle=/opt/oracle/win_ora_backup/backup_arc/arc_20140328-1729 tag=TAG20140328T173021
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/opt/oracle/arclog/win_2476_1_827060640.arc thread=1 sequence=2476
archive log filename=/opt/oracle/arclog/win_2477_1_827060640.arc thread=1 sequence=2477
media recovery complete, elapsed time: 00:00:05
Finished recover at 28-MAR-14

TIPs:

ArchiveLog 在做完復原後，可以檢查ArchiveLog 的儲存目錄 /opt/oracle/arclog 是否有新增對應的 log 檔。

方法二：回復時用 restore

RMAN> run {
RMAN> set archivelog destination to '/opt/oracle/arclog';
RMAN> restore archivelog from logseq=210 until logseq=212;
RMAN> }

FAQ

RMAN-06556: datafile 1 must be restored from backup older than scn 18438912

在 recover databae 時出現的錯誤訊息

Ans：在回復 Control file 之後，執行

RMAN> list backup of archivelog all;
...
...
  List of Archived Logs in backup set 37
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2473    18670638   28-MAR-14 18670699   28-MAR-14
  1    2474    18670699   28-MAR-14 18670780   28-MAR-14    <=====

RMAN> restore database until sequence 2475;
仍然不行

RMAN> restore database until sequence 2476;
可以了

參考網站

標籤 (Edit tags)

No tags

什麼連接到這裡

文件 0

附加檔案或者圖像

Images 0
No images to display in the gallery.

您必須登入才能發佈評論。