重建 Datapump Utility EXPDP/IMPDP

发表于 2012 年 4 月 23 日 由 惜分飞

因为数据库内部错误,数据字典不一致等原因导致 DataPump不能被正常使用,这个时候可以尝试着通过重建 DataPump来解决问题
sysdba登录数据库

SQL> connect / as sysdba

For Oracle version 10.1

1. Catdp.sql orders the installation of all its components including 
   the Metadata API which was previously installed separately.
   By default catproc.sql invoke this script.
SQL >@ $ORACLE_HOME/rdbms/admin/catdp.sql

2. dbmspump.sql will create DBMS procedures for dataPUMP
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql

For Oracle version 10.2

1. Catdph.sql will Re-Install DataPump types and views
SQL >@ $ORACLE_HOME/rdbms/admin/catdph.sql 

Use this code to verify if XDB is installed:
SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
     comp_id,substr(version,1,12) version,status 
     from dba_registry where comp_id='XDB';

Sample output if XDB installed,
Oracle XML Database            XDB        -version-   VALID

Note: If XDB is installed, then it is required to run "catmetx.sql" script also.
SQL> @ $ORACLE_HOME/rdbms/admin/catmetx.sql


2. prvtdtde.plb will Re-Install tde_library packages
SQL >@ $ORACLE_HOME/rdbms/admin/prvtdtde.plb 


3. Catdpb.sql will Re-Install DataPump packages
SQL >@ $ORACLE_HOME/rdbms/admin/catdpb.sql 


4.Dbmspump.sql will Re-Install DBMS DataPump objects
SQL >@ $ORACLE_HOME/rdbms/admin/dbmspump.sql 


5. To recompile  invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

For Oracle version 11g

1. Catproc.sql 
SQL >@ $ORACLE_HOME/rdbms/admin/catproc.sql 

2. To recompile invalid objects, if any
SQL >@ $ORACLE_HOME/rdbms/admin/utlrp.sql

参考:How To Reload Datapump Utility EXPDP/IMPDP [ID 430221.1]

发表在 逻辑备份/恢复 | 评论关闭

DB2备份恢复(增量备份与恢复)

发表于 2012 年 4 月 22 日 由 惜分飞

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs

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


[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp

修改数据

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649

  3 record(s) selected.


[db2inst1@xifenfei ~]$ db2 "drop table t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "drop table t_xifenfei01"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table t_01xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_01xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"

1          
-----------
        370

  1 record(s) selected.

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478

  1 record(s) selected.

增量备份SQL2426N解决

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp 
SQL2426N  The database has not been configured to allow the incremental backup 
operation. Reason code = "1".

[db2inst1@xifenfei ~]$ db2 ? SQL2426N


SQL2426N  The database has not been configured to allow the incremental
      backup operation. Reason code = "<reason-code>".

Explanation: 

Incremental backups are not enabled for a table space until after
modification tracking has been activated for the database and a
non-incremental backup has been performed on the table space.

Possible reason codes: 
1. The configuration parameter TRACKMOD has not been set for the
   database.
2. The TRACKMOD configuration parameter has been set but at least one
   table space has not had a non-incremental backup taken since the
   TRACKMOD parameter was set.

User response: 

The action is based on the reason code as follows: 
1. Activate modification tracking for the database by setting the
   TRACKMOD database configuration parameter to on, then perform a full
   database backup.
2. Consult the db2diag.log file to determine the name of the table
   space, then perform a full backup of that table space.

[db2inst1@xifenfei ~]$ db2 get db cfg for xff|grep TRACKMOD
 Track modified pages                         (TRACKMOD) = NO

[db2inst1@xifenfei ~]$ db2 update db cfg for xff 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, the database 
must be shutdown and reactivated before the configuration parameter changes 
become effective.

[db2inst1@xifenfei ~]$ db2stop force
04/11/2012 17:49:59     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst1@xifenfei ~]$ db2start
04/11/2012 17:50:09     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

累积增量备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental to /tmp

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

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 2


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp

再次修改数据

[db2inst1@xifenfei ~]$ db2 "create table t_02xff like syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_02xff select * from syscat.tables"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326

  2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"

1          
-----------
        371

  1 record(s) selected.

迭代备份

[db2inst1@xifenfei ~]$ db2 backup db xff online incremental delta to /tmp

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

