Tuesday, December 17, 2013

ORA-01882: timezone region %s not found

Error:  ORA-01882: timezone region %s not found



When: accessing dba_scheduler_jobs view

Where: Pl Sql developer

Cause: Inappropriate values of the time zone for the columns with data type as TIMESTAMP. 

Initial suspect may goes to NLS or TIME_ZONE session parameters as this issue appears in third party tool only

col START_DATE  for a40
col END_DATE for a39
col LAST_START_DATE for a39
col LAST_RUN_DURATION for a39
select owner,JOB_NAME,START_DATE,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,SCHEDULE_LIMIT,MAX_RUN_DURATION from dba_scheduler_jobs;


OWNER  JOB_NAME START_DATE LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE SCHEDULE_LIMIT MAX_RUN_DURATION
---- ------- ------- --------- ---------- ---------------- -------------- ---------------  ------------- ----------------- 

SYS   PURGE_LOG  28-MAY-10 03.00.00.600000 AM PST8PDT 16-DEC-13 03.00.00.296403 AM PST8PDT +000000000 00:00:01.137385  17-DEC-13 03.00.00.300000 AM PST8PDT
                                                                



PST8PDT TZ found to be causing error


Solution:




1. Use below time zone conversion as a workaround for all columns with TIMESTAMP data type which appeared to be giving problem


select OWNER,job_name,JOB_ACTION,start_date at time zone 'Asia/Calcutta', LAST_START_DATE at time zone 'Asia/Calcutta' , STATE from dba_scheduler_jobs ;



2.Modify the value to reflect correct timezone value



exec dbms_scheduler.disable('PURGE_LOG');
exec dbms_scheduler.enable('PURGE_LOG');
exec dbms_scheduler.run_job('PURGE_LOG');


Above three activities will reset the column values for START_DATE,NEXT_RUN_DATE but LAST_START_DATE wont get reset. 

To reset LAST_START_DATE value perform below steps :


Identify the ddl for dba_scheduler_jobs view to get the exact internal view being used to populate LAST_START_DATE column

set long 9000
select dbms_metadata.get_ddl('VIEW','DBA_SCHEDULER_JOBS') from dual;


Identify the value from internal view sys.scheduler$_job

select last_start_date from sys.scheduler$_job where  PROGRAM_ACTION like '%PURGE_LOG%';


Update the value & set as null

update sys.scheduler$_job set LAST_START_DATE='' where  PROGRAM_ACTION like '%PURGE_LOG%';









Verify if no column values have wrong time zone & re-try from third party

Monday, December 9, 2013

preparing for oracle certification

scope of this document is to highlight the benefits of certification and its preparation , it does not target to specific exam certification code or syllabus but to provide hints to prepare them all.it can be referred by all technology aspiring certified professionals and not just oracle

Oracle certification is an educational program by oracle university to increase the oracle user base in the form of profiles like  dba , developers , architects , business analysts which has following benefits




1.Self pride 
"I am an oracle certified professional/expert/master" these few words itself has so much of weight & efforts behind it. Certification adds on the self pride for sure if done in proper way else it is just an another piece of paper (will explain it why , soon..)

2.Knowledge 
Certified means you have acquired all the professional level knowledge which oracle has decided to share for its product invented component (product is a marketing word & may not upholds the spirit of this composition hence striked it ! ). knowledge is the only precious thing to be earn from certification and rest all like certificate/logo/a line in CV are only matters for impression


3.CV strengthening 
Over 80% of certified DBA's have done this to make their CV unique & impressive .. I was one of them initially. Catch here ! if many of those 80% certified DBA's apply to the same famous company then how it will look unique.Yes it will (read and understand last line of previous paragraph) however rest of the DBA's do prefer to skip the certification and go as they get experience which is also good thing but it needs more hands one exposure to multiple scenarios

4.Interview certainty 
if you are certified then you know almost all the topics on which questions would arise which adds to certainty rather than just following the interview trend and mostly asked questions. Interview trend means setting certain time frame for interview say 2 months and starting appearing for it as the call comes wherein initial few interviews would be equivalent to rejection but will give the insight of questions being asked and same can be prepared for future.With this approach you could loose top most opportunities and need to satisfy on secondary jobs



