How to check database recovery status ORA-1195

最新推荐文章于 2023-10-20 16:58:18 发布

转载最新推荐文章于 2023-10-20 16:58:18 发布 · 158 阅读

本文详细阐述了在使用Oracle9i数据库时遇到ORA-1195错误时的排查步骤及解决方法，包括如何检查数据库状态、确定是否需要进行介质恢复、通过SQL*Plus运行关键查询并上传生成的日志文件，最终实现数据库恢复。

DBA Notes: 2012/01/20

Cheng Li

(Database: Oracle 9i, OS: AIX)

How to check database recovery status ORA-1195

Following is the meaning of the error message

Error: ORA 1195
Text: online backup of file needs more recovery to be consistent
-------------------------------------------------------------------------------
Cause: An incomplete recovery session was started, but an insufficient number
of redo logs were applied to make the file consistent.
The reported file is an online backup that must be recovered to the
time the backup ended.
Action: Either apply more redo logs until the file is consistent or restore
the file from an older backup and repeat the recovery.
For more information about online backup, see the index entry "online
backups" in the ..
So looks like it needs your Current redo log
Please try the following :-

SQL>Select * from v$log;

Find the group which is current

SQL>Select * from v$Logfile ;

FInd the member name associated with the Current group

SQL>Recover database using backup controlfile until cancel ;

Now when prompted for recovery Enter the Member name for the Current Group and hit enter

If it gives you a Message
LOG APPLIED and media recovery complete
Then try the open resetlogs

if it still fails upload
In order to get a complete picture of the database as it relates to restore/recovery, please run the following queries via SQL*Plus as sys or a sysdba user. Upload the generated spool file, query1.txt, via this service request. The instance only need be mounted. You may cut / paste the following output including the comments into SQL*Plus:

-- Start of queries

column first_change# format 9999999999999999
column checkpoint_change# format 9999999999999999
column resetlogs_change# format 9999999999999999
set pagesize 100 linesize 132 echo on

spool recover_status

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

select sysdate from dual;

select file#, status, fuzzy, checkpoint_time, checkpoint_change#,
resetlogs_change#, resetlogs_time from v$datafile_header;

select status, checkpoint_change#,
to_char(checkpoint_time, 'DD-MON-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 * from v$backup;

select * from v$recover_file;

select * from v$log;

select * from v$logfile;

select HXFIL File#, HXFNM, FHTYP Type, HXERR Validity,
FHSCN SCN, FHSTA status, FHRBA_SEQ Sequence from X$KCVFH;

select fhsta, count(*) from x$kcvfh group by fhsta order by fhsta;

select a.FECPC, b.FHCCC, a.FENUM from X$KCCFE a, X$KCVFH b where b.HXFIL= a. FENUM and a.FECPC < b.FHCCC;

select fhrba_seq, count(*) from x$kcvfh group by fhrba_seq order by fhrba_seq;

-- Check the checkpoint_change number of the controlfile for one thing.
-- Often helpful in determining up to what point the recovery must be taken,
-- what time this controlfile was last updated, etc.

select dbid, name, 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-RRRR HH24:MI:SS') controlfile_time,
resetlogs_change#, resetlogs_time, prior_resetlogs_change#, prior_resetlogs_time
from v$database;
spool off
Upload the spool file

Reference:

V$RECOVER_FILE

This view displays the status of files needing media recovery.

Column	Datatype	Description
FILE#	NUMBER	File identifier number
ONLINE	VARCHAR2(7)	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in ONLINE_STATUS.
ONLINE_STATUS	VARCHAR2(7)	Online status (ONLINE, OFFLINE)
ERROR	VARCHAR2(18)	Why the file needs to be recovered: NULL if reason unknown, or OFFLINE NORMAL if recovery not needed
CHANGE#	NUMBER	SCN where recovery must start
TIME	DATE	Time of SCN when recovery must start