## Database and Table Space Recovery
#B database recovery
Below is the syntax of the restore command:
RESTORE DATABASE SOURCE-DATABASE-Alias {Restore-Options | Continue | Abort}
Restore-Options:
[User UserName [Using Password]] [{TABLESPACE [Online] |
TableSpace (TBLspace-name [{, tblspace-name} ...] [online] |
History file [online]}] [incremental [automatic | Abort]]]
[{USE {TSM | XBSA} [Open Num-sess sessions] |
From Dir / dev [{, DIR / dev} ...] | loading shared-lib
[Open Num-sess sessions]}]}] [TAKEN AT DATE-TIME] [TARGET-DIRECTORY]
[INTO TARGET-DATABASE-Alias] [NewLogPath Directory]
[With num-buff buffers] [buffer buffer-size]
[DLREPORT FILE-NAME] [Replace EXISTING] [redirect] [parallelism n]
[WITHOUT ROLLINK] [WITHOUT PROMPTING]
Let us study an example. To perform the recovery of the Sample database, use the following command:
(1) RESTORE DATABASE SAMPLE
(2) from C: / dbbackup
(3) Taken at 20030314131259
(4) WITHOUT ROLLING FORWARD
(5) WITHOUT PROMPTING
Let us study this command more carefully:
Indicates the name of the database image to be restored.
Specifies to read the input backup file from what location.
If there are multiple backup images in the directory, then this option will determine a specific backup according to the timestamp (it is part of the backup name).
If archive logging is enabled for the database, it will automatically be put into the pre-rolled state when it is restored to the database. This trip tells DB2 Do not make the database in front roll state.
When executing Restore, you will not be prompted.
Note that there is no keyword OFFLINE in the syntax, because this is the default method. In fact, for the RESTORE utility, this is the only way to allow the database.
# Table space recovery
Table space recovery requires quite careful planning, as it is easier to make mistakes, which makes the data in inconsistent state.
Here is an example of the table space restore command:
(1) RESTORE DATABASE SAMPLE
(2) TableSpace (MyTBLspace1)
(3) Online
(4) from / db2tbsp / backup1, / db2tbsp / backup2
Let us study this command more carefully:
Indicates the name of the database image to be restored.
It is pointed out that this is a table space restore and specifies the name of one or more tablespaces to be restored.
It is pointed out that this is online recovery. Note: For the user tablespace, online recovery is allowed to recover offline recovery. As mentioned earlier, for the database, only offline recovery is allowed.
Specifies the location where the backup file is entered.
Table space recovery precautions
After recovering the table space, it will always be in the pre-roll. To allow tablespaces to access and reset this state, you must have at least the table space to the minimum point in time.
(Point In Time, PIT). This smallest PIT ensures that the tablespace and logs are consistent with the content in the system directory.
Consider the following example:
Assume that at time T1 you perform full database backup, the backup includes table space myTBLS1
At time t2, you create a table myTable in the table space MyTBLS1. This will set the minimum PITBS1 recovered to T2 in the tablespace MYTBS1.
At time t3, you decide to restore the table space MyTBLS1 only from the full database backup of T1.
After the recovery is completed, the tablespace MyTBLS1 will be in the pre-rolling state. If you allow a certain point prior to the minimum PIT, the table space MyTBLS1 will lose the table myTable; however, the system directory will display the table to act in MyTBLS1. Therefore, in order to avoid similar inconsistencies, DB2 will force you to at least roll to the minimum PIT when you recover the table space.
When running a DDL statement for a table space or table space, the smallest PIT is updated. In order to determine the minimum PIT recovered in table space, one of the following two methods can be used:
Use list tablepaces show detail command
Get a table space snapshot through the Get Snapshot for TableSpace on db_name command.
In addition, the system catalog table space must be rolled to the end of the log and is offline.
# Redirect recovery
We mention that the backup file includes information about the tablespace and containers. What happens if the container in the past is no longer existed when the backup is made? If the RESTORE utility cannot find the container, you will get an error.
If you don't want to restore this backup in this location, you want to recover other locations, but use other configurations in that place. What should I do? Again, restore the backup will generate a problem in this case.
Redirect recovery solve these problems. The recovery backup process of redirection recovery is only four steps:
Gets information about the containers and tablespaces in the input backup. This task can be completed by inclining the Redirect keyword in the restore command. E.g:
RESTORE DATABASE DB2CERT from C: / DBBackup
INTO NEWDB Redirect Withnout Rolling Forward
No need to generate a database NewDB in advance
Below is the output of this command:
SQL1277N RESTORE HAS Detected That OR More Table Space Containers Are
INACCESSIBLE, OR HAS SET Their State To 'Storage Must Be Defined'.
DB20000i The Restore Database Command Complated SuccessFully.
Note: Database NewDB has been created at this time.
Review from (partially) Table Space Information Recovery Database NEWDB:
List tablepaces show detail
The tablespace has not yet been produced.
Set a new container for each table space. Table space has a logo that can get this identity from the output of the list tablespaces command. Use this logo as follows:
Set TableSpace Containers for 0 Using (file "d: /newdb/cat0.dat" 5000)
Set TableSpace Containers for 1 Using (file "d: /newdb/cat1.dat" 5000)
...
SET TABLESPACE Containers for n Using (Path "D: / NewDB2") The above command will generate a table space.
In the example above, n represents the identity of a table space in the backup. Also note that for redirection recovery, you cannot change the type of table space; that is, if the table space is SMS, then it cannot be changed to DMS.
By inclining keyword continue in the restore command, starting to restore the data itself to the new container, as shown below:
Restore Database DB2CERT Continue
Now, you have learned how redirect recovery work. It is also possible to add containers to SMS tablespaces. If you read the second tutorial of this series, you should know that in most cases, you cannot modify the SMS tablespace to add a container. Redirection Recovery provides a variety of ways.
Subregional database recovery:
In The Following Example, The Database WSDB IS Defined on All 4 Partitions,
Numbered 0 THROUGH 3. THE PATH / DEV3 / Backup Is Accessible from ALL
Partitions. The Following Offline Backup images Are Available from
/ DEV3 / Backup:
Wsdb.0.db2inst1.node0000.catn0000.20020331234149.001
Wsdb.0.db2inst1.node0001.catn0000.20020331234427.001
Wsdb.0.db2inst1.node0002.catn0000.20020331234828.001
Wsdb.0.db2inst1.node0003.catn0000.20020331235235.001
To restore The Catalog Partition First, Then All Other Database Partitions of The THE
WSDB Database from The / dev3 / Backup Directory, Issue the Following
Commands from One of The Database Partitions:
DB2_ALL '<< 0 Taken at 20020331234149 INTO WSDB Replace EXISTING ' DB2_ALL '<< 1 Taken at 20020331234427 INTO WSDB Replace EXISTING ' DB2_ALL '<< 2 Taken at 20020331234828 INTO WSDB Replace EXISTING ' DB2_ALL '<< 3 Taken at 20020331235235 INTO WSDB Replace EXISTING ' ## Database and table space # 数据 数据 前 In the previous chapter, we briefly discuss the rollforward command. In this chapter, we will discuss it in more detail. The ROLLFORWARD command allows you to return to a certain point in time; This means that this command will allow you to traverse DB2 logs and reform or revoke records recorded in the log until a particular point in time. Although the database or table space can be rolled to the smallest At any time after PIT, you can't guarantee that the end time that you select before you will keep all data consistent. We will not discuss the quiesce command in this tutorial. However, it is worth mentioning that this command can be used to set the consistency point during the regular database operation. Set These consistency points, you can always perform any point in time recovery and ensure data synchronization. Consistency points and many other information are recorded in the DB2 history file, you can use the list history command to view the file. During the front roll process, DB2 will: Find the required log files in the current log path. If you find the log, you will apply a transaction from the log file. If the log file is not found in the current path, the overflowlogpath option is used, then DB2 will search in the path specified by this option and will use this location. Log. If the log file is not found in the current path and does not use the OverflowLogPath option, the user exit is called to retrieve the log files in the archive path. The user exit is called to retrieve the log file only when the full database is currently recovering and the user exit is enabled. Once the log is in the current log path or OverflowLogPath, the transaction will be applied. Execute the rollforward command requires sysadm, sysctrl, or sysmaint permissions. Below is the syntax of the rollforward command: RollForward Database Database-Alias [user username [using password]]] [To {isotime [on all dbpartitionnums] | End of logs [On-dbPartitionnum-Clause]}] [and {company | stop}] | {Complete | Stop | Cancel | Query Status [Using Local Time]} [On-dbPartitionnum-Clause] [Tablespace Online | TableSpace (TBLSpace-name) [{, TBLSPACE-NAME} ...] [Online]] [OVLOW LOG PATH (Log-Directory [{, log-directory on dbphartitionnum db-partition-number} ...])] [Noretrieve] [Recover Dropped Table Dropped-Table-Id to Export-Directory] ON-dbPartitionnum-Clause: ON {{dbpartitionnum | dbpartitionnums} (db-partition-number [To DB-Partition-Number], ...) | All dbpartitionnums [Except {Dbpartitionnum | dbpartitionnums} (db-partition-number [To DB-Partition-Number], ...)]} Let us study an example. To perform the front roll of the sample database, you can use any of the following statements: (1) RollForward Database Sample To End of Logs and Complete (2) RollForward Database Sample To TimeStamp and Complete (3) RollForward Database Sample To TimeStamp Using Local Time and Complete Let us carefully study each statement: In this example, we will roll to the end of the log, which means that all archives and event logs will be traversed. In the end it will complete the front roll and remove any uncommitted transactions The front roll is suspended. For this example, DB2 will roll to the specified point in time. The timestamp of use must be CUT (Global Standard Time, Coordinated Universal Time), which can Calculate the local time from the current time zone. This example is similar to the previous example, but it can be used to represent a timestamp with a local time. Note that there is no keyword OFFLINE in the syntax, because this is the default method. In fact, this is the only way for the database allowed for the rollforward command. # 图 空 前 前 第 Page 2 (4 pages of) Table spaces are typically online or offline. However, the system catalog table space is an exception, which can only roll offline. Below is a table space before rollout: RollForward Database Sample To end of logs and completion TableSpace (userSpace1) Online The options in the above example have been explained in front of the database. The only new option here is TableSpace, which specifies the table space you want to roll. Table space roll consideration If the registry variable DB2_COLLECT_TS_REC_INFO is enabled, only the log files required to recover the tablespace; the ROLLFORWARD command will skip the unwanted log files, which can speed up the recovery time. The Query Status option of the ROLLFORWARD command can be used to list the log files that the DB2 has a front roll file, the next required archive log file, and the timestamp of the last submitted transaction since the start of the scheduled start. E.g: RollForward Database Sample Query Status Using Local Time After the table space time is completed, the table space is in the backup state. The tablespace or database must be backed up because all updates to it have been lost between the tablespace and the current time of the current time. ## 创 重 创 重 创 # Reconstruction Index Reconstruction Index Page 1 (1 page) If the database crashes due to some hardware or operating system reasons, then some indexes in the database restart phase may be marked as invalid. Configuration Parameters INDEXREC determines when DB2 will try to rebuild an invalid index. IndexRec is defined in the database manager and database profile. This parameter has three possible settings: System: This value can only be specified in the database profile. When IndexRec is set to this value, DB2 will find the indexrec settings specified in the Database Manager configuration file and use this value. Access: This means reconstructing invalid index when the first access index is accessed. Restart: This means reconstructing invalid indexing during database restart. ### 管理 管理 Get admin cfg Update admin cfg using Backup table space BACKUP DATABASE SAMPLE TABLESPAESPAESPAESPACE (UserSpace1) Online to "D: / DB2 /" with 1 Buffers Buffer 1024 Parallelism 1 WITHOUT PROMPTING Generate table DDL DB2LOOK -D SAMPLE -T MY_EMPLOYEE -A -E -L -X -C; DDL of statistics including the table DB2LOOK -D SAMPLE-T MY_EMPLOYEE -A -E -L -X -M -R -C; svmon 5.1 maintrcie 4 DB2 FixPak 2 ## Database Space Demand # 系统 系统 目录 表间 空间 需间 需间 需间 # 用户 用户 数据 数据 数据 空 空 空 255 lines per page 4KB page 68 bytes for management overhead, 4028 for data, can not exceed 4005 bytes, up to 500 columns. 8,16,32kb page 8101, 16293, 32677 1012 column Estimated size formula 4KB: (4028 / (Average Row Size 10) = Records_Per_Page (Number_Of_Records / Records_Per_Page) * 1.1 = Number_OF_PAGES Spatial demand for long field data Stored in a separate table object. The data is stored in a region with a size of 32 kB. Spatial demand for large object data # 空 's needs (Average index key 9) × line number × 2 Create an index temporary space requirement (Average index key size 9) × line number × 3.2 A rough estimate of the average number of keys for each leaf page is # 日 文件 文件 文件 空 (logprimary logsecond) * (logfilesiz 2) * 4096 If an unlimited record is enabled for the database (LogSecond Setting to -1), you must enable UseRexit configuration parameters. # Temporary space requirements ## Partition Database authentication Select Distinct dbpartitionnum (Empno) from Employee # 分区 键 Unded not specified, use the primary key of the primary key, the unsocambular keys use the first non-long type column. Select whether the data can be distributed uniform and often used, and can be used by column but cannot exceed 16 columns, the less performance, the better performance. The partition key cannot be changed, any unique key or primary key must contain partition key columns # 图 并 置 When you need to be associated, when you specify the partition key, the data type of each partition key must be partitioned and is called a table. The same value but there is a different type of two variables will be equipped with the same partition algorithm map to the same partition number. Such as: Integer, Smallint, Bigint Real, Float Char, varchar # 隔 级 级 The isolation level determines how to lock the data or isolate the data from other processes when data is accessed. You can pre-compile or in the application Specify the isolation level when binding to the database in a static SQL program, or you can specify it as a connection or statement option. The selected isolation level can simultaneously affect the DB2 selected lock policy and the S row lock can be held by the application. The isolation level is applied only to the read line. For changes to the changes, the application needs to get an X or W lock. Regardless of the isolation level of the application, X or W locks are not released before returning or submitted.
