ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to check database recovery status ORA-1195

How to check database recovery status ORA-1195

原创 Linux操作系统 作者:licheng79 时间:2012-01-20 12:36:50 0 删除 编辑

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

http://web.njit.edu/info/oracle/DOC/backup.102/b14191/osbackup007.htm#BRADV204

http://docs.oracle.com/cd/B28359_01/server.111/b28310/start005.htm

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-715138/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    70177