Document Type | Technical Information
Category | Backup/Recovery
Applicable Product Version | 7PS02 6FS07
Document Number | TBATI052
Overview
Backup refers to protecting the database from various types of failures.
In other words, backup is a procedure or technique to recover or maintain system operation when a system failure occurs.
This document provides information about the types and meanings of Tibero backups, how to perform them, and other backup-related details in Tibero.
Method
Tibero Backup Classification
Tibero can perform database backups mainly in two ways.
-
Logical Backup
Logical backup means backing up logical units of the database such as tables, indexes, and sequences.
Tibero provides tbExport and tbImport utilities for this purpose.
-
Physical Backup
Physical backup means backing up the physical files that make up the database, typically by using file copy commands at the operating system level. Files requiring physical backup include data files and archive log files.
Online log files (=Redo log files) are meaningful only when backing up and recovering the entire database in NOARCHIVELOG mode.
Characteristics of Tibero Files
Tibero's files consist of control files, data files, temporary files, and log files.
The backup-related considerations according to the characteristics and roles of each file are as follows.
-
Control File
The control file stores the structure of the database, including the locations of all files that make up Tibero and the database name. It also records status information of data files, log files, and others used by Tibero. The control file is used to determine whether recovery is needed when starting Tibero.
Because the control file is critical for database recovery, it is recommended to specify multiple copies on different physical partitions. Maintaining multiple copies allows recovery using another control file if one partition fails.
Since the control file contains information about the current database files, it must always be kept up to date.Backup of the control file is not done by backing up the control file itself (binary form), but by backing up the CREATE CONTROLFILE statement that generates the control file. In case recovery is needed, the backed-up control file creation statement is used to recreate the control file.
To keep the control file current, you should back up the creation statement each time there is a structural change in the database, such as adding or modifying files, even if you have backed up the control file itself.
-
Data File
Data files are defined in Tablespaces and Undo Tablespaces and store database objects such as tables and indexes. A Tablespace consists of one or more data files, and each data file belongs to one Tablespace.
Since data files physically store user data, they must be backed up.
-
Temporary File
Temporary files are spaces used temporarily when the database handles a large amount of data that cannot be processed in memory.
Tibero uses data files to perform operations such as sorting for user queries and to store data of temporary tables. Data files can be defined only in temporary Tablespaces, which can have one or more data files.
Temporary files do not physically store data that constitutes the database and are used temporarily during operation, so they do not need to be backed up.
-
Log File
Log files store logs. They record changes made to data files in chronological order and are used for database recovery. Log files are divided into online log files that are reused cyclically during operation and archive log files that store reused online log files.
Archive log files are created only when operating in ARCHIVELOG mode. In NOARCHIVELOG mode, only online log files are used. Since log files may be overwritten and lost in NOARCHIVELOG mode, recovery has many limitations.
Types of Tibero Backups
The following are the types of backups provided by Tibero.
-
Backup by Mode
The backup methods available differ depending on whether the database is operated in ARCHIVELOG mode or not.
-
ARCHIVELOG Mode
This is called Online Backup or Hot Backup. You can back up the database while it is running. The files that can be backed up include the control file creation statement, data files, archive log files, etc.
Recovery can be performed to a point before the data file backup time according to the backed-up ARCHIVELOG files' timestamps.
-
NOARCHIVELOG Mode
This is called Offline Backup or Cold Backup. By default, the database operates in NOARCHIVELOG mode.
All files making up the database must be backed up only when Tibero is properly shut down. Recovery can be done up to the time the database was backed up.
-
Backup Classification by Operating Mode
Consistent Backup: Backup performed when Tibero is properly shut down.
Inconsistent Backup: Backup performed while the Tibero database is running or when it is not properly shut down.
This method is not recommended in NOARCHIVELOG mode.
Backup Configuration Principles
- Perform a full backup of all system RDBMS data daily.
- Online backup during server operation is the principle.
- Perform backups at the time of least workload to minimize system load during backup.
- Archive Log Files should be kept on the serverโs hard disk for at least 3 days, considering the importance of data and to reduce recovery time.
- During daily backups, both data and Archive Log Files should be backed up daily.
Summary of Backup Targets and Procedures
Backup Targets
Backup targets include the database's data files, control files, parameter files, and archive log files.
Temporary files are excluded from backup targets and are reconstructed during recovery.
Redo log files are not backup targets during online backup.
Procedure
-
Backup Tools
Introduce disk backup tools provided by disk vendors such as HITACHI and IBM to build an optimal infrastructure that facilitates backup and improves recovery success rates.
BCV Backup (Disk Image Copy)
Backup Execution Method
-
Backup of Control File
Physical backup of the control file is not supported. Tibero supports only logical backup of the control file. It is recommended to back up the control file creation statement when there is a structural change in the database.
The following is an example of backing up the control file creation statement to the ctrfile1.sql file in the /tmp directory.[Example] Control File Backup
SQL> alter database backup controlfile to trace as 2 '/tmp/ctrlfile1.sql' reuse noresetlogs; Database altered.The generated ctrlfile1.sql file contains the following content.
[Example] Backed-up Control File Creation Statement
CREATE CONTROLFILE REUSE DATABASE "t" LOGFILE GROUP 0 '/home/tibero/tbdata7/redo001.redo' SIZE 100M, GROUP 1 '/home/tibero/tbdata7/redo011.redo' SIZE 100M, GROUP 2 '/home/tibero/tbdata7/redo021.redo' SIZE 100M NORESETLOGS DATAFILE '/home/tibero/tbdata7/system001.dtf', '/home/tibero/tbdata7/undo001.dtf', '/home/tibero/tbdata7/usr001.dtf', '/home/tibero/tbdata7/tpr_ts.dtf' NOARCHIVELOG MAXLOGFILES 100 MAXLOGMEMBERS 8 MAXDATAFILES 256 CHARACTER SET UTF8 NATIONAL CHARACTER SET UTF8 ; --ALTER DATABASE MOUNT ---- Recovery is required in MOUNT mode. --ALTER DATABASE RECOVER AUTOMATIC; --ALTER DATABASE OPEN ; ---- Adding Tempfiles is required in OPEN mode. -- ALTER TABLESPACE TEMP2 ADD TEMPFILE '/home/tibero/tbdata5_bak/temp002.dtf' -- SIZE 512M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 30G;NORESETLOGS is created as specified in the control file creation statement. This creation statement is used after generating the trace file and when NORESETLOGS is necessary. If the REDO log files are lost or incomplete recovery is needed, you can modify it to RESETLOGS and apply it when recreating the control file.
Note
The control file creation statement does not include the creation of TEMP TABLESPACE. When Tibero is started after creating a control file, the TEMP TABLESPACE file does not exist. If you have newly created a control file, you must add the TEMP TABLESPACE. At the bottom of the control file, the related content is commented out, and you can create it by uncommenting and executing that content.
If you recreate the control file using the creation statement, it will be created with the path and file name specified in the $TB_SID.tip file.
Additionally, to view the list of control files in MOUNT or OPEN state, query the dynamic view V$CONTROLFILE.
[Example] Control File VIEW Query
SQL> SELECT NAME FROM V$CONTROLFILE; NAME ---------------------------------------------------- /disk1/c1.ctl /disk2/c2.ctl 2 selected.OFFLINE Backup
This section explains how to back up after Tibero has been properly shut down.
Before performing a consistent backup, check the control files, data files, and online log files to be backed up.
The following is how to query data files using the dynamic view V$DATAFILE in MOUNT or OPEN state. Here, MOUNT means the Tibero instance has started, and OPEN means all files defined in the control file are open.[Example] Data File View Query
SQL> SELECT NAME FROM V$DATAFILE; NAME -------------------------------------------------------------------------------- /home/tibero/tbdata7/system001.dtf /home/tibero/tbdata7/undo001.dtf /home/tibero/tbdata7/usr001.dtf /home/tibero/tbdata7/syssub_ts.dtf 4 rows selected.The following is how to query online log files in MOUNT or OPEN state.
[Example] Log File VIEW Query
SQL> SELECT MEMBER FROM V$LOGFILE; MEMBER -------------------------------------------------------------------------------- /home/tibero/tbdata7/redo001.redo /home/tibero/tbdata7/redo011.redo /home/tibero/tbdata7/redo021.redo 3 rows selected.It is recommended not to back up online log files if not in ARCHIVELOG mode.
In conclusion, offline backup means stopping the DBMS service and backing up files by hard copy.Online Backup
This section explains how to back up while Tibero is running.
When the database is running, copying data files using operating system commands is unsafe. You must notify Tibero of the start and end of backup by executing the following statements.[Example] ALTER BEGIN & END
alter tablespace {tablespace name} begin backup ... alter tablespace {tablespace name} end backupBetween the begin backup and end backup statements, the log for changes in the corresponding Tablespace increases, adding load to the database. After starting begin backup, you should complete the backup quickly and return to end backup state.
The entire process of inconsistent backup is as follows.
-
First, select the Tablespace to back up.
[Example] Online Backup (Selecting Tablespace)
SQL> select name, type from v$tablespace; NAME TYPE ---------------------------------------- ---- SYSTEM DATA UNDO UNDO TEMP2 TEMP USERS DATA SYSSUB DATA 4 rows selected.2. After querying the data files belonging to the Tablespace to back up, perform the backup using the begin backup and end backup commands. For example, assume backing up the USER Tablespace.
[Example] Online Backup (Using begin backup, end backup commands)
SQL> select f.name 2 from v$tablespace t join v$datafile f on t.ts# = f.ts# 3 where t.name='USERS'; NAME ---------------------------------------- /home/tibero/tbdata7/usr001.dtf 1 row selected. SQL> alter tablespace USERS begin backup; Tablespace 'USERS' altered. SQL> !cp /home/tibero/tbdata7/usr001.dtf /backup/ SQL> alter tablespace USERS end backup; Tablespace 'USERS' altered.
-
Backup Operation Procedures
This section explains online backup and export backup.
-
Online Backup
Example of Tibero online backup scripts (Tibero_Hotbackup.sh)
[Example] Online Backup Script Example
#!/bin/bash ################################################################################ # Shell Environment ################################################################################ TB_HOME=/home/tibero/tibero7 ARCH_DIR=/home/tibero/tbdata/archive/ WORK_DIR=/home/tibero/backup BACKUP_DIR=$WORK_DIR/`date +%y%m%d_%H%M` BACKUP_CTL=$BACKUP_DIR/control.ctl.bak LIST_DIR=$WORK_DIR/list TMP_DIR=$WORK_DIR/tmp LOG_DIR=$WORK_DIR/log LOG=$LOG_DIR/`uname -n`_`date +%m%d`.log TABLESPACES=$LIST_DIR/TABLESPACES.LIST FILE_LIST=$LIST_DIR/FILES.LIST DB_LIST=$LIST_DIR/DB.LIST TMP_FILE=$TMP_DIR/tmp_file.txt mkdir $BACKUP_DIR mkdir $LIST_DIR mkdir $TMP_DIR mkdir $LOG_DIR touch $LOG \rm $LIST_DIR/* $TMP_DIR/* echo "################################################################################" >> $LOG echo "##### Backup Begin" >> $LOG echo "##### Begin Time : `date`" >> $LOG echo "#####" >> $LOG echo "##### Datafile backup Begin" >> $LOG echo "#####" >> $LOG ################################################################################ # Tablespace List Getting ################################################################################ tbsql sys/tibero <<EOF>> /dev/null spool $TMP_DIR/tbs.tmp select tablespace_name from dba_tablespaces; spool off exit EOF cat $TMP_DIR/tbs.tmp| grep -v TABLESPACE | grep -v "\-\-\-"|grep -v selected |grep -v SQL | sed '/^ *$/d' > $TMP_DIR/tbs_l.tmp cat $TMP_DIR/tbs_l.tmp | awk '{print $1}' > $TMP_DIR/TABLESPACES.TEMP cat $TMP_DIR/TABLESPACES.TEMP | grep -v TEMP > $TABLESPACES ################################################################################ # Datafile List Getting ################################################################################ tbsql sys/tibero <<EOF >> /dev/null spool $TMP_DIR/datafile.tmp select file_name from dba_data_files; spool off exit EOF cat $TMP_DIR/datafile.tmp | grep -v FILE | grep -v "\-\-\-"|grep -v selected |grep -v SQL|sed '/^ *$/d' > $TMP_DIR/df_l.tmp cat $TMP_DIR/df_l.tmp | awk '{print $1}' > $LIST_DIR/datafile.list ################################################################################ # Begin Backup ################################################################################ cat $TABLESPACES | while read TBSPACE do tbsql sys/tibero <<EOF >> /dev/null alter tablespace $TBSPACE begin backup; exit EOF done ################################################################################ # Backup ################################################################################ # cp๋ก ๋ณต์ฌ ์ cat $LIST_DIR/datafile.list | while read DATAFILE do cp $DATAFILE $BACKUP_DIR >> $LOG done # ๋ฒ ๋ฆฌํ์ค ๋ท๋ฐฑ์ ์ผ๋ก ๋ฐฑ์ ์ #/usr/openv/netbackup/bin/bpbackup -p TIBERO_E3500-NEW -s DATAFILE -w -f $LIST_DIR/datafile.list ################################################################################ # End Backup ################################################################################ cat $TABLESPACES | while read TBSPACE do tbsql sys/tibero<<EOF >> /dev/null alter tablespace $TBSPACE end backup; exit EOF done echo "##### Datafile backup End" >> $LOG echo "#####" >> $LOG # Controlfile backup tbsql sys/tibero<<EOF >> /dev/null alter system switch logfile; alter database backup controlfile to trace as '$BACKUP_DIR/control.ctl.bak' reuse resetlogs; exit EOF # Archive Backup cp -R $ARCH_DIR $BACKUP_DIR >>$LOG # ์ด ๋ถ๋ถ์ ๋ท๋ฐฑ์ ์ผ๋ก Archive์ Controlfile ๋ฐฑ์ ์ ์ฒดํฌ ํด์ผ ํ๋ ๋ถ๋ถ #echo $ARCH_DIR > $LIST_DIR/archive.list #ls -ld /backup/control | awk '{print $9}' >> $LIST_DIR/archive.list #/usr/openv/netbackup/bin/bpbackup -p TIBERO_E3500-NEW -s ARCHIVE -w -f $LIST_DIR/archive.list # Return Error Code. Confirm Backup Success. # #if [ $? = 0 ] #then # echo "End" >> $LOG # echo " Result : Backup Success. " >> $LOG #else # echo " Error : Backup Fail. Return Code = $? " >> $LOG # exit 1 #fi echo "##### Archive and Controlfile backup End" >> $LOG echo "#####" >> $LOG echo "##### ALL Backup Complete!!!!" >> $LOG echo "##### End Time : `date`" >> $LOG echo "################################################################################" >> $LOG echo " " >> $LOG -
Export Backup
Example of Tibero Export Backup scripts (Tibero_exportbackup.sh file)
[Example] Tibero Export Backup Script Example
WORK_DIR=/home/tibero/backup PORT=9629 export TB_NLS_DATE_FORMAT="YYYYMMDD" tbsql sys/tibero <<EOF >> /dev/null spool ${WORK_DIR}/rm_list.txt select sysdate-30 from dual; quit EOF \rm -rf ${WORK_DIR}/$TB_SID_`cat ${WORK_DIR}/rm_list.txt grep -v SYSDATE | grep -v "\-\-\-"|grep -v selected |grep -SQL|sed '/^ *$/d'`* \rm -rf ${WORK_DIR}/rm_list.txt tbexport username=sys password=tibero sid=${TB_SID} port{PORT} file=${WORK_DIR}/${TB_SID}_`date +%Y%m%d_%H%M%S`.dmp log{WORK_DIR}/${TB_SID}_`date +%Y%m%d_%H%M%S`_exp.log full=script=y gzip ${WORK_DIR}/*.dmp
Backup Verification
-
Online Backup
Modify the scripts in the examples above to fit your site and verify that files are created in the backup path.
Backup Related References
-
Restarting Tibero During ONLINE Backup
If Tibero is restarted abnormally during online backup, errors occur.
Restarting Tibero During Online Backup
- Start Tibero up to MOUNT mode
- Query the V$BACKUP view to find tablespaces with STATUS โACTIVEโ
- Use the command โALTER TABLESPACE <Tablespace_name> END BACKUP;โ to end the backup
- Restart Tibero.
[Example] End backup during Online Backup
select v.file#, v.status, d.name, t.name as "tablespace_name" from v$backup v, v$datafile d, v$tablespace t where v.file#=d.file# and d.ts#=t.ts# ; FILE# STATUS NAME tablespace_name ----- ---------- --------------------------------------------------- --------------- 0 NOT ACTIVE /tibero/tibero7/database/science/system001.dtf SYSTEM 1 NOT ACTIVE /tibero/tibero7/database/science/undo001.dtf UNDO0 2 NOT ACTIVE /tibero/tibero7/database/science/usr001.dtf USR 3 NOT ACTIVE /tibero/tibero7/database/science/tpr_ts.dtf SYSSUB SQL> alter tablespace USERS end backup; Tablespace 'USERS' altered.