DB2 drop table process

-
恢复已经被DROP掉的数据表：
恢复前提：
有备份，而且有归档日志；
 
恢复限制：
1. 大对象，长字段数据无法恢复；
2. XML数据无法恢复
 
恢复实验：
1. 完全备份数据库；
db2 backup database <dbname> to <directory>.
2. 连接数据库创建表，并添加数据；
db2 connect to <dbname>
db2 create table <tablename> (<column name> <column type)>
db2 insert into <tablename> values (<value>)...
db2 archive log for database <dbname>
3. 模拟意外删除数据表；
db2 drop table <tablename>
4. 使用之前的数据库备份恢复数据库；
db2 restore database <dbname> from <directory>
5. 检索已丢失表ID
db2 list history dropped table all for database <dbname>
a. 记录DROP TABLE ID
b. 记录DROP TABLE DDL
6. 向前恢复数据库，将drop table数据导出之文件中
rollforward database <dbname> to end of logs and stop recover dropped table <DROP TABLE ID> to <directory>
7. 使用DROP TABLE DDL重建表；
8. 导入之前导出的数据
 
例如：
1. db2 backup database COMMERCE to /tmp
2. db2 connect to COMMERCE
db2
create table tab1(no integer)
insert into tab1 values (1), (2), (3), (4), (5)
commit
db2 connect reset
db2 archive log for database COMMERCE
3. db2 connect to COMMERCE
db2 drop table tab1
4. db2 restore database COMMERCE from /tmp
5. db2 list history dropped table all for database COMMERCE
List History File for COMMERCE
 
Number of matching file entries = 1
 
 
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
  D  T  20130210160142                                        000000000000a70500020008
----------------------------------------------------------------------------
  "COMMERCE"."TAB1" resides in 1 tablespace(s):
 
  00001 USERSPACE1                                                           
----------------------------------------------------------------------------
    Comment: DROP TABLE                                                      
Start Time: 20130210160142
   End Time: 20130210160142
     Status: A
----------------------------------------------------------------------------
  EID: 8
 
 DDL: CREATE TABLE "COMMERCE"."TAB1" ( "NO" INTEGER )  IN "USERSPACE1" ;       
----------------------------------------------------------------------------
6. db2 rollforward database COMMERCE to end of logs and stop recover dropped table 000000000000a70500020008 to /tmp
7. db2 connect to COMMERCR
db2
CREATE TABLE "COMMERCE"."TAB1" ( "NO" INTEGER ) IN "USERSPACE1"
8. db2 import from /tmp/NODE0000/data of del insert into COMMERCE.tab1

Comments

Post a Comment

Popular posts from this blog

Nginx Proxy & Load Balance & LNMP

-
Nginx Proxy Service E-commerce website Server 1: provide image request response (  apache / nginx ) Server 2: provide dynamic request response ( php-fpm / apache-php ) Server 3: provide static request response ( apache / nginx ) Server 4: provide data request response ( mysql ) It need one additional nginx server 1. Install nginx 2. proxy to specific server Example location / {      proxy_pass http://192.168.199.187:80/; } location /bbs/ {      proxy_pass http://192.168.199.187:80/bbs/; } location ~* \.(jpg|png|gif)$  {      proxy_pass http://172.16.100.7; } Enhance the log information and specific the hostname location / {      proxy_pass  http://192.168.199.187:80/;      proxy_set_header <parameter name>  $host;  (client requst the hostname )      proxy_set_header <parameter name> $remote_addr; ...
Read more

Snort+barnyard2+Snorby CentOS 6.5_64 Installation

-
#download packages: daq-2.0.2.tar.gz ImageMagicK-6.8.9-8.tar.gz oinkmaster-2.0.tar.gz ruby-1.9.3-p547.tar.gz snort-2.9.6.2.tar.gz snortrules-snapshot-2962.tar.gz MySQL-server-5.5.40-1.rhel5.x86_64.rpm MySQL-client-5.6.21-1.rhel5.x86_64.rpm MySQL-devel-5.6.21-1.rhel5.x86_64.rpm MySQL-shared-5.6.21-1.rhel5.x86_64.rpm epel-release-6-8.noarch.rpm barnyard2-2.1.13.tar.gz #install yum package rpm -ivh epel-release-6-8.noarch.rpm yum install gcc-c++ patch readline readline-devel zlib zlib-devel flex git yum install libyaml-devel libffi-devel openssl-devel make  yum install bzip2 autoconf automake libtool bison iconv-devel libxml2-devel libxslt  yum install gcc g++ build-essential libssl-devel libreadline5-devel zlib1g-devel linux-headers-generic libsqlite3-devel libxslt-devel curl-devel sqlite-devel yum --enablerepo=epel install jasper jasper-libs jasper-devel #Mysql install yum remove mysql mysql-libs rpm -ivh MySQL-server-5.5.40-1.rh...
Read more

ORACLE Error

-
Image
oracle official error explanation  http://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm 从服务器下载ALERT日志文件，传输方式需要选择"ASCII" ORA-01078: failure in processing system parameters 发生场景：新建数据库第一次启动时 SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file ‘/home/oracle/oracle/product/10.2.0/db_1/dbs/initorcl.ora’ 原因：oracle_sid没有配置正确 解决方案： 1. 修改oracle用户.bash_profile文件中ORACLE_SID后的内容； 2. 重新已oracle用户登录； ORA-01157: cannot identify/lock data file string - see DBWR trace file  Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified. Action: Have operating system make file ...
Read more