ORA-00257Archiver Error, Connect Internal Only Until Freed and ORA-16014 and ORA-00312 [ID 376923.1]
I have stop archivelog to stop error
Symptoms
Users cannot connect to the database :
0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 231 not archived, no available destinations
ORA-00312:online log 2 thread 1:'/u02/oradata/wlbranch/redo02.log'
Cause
The flash_recovery_area is full.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive Destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 231
Next log sequence to archive 231
Current log sequence 233
SQL> select group#,status archived from v$log;
GROUP# ARCHIVED
------------ ------------------------
1 INVALIDATED
2 INACTIVE
3 INACTIVE
SQL> archive log all;
ORA-16020: less destinations available than specified by
LOG_ARCHIVE_MIN_SUCCEEDED_DEST
The only one destination allowed (log_archive_min_succeed_dest= 1) is not able to perform the archiving.
Solution
1. Either allow more space in the DB_RECOVERY_FILE_DEST with the DB_RECOVERY_FILE_DEST_SIZE parameter :
SQL> alter system set db_recovery_file_dest_size=3G ;
2. Or to avoid the situation once the 3Gb is full , set the following parameters so that when the dest1 is full, archiving is automatically performed to the alternate dest2 :
log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oracle/product/10.1.0/db_1/flash_recovery_area'
db_recovery_file_dest_size=2G
Saturday, July 27, 2013
Problem while archiving the redo Log
https://forums.oracle.com/thread/977515
Hi all,
I m having few issues in my server...
I get the following error in the alert log of oracle..
There are many errors
1) No space left on device
2) ARC0: I/O error 19502 archiving log 1 to
'/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
3) ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*
Also please find the v$log file query
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 405 1073741824 1 NO CURRENT
60275455 20-OCT-09
2 1 403 1073741824 1 NO INACTIVE
59987366 19-OCT-09
3 1 404 1073741824 1 NO INACTIVE
60125083 20-OCT-09
check the mount point ... is it full ?
if yes then
change the destination .....
alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;
or
delete the archive logfile
or
But not a very good idea ....
SQL> ALTER SYSTEM ARCHIVE LOG STOP;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG start;
Hi all,
I m having few issues in my server...
I get the following error in the alert log of oracle..
There are many errors
1) No space left on device
2) ARC0: I/O error 19502 archiving log 1 to
'/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
3) ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*
Also please find the v$log file query
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 405 1073741824 1 NO CURRENT
60275455 20-OCT-09
2 1 403 1073741824 1 NO INACTIVE
59987366 19-OCT-09
3 1 404 1073741824 1 NO INACTIVE
60125083 20-OCT-09
check the mount point ... is it full ?
if yes then
change the destination .....
alter system set log_archive_dest_1='location=/opt/oracle/backup/arch' scope=both;
or
delete the archive logfile
or
But not a very good idea ....
SQL> ALTER SYSTEM ARCHIVE LOG STOP;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG start;
ALTER SYSTEM SWITCH LOGFILE Statement hangs
ALTER SYSTEM SWITCH LOGFILE Statement hangs
ALTER SYSTEM SWITCH LOGFILE hangs:
Step 1: Login as sysdba.
Step 2: Enable log_archive_dest_state_1 Parameter.
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
ALTER SYSTEM SWITCH LOGFILE hangs:
Step 1: Login as sysdba.
Step 2: Enable log_archive_dest_state_1 Parameter.
SQL>alter system set log_archive_dest_state_1=enable scope=spfile;
System altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
CreateFile error 32 when trying set file time oracle 11g windows 7
Today I am going to install Oracle 10g Client and ODAC11gR2 (Oracle Data Provider .Net) in Microsoft Windows XP Professional Service Pack 2. I have got following error message in console.
CreateFile error 32 when trying set file time
After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message
CreateFile error 32 when trying set file time
After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message
ORA-00333 redo log read error block
PFILE
_ALLOW_RESETLOGS_CORRUPTION = true
UNDO_MANAGEMENT = MANUAL
RENAME SPFILE
recover database until cancel;
or
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
alter database open resetlogs;
--------------------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'
startup restrict pfile=<initsid.ora>
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\rkdatabase\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
Dynamic change of CPU_COUNT causes ORA-600 [KsrpubwaitDeadlock] ORA-2097 Instance crash
Dynamic change of CPU_COUNT causes ORA-600 [Ksrpubwait:Deadlock] ORA-2097 Instance crash
Problem Description
Dynamic change of CPU_COUNT parameter causes oracle bug ORA-600 [ksrpubwait:deadlock]. For example, after issuing command "alter system set cpu_count=5;" it fails with following errors.
ORA-600 [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel]
ORA-2097: parameter cannot be modified because specified value is invalid
And then instance crashes.
If you look at stack trace you will see,
ksrpubwait ksrpublish kspbcast kspset0 kxfpCPUCountUpdate ksbcpcb
Cause of the Problem
This errors are caused by Oracle Bug 7535429
Solution of the Problem
Solution 01: Bug 7535429 is fixed in version 11.2. So upgrade your oracle database to 11gR2 will solve the problem.
There is one-off patch which also help you to solve the problem. Check for the availability of one-off Patch 7535429 for your platform on MetaLink.
Solution 02: Explicitely setting CPU_COUNT to new value.
Solution 03: If you start the instance from pfile instead of using spfile then it will also solve the problem.
Solution 04: Set explicitly PARALLEL_MAX_SERVERS to the current value instead of relying default value.
Problem Description
Dynamic change of CPU_COUNT parameter causes oracle bug ORA-600 [ksrpubwait:deadlock]. For example, after issuing command "alter system set cpu_count=5;" it fails with following errors.
ORA-600 [ksrpubwait:deadlock], [parameters to cluster db instances - broadcast channel]
ORA-2097: parameter cannot be modified because specified value is invalid
And then instance crashes.
If you look at stack trace you will see,
ksrpubwait ksrpublish kspbcast kspset0 kxfpCPUCountUpdate ksbcpcb
Cause of the Problem
This errors are caused by Oracle Bug 7535429
Solution of the Problem
Solution 01: Bug 7535429 is fixed in version 11.2. So upgrade your oracle database to 11gR2 will solve the problem.
There is one-off patch which also help you to solve the problem. Check for the availability of one-off Patch 7535429 for your platform on MetaLink.
Solution 02: Explicitely setting CPU_COUNT to new value.
Solution 03: If you start the instance from pfile instead of using spfile then it will also solve the problem.
Solution 04: Set explicitly PARALLEL_MAX_SERVERS to the current value instead of relying default value.
ORA-600 [6006] ORA-600 [6856]
While startup, instance terminated after ORA-600 [6006] ORA-600 [6856]
Problem Description
While starting up the instance it fails with following errors:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []
and then SMON abnormally crashes the database instance.
Cause of the Problem
These are oracle bugs and these ORA-600 [6006] and ORA-600 [6856] bug occurred whenever undo segments are trying to rollback a failed transaction and cannot.
For error "ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []" ,
Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
For error "ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []" , SMON is trying to recover a dead transaction. But the undo application runs into an internal error (trying to delete a row that is already deleted).
Solution of the Problem
1) Review the trace files and look for the object(s) involved. If the trace file does not have a SQL statement, search on the following: "block dump header"
2) In the block header there will be a seg/obj = hex value. Convert the hex to dec and this will give you the data_object_id.
3) The alert.log may also show the affected object, for example:
ORACLE Instance ORCL (pid = 8) - Error 600 encountered while recovering transaction (9999, 36) on object 45879.
SQL>select owner, object_name from dba_objects where data_object_id = object#;
This will be the object you need to work with.
To implement the solution:
1. shutdown the oracle instance.
SQL> shut immediate
2. set event - event="10513 trace name context forever, level 2" (this event and setting disables transaction recovery which was initiated by SMON). Open the pfile and add the following line inside pfile.
event="10513 trace name context forever, level 2"
3. startup instance
SQL> startup
4.
- If the object is an index - drop and recreate.
- If the object is a table - drop / export / Create Table as Select (CTAS) to change the object ID
5. shutdown the instance
SQL> shut immediate
6. remove the event
Open the pfile and remove the event line that was added.
7. startup the instance
SQL> startup
Problem Description
While starting up the instance it fails with following errors:
ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []
and then SMON abnormally crashes the database instance.
Cause of the Problem
These are oracle bugs and these ORA-600 [6006] and ORA-600 [6856] bug occurred whenever undo segments are trying to rollback a failed transaction and cannot.
For error "ORA-00600: internal error code, arguments: [6006], [1], [], [], [], [], [], []" ,
Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
For error "ORA-00600: internal error code, arguments: [6856], [0], [60], [], [], []" , SMON is trying to recover a dead transaction. But the undo application runs into an internal error (trying to delete a row that is already deleted).
Solution of the Problem
1) Review the trace files and look for the object(s) involved. If the trace file does not have a SQL statement, search on the following: "block dump header"
2) In the block header there will be a seg/obj = hex value. Convert the hex to dec and this will give you the data_object_id.
3) The alert.log may also show the affected object, for example:
ORACLE Instance ORCL (pid = 8) - Error 600 encountered while recovering transaction (9999, 36) on object 45879.
SQL>select owner, object_name from dba_objects where data_object_id = object#;
This will be the object you need to work with.
To implement the solution:
1. shutdown the oracle instance.
SQL> shut immediate
2. set event - event="10513 trace name context forever, level 2" (this event and setting disables transaction recovery which was initiated by SMON). Open the pfile and add the following line inside pfile.
event="10513 trace name context forever, level 2"
3. startup instance
SQL> startup
4.
- If the object is an index - drop and recreate.
- If the object is a table - drop / export / Create Table as Select (CTAS) to change the object ID
5. shutdown the instance
SQL> shut immediate
6. remove the event
Open the pfile and remove the event line that was added.
7. startup the instance
SQL> startup
ORA-600 [17147] ORA-600 [Kghlkrem1] ORA-7445 kghlkremf() database crash
ORA-600 [17147] ORA-600 [Kghlkrem1] ORA-7445 kghlkremf() database crash
During normal database activity, in alert log the following ORA-00600 error occurred and it causes database crashed.
ORA-00600: internal error code, arguments: [17147], [0xC0000003E4CE10A5], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0xC0000003E4CE10B0], [], [], [], []
ORA-7445: exception encountered: core dump [kghlkremf()+33] [SIGSEGV]
If you look at the trace file you no longer will see any sql that causes this bug.
The call stack is as follows:
kghfrmrg kghfre qsmqktcc ktcdso ktcrcm ktdcmt k2lcom k2send xctctl xctcom_with_options opicom
or
k2send xctctl xctcom_with_options opicom opiodr
or
kghalo ktcccadd kwqidracbk kwqidcpmc kwqidafm0 kwqididqx kpoaqdq
or
kghadd_reserved_extent kghget_reserved_extent kghgex kghfnd
Cause of the Problem
The problem occurred due to oracle bug. The bug involves abnormal termination of queries involving bloom filters. This manifests itself as memory corruptions. The memory was freed, reallocated to some other client, and corrupted but slaves were still writing into it.
Solution of the Problem
Solution 01:
This bug is fixed in oracle database version 10.2.0.4. So apply 10.2.0.4 patchset to solve this problem.
Solution 02:
Workaround, you can set the oracle hidden parameter _bloom_filter_enabled to false.
This parameter can be set dynamically:
If you are using spfile then just set as,
SQL> connect / as sysdba
SQL> alter system set "_bloom_filter_enabled"=false scope=both;
If you use pfile to startup your database remove the scope parameter that is use,
SQL> alter system set "_bloom_filter_enabled"=false;
During normal database activity, in alert log the following ORA-00600 error occurred and it causes database crashed.
ORA-00600: internal error code, arguments: [17147], [0xC0000003E4CE10A5], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [KGHLKREM1], [0xC0000003E4CE10B0], [], [], [], []
ORA-7445: exception encountered: core dump [kghlkremf()+33] [SIGSEGV]
If you look at the trace file you no longer will see any sql that causes this bug.
The call stack is as follows:
kghfrmrg kghfre qsmqktcc ktcdso ktcrcm ktdcmt k2lcom k2send xctctl xctcom_with_options opicom
or
k2send xctctl xctcom_with_options opicom opiodr
or
kghalo ktcccadd kwqidracbk kwqidcpmc kwqidafm0 kwqididqx kpoaqdq
or
kghadd_reserved_extent kghget_reserved_extent kghgex kghfnd
Cause of the Problem
The problem occurred due to oracle bug. The bug involves abnormal termination of queries involving bloom filters. This manifests itself as memory corruptions. The memory was freed, reallocated to some other client, and corrupted but slaves were still writing into it.
Solution of the Problem
Solution 01:
This bug is fixed in oracle database version 10.2.0.4. So apply 10.2.0.4 patchset to solve this problem.
Solution 02:
Workaround, you can set the oracle hidden parameter _bloom_filter_enabled to false.
This parameter can be set dynamically:
If you are using spfile then just set as,
SQL> connect / as sysdba
SQL> alter system set "_bloom_filter_enabled"=false scope=both;
If you use pfile to startup your database remove the scope parameter that is use,
SQL> alter system set "_bloom_filter_enabled"=false;
ORA-00600 [730] [SPACE LEAK] OR ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] during shutdown
ORA-00600 [730] [SPACE LEAK] OR ORA-600 [LIBRARYCACHENOTEMPTYONCLOSE] during shutdown
Problem Description
While shutting down oracle database the following error occurred.
In oracle 10g and 11g,
ORA-00600: internal error code, arguments: [730], [4100736], [space leak], [], [], [], [], [], [], [], [], []
If you observe stack trace it will look like,
ksesic2 <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- main <- start
In oracle 10g the error looks like,
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [] ,[], [], [], [], []
If you observe stack trace it will look like, kglshu <- kqlnfy <- kscnfy <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
Cause of the Problem
The error occurred due to oracle bug. Oracle named this bug as BUG 7572335. This happens during database shutdown when child cursors had been previously marked as kept in the shared pool. Note that this is a non-corruptive error.
Solution of the Problem
Solution 01: This bug is solved in Oracle database 11gR2. So upgrade to Oracle database 11.2 or higher is one solution.
Solution 02: If you are using Oracle database 10gR2 then up to patchset version 10.2.0.4 this bug remained and it is solved in version 10.2.0.5. You can upgrade to oracle version 10.2.0.5 by applying the Patchset, Patch 8202632.
Solution 03: If available for your platform and version, you can apply one off patch 7572335. Download and apply the fix, Patch 7572335
Problem Description
While shutting down oracle database the following error occurred.
In oracle 10g and 11g,
ORA-00600: internal error code, arguments: [730], [4100736], [space leak], [], [], [], [], [], [], [], [], []
If you observe stack trace it will look like,
ksesic2 <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- main <- start
In oracle 10g the error looks like,
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [] ,[], [], [], [], []
If you observe stack trace it will look like, kglshu <- kqlnfy <- kscnfy <- ksmshu <- opistp_real <- opistp <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
Cause of the Problem
The error occurred due to oracle bug. Oracle named this bug as BUG 7572335. This happens during database shutdown when child cursors had been previously marked as kept in the shared pool. Note that this is a non-corruptive error.
Solution of the Problem
Solution 01: This bug is solved in Oracle database 11gR2. So upgrade to Oracle database 11.2 or higher is one solution.
Solution 02: If you are using Oracle database 10gR2 then up to patchset version 10.2.0.4 this bug remained and it is solved in version 10.2.0.5. You can upgrade to oracle version 10.2.0.5 by applying the Patchset, Patch 8202632.
Solution 03: If available for your platform and version, you can apply one off patch 7572335. Download and apply the fix, Patch 7572335
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;
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-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
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;
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;
LD file import- in temp file is generating in GBs
there is very much records in tables.
All records are coming.
plz delete the rows and reimport
All records are coming.
plz delete the rows and reimport
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$;
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;
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 .
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 .
Subscribe to:
Posts (Atom)