SQL Shadowing Commands - an Example Session
In the following sections, we use an example session to show how to:
- create shadows
- set them offline and take backup
- set them online again
- restore both user databanks and shadows
- drop shadows.
About the Examples
The examples in the following sections are based on:
Ident: BACADM Ident Privileges: DATABANK, SHADOW and BACKUP privileges User databanks: ARTICLES, CUSTOMERS with the LOG option enabled System databanks: TRANSDB, SYSDB and LOGDB
We assume that you are the ident BACADM and have connected to the database using Mimer BSQL, as follows:SQL> CONNECT; User: BACADM Password: Masterp1ece
Creating a Shadow
The following example creates shadows for the user and system databanks:SQL> CREATE SHADOW TRANSDB_SH FOR TRANSDB IN 'transdb_sh.dbf'; SQL> CREATE SHADOW SYSDB_SH FOR SYSDB IN 'sysdb_sh.dbf'; SQL> CREATE SHADOW LOGDB_SH FOR LOGDB IN 'logdb_sh.dbf'; SQL> CREATE SHADOW ARTICLES_SH FOR ARTICLES IN 'articles_sh.dbf'; SQL> CREATE SHADOW CUSTOMERS_SH FOR CUSTOMERS IN 'customers_sh.dbf';
For information on the CREATE SHADOW command, see the Mimer SQL Reference Manual, Chapter 12, CREATE SHADOW.
Setting a Shadow Offline
You must set a shadow offline, for example, when backing-up a databank, to ensure that databank shadow file is in a consistent state when the backup copy is taken.
The following example sets all the shadows created in the previous example offline:SQL> SET SHADOW TRANSDB_SH,LOGDB_SH,SYSDB_SH,ARTICLES_SH,CUSTOMERS_SH OFFLINE;
For information on the SET SHADOW command, see the Mimer SQL Reference Manual, Chapter 12, SET SHADOW.Caution: TRANSDB stores all operations carried out while shadows are offline. We recommend that you always set shadows online as soon as possible. If you do not, you risk TRANSDB filling disk capacity.
Backing-up from Shadows
You can back-up a databank using its shadow instead of the master databank.
This allows the backup process to proceed without affecting the users working with data contained in the databank.
When a shadow is set offline, the relevant transactions will be written to the online databank and remain in TRANSDB until the shadow is set online again and the transactions can be written to it.
To back-up your database from shadows:
- Set the shadows offline, as shown in the previous example.
- Use your operating system's functionality to copy the shadow files. For example, on UNIX:$ cp sysdb_sh.dbf sysdb_sh.bac $ cp transdb_sh.dbf transdb_sh.bac $ cp logdb_sh.dbf logdb_sh.bac $ cp articles_sh.dbf articles_sh.bac $ cp customers_sh.dbf customers_sh.bac
- Set the shadows online and reset the log, as shown in the next example.
- Move the backup files to a safe medium, such as CD/RW. Your backup is complete.
Setting a Shadow Online
When you set databank shadows online, they are automatically updated in the background to the current state of the master database.
Continuing with the previous example, you can set the shadows online, as follows:SQL> SET SHADOW SYSDB_SH,LOGDB_SH,TRANSDB_SH,ARTICLES_SH,CUSTOMERS_SH ONLINE RESET LOG;
Note: To get a backup timestamp, to be able to use the log when restoring from this backup, the RESET LOG option is used.
For information on the SET SHADOW command, see the Mimer SQL Reference Manual, Chapter 12, SET SHADOW.
Restoring a User Databank
If an error is encountered on a user databank, the system will continue to operate using the databank shadow. You can restore the damaged user databank by swapping it with its shadow using the ALTER SHADOW command.
For example, if the ARTICLES databank has been damaged, you can restore it by swapping it with its shadow ARTICLES_SH using the ALTER SHADOW command:SQL> ALTER SHADOW ARTICLES_SH TO MASTER;
Now, the faulty ARTICLES databank is the shadow. However, it has the same name as the master databank.
To return to our original situation, we must delete (drop) the faulty shadow, create a new shadow and swap the shadow with the master so that the databanks are correctly named.SQL> DROP SHADOW ARTICLES; SQL> CREATE SHADOW ARTICLES FOR ARTICLES_SH IN 'articles.dbf'; SQL> ALTER SHADOW ARTICLES TO MASTER;
The first command deletes the shadow.
The second command creates a shadow for the master with the original name and location.
The third command swaps the shadow with the master.
Note: If the original situation is not restored as shown above, the shadow name, in this case ARTICLES_SH, will remain allocated internally which could be confusing.
For information on the ALTER SHADOW command, see the Mimer SQL Reference Manual, Chapter 12, ALTER SHADOW.
Restoring Both a User Databank and Its Shadow
If both a user databank and its shadow are lost or damaged, you can restore the data using the shadow's backup files and LOGDB.
- We recommend that you stop the Mimer SQL database server when replacing databanks.
- Copy the shadow's backup file to the position of the damaged databank file, for example, on a UNIX system:$ cp articles_sh.bac articles.dbf
- Restart the Mimer SQL database server and use the ALTER DATABANK RESTORE command to restore the databank:SQL> ALTER DATABANK ARTICLES RESTORE USING LOG;
Note: The restore command above will automatically recreate the corresponding shadow databank.
For more information on ALTER DATABANK RESTORE, see the Mimer SQL Reference Manual, Chapter 12, ALTER DATABANK RESTORE
Restoring System Databanks
You cannot use the ALTER SHADOW command to swap the system databanks SYSDB, TRANSDB, and LOGDB with their shadows in order to restore them. You must alter them using the Mimer SQL UTIL program.
For more information, see:
- Transforming a SYSDB Shadow to a Master
- Transforming a TRANSDB Shadow to a Master
- Transforming a LOGDB Shadow to a Master.
Dropping a Shadow
As seen in a previous example, you can delete a shadow by dropping it, for example:SQL> DROP SHADOW ARTICLES_SH;
For information on the DROP SHADOW command, see the Mimer SQL Reference Manual, Chapter 12, DROP.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40