Shulou(Shulou.com)05/31 Report--

Editor to share with you how to restore data file inconsistencies in the database, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article. Let's go and learn about it!

Restore the data file cannot recover the successful scenario:

1. The database is run in non-archived mode:

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination / u01/archivelog

Oldest online log sequence 1

Current log sequence 2

2. Move one of the data files and offline them as follows:

Col file_name for a50

Select file_name,ONLINE_STATUS from dba_data_files

SQL > col file_name for A50

SQL > select file_name,ONLINE_STATUS from dba_data_files

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/users01.dbf ONLINE

/ u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/ u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/ u01/app/oradata/orclpri/system01.dbf SYSTEM

/ u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/ u01/bpep_caweb.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/ u01/app/oradata/orclpri/readonly01.dbf ONLINE

/ u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/ u01/test.dbf ONLINE

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/test2.dbf ONLINE

/ u01/app/oradata/orclpri/test3.dbf ONLINE

Let's experiment with test data files.

Do a MV operation first:

SQL >! mv / u01/test.dbf / u02 /

Then offline the data file after MV

SQL > alter database datafile'/ u01Compact test.dbf' offline drop

Database altered.

Toggle log files:

SQL > alter system switch logfile

System altered.

Switch log files a few more times.

Shut down the database:

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Start the database to the mount state:

SQL > startup mount

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 566234704 bytes

Database Buffers 276824064 bytes

Redo Buffers 5132288 bytes

Database mounted.

Rename the data files that have passed MV:

SQL > alter database rename file'/ u01bind test.dbf'to'/ u02Universe test.dbf'

Database altered.

Open the database:

SQL > alter database open

View the status of each data file:

SQL > select file_name,ONLINE_STATUS from dba_data_files

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/users01.dbf ONLINE

/ u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/ u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/ u01/app/oradata/orclpri/system01.dbf SYSTEM

/ u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/ u01/bpep_caweb.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/ u01/app/oradata/orclpri/readonly01.dbf ONLINE

/ u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/ u02/test.dbf RECOVER

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/test2.dbf ONLINE

/ u01/app/oradata/orclpri/test3.dbf ONLINE

If you online the data file, the following operations will occur:

SQL > alter database datafile'/ u02Universe test.dbf' online

Alter database datafile'/ u02Universe test.dbf' online

*

ERROR at line 1:

ORA-01113: file 11 needs media recovery

ORA-01110: data file 11:'/ u02max test.dbf'

Try to do the recover data file operation:

SQL > recover datafile'/ u02Compact test.dbf'

ORA-00279: change 2684134 generated at 12/04/2016 21:49:15 needed for thread 1

ORA-00289: suggestion: / u01/archivelog/1_2_929742548.dbf

ORA-00280: change 2684134 for thread 1 is in sequence # 2

Specify log: {= suggested | filename | AUTO | CANCEL}

Auto

ORA-00308: cannot open archived log'/ u01 Universe Archivelog Universe 1'2 '929742548. Dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log'/ u01 Universe Archivelog Universe 1'2 '929742548. Dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Found that the above error was reported when doing the recover operation.

At this time, if you want to online the data file normally, you may need to give up the data consistency and use the _ allow_resetlogs_corruption parameter.

Set the _ allow_resetlogs_corruption parameter to true, and then you can set the data file online

Modify this parameter:

SQL > alter system set "_ allow_resetlogs_corruption" = true scope=spfile

SQL > show parameter allow

NAME TYPE VALUE

-

_ allow_resetlogs_corruption boolean TRUE

Restart the database to the mount state, then enter using RMAN to view the incarnation

RMAN > list incarnation

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

-

11 ORCLPRI 1094561153 PARENT 1 13-AUG-09

2 2 ORCLPRI 1094561153 PARENT 754488 13-JUL-16

3 3 ORCLPRI 1094561153 PARENT 2663124 04-DEC-16

4 4 ORCLPRI 1094561153 PARENT 2683818 04-DEC-16

5 5 ORCLPRI 1094561153 CURRENT 2684130 04-DEC-16

We reset the database to the last restore point:

RMAN > reset database to Incarnation 4

Then restart the library to the mount state:

Online the data file:

Alter database datafile'/ u02Universe test.dbf' online

You cannot open the database directly at this time:

SQL > alter database open

Alter database open

*

ERROR at line 1:

ORA-01190: control file or data file 11 is from before the last RESETLOGS

ORA-01110: data file 11:'/ u02max test.dbf'

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

We can recover database:

Use the following two commands:

SQL > recover database until cancel

SQL > recover database using backup controlfile until cancel

You can then open the database:

SQL > alter database open resetlogs

Database altered.

Check that the data files are all online status:

SQL > select file_name,ONLINE_STATUS from dba_data_files

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/users01.dbf ONLINE

/ u01/app/oradata/orclpri/undotbs01.dbf ONLINE

/ u01/app/oradata/orclpri/sysaux01.dbf ONLINE

/ u01/app/oradata/orclpri/system01.dbf SYSTEM

/ u01/app/oradata/orclpri/datafileep_scs_idx.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_scs.dbf ONLINE

/ u01/bpep_caweb.dbf ONLINE

/ u01/app/oradata/orclpri/bpep_caweb_idx.dbf ONLINE

/ u01/app/oradata/orclpri/readonly01.dbf ONLINE

/ u01/app/oradata/orclpri/readwrite01.dbf ONLINE

/ u02/test.dbf ONLINE

FILE_NAME ONLINE_

/ u01/app/oradata/orclpri/test2.dbf ONLINE

/ u01/app/oradata/orclpri/test3.dbf ONLINE

13 rows selected.

The above is all the contents of the article "how to recover data file inconsistencies in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.