Restore data files without backup and full archive log

Shulou Source: shulou.com Published: 2022-06-01 16:05:29 09月12日 Update

If all the archive logs exist from the time a data file was created to the current time, you can recover the data file directly by offline datafile,recover datafile,online datafile, which is a media recovery.

Let's do an experiment:

View the initial scn and termination scn of all current archive logs: (status A means available,D has been deleted)

SQL > col name for A100

SQL > select sequence#,name,first_change#,next_change#,status from v$archived_log

SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE# S

5 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_5_cqztt6no_.arc 990220 1001021 A

6 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc 1001021 1006241 A

7 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc 1006241 1006650 A

8 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc 1006650 1009513 A

9/ u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc 1009513 1009663 A

10 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc 1009663 1009860 A

11 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc 1009860 1010001 A

12 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc 1010001 1010136 A

13 / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_13_cqzycyyl_.arc 1010136 1010268 A

9 rows selected.

View the creation SCN of each data file, as well as checkpoint SCN:

SQL > select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

- -

1/ u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1010268

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1010268

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1010268

4 / u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1010268

5 / u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1010268

6 / u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES 1005138 1010268

6 rows selected.

You can find that currently only datafile 6 is created after the first archive log, so if the file corruption, you can restore the data file directly through media recovery without using a backup.

SQL > select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header where creation_change# > 990220

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

- -

6 / u01/app/oracle/oradata/ora11g/users02.dbf ONLINE YES 1005138 1010268

Delete datafile 6 files

[oracle@ora11g scripts] $rm-f / u01/app/oracle/oradata/ora11g/users02.dbf

Restart the database to expose the loss of data files (do not operate in the production environment)

SQL > startup force

ORACLE instance started.

Total System Global Area 730714112 bytes

Fixed Size 2256832 bytes

Variable Size 486539328 bytes

Database Buffers 239075328 bytes

Redo Buffers 2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6-see DBWR trace file

ORA-01110: data file 6:'/ u01 *

If you check the status of the data file, you can find that the database does not recognize the data file 6.

SQL > select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header

FILE# NAME STATUS FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

- -

1/ u01/app/oracle/oradata/ora11g/system01.dbf ONLINE YES 7 1013668

2 / u01/app/oracle/oradata/ora11g/sysaux01.dbf ONLINE YES 1834 1013668

3 / u01/app/oracle/oradata/ora11g/undotbs01.dbf ONLINE YES 923328 1013668

4 / u01/app/oracle/oradata/ora11g/users01.dbf ONLINE YES 16143 1013668

5 / u01/app/oracle/oradata/ora11g/example01.dbf ONLINE YES 952919 1013668

6 ONLINE 0 0

First offline datafile 6 to open the database

SQL > alter database datafile 6 offline

Database altered.

SQL > alter database open

Database altered.

Create data file 6

SQL > alter database create datafile'/ u01qqappActionoradataUniqora11gUnix users02.dbf'

Database altered.

Restore database files 6

SQL > recover datafile 6

ORA-00279: change 1005138 generated at 07/09/2016 02:33:49 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc

ORA-00280: change 1005138 for thread 1 is in sequence # 6