1 / 24

Ingres MDB Housekeeping

Ingres MDB Housekeeping. Script for General MDB Housekeeping Revised April 2 2006. Mission. This presentation explains the standard Ingres housekeeping operations packaged in the housekeeping script supplied with the MDB.

Roberta
Télécharger la présentation

Ingres MDB Housekeeping

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ingres MDB Housekeeping Script for General MDB Housekeeping Revised April 2 2006

  2. Mission • This presentation explains the standard Ingres housekeeping operations packaged in the housekeeping script supplied with the MDB. • You are expected to configure and adapt this script (at a minimum you should incorporate your preferred backup tool). • It is best practices to run an Event Agent on the MDB machine. This script will use it if present!

  3. Overview • Performance is paramount to all mission critical applications. In order to help keeping Ingres performing at its best, we put together a script that helps performing housekeeping tasks such as: • USERMOD - useful utility for maintaining user tables. Improves the performance of query processing • OPTIMIZEDB - generates statistics that are used by the Ingres Query Optimizer to select an efficient query processing strategy • CKPDB - checkpoints a database • Then moves CHECKPOINT, DUMP and JOURNAL files not needed to recover the MDB from the current (i.e. last) CHECKPOINT sequence • Windows only at the moment

  4. Optimization and Backup © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  5. Optimization and Backup • As you saw in Proper Care and Feeding of your MDB, Optimization and Backup are two very important aspects • The first step here is to optimize the MDB and then checkpoint it: • usermod -umdbadmin -online mdb • optimizedb -zk -zw -umdbadmin mdb • ckpdb -umdbadmin mdb

  6. Finding MDB Locations © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  7. MDB Locations • It is fundamental to find CHECKPOINT, DUMP, and JOURNAL locations in order to keep files needed to recover from the LAST checkpoint sequence online • We parse the output (refer to Proper Care and Feeding of your MDB for examples) of infodb mdb to find those locations. So, you will see commands like the ones below in the script: • infodb mdb | findstr \ckp > .\II_TEMP.TXT • for /F "tokens=3,4,5,6,7,8*" %%i in ('type .\II_TEMP.TXT') do set II_CHECKPOINT=%%i %%j %%k %%l %%m %%n • @echo Checkpoint files for the MDB are located at: %II_CHECKPOINT%

  8. MDB Locations • As we find out locations, we create OFFLINE folders for the files that are not needed ONLINE – i.e. files that can be moved to OFFLINE media. Example: • @echo Creating OFFLINE folder for CHECKPOINT files... • for /F "tokens=1 delims=\" %%i in ('echo %II_CHECKPOINT%') do set II_DELIM=%%i • if not exist %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB md %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB • set CKP_OFFLINE=%II_DELIM%\OFFLINE\CKP\DEFAULT\MDB

  9. Finding Current CHECKPOINT Sequence © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  10. Finding Current CHECKPOINT Sequence • Once we are done with locations, we proceed to find which CHECKPOINT sequence is the last (i.e. current) one • We find out which sequence is the last one by parsing the output of infodb mdb: • for /F "tokens=4" %%i in ('findstr /i /c:"Checkpoint sequence" .\II_TEMP.TXT') do set CUR_CKP_SEQ=%%i • Now that we know what the current CHECKPOINT sequence is, all others can be moved to the OFFLINE folder

  11. JOURNAL History for Current CHECKPOINT Sequence © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  12. JOURNAL History for Current CHECKPOINT Sequence • Now that we know which CHECKPOINT sequence is the current (last) one, we will find which JOURNAL files are needed to recover from that CHECKPOINT sequence • First, we find out the LAST JOURNAL needed: • for /F "tokens=8" %%i in ('findstr /i /c:"Journal sequence" .\II_TEMP.TXT') do set LAST_JNL_SEQ=%%i

  13. JOURNAL History for Current CHECKPOINT Sequence • Now that we know what JOURNAL file is the last one for the current CHECKPOINT sequence, we will find which file is the first one: • for /F "tokens=7" %%i in ('findstr /i /c:"%LAST_JNL_SEQ% 1" .\II_TEMP.TXT') do set FIRST_JNL_SEQ=%%i • Now that we know what JOURNAL files are needed to recover from the current CHECKPOINT sequence, all other files can be moved to the OFFLINE folder – i.e. all files except those in the FIRST_JNL_SEQ – LAST_JNL_SEQ range

  14. Moving JOURNAL Files to the OFFLINE Folder • We use the XCOPY command to “move” JOURNAL files to the OFFLINE folder • First, we put the EXCLUDE LIST together – i.e. files that won’t be “moved” (all files except those in the FIRST – LAST JOURNAL range): • for /L %%i in (%FIRST_JNL_SEQ%,1,%LAST_JNL_SEQ%) do dir /b *%%i.jnl >> .\II_TEMP.TXT • Then, we use the exclude list in conjunction with the XCOPY command to copy files to the OFFLINE folder: • for /F "tokens=1 delims=\" %%i in ('echo %II_JOURNAL%') do set II_DELIM=%%i • xcopy *.JNL %II_DELIM%\OFFLINE\JNL\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y

  15. Moving JOURNAL Files to the OFFLINE Folder • We say we use the XCOPY command to “move” JOURNAL files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the JOURNAL location: • for /F "tokens=1* delims=%II_DELIM%" %%i in ('xcopy *.JNL %II_DELIM%\OFFLINE\JNL\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /l ^| find ":"') do del /F /Q %%i

  16. DUMP History for Current CHECKPOINT Sequence © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  17. DUMP History for Current CHECKPOINT Sequence • The next steps are similar to the ones used to find FIRST and LAST JOURNALS for the current CHECKPOINT sequence • First, we determine the LAST DUMP needed: • for /F "tokens=8" %%i in ('findstr /i /c:"Dump sequence" .\II_TEMP.TXT') do set LAST_DMP_SEQ=%%i

  18. DUMP History for Current CHECKPOINT Sequence • Now that we know what DUMP file is the last one for the current CHECKPOINT sequence, we will find out what file is the first one: • for /F "tokens=7" %%i in ('findstr /i /c:"%LAST_DMP_SEQ% 1" .\II_TEMP.TXT') do set FIRST_DMP_SEQ=%%i • Now that we know the JOURNAL files needed to recover from the current CHECKPOINT sequence, all other files can be moved to the OFFLINE folder – i.e. all files except in the FIRST_DMP_SEQ – LAST_DMP_SEQ range

  19. Moving DUMP Files to the OFFLINE Folder • We use the XCOPY command to “move” DUMP files to the OFFLINE folder • First, we put the EXCLUDE LIST together – i.e. files that won’t be “moved” (all files except those in the FIRST – LAST DUMP range): • for /L %%i in (%FIRST_DMP_SEQ%,1,%LAST_DMP_SEQ%) do dir /b *%%i.DMP >> .\II_TEMP.TXT • for /L %%i in (%FIRST_DMP_SEQ%,1,%LAST_DMP_SEQ%) do dir /b *%%i*.LST >> .\II_TEMP.TXT • for /L %%i in (%CUR_CKP_SEQ%,1,%CUR_CKP_SEQ%) do dir /b *%%i.DMP >> .\II_TEMP.TXT • for /L %%i in (%CUR_CKP_SEQ%,1,%CUR_CKP_SEQ%) do dir /b *%%i*.LST >> .\II_TEMP.TXT • dir /b *.CNF >> .\II_TEMP.TXT • dir /b *.TXT >> .\II_TEMP.TXT

  20. Moving DUMP Files to the OFFLINE Folder • Then, we use the exclude list in conjunction with the XCOPY command to copy files to the OFFLINE folder: • xcopy *.* %II_DELIM%\OFFLINE\DMP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y • We say we use the XCOPY command to “move” DUMP files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the DUMP location: • for /F "tokens=1* delims=%II_DELIM%" %%i in ('xcopy *.* %II_DELIM%\OFFLINE\DMP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /l ^| find ":"') do del /F /Q %%i

  21. Moving CHECKPOINT Files to the OFFLINE Folder © 2005 Computer Associates International, Inc. (CA). All trademarks, trade names, services marks and logos referenced herein belong to their respective companies.

  22. Moving CHECKPOINT Files to the OFFLINE Folder • Now that we know what the current CHECKPOINT sequence is, all other files can be moved to the OFFLINE folder • Put the XCOPY exclude list together for CHECKPOINT files: • dir /b *%CUR_CKP_SEQ%*.CKP >> .\II_TEMP.TXT • dir /b *.TXT >> .\II_TEMP.TXT • We use the XCOPY command to “move” CHECKPOINT files to the OFFLINE folder: • for /F "tokens=1 delims=\" %%i in ('echo %II_CHECKPOINT%') do set II_DELIM=%%i • xcopy . %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /s

  23. Moving JOURNAL Files to the OFFLINE Folder • We say we use the XCOPY command to “move” CHECKPOINT files to the OFFLINE folder because now that XCOPY was used to COPY files to that folder, files can be deleted from the CHECKPOINT location: • for /F "tokens=1*" %%i in ('xcopy . %II_DELIM%\OFFLINE\CKP\DEFAULT\MDB /i /exclude:.\II_TEMP.TXT /y /s /l ^| find ".\"') do del /F /Q %%i

  24. Questions?

More Related