Monday, March 11, 2013

Few days ago my datawarehouse division asked to check and eventually speed up their nightly loading jobs:
they said already in the past
different solutions were implemented by other collegues and would like to know if it was possible to implement or adopt new feature to solve their problems.
I asked which were the most important jobs to speed up and which Oracle schemas they used.
It was clear and evident those hundred of jobs were created years ago and no one wanted to manage them.
Several shell scripts scheduled by Sun Grid Engine (now Oracle Grid Engine) using different sql scripts, several schemas and logging in many different ways:
of course any kind of documentation was available or ever written.
To be concise using find and grep utilities I was able to find those jobs and begin my analisys:
first of all I would like to know which tables are used and started to analyse them.
In the analysed schema no index was present and I also verified all tables used by those jobs were created using the NOLOGGING storage option and that was good.
At the same time I asked if they knew what NOLOGGING meant from a backup point of view:
those tables were dropped and recreated every night, but just because they were based on the NOLOGGING option, those operations were not saved in any redo log, any archived redo log, any backup and so there was no way to recover them in case of a media failure.
It was time to view inside the shell script... which called a sql script in which there were the following lines code:

It was evident that:1) all inserts were still logged:
When you set the NOLOGGING storage option during the creation of your table minimal redo information are generated during this creation whereas you can suppress some kinds of redo operations performed on it. If the database or tablespace were created using FORCE LOGGING mode, then any kind of direct path is logged, regardless of the logging settings of your tables. Every DML statement is not affected by your NOLOGGING setting: it will be always logged, generating it's usual redo information. It is possible to minimize redo information for direct path operations performed on the tables with NOLOGGING option: so the old script issued a common DML statement and it was naturally logged;2) all the extents allocated for the tables needed to be recreated as well:
Every time a table is dropped and recreated Oracle has to de-allocate every allocated extents and then reallocate them one by one during the next massive INSERT statement;3) I had to ask again whether or not datawarehouse division had completely understood the meaning and all the considerations of using NOLOGGING option in their backup strategies:
In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the
redo data is not fully logged. Therefore, if you cannot afford to lose the database object and their
new data, then you should take a backup after the NOLOGGING operation and avoid errors such as:

2 comments:

Hi there,I have found your blog, which I really like it. I enjoyed to read your posts.I was wondering if you would be interested in sharing your posts and ideas on Glipho? It's a quite new social publishing platform, where you can connect to every social network accounts (Facebook, Twitter, Instagram, YouTube, etc). Really easy to use it and communicate with your followers. In additional, you are able to import your posts too.Please, have a look and take a tour to know more about Glipho.If you would like to set up your account, please do not hesitate to ask me for further information.We are always delighted to get any feedback about the things which do or don’t work for you.I hope you will join to Glipho community soon. It's so much fun! :)All the best,Monika