#########################################
#
# ORA-01110: data file 201
#
##########################################
-- Problem
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 201: '+DATA01'
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
-- Gather status of files
col name format a76
col MEMBER format a76
select file#,status,name from v$datafile;
select file#,status,name from v$tempfile;
select GROUP# , MEMBER from v$logfile
where member like '%DATA01%';
-- TEMP datafile showed a problem
FILE# STATUS NAME
---------- ------- ----------------------------------------------------------------------------
1 ONLINE +DATA01
-- The example below shoed that the ASM space was exhausted due to old files being resident
SQL> alter tablespace TEMP add tempfile size 10G;
alter tablespace TEMP add tempfile size 10G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA01'
ORA-17502: ksfdcre:4 Failed to create file +DATA01
ORA-15041: diskgroup "DATA01" space exhausted
-- Workaround
create temporary tablespace temp_sed ;
alter database default temporary tablespace temp_sed;
drop tablespace temp;
select file#,status,name from v$tempfile;
create temporary tablespace TEMP;
alter database default temporary tablespace temp;
drop tablespace temp_sed;
select file#,status,name from v$tempfile;
-- Post check
select file#,status,name from v$tempfile;
FILE# STATUS NAME
----- ------- ----------------------------------------------------------------------------
1 ONLINE +DATA01/qt01dda_tss2l592/tempfile/temp.265.852292851
-- Thanks to the following for advice.
http://www.shaiksameer.com/2011/07/ora-01157-cannot-identifylock-data-file.html
##################################
#
# Database sanity checker
#
##################################
for i in `srvctl config | grep ${ORACLE_SID%[12]}`
do
echo -e "\nChecking database status:"
srvctl status database -d ${i} -v
echo -e "\nChecking service status:"
srvctl status service -d ${i} -s ${ORACLE_SID%[12]}_OCI
DIR1=`adrci<<END | grep -E "ADR base" | awk '{print $NF}' | sed 's/\"//g'
END`
DIR2=`adrci<<END | grep ${ORACLE_SID%[12]} | grep ${HOSTNAME%n1} | awk '{print $NF}' | tr '[:upper:]' '[:lower:]'
show alert
Q
END`
DIR2=`echo $DIR2 | sed -r 's/(.*)\/.*/\1/'`
DIR3=${ORACLE_SID}
TRACE_DIR="${DIR1}/${DIR2}/${DIR3}/trace/"
echo -e "\nChecking alert log for errors:"
tail -200 ${DIR1}/${DIR2}/${DIR3}/trace/alert*.log | grep "ORA-"
sqlplus -s /nolog<<END
connect / as sysdba
set heading on pages 100 feedback off
set serveroutput on
prompt
prompt Is the database clustered?
prompt
SET SERVEROUTPUT ON
BEGIN
IF DBMS_UTILITY.is_cluster_database THEN
DBMS_OUTPUT.put_line(chr(09)||' This database is Clustered'||chr(10));
ELSE
DBMS_OUTPUT.put_line(chr(09)||' This database is Not Clustered'||chr(10));
END IF;
END;
/
declare
RN number(9);
SED varchar2(100);
BEGIN
select DEST_NAME||' '||TARGET||' '||DESTINATION||' '||DATAGUARD_BROKER||' '||DATABASE_ROLE into SED from v\$archive_dest a, v\$database b where TARGET(+) != 'PRIMARY' and DEST_NAME = 'LOG_ARCHIVE_DEST_2';
dbms_output.put_line(chr(09)||'Dataguard Status: --> '||SED);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line (chr(09)||' THIS IS NOT A DATAGUARDED DATABASE.'||chr(10));
end;
/
col "Database Status" format a50
SELECT
'HOST:= '||SYS_CONTEXT('USERENV','SERVER_HOST')||chr(10)||
'Dataguard Role:= '||SYS_CONTEXT('USERENV','DATABASE_ROLE')||chr(10)||
'Service:= '||SYS_CONTEXT('USERENV','SERVICE_NAME') "Database Status"
FROM dual
/
col name format a25
col "DB Status" format a50
col OM format a10
select name||decode(substr(name,4,1),'H',' - HDS Database','T',' - PDS Database',null) "DB Status"
, open_mode OM
from v\$database
/
END
echo -e "\n\n"
done
