ORA-00600 internal error code, arguments [4819]

ORA-00600: internal error code, arguments: [4819]
Problem Description
While reviewing database alert log files it was report an ORA-600 [4819] error followed by ORA-00308 and ORA-27037 errors like below.

ORA-00600: internal error code, arguments: [4819], [], [], [], [], [], [], []
ORA-00308: cannot open archived log '/home/oracle/archive/arc_ARJU_001.arc'
ORA-27037: unable to obtain file status

This problem started to happen because recently database has been switched into NOARCHIVELOG mode from ARCHIVELOG mode.

Cause of the Problem
The problem arises because the LOG_ARCHIVE_DEST_n parameter is still set to point to an archive log destination and the default value for the LOG_ARCHIVE_DEST_STATE_n parameter is 'ENABLE'. Based on these two settings database still attempts to open the required archive log file during transaction recovery.

Solution of the Problem
To resolve this problem, mark the destination as deferred and null out the archive destination, That is
1) Login as sysdba.
SQL> connect / as sysdba

2) Set the archive_dest_state_1 to defer.
SQL> alter system set log_archive_dest_state_1 = defer scope=both;

3) Set log_archive_dest_1 to null.
SQL> alter system set log_archive_dest_1 = '' scope=both;

ORA-00600 internal error code, arguments [13013] [5001]

http://oraclemamukutti.blogspot.in/2011/06/ora-00600-13013-5001.html




ORA-600 [13013] [a] [b] [c] [d] [e] [f]


This format relates to Oracle Server 8.0.3 to 10.1

Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])
Arg [f] Code




ORA-00600: internal error code, arguments: [13013], [5001]

ORA-00600: internal error code, arguments: [13013], [5001], [268], [8447338], [5], [8447338], [17], [], [], [], [], []


Select object_name,object_type,owner from dba_objects where data_object_id=268;


select * from dba_objects where object_name='SMON_SCN_TIME';

select * frOm dba_indexes where table_name='SMON_SCN_TIME';


Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') from dual;

Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') from dual;


 drop index "SYS"."SMON_SCN_TIME_TIM_IDX";


  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("TIME_MP")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ;

 drop index "SYS"."SMON_SCN_TIME_SCN_IDX";

  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("SCN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX" ;


analyze table SYS.SMON_SCN_TIME validate structure cascade online;






======================================================================================================


SQL>Select object_name,object_type,owner from dba_objects where data_object_id=<value reported in argment b>

Once the Object is identified run the following :

The below command check if the table has corruption or not .

SQL> Analyze table <owner>.<table name> validate structure online ;

If this goes fine table doesnot have corruption. For next command.

If the above command fails with ORA-1498 go to Section 3


The below command check if table/index has corruption or not

SQL>Analyze table <owner>.<table name> validate structure cascade online ;


If the above command errors out with ora-1499 it indicates a corruption in index.

Go to section 2 for resolution

Run dbverify on the datafile reported in the error

Arg [c] in the ora-0600[13013] would give the Relative DBA

For example

ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],[155254965], [17], []


Arg [c] --> rdba-->155254965

Use this value and find the file and block number for this dba

select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;

RFILE# Block#
---------- ----------
37 65717

You an run dbveirfy on datafile with rfile#=37

SQL>Select name from v$datafile where rfile#=37

dbv file=<location of datafile> blocksize=<db_block_size>








Section 2 >How to resolve if a Index is corrupted

You would need to drop and recreate the index

Ensure before dropping the Index

SQL>Spool /tmp/createindex.sql

SQL>Set long 100000000

SQL>Select dbms_metadata.get_ddl('INDEX','<Index name>',<'user name>') from dual

SQL>Spool off

Refer the Following note to Identify the index

Note 563070.1

Title: ORA-1499. Table/Index row count mismatch

Please note if there is just one index in the table then you can use dbms_metadata.get_ddl to get the script. of the index and drop and recreate it.


Section 3> How to resolve if table is corrupted

Option a> Backup is available

Ora-1498 would be reported on the table.

The trace file from Ora-1498 would contain following information

Example

Block Checking:DBA = 1066265208,Block Type = KTB-managed data block --->
file 254,block 911992
data header at 0xc00000010118e07c
kdbchk: avsp(816) > tosp(812)
Block header dump: 0x3f8dea78
Object id on Block? Y
seg/obj: 0x155b452 csc: 0x05.7b2b4ee6 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3f8dde0c ver: 0x01
inc: 0 exflg: 0

Note the DBA value reported in the trace file DBA = 1066265208
Convert this to find the file number and block number having issue

Sql>Select dbms_utility.data_block_address_file('1066265208') from dual ;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE('1066265208')
--------------------------------------------------
254

Sql>Select dbms_utility.data_block_address_block('1066265208') from dual ;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK('1066265208')
---------------------------------------------------
911992


Run dbverify on the datafile containing the table

dbv file=<location of datafile> blocksize=<db_block_size>

Corruption would be reported on the block.

If you have an Rman backup do a Rman block recovery (Take the file number and block number affected from the trace file)

Rman> Blockrecover datafile <no> block <block number>

Or

If you have a user managed backup you can do an restore and recovery from good copy of the problematic datafile




Option b> Backup is not available

If no backups are available then use event 10231 at session level to create a salvage table

SQL>alter session set events '10231 trace name context forever, level 10'
SQL> Create table <owner>.salvage_table as select * from <Corrupted table> ;

Rename the Original table to old.

Rename salvage table to Original table name



or

You can use dbms_repair script. to mark the block soft corrupt.

