来讨论一个recovery的问题

wangzhonnew

sounds like it will be very useful if vendors can provide a mechanism to change the block size from operating system side to enforce each flush will flush 4k or 8k or 16/32 k block to disk...
if that is the case, is it still going to be any problem with OS level backup during database run? (of course use DIO/CIO to all containers, control files and transaction logs, and always backup the log control files first, and transaction log files last -- same idea with the oracle frozen SCN, we backup log control files first to know the minbuf/lowtranLSN BEFORE the backup, and put transaction log at end because we want to make sure all changes are in transaction logs, then during crash recovery it can start with minbuf/lowtrasnLSN in the control file and rollforward to end of logs)

wangzhonnew

原帖由 wangzhonnew 于 2008-11-26 22:08 发表
huh, thanks for the info about oracle
yeah that sounds a pretty smart way to froze SCN on data page level, and "rollforward" it anyway after restore regardless whether it's being physically changed before/after the back based on the transaction log file

guess db2 not able to learn this approach because of the LOB files, don't think there's LSN on the LOB pages at all... so how oracle handle large object? there's SCN as well?

Thanks

askgyliu do you have the answer for the above question?
really wonder how oracle handle large object in this case, it will be surprising me if oracle keep SCN even for large object data pages...

hank_2008

Hello,

I am new in here, just find the topic and interested.
here is my real case, I am using the FS level copy to build test and reporting databases every day.
the key point is to make sure the database that has been build from your FS copy is comsistent ( I mean it is consistent in database point view).
here is my question to your guy how can you make sure that database is in comsistent.

wangzhonnew

原帖由 hank_2008 于 2008-11-29 07:00 发表
Hello,

I am new in here, just find the topic and interested.
here is my real case, I am using the FS level copy to build test and reporting databases every day.
the key point is to make sure the database that has been build from your FS copy is comsistent ( I mean it is consistent in database point view).
here is my question to your guy how can you make sure that database is in comsistent.

write suspend
that will flush dirty pages to bufferpool and suspend all write requests

askgyliu

Wangzhongnew的问题：
<<
理论上来讲，如果文件系统级别的备份能够前滚到某一个特定时间点保证文件系统级别的一致性，当activate数据库的时候crash recovery不是理论上也应该能够recover么？
从文件系统的备份恢复到某一时间点与操作系统断电产生的crash有什么本质的不同，从而使得文件系统备份不能被用来恢复数据库呢？
>>

我的理解是，这个问题的本质是在问，若是没有DB的介入，在数据库没停掉的情况下，普通的FS备份能否用来复原数据库。(在ORACLE中-alter database begin backup，或是DB2中-write suspect之后，FS备份是可以用来复原数据库的)

只是在这个讨论之中，我觉得大家对TERMINALOGY的理解有所不同，导致一些可能的交流问题(COMMUNICATION PROBLEM)。

下面是我从DB2 for z/OS Administration Guide找的一些资料，大家再跟DB2 for LUW，Wangzhongnew的问题比较一下。<>中是我的COMMENT。

P35: The DB2 BACKUP SYSTEM and RESTORE SYSTEM utilities invoke DFSMShsm to back up and restore the copy pools.

You can use the DB2 BACKUP SYSTEM online utility to take incremental FlashCopy&reg; backups of the data of a non-data-sharing DB2 subsystem or a DB2 data sharing group. All of the DB2 data sets must reside on volumes that are managed by DFSMSsms

The incremental FlashCopy backup feature is supported by the z/OS Version 1 Release 8, or later operating system.

<这里的FlashCopy&reg;是指用DB2 BACKUP SYSTEM这个UTILITY来取得的，跟普通STORAGE意义上的FLASHCOPY，或是普通FS备份应当是不同的概念>

P52： System-level backups taken with the BACKUP SYSTEM utility will contain NOT LOGGED objects, but they cannot be used for object level recovery of NOT LOGGED objects.

<注意这个词System-level backups。这里的System-level backups是指用(DB2 )BACKUP SYSTEM拿到的，而不是普通意义上的OS SYSTEM LEVEL BACKUP？>

P519： The principal tools for DB2 recovery are the QUIESCE, REPORT, COPY, RECOVER, MERGECOPY, BACKUP SYSTEM, and RESTORE SYSTEM utilities. This section also gives an overview of these utilities to help you with your backup and recovery planning.

