Code Example reprocess_one_file_main.bat: batch calling sqlplus, perl, bash and scp.

===============================
General
===============================
There is a following flow:
main batch is calling several other batch files.
One of the batch files is calling perl program
One of the batch files is calling sqlplus 

===============================
List of files
===============================

reprocess_one_file_main.bat - This is the main bat, calling all others.

convert_one_file.bat
send_scp_mach_one.bat => send_scp_mach.sh
send_file_list.bat               => send_file_list.sh
append_to_file_list.bat    => append_to_file_list.pl
run_sqlplus.bat                  => update_status_in_db.sql

----------------------------------------------
reprocess_one_file_main.bat
----------------------------------------------
ECHO OFF
ECHO.

SET HOME_DIR=D:\ROOT_PATH
SET HOME_DIR_LINUX=d:/ROOT_PATH
REM =======================================================
REM Populate these parameters before running the script!!!
SET FILE_NAME=some_file
SET SEND_FILE_NAME_LIST=%HOME_DIR_LINUX%/sent_file_list/file_list.test
REM =======================================================

ECHO "====================================="
ECHO "Start reprocess_one_file_main.bat"
ECHO "====================================="
ECHO.

ECHO CALL convert_one_file.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME%
CALL convert_one_file.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME%

ECHO CALL send_scp_mach_one.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME%
CALL send_scp_mach_one.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME%

ECHO %HOME_DIR%\append_to_file_list.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME% %SEND_FILE_NAME_LIST%
CALL %HOME_DIR%\append_to_file_list.bat %HOME_DIR% %HOME_DIR_LINUX% %FILE_NAME% %SEND_FILE_NAME_LIST%

ECHO %HOME_DIR%\send_file_list.bat
CALL %HOME_DIR%\send_file_list.bat

ECHO %HOME_DIR%\run_sqlplus.bat %FILE_NAME%
CALL %HOME_DIR%\run_sqlplus.bat %FILE_NAME%

ECHO.
ECHO "====================================="
ECHO "Finished reprocess_one_file_main.bat"
ECHO "====================================="
ECHO.

----------------------------------------------
convert_one_file.bat
----------------------------------------------
SET HOME_DIR=%1
SET HOME_DIR_LINUX=%2
SET FILE_NAME=%3

copy %HOME_DIR%\taptext_tap310_converter\processed\%FILE_NAME%.success %HOME_DIR%\taptext_tap310_converter\input\%FILE_NAME%

%HOME_DIR%\taptext_tap310_converter\run_taptext_tap310_converter_local.bat 

----------------------------------------------
send_scp_mach_one.bat
----------------------------------------------
ECHO OFF

SET HOME_DIR=%1
SET HOME_DIR_LINUX=%2
SET FILE_NAME=%3

ECHO .
ECHO "====================================="
ECHO "Start send_scp_mach_one.bat"
ECHO "====================================="
ECHO .

ECHO HOME_DIR=%HOME_DIR%
ECHO HOME_DIR_LINUX=%HOME_DIR_LINUX%

ECHO FILE_NAME=%FILE_NAME%

ECHO send_scp_mach.sh %FILE_NAME%

d:\software\cygwin\bin\bash.exe --login %HOME_DIR_LINUX%/send_scp_mach.sh %FILE_NAME%

ECHO Done

ECHO .

----------------------------------------------
send_scp_mach.sh
----------------------------------------------
scp -Bq /cygdrive/d/ROOT_PATH/taptext_tap310_converter/output/$1 remote_user@IP:/$1

----------------------------------------------
send_file_list.bat
----------------------------------------------
d:\software\cygwin\bin\bash.exe --login /cygdrive/d/MobilkomAutTaps/send_file_list.sh

----------------------------------------------
send_file_list.sh
----------------------------------------------
scp -Bq /cygdrive/d/ROOT_PATH/sent_file_list/file_list.txt remote_user@IP:/REMOTE/PATH/file_list.txt 

----------------------------------------------
append_to_file_list.bat
----------------------------------------------
ECHO OFF

SET HOME_DIR=%1
SET HOME_DIR_LINUX=%2
SET FILE_NAME=%3
SET SEND_FILE_NAME_LIST=%4

ECHO .
ECHO "====================================="
ECHO "Start append_to_file_list.bat"
ECHO "====================================="
ECHO .

ECHO HOME_DIR=%HOME_DIR%
ECHO HOME_DIR_LINUX=%HOME_DIR_LINUX%
ECHO FILE_NAME=%FILE_NAME%
ECHO SEND_FILE_NAME_LIST=%SEND_FILE_NAME_LIST%


perl append_to_file_list.pl %FILE_NAME% %SEND_FILE_NAME_LIST%

----------------------------------------------
append_to_file_list.pl
----------------------------------------------
#! /usr/bin/perl
use File::Copy;
use strict;
use warnings;

#================================
# Input Parameters Start
#================================
my $tap_file_name = shift;
my $SentFileList = shift;

#================================
# main()
#================================
open(textFile,">>".$SentFileList);
print textFile $tap_file_name.";\n";

close(textFile);

----------------------------------------------
run_sqlplus.bat
----------------------------------------------
ECHO OFF

SET FILE_NAME=%1

ECHO .
ECHO "====================================="
ECHO "Start run_sqlplus.bat"
ECHO "====================================="
ECHO .


sqlplus db_user/db_pass@db_inst@update_status_in_db.sql %FILE_NAME%

----------------------------------------------
update_status_in_db.sql
----------------------------------------------
UPDATE SOME_TABLE SET status = 3 WHERE file_name = '&1' AND status <> 3;

COMMIT; 

Tablespace Fast Reference

====================
Get Tablespace Usage
====================

SET LINESIZE 120
SET PAGESIZE 200
SPOOL tablespace.txt

SELECT TABLESPACE_NAME, 
 (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB, 
 MAX(USED_SPACE) AS USED_SPACE_MB, 
 MAX(MAX_SPACE) AS MAX_SPACE_MB, 
 ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
 CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_IND
FROM ( 
SELECT tablespace_name,  
  ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
  0 AS MAX_SPACE, 
  0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
  0 AS FREE_SPACE, 
  ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;
spool off


TABLESPACE_NAME     DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_IND
------------------- ----------------- ------------- ------------ ---------- -------
DWH_INDEX                      179149         96851       276000         65 N
IGT_TABLE                      144946          5054       150000         97 N
SYSTEM                           1691           309         2000         85 N
IGT_INDEX                       89590           410        90000        100 N
DWH_TABLE                         519        101481       102000          1 Y


====================
Get Segments Usage
====================

SET LINESIZE 120
SET PAGESIZE 200
COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 31;

Example of output:

OWNER                          TABLESPACE_NAME SEGMENT_NAME              USED_MB
------------------------------ --------------- ------------------------- ----------
RUS_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS          71428
RUS_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO           12890
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS           9872
ARM_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_CAMPAIGNS           2764
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_PROFILE            2624
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO            1880
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_PROFILE_PK         1849
ARM_MTSQQ_SPARX_REPORTS        DWH_TABLE       FACT_ROAMER_SCENARIO             792
UKR_MTSQQ_SPARX_REPORTS        DWH_TABLE       SFI_CUSTOMER_OPTIONS             768
RUS_MTSQQ_SPARX_REPORTS        IGT_TABLE       SFI_CUSTOMER_OPTION_20131010     363

10 rows selected.

====================
Get Datafiles Usage
====================

SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999


SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_name;  


TABLESPACE_NAME   FILE_NAME                                           MB     MAX_MB
----------------- -------------------------------------------- --------- ----------
DWH_INDEX         /oracle_db/db1/db_igt/ora_dwh_index_01.dbf         100       6000
DWH_TABLE         /oracle_db/db1/db_igt/ora_dwh_table_01.dbf         200      12000
GIN               /oracle_db/db1/db_igt/ora_gin_01.dbf               500       6000
GININDEX          /oracle_db/db1/db_igt/ora_ginindex_01.dbf          200       3000
IGT_INDEX         /oracle_db/db1/db_igt/ora_igt_index_01.dbf        6400       8000
IGT_TABLE         /oracle_db/db1/db_igt/ora_igt_table_01.dbf       14000      14000
SYSAUX            /oracle_db/db1/db_igt/ora_sysaux_01.dbf            950       1000
SYSTEM            /oracle_db/db1/db_igt/ora_system_01.dbf           1000       1000
UNDOTBS           /oracle_db/db1/db_igt/ora_undotbs_01.dbf          1600       2000
WORKAREA          /oracle_db/db1/db_igt/ora_workarea_01.dbf         1000       1000

====================
Get Datafiles Usage
====================
Resize Datafile
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_system_01.dbf' AUTOEXTEND ON MAXSIZE 2000M;

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_system_01.dbf' RESIZE 2000M;

Add Datafile
ALTER TABLESPACE TABLE_IGT ADD DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

Add Tablespace

ADD TABLESPACE TABLE_IGT_02 DATAFILE '/oracle_db/db1/db_igt/ora_table_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 10000M;

vi editor

vi Reference
vi Short reference.pdf

vi Editing  commands

vi command	description
i	insert at the current position
I	insert at the beginning of line
a	append just after the current cursor position
A	append at the end of line
o	Open a new line below the current line
O	Open a new line above the current line

vi Navigation commands

vi command	description
0	move to beginning of the current line
$	move to end of line
H	move to the top of the current window (high)
M	move to the middle of the current window (middle)
L	move to the bottom line of the current window (low)
1G	move to the first line of the file
20G	move to the 20th line of the file
G	move to the last line of the file

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;