Note 556733.1

DBMS_REPAIR SCRIPT.


How to resolve when Smon terminates the instance due to Ora-00600[13013]


If Smon is terminating the instance then.

Set event 10513 and startup the database

event="10513 trace name context forever, level 2"


SQL>Startup mount ;

SQL>Show parameter event

SQL>Alter datatabase open ;

Identify the object involved using information from Section 1.



How to resolve the issue if the object involved belongs to system tablespace


System objects are very important.
Please open a Service request with Oracle support if system tables are involved.






Identifying the row having issue when the table is having corruption.


1>Once the error occurs, using the ORA-600 arguments, gather the following information :

ORA-00600: internal error code, arguments: [13013], [5001], [57353], [155254965], [261],
[155254965], [17], []


arg b : [57353] - it is the OBJECT_ID
arg c : [155254965] - it is the block address in Decimal
arg d : [261] - is it the slot number

2. Translate the the block address in Decimal to a file# and block #

select dbms_utility.data_block_address_file(155254965) Rfile# ,dbms_utility.data_block_address_block(155254965) "Block#" from dual;

RFILE# Block#
---------- ----------
37 65717

The Relative file is 37
The block number is 65717

Find the data_object_id for this object

Argument b is Object_id--> 57353

SQL>Select data_object_id ,object_name,owner from dba_objects where object_id=57353 ;

3. Create the rowid using dbms_rowid.rowid_create(1,DATA_OBJECT_ID,FILE#,BLOCK#,SLOT#)

In this case :

select dbms_rowid.rowid_create(1,57353,37,65717,261) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAOAJAAlAAAQC1AEF

4. You can select from table and identify the record causing the issue

SQL> Select * from <owner>.<table name> where rowid='AAAOAJAAlAAAQC1AEF';




Are there any Known Bug

There is a internal bug number 5085288

fixed in 11.1

Details

ORA-600 [13013] [5001] error can occur on a MERGE command if the DELETE pass encounters a consistent read (CR) error due to the update pass having updated the same
row and column previously.

Check for availability of one off patch usingpatch 5085288

Bug 4549673

Abstract: ORA-30926 / OERI:13030 during update
Fixed-Releases: 9208 A204 B106

Details:
ORA-30926 (in Oracle 9i) or ORA-600 [13030] (in Oracle10g) can occur
during an update DML. This can occur if an internal ORA-1551 error
occurs and is trapped (1551 errors are not visible to client
code and are trapped and handled internally)

Fixed In Ver: 11.0
Check for availability of one off patch usingpatch 5085288

ORA-00600 internal error code, arguments [kdsgrp1], [], [], [], [], [], [],[]

ORA-00600: internal error code, arguments: [kdsgrp1] , This error may occur on a RAC database when (re)building index online.

The fixes for this bug are in Metalink Note : 285586.1

As a workaround please try to rebuild the index either offline or online with as little as possible activity on the affected table.

If error continues check the table for chained rows.


---------------------------------


If this error comes up, you have to do the following:

use adrci to package the incident. How to do that is described here.
adrci
show incidents
ips pack incident … to …

afterwards check into the trace file in the package.
<the package zip>\diag\rdbms\$ORACLE_SID\$ORACLE_SID\incident\incdir_<some kind of id>look into the trc files searching right in the beginning for an sql statement.

If you find a select with bind variables, check the trace furthermore for "bind" or "variable" to get the variable which has been used in that specific statement.

Find out, which tables are involved in the specific SQL and analyze each by using followin syntax

ANALYZE TABLE <SCHEMA.TABLE_NAME> VALIDATE STRUCTURE CASCADE;

You should get an error like, this:

ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

go to the tracefile, that just popped up in the background dump destination.

In that file, look for the string "mismatch".
You should now arrive at a line, which explains what exactly is wrong.

In my case, I found a mismatch between an index of the table and the table itself.
The tracefile with the mismatch pointed out the object_id of the specific index, which was corrupt.

You should be able to identify it by

select owner, object_name, object_type from dba_objects where object_id=<OBJECT_ID>;

If you rebuild/recreate indexes, the analyze table should run through without any error message.


To rebuild an index, use
alter index SCHEMA.INDEX_NAME rebuild online;

or get the metadata and recreate it using

set long 999999
set lines 160 pages 9999
col text for a150
select dbms_metadata.get_ddl('INDEX','<INDEX_NAME>','<INDEX_OWNER>') as text from dual;
replace the "create index" with "create or replace index"
and run it.

To check the status of all other tables, an idea is to extract the validate.sql script out of
Note:100419.1 SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace

Then, log as sys user and run this script:

sql> @validate
This will create the "ValidateStructure" package.

After this, open a spool file and enable SERVEROUT

sql> spool myoutput.log
sql> execute dbms_output.enable(1000000);
sql> set serveroutput on
and run:

sql> execute ValidateStructure.TS('TABLESPACE_NAME', TRUE);
This will run until all requested items are scanned.

Errors from the ANALYZE commands are output to DBMS_OUTPUT and so any failing objects are listed when all TABLES / CLUSTERS have been analyzed.
More detailed output from failing ANALYZE commands will be written to the user trace file in USER_DUMP_DEST

Although, initially written for 10.2, this should still work fine against 11g databases.

Note 1413928 - Index corruption wrong results after rebuild index ONLINE

Other terms
Wrong Results, Corruption (Index), ORA-08102 / ORA-01499,
ORA-00600 [kdsgrp1], ORA-00600 [qertbFetchByRowID], ora-01452

Reason and Prerequisites
Wrong results or the next errors can be produced by SQL statements:

ORA-8102 by a delete/update
ORA-1499 by "analyze table validate structure cascade"
ORA-600 [kdsgrp1]
ORA-600 [qertbFetchByRowID]

This is Oracle Bug 7329252 which is fixed in Oracle release 10.2.0.5.

Solution
Install the relevant patch for your platform.
You can find this on SAP Service Marketplace in the software center

ora-00942 ora-06512 sys.dbms_snapshot

Solution

1) compile mv

