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
