Привет уважаемые специалисты. Продолжая серию детских вопросов хочу спросить:

Когда и делаю Backup а потом Restore, я меняю содержание базы данных. А что происходит со структурой? Полями таблиц например. Если я после бекапа вставил новые ячейки, а потом сделал Restore, что случится с этими ячейками?

Спасибо заранее

Взял бекап с другой базы, сделал на этой restore , а потом rollforward . На rollforward ругается:

VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 "rollforward database KTC3 to end of logs overflow log path (/space/db2_restore/KTC3)"
SQL1271W Database "KTC3" is recovered but one or more table spaces are
off-line on node(s) "0".
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3

К базе приконнектится не могу :-(

Any Idea?

Mark Barinstein,

спасибо. Вот что я делаю

VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> pwd
/space/db2_restore/KTC3
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 restore database KTC3 from /space/db2_restore/KTC3
SQL1035N The database is currently in use. SQLSTATE=57019
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2stop
03/22/2016 09:17:36 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2stop force
03/22/2016 09:17:49 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2start
03/22/2016 09:18:00 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 restore database KTC3 from /space/db2_restore/KTC3
SQL2539W Warning! Restoring to an existing database that is the same as the
backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
SQL2563W The restore process completed successfully. However, one or more
table spaces from the backup image were not restored.
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 rollforward database KTC3 query status

Rollforward Status

Input database alias = KTC3
Number of nodes have returned status = 1

Node number = 0
Rollforward status = DB pending
Next log file to be read = S0007481.LOG
Log files processed = -
Last committed transaction = 2016-03-20-22.03.46.000000 UTC

VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 "rollforward database KTC3 to end of logs overflow log path (//space/db2_restore/KTC3)"
SQL1271W Database "KTC3" is recovered but one or more table spaces are
off-line on node(s) "0".
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 rollforward database KTC3 complete
SQL1271W Database "KTC3" is recovered but one or more table spaces are
off-line on node(s) "0".
VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 rollforward database KTC3 query status
Rollforward Status

Input database alias = KTC3
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0007481.LOG - S0007482.LOG
Last committed transaction = 2016-03-22-08.09.10.000000 UTC


### db2diag.log

VH2E:db2inst1@z12koadt:~> tail ./sqllib/db2dump/db2diag.log
0x000003FFF27F59A0 : 0000 0000 ....

2016-03-22-09.22.27.252639+060 E705124A467 LEVEL: Event
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::TermDbConnect, probe:2000
STOP : DATABASE: KTC3 : DEACTIVATED: NO
VH2E:db2inst1@z12koadt:~> tail -78 ./sqllib/db2dump/db2diag.log
2016-03-22-09.22.26.290657+060 I701555A522 LEVEL: Error
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:20
MESSAGE : ZRC=0x80020035=-2147352523=SQLB_NOT_ALLOWED_RECOVERY_P
"Access not allowed. Tblspc Recovery Pend."

2016-03-22-09.22.26.291311+060 I702078A473 LEVEL: Warning
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbIsAllowed, probe:1075
DATA #1 : String, 39 bytes
Tablespace 3 : state= 0, action= 65552.

2016-03-22-09.22.26.292865+060 I702552A473 LEVEL: Warning
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbIsAllowed, probe:1075
DATA #1 : String, 39 bytes
Tablespace 4 : state= 0, action= 65552.

2016-03-22-09.22.26.454848+060 E703026A464 LEVEL: Event
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
START : DATABASE: KTC3 : ACTIVATED: NO

2016-03-22-09.22.26.455589+060 I703491A576 LEVEL: Warning
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, PSM - SQL Procedure, psm_recover_all_procs, probe:10
MESSAGE : SQL procedure executables recovery has been initiated.
DATA #1 : Hexdump, 8 bytes
0x000003FFF27F597E : 4B54 4333 2020 2020 KTC3

2016-03-22-09.22.26.455655+060 I704068A489 LEVEL: Warning
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, PSM - SQL Procedure, psm_recover_all_procs, probe:20
MESSAGE : Executables not restored because rollforward or restore in progress

2016-03-22-09.22.26.455899+060 I704558A565 LEVEL: Warning
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (KTC3) 0
FUNCTION: DB2 UDB, PSM - SQL Procedure, psm_recover_all_procs, probe:900
MESSAGE : SQL procedure executables recovery ended. RC:
DATA #1 : Hexdump, 4 bytes
0x000003FFF27F59A0 : 0000 0000 ....

2016-03-22-09.22.27.252639+060 E705124A467 LEVEL: Event
PID : 11802 TID : 4397820012816PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : KTC3
APPHDL : 0-15 APPID: *LOCAL.db2inst1.160322082224
AUTHID : DB2INST1
EDUID : 35 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::TermDbConnect, probe:2000
STOP : DATABASE: KTC3 : DEACTIVATED: NO

Mark Barinstein,

VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> db2 restore db KTC3 from /space/db2_restore/KTC3 taken at 20160320230002 redirect generate script test_node0000.clp
DB20000I The RESTORE DATABASE command completed successfully.


VH2E:db2inst1@z12koadt:/space/db2_restore/KTC3> more test_node0000.clp
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON KTC3_NODE0000.out V ON;
SET CLIENT ATTACH_DBPARTITIONNUM 0;
SET CLIENT CONNECT_DBPARTITIONNUM 0;
-- *****************************************************************************
-- ** automatically created redirect restore script
-- *****************************************************************************
RESTORE DATABASE KTC3
-- USER <username>
-- USING '<password>'
FROM '/space/db2_restore/KTC3'
TAKEN AT 20160320230002
-- ON '/home/db2inst1'
-- DBPATH ON '<target-directory>'
INTO KTC3
-- LOGTARGET '<directory>'
-- NEWLOGPATH '/space/db2_backup/db/KTC3/aktivelogs/NODE0000/'
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- COMPRLIB '<lib-name>'
-- COMPROPTS '<options-string>'
-- 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) = 32684
-- *****************************************************************************
-- *****************************************************************************
-- ** 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 = 1875968
-- ** Number of usable pages = 1875936
-- ** High water mark (pages) = 1868032
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = KTC_SPACE
-- ** Tablespace ID = 3
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 4096
-- ** Number of usable pages = 4064
-- ** High water mark (pages) = 352
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = SYSTOOLSPACE
-- ** Tablespace ID = 4
-- ** 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) = 152
-- *****************************************************************************
-- *****************************************************************************
-- ** Tablespace name = SYSTOOLSTMPSPACE
-- ** Tablespace ID = 5
-- ** 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 KTC3 CONTINUE;
-- *****************************************************************************
-- ** end of file
-- *****************************************************************************