2) recreate mv

---------
ORA-942 on Materialized View Refresh after Master Mview Recreated on Different Table [ID 867042.1]

exec dbms_mview.refresh(' ',C);

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at "LDBO.SP_MVRKFOUTILIZATION", line 4
ORA-06512: at line 1

In this case, you do not know which of the tables within the materialized view is throwing the ORA-00942 error.  This user was advised to enable SQL Trace (TKPROF) st the session level to determine the exact table that is seeing the ORA-00942 error.


 could solve an occurance of this problem by calling
ALTER MATERIALIZED VIEW my_mview COMPILE;
before refreshing the mview. But I do not know why this was necessary in this case.


Possibly either of
a. the source table itself get's DROPped and reCREATEd -- and an MV Refresh within the DROP-CREATE cycle would error with ORA-942
b. grants on the source table get DROPped and reCREATEd -- and ....same as above ......
but dont know which table
The MV Definition (which is the QUERY column in USER_MVIEWS/DBA_MVIEWS) shows the query that the MV uses.
(QUERY is a LONG column so you must SET LONG 1000000 in sqlplus before querying the USER_MVIEWS/DBA_MVIEWS view



SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME IN ('DBMS_MVIEW',> 'DBMS_SNAPSHOT');

SELECT * FROM ALL_TAB_PRIVS_RECD WHERE GRANTEE = 'PUBLIC' AND TABLE_NAME = 'DBMS_SNAPSHOT';

ORA-29273 HTTP request failed ORA-24247

drop and recreate acl


begin
  dbms_network_acl_admin.drop_acl(acl=>'utl_http_access.xml');
end;
/

commit;
begin
  dbms_network_acl_admin.drop_acl(acl=>'utl_inaddr_access.xml');
end;
/

commit;
begin
  dbms_network_acl_admin.drop_acl(acl=>'utl_mail.xml');
end;
/
commit;

begin
  dbms_network_acl_admin.drop_acl(acl=>'utl_http.xml');
end;
/
commit;




Exec dbms_network_acl_admin.create_acl ('utl_http_access.xml','Normal Access','LDBO',TRUE,'connect',NULL,NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_http_access.xml', principal =>  'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_http_access.xml', '*',NULL,NULL);
Commit ;
               
Exec dbms_network_acl_admin.create_acl ('utl_inaddr_access.xml','Normal Access','LDBO',TRUE,'resolve',NULL, NULL);
Exec dbms_network_acl_admin.add_privilege (acl => 'utl_inaddr_access.xml', principal =>  'LDBO',is_grant => TRUE, privilege => 'resolve');
Exec dbms_network_acl_admin.assign_acl ('utl_inaddr_access.xml', '*',NULL,NULL);
commit;

Exec dbms_network_acl_admin.create_acl ('utl_mail.xml','Allow mail to be send','LDBO',TRUE,'connect' );
Exec dbms_network_acl_admin.add_privilege ('utl_mail.xml','LDBO',TRUE,'resolve');
Exec dbms_network_acl_admin.assign_acl('utl_mail.xml','*',NULL,NULL);
commit ;


Exec dbms_network_acl_admin.create_acl ('utl_http.xml','HTTP Access','LDBO',TRUE,'connect',null,null);
Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','LDBO',TRUE,'resolve',null,null);
Exec dbms_network_acl_admin.assign_acl ('utl_http.xml','*',NULL,NULL);
commit;

Grant Execute on utl_inaddr to LDBO ;
Grant Execute on utl_http to LDBO ;

select acl, principal, privilege, is_grant from dba_network_acl_privileges;

SELECT * FROM DBA_NETWORK_ACLS;

ORA-01591 lock held by in-doubt distributed transaction string

ORA-01591: lock held by in-doubt distributed transaction string

Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.




ORA-01591: lock held by in-doubt distributed transaction string
The first thing to do is to check the result of these queries:
Check the following table:

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

From the above queries keep all the value of LOCAL_TRAN_ID in each table and try to force commit or rollback.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
EXEC DBMS_TRANSACTION.rollback_force('LOCAL_TRAN_ID')
--this changed state prepared to forced_rollback after that
COMMIT ;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');
COMMIT;

Repeat each steps for each LOCAL_TRAN_ID, turn on recovery for distributed transaction
crosscheck these tables (must be empty):

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

ORA-14402 updating partition key column would cause a partition change

Cause:

You want to update a record and new value will belong to a different partition.


Solution:

Block must be physically re-located. You can not do this if row movement is not enabled:


ldbo@ari1112>  update ledger set branchcode='2816' Where firmnumber='ACML-00001' and Oowncode='26964
0000';
 update ledger set branchcode='2816' Where firmnumber='ACML-00001' and Oowncode='269640000'
        *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


ALTER TABLE <Table_Name> enable row movement;

ORA-01031 insufficient privileges with dba IIS application

iis reset after do changes in sqlnet.ora


Do you have:
SQLNET.AUTHENTICATION_SERVICES=(NTS)

In your sqlnet.ora?
Is your Windows user in the ORA_DBA group?

sho parameter emote_login_passwordfile
select * from v$pwfile_users;

Set
SQLNET.AUTHENTICATION_SERVICES = (ALL)
in sqlnet.ora file in $ORACLE_HOME/network/admin/sqlnet.ora

Also set
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

Bouce the database and then try to connect through sqlplus / as sysdba
Refer to following MOS:
Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]


ORA-1031: Insufficient Privileges

This is one of very common and frequently occuring error . According to the docs note ,the cause of the ORA-01031  is :

Cause : An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login .


Action :  Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the appropriate privilege at a higher label, ask the database administrator to re-grant the privilege at the appropriate label .

As we see the above action , nothing is explained in details and only mention about the privileges issues . But ORA-1031 may not only occurs with insufficient privileges but may other reasons too. Here i have try to cover all the possible reason of this error .

There are generally two method through which a user can connect to oracle database AS SYSDBA .
1.) OS authentication :
2.) Password file authentication :


