Backup and recovery of DB2 Database
[db2inst1@localhost ~]$db2 list db directory
2. Create test database $db2
db2 connect to test
List active libraries
$ db2 list active databases
l View tablespaces
$ db2 list tablespaces
List the tables in the library
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
0 record(s) selected.
l Creating tables
$ db2 "create table student(id int,fname varchar(30),age int)"
l Grant user rights
db2 "grant use of tablespace TABLESPACE_TEST to user XXX"
Insert data into a table
$ db2 "insert into student values (1,'Tom',22)"
l Query table contents
IV. Online backup backup of designated libraries
$ db2 backup db TEST online to /opt/dbbak include logs
If reported:
SQL2413N Online backup is not allowed because the database is not recoverable
or a backup pending condition is in effect.
Reason: Database online backup needs to enable archive log function, just installed database is not enabled
Backup to null
db2 backup db idsldap online to /dev/null
Backup successful. The timestamp for this backup image is : 20161228092925
Incremental backup:
/usr/bin/su - db2inst1 -c "db2 backup db FDBKDB61 online incremental use tsm >>/IBM/tsm/db2inst1/FDBKDB61_incbk.log 2>&1"
l View archive settings
$ db2 get db cfg for test|grep -i LOGARCHMETH
l Set archive path
$ db2 update db cfg using logarchmeth2 disk:/opt/dbbak/arch
if newspaper
SQL1024N A database connection does not exist. SQLSTATE=08003
Reason: Need to back up database once and then modify main log file and archive path of this log file
Workaround: Make a backup when connecting to the database.
Solution: (need to backup offline once, and then connect to the database)
l Display application status
$ db2 list applications
Stop the database application.
$ db2 force applications all
l Check the application status again, no output
$ db2 list applications
SQL1611W No data was returned by Database System Monitor.
l Make offline backup
$ db2 backup db TEST to /opt/dbbak
l Generate backup files and archive logs
l View backup records
> db2adutl query //This command is a TSM integration command and cannot be executed without TSM environment
$ db2 list history backup all for test
l Start entering library
$ db2 connect to test
l Make online backup again, you can do it
$ db2 backup db TEST online to /opt/dbbak include logs
5. Delete database simulation damage $db2 drop db test
$ db2 connect to test
SQL1013N The database alias name or database name "TEST" could not be found.
SQLSTATE=42705
VI. Restore database $db2 restore db TEST from /opt/bak taken at 20170123152954
$ db2 restore db test taken at 20160513020408
At this time, connect to the database, prompt:
SQL1117N A connection to or activation of database "TEST" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
Database "XXX" will not roll forward until a point in time greater than or equal to this has passed
Cannot leave roll forward pending state because node '0' contains information newer than specified time
Workaround: Roll forward logs
$ db2 rollforward database test to end of logs and complete
If reported:
SQL4970N Rollforward recovery stopped on the database named "TEST" because
the rollforward utility cannot reach the specified stop point (end-of-log or
point-in-time) on the following database partitions: "0".
Workaround: Recovery logs
$ db2 restore db test logs from /opt/dbbak/ taken at 20160513020408 logtarget /opt/dbbak/
Copy recovered archive files
$ cp /opt/dbbak/S0000000.LOG /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/LOGSTREAM0000
l Initiate entry into database
l Query tables and data
Data recovery back
l Close apps and libraries
$ db2 force applications all
$ db2stop force
SQL1064N DB2STOP processing was successful.
