#Oracle RMANでバックアップしたものを使って全損からリストアする
Oracleが全損、もしくは他サーバに同じOracleを建てたい時の備忘録。
順番は空でも覚えていたけどやり方はすっかり忘れていたのでメモしとかないダメですね。
全損から戻せればあとはきっとどんな状況でも対応できる・・・はず・・・！
概要
環境は以下のとおり
OS : Oracle Linux 5.4
Oracle Version : 11.2.0.1.0
インスタンス名 : PROD1
データファイルを置いてるディレクトリ：/u01/app/oracle/oradata/prod1
高速リカバリ領域：/u01/app/oracle/flash_recovery_area
バックアップするディレクトリ : /backup
アーカイブログモード：YES!!
rmanを使ってバックアップファイル、アーカイブログ、redoログを駆使して戻すことを想定して実施します。
バックアップ手順
データファイルと同時にアーカイブログと制御ファイルも一緒にオンラインでバックアップを取得する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 11 17:26:55 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD1 (DBID=2038747369) # /backupを指定して保存します。取得した後は余分なアーカイブログは削除します RMAN> backup database format '/backup/data_%d_%U.bak.%T' include current controlfile plus archivelog delete all input format '/backup/archivelog_%d_%U.bak.%T'; Starting backup at 11-OCT-15 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=64 STAMP=892845306 channel ORA_DISK_1: starting piece 1 at 11-OCT-15 channel ORA_DISK_1: finished piece 1 at 11-OCT-15 piece handle=/backup/archivelog_PROD1_1pqjff7q_1_1.bak.20151011 tag=TAG20151011T203506 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2015_10_11/o1_mf_1_3_c1nlcbjz_.arc RECID=64 STAMP=892845306 Finished backup at 11-OCT-15 Starting backup at 11-OCT-15 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/prod1/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/prod1/sysaux01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/prod1/undotbs01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/prod1/users01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/tcts.dbf channel ORA_DISK_1: starting piece 1 at 11-OCT-15 channel ORA_DISK_1: finished piece 1 at 11-OCT-15 piece handle=/backup/data_PROD1_1qqjff7s_1_1.bak.20151011 tag=TAG20151011T203508 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 11-OCT-15 channel ORA_DISK_1: finished piece 1 at 11-OCT-15 piece handle=/backup/data_PROD1_1rqjff8b_1_1.bak.20151011 tag=TAG20151011T203508 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 11-OCT-15 Starting backup at 11-OCT-15 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=65 STAMP=892845325 channel ORA_DISK_1: starting piece 1 at 11-OCT-15 channel ORA_DISK_1: finished piece 1 at 11-OCT-15 piece handle=/backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 tag=TAG20151011T203526 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2015_10_11/o1_mf_1_4_c1nlcxyp_.arc RECID=65 STAMP=892845325 Finished backup at 11-OCT-15 |
バックアップの確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
RMAN> list backup; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 50 6.50K DISK 00:00:00 11-OCT-15 BP Key: 54 Status: AVAILABLE Compressed: NO Tag: TAG20151011T203506 Piece Name: /backup/archivelog_PROD1_1pqjff7q_1_1.bak.20151011 List of Archived Logs in backup set 50 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 783810 11-OCT-15 783945 11-OCT-15 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 51 Full 454.67M DISK 00:00:07 11-OCT-15 BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20151011T203508 Piece Name: /backup/data_PROD1_1qqjff7s_1_1.bak.20151011 List of Datafiles in backup set 51 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 783953 11-OCT-15 /u01/app/oracle/oradata/prod1/system01.dbf 2 Full 783953 11-OCT-15 /u01/app/oracle/oradata/prod1/sysaux01.dbf 3 Full 783953 11-OCT-15 /u01/app/oracle/oradata/prod1/undotbs01.dbf 4 Full 783953 11-OCT-15 /u01/app/oracle/oradata/prod1/users01.dbf 5 Full 783953 11-OCT-15 /u01/app/oracle/oradata/orcl/tcts.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 52 Full 9.73M DISK 00:00:01 11-OCT-15 BP Key: 56 Status: AVAILABLE Compressed: NO Tag: TAG20151011T203508 Piece Name: /backup/data_PROD1_1rqjff8b_1_1.bak.20151011 SPFILE Included: Modification time: 11-OCT-15 SPFILE db_unique_name: PROD1 Control File Included: Ckp SCN: 783963 Ckp time: 11-OCT-15 ## ↑ここにSPFILEと制御ファイルが入ったバックアップがあります！！メモしておきましょう！ BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 53 6.00K DISK 00:00:00 11-OCT-15 BP Key: 57 Status: AVAILABLE Compressed: NO Tag: TAG20151011T203526 Piece Name: /backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 List of Archived Logs in backup set 53 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 4 783945 11-OCT-15 783969 11-OCT-15 |
次にバックアップ後にデータを更新しておく。
まずは現在のredoログを確認する。現在はsequence 5に書き込んでいる模様
|
1 2 3 4 5 6 7 8 |
$ sqlplus / as sysdba SYS@prod1> select GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log; GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ---------------- ------------- ------------ 1 4 ACTIVE 783945 783969 2 5 CURRENT 783969 2.8147E+14 3 3 INACTIVE 783810 783945 |
データを書き込む
|
1 2 |
SYS@prod1> insert into dip.hoge values (1); SYS@prod1> commit; |
redoログが1週するまでアーカイブログを出力する。5は無事上書きされていきました。
|
1 2 3 4 5 6 7 8 9 10 |
SYS@prod1> alter system archive log current; SYS@prod1> alter system archive log current; SYS@prod1> alter system archive log current; SYS@prod1> select GROUP#,SEQUENCE#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log; GROUP# SEQUENCE# STATUS FIRST_CHANGE# NEXT_CHANGE# ---------- ---------- ---------------- ------------- ------------ 1 7 ACTIVE 784116 784136 2 8 CURRENT 784136 2.8147E+14 3 6 ACTIVE 784110 784116 |
改めて、現在のREDOログ（sequence#8)にもデータを残しておく
|
1 2 |
SYS@prod1> insert into dip.hoge values (2); SYS@prod1> commit; |
リストア手順
別サーバ、もしくは新規にOracleをインストールしなおしたサーバなどにバックアップを転送する。
|
1 2 |
$ cp data_* /backup $ cp archivelog_* /backup |
バックアップ後に出力したアーカイブログも同じbackupディレクトリにおいておく
|
1 |
$ cp *arc /backup |
データファイルのあったディレクトリや高速リカバリ領域のディレクトリを作成しておく
|
1 2 |
$ mkdir /u01/app/oracle/oradata/prod1 $ mkdir /u01/app/oracle/flash_recovery_area |
redoログもあるなら元サーバから元の位置にコピーしておく(稼働してる途中にこぴってくるでもOKだった）
|
1 |
cp redo*log /u01/app/oracle/oradata/prod1 |
パスワードファイルを作成、もしくは復元する。作成の場合は以下のとおり。
|
1 2 |
$ cd $ORACLE_HOME/dbs $ orapwd file=orapwprod1 |
制御ファイルをとった時についでにspfileもバックアップされているのでそれを使ってspfileを復元し、nomountで立ち上げる。
別途spfileのバックアップを取っており、すぐ復元できるならそちらを使うでもOK
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
$ echo 'db_name=PROD1' > $ORACLE_HOME/dbs/initprod1.ora $ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 11 20:48:05 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 217157632 bytes Fixed Size 2211928 bytes Variable Size 159387560 bytes Database Buffers 50331648 bytes Redo Buffers 5226496 bytes RMAN> RESTORE SPFILE FROM '/backup/data_PROD1_1rqjff8b_1_1.bak.20151011'; Starting restore at 11-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/data_PROD1_1rqjff8b_1_1.bak.20151011 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 11-OCT-15 RMAN> shutdown immediate Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 417546240 bytes Fixed Size 2213936 bytes Variable Size 322963408 bytes Database Buffers 88080384 bytes Redo Buffers 4288512 bytes |
制御ファイルを復元してマウントする
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
RMAN> RESTORE CONTROLFILE FROM '/backup/data_PROD1_1rqjff8b_1_1.bak.20151011'; Starting restore at 11-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/prod1/control01.ctl output file name=/u01/app/oracle/oradata/prod1/control02.ctl output file name=/u01/app/oracle/oradata/prod1/control03.ctl Finished restore at 11-OCT-15 RMAN> ALTER DATABASE MOUNT; database mounted released channel: ORA_DISK_1 |
バックアップの対象となるファイルを登録。最後にも/をいれるのがコツ。
これをすることでバックアップ後にとったアーカイブログなどもrmanでリカバリの時に自動的に見てくれるようになる。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
RMAN> CATALOG START WITH '/backup/' NOPROMPT; Starting implicit crosscheck backup at 11-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 11-OCT-15 Starting implicit crosscheck copy at 11-OCT-15 using channel ORA_DISK_1 Finished implicit crosscheck copy at 11-OCT-15 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /backup/ List of Files Unknown to the Database ===================================== File Name: /backup/data_PROD1_1rqjff8b_1_1.bak.20151011 File Name: /backup/o1_mf_1_7_c1nlok3j_.arc File Name: /backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 File Name: /backup/o1_mf_1_6_c1nlndsd_.arc File Name: /backup/o1_mf_1_5_c1nln4mr_.arc cataloging files... cataloging done List of Cataloged Files ======================= File Name: /backup/data_PROD1_1rqjff8b_1_1.bak.20151011 File Name: /backup/o1_mf_1_7_c1nlok3j_.arc File Name: /backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 File Name: /backup/o1_mf_1_6_c1nlndsd_.arc File Name: /backup/o1_mf_1_5_c1nln4mr_.arc |
データをリストア/リカバリする
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
RMAN> RESTORE DATABASE; RMAN> RECOVER DATABASE; Starting recover at 11-OCT-15 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 5 is already on disk as file /backup/o1_mf_1_5_c1nln4mr_.arc archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/oradata/prod1/redo03a.log archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/oradata/prod1/redo01a.log archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/oradata/prod1/redo02a.log channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=4 channel ORA_DISK_1: reading from backup piece /backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 channel ORA_DISK_1: piece handle=/backup/archivelog_PROD1_1sqjff8e_1_1.bak.20151011 tag=TAG20151011T203526 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2015_10_11/o1_mf_1_4_c1nmcg00_.arc thread=1 sequence=4 channel default: deleting archived log(s) archived log file name=/u01/app/oracle/flash_recovery_area/PROD1/archivelog/2015_10_11/o1_mf_1_4_c1nmcg00_.arc RECID=74 STAMP=892846334 archived log file name=/backup/o1_mf_1_5_c1nln4mr_.arc thread=1 sequence=5 archived log file name=/u01/app/oracle/oradata/prod1/redo03a.log thread=1 sequence=6 archived log file name=/u01/app/oracle/oradata/prod1/redo01a.log thread=1 sequence=7 archived log file name=/u01/app/oracle/oradata/prod1/redo02a.log thread=1 sequence=8 media recovery complete, elapsed time: 00:00:00 Finished recover at 11-OCT-15 |
※番外編：sequenceの指定してリカバリ
アーカイブログしかなかったり、中途半端なredoログしかなければsequenceを指定してリカバリすることも可能
|
1 2 3 4 5 |
RMAN> list archivelog all; # これで登録されているアーカイブログの一覧確認 RMAN> run{ set until sequence = 7; recover database; } |
ちなみに他にも時間での指定とかも出来ます
※番外編ここまで
バックアップの制御ファイルから戻したのでredoログを作りなおしてopenする
|
1 2 3 |
RMAN> ALTER DATABASE OPEN RESETLOGS; database opened |
データがきちんと戻っているのを確認する
|
1 2 3 4 5 6 7 |
$ sqlplus / as sysdba SYS@prod1> select * from dip.hoge; ID ---------- 1 2 |
これでいざというときになんとか復元できるはずっ！！