1.) OS Authentication : OS authentication allows Oracle to pass control of user authentication to the operating system .The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met:

i.) The  user is a member of a special group : The OS user should belong to the OSDBA group in order to login as sysdba. On Unix/Linux the default name of  these group is "dba" and on Windows the name of the group is "ORA_DBA" .


ii.) The OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly) : On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS) or (NONE) or ALL.


If we have configured the Operating System authentication and an OS user is a member of the special groups OSDBA or OSOPER then the OS user does not have to provide any credentials while connecting to the database as SYSDBA or AS SYSOPER from the oracle Server host machine when using the bequeath protocol. As long as the session is not established through the listener the OS Authentication will be used and the credentials provided will be ignored. This means that the users who are able to use the OS authentication can use any username and any password to connect to the database locally on the Oracle Server using the below syntax:


$sqlplus / as sysdba
or
SQL> connect any_username/any_password AS SYSDBA


2.) Password File Authentication :  The password file provides a method to authenticate privileged users from a remote (over sqlnet / listener) location . By default the user SYS gets an entry in the passwordfile when we create it so we can connect to a remote database . The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile. For example
$sqlplus sys/xxxx@db_name as sysdba


Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive". For more about Password file Click Here


If we  grant the SYSDBA or SYSOPER privilege to any additional user then that user will also get an entry in the passwordfile: the hashed password of that user is then copied to the passwordfile , when this user connects,the effective user will be SYS .

Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that we can connect with any username/password combination.


Here we will considering all the possible issue related to ORA-1031


Case 1  :
One of the reason of getting ORA-1031 may be because the osuser is not the member of dba group .In case of  Linux , the osuser user must be the memeber of   "DBA"  group . To check the group the use the below command .
[oracle@Ramtech ~]$ id
uid=501(oracle) gid=502(oinstall) groups=501(dba),502(oinstall)


In case of Window, OS user must be a member of ora_dba group . Check whether the OS user is a member of ORA_DBA or not by using the below command  :
C:\>echo %username%                    ( for current osuser )
Neerajs


C:\>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
------------------------------------
Neerajs
NT AUTHORITY\SYSTEM
The command completed successfully.


If the osuser is not the member of the above group then add the osuser to the DBA and ORA_DBA in case of Linux and Window respectively.

Case 2 :
Check the value of  the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA .


On Unix/Linux  : This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following  values:
SQLNET.AUTHENTICATION_SERVICES = (ALL)
 or
SQLNET.AUTHENTICATION_SERVICES = (BEQ,<the strong auth method>)
Where  <the strong auth method> can be any combination of the following values: TCPS, KERBEROS5, RADIUS .I will cover more about the sqlnet parameter in my later post .


On Windows : This parameter should be set to NTS or if needed we can add other strong authentication methods besides NTS as such NONE .
SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)


Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database. On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if we  are in this scenario:

A.) It is important that this user is a direct member of the local ORA_DBA group .
B.) Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally.
C.) Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent.
D.) Check whether the Oracle Service is started by an user whose name contains non ASCII characters .


CASE  3 :
Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED .


SQL> show  parameter  remote_login_passwordfile
NAME                                           TYPE           VALUE
-------------------------------            --------      ---------------
remote_login_passwordfile         string        EXCLUSIVE


If the parameter is not set correctly then modify it and then restart the database:
 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile ;


Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions .
On Unix/Linux  : The password file with the name  orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command .


[oracle@Ramtech dbs]$orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n
[oracle@Ramtech dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Jan 10 14:44 orapwcomcast


On Windows : The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):

The folder pointed to by the registry key  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_<%ORACLE_SID%>_PWFILE
The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_PWFILE
The default location ( %ORACLE_HOME%\database)


If the password file does not exist in the right folder then create it using the orapwd command:


C:\> cd  %ORACLE_HOME/database
C:\> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n


Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.


CASE 4 :
Check whether the user was granted the SYSDBA privilege. Sometimes , we may get this error when we don't have sysdba privileges and try to connect as sysdba when sqlnet.authentication_services is NONE . For example :
C:\>sqlplus scott/tiger@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 10 17:56:27 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges


Check the v$pwfile_users  view to find the user which is having sysdba or sysoper privileges .
SQL> select * from v$pwfile_users;
USERNAME         SYSDB   SYSOP     SYSAS
------------          -------     --------     --------
SYS                    TRUE     TRUE       FALSE


