USE master;
ALTER DATABASE Prod_Clone SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE Prod_Clone
go
--DBCC CHECKTABLE ('AIFDOCUMENTLOG', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
--DBCC CHECKTABLE ('AIFDOCUMENTLOG') WITH NO_INFOMSGS,ESTIMATEONLY ;
DBCC CHECKDB ('Prod_Clone') WITH NO_INFOMSGS;
GO
--ALTER DATABASE Prod_Clone SET MULTI_USER;
--GO
If you get the following DBCC CHECKDB errors, then the problem could simply be a small TEMPDB. I've BOLDEN the really important clue at the end of the error stack.
This was really annoying!!!!!
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data). The off-row data node at page (1:35728379), slot 0, text ID 1055064064 is not referenced.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:35797497), whose header indicates that it is allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data), is allocated by another object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:34608553) allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data). The off-row data node at page (1:33939922), slot 0, text ID 944701440 is referenced by page (1:36219352), slot 4, but was not seen in the scan.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:34616630) allocated to object ID 630097831, index ID 1, partition ID 72057653728051200, alloc unit ID 72057594343129088 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 3366 consistency errors in table 'XX' (object ID 630097831).
CHECKDB found 0 allocation errors and 3366 consistency errors in database 'DB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB).
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command = ('DBCC TABLE CHECK')
To remove or re-size any files belonging to TEMPDB:
net stop MSSQLSERVER
NET START MSSQLSERVER /f /mSQLCMD
PS C:\tmp> sqlcmd -S SERVER -E
use TEMPDB
go
DBCC SHRINKFILE (temp5, EMPTYFILE);
USE master
GO
ALTER DATABASE tempdb REMOVE FILE temp5;
go
USE Prod_Clone
GO
ALTER DATABASE Prod_Clone SET SINGLE_USER
GO
DBCC CHECKDB('Prod_Clone', REPAIR_REBUILD)
GO
ALTER DATABASE Prod_Clone SET MULTI_USER
GO
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Temporary tablespace space allocation fails in RAC even when there is still free temp space.
ORA-12801: error signaled in parallel query server P017
ORA-01652: unable to extend temp segment by 640 in tablespace XY_TEMP
CAUSE
Unbalanced temp space distribution in RAC. One instance seems to consume and cache most of the temp space, causing another instance to hit the ora-1652.
SQL> select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"
2 from gv$sort_segment
3 where tablespace_name='XY_TEMP'
4 order by 1;
INST_ID TABLESPACE_NAME Space(GB)
---------- ------------------------------ ----------
1 XY_TEMP 33.39
2 XY_TEMP 33.77
3 XY_TEMP 34.16
4 XY_TEMP 33.39
5 XY_TEMP 33.46
6 XY_TEMP 1118.79 <<<<very unbalanced
7 XY_TEMP 33.28
8 XY_TEMP 34.26
This is reported in Bug 14383007 - sort runs out of temp space on 2 nodes even when temp space is available
This bug will be fixed in 11.2.0.4 (future release). Refer < Document 14383007.8> for more details.
Useful queries for debugging:
Collect the information every few seconds:
1. select * from gv$sort_segment
2. select sum(bytes), owner from gv$temp_extent_map group by owner;
3. select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;
SOLUTION
Workaround is:
Retry the operation.
One-off patch 14383007 has been provided for certain platform, please check My Oracle Support for patch detail.
REFERENCES
NOTE:14383007.8 - Bug 14383007 - Sort runs out of temp space in RAC even when temp space is available