<这些UTILITIES都是DB2 UTILITIES？只要用到了这些UTILITIES，某种程度DB2就被包括在certain level of DB2 involvement is expected? 就是说，不再是OS SYSTEM BACKUP而已，也不是FS LEVEL BACKUP而已？>

P520(对DISTRIBUTED DATA而言。大概是相当于DB2 EEE吧？)： Point-in-time recovery (to the last image copy or to a relative byte address (RBA)) presents other challenges. You cannot control a utility in one subsystem from another subsystem. In practice, you cannot quiesce two sets of table spaces, or make image copies of them, in two different subsystems at exactly the same instant. Neither can you recover them to exactly the same instant, because two different logs are involved, and a RBA does not mean the same thing for both of them.

In planning, the best approach is to consider carefully what the QUIESCE, COPY, and RECOVER utilities do for you, and then plan not to place data that must be closely coordinated on separate subsystems. After that, recovery planning is a matter of agreement among database administrators at separate locations.

P521： DB2 can recover a page set by using an image copy or system-level backup, the recovery log, or both.

<这里提到了两种可以用来RECOVER DB2的东西，an image copy or system-level backup。IMAGE COPY也是用DB2 UTILITY创建的？>

P521/522 有一个DB2 RECOVERY SCENARIO。

P522： create a full image copy by using the MERGECOPY utility to merge the incremental image copy with the full image copy

P522： An unsuccessful write operation occurs and you need to recover the table space. You run the RECOVER utility. The utility restores the table space from the full image copy that was made by MERGECOPY on Wednesday and the incremental image copies that were made on Thursday and Friday, and includes all changes that are made to the recovery log since Friday morning.

P522： To recover a page set, the RECOVER utility typically uses these items:
- A backup that is a full image copy or a system-level backup.
- For table spaces only, when the COPY utility is used, any later incremental image copies; each incremental image copy summarizes all the changes that were made to the table space from the time that the previous image copy was made.
- All log records for the page set that were created since the image copy. If the log has been damaged or discarded, or if data has been changed erroneously and then committed, you can recover to a particular point in time by limiting the range of log records that are to be applied by the RECOVER utility.

<这里的FULL IMAGE COPY就是RECOVERY 的 基础来着？而这个FULL IMAGE COPY也是用DB2 COPY这个UTILITY创建出来的(参考DB2 ONLINE UTILITIES - COPY)>

P534： System-wide points of consistency In any disaster recovery scenario, system-wide points of consistency are necessary for maintaining data integrity and preventing a loss of data. A direct relationship exists between the frequency at which you make and send copies to the recovery site and the amount of data that you can potentially lose.

<注意make and send copies。在P535图中，用到的是Full Copy & Incremental Copy。我假设这些是DB2 COPY UTILITY 创建出来的？也是一个FULL COPY，加LOG FILE，或是INCREMENTAL COPY>

P536： To minimize problems when recovering inconsistent data, avoid these actions.
v Do not discard archive logs you might need. The RECOVER utility might need an archive log to recover from an inconsistent data problem. If you have discarded it, you cannot use the RECOVER utility and must resolve the problem manually.
v Do not make an image copy of a page set that contains inconsistent data. If you use the COPY utility to make an image copy of a page set that contains inconsistent data, the RECOVER utility cannot recover a problem that involves that page set unless you have an older image copy of that page set that wastaken before the problem occurred. You can run DSN1COPY with the CHECK option to determine whether intra-page data inconsistency problems exist on page sets before making image copies of them. You can also specify the CHECKPAGE parameter on the COPY utility which will cause the image copy to fail if an inconsistent page is detected. If you are taking a copy of a catalog or directory page set, you can run DSN1CHKR, which verifies the integrity of the links, and the CHECK DATA utility, which checks the DB2 catalog (DSNDB06).
v Do not use the TERM UTILITY command on utility jobs that you want to restart. If an error occurs while a utility is running, the data on which the utility was operating might continue to be written beyond the commit point. If the utility is restarted later, processing resumes at the commit point or at the beginning of the current phase, depending on the restart parameter that was specified. If the utility stops while it has exclusive access to data, other applications cannot access that data. In this case, you might want to issue the TERM UTILITY command to terminate the utility and make the data available to other applications. However, use the TERM UTILITY command only if you cannot restart or do not need to restart the utility job.

<CONSISTENCY！>

askgyliu

P549: The BACKUP SYSTEM FULL utility control statement creates system-level backups that contain both logs and databases. With these backups, you can recover your DB2 system to the point in time of a backup by using normal DB2 restart recovery, or to an arbitrary point in time by using the RESTORE SYSTEM utility.