If the user is not granted the privilege then log as sys user and run:
SQL> grant SYSDBA to scott ;
where scott is the user wewant to use to connect as sysdba.


CASE   5 :  If  the problem is not solved after reviewing the above notes open a service request with Oracle Support .

ORA-00600 internal error code, arguments [2662]

ORROR at line 1:
oRA-00603: ORACLE server session terminated by fatal error
oRA-00600: internal error code, arguments: [2662], [0], [6202286], [0], [6211768], [12582976], [], [], [], [], [], []
oRA-00600: internal error code, arguments: [2662], [0], [6202285], [0],
6211768], [12582976], [], [], [], [], [], []
oRA-01092: ORACLE instance terminated. Disconnection forced
oRA-00600: internal error code, arguments: [2662], [0], [6202283], [0],
6211768], [12582976], [], [], [], [], [], []
rocess ID: 4168
ession ID: 391 Serial number: 3




ORA-00600: internal error code, arguments: [2662], [0], [103516293]

1. create a pfile from spfile. Set parameter

_ALLOW_RESETLOGS_CORRUPTION = true
UNDO_MANAGEMENT = MANUAL

2. startup mount pfile=”;
3.

recover database until cancel;
or
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

recover database using backup controlfile;


4. Enter CANCEL to cancel recovery - there are NO archive logs applied.

5. Enter ALTER DATABASE OPEN RESETLOGS


==============solutions

restart oracle services many times


==============

http://myoracledbablogon.blogspot.in/2010/09/all-that-ive-found-to-recover-database.html


ERROR:            


  Format: ORA-600 [2662] [a] [b] [c] [d] [e]

VERSIONS:
  versions 6.0 to 10.1

DESCRIPTION:

  A data block SCN is ahead of the current SCN.

  The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
  stored in a UGA variable.

  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
  internal error.

ARGUMENTS:
  Arg [a]  Current SCN WRAP
  Arg [b]  Current SCN BASE
  Arg [c]  dependent SCN WRAP
  Arg [d]  dependent SCN BASE
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

FUNCTIONALITY:    
  File and IO buffer management for redo logs

IMPACT:
  INSTANCE FAILURE
  POSSIBLE PHYSICAL CORRUPTION

SUGGESTIONS:      
   
  There are different situations where ORA-600 [2662] can be raised.

  It can be raised on startup or duing database operation.

  If not using Parallel Server, check that 2 instances have not mounted
  the same database.

  Check for SMON traces and have the alert.log and trace files ready
  to send to support.

  Check the SCN difference [argument d]-[argument b].

  If the SCNs in the error are very close, then try to shutdown and startup
  the instance several times.

  In some situations, the SCN increment during startup may permit the
  database to open. Keep track of the number of times you attempted a
  startup.

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle
  Support Services for further analysis.

REDO UNDO CORRUPT DUE TO POWER FAILURE ORA-00333 ORA-600 [4194]

PFILE

_ALLOW_RESETLOGS_CORRUPTION = true
 UNDO_MANAGEMENT = MANUAL


RENAME SPFILE


recover database until cancel;
or
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

cancel

alter database open resetlogs;

it will recreate redologs.


restart oracle services


--------------------IF ERROR: ORA-00600: internal error code, arguments: [2662]

restart oracle services many times

Format: ORA-600 [2662] [a] [b] [c] [d] [e]

 The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
  stored in a UGA variable.

  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
  internal error.

ARGUMENTS:
  Arg [a]  Current SCN WRAP
  Arg [b]  Current SCN BASE
  Arg [c]  dependent SCN WRAP
  Arg [d]  dependent SCN BASE
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

FUNCTIONALITY:    
  File and IO buffer management for redo logs

IMPACT:
  INSTANCE FAILURE
  POSSIBLE PHYSICAL CORRUPTION


--------------------UNDO RECREATION

now undo is corrupted
ORA-00600: internal error code, arguments: [4194]
drop corrupted undo tablespace and create a new undo tablespace.

but database is not able to open due to undo corruption

-----------------------------add following in pfile


undo_management = manual
event = '10513 trace name context forever, level 2'

--------------
Set the Oracle event 10513 before opening the database. This event disables Oracle from rolling back an uncommitted transaction and should be used very carefully.


select file_name,tablespace_name from dba_data_files;

startup restrict pfile=


CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\APX1213D\UNDOTBS02.ORA' SIZE 500M REUSE AUTOEXTEND ON;

shut immediate

change undo_tablespace=UNDOTBS2 into parameter file

startup

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;


REMOVE ADD PARAMETER FROM PFILE

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],[13836], [16279], [16685], [], [], [], [], [], [], []

PFILE

_ALLOW_RESETLOGS_CORRUPTION = true
 UNDO_MANAGEMENT = MANUAL


RENAME SPFILE


recover database until cancel;
or
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

cancel

alter database open resetlogs;

it will recreate redologs.


restart oracle services


--------------------IF ERROR: ORA-00600: internal error code, arguments: [2662]

restart oracle services many times

Format: ORA-600 [2662] [a] [b] [c] [d] [e]

 The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
  stored in a UGA variable.

  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
  internal error.

ARGUMENTS:
  Arg [a]  Current SCN WRAP
  Arg [b]  Current SCN BASE
  Arg [c]  dependent SCN WRAP
  Arg [d]  dependent SCN BASE
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

FUNCTIONALITY:    
  File and IO buffer management for redo logs

IMPACT:
  INSTANCE FAILURE
  POSSIBLE PHYSICAL CORRUPTION


--------------------UNDO RECREATION