Mark Barinstein-_Михаил_-,

Вы восстанавливаете всю базу в /home/db2inst1.
Путь к журналам базы - /space/db2_backup/db/KTC3/aktivelogs/NODE0000/


Собственно говоря я хотел взять журналы из другого места. Вот отсюда
/space/db2_restore/KTC3. То есть использовать журналы другой базы. Моя задача сделать копию другой базы
Где моя ошибка? Как мне сделать это правильно?

db2diag

Mark Barinstein-_Михаил_-,

> Собственно говоря я хотел взять журналы из другого места. Вот отсюда
> /space/db2_restore/KTC3. То есть использовать журналы другой базы. Моя задача сделать копию другой базы

Какой другой базы?
Вы должны взять журналы от той базы, которая в архиве, а не от какой-то другой.

Есть две базы база1 и база2 . Мне нужно с помощю RESTORE и ROLLFORWARD сделать базу1 копией базы2 . Раньше это получалсь. Я задавал следующие комманды

db2 restore database база1 from путь к имеджу базы2
а потом накатывал логи
db2 "rollforward database база1 to end of logs overflow log path (путь к активным логам базы2 )"

Где тут ошибка

Спасибо заранее

Mark Barinstein,

спасибо Марк, но я почему то этого не вижу. Ни до RESTORE/ROLLFORWARD ни после. Специально запустил while петлю что бы контролировать расход места во время восстановления. Места больше, чем достаточно

Не понимаю :-(

Mark Barinstein-_Михаил_-Где тут ошибкаОшибка указана выше - у вас переполнение диска.

Спасибо Марк, я увеличил свободное место и все получилось