I am lohith working on DB2 DBA administration from past 5 yr.
Let me explain the DB2 DBA real time experience and the problem faced by me and the solution for the problem.
General DB2 DBA responsibility.
•Creating primary database storage structures (table spaces) once developers have designed an application.
•Modifying the database structure, as necessary, from information given by application developers.
•Manage Backup, Security, and Controls of databases
•Enrolling users and maintaining system security.
•Controlling and monitoring user access to the database.
•Monitoring and optimizing the performance of the database.
•Responsible for support of DB2 databases as per company procedures and policy.
•Be engaged with application vendor support till problem resolution
•Fill and close all assigned TTs correctly and keep DB documentation Files updated.
•Restore DB and application server whenever required
•Manage Storage & IO performance issues related to databases
•Designing of logical and physical structure of database (storage parameters)
•Ensuring that work is in compliance with IBM DB2 License agreements.
•Database tuning and manage Production & Development Environment
•Responsible to plan, document and implement new data base management system software and release upgrades after approval from company.
•Monitor the effectiveness and efficiency of data base systems operation and recommend and lead implementation of performance improvements for data base systems and user applications
•Implement and maintain all procedures for backup and recovery of data base management systems, including audit logs of all data base activity.
•Assists in file design, file maintenance and file space management related to data base management systems
I was not given proper KT from previous DB2 DBA. so all that was in document.
So I dent have full information of the database and work process. one day when the datawarehosue process was going one. the new unix admin was not aver of all the files in the Unix system. So to create some space for the next month. He deleted some files from the UNIX db2 server.
“The expect problem was the datawarehosue data loading was going one whole night .may be 400 to 500 GB data loading was going one. Due to the more transaction process. the transaction logs file size was increasing and finally the mount point got full 100% space and the data load got stopped.”
so the Unix admin thing that the some space has to be created by deleting the files. so thinking that the log files are not required. He deleted these log files.(it was circular log file).so the database was crashed.
The database was too big .it was nearly 600GB.it was my first time handling such a big database .i was scared and was no other option to bring the database up and running by the morning.
So I started googling the things .how to recover the database .so got the inform abt log files
There are two types of log life.
1.circular logs are created when the database is in offline backup more(full database backup is taken).The log will use the same log files circular till the transaction is completed. so if we have 12 logs .so transaction will use 1,2nd …then 12..thena gain 1,2nd..ext. so never try to delete these logs.
2.Archive logs are created when the database is in online backup mode(online DB backup is taken may be incremental or delta backup).the log files will be create for every transaction and it will not use the old log back for new transaction. means 1,2, 3..12…then 13 new will be created.14,15,16..etc.Here we can delete the old log files. But we should know the last active log file. Check it from DBMCFG file.
Now I got the exact problem that we have deleted circular log file. so need to restore the DB from backup. I was taking DB back weekly one. since there was no day to day transaction happening in our DWH. so we have full DB backup in the tape. i copied the DB backup from tape to Unix server. Then checked that the back is perfect or not. using the below command
db2ckbkp SAMPLE(sample is the database backup name)
The I got confirmation that it is successful. means the backup is good.
The I started the restoration of the database using the below command.
db2 restore db sample from /dev3/backup/ SAMPLE(sample is the database backup name)
Since the database backup is offline backup. i lost 2 days data .the database restoration completed after 24 hours but the database was up and running. i was fully happy and 2 days data lose was not a matter .we re-process the DWH again to get the 2 days data.