Archive

Posts Tagged ‘Oracle’

ora-600

November 26, 2010 Leave a comment

If you don’t know what an ORA-600 is, you probably are new in the bussiness, or really lucky.

ORA-600 is the worst error you could face in an Oracle Database, and unfortunately, it’s more

common than people would want.

The description of the ORA-600 error is the following:

Error: ORA 600
Text: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s]
——————————————————————————-
Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
Action: Report as a bug – the first argument is the internal error number

When you get an ORA-600 in your database, a record will always be written into the alert log,

and you will probably have the reference to a trace file for debugging purposes. If you use

grid control, you will want to configure it in order to receive an e-mail whenever an ORA-600

is produced.

Basically, if you receive an ORA-600, you are recommended to open a SR in metalink (My Oracle

Support), but maybe you are facing a known error that can be solved without the intervention

of Oracle Support. Notice that you have to know what are you doing, if you lack the required

knowledge then you should open an SR and follow the help provided by the Oracle staff.

And a last piece of information: there is an undocumented oracle utility called ORADEBUG that

you could use under your responsability to troubleshoot ORA-600 errors. For more info, check

the following paper:

Click to access oradebug.pdf

I hope this information helped you in case you have found an ORA-600 in your database. And

remember, ORA-600 are almost always serious bussiness, so if you see this kind of failure, you

should assign the maximum priority to solve it!

What is the data dictionary?

The scope of this article is to clarify what exactly is the data dictionary. Probably all of you have heard of it, and you know it’s a bunch of metadata that Oracle uses to work, but if you want to know more about this Data Dictionary, then I hope this article teaches you something new.

The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace (in Oracle 10+).

The data dictionary is composed of tables and views. Some of these tables are inside of the Oracle kernel, so you would never work directly with them unless you are working for Oracle support or performing a disaster recovery scenario. But instead you can access to the views in order to know the “information about the information”.

For example, a possible usage of this data dictionary would be to know all the tables owned by a single user, or the list or relationships between all the tables of the database.

The main view of the data dictionary is the view DICT (or DICTIONARY):

SQL> DESC DICT
Nombre                                    ?Nulo?   Tipo
—————————————– ——– —————————-
TABLE_NAME                                         VARCHAR2(30)
COMMENTS                                           VARCHAR2(4000)

Through the DICT view, you can access to all the data dictionary views that could provide you the information that you need.

For example, if you are looking for information related to db_links, but you don’t know where to look for, then query the DICT view:

SQL>  SELECT TABLE_NAME FROM DICT WHERE TABLE_NAME LIKE ‘%LINK%’;

TABLE_NAME
——————————
USER_DB_LINKS
ALL_DB_LINKS
DBA_DB_LINKS
V$DBLINK
GV$DBLINK

Now, you have just to query one of these views to find the data you are looking for. GV$ views are very useful when you are working with RAC, and V$ views are instance related.

Remember that the data dictionary provides critical information of the database, and it should be restricted to users. However, if a user really needs to query the data dictionary, you can use the following sentence:

GRANT SELECT_CATALOG_ROLE TO user_name;

Undocumented parameter _allow_read_only_corruption

July 7, 2010 4 comments

Some time ago we talked about the undocumented parameter _allow_resetlogs_corruption, which could help us to open a database in case we have corruption in our redo logs. In this post we will discuss another undocumented parameter that could be helpful in case of corruption: _allow_read_only_corruption.

This parameter should not be used without the support of Oracle itself. But if you want to do some tests to retrieve your data more quickly, you can make a copy of your corrupted database and try this hidden parameter. Just make sure that you have a copy, because playing with this kind of parameters you can corrupt your database much more.

The procedure to use this hidden parameter would be to shutdown abort your database to make sure it’s completly closed.

Then, startup the database in nomount mode, and set this parameter to on:

ALTER DATABASE SET _allow_read_only_corruption SCOPE=SPFILE;

Shutdown immediate the database, and then try to startup your database “normally”. It will open your database even if you have some corruption, or at least, it will try to do it.

The first thing you should do after opening the database in this mode is to export all the data, and recover as much as you can. You shouldn’t use the database in this instance as this is an inconsistent mode, and _allow_resetlogs_corruption.

Just be careful, and remember that if you use this kind of knowledge without knowing what you are doing, you are claiming for disaster.

A script to check the frequency of Oracle log switches

May 11, 2010 6 comments

