Correct Corrupted Undo Tablespace Segment

=====================
Issue
=====================
Due to a power failure Oracle server was abruptly shutdown.
After Linux server was started, the database could not start.
It seems that data block(s) in UNTO Tables are now corrupt.
The solution to fix this issue was of several steps.

=====================
First Error:
=====================
ORA-00600 kcratr1_lastbwr

This error was fixed following Oracle Metalink: 
ORA-600 [kcratr1_lastbwr] (Doc ID 1200478.1)

After a disk failure that caused the database to crash, the instance fails to start up with ORA-00600: arguments: [kcratr1_lastbwr].

The alert log file shows the following entries :
Completed: ALTER DATABASE MOUNT
Tue Sep 19 09:43:03 2006
ALTER DATABASE OPEN
Block change tracking file is current.
Tue Sep 19 09:43:04 2006
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Tue Sep 19 09:43:04 2006
Started redo scan
Tue Sep 19 09:43:05 2006
Errors in file gns80_ora_9936.trc:
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],[], [], []
Tue Sep 19 09:43:06 2006
Aborting crash recovery due to error 600

CHANGES
There was a disk problem that caused the database to crash.

CAUSE
Oracle is unable to perform instance recover but it works when is invoked manually.

SOLUTION
Mount the database and issue a recover statement

STARTUP MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN;

=====================
Second Error:
=====================
ORA-00600: internal error code, arguments: [4194], [46], [8], [], [], [], [], [], 

This error is addressed in Oracle Metalink: 
Step by step to resolve ORA-600 4194 4193 4197 on database crash (Doc ID 1428786.1)

These are the errors I have faced
Tue Feb 28 12:59:56 2017
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_mmon_6581.trc  (incident=156131):
ORA-00353: log corruption near block 65536 change 6002234962 time 02/28/2017 02:03:40
ORA-00334: archived log: '/oracle_db/db2/db_igt/arch/arch0001_188396_669127092.arc'
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_mmon_6581.trc:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 65536 change 6002234962 time 02/28/2017 02:03:40
ORA-00334: archived log: '/oracle_db/db2/db_igt/arch/arch0001_188396_669127092.arc'
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_mmon_6581.trc  (incident=156132):
ORA-00600: internal error code, arguments: [4194], [46], [8], [], [], [], [], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_156132/igt_mmon_6581_i156132.trc
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_28127.trc  (incident=129763):
ORA-00600: internal error code, arguments: [4194], [46], [8], [], [], [], [], [], [], [], [], []
Incident details in: /software/oracle/diag/rdbms/igt/igt/incident/incdir_129763/igt_ora_28127_i129763.trc

This is Oracle Solution

The following error is occurring in the alert.log right before the database crashes.

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block

Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then an ORA-600 [4194] will be triggered.

CHANGES

This issue generally occurs when there is a power outage or hardware failure that initially crashes the database. On startup, the database does the normal roll forward (redo) and then rollback (undo), this is where the error is generated on the rollback.

CAUSE

This also can be cause by the following defect

Bug 8240762 Abstract: Undo corruptions with ORA-600 [4193]/ORA-600 [4194] or ORA-600 [4137] after SHRINK

Details: 

Undo corruption may be caused after a shrink and the same undo block may be used 

for two different transactions causing several internal errors like:

ORA-600 [4193] / ORA-600 [4194] for new transactions

ORA-600 [4137] for a transaction rollback

SOLUTION

Best practice to create a new undo tablespace.

This method includes segment check.

Create pfile from spfile to edit

>create pfile from spfile;

1. Shutdown the instance

2. set the following parameters in the pfile

    undo_management = manual

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

3. >startup restrict pfile=<initsid.ora>

4. >select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR.  There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.

If all offline then continue to the next step

5. Create new undo tablespace - example

>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;

6. Drop old undo tablespace

>drop tablespace <old undo tablespace> including contents and datafiles;

7. >shutdown immediate;

8 >startup nomount;  --> Using your Original spfile

9 modify the spfile with the new undo tablespace name

  Alter system set undo_tablespace = '<new tablespace created in step 5>' scope=spfile;

10. >shutdown immediate;

11. >startup;  --> Using spfile

The problem is that step 4. "select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';" returned a segment, with status = PARTLY AVAILABLE

TABLESPACE_NAME                STATUS               SEGMENT_NAME

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

