Coskan’s Approach to Oracle

April 27, 2007

ORA-01031: insufficient privileges

Filed under: Tips — coskan @ 8:23 am

When you set SQLNET.AUTHENTICATION_SERVICES to NONE on SQLNET.ora file on windows you will get ORA-01031 when you try to connect with the strings belowfor SQL*Plus

C:\Documents and Settings\cgundogar>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:47:42 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 10:48:00 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

idle> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges


idle> connect “/ as sysdba”
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied –password is not accepted

Warning: You are no longer connected to ORACLE.

for RMAN

C:\Documents and Settings\cgundogar>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:58:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-01031: insufficient privileges

RMAN>

To solve this problem you can must connectby giving valid username username and password

for SQL*Plus

sys@XE> connect sys as sysdba
Enter password:
Connected.
sys@XE> connect sys/passwd as sysdba
Connected.
sys@XE> connect sys@passwd as sysdba
Enter password:
Connected.
sys@ORACOS> connect sys/passwd@oracos as sysdba –over listenerConnected.
Connected.


C:\Documents and Settings\cgundogar>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:08:57 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter password:Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

sys@XE>exit


C:\Documents and Settings\cgundogar>sqlplus sys/passwd as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:09 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production


C:\Documents and Settings\cgundogar>sqlplus sys/passwd@oracos as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Cum Nis 27 11:09:15 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
sys@ORACOS> exit

for RMAN

C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target sys

target database Password:
connected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target sys/passwdconnected to target database: XE (DBID=2496353564)

RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:28 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:

connected to target database: XE (DBID=2496353564)RMAN> exit

Recovery Manager complete.


C:\Documents and Settings\cgundogar>rman target sys/passwd

Recovery Manager: Release 10.2.0.1.0 – Production on Cum Nis 27 10:56:38 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: XE (DBID=2496353564)

Edit (03/07/2009) : Check also
Metalink Note ID:730067.1
Metalink Note ID:114384.1

April 23, 2007

Oracle Idiosyncrasies

Filed under: Tips — coskan @ 5:33 pm

Again stumbling upon  internet, I found interesting staff about Oracle by Yong Huang who claims we can’t find anywhere else about what he wrote. Before checking out his web page lets prove one bug like thing he wrote about comments in Oracle.

hr@XE> drop table t PURGE;

Table dropped.

sys@XE> drop table t purge;

Table dropped.

sys@XE> create table t (a number);

Table created.

sys@XE> insert into t values (123);

1 row created.

sys@XE> /*update T set a=321 where a=123;*/

1 row created.

sys@XE> /*insert into T values=456 */

1 row created.

sys@XE> /*example comment*/

1 row created.

sys@XE> /* working comment because of the beginning with space character*/
sys@XE> –this is what a comment must begin with
sys@XE> REM and again this is what a comment must begin with
sys@XE> select * from t;

A
———-
123
123
123
123

sys@XE>—you have 3 more rows and guess what will happen when you commit
sys@XE> commit;

Commit complete.

sys@XE> select * from t;

A
———-
123
123
123
123

This case is working for all statements you execute before the comment because the interpreter runs the previous command if you don’t have space after asterix. Woooow think about what this can cause for your commented batch operations

If I were you I would be careful before commenting and would use — instead of /* */

Now its time to read all off the Oracle Idiosyncrasies

Act as if, temporarily in Oracle

Filed under: Security — coskan @ 10:37 am

While discovering the internets new trend StumbleUpon after reading Eddie Awads last entry I found a nice site Red Database Security about Oracle Security. The whitepaper about passwords has useful paragraphs for Oracle DBAs.

Here is a sample paragraph about changing a users password temporarily without knowing the original passwords by using the undocumented feature called “by values” of alter user command. Suppose you want to login as user HR but you don’t know its password and you can’t change it all you have to do is backing up the hash key of password from dba_users table. Lets look how ;

From session 1; –backup the hash key and change the password

