tnsManager v1.8.3

tnsManager is an interesting tool by Andrew Barry to centralize your TNSNames management.

Version 1.8.3 is the latest stable build. Simple steps and easy to deploy and manage. It is available for both Windows and Linux Environments. You can also configure tnsManager for High Availability.Test it out, it should be fun.

You can download the installation and check out the Installation Guide and Administration Guide.
http://www.shutdownabort.com/tnsmanager/index.php

RMAN Clone erroring out with ORA-19804

ORA-19804: cannot reclaim string bytes disk space from string limit

I received ORA-19804 on one of my RMAN clone jobs this morning.

RMAN-00571: ===========================================
RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS =========
RMAN-00571: ===========================================
RMAN-03002: failure of Duplicate Db command at 11/30/2010 17:59:32
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error reading backup piece \\BLADE5\MIS\BACKUP\RMAN\ORCL\ORCL_ESLU9PLK_1_1_20101130
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 103388672 bytes disk space from 1073741824 limit

The problem is that Oracle cannot reclaim disk space from DB_RECOVERY_FILE_DEST_SIZE set limit.

There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

One can choose all or any that corresponds to his situation to fix the problem. Although, it is easy to figure out and apply the fix, thought of sharing it here with an OTN Thread link, as Aman, Anand and Robert have explained it very well.

Thread: RMAN BACKUP

The Seven Deadly Habits of a DBA . . and How to Cure Them

The Seven Deadly Habits of a DBA . . and How to Cure Them
by Paul Vallée

Calling widespread bad habits in database administration "deadly" may seem extreme. However, when you consider the critical nature of most data, and just how damaging data loss or corruption can be to a corporation, "deadly" seems pretty dead-on.

Although these habits are distressingly common among DBAs, they are curable with some shrewd management intervention. What follows is a list of the seven habits we consider the deadliest, along with some ideas on how to eliminate them.

Habit #1. THE LEAP OF FAITH: "We have faith in our backup."
Blind faith can be endearing, but not when it comes backing up a database. Backups should be trusted only as far as they have been tested and verified.
Cures:
§ Have your DBAs verify that the backup is succeeding regularly, preferably using a script that notifies them if there's an issue.
§ Maintain a backup to your backup. DBAs should always use at least two backup methods. A common technique is to use those old-fashioned exports as a backup to the online backups.
§ Resource test recoveries as often as is practical. An early sign that your DBA team is either overworked or not prioritizing correctly is having a quarter go by without a test recovery. Test recoveries confirm that your backup strategy is on track, while allowing your team to practice recovery activities so they can handle them effectively when the time comes.

Habit #2. GREAT EXPECTATIONS: "It will work the way we expect it to. Let's go ahead."
Although not user friendly in the traditional sense, Oracle is very power-user friendly— once you've been working with it for a while, you develop an instinct for the way things "should" work. Although that instinct is often right, one of the most dangerous habits any DBA can possess is an assumption that Oracle will "just work" the way it should.
Cures:
§ Inculcate a "practice, practice, practice" mentality throughout the organization. DBAs need to rehearse activities in the safe sandbox of a test environment that's designed to closely mimic the behaviour of the production system. The organization needs to allow the time and money for them to do so.
§ Pair inexperienced DBAs with senior ones whenever possible—or take them under your own wing. New DBAs tend to be fearless, but learning from someone else's experience can help instill some much needed paranoia.
§ Review the plans for everything. It's amazing how often DBAs say, "I've done that a hundred times, I don't need a plan." If they're heading into execution mode, they absolutely need a plan.

Habit #3. LAISSEZ-FAIRE ADMINISTRATION: "We don't need to monitor the system. The users always let us know when something's wrong."
If you depend on the users to inform the DBA team that there's a problem, it may already be too late.
Cures:
§ Install availability and performance monitoring systems so that issues are identified and resolved before they cause service-affecting failures.
§ Avoid post-release software issues by working with developers and testers to ensure that all production-ready software is stable and high-performance.