now undo is corrupted
ORA-00600: internal error code, arguments: [4194]
drop corrupted undo tablespace and create a new undo tablespace.

but database is not able to open due to undo corruption

-----------------------------add following in pfile


undo_management = manual
event = '10513 trace name context forever, level 2'

--------------
Set the Oracle event 10513 before opening the database. This event disables Oracle from rolling back an uncommitted transaction and should be used very carefully.


select file_name,tablespace_name from dba_data_files;

startup restrict pfile=


CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\APX1213D\UNDOTBS02.ORA' SIZE 500M REUSE AUTOEXTEND ON;

shut immediate

change undo_tablespace=UNDOTBS2 into parameter file

startup

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;


REMOVE ADD PARAMETER FROM PFILE

ORA-01157 ORA-01110 Undo Corrupt

===========================
undo_management = manual
pfile

shutdown immediate;
startup

alter database datafile 'd:\rkdatabase\undotbs01.ora' offline drop;

alter database open;

drop tablespace undotbs1;

create undo tablespace undotbs1 datafile 'd:\rkdatabase\undotbs02.ora' size 500m autoextend on next 10m maxsize unlimited;

undo_management = auto


shutdown immediate
startup


===========================

Recovery of UNDO tablespace in a Non Archive Log mode Database

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/oradata/burp/undotbs01.dbf'


Since the database in Non Archive Log mode, we cannot open the database by taking the datafile offline using ‘alter database datafile 2 offline’,but we need to use ‘…OFFLINE FOR DROP‘. This will mark the datafile for subsequent dropping.A datafile once marked ‘OFFLINE FOR DROP’ can never be brought online.

SQL>alter database datafile 2 offline for drop;
SQL>alter database open;

Now we need to create another undo tablespace for the transactions to use. Once the new undo tablespace is created,shutdown the database and edit the init.ora file and change the parameter undo_tablepace=<new_undo_tablespace>

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;

SQL>shutdown immediate;
edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;
Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;
While database is OPEN
+++++++++++++++++++

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;


SQL>shutdown immediate;
edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;
Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;





======================================or

 alter system set undo_management = manual scope=spfile;


shut immediate

startup pfile

-----This is to confirm no backups available

rman target /
RMAN> restore tablespace undotbs1;
RMAN> exit

SQL> alter database datafile 'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7OS7JZJV_.DBF' offline drop;

alter database open;

drop tablespace undotbs1;


create UNDO tablespace undotbs1 datafile 'C:\APP\ORACLE11GR2\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS1_new.dbf' size 25m autoextend on next 1m maxsize 50m;



alter system set undo_management = auto scope=spfile;

Listener ORA-00119 ORA-00132

Issues with setting Local Listener ORA-00119 ORA-00132


Today I was caught up in a issue with TNS while trying to setup SOA setup with OEM

Windows64bit machine

Database 11gr2

As per requirement we wanted to run the listener not on default port (1521 ) but some other port. Now as per Documentation if i want my non default port listener to register with pmon i need to set local_listener

“If you want PMON to register with a local listener that does not use TCP/IP, port 1521, then configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.”

I though it will be a straight forward process just go ahead and run the alter system set local_listener=listener2 scope=both; but to surprise it didnt work as expected it returned an error

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER2' … so a straightforward process didnt work as expected.


alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=TAQVIA-IN.xx.xxx.com)(PORT=1522))))' sid='orcl64' scope=both





----------------------------------Startup fails with oracle error ORA-00119, ORA-00132-----------------------------------------------
Error Description:
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'


Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.

PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.

Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.

Solution of The problem
-----------------------------
A)Correct the tnsnames.ora

i)Determine if the tns alias is good by using tnsping.

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name

So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.

SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')

and run tnsping utility,

SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters

So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.

SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))

iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.


B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;

2)Open the pfile and remove the LOCAL_LISTENER entry.

$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora

3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;

4)Start the database.

SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.

Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.

In order to set dynamically inside spfile and in memory log in with dba privileges and issue:

ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';

In my system,

SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=neptune)(port=1522))

ora 12560 tns protocol adapter error database DBCA fails

ora 12560 tns protocol adapter error database DBCA fails

-------------------Most common reasons for ORA-12560 in Windows are----------------------------

- firewall (port 1521)
- DHCP and not using loopback adapter
- antivirus
-Windows Domain Control should not be installed(Oracle recommends to NOT install it on a domain controller.)
-There was no ORA_DBA group and no ORADBA user on the server




while creating database in oracle 11.2.0.1 using DBCA i am getting following error.

ora 12560 tns protocol adapter error

i am using administrator userid . any soultion for this

1)
It seems you use windows OS so go %ORACLE_HOME%\network\admin directory and edit sqlnet.ora then change/add context below text
SQLNET.AUTHENTICATION_SERVICES = (NTS)

same error

2) Don't Install from mstsc
Did you try creating database through remote or directly server? if remote then try it from directly server.


i tried directly from server. oracle database software installed successfully. After that database creation i am getting this error. After getting ora 12560 tns protocol adapter error
i added tnsnames through NETCA. After that i started dbca i am getting same error. Then i checked tnsnames.ora file the entry i added NETCA is not there in the file.please provide any solution

3)

Are there only one ORACLE_HOME?
Post there sqlnet.ora file context.
Did you get other error messages than ora 12560?

Only ione oracle_home is there.Other than ora 12560 i am not getting any error. The sqlnet.ora file is given below

# sqlnet.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)



4)

