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

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 !)

ORA-01078 ORA-29701

Error:

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service


$/rdbms/11gR2/grid/bin/ocssd.bin start
Segmentation Fault - core dumped


Background:

Errors observed while starting up the single instance ASM database post crash happened due to killing of pmon background process or server restart without appropriate service stop

Verifications & Resolution:

ASM instance requires oracle cluster synchronization agent services & daemon in running state.

1. Verify the css process status

$ps -ef | grep -i css
  oracle  2385     1   0 01:23:53 ?           4:44 /rdbms/11gR2/grid/bin/ocssd.bin
  oracle  2373     1   0 01:23:53 ?           0:42 /rdbms/11gR2/grid/bin/cssdagent
  oracle   655   644   0 18:38:17 pts/1       0:00 grep -i css


2. check the status of the cluster resources like diskgroup , asm instance etc.

$crs_stat -t
Name                Type                Target         State        Host
------------- ----------------  -------------  ---------- --------
ora.DG_ARCH.dg ora....up.type    ONLINE    ONLINE    ace-...tuat
ora.DG_DATA.dg ora....up.type    ONLINE    ONLINE    ace-...tuat
ora.DG_INDX.dg ora....up.type     ONLINE    ONLINE    ace-...tuat
ora....ED01.dg   ora....up.type     ONLINE    ONLINE    ace-...tuat
ora....ED02.dg   ora....up.type     ONLINE    ONLINE    ace-...tuat
ora....ED03.dg   ora....up.type     ONLINE    ONLINE    ace-...tuat
ora.asm            ora.asm.type      ONLINE    ONLINE    ace-...tuat
ora.cssd           ora.cssd.type     ONLINE    ONLINE    ace-...tuat
ora.diskmon       ora....on.type     ONLINE    ONLINE    ace-...tuat

3. check alert log of asm for any errors

cat $ORACLE_BASE/log/diag/asm/+asm/<ASM_instance_name>/trace/alert_<ASM_instance_name>.log

Thu Nov 28 01:02:08 2013
Errors in file /rdbms/11gR2/grid/log/diag/asm/+asm/+ASM/trace/+ASM_gmon_17716.trc:
ORA-29702: error occurred in Cluster Group Service operation
ORA-29702: error occurred in Cluster Group Service operation
GMON (ospid: 17716): terminating the instance due to error 29702 


4. verify the existence of parameter file for ASM & check the accessibility of disk paths, other mounted disk

cat $ORACLE_HOME/dbs/init<ASM_instance_name>.ora

asm_diskgroups ='DG_DATA','DG_INDX','DG_ARCH','DG_RED01','DG_RED02','DG_RED03'
asm_diskstring ='/dev/zvol/rdsk/db-dataP*'
instance_type='asm'
large_pool_size=12M
 

$ls -lrt /dev/zvol/rdsk/db-dataP*
total 6
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_ARCH -> ../../../../devices/pseudo/zfs@0:2c,raw
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_DATA -> ../../../../devices/pseudo/zfs@0:3c,raw
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_INDX -> ../../../../devices/pseudo/zfs@0:5c,raw
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_RED01 -> ../../../../devices/pseudo/zfs@0:6c,raw
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_RED02 -> ../../../../devices/pseudo/zfs@0:7c,raw
lrwxrwxrwx 1 root root  39 Sep  5  2012 DG_RED03 -> ../../../../devices/pseudo/zfs@0:8c,raw
 


5. if all above checks found ok then try starting up the ASM instance again using srvctl , make sure ORACLE_SID & other environment variables are set correctly

$srvctl start asm

$ps -ef | grep -i pmon
  oracle  2435     1   0 01:24:10 ?           0:00 asm_pmon_+ASM
  oracle  2518  1498   0 01:24:35 pts/2       0:00 grep -i pmon


6. if Step 5 fails then repeat the same after server restart or ask Storage / Unix Admin to check for any issues as server restart may not be feasible in most of the cases


ORA-12154: TNS: could not resolve the connect identifier specified



ORA-12154: TNS: could not resolve the connect identifier specified

ORA 12154: TNS: Listener does not currently know of service requested

Checklist:

ORA 12154 looks a generic & very much known but getting cause for this error is the trick to resolve it quickly. Below 8 point checklist will ensure all basic's are followed & 9th point will be the final one which will take you closure to the resolution

1. Verify if you have compatible client version with RDBMS db version

10g, 11g or higher oracle version client are compatible with 9i or higher database versions but 8i or lower oracle databases need specific versions of oracle clients only. 

When 32 bit oracle client software is installed on 64 bit windows server it appends its own characters/braces() in connection description and results in a exception.Such issue can be tackled by installing 32 bit client in any other drive like d: , e: but not inside c: drive program files (64)

2. Client Server has only single oracle client binary installed

Mostly seen in windows machines previous oracle clients are not cleanly removed & reflecting multiple client in the same server. Need to remove all old client along with their directory structures correctly if not done by a clean removal method & have a reboot of the system before proceeding with new client installation 

3. tnsnames.ora file contains proper TNS Entry formatting

Having correct TNS entry is the key to resolving ORA-12154 error, make it as simple as below

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 1527))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mydb)
    )
  )

If you suspect there are any missing braces or unknown issue in formatting just use below single liner

mydb=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))


and you can easily test its resolution  through TNS alias or using description itself as below

Yes this single liner will work , I am not keen to show any example snaps here as I believe you will test yourself instead of assuming it from my snaps

tnsping "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT = 1527)))(CONNECT_DATA = (SERVICE_NAME = mydb)))


4. Host which you are trying to connect is pingable & added in /etc/hosts

Coming from point 3 one of the basic check should be the resolution of hostname specified in HOST clause of TNS entry which can be an IP address or domain/DNS , LDAP or /etc/hosts resolvable entry

Whichever it is make sure it is the correct one

5. telnet Host Port is working

Checking correctness of hostname specified in HOST, we just have ping & telnet.
If it is pinging then check telnet to DB port on which listener is listening

telnet <IP> <Port>

In most of the cases ping & telnet seems to be working but error does not disappear , don’t lose the battle yet still few more points to be checked

6. DB service/Listener you are trying to connect is up & running on hosted db server

Yes this check looks obvious but do not skip it, may be listener is up on your DB server or although it is up but running with wrong IP (may be cluster IP) or hostname. So verify & compare TNS entry of the server against the client address and confirm its resolution to yourself

7. tnsping <TNS_Alias> is working from client

tnsping is a great tool to troubleshoot oracle net relevant issues , verify if it resolves through correct address description & within stipulated time

8. sqlplus <user>@<TNS_Alias>/<password> is working

Using sqlplus will isolate the issue if ORA-12154 is faced only in third party tools or application . sqlplus is an inbuilt oracle application development tool. Traversing it via tns alias will allow to replicate the issue & can be tested/fixed by yourself only rather than asking user to replicate it for you.

9. Troubleshoot using tracing 

If above all basic check has not helped then use tracing for sqlplus session by following the link, which will surely help to get the cause & can be fixed accordingly 

Before enabling tracing for the session make sure you are able to replicate the error which will get capture in the trace file along with the error around it


Let me know if all above doesn't help !!

My Popular Posts