I was looking for a script that showed me the frequency of my redo log switching through a SQL query. Although I can use Grid Control to monitor my redo logs, and use his several advisors, I prefer to use SQLPlus when it’s possible to keep the control of what I’m doing.

What I found is a fancy script written by Donald K. Burleson, that can be found in the following link

http://www.remote-dba.cc/oracle_tips_log_switches.htm

The script, in resume will show the frequency of your redo logs by hour, but unfortunately the view v$log_history has changed since the time that this script was done (I’m using Oracle 10g), so the select fails with the following error:

group by substr(time,1,5)
*
ERROR at line 27:
ORA-00904: “TIME”: invalid identifier

So I tried to fix the script to adapt it to Oracle 10g, but being unable to make the conversion, I decided to make a new script.

This is the resultant script:

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24′) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ASC;

And there is an output example:

DAY             HOUR TOTAL
————— —- —–
2010-04-30      01      24
2010-04-30      02       1
2010-04-30      13       1
2010-04-30      22       1
2010-05-01      00       1
2010-05-01      01      23
2010-05-01      03       1
2010-05-01      22       1
2010-05-02      00       1
2010-05-02      01      23
2010-05-02      22       1
2010-05-03      00       1
2010-05-03      01      23
2010-05-03      11       1
2010-05-03      22       1
2010-05-04      00       1
2010-05-04      01      24
2010-05-04      02       1
2010-05-04      12       1
2010-05-04      14       1
2010-05-05      00       1
2010-05-05      01      24
2010-05-05      02       1
2010-05-05      12       1
2010-05-05      22       1
2010-05-06      00       1
2010-05-06      01      23
2010-05-06      02       1
2010-05-06      12       1
2010-05-06      16       1
2010-05-07      00       1
2010-05-07      01      24
2010-05-07      02       1
2010-05-07      12       1
2010-05-07      22       1
2010-05-08      01      24
2010-05-08      03       1
2010-05-09      00       1
2010-05-09      01      24
2010-05-10      00       1
2010-05-10      01      24
2010-05-10      12       1
2010-05-10      22       1
2010-05-11      01      24
2010-05-11      02       1

Categories: oracle, Scripts, SQL Tags: , ,

Undocumented parameter: _allow_resetlogs_corruption

May 6, 2010 1 comment

Note: This is an undocumented paramete and shouldn’t be used if you don’t know what you are doing. You can put your database in an unstable state if you don’t know what you’re doing, so be careful. You have been warned…

_allow_resetlogs_corruption is a hidden parameter, and undocumented for mortals like us, that is only supposed to be used by Oracle Staff in case of emergency. If you need to use it by yourself, you should have a cold backup ready to restore, and NEVER use it on a working database that you don’t wanna lose, because it’s very probable that you will have corruption problems afterwards.

To use this parameter, you have to set it into your spfile or pfile the following way:

_allow_resetlogs_corruption=TRUE

By default, it is set to false, of course.

When to use this parameter?

You may be in need to use the undocumented parameter _allow_resetlogs_corruption if you have a database that has crashed and cannot be recovered because one of your redologs is corrupted. Yes, you could make an incomplete recover, but… what if you MUST have the data that is inside your corrupted redo logs? What if you can’t afford to lose that data, or at least, you must try to recover it when humanly possible?

Well, in that case then you can try to use this parameter, because you have nothing to lose. However, make sure that you have a cold backup of your broken database, or you could miss any chance of recovering your data.

And just in case you are wondering, yes, you have other ways to recover the data from a “broken” database, but we will talk about this kind of disaster recovering tools in the future.

To activate the resetlog corruption and try to recover all the data that you can, you must:

1. STARTUP MOUNT;
2. ALTER SYSTEM SET _allow_resetlogs_corruption=TRUE;
3. Cross your fingers.
4. ALTER DATABASE OPEN;

If your database opens, DON’T PUT IN IN PRODUCTION. Extract all the data that you need and recover it by any means, for example, using exp or data pump. You could have problem extracting the data, but if you have managed to open the database, you should be able to make an export.

However, it’s best if you don’t ever need to use this kind of parameters. But, in case you have problems with your database, I hope this post will help someone to recover it in case of disaster.

Be careful and don’t play with your production environment!

Grid Control Problem

Today I noticed that my Grid Control was slower than normally, and when I try to connect to the Grid Control Console, sometimes I can connect, but other

times I get the following error:

Error

Authentication failed. Verify username/password that you have provided. If you believe you entered correct credentials, your account may have been locked,