Habit #4. THE MEMORY TEST: "We'll remember how this happened, and what we did to get things going again."
It may seem impossible that a DBA team would forget a massive procedure that took them weeks to get right, and yet it happens all the time. In order to prevent recurring mistakes and take advantage of gained experience, documentation is essential.
Cures:
§ Require that your DBAs maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail.
§ Provide your team with groupware on your intranet so that these documents become searchable in an emergency.
§ Enforce the discipline of documentation and check it periodically. Ask your DBAs: When was this tablespace created, by whom, and with what SQL? What tasks were performed on a particular day? If they can't answer quickly, you'll know they've gone back to relying on memory.

Habit #5. THE BLAME GAME: "Don't look at me, it's the developer's fault that SQL is in production"
Some DBAs have a real "us versus them" mentality when it comes to developers in their organization. They see themselves not as facilitators helping the developers develop quality code from a database standpoint, but rather as guardians who prevent poor-quality code from making it into production. This might seem like semantics, but a confrontational relationship between developers and DBAs results in a lack of developer initiative and significant slowdowns in release cycles.
Cures:
§ Select DBAs who understand it's their responsibility to work as an integrated team with the developers they support.
§ Cultivate a team attitude by structuring continuous DBA involvement in every project rather than at review milestones.
§ Consider assigning an individual DBA in a developer support role. If it's clearly in the job description, there's more motivation to do it well.

Habit #6. THE SOLO ACT: "I know what I'm doing and don't need any help."
Database administration is increasingly complex and even the most senior DBAs can't possibly know every last detail. DBAs have different specialties, which need to be culled and utilized. When DBAs feel like they know, or should know, everything, they don't ask questions and miss out on valuable knowledge they could be gaining from others.
Cures:
§ Foster a teamwork culture where it's acceptable for DBAs to admit they don't know the answer and to ask for help.
§ Encourage your DBAs to seek out an outside peer group as a forum for brainstorming and testing their assumptions. No single person can match the expertise and experience of even a relatively small group.
§ Provide a safety net of tech resources such as reference materials, courses, and outside experts or consultants on call.

Habit #7. TECHNO-LUST: "Things would work so much better if only we had..."
DBAs are often on top of the latest technology, which can help them do a superlative job. But when the desire for new technology causes DBAs to recommend unnecessary hardware purchases or software add-ons, costs tend to skyrocket quickly—as do problems.
Cures:
§ Never upgrade your hardware infrastructure without first exhausting all tuning opportunities. Remember, ten years ago enormous enterprises were run on servers one-tenth the capacity—all thanks to necessity and skill.
§ Never consent to using advanced or new features until you're well aware of the ongoing maintenance commitment and resulting costs.
§ Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks. This type of interface allows a beginner DBA to act as an intermediate DBA under certain circumstances, but simultaneously prevents that beginner from learning the actual skills behind the tasks. Moreover, these tools tend to hide real risks from the DBA, making potentially damaging activities as easy as point-and-click.

Whether it takes a twelve-step program or one tiny adjustment, all of these deadly DBA habits can be kicked. Of course, the first step is recognizing the problem. By starting with this list and doing a careful inventory of the successes and failures in your team's database administration, you'll be well on your way to finding a cure.

-------------------------------------------------------------------------------------------------
Paul Vallée is the Executive Chairman and Founder of Pythian, a leading global database application and infrastructure services company for Oracle, MySQL and SQL Server.

Resizing an Over-Grown Temporary Tablespace

One fine day, while carrying out our daily health checks we came across an over-grown Temporary Tablespace. The Temporary Tablespace had over-grown to 12GB. Normally, our temporary tablespace has not exceeded beyond 1 GB. But due to some one-time un-tuned script that ran the other evening, the tablespace having autoextend set to unlimited had grown to 12GB. We needed to reduce the size of the temporary tablespace to free space on the server, as our server had resource constraints.

You can not reduce the size of the temporary tablespace, despite the % of free space shown. You have to drop and re-create the temporary tablespace. Let me show you how to regain that occupied space by the over-grown temporary tablespace.

Assuming the Default Temporary Tablespace is "TEMP", you create a new temporary tablespace "TEMP02".

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP02" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP02.dbf' SIZE 100M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 1024M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you set "TEMP02" as your default temporary tablespace.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP02";
Database altered.