<这是不是Pythagoras所指的直接就可以START DB2？只是这里还是用的是DB2 BACKUP SYSTEM UTILITY，而不是普通的OS/FS BACKUP>

P550：When you recover your DB2 system to the point in time of a full system backup, you could lose a few hours of data, but your recovery time would be only a few minutes (because restart is fast).

<非常时期？>

P550： Preparing to recover to a prior point of consistency With some preparation, recovery to the point of consistency is as quick and simple as possible. DB2 begins recovery with the image copy or system-level backup that you made and reads the log only up to the point of consistency. At that point, no indoubt units of recovery hinder restarting.

<DB2 begins recovery with the image copy or system-level backup that you made>

P551： Establishing a point of consistency:

After copying the data, immediately establish a point when the data is consistent and no unit of work is changing it.

Use the QUIESCE utility also to establish a single point of consistency (a quiesce point) for one or more page sets. Typically, you name all the table spaces in a table space set that you want recovered to the same point in time to avoid referential integrity violations. Alternatively you can use the QUIESCE utility with the TABLESPACESET keyword for referential integrity-related tables.

QUIESCE writes changed pages from the page set to disk. The catalog table SYSIBM.SYSCOPY records the current RBA and the timestamp of the quiesce point. At that point, neither page set contains any uncommitted data. A row with ICTYPE Q is inserted into SYSCOPY for each table space that is quiesced. Page setsDSNDB06.SYSCOPY, DSNDB01.DBD01, and DSNDB01.SYSUTILX are an exception: their information is written to the log. Indexes are quiesced automatically when you specify WRITE(YES) on the QUIESCE statement. A SYSIBM.SYSCOPY row with ICTYPE Q is inserted for indexes that have the COPY YES attribute

QUIESCE allows concurrency with many other utilities; however, it does not allow concurrent updates until it has quiesced all specified page sets and depending on the amount of activity, that can take considerable time. Try to run QUIESCE when system activity is low

Also, consider using the MODE(QUIESCE) option of the ARCHIVE LOG command when planning for off-site recovery. It creates a system-wide point of consistency, which can minimize the number of data inconsistencies when the archive log is used with the most current image copy during recovery.

<>

P552： Preparing to recover an entire DB2 subsystem to a prior point in time using image copies or object-level backups

Under certain circumstances, you might want to reset the entire DB2 subsystem to a point of consistency.

To prepare a point of consistency:

。。。

3. Stop DB2 with the command STOP DB2 MODE (QUIESCE).

。。。

<给我印象最深的就是CONSISTENCY这个词了，而每次看到这个词都有DB2出现>

P553： Creating essential disaster recovery elements

You must take steps to create essential disaster recovery elements. For example, you must determine how often to make copies and send them to the recovery site.

1. Make image copies:
   a. Make copies of your data sets and DB2 catalogs and directories.
       Use the COPY utility to make copies for the local subsystem and additional copies for disaster recovery. You can also use the COPYTOCOPY utility to make additional image copies from the primary image copy made by the COPY utility. Install your local subsystem with the LOCALSITE option of the SITE TYPE field on installation panel DSNTIPO. Use the RECOVERYDDN option when you run COPY to make additional copies for disaster recovery. You can use those copies on any DB2 subsystem that you have installed using the RECOVERYSITE option.

<COPY utilizty。还是DB2的UTILITY？>

4. Back up integrated catalog facility catalogs:

a. Back up all DB2-related integrated catalog facility catalogs with the VSAM EXPORT command on a daily basis.
b. Synchronize the backups with the cataloging of image copies and archives.
c. Use the VSAM LISTCAT command to create a list of the DB2 entries.
d. Send the EXPORT backup and list to the recovery site.
e. Record this activity at the recovery site when the EXPORT backup and list are received.

Pythagoras

Well, I have to stop my writings, and answer some questions from askgyliu.

At the very beginning of this long post, I'd like to say I love this great discussion!  I have been working as a lead DBA, architect on DB2 for Z, since more than 5 years ago. I think my point of view on this topic is somewhat trustworthy.

Now, let's begin.

我的理解是，这个问题的本质是在问，若是没有DB的介入，在数据库没停掉的情况下，普通的FS备份能否用来复原数据库。(在ORACLE中-alter database begin backup，或是DB2中-write suspect之后，FS备份是可以用来复原数据库的)