idle> connect / as sysdbaConnected.sys@XE> select username,password from dba_users where username=’HR';
USERNAME PASSWORD

————- ——————————

HR 4C6D73C3E8B0F0DA

sys@XE> alter user hr identified by passwd;

User altered.

From Session 2; try to logon with old password

idle> connect hr/hr
ERROR:ORA-01017: invalid username/password;logon denied

Warning: You are no longer connected to ORACLE.

From session 1; –login with temporary passsword do your job and change back it

idle> connect hr/passwd;Connected.hr@XE>…..
–do you job
hr@XE>connect / as sysdba

connected
sys@XE> alter user hr identified by values ‘4C6D73C3E8B0F0DA';

User altered.

From Session 2; –vadaaaaaaa old password is still working

idle> connect hr/hr;

Connected.

Because it is undocumented try this carefully !!!

April 20, 2007

Cloning Database on Same Machine on Windows without rman clone or rman restore

Filed under: Backup/Recovery — coskan @ 1:22 pm

Yesterday I tried to clone a database instance to the same windows machine. I used rman copy datafile command for this process. The primary database is 10.2.0.1 EE with default installation database having ORACLE_SID=OCP . What I want was to create a clone database with ORACLE_SID=OCPDENE

Here is step by step what I did and what i faced

First of all I created the file hierarchy for datafiles and logfiles then I created a pfile of OCP database and backed the controlfile to trace.

sys@OCP> create pfile from spfile;
File created.sys@OCP> alter database backup controlfile to trace;
Database altered.

Then I spool a datafile copy command from directory structure of OCP to directory structure of OCPDENE database.

sys@OCP> select ‘COPY DATAFILE ‘||FILE_ID||’ TO ”D:\oracle\product\10.2.0\oradata\ocpdene\’||SUBSTR(FILE_NAME,38,50)||”';’ from dba_data_files
2 ;’COPYDATAFILE’||FILE_ID||’TO”D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\’||SUBSTR(FILE_NAME,20,50)||’

—————————————————————————————————-

COPY DATAFILE 4 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\USERS01.DBF';

COPY DATAFILE 3 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSAUX01.DBF';

COPY DATAFILE 2 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\UNDOTBS01.DBF';

COPY DATAFILE 1 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSTEM01.DBF';

COPY DATAFILE 5 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\EXAMPLE01.DBF';

Right after the spooling I modified the initocpdene.ora with the new datafile,logfile, dumpfile flash recovery area destinations and new SID.

Next modification was on listener.ora for adding listener_OCPDENE service.

The last modification was for controlfile creation script. I chosed the set database and resetlogs options like below

STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE “OCPDENE” RESETLOGS ARCHIVELOGMAXLOGFILES 16

MAXLOG

MEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO01.LOG’ SIZE 50M,

GROUP 2 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO02.LOG’ SIZE 50M,

GROUP 3 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\REDO03.LOG’ SIZE 50M

– STANDBY LOGFILE

DATAFILE

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\SYSTEM01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\UNDOTBS01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\SYSAUX01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\USERS01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\EXAMPLE01.DBF’

CHARACTER SET TR8MSWIN1254

;

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\OCPDENE\TEMP01.DBF’

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Everything was ready I connected to RMAN and start the command below

RMAN>run1> {

2> allocate channel ch1 type disk;

3> allocate channel ch2 type disk;

4> COPY DATAFILE 4 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\USERS01.DBF';

5> COPY DATAFILE 3 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSAUX01.DBF';

6> COPY DATAFILE 2 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\UNDOTBS01.DBF';

7> COPY DATAFILE 1 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\SYSTEM01.DBF';

8> COPY DATAFILE 5 TO ‘D:\oracle\product\10.2.0\oradata\ocpdene.2.0\ORADATA\OCP\EXAMPLE01.DBF';

9> release channel ch1;

10> release channel ch2;
11> };

..

..

