DB2資料庫的備份測試--備份環境變數配置

anycall2010發表於2009-10-06
DB2資料庫變數

1、 在/tmp目錄下建立 /tmp/db2inst1/sample/下建立以下目錄：

archivelog : 用來存放第一條歸檔路徑；

archivelog1：用於存放第二條歸檔路徑；

mirrorlog： 用於存放映象聯機日誌；

on_back：存放線上熱備份集；

off_abck：存放冷備份集；

[db2inst1@db2 ~]$ cd /tmp

[db2inst1@db2 tmp]$ mkdir db2inst1

[db2inst1@db2 tmp]$ cd db2inst1

[db2inst1@db2 db2inst1]$ ls

[db2inst1@db2 db2inst1]$ mkdir sample

[db2inst1@db2 db2inst1]$ cd sample

[db2inst1@db2 sample]$ mkdir mirrorlog

[db2inst1@db2 sample]$ mkdir archivelog

[db2inst1@db2 sample]$ mkdir archivelog1

[db2inst1@db2 sample]$ mkdir on_back

[db2inst1@db2 sample]$ mkdir off_back

 

2、 建立toolsdb實現備份排程

 

[root@db2 instance]# su - db2inst1

[db2inst1@db2 ~]$ db2 create tools catalog systools create new db toolsdb

DB20000I  The CREATE TOOLS CATALOG command completed successfully.

 

3、 開歸檔日誌

[db2inst1@db2 sample]$ db2 update db cfg for sample using logretain on

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

 

3.1、重新啟動使之生效

    [db2inst1@db2 sample]$ db2stop force

09/17/2009 04:29:01     0   0   SQL1064N  DB2STOP processing was successful.

SQL1064N  DB2STOP processing was successful.

[db2inst1@db2 sample]$ db2start

09/17/2009 04:29:08     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

 

FAQ：

如果繼續配置引數，會報如下錯誤，由於資料庫無法被開啟：

[db2inst1@db2 /]$ db2 update db cfg for sample using trackmod on

SQL1224N  The database manager is not able to accept new requests, has

terminated all requests in progress, or has terminated your particular request

due to a problem with your request.  SQLSTATE=55032

 

進入資料庫，會出現如下報錯：

db2 => connect to sample

SQL1116N  A connection to or activation of database "SAMPLE" cannot be made

because of BACKUP PENDING.  SQLSTATE=57019

 

解決辦法：

資料庫修改引數後，需要進行一次全備份，因此進行全備份就可以了。

[db2inst1@db2 /]$ cd /home/db2inst1

[db2inst1@db2 ~]$ ls

db2inst1  sqllib

[db2inst1@db2 ~]$ mkdir off_back

 

[db2inst1@db2 ~]$ db2 backup db sample to /home/db2inst1/off_back

 

  Backup successful. The timestamp for this backup image is : 20090917043704

 

   [db2inst1@db2 ~]$ db2 connect to sample

 

       Database Connection Information

 

     Database server        = DB2/LINUX 9.1.3

     SQL authorization ID   = DB2INST1

     Local database alias   = SAMPLE

 

4、 映象日誌：

[db2inst1@db2 mirrorlog]$ db2 update db cfg for sample using mirrorlogpath /tmp/db2inst1/sample/mirrorlog/

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

 

5、 開啟增量備份

  [db2inst1@db2 mirrorlog]$ db2 update db cfg for sample using trackmod on

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

SQL1363W One or more of the parameters submitted for immediate modification

were not changed dynamically. For these configuration parameters, all

applications must disconnect from this database before the changes become

effective.

 

6、 指定第一條歸檔路徑：

  [db2inst1@db2 archivelog1]$ db2 update db cfg for sample using logarchmeth1 disk:/tmp/db2inst1/sample/archivelog/

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

FAQ：當歸檔路徑配置後，需要重啟資料庫才能做第二個歸檔路徑，否則會報錯：

    [db2inst1@db2 archivelog]$ db2 update db cfg for sample using logarchmeth2 disk:/tmp/db2inst1/archivelog1/

SQL5099N  The value "/tmp/db2inst1/archivelog1/" indicated by the database

configuration parameter "LOGARCHMETH2" is not valid, reason code "2". 

SQLSTATE=08004

解決辦法：重啟資料庫

[db2inst1@db2 archivelog]$ db2stop force

09/17/2009 04:47:34     0   0   SQL1064N  DB2STOP processing was successful.

SQL1064N  DB2STOP processing was successful.

[db2inst1@db2 archivelog]$ db2start

09/17/2009 04:47:40     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

 

7、 指定第二條歸檔路徑

   [db2inst1@db2 archivelog]$ db2 update db cfg for sample using logarchmeth2 disk:/tmp/db2inst1/sample/archivelog1/

DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

 

8、 察看最終配置

    [db2inst1@db2 sample]$ db2 get db cfg for sample

      ...............

    

Path to log files                                       = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

 Overflow log path                     (OVERFLOWLOGPATH) =

 Mirror log path                         (MIRRORLOGPATH) = /tmp/db2inst1/sample/mirrorlog/NODE0000/

 First active log file                                   = S0000000.LOG

 

 First log archive method                 (LOGARCHMETH1) = DISK:/tmp/db2inst1/sample/archivelog/

 Options for logarchmeth1                  (LOGARCHOPT1) =

 Second log archive method                (LOGARCHMETH2) = DISK:/tmp/db2inst1/sample/archivelog1/

 

 

 

 

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

相關文章