refer alert.log(C:\Oracle\product\11.2.0\diag\rdbms\<sid>\<db_unique_name>\trace directory)

In trace file i am getting following error in the OraVSSW file.
Could not execute [C:\Oracle\product\11.2.0\dbhome_1\bin\OraVSSW.exe ORAWH /d]; error 1


In this location no file is generated C:\Oracle\diag\rdbms


5)
ORA-12560 running DBCA on Windows 2008 AD Controller [ID 729774.1]


Another Solution:
Install the Oracle Software on a Non-Controller machine.
You can demote the Controller machine to a Member machine.
Perform the install, create the database.
Once everything is installed and setup, you can promote the machine to a Controller.
Be aware, you may have to go through these sames steps if you want to create another Database.


6) ORA-12560 - Database Creation Assistant (DBCA) Fails With TNS Error [ID 747243.1]

Cause
There was no ORA_DBA group and no ORADBA user on the server
System policies removed the group and/or the user used to install Oracl

Solution
After creating the group/user account (by matching the ones on the current production server), the DBCA ran to completion and created the database successfully.

Check with the sys admins to see if there is a policy or process in place to remove certain users and groups.  The ORA_DBA group and the Oracle user used for the installation must remain intact.

If the problem remains, after creating the group/user account, you might need to remove Oracle and reinstall the software.

Oracle alert.log to Table

The following script creates two tables: read_alert andread_alert_disk. read_alert_disk is an external table and contains the content of the alert log. read_alert will be empty after this script has been executed. It is used by the update_alert_log script, shown further below. define alert_length="2000" drop table alert_log; create table alert_log ( alert_date date, alert_text varchar2(&&alert_length) ) storage (initial 512k next 512K pctincrease 0); create index alert_log_idx on alert_log(alert_date) storage (initial 512k next 512K pctincrease 0); column db new_value _DB noprint; column bdump new_value _bdump noprint; select instance_name db from v$instance; select value bdump from v$parameter where name ='background_dump_dest'; drop directory BDUMP; create directory BDUMP as '&&_bdump'; drop table alert_log_disk; create table alert_log_disk ( text varchar2(&&alert_length) ) organization external ( type oracle_loader default directory BDUMP access parameters ( records delimited by newline nologfile nobadfile fields terminated by "&" ltrim ) location('alert_&&_DB..log') ) reject limit unlimited; update_alert_log.sql Now, after the two tables are created, the alert_log table can be filled with the following script. It only loads those records that are greater than the last time it loaded. And it loads the date/time on every line for convienance. It also helps when the alertlogs get rotated. You still keep the history within an Oracle table. Finally, it also strips out all the «crap» that is really not needed to see if you are looking for errors. update_alert_log.sql set serveroutput on declare isdate number := 0; start_updating number := 0; rows_inserted number := 0; alert_date date; max_date date; alert_text alert_log_disk.text%type; begin /* find a starting date */ select max(alert_date) into max_date from alert_log; if (max_date is null) then max_date := to_date('01-jan-1980', 'dd-mon-yyyy'); end if; for r in ( select substr(text,1,180) text from alert_log_disk where text not like '%offlining%' and text not like 'ARC_:%' and text not like '%LOG_ARCHIVE_DEST_1%' and text not like '%Thread 1 advanced to log sequence%' and text not like '%Current log#%seq#%mem#%' and text not like '%Undo Segment%lined%' and text not like '%alter tablespace%back%' and text not like '%Log actively being archived by another process%' and text not like '%alter database backup controlfile to trace%' and text not like '%Created Undo Segment%' and text not like '%started with pid%' and text not like '%ORA-12012%' and text not like '%ORA-06512%' and text not like '%ORA-000060:%' and text not like '%coalesce%' and text not like '%Beginning log switch checkpoint up to RBA%' and text not like '%Completed checkpoint up to RBA%' and text not like '%specifies an obsolete parameter%' and text not like '%BEGIN BACKUP%' and text not like '%END BACKUP%' ) loop isdate := 0; alert_text := null; select count(*) into isdate from dual where substr(r.text, 21) in ('2003','2004','2005','2006','2007') and r.text not like '%cycle_run_year%'; if (isdate = 1) then select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr') into alert_date from dual; if (alert_date > max_date) then start_updating := 1; end if; else alert_text := r.text; end if; if (alert_text is not null) and (start_updating = 1) then insert into alert_log values (alert_date, substr(alert_text, 1, 180)); rows_inserted := rows_inserted + 1; commit; end if; end loop; sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS')); sys.dbms_output.put_line('Rows Inserted: '||rows_inserted); commit; end; / Let's execute the script: SQL> @update_alert_log Inserting after date 01/01/80 00:00:00 Rows Inserted: 17361 PL/SQL procedure successfully completed. The alert_log table now contains the errors as recorded in thealert.log file: select alert_date, substr(alert_text,1, 69) from alert_log;

ORA-02290 check constraint violated

Select 'SELECT * FROM ' || Table_Name || ' MINUS SELECT * FROM ' || Table_Name || ' WHERE ' || Search_Condition || ';'  From User_Constraints Where Constraint_Type='C'
AND Constraint_Name Like '%CK%LEDGERAM%';
----AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE Data_Type NOT LIKE '%LONG%');

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"