it was time to connect to database OCPDENE but nothing could go this smooth for an Oracle Database creation:)

C:\Documents and Settings\admin>set ORACLE_SID=OCPDENE
C:\Documents and Settings\admin>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Fri Apr 20 15:27:03 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:ORA-12560: TNS: protocol adapter error

What was this ? I must take a connected to an idle instance warning not ORA-12560.

Ohhhh I forgot I was on Windows everything could go different with oracle.

The problem was that I was trying to connect a database which did not have a service defined for it. So windows behaved crazy as if it was connecting through a listener. By this cloning operation i learned the tool oradim utility. Shortly Oradim is the tool to administer database related service on windows platforms.

I created the service by the command below and this service created OracleServiceocpdene OracleJobSchedulerocpdene and OracleOraDb10g_home1TNSListenerlistener_ocpdene service.

C:\Documents and Settings\admin> oradim -NEW -SID ocpdene -SRVC OracleServiceOCPDENE -STARTMODE manual -PFILE D:\oracle\product\10.2.0\db_1\database\initocpdene.oraInstance Created

After the service creations I started the service and connect the database and startup nomount the OCPDENE database. Run the control file creation script and script wanted the archive logs which were produced during datafile copy operation. I applied them and press cancel and open the database with resetlogs option (ALTER DATABASE OPEN RESETLOGS)

Now I have a clone database on the same machine different names and directory hierarch with same names.

April 19, 2007

Io Exception: the network adapter could not establish the connection (Enterprise Manager)

Filed under: Diary, Tips — coskan @ 12:31 pm

Yesterday I gave my decision to upgrade my 10G OCA certificate to OCP. For preperation I need an EE or PE 10G database and I have two database installed (XE and 10.1.0.1 EE (SID= ORACOS) )on my Windows XP Pro Machine. The installation order was first XE next 10.1.0.1 EE and after installation I configured the listener of 10.1.0.1 with another port rather than 1521 because of the port conflicts. Moreover I configure the listener start from the XE home and I didn’t configure EM for 10.1.0.1 database from the installation till today.

When I need EE what i did is firts I changed the ORACLE_HOME to 10.1.0.1 home from XE home and I changed the ORACLE_SID to ORACOS then I started EM with

>emctl start

Enterprise manager started but when I went to EM page database status was unavailable and needs recovery.

I said recover database and this button took me the host and database credential entry page. I gave local administrator for host and sys for database unfortunately I took the error “RemoteOperationException: ERROR: Wrong password for user”. I changed the password for the local administrator bu nothing has changed after googling I found a solution at an orafaq forum thread . The solution is below and worked well and took me to the next trouble :)
Solution for “RemoteOperationException: ERROR: Wrong password for user” error :

If you are on windows: Go to Control Panel–>Administrative Tools–>Local Security Policy–>Local Policies–>User Rights Assignment–>U will see in the policy as “Log on as a batch Job–>right click–>Properties–>add user or groups–>give your OS username.

The next trouble was Io exception: The Network Adapter could not establish the connection”

I checked the listener_oracos it was working well. For a try I restarted both dbconsole and listener but the error was there again. What could be the problem ?

Here comes the solution from the survivor Metalink. Note:273758.1 . The problem is that I did a default installation for database and made changes to default listener configuration but I did not reflect it to other hard coded configuration files such as enterprise manager config files.

To solve the problem I change the default entries (oracle_listener,Port,LsnrName,ListenerOraDir ) on the file ORACLE_HOME/hostname_sid/sysman/emd/targets.xml and port entries for the parameter oracle.sysman.eml.mntr.emdRepPort, oracle.sysman.eml.mntr.emdRepConnectDescriptor on the file ORACLE_HOME/hostname_sid/sysman/config/emoms.properties

and restart the dbconsole. Guess what happened ? I started to sing the song “Yesterday All my troubles seems so far away …..”

Moral of the story; If you make a configuration change, reflect the change to all of the files related with your change.