Then, you drop the over-grown temporary tablespace "TEMP" and recreate the "TEMP" temporary tablespace.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> CREATE
2 TEMPORARY TABLESPACE "TEMP" TEMPFILE
3 'C:\ORACLE\ORADATA\ORCL\TEMP01.dbf' SIZE 500M REUSE
4 AUTOEXTEND ON
5 MAXSIZE 600M EXTENT MANAGEMENT LOCAL;
Tablespace created.

Then, you make "TEMP" as your default temporary tablespace, and drop the temporary tablespace "TEMP02".

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
Database altered.

SQL> DROP TABLESPACE temp02 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Please ensure that you are carrying out this activity in your Maintenance Windows, or else you may encounter following errors while dropping the temporary tablespace in use:

Errors in file c:\oracle\admin\orcl\udump\orcl_ora_2300.trc:
ORA-01258: unable to delete temporary file C:\ORACLE\ORADATA\ORCL\TEMP01.DBF
ORA-27056: skgfrdel: could not delete file
OSD-04024: Unable to delete file.
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

Dealing with Unindexed Foriegn Keys

Couple of months back we were hitting a lot of deadlock issues. I found out that deadlocks occur due to unindexes foreign keys and found the following script to find and create unindexed foriegn keys.

Using SQL*Plus, connect to the schema for which you need to find the unindexed foriegn keys. Then execute the following scripts:

Script to find Unindexed Foriegn Keys

column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select table_name, constraint_name,
cname1 PIPE-PIPE nvl2(cname2,',' PIPE-PIPE cname2,null) PIPE-PIPE
nvl2(cname3,',' PIPE-PIPE cname3,null) PIPE-PIPE nvl2(cname4,',' PIPE-PIPE cname4,null) PIPE-PIPE
nvl2(cname5,',' PIPE-PIPE cname5,null) PIPE-PIPE nvl2(cname6,',' PIPE-PIPE cname6,null) PIPE-PIPE
nvl2(cname7,',' PIPE-PIPE cname7,null) PIPE-PIPE nvl2(cname8,',' PIPE-PIPE cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
);

Script to generate 'CREATE' statements for Unindexed Foriegn Keys

create global temporary table temp_index
(commande varchar2(500))
on commit delete rows;

define Tablespace_name = &&Tablespace_name
set feedback off
set linesize 255
set serveroutput on
set verify off
set heading off;
declare
L_nom_colo varchar2(2000);

Cursor sel_cons is
Select constraint_name, table_name
from user_constraints
where constraint_type = 'R';

Cursor sel_colo(P_nom_cons user_cons_columns.constraint_name%type,
P_nom_tabl user_cons_columns.table_name%type) is
Select column_name
from user_cons_columns
where constraint_name = P_nom_cons
and table_name = P_nom_tabl
order by position;

begin

for liste_cons in sel_cons loop
L_nom_colo := null;
for liste_colo in sel_colo(liste_cons.constraint_name, liste_cons.table_name) loop
if L_nom_colo is not null and liste_colo.column_name is not null then
L_nom_colo := L_nom_colo PIPE-PIPE ',';
end if;
L_nom_colo := L_nom_colo PIPE-PIPE liste_colo.column_name;
End loop;

insert into temp_index values('Create index ' PIPE-PIPE liste_cons.constraint_name PIPE-PIPE ' on '
PIPE-PIPE liste_cons.table_name PIPE-PIPE '(' PIPE-PIPE L_nom_colo PIPE-PIPE ') tablespace &Tablespace_name;');
end loop;
end;
/
spool &SCRIPT_NAME
select * from temp_index;
spool off
drop table temp_index;

A deadlock means that process X has a lock on resource 1 and is waiting for resource 2, while process Y has a lock on resource 2 and is waiting to acquire a lock on resource 1.

SQL Performance Diagnostics Scripts

I use the following SQL Scripts for finding top SQLs that may cause performance degradation.

Top SQL by Disk Reads

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;

Top SQL by Buffer Gets

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end "Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1)) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;

Top SQL by CPU

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by cpu_time desc nulls last;

Top SQL by Executions

select substr(sql_text,1,500) "SQL",
(cpu_time/1000000)
"CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets
"Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then
null
else (buffer_gets/nvl(replace(rows_processed,0,1),1))
end
"Buffer_gets/rows_proc",
(buffer_gets/nvl(replace(executions,0,1),1))
"Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module
"Module"
from v$sql s
order by executions desc nulls last;