How to create the same database on other system
restoring a backup image in a different server
To create the same database on other system you need to specific the new devices, directories and files for the tablespaces containers . You can perform that using the redirect option on your restore command. Also, you have to move the logs from source server to the target server into the logpath. Let's see the concepts and steps below to do it :
Restore command
RESTORE DATABASE source-database-alias
[USER username [USING password]]
[TABLESPACE [(tblspace-name, ...)] [ONLINE]
| HISTORY FILE [ONLINE] | LOGS [ONLINE]]
[INCREMENTAL [AUTOMATIC | ABORT]]
[USE TSM | FROM dir/dev, ... ]
[TAKEN AT date-time]
[TO target-directory]
[INTO target-database-alias]
[LOGTARGET directory]
[NEWLOGPATH directory]
[WITH num-buff BUFFERS]
[BUFFER buffer-size]
[REPLACE EXISTING]
[REDIRECT]
[PARALLELISM n]
[WITHOUT ROLLING FORWARD]
[WITHOUT PROMPTING]
or
RESTORE DATABASE source-database-alias [CONTINUE | ABORT]
Restore Redirect
- Definition of table space containers is kept during a backup
- Used to redefine or redirect table space containers during a restore (usually used when restoring a backup image in a different server).
- If containers are not available during a restore, new containers can be specified.
- Redirected restore operations are performed by executing the RESTORE command with the REDIRECT option specified, followed by one or more SET TABLESPACE CONTAINERS commands, followed by the RESTORE command with the CONTINUE option specified.
- The basic syntax for the SET TABLESPACE CONTAINERS command is:
SET TABLESPACE CONTAINERS FOR tablespace-id
USING ( {PATH 'container-string‘, ... |
{FILE | DEVICE} 'container-string' number-of-pages,
...})
- Containers in the SET TABLESPACE CONTAINERS should have the same type of the containers defined on the source database. The container size on the target database can be smaller then on source, but the sum of the containers size should be at least equal to the table space high water mark on the source.
Example: Restore with redirect
db2 "RESTORE DATABASE SAMPLE FROM C:\BACKUPS TO D:\DB_DIR
INTO SAMPLE_2 REDIRECT"
db2 "SET TABLESPACE CONTAINERS FOR 0 USING (PATH
'D:\DB_DIR\SYSTEM')"
db2 "SET TABLESPACE CONTAINERS FOR 1 USING (PATH
'D:\DB_DIR\TEMP')"
db2 "SET TABLESPACE CONTAINERS FOR 2 USING (PATH
'D:\DB_DIR\USER')"
db2 "RESTORE DATABASE SAMPLE CONTINUE"
Assuming the backup was done online with log archiving enabled, there is one more thing to do:
Transfer your log files to the logpath in your new server.
Now you will perform a roll forward operation.
Roll Forward
- Allows to return a database to the state it was in at a given point in time.
- In order to perform a roll- forward recovery operation, the database must be recoverable
- Invoked after a database or a table space restore.
- You must have access to all archived log files that will be needed to perform the roll-forward recovery operation.
- Once the logs needed are is in the current log path, transactions are reapplied.
- Roll forward must be invoked after restoring a recoverable database from an online backup if the option without rolling forward were not specified in the restore command.
Before starting a roll forward, you should check the logs that you need using the command. For example:
db2 rollforward db sample query status
Then copy the logs from archive to the logpath or overflowlogpath, starting with the log showed in on “roll forward query status command”. Once the logs are in the logpath, run the roll forward command to restore your database on your new server.
To know what is the logpath you can execute the command below:db2 get db cfg for <dbname>Output:
Changed path to log files (NEWLOGPATH) =
Path to log files = C:\DB2\NODE0000\SQL00002\SQLOGDIR\
Overflow log path (OVERFLOWLOGPATH) =
EXAMPLES:
To to roll forward the database sample to the end of logs, and complete:
db2 rollforward db sample to end of logs and complete
After restoring the database, roll forward to a point in time, using local time
db2 rollforward db sample to 2006-05-15-17.21.56.245378 using local time and stop