contact system administrator to unlock your account.

Weird, I’m not typing bad my password, what could be wrong?

The last change I made in my server was modifying the file /etc/hosts , because the name of my machine was pointing to 127.0.0.1, and I wanted to change it

to his real IP. It looks that something went wrong.

Another symptom was revealed when I used the command netstat:

[root@superlopez ~]# netstat -punta | wc -l
42487

And 1 minute later I execute it again…

[root@superlopez ~]# netstat -punta | wc -l
62881

Definitely something was wrong, almost all the connections were of the following kind:

tcp        0      0 127.0.0.1:45118             127.0.0.1:6103              TIME_WAIT   –

Ok, first step, this error must being logged somewhere, let’s search all the logs in the server and order it chronolocally:

[root@superlopez ~]# ls -larth $(find / -name “*.log” 2>/dev/null)
-rw-rw—- 1 oracle oinstall  18M may  4 08:23 /u01/oracle/app/10.2.0.4/oms10g/j2ee/home/log/home_default_island_1/server.log
-rw-rw—- 1 oracle oinstall  18M may  4 08:23 /u01/oracle/app/grid/oms10g/j2ee/OCMRepeater/log/OCMRepeater_default_island_1/server.log
-rw-rw—- 1 oracle oinstall  18M may  4 08:23 /u01/oracle/app/grid/oms10g/j2ee/OC4J_EMPROV/log/OC4J_EMPROV_default_island_1/server.log
-rw——- 1 oracle oinstall  18M may  4 08:23 /u01/oracle/app/grid/oms10g/j2ee/home/log/home_default_island_1/server.log
-rw-r—– 1 oracle oinstall  51M may  4 08:23 /san/datos/CATRMAN/redo02.log
-rw-rw—- 1 oracle oinstall  31M may  4 08:23 /u01/oracle/app/grid/oms10g/j2ee/OC4J_EM/log/OC4J_EM_default_island_1/default-web-access.log

These are the logs written in the last minute. I don’t think the poor redo log gives me any information, but the other logs might insight me.

… But none of them gave me any information at all. However, the log /u01/oracle/app/10.2.0.4/oms10g/opmn/logs/ons.log shows the following error several

times, and the log occupies 1Gb:

10/05/03 12:44:08 [4] Falta el factor de formato de la conexión local 0,127.0.0.1,6103
<unknown>

So, there is a problem with the port 6103. After googling a bit, I modified the file /u01/oracle/app/10.2.0.4/opmn/conf/ons.config and changed:

localport=6103
remoteport=6203
loglevel=3

to

localport=6115
remoteport=6205
loglevel=3

After that I rebooted…

[root@superlopez ~]# netstat -punta | wc -l
177

Problem solved! It looks like the port 6103 was in onflict with another service, so after changing the port in the previous file, everything started working

fine.

One problem less…

Categories: Grid Control, oracle Tags: ,

Cheating incident

Look at the interest notice about certifications I just read in an

oracle blog, I extracted it from the following link:

http://blogs.oracle.com/certification/2010/04/0255.html


Hi Everyone –

I just wanted to give you a heads-up on some recent actions that we had

to take regarding someone who tried to cheat on a test.

Just last week, we had a candidate who was bold enough to try to take

screen-shots during the exam using his cell phone. Of course this is

completely against Oracle Certification Program policy. This

candidate’s exam was immediately stopped, and the candidate was ejected

from the testing center.

Of course it doesn’t end there. This candidate was also suspended

permanently from the Oracle Certification Program, and all of his

previous certifications were revoked. He is no longer eligible to

participate in the program. Unfortunately – if this candidate works for

an Oracle partner company, then his company will also likely become

aware of his indiscretion – particularly when they request validation

of his certification.

Although this particular candidate was blatantly involved in content

theft, any type of cheating incident is very unfortunate. It erodes the

value of the certification credentials, and hurts everyone involved.

The actions described above represent our efforts to curb cheating and

maintain the value of Oracle certification credentials.

I encourage anyone seeking Oracle certification to carefully read and

adhere to the Oracle Certification Program Fraudulent Activity Policy.

Thanks,

Paul

Personally I don’t understand why anyone cheats with the

certifications, because they are completly useless if you don’t have

the required knowledge. If you are hired because of an Oracle

Certification, and you have no idea of the knowledge you are supposted

to have, then you will be fired sooner or later…

At least that’s my opinion…