DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Wednesday, September 10, 2014

Internal error code, arguments [kcratr_nab_less_than_odr]

Solution for kcratr_nab_less_than_odr argument

This error may cause due to improper shutdown and will throws the error 

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]

while issuing the command alter database open

Option 1:

SQL>Startup mount ;

SQL>Show parameter control_files

Query 1

SQL> select a.member,, b.status from v$logfile a ,v$log b where and b.status='CURRENT' ;

Note down the name of the redo log

SQL> Shutdown abort ;

Take a OS Level backup of the controlfile (This is to ensure we have a backup of current state of controlfile)

SQL> Startup mount ;

SQL> recover database using backup controlfile until cancel ;

Enter location of redo log shown as current in Query 1 when prompted for recovery

Hit Enter

SQL> Alter database open resetlogs ;

Option 2:

Recreate the controlfile using the Controlfile recreation script

With database in mount stage

rman target /

rman> spool log to '/tmp/rman.log';

Rman> list backup ;

Rman> exit

Keep this log handy

Go to sqlplus

SQL> Show parameter control_files 

Keep this location handy.

SQL> oradebug setmypid 

SQL> Alter session set tracefile_identifier='controlfilerecreate' ;

SQL> Alter database backup controlfile to trace ;

SQL> oradebug tracefile_name ; --> This command will give the path and name of the trace file

Go to this location ,Open this trace file and select the controlfile recreation script with NO Resetlogs option 

SQL> Shutdown immediate;

Rename the existing controlfile to <originalname>_old ---> This is Important as we need to have a backup of existing controlfile since we plan to recreate it

SQL> Startup nomount

Now run the Controlfile recreation script with NO Resetlogs mode

SQL> Alter database open ;

For database version 10g and above 

Once database is opened you can recatalog the rman backup information present in the list /tmp/rman.log using

Rman> Catalog start with '<location of backupiece>' ;

it is recommended to take a hot backup of the database once recovery completed.

Friday, September 5, 2014

Enable Auditing for DB user login attempts in 11g

1 - Add initialization parameters & bounce instance:

alter system set audit_trail=db scope=spfile;

2 - Enable auditing of failed logion attempts :

SQL> audit create session whenever not successful;

To view all the login attempts either success or failure :

col os_username format a15
col username format a15
col terminal format a15
col to_char(timestamp) format a35
select os_username,username,terminal,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') from dba_audit_trail;

To view the failed login attempts:

set lines 130
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0;

Also we can able to see the sys failed and succesful login attempts in audit_file_dest files.

Monday, September 1, 2014

Using adrci Tool in 11g

Oracle 11g introduces adrci tool. This tool is used to examine contents of ADR repository and also to package information related to a specific problem into a compressed (zip) file to send it to Oracle support.

To invoke the tool, log in as the Oracle software owner and type Adrci in the command line.
To list the tool command line commands, type the following:

adrci –help

To display the tool commands, type the following command in the
Adrci command line:


To obtain guiding information about specific command, type the following:

adrci>help show incident

adrci commands will run on the ADR root (known when dealing with this toolas ADR base).

To display the ADR base directory the tool is handling, type the following in the adrci command:

adrci>show base

adrci tool can deal with all the Oracle homes under the ADR base. If you want to specify which home should be handled by the tool, you must specify the current homepath
. If you do not specify the current homepath, all the homes will be handled by the tool.

To display homes under the ADR root, issue the following command:

adrci>show homes

To display the current ADR homepath, issue the following command:

adrci>show homepath

To set the current ADR home, issue the following command:
adrci>set homepath diag\rdbms\ora11g\ora11g

You can specify multiple homes as current homepaths. In this case, adrci tool will deal with all the
specified current homepaths. However, not all adrci commands can work with multiple current
Any text output from adrci can be captured and saved in an external text file using spool command:

adrci>spool /u01/myfiles/myadrci.txt
adrci> ...
adrci>spool off

Using adrci to View the Alert Log

By default, adrci displays the alert log in your default editor. You can use the SET EDITOR
command to change your default editor.

adrci>set editor notepad.exe
To display contents of the alert log file (xml tags will be excluded), issue the following command:

adrci>show alert

To see only the last 30 messages in the alert, modify the command as follows:
adrci>show alert –tail 30

To display messages containing ORA-600 errors, issue the following command:

adrci>show alert –p "MESSAGE TEXT LIKE '%ORA-600%'*

Using adrci to List Trace Files

Trace files can be listed in adrci using the following command:

adrci>show tracefile

Using adrci to View Incidents

Use the following command to obtain a report about all the incidents in the current homepath(s):
adrci>show incident

If you want to obtain further details about an incident, issue the command with –p(predicate string) option:

adrci>show incident –mode detail –p "incident_id=112564"

You can use many fields in the predicate options.To list all available fields, issue the command
describe incident
Using adrci to Package Incidents

With adrci tool, you can package all the diagnostic files related  to specific problems into a ZIP file to submit it to Oracle support. To do so, you use special commands called IPS as shown in the following steps:

1. Create a logical package: use  ips create package command to create an empty logical package as shown in the example below. The package will be given a serially generated number.

adrci>ips create package

2. Add diagnostic data to the logical package: this is done by ips add incident command as shown below:

adrci>ips add incident 112564 package 1

Actually, there are formats of the ips create package command which enables you to perform the steps 1 and 2 in one command. Following are those command formats:

O ips create package problem
O ips create package problem key
O ips create package incident
O ips create package time

3. Generate the physical package. The files related to the incident will be collected in a ZIP file. The   following  example shows the command to perform this task:

adrci>ips generate package 1 in /u01/myfiles/incidents

If you decide to add or change any diagnostic data later, you can do so by generating an
Incremental ZIP file. Modify the command as follows to achieve that:

adrci>ips generate package 1 in /u01/myfiles/incidents incremental

You will notice that the generated file has the phase INC in its name indicating that it is an incremental ZIP file.

ips commands behavior is controlled by various configuration options. To display those configuration
options, use the command ips show configuration