[DB2]離線備份與離線還原
所謂離線備份，就是在所有使用者斷開連結的情況下，對資料進行的備份。
本例中，操作的資料庫是示例資料sample。
1、確認日誌歸檔功能開啟：
1.1、 檢視資料的配置引數LOGARCHMETH1，發現歸檔日誌未開啟：
db2 => get db cfg
。。。。。。。。。。。。。。。。
First log archive method (LOGARCHMETH1) = OFF
。。。。。。。。。。。。。。。。
1.2、 修改LOGARCHMETH1引數，讓資料處於歸檔模式：
db2 =>update db cfg for sample using LOGARCHMETH1 logretain
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
2、修改完上述引數後，資料庫會處於BACKUP PENDING狀態，需要我們進行一次備份。執行backup命令即可：
db2 => connect to sample
SQL1116N A connection to or activation of database "SAMPLE" cannot be made
because of BACKUP PENDING. SQLSTATE=57019
db2 => backup database sample to /home/db2inst1/bak_sample
Backup successful. The timestamp for this backup image is : 20100804192910
3、檢視備份檔案的資訊：
[db2inst1@localhost ~]$ db2ckbkp -h /home/db2inst1/bak_sample/SAMPLE.0.db2inst1.NODE0000.CATN0000.20100804192910.001
=====================
MEDIA HEADER REACHED:
=====================
Server Database Name -- SAMPLE
Server Database Alias -- SAMPLE
Client Database Alias -- SAMPLE
Timestamp -- 20100804192910
Database Partition Number -- 0
Instance -- db2inst1
Sequence Number -- 1
Release ID -- D00
Database Seed -- 82D84DC2
DB Comment's Codepage (Volume) -- 0
DB Comment (Volume) --
DB Comment's Codepage (System) -- 0
DB Comment (System) --
Authentication Value -- -1
Backup Mode -- 0
Includes Logs -- 0
Compression -- 0
Backup Type -- 0
Backup Gran. -- 0
Status Flags -- 15
System Cats inc -- 1
Catalog Partition Number -- 0
DB Codeset -- UTF-8
DB Territory --
LogID -- 1279630639
LogPath -- /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Backup Buffer Size -- 4460544
Number of Sessions -- 1
Platform -- 12
The proper image file name would be:
SAMPLE.0.db2inst1.NODE0000.CATN0000.20100804192910.001
[1] Buffers processed: #################################
4、我們在某個表空間上新建一張表，然後進行離線備份，之後再向該表中插入資料，讓日誌中記錄有事務操作。最後，模擬一次故障，將表空間所對應的容器刪除。
4．1、首先檢視下，sample的表空間
db2 => list tablespaces
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
4.2、檢視2號表空間USERSPACE1所對應的容器：
db2 => list tablespace containers for 2 show detail
Tablespace Containers for Tablespace 2
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG
Type = File
Total pages = 4096
Useable pages = 4064
Accessible = Yes
4.3、現在我們在表空間上，新建一張表，並且插入一條資料：
db2 => create table newtable(aa int) in userspace1
DB20000I The SQL command completed successfully.
db2 => insert into newtable values(123)
DB20000I The SQL command completed successfully.
db2 => commit
DB20000I The SQL command completed successfully.
db2 => select * from newtable
AA
-----------
123
1 record(s) selected.
4.4、下面，我再進行一次離線備份，此次備份是距離資料庫出現故障前的一次備份：
db2 => force applications all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
db2 => backup database sample to /home/db2inst1/bak_sample
Backup successful. The timestamp for this backup image is : 20100804193718
4.5、連線上資料庫，在新建的表中插入三條記錄：
db2 => connect to sample
Database Connection Information
Database server = DB2/LINUX 9.7.1
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2 => insert into newtable values(222)
DB20000I The SQL command completed successfully.
db2 => insert into newtable values(333)
DB20000I The SQL command completed successfully.
db2 => insert into newtable values(444)
DB20000I The SQL command completed successfully.
db2 => commit
DB20000I The SQL command completed successfully.
4.6、現在將2號表空間所對應的資料檔案（容器），手動刪除，模擬硬碟故障。再去檢視錶空間的容器時，發現已經不可訪問了（Accessible已經變成了No）：
[db2inst1@localhost ~]$ rm -f /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG
db2 => list tablespace containers for 2 show detail
Tablespace Containers for Tablespace 2
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/SAMPLE/T0000002/C0000000.LRG
Type = File
Total pages = 4096
Useable pages = 4064
Accessible = No
5、資料庫還原
還原分為兩個步驟：restore（將資料庫恢復到上次備份的時刻）和roll forward（使用事務日誌前滾到失敗點那一刻或使用者指定的時刻）。這兩個步驟，類似於oracle中的restore和recovery。
5.1 首先檢視下備份的資訊，確認能夠restore遭到破壞的表空間
db2 => list history backup all for db sample
List History File for sample
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20100804120733001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
00004 SYSTOOLSPACE
00005 IBMDB2SAMPLEXML
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100804120733
End Time: 20100804120743
Status: A
----------------------------------------------------------------------------
EID: 4 Location: /home/db2inst1/bak_sample
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20100804144846001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
00004 SYSTOOLSPACE
00005 IBMDB2SAMPLEXML
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE OFFLINE
Start Time: 20100804144846
End Time: 20100804144856
Status: A
----------------------------------------------------------------------------
EID: 7 Location: /home/db2inst1/bak_sample
5.2 使用restore命令，進行恢復
5.2.1首次使用restore命令時，提示狀態不正確：
db2 => restore database sample from /home/db2inst1/bak_sample
SQL1350N The application is not in the correct state to process this request.
Reason code="1".
檢視一下，出錯的原因，發現是需要執行commit或rollback後，才可以執行restore工具：
db2 => ? SQL1350N
SQL1350N The application is not in the correct state to process this
request. Reason code="
Explanation:
Corresponding to "
01 The application is currently processing SQL and cannot process
the utility command requested.
User response:
Corresponding to "
01 Complete the unit of work (using either COMMIT or ROLLBACK)
before reissuing this command.
5.2.2執行commit命令，並且再次執行2.1中的命令，又提示無法進行操作（提示有多個備份檔案可供使用）：
db2 => commit
DB20000I The SQL command completed successfully.
db2 => restore database sample from /home/db2inst1/bak_sample
SQL2522N More than one backup file matches the timestamp value provided for
the backed up database image.
5.2.3 現在我們使用taken at子句來指定restore命令使用的備份檔案，taken at 後是需要接一個timestamp的，這個可以從備份檔名中提取出來，請關注下面的檔名中，標紅的部分：
[db2inst1@localhost bak_sample]$ ll
total 270384
-rw------- 1 db2inst1 db2iadm1 138297344 Aug 4 12:07 SAMPLE.0.db2inst1.NODE0000.CATN0000. 20100804192910.001
-rw------- 1 db2inst1 db2iadm1 138297344 Aug 4 14:48 SAMPLE.0.db2inst1.NODE0000.CATN0000. 20100804193718.001
db2 => restore database sample from /home/db2inst1/bak_sample taken at 20100804193718
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
5.2.4 經過以上步驟，終於將資料庫restore成功，但是此時連線不上資料庫，因為還需要進行前滾：
db2 => connect to sample
SQL1117N A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
6 進行前滾
db2 => rollforward database sample to end of logs and stop
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG - S0000001.LOG
Last committed transaction = 2010-08-04-11.39.27.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
發現使用了S0000001.LOG日誌，來進行rollforward。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14600958/viewspace-670309/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- db2離線全備份DB2
- oracle備份--離線備份Oracle
- 【原】Oracle學習系列—資料庫備份—離線備份Oracle資料庫
- Oracle 聯機備份 離線備份 物理備份 恢復Oracle
- 揭祕ORACLE備份之－－冷備份（也叫離線備份）Oracle
- 轉：DB2離線和線上全備、增量備份及恢復的操作步驟DB2
- 使用離線工具dmbackup進行資料庫備份資料庫
- Openshift 4.4 靜態 IP 離線安裝系列：準備離線資源
- 完整恢復模式下主檔案組離線還原模式
- 表空間離線與資料檔案離線的區別
- mysql 備份與還原MySql
- SQL學習-資料庫的備份和還原 分離和附加SQL資料庫
- [原] jQuery EasyUI 1.3.4 離線API、Demo (最新)jQueryUIAPI
- 印表機離線了怎麼連線 印表機離線的連線方法
- DM7使用離線工具DMRMAN執行資料庫備份資料庫
- 離線快取快取
- 離線查詢與線上查詢
- 印表機離線如何處理 印表機離線的方法
- Docker 離線安裝 & 離線私有倉庫搭建總結Docker
- 印表機離線了怎麼重新連線 印表機離線重新連線的方法
- Ubuntu Desktop: 備份與還原Ubuntu
- oracle資料還原與備份Oracle
- DB2 V9聯機備份還原（七）DB2
- DB2 V9聯機備份還原（六）DB2
- DB2 V9聯機備份還原（五）DB2
- DB2 V9聯機備份還原（四）DB2
- DB2 V9聯機備份還原（三）DB2
- DB2 V9聯機備份還原（二）DB2
- DB2 V9聯機備份還原（一）DB2
- 離線安裝 dockerDocker
- web離線應用Web
- 離線安裝dockerDocker
- 印表機離線怎麼回事 印表機離線的解決方法
- 【Mongodb】資料庫備份與還原MongoDB資料庫
- MySQL的資料備份與還原MySql
- SVN程式碼管理 備份與還原
- MySQL資料庫備份與還原MySql資料庫
- mysql備份還原MySql