I am trying to refresh the content of QA database using DB2 restore redirect method to restore production database to QA database:

Both servers are on AIX 5.3 with DB2 V8.1 fixpack 10.

The QA TST database has log-retain mode turn off.

The production PRD database has log-retain mode turn on.

I created an offline backup of production database PRD, and copied the backup file to the QA server.
I shut down the QA SAP TST system, and perform offline backup of TST database.

When executed a restore redirect script in the QA server:

the step 1 had a warning message:

RESTORE DATABASE PRD FROM '/dbexport/bkup' TAKEN at 20080215203024 INTO TST REPLACE EXISTING REDIRECT

SQL2529W Warning! Restoring to an existing database that is different from the backup image database, and the alias name "TST" of the existing database does not match the alias name "PRD" of the backup image, and the database name "TST" of the existing database does not match the database name "PRD" of the backup image. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target database will be deleted.
SQL1277N Restore has detected that one or more table space containers are inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

the step 2 - set tablespace container for # had executed successfully with
SET TABLESPACE CONTAINER FOR 0 USING ( FILE /db2/TST/NODE0000/SYSCATSPACE.container000 153600 )
DB20000I The SQL command completed successfully.

SET TABLESPACE CONTAINER FOR 3 USING ( FILE /db2/TST/NODE0000/SYSTOOLSPACE.container000 6400 )
DB20000I The SQL command completed successfully.

SET TABLESPACE CONTAINER FOR 4 USING ( PATH /db2/TST/NODE0000/temp/SYSTOOLSTMPSPACE.container000 )
DB20000I The SQL command completed successfully.

SET TABLESPACE CONTAINER FOR 5 USING ( PATH /db2/TST/NODE0000/temp/PSAPTEMP16.container000 )
DB20000I The SQL command completed successfully.

SET TABLESPACE CONTAINER FOR 6 USING ( FILE /db2/TST/NODE0000/TST#PRODINFO.container000 48960 )
DB20000I The SQL command completed successfully.


At the step 3 - I had the error message:
RESTORE DATABASE PRD CONTINUE
SQL1651N The request cannot be executed because the DB2 server version does not support this functionality.

At this point, I can not execute db2 restore database PRD continue or db2 restore database PRD abort or db2 restore database TST abort.

I have to drop my database TST, and successfully restore from the TST backup taken before I started the redirect restore of PRD.

Is the redirect restore work in my database version?

According to DB2 version 8, I should be able to perform the redirect restore.

Is the QA database need to have the same LOG RETAIN mode as the production database?

Why did SQL1651N error occur?

What is the proper way to refresh the content of the QA database?

Thanks in advance!

It would be helpful to see the actual BACKUP command issued in production, along with the db2level output on both servers. Something like this could happen, for example, when restoring a compressed backup from a 64-bit system to a 32-bit one.

Do you have to use # in TST#PRODINFO?

Try without #. If this doesn't help:

- you're not redefining tablespace ID 1 and 2. They don't exist or are they using default path on source db (db directory path)?

- you can also try to put the container name in quotes

- the correct syntax is "set tablespace containers" (with an "s" at the end)

Thanks for replying to my post.

db2level is the same in Produciton and QA:
Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and FixPak
"10".
Product is installed at "/usr/opt/db2_08_01".

Both server is AIX 5.3, and 64 bit.

I corrected the syntax error, and rerun the redirect restore script, the restore is completed successfully.

However, the log archive method and option in production database is different in the test database. After the restoration, the database can not be connected.

db2 restore database PR1 continue
DB20000I The RESTORE DATABASE command completed successfully.

db2 list tablespaces
SQL2071N An error occurred while accessing the shared library
"/usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a". Reason code: "2".

db2 get db cfg for TST | grep -i log
Log retain for recovery status = RECOVERY
User exit for logging status = YES
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 2560
Log buffer size (4KB) (LOGBUFSZ) = 1024
Log file size (4KB) (LOGFILSIZ) = 40950
Number of primary log files (LOGPRIMARY) = 20
Number of secondary log files (LOGSECOND) = -1
Changed path to log files (NEWLOGPATH) =
Path to log files = /db2/TST/log_dir/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000568.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = YES
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 300
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
First log archive method (LOGARCHMETH1) = VENDOR:/usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a
Options for logarchmeth1 (LOGARCHOPT1) = /db2/PR1/tdp_r3/vendor.env
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Log pages during index build (LOGINDEXBUILD) = OFF

db2 update db cfg using LOGARCHMETH1 off
SQL1024N A database connection does not exist. SQLSTATE=08003

db2 connect to TST
SQL2071N An error occurred while accessing the shared library
"/usr/tivoli/tsm/tdp_r3/db264/libtdpdb264.a". Reason code: "2".

It appears that the log archive method and option are resetored using the production database configuration. The log archive option is retaining the production directory and file name.

Since I can not connect to the database TST, I can not issue db2 update db cfg command to set the log archive method and option. I dropped the database TST, and restored TST from the backup that was taken before I started the restore processes.

I will create another production off-line backup with the log retain mode, log archive method, and log archive option turn off. I have to wait for another maintenance window to create this production backup.

Is there another way to restore the production off-line backup and overrride the log retain mode, log archive method and option?

Thanks for your input!

Change the command "db2 update db cfg using LOGARCHMETH1 off" to
"db2 update db cfg for TST using LOGARCHMETH1 off". This way does not require the db connection.

Andy

you forgot to specify the db name when updating logarchmeth1. It should be:

db2 update db cfg for tst using LOGARCHMETH1 off

I am able to turn off the LOGARCHMETH1 after the redirect restore.

Thank you!!