Do you have the full rman log? To restore a cold backup you have to use the noredo.

restore database;
recover database noredo;



1. I need additional information to analyze and troubleshoot your issue.


You can copy and paste into sqlplus the complete script and it will create
results01.txt. I will review it and see what our options are.

Note: Do this as the SYSDBA.

REM # Please run the following SQL statements, as shown below, via SQL*Plus,
REM # and then upload the "results01.txt" for further analysis. You can run
REM # these statements while the database is at least mounted. Cut and paste
REM # the script into sqlplus.
REM ##########################################################################
REM

spool results01.txt
set echo on feedback on
set pagesize 50
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select * from v$version;
select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as current_date from dual;
column name format a10
select dbid, name,
to_char(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode, log_mode,
to_char(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
to_char(controlfile_change#, '999999999999999') as controlfile_change#,
to_char(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time
from v$database;

select * from v$instance;

SELECT to_char(Current_scn, '9999999999999999') FROM V$DATABASE;

select to_char(checkpoint_change#, '999999999999999') as ck_chg from v$database;

select min(fhscn) from x$kcvfh;


Select min(fhafs) "minvalue" ,max(fhafs) "max value" from x$kcvfh;

select max(to_number(fhafs)) "Minimum PITR SCN", min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

archive log list;


select SEQUENCE#, to_char(FIRST_CHANGE#,'999999999999999') as CHK_CHANGE, group#, archived, status,thread#
from v$log order by first_change#;


column member format a45
select group#, status,type,member from v$logfile;

column name format a79
select name, file#, status, enabled, creation_change#,
to_char(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time, to_char(checkpoint_change#, '999999999999999') as checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, to_char(offline_change#, '999999999999999') as offline_change#, to_char(online_change#, '999999999999999') as online_change#, to_char(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time, to_char(bytes, '9,999,999,999,990') as bytes
from v$datafile
where status <> 'ONLINE'
or checkpoint_change# <> (select checkpoint_change# from v$database);


select distinct to_char(checkpoint_change#, '9999999999999999') from v$datafile;

select distinct(status)from v$datafile;
select FILE#,TS# , status, NAME from v$datafile
where status not in ('SYSTEM','ONLINE')
order by 1;

select distinct (to_char(checkpoint_change#,'9999999999999999')) from v$datafile_header;

REM #
REM # fuzzy (fhsta=4 for example).
REM # Status to open the database is fhsta=0 (except system datafile(s) may be something like 8192)
REM # status 4 which means "online fuzzy" this datafile should be restored again.
REM # You can only cancel recovery once all datafiles are at status 0 using this query
REM# Absolutely FuZzy in Status 16 / 64 / 80 and the database will not open
REM# Status 16 - Media recovery fuzzy
REM# Status 64 - Absolute Fuzzy If you see status 64 redo the recover and apply
REM# the next log, keep going until there are no status 64.


REM# Status 80 - Media fuzzy & Absolute.
REM #Summarize the fhsta information from above in case query above returns hundreds of rows.

select distinct FHSTA from X$KCVFH;

REM#You may run the following query to determine the sequence# of the archivelogs needed for the initial recovery.

select distinct FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH
order by FHTHR, FHRBA_SEQ;


select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

REM #
REM#

select to_char(hxfil,'99999999999') as FILE#,fhsta STAT,fhscn SCN,
fhthr thrd, fhrba_Seq SEQ,fhtnm T_SPACE
from x$kcvfh order by 1;


select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;


select df.file#, to_char(df.checkpoint_change#, '9999999999999999') controlfile_change,
to_char(dh.checkpoint_change# , '9999999999999999') datafile_change, df.status
from v$datafile df, v$datafile_header dh
where df.file# = dh.file#;

select a.recid, a.thread#, a.sequence#, a.name, a.archived, a.deleted,
to_date(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
from v$archived_log a, v$log l
where a.thread# = l.thread#
and a.sequence# = l.sequence#;

REM # If the query return 1 row with STATUS='ONLINE', FUZZY='NO', and similar CHECKPOINT_TIMEthen you REM # can proceed to open the database.

set echo on feedback on time on timing on pagesize 100 linesize 80 numwidth 13
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select error, fuzzy, status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by error, fuzzy, status, checkpoint_change#,checkpoint_time
order by checkpoint_change#, checkpoint_time;


select * from v$recovery_log;

select file#, to_char(change#, '9999999999999999') from v$recover_file;

rem# Use the third value: RECOVER DATABASE UNTIL SCN xxxxxxxxxx;

select min(FHSCN) "LOW FILEHDR SCN", max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN"
from X$KCVFH ;


SELECT f.name, f.file#, b.status, to_char(b.change#, '9999999999999999'), b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*)
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;


spool off
REM
REM ##########################################################################
REM # Note:
REM # 1. The "results01.txt" will help me analyze the latest status of
REM # the datafiles and controlfile, particularly their checkpoint change#.



---- Upload results01.txt

---- Upload the alert log

3. Do the following so we will know your RMAN configuration.

To get the right time of the backup you have to make sure NLS date is set.

If using csh

setenv NLS_DATE_FORMAT 'DD-MON-YYYY HH24:MI:SS'

If using ksh use:

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

For Windows:

sql 'alter session set NLS_DATE_FORMAT = "Mon dd yyyy hh24:mi:ss"';
set echo on;


crosscheck backup of database;
crosscheck backup of archivelog all;

spool log to rman_report.log;
set echo on;
show all;

list incarnation of database;

list backup summary;

list backup of database archivelog all;

list backup of controlfile;

list archivelog all;

report schema;

list copy of database;

restore preview database;

-- Upload rman_report.log