Now it is time to prepare for OCP exam. I hope In 2-3 weeks time i will take the exam and will write the interesting OCP topics here.

April 17, 2007

Who is an Invalid DBA ?

Filed under: Diary — coskan @ 10:16 am

When i got errors from database and complaining about “what happened again” my colleagues start joking and saying “Coskan got Invalid DBA exception” :).

I googled internet and found no entry about term “Invalid DBA” so I want to explain what it is.

Lets look at who can be called Invalid DBA in my point of view;

  • Invalid DBA’s are the ones who don’t like to read Concepts guide of the RDBMS they work on. (DBA job Kick off must be always concepts guide because it is the big picture. Without seeing the whole you can’t focus on parts)
  • Invalid DBA’s are the ones who don’t have a good understanding on importance of Backup/Recovery. (If you don’t backup you can’t recover this is as simple as this)
  • Invalid DBA’s are the ones who don’t have test databases. (the ones who try on production server first.) Once one of Oracle Educators told us a story about a DBA who tried block corruption examples of training material on production database ….
  • Invalid DBA’s are the ones who say “I know OS , I can watch monitors of EM or QUEST and I can write shell scripts I dont need deep knowledge about SQL”. Without SQL knowledge you are system admin, not database admin please do not call yourself a DBA. Tuning SQL starts with writing SQL.
  • Invalid DBA’s are the ones who advice buying cpu instead of investigating bottlenecks.
  • Invalid DBA’s are the ones who are still using ratio-based methodology instead of oracle wait interface for performance optimization. (Oracle special, invalid dba exception)
  • Invalid DBA’s are the ones who say “I am behind the firewall i do not need to focus on security”. (Firewalls are good but history of world is full with conquered iron castles)
  • Invalid DBA’s are the ones who don’t have a maintenance routine for their databases. (If you take you car to service after some miles then you have to do the same for your database too. This is a must)
  • Invalid DBA’s are the ones who don’t care, about business needs or what is the business of the company. I don’t mean know it as much as the core developers know, but knowing the big picture takes you one step further for the bottlenecks.
  • Invalid DBA’s don’t like asking questions to their senior DBA’s. (Don’t forget there is always possibility of a shortest path. If there is a senior DBA in your organization feel free to consult him. This can avoid reinventing the wheel.)
  • Invalid DBA’s are the ones who always ask everything instead of reading. Is this a confliction with the above one ? No :) First you must RTFM then metalink/msdn then google and then ask.
  • Invalid DBA’s are the ones who don’t try what he learns. Guru’s can write something but it doesn’t mean it is true even the guru’s name is Thomas Kyte or Jonathan Lewis. Don’t forget! every book has an errata and every release can behave different.
  • Invalid DBA’s are the ones who put commit before running the command. Think about the wrong or forgotten where clause.
  • Invalid DBA’s are the ones who are not nerveless. Don’t forget, In a crisis environment the only thing you can lose is your job and you can always find another one, so you don’t have to worry as much as you managers. They are worried because they can’t find a new position as high as recent one after an inconsistent recovery which caused loss of millions. Stay calm.
  • Invalid DBA’s are the ones who refuse to read new features guide. Time changes and developers try to work for the better. Keep yourself up-to-date for saving time.
  • Special Invalid DBA award comes for the ones who does not read installation guides before installing. (My early career is full with this award :) )

I can not cover all but Mr Chris Foot (Oracle ACE ) has a good series about The Non-Technical Art of Being a Successful DBA . I suggest you to take a look at what he says, maybe the opposite of his thoughts can lead you about how can an Invalid DBA be :).

The Non-Technical Art of Being a Successful DBA

The Non-Technical Art of Being a Successful DBA – Database Recovery Best Practices

The Non-Technical Art of Being a Successful DBA – Application Change Management Best Practices

The Non-Technical Art of Being a Successful DBA – Application Change Management Best Practices