Modern disk tech has evolved a lot, such inventions like FlashCopy, XRC, PPRC change the mode and method we used to administrate our DBMS, especially in the backup/recovery fields. Based on these techs, it is now possible to make a system level consistent point without stop running DBMS or I/O suspension. And this possibility gives us wide choices of DR solution. A typical DR scenario is DBMS sudden crash/failure without beforehand I/O suspension, however, anyway, we still can recover it, using files backup which is taken out of DBMS control and involvement. Of course, this files backup is NOT a "ordinary" one, for instance, it's a disk mirror.

P35: The DB2 BACKUP SYSTEM and RESTORE SYSTEM utilities invoke DFSMShsm to back up and restore the copy pools.
You can use the DB2 BACKUP SYSTEM online utility to take incremental FlashCopy&reg; backups of the data of a non-data-sharing DB2 subsystem or a DB2 data sharing group. All of the DB2 data sets must reside on volumes that are managed by DFSMSsms
The incremental FlashCopy backup feature is supported by the z/OS Version 1 Release 8, or later operating system.
<这里的FlashCopy&reg;是指用DB2 BACKUP SYSTEM这个UTILITY来取得的，跟普通STORAGE意义上的FLASHCOPY，或是普通FS备份应当是不同的概念>

OK, maybe there are some diffenrence between BACKUP SYSTEM-trigger FC and "ordinary" FC. What does BACKUP SYSTEM really do?
1,Take an X lock to ensure no other backup utility. (We ignore.)
2,Suspend 32K page writes created prior to V8 NFM(New Function Mode). (The reason is what wangzhonnew told us, but this is NOT necessary, because V8 NFM allows to make CI size=page size, and you can avoid this suspension by REORG thi object in NFM. We ignore.)
3,Suspend data set creation, deletion, rename, and extension. (They are not I/O, just for the purpose of make file catalogs consistent. We ignore.)
4,Suspend system checkpoints. (Because BACKUP SYSTEM utility will record some info into DB2 catalog and BSDS, like a checkpoint does. We ignore.)
5,Prevents data set from pseudo close.(Nothing related to I/O. We ignore.)
6,Records the RBLP RBA or LRSN in the header page of DBD01 and writes the header page to DASD. (The purpose is to record some control info into DB2 catalog to mark this occurence of BACKUP SYSTEM. We ignore.)
7,Updates the BSDS with the system backup information. (The purpose is to record some control info into DB2 BSDS to mark this occurence of BACKUP SYSTEM. We ignore.)
8,Invokes DFSMShsm to take a FlashCopy of the "database" copy pool. (Just FlashCopy all disk volumes included in the data copypool. Nothing different from an ordinary FC.)
9,Invokes DFSMShsm to take a FlashCopy of the "log" copy pool if it is a full system backup. (Just FlashCopy all disk volumes included in the log copypool. Nothing different from an ordinary FC.)
10,Resumes all suspend activities above. (We have known there is nothing related to I/O suspension.We ignore.)
11,Release X lock.
12,Issue message to indicate BACKUP SYSTEM complete. (Because FlashCopy is instantaneous, need no time to wait. FC will do background.)

P52： System-level backups taken with the BACKUP SYSTEM utility will contain NOT LOGGED objects, but they cannot be used for object level recovery of NOT LOGGED objects.
<注意这个词System-level backups。这里的System-level backups是指用(DB2 )BACKUP SYSTEM拿到的，而不是普通意义上的OS SYSTEM LEVEL BACKUP？>

They are actually the same things, from the disks or files point of view. The real explaination of this sentence is, if the system-level FC contain NOT LOGGED objects, it can't be used to object-level recovery for these NOT LOGGED objects, because LOG APPLY is not possible (there is no log for this kind of objects).

P519： The principal tools for DB2 recovery are the QUIESCE, REPORT, COPY, RECOVER, MERGECOPY, BACKUP SYSTEM, and RESTORE SYSTEM utilities. This section also gives an overview of these utilities to help you with your backup and recovery planning.
<这些UTILITIES都是DB2 UTILITIES？只要用到了这些UTILITIES，某种程度DB2就被包括在certain level of DB2 involvement is expected? 就是说，不再是OS SYSTEM BACKUP而已，也不是FS LEVEL BACKUP而已？>

