The post reminded me of my own fail happened several months ago.
I was in a performance testing project on a customer’s environment. It was RAC and included two DBs plus two standbys. In the process of testing it was necessary to rollback changes made to the databases during one test run. Flashback database was chosen as a way to do that. The approach was tested by DBAs and we’ve started working according to our plan. Unfortunately Flashback didn’t work out very well. Minor issues with available space in fast recovery area were solved, but more importantly the procedure took valuable DBA’s time and made our project highly contended on shared resource That was very bad because we were out of the plan by a week (or two) already. So after pushing hard we were given direct access to the databases to not disturb DBAs and to do everything we need by ourselves. That “we” was me of course.
Now little background how do I feel about mine DBA skills. Obviously I can do everything in some time – and depending on the topic the time it will take me to do something will vary from seconds to days or maybe weeks. With the backup and recovery I was at the level of absolute zero: I’ve never done PITR; I’ve never done flashback DB; I’ve never used RMAN. So you can imagine that for me doing all that wasn’t easy and it wasn’t fun either due to time pressure.
Anyway after I’ve tried it and it worked pretty easy I’ve decided that doing it manually is so boring and time consuming that I need a script. Especially since I knew I will need to repeat the same operation several dozen times in the next weeks.
Now little background how do I feel about my scripting abilities. Obviously I can do everything but my scripting skill is on a level similar to DBA. So I spent several hours to come up with something that will do the right thing in reasonable time. It was actually several scripts with one main entry point that called other scripts concurrently. The problem was: nor did I have time to test, nor did I have a test environment. So I was very nervous when I launched it like this:
./flashback.sh "20120405 18:00"
And I was so happy it finished without any error! It actually flashed back all 4 databases. The only problem was, as it turned out, the date was not correct. The time portion was ignored. I didn’t forget to include double apostrophes on the command line, but I forgot to add them inside the main script, which passed the parameter to the next scripts. And then, in turn, the date became two parameters instead of one. So when actually flashback database to timestamp command was issued the date was without time!! OMFG that was 1 day of work lost…
Now I know that it was too stupid to try it this way. Anyway after fixing the error the script was successfully used many times and usually worked about 20 minutes – that was good. Well, sometimes it didn’t work due to ORA-600, but that’s a different story.