Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Restore data files without backup and full archive log

2025-04-09 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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

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

ORA-00279: change 1006241 generated at 07/09/2016 02:36:15 needed for thread 1

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

ORA-00280: change 1006241 for thread 1 is in sequence # 7

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

ORA-00279: change 1006650 generated at 07/09/2016 02:41:36 needed for thread 1

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

ORA-00280: change 1006650 for thread 1 is in sequence # 8

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

ORA-00279: change 1009513 generated at 07/09/2016 03:04:58 needed for thread 1

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

ORA-00280: change 1009513 for thread 1 is in sequence # 9

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

ORA-00279: change 1009663 generated at 07/09/2016 03:04:59 needed for thread 1

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

ORA-00280: change 1009663 for thread 1 is in sequence # 10

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

ORA-00279: change 1009860 generated at 07/09/2016 03:05:28 needed for thread 1

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

ORA-00280: change 1009860 for thread 1 is in sequence # 11

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

ORA-00279: change 1010001 generated at 07/09/2016 03:05:32 needed for thread 1

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

ORA-00280: change 1010001 for thread 1 is in sequence # 12

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

Log applied.

Media recovery complete.

SQL > SQL >

Recovery complete

View data file status

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 1034000

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

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

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

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

6 / u01/app/oracle/oradata/ora11g/users02.dbf OFFLINE NO 1005138 1034000

Online data File 6

SQL > alter database datafile 6 online

Database altered.

Restore completed, confirm the status of the data file

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 1034000

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

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

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

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

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

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.

Views: 225

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report