The Non-Technical Art of Being a Successful DBA Continues

The Non-Technical Art of Being a Successful DBA – Paranoid DBA Best Practices

The Non-Technical Art of Being a Successful DBA – A Preview of Future Blogs

The Non-Technical Art of Being a Successful DBA – Third Party Product Evaluations

The Non-Technical Art of Being a Successful DBA – Obtaining the Most Benefit From Oracle Training

The Non-Technical Art of Being a Successful DBA – Excelling at Verbal and Written Communications

One more recommended reading and editing is Oracle Best and Worst practices wikipedia prepared by Eddie Awad http://orapractices.pbwiki.com

April 16, 2007

Take the control of TIME Part1 (Introduction to Date/Time)

Filed under: Basics — coskan @ 2:46 pm

In todays world data is controlled by time and if you don’t know how to control the time, you can’t have enough control over your data. Thanks Larry Ellison and his employees for building Oracle with powerful time manipulating functions and datatypes which you have to know to administer your Oracle DB. On the next lines I’m going to try to explain Date Time datatypes and functions related them within Oracle as in a nutshell paper. At second part of this issue I will try to cover more complex operations using the functions below over date time values.

First of all I want to tell about date time data types of Oracle then I will explain how to use this data types with Oracle functions.

Oracle Date Time Datatypes


DATE Data type: Stores date and time information ( century, year, month, date, hour, minute, and second). width is fixed (7-bytes).

TIMESTAMP Data type: Extended date datatype. It differs from date by ability to store fractional seconds. Fixed width (7 or 11 bytes).

TIMESTAMP [(fractional_seconds_precision)]

–up to 9 digits precision with default 6

TIMESTAMP WITH TIME ZONE Data type: We can call it timestamp with time zone support. It includes time zone offset (the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time)). Fixed width (13 Bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

–up to 9 digits precision with default 6

TIMESTAMP WITH LOCAL TIME ZONE Data type: Another variant of timestamp that includes a time zone offset but it differs from the timestamp with time zone datatype by not storing the time zone offset as a column data. It gets time zone offset information from user’s local session time zone. (this is useful for displaying data-time info of the client in a two tier application). Fixed width (7 or 11 bytes)

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

–up to 9 digits precision with default 6

INTERVAL YEAR TO MONTH Data type: Stores a duration between a period of time by means of year and month. It is usually used for math operations (addition+ subtraction). Fixed width (5 bytes)

INTERVAL YEAR [(year_precision)] TO MONTH

–default precision 2

INTERVAL DAY TO SECONDS Data type: Stores a duration between a period of time by means of days,hours, minutes,seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

–precision for date is up to 9 with default 2

–precision for fractional seconds is up to 9 with default 6

(more…)

April 5, 2007

Questions to a new graduate (Are you ready to be a DBA)

Filed under: Diary — coskan @ 11:11 am

There are always questions in mail group threads from new graduates, like why should I choose to be DBA? or tell my about pros/cons of DBA job. I asked them the questions below and say them If you have a little hesitation on any of these questions be something else rather than DBA . Here are the questions that must be answered with Yes to give decision to be a DBA.

1- would you like to work when all of the others are on holiday ? (because of SLA’s you can do maintenance only on national holiday times mostly)

2-Would you like to wake up at 4 AM with the voice of the operator who says there is something wrong in the DB ? (no matter when you left the building for going home if you must be called then you will be called). Believe me there will always be  something wrong with the DB because database is not an install and forget system.

3- Are you calm enough to work under pressure?? This stress is not like every pressure it is the pressure comes from the distress of a man (usually your boss or his representative) who is loosing a lot money every second because of a DB fault. They can look like a monster in that crisis times.

4- Do you like to work when there are many people especially the boss or general manager (with telephone listening the boss) and his/her flatterers looking at your screen and asking when will it recover or when will the problem be solved ? For example in crisis times when you are a unix admin you will work with you primary manager, when you are a network engineer you will work with your colleagues, when you are a developer you will work with your project leader but if it is something about DB you will work in front of the ones who has great balls.

5- Are you ready not to get  of reading ?? Every oracle release has over 10 thousands of pages official documents and zillion articles and as a bad news Oracle announce new releases almost every 3 years.

6- Do you like overtimes ? Due to the fact that DB is at the center of everything (network, system, application etc) many times you will stay at office even if there is someone working on something. If there is a network work you should stay if there is a unix work you should stay if there is an update you should stay because every work can effect the DB.

7 - Would you see the database as a lover or more than a lover? DB is something like a lover or a kid. You have to show your interest every time and look after DB in 7/24 time period. Your lover or kid may thank you with a kiss or hug for your effort but DB wont :) This love and interest to DB is something like unrequited love.