spool c:\constr1.txt
set line 9999
set serveroutput on
BEGIN
FOR r IN (
SELECT cns.table_name, cns.constraint_name
    , cns.search_condition
FROM   user_constraints cns
    , user_cons_columns col
WHERE  cns.constraint_type = 'C'
AND    col.owner = cns.owner
AND    col.table_name = cns.table_name
AND    col.constraint_name = cns.constraint_name
)
LOOP
DBMS_OUTPUT.PUT_LINE('SELECT * FROM ' || r.Table_Name || ' MINUS SELECT * FROM ' || r.Table_Name || ' WHERE ' || r.Search_Condition || ';');
END LOOP;
END;
/
spool off

ORA-02291: integrity constraint (string.string) violated - parent key not found

ORA-02291: integrity constraint (string.string) violated - parent key not found

Cause: A foreign key value has no matching primary key value.

Action: Delete the foreign key or add a matching primary key.

For an insert statement, this ORA-02291 error is common when you are trying to insert a child without a matching parent, as defined by a foreign key constraint.  In that case, you need to add the parent row to the table and then re-insert your child table row.




SELECT   DISTINCT uc.constraint_name||CHR(10)
||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
And      Uc.Constraint_Type = 'R'
----AND      uc.constraint_name = UPPER('&input_constraint_name');
AND      uc.constraint_name = 'FK_CHARGESCLIENT';



Select Distinct Cfirmnumber,Cpostingaccount From Tblclientchargesdetail
Minus
Select Distinct FIRMNUMBER,OOWNCODE From ACCOUNTS;


add the missing row into table.


---------------------------------------------------------------------------------------------------------------------------------------
 Select 'SELECT DISTINCT ' || Columns1 || ' from ' ||tablename || ' minus ' || 'SELECT DISTINCT ' || Columns2 || ' from ' || r_table_name || ';'
 From
 (
 select tablename,r_table_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
Nvl2(Cname7,','||Cname7,Null) || Nvl2(Cname8,','||Cname8,Null)
Columns1,
cname9 || nvl2(cname10,','||cname10,null) ||
Nvl2(Cname11,','||Cname11,Null) || Nvl2(Cname12,','||Cname12,Null) ||
Nvl2(Cname13,','||Cname13,Null) || Nvl2(Cname14,','||Cname14,Null) ||
Nvl2(Cname15,','||Cname15,Null) || Nvl2(Cname16,','||Cname16,Null) Columns2
From (
 Select B.Table_Name Tablename,
b.Constraint_Name,
Max(Decode( A.Position, 1, A.Column_Name, Null )) Cname1,
Max(Decode( A.Position, 2, A.Column_Name, Null )) Cname2,
Max(Decode( A.Position, 3, A.Column_Name, Null )) Cname3,
Max(Decode( A.Position, 4, A.Column_Name, Null )) Cname4,
Max(Decode( A.Position, 5, A.Column_Name, Null )) Cname5,
Max(Decode( A.Position, 6, A.Column_Name, Null )) Cname6,
Max(Decode( A.Position, 7, A.Column_Name, Null )) Cname7,
Max(Decode( A.Position, 8, A.Column_Name, Null )) Cname8,
c.Table_Name r_table_name,
Max(Decode( C.Position, 1, C.Column_Name, Null )) Cname9,
Max(Decode( C.Position, 2, C.Column_Name, Null )) Cname10,
Max(Decode( C.Position, 3, C.Column_Name, Null )) Cname11,
Max(Decode( C.Position, 4, C.Column_Name, Null )) Cname12,
Max(Decode( C.Position, 5, C.Column_Name, Null )) Cname13,
Max(Decode( C.Position, 6, C.Column_Name, Null )) Cname14,
Max(Decode( C.Position, 7, C.Column_Name, Null )) Cname15,
Max(Decode( C.Position, 8, C.Column_Name, Null )) Cname16
From User_Cons_Columns a,user_constraints b,User_Cons_Columns c
Where A.Constraint_Name = B.Constraint_Name
And B.R_Constraint_Name=C.Constraint_Name
And B.Constraint_Type = 'R'
And B.Constraint_Name='FK_CHARGESCLIENT'
Group By B.Table_Name, B.Constraint_Name,C.Table_Name
));

-----------------------------------

Change Notification not received from Oracle database 11.2.0.1 to client 11.2.0.3

                                      

Server version

Client version 11.2.0.1.0 11.2.0.2.0 11.2.0.2.0 + fix 11.2.0.3.0 11.2.0.1.0 works fails works fails 11.2.0.2.0 fails  works fails fails 11.2.0.2.0 + fix works fails works works 11.2.0.3.0 fails works works

Solution

Apply patches or upgrade versions as applicable to obtain a working combination.

A workaround can be used to bypass the authentication phase, which is where the problem behavior occurs.  To do that, set the following event in the database:

  

alter system set events '10867 trace name context forever, level 1';

ORA-12839: cannot modify an object in parallel after modifying it

ORA-12839: cannot modify an object in parallel after modifying it


Affects:

Product (Component)  Oracle Server (Rdbms)
Range of versions believed to be affected  Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected
11.2.0.2
11.1.0.7
Platforms affected  Generic (all / most platforms affected)
Fixed:

This issue is fixed in
12.1 (Future Release)
11.2.0.3
Symptoms:

Related To:

Error May Occur
Internal Error May Occur (ORA-600)
ORA-12839
ORA-600 [qerltcFetch_20]
Constraint Related
Parallel Query (PQO)
_disable_parallel_conventional_load
DML
Description

If you have a parent table and child table with a parent referential constraint
then running Parallel DML again this may fail with an unexpected ORA-12839
or even fail with an ORA-600.

Workaround
 Setting "_disable_parallel_conventional_load" = true can help avoid this.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References

Bug:9831227 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article