5.Subject Expertise 
Huge number of certifications are available & each of them has a scope to get expertise in it
You can see a Security Oracle DBA , Performance Tuning Expert , Backup/Recovery database administrator or just a simple Oracle DBA (knowing upto certain level but little less than mastery in all topics , which i prefer although people may call "Jack of all trades, master of none" but think your self an all-rounder ! ). While preparing for certification you may build up an interest in certain topics and starts thinking of it more or putting more efforts to it which will make you subject matter expert for the same in future


6.Decision Making 
Knowledge and confidence gained throughout the learning path of certification gives its result in decision making while deciding on technical scenarios like new setup architecture  , backup methodology , performance / scalable approach , cost effectiveness  , upgrade / migration planning and many more. 

I had experienced it in my initial stages and you must read it to get exact meaning of this article.

"A group of managers discussing on migration plan to move a primary & standby database setup to new servers with almost 2 days of business downtime and which seems to be on its final stage of freeze.I was just invited to know my responsibility as a Jr. DBA which was nothing but starting up the database and few post checks after offline cloning from backup to new servers.I was just shocked to know the plan & felt lucky for them to be a part of the late invited discussion.i said 'Being a primary standby database & only server migration without version upgrade it can be done in 30 minutes of downtime'. They said, 'What?How?'. obviously it was simple answer for a certified DBA, 'make use of data guard setup , migrate standby database first to new server then switch over the primary to new standby and repeat the same for old primary, downtime required would be equal to switching over the db & pointing applications to new server/ip's. That's it ! '

A business benefits resides in every smart decisions we make with minimum risk and technical support. knowledge is the key for it which can be achieved through proper certification preparation only.

All above benefits can be enjoyed only if proper path is followed.

Many of the DBA's prefer the fast track approach for certification just by learning the question/answers of examination which makes them certified for sure but they don't enjoy it before , during & after the certification too.

Certification with proper way can be done as below :


   1. Identification
Certification learning path provided on http://education.oracle.com will guide you to select an appropriate certification path and identification of exams , syllabus , passing criteria etc.
This phase mostly matters at the start of the carrier or during switch. Suitable technology with interest to be selected to attain proficiency in future as a long term goal.Certification path includes set of exams which are interdependent according to which associate/professional/expert/master levels can be achieved.Accurate planning is required to attempt all such exams at specific intervals without any distraction till desired level is attained.

 2. Syllabus
Every exam has its syllabus listed with topics & sections.chances of certain questions being asked although it is relevant to syllabus but may look irrelevant due to its scenario based design which demands out of syllabus knowledge as well like UNIX , backup etc.

 3. Study material
Oracle provides the student guide books for its classroom training , same can be referred as a basic by joining oracle partnered institutes. As a low cost approach you may download .pdf of it or borrow a book from your colleague and take printouts from office printer itself (100 pages daily to escape from log capture and print four pages on single page both side which will save trees as well) & do the spiral bind.
In addition to student guide author book/exam guides can be referred ( be aware to purchase the books of standard publications only like McGraw-Hill Oracle press ).

  4. Lab Practice
You can have a test setup on set of machines or on a lower environment server to play with.
Reading & practicing the same will be remembered forever.Make sure when you install the binaries on server do not use enterprise edition as it may impact licensing or try to bring down the db once done with the practice 

 5. Reading 
Reading is the best resolution accompanied by understanding which can keep you away from all forms of problems in exam and work.Reading the study material not only boosts your knowledge about the topic but also gives you the power to think along with confidence during crunch time.Reading consistently improves your concentration and yields better results in every good thing you attempt.

 6. Revision

Revising all that you have prepared for exam is necessary because the time span in which complete study is done is long enough to forget. Revision helps to recollect everything required before appearing for certification exam and helps to gain confidence at right time.

 7. Exam Questions

Important phase before appearing an exam and must know the previous exam questions & identify the correct answers for the same.One should not jump to this stage skipping above all as it may land up you in crisis. Appearing exam directly after learning only questions and answers will freeze your mind if questions gets twisted and no other option than loosing the marks, having done book reading & practice as a backup will find logical way to the problems and unknown issues.

 8. Exam Schedule
Plan the exam schedule well in advance at least 2-3 weeks before appearing, get enough spare time before appearing the exam to keep yourself calm and prepared. plan within such a period where working environment is stable and no major tasks are assigned.
Before appearing for exam just ask this question to yourself , "Have i done full preparation ? does anything left which will be blamed by myself if i fail?" if any doubt get the exam rescheduled till you get positive answer from yourself.



I have followed above approach without any deviation and did not came across any failure (till now at least and able to share these on completion of my 10th certification exam !)