Creating a Database Copy (Importing a Data Backup into Another Database Instance)

Use

You create a copy of a database instance by importing a complete data backup of one database instance (source database) into another database instance (target database). The target database can be an existing one. You can also create a new database instance as the target database.

The database parameters of the target database instance do not necessarily have to be the same as those of the source database instance. We do recommend it, however, if you are creating the database copy for a standby instance. It is absolutely imperative, however, that you configure the data area of the target database to be large enough for the data backup to be imported into it.

Since the database IDs of the data backup and the target database are different, you have to initialize the target database when you import the backup. In the process, all catalog information and application data as well as any entries in the log area are deleted from the target database.

The first step is to create a complete data backup of the source database. Then you log on to the target database as a DBM operator and define a backup template for a complete data backup for the target database. The values defined for the properties type, backup type, size, block size and overwrite have to be identical to the values of the backup template that was used to create the backup of the source database. The backup template name is freely selectable; the data carrier (device/file) property must refer to the location at which the complete data backup of the source database was saved.

You transfer the target database to the ADMIN operational state and open a database session with this database instance. Then you import the data backup from the source database to the target database using your newly-defined backup template and initialize the target database. You use the DBM command db_activate RECOVER to do this.

Finally, you transfer the target database to the ONLINE operational state and load the system tables. To do this, you enter the logon data for the database system administrator of the source database.

To read the prerequisites, see Database Manager CLI, medium_put, backup_start, db_activate RECOVER

See also:

Creating and Configuring a Database Instance

Using the Parameter File from Another Database Instance

Defining a Backup Template for Complete Data Backup to a File

Backup

Concepts of the Database System, Backing Up and Restoring, Database Copy

Scenario

You have a database instance DEMODB and want to generate a database copy of it in the database instance TESTDB. The database instances are located on different, but compatible, computers. They were both created with the same version of the database software. The database parameters in TESTDB were defined with same values as in DEMODB.

Details on DEMODB:

Database computer: GENUA

DBM operator: user name OLEG, password MONDAY

Database system administrator: user name DBADMIN, password SECRET

Details on TESTDB:

Database computer: local, name: PARMA

DBM operator: user name ELENA, password SUNDAY

Database system administrator: user name SUPER, password MAX

Procedure

...

       1.      Call the Database Manager CLI in session mode, log on as operator OLEG with the password MONDAY, connect to the database instance DEMODB:

>dbmcli –u OLEG,MONDAY –d DEMODB

dbmcli on DEMODB>

       2.      Define the backup template for a complete data backup to a file using the following properties:

Backup template name: DemoDataCompl
Data carrier (device/file): Backups 2005\Data\completeDataJan
Type:
FILE
Backup type: complete data backup (
DATA)
Size restriction: none (
0)
Block size:
6
Overwrite: NO

dbmcli on DEMODB>medium_put DemoDataCompl "Backups 2005\Data\completeDataJan" FILE DATA 0 6 NO

OK

       3.      Execute a complete data backup of DEMODB using the backup template DemoDataCompl:

dbmcli on DEMODB>db_connect

OK

dbmcli on DEMODB>backup_start DemoDataCompl

OK

...

...

       4.      Close the Database Manager CLI:

dbmcli on DEMODB>exit

       5.      Call the Database Manager CLI, log on as operator ELENA with password SUNDAY, connect to the database instance TESTDB:

>dbmcli –u ELENA,SUNDAY –d TESTDB

dbmcli on TESTDB>

       6.      Define the backup template for a complete data backup to a file using the following properties:

Backup template name: DemoDataCompl_recover

Data carrier (device/file): \\GENUA\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\completeDataJan

Type: File

Backup type: complete data backup (DATA)

Size (restriction): none (0)

Block size: 6

Overwrite: NO

dbmcli on TESTDB>medium_put DemoDataCompl_recover "\\GENUA\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\Data\completeDataJan" FILE DATA 0 6 NO

OK

       7.      Transfer the database instance to the ADMIN operational state:

dbmcli on TESTDB>db_admin

OK

       8.      Open a database session:

dbmcli on TESTDB>db_connect

OK

       9.      Import the complete data backup from DEMODB to TESTDB (initializing TESTDB using the backup template DemoDataCompl_recover created for TESTDB:

dbmcli on TESTDB>db_activate RECOVER DemoDataCompl_recover

OK

Returncode              0

Date                    20060126

Time                    00142843

Server                  GENUA

Database                DEMODB

Kernel Version          Kernel    7.6.00   Build 016-123-109-428

Pages Transferred       6376

Pages Left              0

Volumes                 1

Medianame               DemoDataCompl_recover

Location                C:\Documents and Settings\All Users\Application Data\sdb\data\wrk\DEMODB\Backups 2005\completeDataJan

Errortext

Label                   DAT_000000008

Is Consistent           true

First LOG Page                  3146

Last LOG Page

DB Stamp 1 Date         20060126

DB Stamp 1 Time         00113848

DB Stamp 2 Date

DB Stamp 2 Time

Page Count              6354

Devices Used            1

Database ID             GENUA:DEMODB_20060125_165646

Max Used Data Page      0

   10.      Transfer the database instance to the ONLINE operational state:

dbmcli on TESTDB>db_online

OK

   11.      Load the system tables using the logon data of the database system administrator of DEMODB:

dbmcli on TESTDB>load_systab –u DBADMIN,SECRET

OK

0,OK: everything works fine

0,""C:\Program Files\sdb\TESTDB\bin\x_python" "C:\Program Files\sdb\TESTDB\env\systab.py" -R "C:\Program Files\sdb\TESTDB" -d TESTDB -u dbadmin,*"

Removing obsolete objects

Installing Loader tables

Installing messages and help

Installing SYSDBA tables

Installing comments for SYSDBA tables

Installing precompiler tables

Installing system tables for R3 backup information

Installing ODBC tables

Installing system tables for ORACLE (V7) mode

Installing data dictionary tables and views

Installing synonyms for oracle system tables

Installing comments on oracle system tables

Installing tables for WebAgent

Installing tables for Repository

Installing DOMAIN tables

Installing comments for DOMAIN tables

Installing Views as replacement for SHOWs

Installing Database Manager tables

Installing SYSINFO tables

Installing comments for SYSINFO tables

Installing support for UDE (User Defined Extensions)

Installing tables for JDBC Database Metadata

Installing tables for SQLDBC Database Metadata

Installing additional triggers

==================================

Installation successfully finished

==================================

---

dbmcli on TESTDB>

   12.      Exit the Database Manager CLI:

dbmcli on testdb>exit

OK

Result

The database instance TESTDB on computer PARMA now contains the data from the complete data backup of DEMODB on computer GENUA.

TESTDB is in the ONLINE operational state.

The original database system administrator and other database users of TESTDB have been overwritten with the data for the database system administrator of DEMODB and the other database users of DEMODB, respectively. Only these users now have access to the database instance.

The original DBM operators of TESTDB are still valid and can still manage the database instance TESTDB using the Database Manager.