restore DB in a different system

desian · #1 (**permalink**) 12-22-09, 03:16

Hi,

have an BD2 Host. Work fine.
Now I want do an develop station so i backup my db, install a nearly similar system and try to restore a backup into the develop mashine.

db2 RESTORE DATABASE MYDB FROM "/" TO "/home/myuser/db2/MYDB" INTO SHOP NEWLOGPATH "/home/myuser/db2/MYDB" WITHOUT PROMPTING
DB20000I Der Befehl RESTORE DATABASE wurde erfolgreich ausgeführt. => success

now i try
db2 connect to MYDB
SQL1117N Die Verbindung zur bzw. die Aktivierung der Datenbank "SHOP" war
aufgrund einer anstehenden aktualisierenden Recovery nicht möglich.
SQLSTATE=57019 => faild

ok so far. I was reading that a rollforward is needed.

so i try
db2 ROLLFORWARD DATABASE shop

Code:

Status der aktualisierenden Recovery

 Aliasname der Eingabedatenbank                                   = mydb
 Anzahl Knoten, die Status zurückgaben                            = 1

 Knotennummer                                                     = 0
 Aktualisierende Recovery: Status                                 = DB  aktiv
 Nächste zu lesende Protokolldatei                                = S0001073.LOG
 Verarbeitete Protokolldateien                                    =  -
 Letzte festgeschriebene Transaktion                              = 2009-12-20-06.30.29.000000 UTC

=> seems like success but
db2 connect to MYDB
faild agin.

dont know what to do now.

Any Help?

Greetings
desian

przytula_guy · #2 (**permalink**) 12-22-09, 05:55

do : rollforward to end of logs and complete
the complete will bring the db out of the pending status

desian · #3 (**permalink**) 12-22-09, 10:09

tx. Bu will not work.
i try
db2 rollforward DATABASE MYDB to end of logs and complete

Code:

SQL4970N  Die aktualisierende Recovery der Datenbank "MYDB" kann in den
Datenbankpartitionen "0" nicht den angegebenen Unterbrechungspunkt
(Protokollende oder angegebener Zeitpunkt) erreichen. Die Verarbeitung der
aktualisierenden Recovery wurde bei Protokolldatei "S0001073.LOG" angehalten.

updatedb
find / -name S0001073.LOG
but "S0001073.LOG" not found.

The backup file is just the from 19.12.2009 from a database normaly made automatic backups each day.
Help this?

n_i · #4 (**permalink**) 12-22-09, 10:18

Try

Code:

rollforward DATABASE MYDB to end of logs and complete
overflowlogpath ("/home/myuser/db2/MYDB")
noretrieve

desian · #5 (**permalink**) 12-24-09, 04:24

Hi,

i try it but

Code:

SQL0104N  Auf "COMPLETE" folgte das unerwartete Token "overflowlogpath".  Zu
den möglichen Token gehören: "ANWEISUNGSENDE".  SQLSTATE=42601

I google for "overflowlogpath" and found
DB2 Universal Database
I rewrite the line and try:
>db2 "ROLLFORWARD DB SHOP TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH (/home/desian/db2/logs/shop) NORETRIEVE"

Code:

SQL4970N  Die aktualisierende Recovery der Datenbank "MYDB" kann in den
Datenbankpartitionen "0" nicht den angegebenen Unterbrechungspunkt
(Protokollende oder angegebener Zeitpunkt) erreichen. Die Verarbeitung der
aktualisierenden Recovery wurde bei Protokolldatei "S0001073.LOG" angehalten.

It dont solve the problem.
Sry.

Any Ideas?

Greetings
Desian

db2girl · #6 (**permalink**) 12-24-09, 09:35

Is the backup image you're restoring from online or offline. If offline, you can use: db2 rollforward db <db name> stop

I think you've tried that before so the image must be online.

Did you copy the logs needed by rollforward from source to target? Or does your backup include the logs?

You need to copy the logs / extract them from the image (if they were included) and place them in the active log dir or dir pointed to by the "overflow log path" parameter. The first log rollforward is looking for is S0001073.LOG. Copy this one and a few more to the target system, place them in /home/desian/db2/logs/shop and then retry rollforward.

desian · #7 (**permalink**) 12-24-09, 12:01

Hi,

I tryed.
db2 "ROLLFORWARD DB MYDB STOP"

Code:

SQL0276N  Zu der Datenbank "SHOP" kann keine Verbindung hergestellt werden, da
sie sich im Status 'Restore anstehend' befindet.  SQLSTATE=08004

I see a state "pending" here.
Have an online backup file.

The database goes nt ou of this stace.
i can not connect to this database.
I see the datbase in db2cc but can not do anything wit it.
May delete for a new try.

Normaly simple thing:
- Have a backup file. (online backup)
- same DB2 releese, same environment, Linux x86_64
- Want make a database from.
Question: How to do?

Greetings
Desian

off toppic:
DB2 is realy hard to use...
Mey next releese with focus on usability and OutOfTheBoxWorkingThings

[ok, was just a nice try...]

db2girl · #8 (**permalink**) 12-24-09, 13:36

To make it easier for you:
- take an online backup with "include logs" on source
- transfer this image to target
- restore the image and extract the logs
- rollforward using the extracted logs

Here is an example (I did it on the same server, but you use similar commands when the source and target servers are different):

bkogan@panipuri /home/bkogan > db2 backup db test online include logs
Backup successful. The timestamp for this backup image is : 20091224132814

bkogan@panipuri /home/bkogan > mkdir test_logs

bkogan@panipuri /home/bkogan > db2 "restore db test logtarget /home/bkogan/test_logs"
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
DB20000I The RESTORE DATABASE command completed successfully.

bkogan@panipuri /home/bkogan > ls -l /home/bkogan/test_logs
total 24
-rw------- 1 bkogan build 12288 Dec 24 13:29 S0000001.LOG

bkogan@panipuri /home/bkogan > db2 connect to test
SQL1117N A connection to or activation of database "TEST" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019

bkogan@panipuri /home/bkogan > db2 "rollforward db test to end of logs and stop overflow log path (/home/bkogan/test_logs) noretrieve"

Rollforward Status

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

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000001.LOG - S0000001.LOG
Last committed transaction = 2009-12-24-18.28.21.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

bkogan@panipuri /home/bkogan > db2 connect to test

Database Connection Information

Database server = DB2/AIX64 9.5.4
SQL authorization ID = BKOGAN
Local database alias = TEST

desian · #9 (**permalink**) 12-25-09, 04:15

Hi db2girl,

thx to made it easyer for me.
But on the first commend did not work.

I try:
make new directory for my special backup and go in this directory.
cd /home/myuser/backup/mydb
db2 backup db mydb online include logs

Code:

SQL2059W  A device full warning was encountered on device "/home/myuser/backup/mydb".
Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
SQL2001N  The utility was interrupted.  The output data may be incomplete.

only "t" give a result.
after "c" and "d" he wait a while and ask me agin.
So i can not follow the description, that looks so nice easy.

Greetings
Desian

db2girl · #10 (**permalink**) 12-28-09, 19:48

free up space on /home or use a different filesystem

desian · #11 (**permalink**) 05-28-10, 05:13

Late but Project was on hold...

Was not expecting full disk

As second the file was corrupt.
I download offline backup agin from server, free space and it work fine.
Tx.

db2 "restore database MYDB from /home/myuser/sourcedir/ logtarget /home/myuser/MYDBdir"
db2 "rollforward db MYDB to end of logs and stop overflow log path ('/home/myuser/MYDBdir/')"

Internet Services

Web Development

Digital Marketing

Consumer Tech