SYSTEM                         ONLINE               SYSTEM

UNDOTBS                        PARTLY AVAILABLE     _SYSSMU10_1225024740$

Per Oracle Documentation "This is critical - we are looking for all undo segments to be offline - 

If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support.  There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments."

=====================
Third Error:
=====================

Same error as before, 
ORA-00600: internal error code, arguments: [4194], [46], [8], [], [], [], [], [], 
and _SYSSMU10_1225024740$ segment in UNDO Tablespace being in status PARTLY AVAILABLE.

Following steps are not available from Oracle Metalink, nut were provided by Oracle Support.
This is what worked for me.
These is internal Oracle stuff, and not an official solution. From here, proceed at your own risk

Solution:
The goal of this solution is to clear the faulted segment from UNDO tablespace, and recreate the undo tablespace. 

1. Create pfile
create pfile '/software/oracle/admin/igt/pfile/pfile_20170227.ora' from spfile; 

2. Shutdown the instance 
SHUTDOWN IMMEDIATE;

3. Edit the pfile, set the following parameters in the pfile:
undo_management = manual 
event = '10513 trace name context forever, level 2' 
_corrupted_rollback_segments = ('_SYSSMU10_1225024740$') 

4. Startup using pfile
startup restrict pfile='/software/oracle/admin/igt/pfile/pfile_20170227.ora'

5. Verify segment is now in  'NEEDS RECOVERY' status
SELECT tablespace_name, status, segment_name 
  FROM dba_rollback_segs 
 WHERE status != 'OFFLINE'; 

Now the segment in question should be in 'NEEDS RECOVERY'. 
If so, continue to the next step.

Indeed, now segment _SYSSMU10_1225024740$ status has changed from 'PARTLY AVAILABLE' to 'NEEDS RECOVERY'

TABLESPACE_NAME                STATUS               SEGMENT_NAME

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

SYSTEM                         ONLINE               SYSTEM

UNDOTBS                        NEEDS RECOVERY      _SYSSMU10_1225024740$

6. Create new undo tablespace 
CREATE UNDO TABLESPACE UNDOTBS_02 datafile '/oracle_db/db1/db_igt/ora_undotbs_02.dbf' size 4000M;

7. Drop old undo tablespace 
DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

8. Shutdown Database
SHUTDOWN IMMEDIATE;

9. Startup mount; 
STARTUP MOUNT;

10. Modify the spfile with the new undo tablespace name 
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS_02 SCOPE=SPFILE;

11. Shutdown Database
SHUTDOWN IMMEDIATE;

12. Start Database; 

STARTUP;

Optionally, rollback to the original name and file.

This is useful, if database is running in cluster mode, and you do not want to update all the nodes to the new name.

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db1/db_igt/ora_undotbs_01.dbf' SIZE 4000M;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS SCOPE=BOTH;

DROP TABLESPACE UNDOTBS_02 INCLUDING CONTENTS AND DATAFILES;

ORA-00600 kcratr1_lastbwr

======================
Issue
======================
Oracle is crashing with ORA-00600 kcratr1_lastbwr

======================
Oracle documentation
======================

ORA-00600 kcratr1_lastbwr

After a disk failure that caused the database to crash, the instance fails to start up with ORA-00600: arguments: [kcratr1_lastbwr].

The alert log file shows the following entries :
Completed: ALTER DATABASE MOUNT
Tue Sep 19 09:43:03 2006
ALTER DATABASE OPEN
Block change tracking file is current.
Tue Sep 19 09:43:04 2006
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Tue Sep 19 09:43:04 2006
Started redo scan
Tue Sep 19 09:43:05 2006
Errors in file gns80_ora_9936.trc:
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],[], [], []
Tue Sep 19 09:43:06 2006
Aborting crash recovery due to error 600

CHANGES
There was a disk problem that caused the database to crash.

CAUSE
Oracle is unable to perform instance recover but it works when is invoked manually.

SOLUTION
Mount the database and issue a recover statement

======================
Commands to do
======================
STARTUP MOUNT;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Storage by Example. Mount Manually Oracle Cluster Disk Group

==================================
To mount Oracle Cluster Disk Group Locally:
==================================
df
hastatus -sum
hagrp -freez ora_igt_sg
vxdg import OraDg1
vxdg import OraDg2
vxdg import OraDg3
hastatus -sum
vxvol -g OraDg1 startall
vxvol -g OraDg2 startall
vxvol -g OraDg3 startall
mount -t vxfs /dev/vx/dsk/OraDg1/db1 /oracle_db/db1
mount -t vxfs /dev/vx/dsk/OraDg2/Ora_Exp /backup/ora_exp/
mount -t vxfs /dev/vx/dsk/OraDg2/Ora_Online /backup/ora_online/
mount -t vxfs /dev/vx/dsk/OraDg3/db2 /oracle_db/db2
df -hP
hastatus -sum

Now possible to start oracle manually.
SHUTDOWN ABORT;
STARTUP MOUNT;
SHUTDOWN IMMEDIATE;
STARTUP OPEN;

==================================
To mount Oracle Cluster Disk Group in Cluster:
==================================
umount -f /oracle_db/db1
umount -f /backup/ora_online
umount -f /backup/ora_exp
umount -f /oracle_db/db2

hagrp -unfreez ora_igt_sg
hastatus -sum
df
ps -ef | grep pmon

Commands Summary:

hastatus -sum
Veritas CLI
The hastatus command displays resource, group, and system attribute value changes. 


hagrp -freez ora_igt_sg
hagrp - perform VCS service group operations.
Freeze a service group (disables onlining and offlining and failover).


vxdg import OraDg1
Symantec CLI
Importing disk groups as shared.

Shared disk groups can only be imported on the master node.

vxvol -g OraDg1 startall
startall
Attempts to start all volumes that are disabled. 
By default, all volumes in the default disk group (see vxdg(1M)) are started. 
A different disk group can be specified with the -g option.


mount -t vxfs /dev/vx/dsk/OraDg1/db1 /oracle_db/db1
mount - mount a filesystem
-t vxfs - type VxFS
/dev/vx/dsk/OraDg1/db1 - device
/oracle_db/db1 - directory

vxfs - Stands for VERITAS File System is an extent-based file system. It was originally developed by VERITAS Software. Through an OEM agreement, VxFS is used as the primary filesystem of the HP-UX operating system


umount -f /oracle_db/db1
Unmount FileSystem
-f     Force unmount

hagrp -unfreez ora_igt_sg
Re-enables onlining, offlining, and failover of a service group.

Code example for batch, SQLoader, sqlplus

==========================
General
==========================
The main idea is to get data from a table, from several Databases, and load the data into a single table.

1. main_capacity_loader.bat 
- Set run date in format YYYYMMDD_hh24mmss
- Sets environment variables
- Calls main_extact_capacity.bat

2. main_extact_capacity.bat:- Calls extract_capacity.bat
- Moves generated file to input directory for SQL Loader
- Calls sql_loader_main.bat
- Moves processed file to output directory for SQL Loader

3. extract_capacity.bat
- Reads ini file, with DB connections details
- Per each connections calls run_sql.bat

4. run_sql.bat
- Wrapper to get_capacity_counters.sql

5. get_capacity_counters.sql
- Calls Handles the actual access to remote DB.

6.  sql_loader_main.bat
- Recieved output file from extract_capacity.bat. The fields are comma delimited, and loads, via SQL LOADER into Database.

==========================
main_capacity_loader.bat
==========================
ECHO OFF
setlocal
cls

ECHO.
ECHO =============================================
ECHO main is starting
ECHO =============================================

ECHO.
ECHO =============================================
ECHO set run date
ECHO =============================================

set run_year=%date:~-4%
set run_month=%date:~4,2%
set run_day=%date:~7,2%
set hh=%time:~0,2%
set mm=%time:~3,2%
set ss=%time:~6,2%

IF %run_month% LSS 10 (set run_month=0%run_month:~1,1%)
IF %run_day% LSS 10 (set run_day=0%run_day:~1,1%)
IF %hh% LSS 10 (set hh=0%hh:~1,1%)
IF %mm% LSS 10 (set mm=0%mm:~1,1%)
IF %ss% LSS 10 (set ss=0%ss:~1,1%)

set RUN_DATE=%run_year%%run_month%%run_day%_%hh%%mm%%ss%
ECHO Start Run at %RUN_DATE%

ECHO.
ECHO =============================================
ECHO init parameters
ECHO =============================================

SET HOME_DIR=d:\RDG\Capacity\CAPACILY_LOADER
SET LOG_FILE=%HOME_DIR%\Logs\capacity_load_%RUN_DATE%.log
SET INIFILE=%HOME_DIR%\db_list.ini
SET SPOOL_TO_FILE=%HOME_DIR%\capacity_counters.dat

ECHO LOG_FILE: %LOG_FILE%

ECHO.
ECHO. > %LOG_FILE%
ECHO =============================================
ECHO ============================================= >> %LOG_FILE%
ECHO.
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE% >> %LOG_FILE%
     CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%
ECHO.
ECHO =============================================
ECHO.


ECHO ============================================= >> %LOG_FILE%
ECHO main_capacity_loader.bat has finished >> %LOG_FILE%
ECHO ============================================= >> %LOG_FILE%

REM EXIT

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

main_extact_capacity.bat

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

ECHO OFF

setlocal

cls

ECHO.

ECHO =============================================

ECHO main is starting

ECHO =============================================

ECHO CALL %HOME_DIR%\code\main_extact_capacity.bat %HOME_DIR% %RUN_DATE% %LOG_FILE% %INIFILE% %SPOOL_TO_FILE%

SET HOME_DIR=%1

SET RUN_DATE=%2

SET LOG_FILE=%3

SET INIFILE=%4

SET SPOOL_TO_FILE=%5

ECHO Inside main_extact_capacity.bat >> %LOG_FILE%

ECHO.

ECHO =============================================

ECHO init parameters

ECHO =============================================

SET SQL_LOADER_DIR=%HOME_DIR%\SQL_LOADER

SET SQL_LOADER_DIR_HANDLED=%SQL_LOADER_DIR%\HANDLED

SET SQL_LOADER_MAIN=%SQL_LOADER_DIR%\sql_loader_main.bat

SET SQL_LOADER_FILE=%SQL_LOADER_DIR%\input_capacity.dat

SET SQL_LOADER_FILE_HANDLED=%SQL_LOADER_DIR_HANDLED%\input_capacity_%RUN_DATE%.dat

ECHO.

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO Delete old capacity_counters.dat file

ECHO Delete old capacity_counters.dat file >> %LOG_FILE%

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO DEL %SPOOL_TO_FILE% 

ECHO DEL %SPOOL_TO_FILE%  >> %LOG_FILE%

DEL %SPOOL_TO_FILE%

ECHO.

ECHO. >> %LOG_FILE%

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

ECHO CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE% >> %LOG_FILE%

     CALL  %HOME_DIR%\code\extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

ECHO.

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO.

ECHO ============================================= >> %LOG_FILE%

ECHO Move capacity_counters.dat file to SQL_LOADER >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO =============================================

ECHO Move capacity_counters.dat file to SQL_LOADER

ECHO =============================================

ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE% >> %LOG_FILE%

ECHO MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%

     MOVE /Y %SPOOL_TO_FILE% %SQL_LOADER_FILE%

ECHO.

ECHO ============================================= >> %LOG_FILE%

ECHO Running SQL_LOADER >> %LOG_FILE%

ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO =============================================

ECHO Running SQL_LOADER

ECHO =============================================

ECHO CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% 

     CALL %SQL_LOADER_MAIN% %SQL_LOADER_DIR% 

 

ECHO =============================================

ECHO %SQL_LOADER_MAIN% has Finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO %SQL_LOADER_MAIN% has Finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

 

ECHO.

ECHO =============================================

ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO Move capacity_counters.dat file to SQL_LOADER_HANDLED >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%

ECHO MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED% >> %LOG_FILE%

     MOVE /Y %SQL_LOADER_FILE% %SQL_LOADER_FILE_HANDLED%

 

ECHO.

ECHO =============================================

ECHO main_extact_capacity.bat has finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO main_extact_capacity.bat has finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

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

extract_capacity.bat

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

ECHO OFF

setlocal

cls

ECHO.

ECHO =============================================

REM CALL extract_capacity.bat %HOME_DIR% %SPOOL_TO_FILE% %INIFILE% %LOG_FILE%

SET HOME_DIR=%1

SET SPOOL_TO_FILE=%2

SET INIFILE=%3

SET LOG_FILE=%4

SET SQL_FILE=%HOME_DIR%\code\get_capacity_counters.sql

ECHO ============================================= >> %LOG_FILE%

ECHO Inside extract_capacity.bat >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

ECHO.

ECHO =============================================

ECHO extract_capacity.bat is starting

ECHO This Program read entries from file %inifile%

ECHO =============================================

ECHO.

FOR /F "tokens=*" %%i IN (%inifile%) DO (

ECHO --------------------------------------------------------

ECHO Handling %%i 

 ECHO --------------------------------------------------------

ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO Handling %%i  >> %LOG_FILE%

ECHO -------------------------------------------------------- >> %LOG_FILE%

call :process_one_db %%i %LOG_FILE% %SQL_FILE% %SPOOL_TO_FILE% %HOME_DIR%

)

ECHO.

ECHO =============================================

ECHO extract_capacity has finished

ECHO =============================================

ECHO ============================================= >> %LOG_FILE%

ECHO extract_capacity has finished >> %LOG_FILE%

ECHO ============================================= >> %LOG_FILE%

GOTO :EOF

:process_one_db

  SET CONNECTION_NAME=%1

  SET LOG_FILE=%2

  SET SQL_FILE=%3

  SET SPOOL_TO_FILE=%4

  SET HOME_DIR=%5

  

  REM ECHO CONNECTION_NAME %CONNECTION_NAME%

  SET IS_REMARK=%CONNECTION_NAME:~0,1%

  IF [%IS_REMARK%]==[#] (

    ECHO Skip Processing %CONNECTION_NAME% 

ECHO Skip Processing %CONNECTION_NAME%  >> %LOG_FILE%

  ) ELSE (

    ECHO call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE%  >> %LOG_FILE%

    call %HOME_DIR%\code\run_sql.bat %CONNECTION_NAME% %SQL_FILE% %SPOOL_TO_FILE%    

ECHO --------------------------------------------------------

ECHO Done

        ECHO --------------------------------------------------------

ECHO.

ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO Done >> %LOG_FILE%

        ECHO -------------------------------------------------------- >> %LOG_FILE%

ECHO. >> %LOG_FILE%

  )

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

run_sql.bat

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

SET db_name=%1

SET sql_file_name=%2

SET spool_to_file=%3

REM ECHO db_name: %db_name%

REM ECHO sql_file_name: %sql_file_name%

REM ECHO spool_to_file: %spool_to_file%

REM ECHO sqlplus %db_name% @%sql_file_name% %spool_to_file% 

     sqlplus %db_name% @%sql_file_name% %spool_to_file% 

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

get_capacity_counters.sql

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

SET SHOW OFF 

SET VERIFY OFF 

SET HEAD OFF

SET LINE 500

SET FEEDBACK OFF

SET PAGES 0

SET TRIMS ON

SET TERMOUT OFF  

SET COLSEP ,

SET LINESIZE 140

--SPOOL capacity_counters.dat APPEND;

SPOOL &&1 APPEND;

SELECT TRIM(USER),

       SUBSTR(TO_CHAR(static_id),1,30) AS static_id,

       TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss') AS RUN_DATE,

       ROUND(SUM(CNT_VALUE)/900) AS value   

  FROM SUPPORT_CNT_LOG, DUAL 

 WHERE VALUE_DATE >= ADD_MONTHS(SYSDATE,-6) 

   AND TO_CHAR(static_id) NOT LIKE '9999999%' 

   AND TO_CHAR(static_id) NOT LIKE '1011500104190000' 

   AND CNT_VALUE IS NOT NULL 

GROUP BY TO_CHAR(static_id), TO_CHAR(VALUE_DATE,'YYYYMMDD hh24:mi:ss');

EXIT;

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

db_list.ini

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

# Use # to mark out lines

#-------------------------------------------------------

USER_A/PASS_A@CONN_STR_A

USER_B/PASS_B@CONN_STR_B

USER_C/PASS_C@CONN_STR_C

USER_D/PASS_D@CONN_STR_D

#USER_E/PASS_E@CONN_STR_E

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

sql_loader_main.bat
==========================
ECHO OFF
REM sqlldr collector/ncgwcol@igt control=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_ct_aps1.dat log=${SA_LOAD_HOME}/${SA_LOAD_CST}/loader_aps1_log.log

SET SQL_LOADER_DIR=%1
REM SET SQL_LOADER_DIR=D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER

sqlldr CAPACITY/CAPACITY@CGW_SA control=%SQL_LOADER_DIR%\loader_capacity.ctl log=%SQL_LOADER_DIR%\loader_capacity.log

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

loader_capacity.ctl

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

LOAD DATA

INFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\input_capacity.dat'

BADFILE 'D:\RDG\Capacity\CAPACILY_LOADER\SQL_LOADER\bad_capacity.bad'

discardmax 0

TRUNCATE

into table CAPACITY_COUNTER_DATA

fields terminated by ','

(

 schema_name    "TRIM(:schema_name)",

 counter_id     "TRIM(:counter_id)",

 run_date       "TRIM(:run_date)",

 counter_value  "TRIM(:counter_value)"

)

ORA-00447 and ORA-00321 by Example

=============================
General
=============================
Issue:
For no apparent reason the database is "stuck".
When checking Oracle alert.log, following strange messages appear:

Sat Feb 04 19:01:51 2017
Thread 1 advanced to log sequence 93642 (LGWR switch)
  Current log# 3 seq# 93642 mem# 0: D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_03_A.RDO
Sat Feb 04 19:31:51 2017
Thread 1 advanced to log sequence 93643 (LGWR switch)
  Current log# 1 seq# 93643 mem# 0: D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_01_A.RDO
Sat Feb 04 20:01:50 2017
Sat Feb 04 20:01:50 2017
Sat Feb 04 20:01:50 2017
Sat Feb 04 20:01:50 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017
Sat Feb 04 20:01:51 2017


Checking trace files from same time, following errors are repeated in arch trace files:
For example: igt_arc0_908.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 2
CPU                 : 16 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:9564M/10229M, Ph+PgF:13344M/14102M, VA:1222M/2047M
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 35
Windows thread id: 908, image: ORACLE.EXE (ARC0)


*** 2016-06-11 19:45:08.373
*** SESSION ID:(311.3) 2016-06-11 19:45:08.373
*** CLIENT ID:() 2016-06-11 19:45:08.373
*** SERVICE NAME:() 2016-06-11 19:45:08.373
*** MODULE NAME:() 2016-06-11 19:45:08.373
*** ACTION NAME:() 2016-06-11 19:45:08.373
*** 2017-02-04 19:31:51.098
...
...
...
...
kcrroda: calling ksfdrcres to create AL or RL
*** 2017-02-04 19:31:51.098 3353 kcrr.c
kcrroda: completed call to ksfdrcres
error 321 detected in background process
OPIRIP: Uncaught error 447. Error stack:

*** 2017-02-04 20:01:52.567
ORA-00447: fatal error in background process
ORA-00321: log  of thread , cannot update log file header

How to resolve the issue?

=============================
Resolution
=============================

Restart Oracle service

SHUTDOWN ABORT

STARTUP NORMAL

SHUTDOWN IMMEDIATE

STARTUP NORMAL

=============================
Reason for ORA-00447 and ORA-00321
=============================
Oracle Metalink is pretty vague about it:
"Errors are usually due to usage, application or configuration issues but in some cases they may be caused by a bug issue."

After restart, Oracle has recovered from a crash successfully.

From alert.log:

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = d:\software\oracle

Sat Feb 04 23:33:30 2017

alter database mount exclusive

Sat Feb 04 23:33:30 2017

MMNL started with pid=16, OS id=3692 

Sat Feb 04 23:33:33 2017

Sweep Incident[30074]: completed

Sweep Incident[30073]: completed

Sweep Incident[30072]: completed

Setting recovery target incarnation to 1

Successful mount of redo thread 1, with mount id 1173416138

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount exclusive

alter database open

Beginning crash recovery of 1 threads

 parallel recovery started with 15 processes

Started redo scan

Completed redo scan

 0 redo blocks read, 0 data blocks need recovery

Started redo application at

 Thread 1: logseq 93643, block 3261, scn 3254622468935

Recovery of Online Redo Log: Thread 1 Group 1 Seq 93643 Reading mem 0

  Mem# 0: D:\ORACLE_DB\DB1\DB_IGT\ORA_REDO_01_A.RDO

Completed redo application of 0.00MB

Completed crash recovery at

 Thread 1: logseq 93643, block 3261, scn 3254622488936

 0 data blocks read, 0 data blocks written, 0 redo blocks read

LGWR: STARTING ARCH PROCESSES

In case the restart fails, need to restart with RESETLOGS option

SHUTDOWN ABORT

STARTUP MOUNT

RECOVER DATABASE UNTIL CANCEL

ALTER DATABASE OPEN RESETLOGS