DBA Tasks

December 3, 2010 8 comments

We can separate the dba tasks between daily, weekly and monthly procedures:

Daily
– Verify instance status
– Check alerts
– Check configured metrics
– Check RMAN backups
– Check storage
– Check CPU contention
– Check waiting times
– Check memory usage
– Check network load
– Check iostat

Weekly

– Invalid objects
– Tunning: indexes and execution plans
– Top SQL
– Environments consistence
– Review of ressource policy
– Trends and peaks
– Cleaning of alert logs
– Review of RMAN

Monthly

– Recovery tests
– Analyze the data increment trend
– Tunning
– Review I/O
– Fragmentation
– Row chaining
– High Availability Analysis
– Scalability
– Schedule monthly downtime

Advertisements

Grid Control: EMD upload error

December 2, 2010 Leave a comment

Today I was installing the grid control agent in a new server, and I probably mistyped the password during the installation. I didn’t notice at the start, but the client was succesfully installed. However, it didn’t appear on my grid control maintenance window.

I connected to the brand new server and issued the following command:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl upload agent

And it produced this error:

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

This error was caused because I typed a bad password, and in order to change it, I had to remove first the password:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl unsecure agent

And then, securing the agent again:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl secure agent

It asked me for a new password, which I typed well this time, and everything started working fine.

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:

http://www.orafaq.com/papers/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!

Default passwords in Oracle

November 26, 2010 Leave a comment

Have you ever asked yourself which are the default passwords in Oracle?

Here is the list:

SYS: CHANGE_ON_INSTALL
SYSTEM: MANAGER
SCOTT: TIGER
ADAMS: WOOD
JONES: STEEL
BLAKE: PAPER
FORD: CAR
KING: GOLD

Categories: oracle, Security Tags:

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: , ,