[db2inst1@xifenfei ~]$ ll /tmp/XFF*
-rw-------  1 db2inst1 db2iadm1 122044416 Apr 11 18:19 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411181918.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:27 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411182708.001
-rw-------  1 db2inst1 db2iadm1  55128064 Apr 11 18:31 /tmp/XFF.0.db2inst1.NODE0000.CATN0000.20120411183129.001
--这里可以看出最近一次的增量备份和迭代备份备份文件大小相同,说明迭代备份是在最近一次增量备份基础之上进行

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 3


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411181918001   N    D  S0000015.LOG S0000015.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411181918
   End Time: 20120411181925
     Status: A
 ----------------------------------------------------------------------------
  EID: 27 Location: /tmp


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411182708001   O    D  S0000017.LOG S0000017.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411182708
   End Time: 20120411182712
     Status: A
 ----------------------------------------------------------------------------
  EID: 33 Location: /tmp


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411183129001   E    D  S0000019.LOG S0000019.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411183129
   End Time: 20120411183133
     Status: A
 ----------------------------------------------------------------------------
  EID: 36 Location: /tmp

还原数据库

[db2inst1@xifenfei ~]$ db2 restore db xff  incremental automatic from /tmp taken at 20120411183129
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.

尝试登陆数据库

[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019
--数据库需要前滚,因为是在线备份

恢复数据库

[db2inst1@xifenfei ~]$  db2 "rollforward db xff to end of logs and stop"

                                 Rollforward Status

 Input database alias                   = xff
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000019.LOG - S0000019.LOG
 Last committed transaction             = 2012-04-11-10.31.30.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.

验证恢复过程

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326

  2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_01xff"

1          
-----------
        370

  1 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_02xff"

1          
-----------
        371

  1 record(s) selected.

本篇主要测试了增量备份和数据库恢复,在下篇中将对不完全恢复进行测试,坚持逐步学习db2数据库相关知识

发表在 DB2 | 评论关闭

DB2备份恢复(全备与恢复)

发表于 2012 年 4 月 22 日 由 惜分飞

日志模式

[db2inst1@xifenfei ~]$ db2 get db cfg for xff |grep -i log
 Log retain for recovery status                          = NO
 User exit for logging status                            = YES   --(1)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 260
 Log buffer size (4KB)                        (LOGBUFSZ) = 98
 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 6
 Number of secondary log files               (LOGSECOND) = 4
 Changed path to log files                  (NEWLOGPATH) = 
 Path to log files                                       = /home/db2inst1/xff/redolog/NODE0000/
 Overflow log path                     (OVERFLOWLOGPATH) = 
 Mirror log path                         (MIRRORLOGPATH) = 
 First active log file                                   = S0000013.LOG
 Block log on disk full                (BLK_LOG_DSK_FUL) = NO
 Block non logged operations            (BLOCKNONLOGGED) = NO
 Percent max primary log space by transaction  (MAX_LOG) = 0
 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
 Log retain for recovery enabled             (LOGRETAIN) = OFF
 User exit for logging enabled                (USEREXIT) = OFF
 HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
 First log archive method                 (LOGARCHMETH1) = DISK:/home/db2inst1/xff/archivelog/  --(2)
 Options for logarchmeth1                  (LOGARCHOPT1) = 
 Second log archive method                (LOGARCHMETH2) = OFF
 Options for logarchmeth2                  (LOGARCHOPT2) = 
 Failover log archive path                (FAILARCHPATH) = 
 Number of log archive retries on error   (NUMARCHRETRY) = 5
 Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
 Log pages during index build            (LOGINDEXBUILD) = OFF

由(1)和(2)可以判断该数据库处于归档日志模式下

查看当前存在备份

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG  
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE    --离线备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp

在线全备

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs

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

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 2


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120406053431001   F    D  S0000000.LOG S0000000.LOG  
 ----------------------------------------------------------------------------
  Contains 2 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF OFFLINE  --本次试验的online备份
 Start Time: 20120406053431
   End Time: 20120406053439
     Status: A
 ----------------------------------------------------------------------------
  EID: 1 Location: /tmp


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411165312001   N    D  S0000013.LOG S0000013.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411165312
   End Time: 20120411165322
     Status: A
 ----------------------------------------------------------------------------
  EID: 19 Location: /tmp

当前数据库当前数据

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469

  2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "create table t_xifenfei01 like t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01 
> select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_xifenfei01 
select * from t_xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"

1          
-----------
        734

  1 record(s) selected.

恢复数据库

[db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411165312
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.
[db2inst1@xifenfei ~]$ db2 "rollforward db xff to end of logs and stop"

                                 Rollforward Status

 Input database alias                   = xff
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000013.LOG - S0000014.LOG
 Last committed transaction             = 2012-04-11-08.56.20.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db connect to xff
-bash: db: command not found
[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_XFF                           DB2INST1        T     2012-04-05-09.45.29.148434
T_XIFENFEI                      DB2INST1        T     2012-04-06-05.50.11.111469
T_XIFENFEI01                    DB2INST1        T     2012-04-11-16.55.51.853649

  3 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_xifenfei01"

1          
-----------
        734

  1 record(s) selected.

备份恢复是dba最重要的职责,本篇做为db2学习过程中第一篇关于备份恢复文章,后续将继续学习db2增量备份恢复等知识.

发表在 DB2 | 评论关闭