Erman Arslan's Oracle Forum › Database

datafile_problem

Classic

List

Threaded

27 messages Options

Arsalan

datafile_problem

I am using oracle 11g db that is testdb
i last systemo1 datafile then i restored that using rman

restore datafile 1;
recover datafile 1;

but still have below error

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'C:\APP\SIGTAS\ORADATA\DWHSIG\SYSTEM01.DBF'

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

Reference:
10g RMAN Benefits of Simplified Recovery Through Resetlogs. (Doc ID 472536.1)

The RESETLOGS operation creates a new incarnation of the database and resets the logs.
The Simplified Recovery Through Resetlogs (a feature of 10g) is also applicable for performing a recovery using a backup control file and opening the database with the RESETLOGS operation.
You can use the newly generated logs with an earlier incarnation of the database.

So, you should be able to recover that datafile without any problems.

Send me the output of ;

recover datafile 1;

It wanted the archivelogs from your right? Did you apply the archivelogs that recovery wanted from you?
Did you see the following outputs after applying all the necessary archivelogs?

"Log applied.
Media recovery complete."

Arsalan

Re: datafile_problem

RMAN> connect target /

connected to target database: DWHSIG (DBID=1978044831, not open)

RMAN> recover datafile 1;

Starting recover at 07-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=221 device type=DISK

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2017 17:16:43
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
datafile 1
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
-----------------------------------------------------------------------------------------------------

SQL> startup
ORACLE instance started.

Total System Global Area 6847938560 bytes
Fixed Size 2188768 bytes
Variable Size 3120564768 bytes
Database Buffers 3707764736 bytes
Redo Buffers 17420288 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: 'C:\APP\SIGTAS\ORADATA\DWHSIG\SYSTEM01.DBF'

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

Arsala,

Use sqlplus to recover the datafile.

sqlplus "/as sysdba"
SQL>shutdown immediate,
SQL>startup mount;
SQL> recover datafile 1;

Arsalan

Re: datafile_problem

I flowing your instruction so it give error

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6847938560 bytes
Fixed Size 2188768 bytes
Variable Size 3120564768 bytes
Database Buffers 3707764736 bytes
Redo Buffers 17420288 bytes
Database mounted.
SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

Arsalan

Re: datafile_problem

SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

In reply to this post by Arsalan

SQL>startup mount;
SQL>recover database using backup controlfile until cancel;

Arsalan

Re: datafile_problem

SQL> startup mount;
ORACLE instance started.

Total System Global Area 6847938560 bytes
Fixed Size 2188768 bytes
Variable Size 3120564768 bytes
Database Buffers 3707764736 bytes
Redo Buffers 17420288 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

ORA-16433 is unexpected actually.

What is the story behind this restore&recovery? You didn't tell anything?

I suspect your restore scneario is problematic.
I guess you have problems with your controlfile.
But these are only guesses, because I don't have any info about your environment and recovery scenario..

According to note: Manual Completion of a Failed RMAN Backup based Duplicate (Doc ID 360962.1), you can fix this by recreating the controlfile.

If the error "ORA-16433: The database must be opened in read/write mode" is returned, you must recreate the controlfile. Often, when this error occurs, you are unable to issue "alter database backup controlfile to trace" which means you must manually compile the 'create controlfile' statement.

So, recreate the controlfile and retry the recovery.

Arsalan

Re: datafile_problem

In reply to this post by Arsalan

i create control file manual
--------------------

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DWHSIG" RESETLOGS archivelog
MAXLOGFILES 3
MAXLOGMEMBERS 3
MAXDATAFILES 9
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'C:\app\sigtas\oradata\dwhsig\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\app\sigtas\oradata\dwhsig\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\app\sigtas\oradata\dwhsig\REDO03.LOG' SIZE 100M
DATAFILE
'C:\app\sigtas\oradata\dwhsig\SYSTEM01.DBF' ,
'C:\app\sigtas\oradata\dwhsig\USERS01.DBF' ,
'C:\app\sigtas\oradata\dwhsig\EXAMPLE01.DBF' ,
'C:\app\sigtas\oradata\dwhsig\SYSAUX01.DBF' ,
'C:\app\sigtas\oradata\dwhsig\IRD_INDEXES.DBF',
'C:\app\sigtas\oradata\dwhsig\IRD_DATA_LARGE.DBF' ,
'C:\app\sigtas\oradata\dwhsig\IRD_BASE.DBF',
'C:\app\sigtas\oradata\dwhsig\DB_AUDIT_TBS_01.DBF',
'C:\app\sigtas\oradata\dwhsig\\UNDOTBS01.DBF' ;
---------------------------------------------------

SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 23957082 generated at 03/01/2017 06:33:01 needed for thread 1
ORA-00289: suggestion : D:\BACKUP\ARCHIVELOG1\ARC0000001559_0930392801.0001
ORA-00280: change 23957082 for thread 1 is in sequence #1559

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\APP\SIGTAS\ORADATA\DWHSIG\SYSTEM01.DBF'

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

You need to recover a little bit more,

send me the output of following ->

sqlplus "/as sysdba"

SQL>select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

Ref:Unable to open the database in read only mode after restore/recovery (Doc ID 316154.1)

Arsalan

Re: datafile_problem

C:\Users\Arsala>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 8 12:27:59 2017

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;

FILE_NUM FILE_NAME SCN STATUS
---------- ---------------------------------------- ---------------- ----------
SEQUENCE
----------
1 C:\APP\SIGTAS\ORADATA\DWHSIG\SYSTEM01.DB 25029547 8192
1620

2 C:\APP\SIGTAS\ORADATA\DWHSIG\SYSAUX01.DB 23957082 0
1559

3 C:\APP\SIGTAS\ORADATA\DWHSIG\UNDOTBS01.D 23957082 0
1559

FILE_NUM FILE_NAME SCN STATUS
---------- ---------------------------------------- ---------------- ----------
SEQUENCE
----------
4 C:\APP\SIGTAS\ORADATA\DWHSIG\USERS01.DBF 23957082 0
1559

5 C:\APP\SIGTAS\ORADATA\DWHSIG\EXAMPLE01.D 23957082 0
1559

6 C:\APP\SIGTAS\ORADATA\DWHSIG\IRD_DATA_LA 23957082 0
1559

FILE_NUM FILE_NAME SCN STATUS
---------- ---------------------------------------- ---------------- ----------
SEQUENCE
----------
7 C:\APP\SIGTAS\ORADATA\DWHSIG\DB_AUDIT_TB 23957082 0
1559

8 C:\APP\SIGTAS\ORADATA\DWHSIG\IRD_INDEXES 23957082 0
1559

9 C:\APP\SIGTAS\ORADATA\DWHSIG\IRD_BASE.DB 23957082 0
1559

9 rows selected.

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

your system1.dbf (file 1) is expecting the log sequence 1620 , but all the other datafiles are expecting 1559.
why? How did you restore that system01.dbf?

Tell me how did you restored this database and tell me all the thing you done until this moment.

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

Anyways,

you have 2 choices
1)restore system1.dbf from an older backup and do the recovery till the log sequence 1559 (if needed).
2)Don't restore system1.dbf , but do the recovery till you reach the log sequence number 1620. (by applying archivelogs)

Arsalan

Re: datafile_problem

I want to restore system1.dbf till you reach log sequence number 1620.

2)Don't restore system1.dbf , but do the recovery till you reach the log sequence number 1620. (by applying archivelogs)

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

Then apply the archivelogs needed by the recovery.

Arsalan

Re: datafile_problem

RMAN> recover database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2017 13:18:57
ORA-01422: exact fetch returns more than requested number of rows
RMAN-20505: create datafile during recovery
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile 'D:\BACKUP\ARCHIVELOG1\ARC0000001615_0930392801.0001'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: 'C:\APP\SIGTAS\ORADATA\DWHSIG\IRD_DATA_LARGE02.DBF'

Arsalan

Re: datafile_problem

This datafile i don;t needs becuase ignor it.

ORA-01110: data file 11: 'C:\APP\SIGTAS\ORADATA\DWHSIG\IRD_DATA_LARGE02.DBF'

Arsalan

Re: datafile_problem

Should i used this command ?

SQL>recover database until cancel;
SQL>alter database open resetlogs;

I just flowing your instruction .

ErmanArslansOracleBlog

Re: datafile_problem

Administrator

yes.

use bakıp controlfile syntax

8 Mar 2017 12:27 tarihinde "Arsala [via Erman Arslan's Oracle Forum]" <[hidden email]> yazdı:

Should i used this command ?

SQL>recover database until cancel;
SQL>alter database open resetlogs;

I just flowing your instruction .

If you reply to this email, your message will be added to the discussion below:
http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/datafile-problem-tp2282p2307.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML