Strange but Interesting problem

Our database backup through RMAN was terminated with below error message

In RMAN log file

RMAN-03009: failure of backup command on C1 channel at 06/20/2008 10:04:34

RMAN-10038: database session for channel C1 terminated unexpectedly

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 06/20/2008 10:06:03

RMAN-00600: internal error, arguments [8039] [sqlrcn failed] [-1075] [] []

In alertsid.log file

Sat Jun 21 17:11:56 2008
Errors in file e:\oracle\product\10.1.0\admin\udump\ora_3856.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x114B926] [] [] [] []

After that I start my searhing in metalink for ORA-07445 but didn't get any useful.

then search for RMAN-00600 error and get hint through increase SWAP space will solve the issue but it is not solve the problem

then after I search for RMAN-10038 error and get metalink note: 337894.1 which refer to check RMAN configuration, and this is the actual problem when I reset all RMAN configuration then RMAN backup is work smootly.

In RMAN configuration below parameter is set wrongly.

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'G:\ORA_BACKUP\%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024 M;

Actually I set above parameter for the backup location and format and also mention FORMAT cluase in BACKUP SCRIPT with "BACKUP DATABASE ... FORMAT ...". but when I clear this configuration parameter RMAN backup is work.

CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;

After that RMAN backup work properly and problem solved.

RMAN-00600: [8039] [sqlrcn failed] [-1075]

Yesterday I was getting below error message during RMAN backup session on our developement database

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 06/20/2008 10:06:03

RMAN-00600: internal error, arguments [8039] [sqlrcn failed] [-1075] [] []

Backup session was terminated with RMAN-00600 error message.

When I search on metalink then I found below metalink note: 602757.1

Metalink note refer Oracle 9i database but our database version is 10gr1 on WIN2003 platform.

it is related to OS SWAP space problem, SWAP space increase will solve the issue.

Delete LOGFILE older date

In Oracle database there are some files which reguarly create day by day such as trace files, log files, archivelog files & Exported dump files.

In this article i will show how to remove above files as batch job.

Linux platform

We can use find command for this purpose.

for example:

find $TRACEFILE/*.trc -mtime +7 -exec rm {} \;

above script remove all .trc files which older than 7 days.

Schedule: Through crontab utility schedule above script.

For Reference

Windows platform

We can use FORFILES.exe for this purpose

find more about forfiles.exe

for example:

K:\FORFILES.EXE /P K:\archive_dump_files -s /M *.* /D -5 /C "cmd /c del @FILE echo @FILE"

In above example files deleted older than 5 days from "archive_dump_files" location.

Schedule: We can use WINDOWS SCHEDULER TASK for this purpose.

ORA-12516

Last couple of days i faced strange problem with our development database.

We are getting following error when trying to connect to database with TNS_ENTRY

ORA-12516: TNS:listener could not find available handler with matching protocolstack

When i check ALERTSID.LOG file i am getting following entry

Mon Jun 16 09:36:01 2008

Process q000 started up but failed with error = 20

Mon Jun 16 09:57:55 2008

Process q000 started up but failed with error = 20

Mon Jun 16 10:02:01 2008

Timed out trying to start process J000.

Mon Jun 16 10:02:02 2008

kkjcre1p: unable to spawn jobq slave process

Mon Jun 16 10:02:02 2008

Errors in file d:\oracle\product\10.1.0\admin\bdump\oramfe_cjq0_3328.trc:

Timed out trying to start process J000.

according error message i search on metalink and found to increase

job_queue_processes parameter from default value 10 to 20 but after that the problem is not solved.

after that i checked without TNS_ENTRY and found below error message

SQL> conn scott/tiger

ERROR:ORA-00020: maximum number of processes (%s) exceeded

then finally I increase PROCESSES parameter & change Listener Registration from DYNAMIC to STATIC then after problem solved.

How to change listener registration from DYNAMIC to STATIC see below link

http://dbataj.blogspot.com/2007/02/ora-12514.html

SQL> alter system set processes = 400 scope=spfile;

ORA-00600: [17183], [0xA2C0044]

Today I getting following error in 10gr1 database running on windows 2003 server

ORA-00600: internal error code, arguments: [17183], [0xA2C0044], [], [], [], [], [], []

It is reported as BUG.

It can be found in Oracle 9i,10gr2.

The BUG is fixed in 10.1.0.4 Server Patch Set

Kindly Refer Metalink Note: 3350337.8

Global Temporary Table + ORA-01031

If we are trying to create GLOBAL TEMPORARY TABLE inside a PROCEDURE then we are getting following error

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;

BEGIN PRO_TEST; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.PRO_TEST", line 4
ORA-06512: at line 1

Reason: PLSQL stored procedures execute with the base privs of the definer (owner) of the routine meaning that ROLES are not enabled.

It is very bad idea to execute DDL statement (create Global Temporary Table inside procedure) without any compling reason.

Solution: Grant explicit "CREATE TABLE" privilege to Schema which Create & Execute Procedure inside Global Temporary Table.

SQL> conn system@orcl
Enter password:
Connected.
SQL> grant create table to scott;
Grant succeeded.

SQL> create or replace procedure PRO_TEST
2 as
3 begin
4 execute immediate 'create global temporary table GTT_TEST (no number)';
5 end;
6 /
Procedure created.

SQL> exec PRO_TEST;
PL/SQL procedure successfully completed.

Rewind Table Drop operation

Table Dropped by mistaken !!! offf

Before Oracle 10g release it is one of the biggest and time taken task for DBA's for recover DROPPED table through perform INCOMPLETE recovery.

But now in 10g it is very easy just taken few minutes and table is recovered without perform INCOMPLETE recovery or NOT restore and recovery backup.

It is called FLASHBACK drop table option

We need "DROP" & "SELECT" privilege on object where we want to perform FLASHBACK operation.

And RECYCLEBIN option must be enable otherwise is not going to recyclebin if it is disable.

In 10gr1 there is hidden parameter for enable recyclebin

SQL> alter database set "_recyclebin"=ON;

and later release we can just use recyclebin parameter

SQL> alter database set recyclebin=ON;

SQL> create table test ( no number);
Table created.
SQL> drop table test;
Table dropped.

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------
TEST BIN$pqWN6pCzRxe+PtPucq/55A==$0 TABLE 2008-06-10:11:49:18

Now above table is dropped by mistake, but take it easy it is very easy with 10g to recover dropped table.

SQL> flashback table oem.test to before drop;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)

----------

0

SQL> show recyclebin

For more details

Enabling FLAHSBACK database

Database FLAHSBACK option is introduce in oracle 10g version.

and it is only available with ENTERPRISE EDITION,

if we have STANDARD EDITION or OTHER then you can't use FLAHSBACK DATABASE option.

if we trying to enable FLASHBACK DATABASE option then we are getting below error message

ORA-00439:feature not enabled: string

Flashback database option is enabled or not, we can check in v$option or v$version data dictionary view

Now if we are on ENTERPRISE EDITION and want to enable FLASHABACK database, so below are steps for this

1. We need to set below three parameters

1. db_recovery_file_dest

For FLASHBACK RECOVERY AREA LOCATION

2. db_recovery_file_dest_size

For FLASHBACK RECOVERY AREA size

3. db_flashback_retention_target

This parameter value specify in MINUTE, default value is 1440 minute. This parameter is use for how far back in time the database may be flashed back.

NOTE: All three parameter are DYNAMIC so no need to bounce database to take effect the parameter value.

Steps for enable flashback database

1. we can check flashback database is enable or not?

select flashback_on from v$database

NO --mean disable, YES -- mean enable

2. shutdown the database

SQL>shutdown immediate

3. startup the database in mount mode

SQL> startup mount

4. enable the flashback database

SQL> alter database flashback on;

5. open the database

SQL> alter database open;

RMAN Backup Fails With ORA-19571

RMAN Backup Fails With ORA-19571

RMAN-00571: ===========================================================

RMAN-00569: ===============

ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on

ORA_DISK_1 channel at 06/06/2008 20:47:31

ORA-19571: archived-log recid 13100 stamp 656015599 not found in controlfile

If you are getting above error message then apply below solution

1. increase value of CONTROL_FILE_RECORD_KEEP_TIME parameter

NOTE: It is static parameter so we need to bounce our database to take effect.

2. Use RECOVERY CATALOG instead of CONTROLFILE to keep RMAN backup information.

NOTE: For more details check metalink note: Note:563219.1