10. Backup and Recovery#
This chapter explains the Altibase backup and recovery features which are provided to help prevent the loss of data in the event of unforeseen circumstances such as the loss or damage of a disk or data file, and explains how to manage database backup and recovery tasks.
Database Backup#
This section describes the backup methods and policies supported for memory and disk tablespaces depending on whether the database is in Archivelog or Nonarchievelog mode.
The Altibase Backup Policy#
Altibase supports the following types of backup operations:
Logical Backup#
- Utility backup
Physical Backup#
- Offline backup
- Online backup
When logical backup is performed using the aexport or iLoader utility, script files for creating tables, indexes and files in which table records are written are created.
Physical backup means that the data files and log anchor files constituting the database are copied.
Physical backup is categorized as either online backup or offline backup, depending on whether the service is interrupted while a snapshot of the data file or files is taken.
Before performing an offline backup, the database server must be shut down normally, and then all tablespace files, log anchor files and log files must be copied.
In the online backup, the database's data files, log anchor files, etc. are copied without interrupting service. During this process, some uncommitted data may also be backed up. Therefore, in the event of recovery, log files will be required in order to undo these uncommitted transactions. Additionally, online backup is only possible in archivelog mode, in which archive log files are created.
Online Backup Targets and Methods#
When performing online backup, the full database can be backed up, or a specific tablespace or log anchor file can be backed up as desired.
The statements that would be used in each case are as follows:
Full Database Backup#
iSQL> ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
Tablespace-Level Backup#
Method 1:#
iSQL> ALTER DATABASE BACKUP TABLESPACE SYS_TBS_DISK_DATA TO '/backup_dir';
Method 2:#
① BEGIN Backup Process
iSQL> ALTER TABLESPACE SYS_TBS_DISK_DATA BEGIN BACKUP;
② Copy the data files to the backup location
$ cp system001.dbf /backup_dir
③ END Backup Process
iSQL> ALTER TABLESPACE SYS_TBS_DISK_DATA END BACKUP;
SYS_TBS_MEM_DIC, the memory tablespace that contains system catalog data, can be backed up with tablespace backup or full database backup.
The following table describes the various backup modes of Altibase:
[Table 10-1] Backup Methods
| Backup Type | Backup Method | Backup Object | Restoration Method | Possible while Online |
|---|---|---|---|---|
| Backup using the iLoader utility | Use the iLoader "out" command | User-defined tables | Use the iLoader "in" command. | O |
| Full database online backup | Use the SQL statement ALTER DATABASE BACKUP DATABASE TO ''backup_dir''; |
All data files and log anchor files in all tablespaces in the system | ① Use the UNIX "cp" command ② ALTER DATABASE RECOVER DATABASE; |
O |
| Tablespace-level online backup | Method 1: Use the SQL statement: ALTER DATABASE BACKUP TABLESPACE tablespace name TO 'backup_dir'; Method 2: ① BEGIN Backup ALTER TABLESPACE tablespace name BEGIN BACKUP; ② Use the UNIX "cp" command cp datafiles backup_dir/ ③ ALTER TABLESPACE tablespace name END BACKUP; |
All data files in the tablespace | ① Use the UNIX "cp" command ② ALTER DATABASE RECOVER DATABASE; |
O |
| Offline backup | ① Shut down the database ② Use the UNIX "cp" command |
The entire database | Use the UNIX "cp" command | X |
| Backup Time Comparison | iLoader < online backup < offline backup |
Classifying Backup According to Scope#
Database-Level Backup#
-
All data files in the database are backed up.
-
Ensures that all backup-related log files are archived.
Tablespace-Level Backup#
-
All data files in a specific memory or disk tablespace are backed up.
-
Because the archiving of backup-related log files is not guaranteed, they must be archived separately using a DCL statement.
Classifying Backup according to Method#
Database-Driven Backup#
-
Files are copied by the Altibase server.
-
When a single DCL statement is executed, the full database(or specified tablespace) is backed up according to a predetermined order.
-
Can be used to perform both database-level backup and tablespace-level backup.
DBA-Driven Backup#
-
Files are copied by the DBA.
-
Because multiple tablespaces can be backed up in parallel, integration with 3rd-party backup solutions is supported.
-
Can be used to perform only tablespace-level backup.
Database Mode#
Depending on the way that online log files, in which all changes made to data are recorded, are managed, the database runs in either the archivelog mode or noarchivelog mode.
In the archivelog mode, when a log file fills up, logging continues in a new log file, and the previous log file is copied to an archive directory. The archive log directory is set using the ARCHIVE_DIR property in the $ALTIBASE_HOME/conf/altibase.properties file.
In the noarchivelog mode, these log files are deleted automatically by the system after checkpointing.
[Table 10-2] outlines the pros and cons of each database mode
| Database Mode | Pros | Cons |
|---|---|---|
| archivelog mode | - Media recovery is supported. - The database can be recovered up to the current point even if data files are lost or corrupted. |
- Disk space is required to store archive log files. - Because the DBA must provide a separate storage device in which to store archive logs, organize files, etc., the burden of management tasks is increased. - If there is not enough disk space for archive logs, a fault occurs. |
| noarchivelog mode | - The DBA does not have to manage archive log files. | - The DBA can only perform recovery using offline backups, even if data files are corrupted. - Changes to the data that were made between the time of the backup and the time point at which the data file was corrupted cannot be recovered. |
Database Mode Configuration and Change#
The database mode is determined when the database is created using the CREATE DATABASE statement, and can be changed during the control phase when the database is started.
An example of creating a database in archivelog mode is shown below:
CREATE DATABASE mydb INITSIZE=100M ARCHIVELOG;
The following example shows how to change the database mode to archivelog mode during the control phase when starting up the database:
$ isql -silent -u sys -p manager -sysdba
iSQL(sysdba)> STARTUP CONTROL;
iSQL(sysdba)> ALTER DATABASE ARCHIVELOG;
Online Backup and Media Recovery in Different Database Modes#
[Table 10-3] Backup and Recovery in Different Database Modes
| Mode | Backup Method | Media Recovery Type |
|---|---|---|
| noarchivelog modeOffline backup | Offline backup | Full database recovery |
| archivelog mode | Online backup Offline backup is |
Complete recovery - Full database recovery Incomplete - Cancel-based recovery - Time-based recovery |
| Either mode | Backup using the iLoader utility | Recovery using the iLoader utility |
Online Backup and Media Recovery in Different Tablespace States#
[Table 10-4] Online Backup and Media Recovery in Different Tablespace States
| Tablespace State | Online Backup | Media Recovery |
|---|---|---|
| ONLINE | YES | YES |
| OFFLINE | YES | YES |
| DISCARDED | NO | NO |
| DROPPED | NO | NO |
| BACKUP | NO | N/A |
Cautions when Performing Online Backup#
Online backup and checkpointing cannot be performed at the same time
Because the contents of the database that are maintained in memory are written to disk during checkpointing, and online backup only guarantees that the data are backed up to the point in time at which the backup was made, checkpointing and online backup are considered mutually exclusive, and cannot be run at the same time.
If a request to perform an online backup is received during checkpointing, the online backup will start after checkpointing is complete.
Similarly, if a request to perform checkpointing is received while the online backup is underway, checkpointing will start after the online backup is complete. Because Altibase is a hybrid database, one of its characteristics is that memory tablespaces are backed up first, followed by disk tablespaces, when the database is backed up.
Checkpointing cannot be performed on memory tablespaces while they are being backed up. When the backup of memory tablespaces is complete, and the backup of disk tablespaces is underway, memory tablespaces can be checkpointed, but disk tablespaces cannot.
Database Recovery#
In every database system, the possibility of a system or hardware failure always exists. If a failure that affects the database occurs, then recovery must be performed to restore the database. The goals after such failure are to ensure that the effects of all committed transactions are preserved in the recovered database and to return to normal operation as quickly as possible while insulating users from problems caused by the failure.
Altibase Recovery Policy#
Altibase supports the following types of recovery:
- Recovery using Logical Backup
- Restart Recovery
- Media Recovery
Restart recovery is automatically executed during the startup phase if the Altibase process was terminated abnormally due to a system crash or software error.
If a data file is lost or corrupted, media recovery uses a snapshot of a previous backup of the data file and re-executes all operations from the recovery start LSN to the current LSN to restore all data from the backup of the data file to the current point in time.
Whether it is necessary to perform media recovery is determined by checking whether the version of the data file which is written in the log anchor file is the same as the version of the current data file. This is illustrated below:
Media recovery can be performed only during the control phase when starting up the database. That is, Alitibase only supports offline media recovery.
Example#
The following is an example of media recovery. The data file 'user1.dbf,' which belonged to the tablespace TEST, has been lost. The lost data file is restored to its current state using the file 'user1.dbf,' an online backup that was made two days ago.
Copy the backup files to the data file location
cp /backup_dir/user1.dbf $ALTIBASE_HOME/dbs
Startup in control phase
iSQL(sysdba)> STARTUP CONTROL;
Perform media recovery
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE;
The data file 'user1.dbf' in the tablespace TEST has been restored.
Startup in service phase
iSQL(sysdba)> STARTUP SERVICE;
Media recovery has been completed, and thus the system progresses to the service phase.
Complete vs Incomplete Recovery#
The Altibase media recovery policies support both complete and incomplete recovery.
"Complete recovery" means restoring data files up to the current time point in the case where online logs and archive logs have not been lost.
"Incomplete recovery" refers to the case in which archive log files or online log files have been lost, and thus the database is recovered to the point in time immediately before the log files were lost, or the case where the full database is rewound to a certain time point in the past in order to restore the database at that point in time.
The following is an example of complete recovery:
ALTER DATABASE RECOVER DATABASE;
Incomplete recovery can be classified into two cases:
To rewind the full database to a certain point in the past:#
Copy all database backup files, which were created on September 10, 2007, and use them to perform recovery.
ALTER DATABASE RECOVER DATABASE UNTIL TIME '2007-09-10:17:55:00';
When the database cannot be restored to the present point in time due to the corruption of a particular online log file:#
It is restored to the time point immediately before the corruption of the online log file using the following command:
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
If incomplete recovery was performed in the control startup phase, the following statement must be subsequently executed in order to proceed to the meta startup phase
ALTER DATABASE db_name META RESETLOGS;
Because the database has been restored to a specific past time point, it will be necessary to prevent automatic recovery ("restart recover") from executing when the database is restarted. To accomplish this, the online logs are initialized using the resetlogs option.
When the database is started up in the meta startup phase, if the resetlogs option is used to reset the logs, the full database must be backed up, either offline or online.
The reason for this is as follows: if the logs are reset when proceeding to the meta startup phase and then another media error occurs two days later, it will only be possible to recover the data to the time point before the logs were reset. That is, changes made to the data in the two days since the logs were reset will be lost.
Cautions when Performing Media Recovery#
Due to the media recovery algorithm, the current log anchor files must be used for media recovery whenever possible.#
When recovery is performed, only the data files should be restored from backup copies. Except for special cases, log anchor files should not be restored from backup copies.
One such special case is the case where a user accidentally deletes a tablespace using the DROP TABLESPACE command. Because there are no data pertaining to the dropped tablespace in the current log anchor files at that time, it is acceptable to restore log anchor files from backup copies.
When recovering a data file of a memory tablespace, the stable memory data file must be used to restore the other memory data file.#
Because ping-pong checkpointing is used for memory tablespaces in Altibase, two data files pertaining to each memory tablespace are maintained on disk. A pair of data files, in which the same image is saved, is stored in a location set using the MEM_DB_DIR property. Both data files must exist in order for Altibase to operate normally. At any particular point in time, the memory tablespace is only using one of these data files.
In order to reduce the time required to perform backups, only the most recently checkpointed data file for the memory tablespace is backed up.
The data files containing the backup of memory tablespace are as follows:
SYS_TBS_MEM_DIC-1-0
SYS_TBS_MEM_DIC-1-1
SYS_TBS_MEM_DIC-1-2
The data files for a memory tablespace must be copied in the following manner:
$ cp SYS_TBS_MEM_DIC-1-0 $ALTIBASE_HOME/dbs;
$ cp SYS_TBS_MEM_DIC-1-1 $ALTIBASE_HOME/dbs;
$ cp SYS_TBS_MEM_DIC-1-2 $ALTIBASE_HOME/dbs;
When recovery is complete, automatically copies the stable memory data file to create an unstable memory data file.
If a tablespace is added or deleted or the name of a tablespace is changed, the dictionary tablespace (SYS_TBS_MEM_DIC) and the changed tablespace will need to be backed up. Otherwise, the full database will need to be backed up.#
ALTER DATABASE BACKUP TABLESPACE SYS_TBS_MEM_DIC TO '/backup_dir';
Because log anchor files include information about the tablespaces in a database, they must be backed up along with the dictionary tablespace whenever the structure of a tablespace is changed.
iSQL(sysdba)> ALTER DATABASE BACKUP LOGANCHOR TO 'anchor_path';
Altibase encounters the following problems when the database is backed up and restored with a replicated database.#
If a database backed up on one server is restored on another server, there might be problems to use replication after Altibase recovery because the network addresses are different.
Even if a database is restored on the same system, replication can be retransmitted based on meta information at the time of backup. In this case, some data may be changed to data at the time of backup.
Therefore, if replication is active, it is necessary to either: (1) the value of REPLICATION_SENDER_AUTO_START property changes to '0', and performs the recovery using the backup file (2) when the recovery is completed, RESET the replication or recreate the replication objects.
Backup and Recovery Examples#
Backing Up and Restoring Tables Using the iLoader Utility#
Backup can be used in order to be prepared for unforeseen problems with individual tables, or to back up a particular table for some specific reason.
Before Backup#
Before a backup can be performed, it is necessary to create a schema file, called a FORM file, for the table to be backed up. The FORM file contains basic information about the table, such as the name and data type of each column.
Example: To create a form file for table t1
iLoader> formout –T t1 –f t1.fmt
- Created with the file name t1.fmt
Backup#
Use the iLoader utility with the out option. A backup of the table will be created having the name specified by the user
Example: Back up table t1
iLoader> out –d t1.dat –f t1.fmt
- The form file to use is t1.fmt, and the backup file that will be created is t1.dat
Restore#
Use the iLoader utility with the in option
Example: Restore table t1
iLoader> in –d t1.dat –f t1.fmt
Offline Backup and Recovery#
Offline backup and recovery are generally used when the database was created in the noarchivelog mode.
Cautions When Performing Offline Backup#
Before performing the offline backup, stop all services related to Altibase
If an offline backup is performed while the database is running, the contents of log files will change during the backup, and thus the backup will not be performed correctly. Therefore, be sure that the Altibase server is stopped before performing an offline backup.
How to Perform Backup#
Use the UNIX cp (copy) command, or a similar command depending on the OS, to back up all of the data files, log files and log anchor files in all tablespaces. In Altibase, not only the memory data files but also the data files and log anchor files pertaining to disk tablespaces must be backed up.
The location where the data files for memory tablespace are stored is set using the MEM_DB_DIR property in the Altibase property file $ALTIBASE_HOME/conf/altibase.properties. To back up the data files for memory tablespace, all of the directories specified using MEM_DB_DIR must be copied.
The location of the log anchor files is set using the LOGANCHOR_DIR property in the $ALTIBASE_HOME/conf/altibase.properties file. The files in the directory specified using LOGANCHOR_DIR must be copied in order to back up the log anchor files. Furthermore, the data files for disk tablespaces should be copied after consulting the data dictionary.
Example#
$ vi $ALTIBASE_HOME/conf/altibase.properties
MEM_DB_DIR = $ALTIBASE_HOME/dbs0
MEM_DB_DIR = $ALTIBASE_HOME/dbs1
LOGANCHOR_DIR = $ALTIBASE_HOME/logs
Disk tablespace have been being only system tablespace, undo tablespace, and temp tablespace.
Location where backup file stores: /home/backup
$ cp $ALTIBASE_HOME/dbs0 /home/backup
$ cp $ALTIBASE_HOME/dbs1 /home/backup
$ cp $ALTIBASE_HOME/logs /home/backup
$ cp $ALTIBASE_HOME/dbs/system*.dbf /home/backup
$ cp $ALTIBASE_HOME/dbs/undo.dbf /home/backup
$ cp $ALTIBASE_HOME/dbs/temp.dbf /home/backup
How to Perform Recovery#
The Altibase properties file that was used when the database was backed up must be used when performing recovery. The backup files created during backup can be restored using the "cp" copy command or equivalent. It is necessary to have sufficient privileges to access these files.
Example#
In the following example, the database that was backed up above is restored.
$ cp /home/backup/dbs0 ALTIBASE_HOME/dbs0
$ cp /home/backup/dbs1 $ALTIBASE_HOME/dbs1
$ cp /home/backup/logs $ALTIBASE_HOME/logs
$ cp /home/backup/system*.dbf $ALTIBASE_HOME/dbs
$ cp /home/backup/undo.dbf $ALTIBASE_HOME/dbs
$ cp /home/backup/temp.dbf $ALTIBASE_HOME/dbs
Database-Driven Online Backup#
Database-Level Online Backup#
The full database is backed up online to the /backup_dir directory.
Database-Level Online Backup
ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
The backed-up files
$ ls /backup_dir
SYS_TBS_MEM_DIC-0-0
SYS_TBS_MEM_DATA-0-0
system001.dbf
system002.dbf
undo001.dbf
loganchor0
loganchor2
loganchor1
Tablespace-Level Online Backup#
The stable versions of the data files in the SYS_TBS_MEM_DIC tablespace are backed up online to the /backup_dir directory.
Tablespace-Level Online Backup
ALTER DATABASE BACKUP TABLESPACE SYS_TBS_MEM_DIC TO '/backup_dir';
The backed-up file
$ ls /backup_dir
SYS_TBS_MEM_DIC-0-0
Loganchor Online Backup#
All loganchor files are backed up online to the /backup_dir directory.
Log Anchor Online Backup
ALTER DATABASE BACKUP LOGANCHOR TO '/backup_dir';
The backed-up files
$ ls /backup_dir
loganchor0 loganchor1 loganchor2
DBA-Driven Online Backup#
Tablespace-Level Online Backup#
Back up the data files in the USER_MEMORY_TBS and USER_DISK_TBS tablespaces online to the /backup_dir directory.
The data files in the memory tablespace are backed up online after checking that they are stable copies.
Perform BEGIN backup on the memory tablespace USER_MEMORY_TBS#
iSQL(sysdba)> ALTER TABLESPACE user_memory_tbs BEGIN BACKUP;
Verify stable data files#
iSQL(sysdba)> SELECT * FROM V$STABLE_MEM_DATAFILES;
MEM_DATA_FILE
------------------------------
/altibase_home/dbs/USER_MEM_TBS-0-0
Stable data file backup#
$ cp $ALTIBASE_HOME/dbs/USER_MEMORY_TBS-0-0 /backup_dir/
Perform END backup on the memory tablespace USER_MEMORY_TBS#
iSQL(sysdba)> ALTER TABLESPACE user_memory_tbs END BACKUP;
Perform BEGIN backup on the disk tablespace USER_DISK_TBS#
iSQL(sysdba)> ALTER TABLESPACE user_memory_tbs BEGIN BACKUP;
Backup the data files of the disk tablespace#
$ cp $ALTIBASE_HOME/dbs/USER_DISK_TBS.dbf /backup_dir/
Perform END backup on the disk tablespace USER_DISK_TBS#
iSQL(sysdba)> ALTER TABLESPACE user_memory_tbs END BACKUP;
Verify the backed-up files#
$ ls /backup_dir
USER_MEMORY_TBS-0-0 USER_DISK_TBS.dbf
SNAPSHOT Backup#
SNAPSHOT backup can be performed by Altibase iLoader after specifying a certain spot with SCN.
In general, the snapshot backup is efficient for tables with foreign keys or triggers, and when performing data backup with iLoader at the time of service. This is because the snapshot backup can keep the data consistency.
Only the DBA with SYSDBA privilege can set up or disable the snapshot.
SNAPSHOT Setting#
iSQL(sysdba)> ALTER DATABASE BEGIN SNAPSHOT;
If snapshot is set up, the SCN value specified in the V$SNAPSHOT performance view is able to be confirmed.
Disabling SNAPSHOT#
iSQL(sysdba)> ALTER DATABASE END SNAPSHOT;
Caution#
- If the snapshot SCN is specified, it should be used in case of not frequent DML since the data after SCN is not deleted.
- The snapshot will be stopped if thresholds specified in the SNAPSHOT_MEM_THRESHOLD, and SNAPSHOT_DISK_UNDO_THRESHOLD properties are exceeded.
- The standard point of snapshot while receiving data from the receiver performing replication or updating a large amount of tables may be varied.
- The snapshot setting cannot be specified during exporting data with iLoader.
Completing Online Backup#
When manually backing up the database online, the final step is to issue a command to archive the backup-related log files forcibly. This command closes the current log file, even if it is not full. Logging continues in the next log file.
iSQL(sysdba)> ALTER SYSTEM SWITCH LOGFILE;
A message indicating that online backup is complete is written to altibase_sm.log. In this example of manual backup, the completion of backup is indicated by a message saying that the log file named logfile15341 is being archived.
[2007/09/18 14:42:38] [Thread-6] [Level-9]
Waiting logfile15341 to archive
[2007/09/18 14:42:43] [Thread-6] [Level-9]
Database-Level Backup Completed [SUCCESS]
Media Recovery Example 1: Loss of data files for disk tablespaces that were not backed up.#
Suppose that the database is operating in archivelog mode and that the data file $ALTIBASE_HOME/dbs/abc.dbf, which was not backed up, has been lost.
Data files in a memory tablespace cannot be recovered in this way.
Recovery Procedure#
Check the archive log files required for complete recovery#
Check which archive log files are required for complete recovery.
iSQL(sysdba)> SELECT NAME, CREATE_LSN_LFGID, CREATE_LSN_FILENO FROM V$DATAFILES;
---------------------------------------------------------
…
/altibase_home/dbs/abc.dbf 0 18320
To check the log file deleted most recently, view the contents of the log anchor file using the "dumpla" utility
$ dumpla loganchor0
[LOGANCHOR HEADER]
Binary DB Version [ 7.3.0 ]
Archivelog Mode [ Archivelog ]
Transaction Segment Entry Count [ 256 ]
Begin Checkpoint LSN [ 20345, 469859 ]
End Checkpoint LSN [ 20345, 470300 ]
Disk Redo LSN [ 20345, 469859 ]
LSN for Recovery from Replication [ NULL ]
Server Status [ SERVER SHUTDOWN ]
End LSN [ 20345,470341 ]
Media Recovery LSN [ 0, 469859 ]
ResetLog LSN [ 4294967295, 4294967295 ]
Last Created Logfile Num [ 20350 ]
Delete Logfile(s) Range [ 20333 ~ 20344 ] # Information about the most recently deleted log file
Update And Flush Count [ 316 ]
New Tablespace ID [ 8 ]
Check if the archive log file required for complete recovery is available#
Check whether the log files ranging from logfile18320 to logfile20344 exist in the directory specified using the ARCHIVE_DIR property. If not, copy the archive log files from a backup storage device to the directory specified using the ARCHIVE_DIR property.
All log files after logfile20345 are online log files that exist in the directory specified using the LOG_DIR property. That is, the log files ranging from logfile18320 to logfile20345 are required in order to recover the lost abc.dbf file completely.
To avoid the waste of disk space that would occur if duplicate log files existed in the directories specified using both the ARCHIVE_DIR and LOG_DIR properties, Altibase directly reads the log files from the directory specified using the ARCHIVE_DIR property.
Startup in control phase#
iSQL(sysdba)> STARTUP CONTROL;
Create the lost data file#
Create the lost abc.dbf file by executing the following command during the control startup phase.
iSQL(sysdba)> ALTER DATABASE CREATE DATAFILE 'abc.dbf';
Perform complete recovery#
Perform complete media recovery by executing the following command during the control phase.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE;
Media Recovery Example 2: Loss of data files for disk tablespaces that were backed up.#
Suppose that the database is operating in archivelog mode and that the data files in the USER_DISK_TBS tablespace were backed up three days ago.
All of the data files in the USER_DISK_TBS tablespaces were lost this morning.
Backup Procedure#
The backup from three days ago was performed as follows:
iSQL(sysdba)> ALTER DATABASE BACKUP TABLESPACE user_disk_tbs TO '/backup1';
iSQL(sysdba)> ALTER SYSTEM SWITCH LOGFILE;
The backed-up files
$ ls /backup1
USER_DISK_TBS01.dbf USER_DISK_TBS02.dbf USER_DISK_TBS03.dbf
Recovery Procedure#
Check the archive log files required for complete recovery#
After determining which archive log files are required for complete recovery, these files are copied into the archive directory. The method of determining which archive log files are required is to check the headers of the data files that are to be recovered. The header information can be viewed using the dumpddf utility, as shown below:
$ dumpddf -m -f USER_DISK_TBS01.dbf
[BEGIN DATABASE FILE HEADER]
Binary DB Version [ 5.4.1 ]
Redo LSN [ 4, 2257550 ] # The archive log file required for complete recovery is logfile4.
Create LSN [ 0, 657403 ]
[END DATABASE FILE HEADER]
The above results indicate that the logfile4 archive log file and subsequent files are required in order to restore the database using the backup data files.
Copy the backup of the data file to its original location#
Copy the data file backups in the backup_dir directory to the original location of the data files for the USER_DISK_TBS tablespace, which is the $ALTIBASE_HOME/dbs/ directory.
$ cp /backup_dir/*.dbf $ALTIBASE_HOME/dbs;
Startup in control phase#
iSQL(sysdba)> STARTUP CONTROL;
Perform complete recovery#
Perform complete media recovery during the control phase.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE;
Media Recovery Example 3: Restore by changing the path of the data files for the disk tablespace.#
Suppose that the database is operating in archivelog mode and that the data files in the USER_DISK_TBS tablespace were backed up seven days ago.
This afternoon, the /disk1 file system, which contained the data files for the USER_DISK_TBS tablespace, was corrupted, but the /disk2 file system remains intact.
In this case, because the /disk1 partition is corrupt, the backup data files are moved to the healthy partition, which is /disk2, in order to perform media recovery.
Backup Procedure#
The backup from seven days ago was performed as follows:
iSQL(sysdba)> ALTER DATABASE BACKUP TABLESPACE user_disk_tbs TO '/backup_dir';
iSQL(sysdba)> ALTER SYSTEM SWITCH LOGFILE;
The backed-up files
$ ls /backup_dir
USER_DISK_TBS01.dbf USER_DISK_TBS02.dbf
Recovery Procedure#
Check the archive log files required for complete recovery#
After checking which archive log files are required to perform complete recovery, these files are copied to the archive directory.
Copy the backup of the data file to a different file system#
Copy the backups of the files for the USER_DISK_TBS tablespace, which are in the backup_dir directory, to the /disk2 file system.
$ cp /backup_dir/*.dbf /disk2/dbs;
Startup in control phase#
Startup in control phase.
iSQL(sysdba)> STARTUP CONTROL;
Change the path of the data file#
Change the file path for the data files for the USER_DISK_TBS tablespace during the CONTROL phase.
iSQL(sysdba)> ALTER DATABASE RENAME DATAFILE '/disk1/dbs/USER_DISK_TBS01.dbf' TO '/disk2/dbs/USER_DISK_TBS01.dbf';
iSQL(sysdba)> ALTER DATABASE RENAME DATAFILE '/disk1/dbs/USER_DISK_TBS02.dbf' TO '/disk2/dbs/USER_DISK_TBS02.dbf';
The ALTER TABLESPACE command can also be used to perform this task.
iSQL(sysdba)> ALTER TABLESPACE user_disk_tbs RENAME DATAFILE '/disk1/dbs/USER_DISK_TBS02.dbf' TO '/disk2/dbs/USER_DISK_TBS02.dbf';
Check the changed path of the data file#
Check the V$DATAFILE performance view to verify that the data file path was correctly changed.
iSQL(sysdba)> SELECT * FROM V$DATAFILES;
Perform complete recovery#
Complete media recovery can be performed by executing the following command during the control phase.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE;
Media Recovery Example 4: Incomplete recovery example after table deletion.#
Suppose that the database is operating in archivelog mode and that a user accidentally dropped the summary table.
- The time of completion of the most recent full database online backup: September 18, 2007, 12:00
- The time when the table was dropped: September 18, 2007, 15:00
- The current time: September 18, 2007, 18:00
In order to recover the summary table, it will be necessary to perform incomplete media recovery to restore the database to its state at 14:30, 2007, on September 18, which is 3.5 hours before the current time.
Backup Procedure#
When the last backup was performed, the full database was backed up, as shown below:
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
iSQL(sysdba)> alter SYSTEM SWITCH LOGFILE;
Recovery Procedure#
Copy the backup file its original location#
Copy the backups of the data files to their original location.
$ cp /backup_dir/*.dbf $ALTIBASE_HOME/dbs;
Copy the backup of the memory checkpoint image file to its original location#
Because the ping-pong checkpointing technique is used with memory tablespaces, when the database is backed up, only the stable data files for a memory tablespace are copied.
Example) The backups of the data files for a memory tablespace are as follows:
SYS_TBS_MEM_DIC-1-0
SYS_TBS_MEM_DIC-1-1
SYS_TBS_MEM_DIC-1-2
SYS_TBS_MEM_DATA-0-0
SYS_TBS_MEM_DATA-0-1
SYS_TBS_MEM_DATA-0-2
Since the backup of the memory tablespace contains valid data files, they can be copied without checking their validity.
$ cp SYS_TBS_MEM_DIC-1-0 $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DIC-1-1 $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DIC-1-2 $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DATA-0-0 $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DATA-0-1 $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DATA-0-2 $ALTIBASE_HOME/dbs
Copy the backup of the log anchor file to its original location#
Incomplete recovery uses the backups of the log anchor files. Copy the log anchor files from the backup storage device.
$ cp /backup_dir/loganchor* $ALTIBASE_HOME/logs
Check the archive log files required for incomplete recovery#
Check which archive log files are required for incomplete recovery, as shown below:
iSQL(sysdba)> SELECT LAST_DELETED_LOGFILE FROM V$LFG;
LAST_DELETED_LOGFILE
-----------------------------------------------------
15021
Check the altibase_sm.log file, which is in the $ALTIBASE_HOME/trc directory, to verify that the backup-related log files were forcibly archived at the end of the backup.
$ ls
logfile15361 logfile15362 logfile15363 logfile15364 logfile15365
According to the results above, copy all files ranging from logfile15022 to logfile15360, which is the last archive log file in which logging was performed at the end of backup, from the directory specified using the ARCHIVE_DIR property (or a backup device) to the directory specified using the LOG_DIR property. Unlike complete recovery, when performing incomplete recovery, the duplication of log files is inevitable.
Create data files for the system temporary tablespace#
Because SYS_TBS_DISK_TEMP was not backed up, create the corresponding file.
iSQL(sysdba)> ALTER DATABASE CREATE DATAFILE 'temp001.dbf'
Perform incomplete recovery#
Perform incomplete media recovery as shown below.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE UNTIL TIME '2007-09-18:14:30:00';
Run the meta phase using the RESETLOGS option#
Because incomplete media recovery has been performed, the resetlogs option must be used when proceeding to the meta startup phase.
iSQL(sysdba)> ALTER DATABASE mydb META RESETLOGS;
Perform full database backup#
Once resetlogs has been performed, perform a complete database backup.
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO 'backup_dir';
Media Recovery Example 5: Incomplete recovery example after loss of online log files.#
Suppose that the database is running in archivelog mode, that log files numbered between #499 and #600 exist, and that log file #570 has been lost.
Recovery Procedure#
데이터 파일과 로그앵커 파일의 백업본을 원래 위치로 복사#
Copy the data files and log anchor files required to perform incomplete recovery.
Check the archive log files required for incomplete recovery#
Check which archive log files are required to perform incomplete recovery.
Perform incomplete recovery#
Apply the redo records of the log files from #499 to #569 to the database, but not the redo records of log file #570 and subsequent files.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
Run the meta phase using the RESETLOGS option#
Since incomplete media recovery has been performed, the resetlogs option must be used when proceeding to the meta startup phase.
iSQL(sysdba)> ALTER DATABASE mydb META RESETLOGS;
Perform full database backup#
Once resetlogs is complete, perform a complete database backup.
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
Media Recovery Example 6: Loss of data files for the temporary tablespace.#
In some cases, media recovery can be performed even if the database is running in noarchivelog mode. One such case is the case where data files in the temporary tablespace are lost. This is because there are no changes that must be reapplied to the database during the recovery of the temporary tablespace.
Recovery Procedure#
Startup in control phase#
iSQL(sysdba)> STARTUP CONTROL;
Create data files for the system temporary tablespace#
In the control startup phase, create a new file called temp001.dbf to replace the lost file for the SYS_TBS_DISK_TEMP tablespace.
iSQL(sysdba)> ALTER DATABASE CREATE DATAFILE 'temp001.dbf'
Startup in service phase#
Start the server.
iSQL(sysdba)> ALTER DATABASE mydb SERVICE;
Media Recovery Example 7: Loss of memory checkpoint image files.#
Suppose that the database is running in archivelog mode and that the data files that belong to the SYS_TBS_MEM_DIC dictionary tablespace have been lost.
Backup Procedure#
At the last backup, back up the full database as follows.
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
Recovery Procedure#
Check the archive log files required for complete recovery복사#
After checking which archive log files are required in order to perform complete recovery, copy these files to the archive directory. The method of determining which archive log files are required is to check the headers of the checkpoint image files that are to be recovered. The header information can be viewed using the dumpdb utility, which will yield results similar to those shown below.
$ dumpdb -j 0 -f SYS_TBS_MEM_DIC-0-0
[BEGIN CHECKPOINT IMAGE HEADER]
Binary DB Version [ 7.3.0 ]
Redo LSN [ 4, 2257550 ]
Create LSN [ 0, 657403 ]
[END CHECKPOINT IMAGE HEADER]
The above results indicate that the logfile4 archive log file and subsequent files are required in order to restore the database using the backup data files.
Copy the backup of the memory checkpoint image file to its original location#
The backed up stable data file should be copied to the original position. Supposing that the backup file is SYS_TBS_MEM_DIC-0-0, copy the backup data file as shown below:
$ cp /backup_dir/SYS_TBS_MEM_DIC-0-0 $ALTIBASE_HOME/dbs;
Copy the backup of the memory checkpoint image to the stable checkpoint image file number#
In the results of $ALTIBASE_HOME/bin/dumpla loganchor0, check the "Stable Checkpoint Image Num" tablespace attribute for the tablespace named SYS_TBS_MEM_DIC.
$ dumpla loganchor0
[ TABLESPACE ATTRIBUTE ]
Tablespace ID [ 0 ]
Tablespace Name [ SYS_TBS_MEM_DIC ]
New Database File ID [ 0 ]
Tablespace Status [ ONLINE ]
TableSpace Type [ 0 ]
Checkpoint Path Count [ 0 ]
Autoextend Mode [ Autoextend ]
Shared Memory Key [ 0 ]
Stable Checkpoint Image Num. [ 1 ]
Init Size [ 4 MBytes ( 129 Pages ) ]
Next Size [ 4 MBytes ( 128 Pages ) ]
Maximum Size [ 134217727 MBytes ( 4294967295 Pages ) ]
Split File Size [ 1024 MBytes ( 32768 Pages ) ]
[ MEMORY CHECKPOINT PATH ATTRIBUTE ]
Tablespace ID [ 0 ]
Checkpoint Path [ /home/altibase_home/dbs ]
[ MEMORY CHECKPOINT IMAGE ATTRIBUTE ]
Tablespace ID [ 0 ]
File Number [ 0 ]
Create LSN [ 0, 2028 ]
Create On Disk (PingPong 0) [ Created ]
Create On Disk (PingPong 1) [ Created ]
Since the backup datafile number is [0] and the current stable datafile number is [1], copy the tablespace as shown below.
$ cd $ALTIBASE_HOME/dbs
$ cp SYS_TBS_MEM_DIC-0-0 SYS_TBS_MEM_DIC-1-0
Startup in control phase#
iSQL(sysdba)> STARTUP CONTROL;
Perform media recovery#
Perform media recovery during the control phase.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE;
Startup in service phase#
Now that media recovery is complete, restart recovery will occur automatically.
iSQL(sysdba)> ALTER DATABASE mydb SERVICE;
Media Recovery Example 8: Incomplete recovery example after disk tablespace deletion.#
Suppose that the database is running in archivelog mode and that a user deleted the USER_DISK_TBS tablespace by mistake. The tablespace was deleted at 22:30 on April 6, 2007. The database will be restored to its state as of 10 minutes before that time, when the tablespace existed.
Backup Procedure#
When the last backup was performed, the full database was backed up, as shown below:
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO '/backup_dir';
iSQL(sysdba)> ALTER SYSTEM SWITCH LOGFILE;
Recovery Procedure#
Copy the backup of the data file to its original location#
Copy the backups of the data files and log anchor files that are required for incomplete recovery. Copy the data files for all disk tablespaces in the backup of the database to the original location of the data files.
$ cp /backup_dir/*.dbf $ALTIBASE_HOME/dbs
$ cp /backup_dir/SYS_TBS_* $ALTIBASE_HOME/dbs
Check archive log files#
Check which archive log files are required to perform incomplete recovery.
Copy the backup of the log anchor file to its original location#
Incomplete recovery uses the backups of the log anchor files. Copy the log anchor files from the backup storage device.
$ cp /backup_dir/loganchor* $ALTIBASE_HOME/logs
Create data files for the system temporary tablespace#
Because the SYS_TBS_DISK_TEMP tablespace is not backed up, create a new data file for it.
iSQL(sysdba)> ALTER DATABASE CREATE DATAFILE 'temp001.dbf'
Perform incomplete recovery#
Perform incomplete media recovery.
iSQL(sysdba)> ALTER DATABASE RECOVER DATABASE UNTIL TIME '2007-04-06:22:20:00';
Run the meta phase using the RESETLOGS option#
Because incomplete media recovery has been performed, logs must be reset when proceeding to the meta startup phase.
iSQL(sysdba)> ALTER DATABASE mydb META RESETLOGS;
Startup in service phase#
Start the server.
iSQL(sysdba)> ALTER DATABASE mydb SERVICE;
Perform full database backup#
Because the logs have been reset, it is recommended that the full database be backed up.
iSQL(sysdba)> ALTER DATABASE BACKUP DATABASE TO 'backup_dir';