8- Are you ready to stay calm over against meaningless requests of a developer who has a black box approach to DB and who believes that he is only responsible for writing code not performance ? (I want to kill the ones who wrote that is independent from DB pufffffffffff)

9- Are you careful enough?. You will play with real data and it is very easy to drop a database just like the 2 word command “drop database”. A simple where clause could change everything.

10-Are you ready to be blamed every time ? No matter what the problem is you will always be blamed first. Everyone will calumniate the DB and its Administrator because it is at the center of everything.

Again if you have hesitation to answer YES to any of these questions don’t think be a DBA . Be something else because this is the DBA’s life.

Will it worthy enough to stand all those?? I don’t know but i am happy to be an Oracle DBA.

Child Listener Problem

Filed under: Uncategorized — coskan @ 7:27 am

A while ago one of our systems faced with a child listener occurrence problem. The listener wasn’t answering the requests., when our clients were trying to reach the server through listener as a new connection.We couldn’t get the status of the listener with lsnrctl status command and nothing was written to the listener logs.

There wasn’t any problem on the database itself because local connections are accepted and database answers your requests. Also already connected users are working normally. Conclusion was “our listener hanged”

When we looked at the listener proceses we saw that there was a a child listener forked from the real listener.

#ps -ef | grep tns
oracle 22480 14188 0 18:33:57 $ORACLE_HOME/bin/tnslsnr listener_DBNAME -inherit
oracle 14188 1 0 Apr 1 $ORACLE_HOME/bin/tnslsnr listener_DBNAME -inherit

We tried to stop the listener with lsnrctl stop command but it didn’t work. (after closing the application servers)

So we killed the listener processes from OS

# kill -15 22480,14188

After the killing operation we opened the listener and everything worked well and all the connection requests were accepted. This fast action must be taken immediately and it was time to find a real solution for the problem.

Simple metalink search explained the problem and the solution. Note:340091.1 says that “This is a known problem addressed via non-published bug:4518443 (Abstract: Listener Gets Hung Up). The issue is that the TNS listener can hang under load while spawning a process” . Note has 2 solutions one is applying patch 4518443 or setting the listener parameter SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> =OFF. We choose the parameter setting option and did not face with the problem again. There is something to consider about the parameter setting option for RAC systems, when you set parameter to OFF, FAN (fast application notification) will not be possible.

PS: Note says this problem can occur on any platform for releases from 10.1.0.2.0 to 10.2.0.3.0

April 2, 2007

ORA-12838 after direct load

Filed under: Tips — coskan @ 3:02 pm

When I was testing the virtual indexes for the previous post, I got ORA-12838 from the subsequent select after inserting 4 million rows without commit (I mean I forgot committing). The error was

ORA-12838: cannot read/modify an object after modifying it in parallel

The error documentation says ;

Cause: Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.
Action: Rewrite the transaction, or break it up into two transactions: one containing the initial modification and the second containing the parallel modification operation.

Action was not clear for me. When I look at the code I saw that there wasnt any commit when i committed the transaction the error solved.

As a main idea for the blog entry “look twice to your code before taking an action” :)

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers