Script to Delete Archivelog Files

#!/bin/sh

# Setting Oracle SID
ORACLE_SID=SID
export ORACLE_SID

#Setting Oracle Home

ORACLE_HOME=Oracle_Home
export ORACLE_HOME

# To Print Start Time
startTime=`date`
print
print "Starting $startTime"

# To delete archivelog files older than 3 days.

rman target/ << EOF
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate-3';
crosscheck archivelog all;
exit;
EOF

# To Print End Time
endTime=`date`
print "Complete $endTime"

ORA-01113: file <> needs media recovery

Error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 14 needs media recovery
ORA-01110: data file 14: '/oradata/xx01.dbf'

Solution:

To Recover the Database:

SQL> recover database;
Media recovery complete.

To Open the Database:

SQL> alter database open;

Database altered.

Query to find Tabelspace Usage

SELECT d.status , d.tablespace_name as Name, d.CONTENTS as Type,
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024,
'99999999.999'
)
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),
'990.00'
) as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
UNION ALL
SELECT d.status , d.tablespace_name as Name, d.CONTENTS as Type,
d.extent_management as ExtentManagement,
TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99,999,990.900') as Siz,
TO_CHAR (NVL (t.BYTES, 0) / 1024 / 1024, '99999999.999')
|| '/'
|| TO_CHAR (NVL (a.BYTES / 1024 / 1024, 0), '99999999.999') as Used,
TO_CHAR (NVL (t.BYTES / a.BYTES * 100, 0), '990.00') as "Used (%)"
FROM dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY';

Query to find Schema size

SELECT s.owner,SUM (s.BYTES) / (1024 * 1024) SIZE_IN_MB
FROM dba_segments s
GROUP BY s.owner;

ORA-00214: Controlfile Inconsistent Error

Error Description:

During database Startup noticed the below Error in alert log file:
ORA-00214: control file 'ORACLE_HOME\CONTROL02.CTL' version 3016
inconsistent with file 'ORACLE_HOME\CONTROL01.CTL' version 2993

Resolution:

Step 1: Shutdown the database abruptly (SQL>shutdown abort)
Step 2: Create the pfile using spfile (SQL>create pfile='ORACLE_HOME\pfile.ora' using spfile)
Step 3: Edit the ORACLE_HOME\pfile.ora file
Step 4: Set CONTROL_FILES Parameter in pfile.ora with only one copy of control file. If more than one exists, delete it.
(CONTROL_FILES='ORACLE_HOME\CONTROL02.CTL')
Step 5: Start the database using pfile (SQL> startup using pfile='ORACLE_HOME\pfile.ora')
Step 6: If you find any below error, execute step 3, 4 and 5 again with another control file location (CONTROL_FILES='ORACLE_HOME\CONTROL01.CTL')

01122, 00000, "database file %s failed verification check"
// *Cause: The information in this file is inconsistent with information
// from the control file. See accompanying message for reason.
// *Action: Make certain that the db files and control files are the correct
// files for this database.

01207, 00000, "file is more recent than control file - old control file"
// *Cause: The control file change sequence number in the data file is
// greater than the number in the control file. This implies that
// the wrong control file is being used. Note that repeatedly causing
// this error can make it stop happening without correcting the real
// problem. Every attempt to open the database will advance the
// control file change sequence number until it is great enough.
// *Action: Use the current control file or do backup control file recovery to
// make the control file current. Be sure to follow all restrictions
// on doing a backup control file recovery.

Step 7: If you find any below error, Recover the database (SQL>recover database)

01113, 00000, "file %s needs media recovery"
// *Cause: An attempt was made to online or open a database with a file that
// is in need of media recovery.
// *Action: First apply media recovery to the file.

Step 8: Open the database (SQL>alter database open)
Step 9: Shutdown the database (SQL>shutdown)
Step 10: Copy the good control file which you used to startup the database onto all other copies, whatever listed in the CONTROL_FILES Paramater in spfile.ora before editing (Step 2).
Step 11: Start the database (SQL>Startup).

ORA-00600: internal error code

Error:

ORA-00600: internal error code, arguments: [kcratr1_lostwrt], [], [],[], [],[], [], []

Description:

This erorror occurs during database startup.

Resolution:

Step 1: Start the database in Mount Stage (SQL>startup mount)
Step 2: Recover the database (SQL>recover database)
Step 3: Open the database (SQL> alter database open)