Not agree. During recovery, we can of course use utilities. When backup, generally, QUIESCE, COPY, MERGECOPY,BACKUP SYSTEM will be used.
QUIESCE is not related to I/O, and with DB2 9 new features, QUIESCE is NOT so important as it was. In fact, few Z customers use this utility in their production.
COPY is used to create an imagecopy, and record some info into DB2 catalog, such as SYSIBM.SYSCOPY. Since online imagecopy is widely used, there is no difference from OS-level backup, as I said before.
MERGECOPY is a backgroud utility, to make full IC(or incremental IC) and incremental IC consolidated together.
BACKUP SYSTEM, we have discussed above, nothing related to I/O suspension and DB2 involvements only for marking the occurence of the utility execution.


I have to create a new post at the current tail of this topic, continuing to reply.

[ 本帖最后由 Pythagoras 于 2008-12-1 15:15 编辑 ]

askgyliu

<这个是最有意思的。set log suspend, not "write suspend"? >

p578： Using backups from FlashCopy

You can use FlashCopy to recover your DB2 system.

To recover a DB2 system to the point in time of a backup that FlashCopy creates, you must perform backup and recovery steps. For more information about the FlashCopy function, see z/OS DFSMS Advanced Copy Services.

1. Back up your system:

   a. Issue the DB2 command SET LOG SUSPEND to suspend logging and update activity, and to quiesce 32KB page writes and data set extensions. For data sharing systems, issue the command to each member of the group.

   b. Use the FlashCopy function to copy all DB2 volumes. Include any ICF catalogs that are used by DB2, as well as active logs and BSDSs.

  c. Issue the DB2 command SET LOG RESUME to resume normal DB2 update activity. To save disk space, you can use DFSMSdss to dump the disk copies that you just created to a lower-cost medium, such as tape.

2. Recover your system:

a. Stop the DB2 subsystem. For data sharing systems, stop all members of the group.

b. Use DFSMSdss RESTORE to restore the FlashCopy data sets to disk. See z/OS DFSMSdss Storage Administration Reference for more information.

c. For data sharing systems, delete all CF structures that are owned by this group.

d. Start DB2. For data sharing systems, start all active members.

e. For data sharing systems, execute the GRECP and LPL recovery, which recovers the changed data that was stored in the coupling facility at the time of the backup

[ 本帖最后由 askgyliu 于 2008-11-29 12:37 编辑 ]

askgyliu

<跟上一楼基本一样>

P582:

Recovering without BACKUP SYSTEM

You can recover your DB2 system if you do not use the BACKUP SYSTEM utility to produce backups

The following procedures outline this recovery method.

1. Prepare for recovery.

  a. Issue the DB2 command SET LOG SUSPEND to suspend logging and update activity, and to quiesce 32KB page writes and data set extensions. For data sharing systems, issue the command to each member of the data sharing group.

  b. Use the FlashCopy function to copy all DB2 volumes. Include any ICF catalogs that are used by DB2, as well as active logs and BSDSs.

  c. Issue the DB2 command SET LOG RESUME to resume normal DB2 activity.

  d. Use DFSMSdss to dump the disk copies that you just created to tape, and then transport this tape to the remote site. You can also use other methods to transmit the copies that you make to the remote site.

2. Recover:

  a. Use DFSMSdss to restore the FlashCopy data sets to disk.

  b. Run the DSNJU003 utility by using the CRESTART CREATE, SYSPITR=log-truncation-point control statement. The log-truncation-point is the RBA or LRSN of the point to which you want to recover.

  c. Restore any logs on tape to disk.

  d. Start DB2.

  e. Run the RESTORE SYSTEM utility using the RESTORE SYSTEM LOGONLY control statement to recover to the current time (or to the time of the last log transmission from the local site).

askgyliu

引用了这么多的手册资料，是为了大家都是在同一页(AT THE SAME PAGE)，在有共同理解的基础上讨论问题。

只用STORAGE技术实际上确实是可以用来RECOVER DATABASE，只是目前来说好象不是象普通的OS/FS备份那么简单。我见过的就是GLOBAL MIRRORING而已。我自己不是做GLOBAL MIRRORING的，但据我了解，用GLOBAL MIRRORING来创建可用的DB SNAPSHOT时，也是有IO SUSPEND以得到一个CONSISTENT DB IMAGE，在GLOBAL MIRRORING部分，所以对当前的数据库没影响。只是目前这种技术还是挺贵挺复杂的。

[ 本帖最后由 askgyliu 于 2008-11-29 19:50 编辑 ]