Backup a DB2 database and restore redirect it to a different location
[db2inst1@primarynode-1 ~]$ db2 backup db erpdb to /home/db2inst1/backups
[db2inst1@primarynode-1 ~]$ scp ERPDB.0.db2inst1.NODE0000.CATN0000.20170511104723.001 172.16.0.77:/home/db2inst1/backups/
重定向:
[db2inst1@oracle backups]$ db2 restore db erpdb from /home/db2inst1/backups/ into devdb77 redirect generate script new_db.txt
修改重定向文件:
[db2inst1@oracle backups]$ vi new_db.txt
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON ERPDB_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM 0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
RESTORE DATABASE ERPDB
-- USER <username>
-- USING '<password>'
FROM '/home/db2inst1/backups/'
TAKEN AT 20170511104723
-- ON '/home/db2inst1'
-- DBPATH ON '<target-directory>'
INTO DEVDB77
-- NEWLOGPATH '/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- *****************************************************************************
-- ** table space definition
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = SYSCATSPACE
-- ** Tablespace ID = 0
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Regular table space.
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 32768
-- ** Number of usable pages = 32764
-- ** High water mark (pages) = 27332
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = TEMPSPACE1
-- ** Tablespace ID = 1
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Total number of pages = 1
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = USERSPACE1
-- ** Tablespace ID = 2
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 8192
-- ** Number of usable pages = 8160
-- ** High water mark (pages) = 1504
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = TEMP_SYSTEM_4K
-- ** Tablespace ID = 3
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Total number of pages = 1
-- *****************************************************************************
SET TABLESPACE CONTAINERS FOR 3
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
PATH '/db/db2inst1/data/temp/TEMP_4K'
);
-- *****************************************************************************
-- ** Tablespace name = TEMP_USER_4K
-- ** Tablespace ID = 4
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = User Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = No
-- ** Total number of pages = 1048576
-- ** Number of usable pages = 1048544
-- ** High water mark (pages) = 64
-- *****************************************************************************
SET TABLESPACE CONTAINERS FOR 4
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
FILE '/db/db2inst1/data/temp/TEMP_USER_4K' 1048576
);
-- *****************************************************************************
-- ** Tablespace name = TBS_GAME01_4K
-- ** Tablespace ID = 5
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 3932160
-- ** Number of usable pages = 3932128
-- ** High water mark (pages) = 20192
-- *****************************************************************************
SET TABLESPACE CONTAINERS FOR 5
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
FILE '/db/db2inst1/data/erpdb/TBS_GAME01_4K' 3932160
);
-- *****************************************************************************
-- ** Tablespace name = SYSTOOLSPACE
-- ** Tablespace ID = 6
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 8192
-- ** Number of usable pages = 8188
-- ** High water mark (pages) = 180
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = SYSTOOLSTMPSPACE
-- ** Tablespace ID = 7
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = User Temporary data
-- ** Tablespace Page size (bytes) = 4096
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Total number of pages = 1
-- *****************************************************************************
-- *****************************************************************************
-- ** start redirected restore
-- *****************************************************************************
RESTORE DATABASE ERPDB CONTINUE;
-- *****************************************************************************
-- ** end of file
-- *****************************************************************************
[db2inst1@oracle backups]$db2 list application for db devdb77
[db2inst1@oracle backups]$db2 force application (5497) 或者
[db2inst1@oracle backups]$db2 force application all
重定向恢复:[db2inst1@oracle backups]$db2 -tvf new_db.txt
最后回滚日志:
[db2inst1@oracle backups]$ db2 rollforward db devdb77 to end of logs and complete
表空间满的情况: 修改表空间大小
SET TABLESPACE CONTAINERS FOR 5 USING ( FILE '/db/db2inst1/data/devdb77/TBS_GAME01_4K' 3932160 )
SQL0968C The file system is full. SQLSTATE=57011
[db2inst1@primarynode-1 backups]$ db2 "ALTER TABLESPACE TBS_GAME01_4K RESIZE (ALL 2392160)"
DB20000I The SQL command completed successfully.
原始大小为3392160 修改为2392160