以Linux 上 V9 版本DB2 实验为基础,制定生产系统数据库备份策略以及恢复步骤
前提:
第一:修改生产库 NEWLOGPATH 参数指向单独保存在线日志的文件系统,设为 /log_online1
第二:修改生产库 MIRRORLOGPATH 参数,镜像在线日志文件,设为 /log_online2
第三:修改生产库 LOGARCHMETH1 参数,指向专门保存归档日志的文件系统(不再修改老的 LOGRETAIN 参数),设为 /log_arch
第四:生产库每天做 online 模式的全备,保存在 /bak 下
第五:以上备份保留10天,超过10天自动删除,每天将这些备份backup到磁带进行保存,每周循环
故障:
假设裸设备出现故障,数据库需要恢复
预处理动作:
在本机或者备机恢复以上备份到指定目录
恢复过程:
第一步:untag 裸设备
[db2inst1@bakserver bak]$ db2untag /dev/raw/raw1
db2untag: A service tool to remove the DB2 tag on a tablespace container.
The tag is used to prevent DB2 from reusing a container
in more than one tablespace.
If a tablespace/database is destroyed thru unnatural means,
then the tag can be left behind preventing future DB2 use of
the resource.
WARNING: This tool should only be used by informed sysadmins.
Using file
version = 213
db seed = A8F0F0CC
poolID = 3
contID = 0
created = 0
used = 0
poolLSN = 0000 00FA 01AC
CSum = 19AF068B
Instance = db2inst1
Database = NEW
If you are sure that this container is no longer needed
by the identified DB2 database, answer 'Yes'.
Do you want to untag the container /dev/raw/raw1?
---> YES
Container tag has been removed successfully.
[db2inst1@bakserver bak]$ db2untag /dev/raw/raw2
db2untag: A service tool to remove the DB2 tag on a tablespace container.
The tag is used to prevent DB2 from reusing a container
in more than one tablespace.
If a tablespace/database is destroyed thru unnatural means,
then the tag can be left behind preventing future DB2 use of
the resource.
WARNING: This tool should only be used by informed sysadmins.
Using file
version = 213
db seed = A8F0F0CC
poolID = 3
contID = 1
created = 0
used = 0
poolLSN = 0000 00FA 01AC
CSum = 19AF068A
Instance = db2inst1
Database = NEW
If you are sure that this container is no longer needed
by the identified DB2 database, answer 'Yes'.
Do you want to untag the container /dev/raw/raw2?
---> YES
Container tag has been removed successfully.
[db2inst1@bakserver bak]$ db2untag /dev/raw/raw3
db2untag: A service tool to remove the DB2 tag on a tablespace container.
The tag is used to prevent DB2 from reusing a container
in more than one tablespace.
If a tablespace/database is destroyed thru unnatural means,
then the tag can be left behind preventing future DB2 use of
the resource.
WARNING: This tool should only be used by informed sysadmins.
Using file
version = 213
db seed = A8F0F0CC
poolID = 3
contID = 2
created = 0
used = 0
poolLSN = 0000 00FA 01AC
CSum = 19AF0689
Instance = db2inst1
Database = NEW
If you are sure that this container is no longer needed
by the identified DB2 database, answer 'Yes'.
Do you want to untag the container /dev/raw/raw3?
---> YES
Container tag has been removed successfully.
[db2inst1@bakserver bak]$ db2untag /dev/raw/raw4
db2untag: A service tool to remove the DB2 tag on a tablespace container.
The tag is used to prevent DB2 from reusing a container
in more than one tablespace.
If a tablespace/database is destroyed thru unnatural means,
then the tag can be left behind preventing future DB2 use of
the resource.
WARNING: This tool should only be used by informed sysadmins.
Using file
version = 213
db seed = A8F0F0CC
poolID = 3
contID = 3
created = 0
used = 0
poolLSN = 0000 00FA 01AC
CSum = 19AF0688
Instance = db2inst1
Database = NEW
If you are sure that this container is no longer needed
by the identified DB2 database, answer 'Yes'.
Do you want to untag the container /dev/raw/raw4?
---> YES
Container tag has been removed successfully.
第二步: restore redirect
[db2inst1@bakserver /]$ db2 restore database NEW from /bak redirect
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst1@bakserver /]$ db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => set tablespace containers for 3 using (device '/dev/raw/raw1' 25600,device '/dev/raw/raw2' 25600,device '/dev/raw/raw3' 25600,device '/dev/raw/raw4' 25600)
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
db2 => restore database NEW continue
DB20000I The RESTORE DATABASE command completed successfully.
db2 => quit
DB20000I The QUIT command completed successfully.
第三步:rollforward
[db2inst1@bakserver /]$ db2 rollforward database NEW to end of logs
Rollforward Status
Input database alias = NEW
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000030.LOG
Log files processed = S0000029.LOG - S0000030.LOG
Last committed transaction = 2007-07-30-07.53.30.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$ db2 rollforward database NEW stop
Rollforward Status
Input database alias = NEW
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000029.LOG - S0000030.LOG
Last committed transaction = 2007-07-30-07.53.30.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
第四步:检查
[db2inst1@bakserver /]$ db2 connect to NEW
Database Connection Information
Database server = DB2/LINUX 9.1.0
SQL authorization ID = DB2INST1
Local database alias = NEW
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$ db2 list tablespaces show detail
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
Total pages = 8192
Useable pages = 8188
Used pages = 7912
Free pages = 276
High water mark (pages) = 7912
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 96
Free pages = 8064
High water mark (pages) = 96
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 3
Name = CT
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 102400
Useable pages = 102272
Used pages = 5696
Free pages = 96576
High water mark (pages) = 5696
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 4
Minimum recovery time = 2007-07-30-07.08.39.000000
[db2inst1@bakserver /]$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST DB2INST1 T 2007-07-30-14.52.02.561986
TEST2 DB2INST1 T 2007-07-30-15.08.38.323513
2 record(s) selected.
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$
[db2inst1@bakserver /]$ db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.0
You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => select * from test2
A
-----------
99
1
2
3
4
5
6 record(s) selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7437037/viewspace-929426/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7437037/viewspace-929426/