DB2 using redirection to recover data and modify table space size

Backup a DB2 database and restore redirect it to a different location

[[Email protected] ~]$ DB2 backup DB erpdb to/home/db2inst1/backups

[Email protected]primarynode-1 ~]$ SCP erpdb.0.db2inst1.node0000.catn0000.20170511104723.001 172.16.0.77:/home/db2inst1/backups/

Redirect:

[[Email protected]Oracle backups]$ DB2 restore DB erpdb from/home/db2inst1/backups/into devdb77 redir ECT Generate script new_db.txt

To modify a redirect file:

[Email protected] backups]$ VI new_db.txt

-- *****************************************************************************

--* * Automatically created redirect restore script

-- *****************************************************************************

UPDATE COMMAND OPTIONS USING S 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

-- *****************************************************************************

[[email protected] backups] $db 2 list application for DB devdb77

[[email protected] backups] $DB 2 force application (5497) or

[[email protected] backups] $DB 2 force application All

REDIRECT recovery: [[email protected] backups] $db 2-TVF new_db.txt

Last rollback log:

[Email protected] backups]$ DB2 Rollforward DB devdb77 to end of logs and complete

Table space is full: Modify table space size

SET tablespace CONTAINERS for 5 USING (FILE '/db/db2inst1/data/devdb77/tbs_game01_4k ' 3932160)

sql0968c the file system is full. sqlstate=57011

[email protected] backups]$ DB2 "ALTER tablespace tbs_game01_4k RESIZE (all 2392160) "

db20000i the SQL command completed successfully.

Original size is 3 392160 modified to 2 392160

This article is from the "arvin_0213" blog, make sure to keep this source http://arvin0213.blog.51cto.com/2455782/1929594

DB2 using redirection to recover data and modify table space size