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

Experimental environment

Operating system: CentOS 7.1

Database: Oracle 11.2.0.4

Catalogue

Example 1: restore based on SCN or point-in-time-restore tables that were mistakenly deleted at some time in the past

Example 2: the current log group is damaged, causing the database to crash

Example 3: missing or corrupted archive log

Example 1: restore based on SCN or point-in-time-restore tables that were mistakenly deleted at some time in the past

Environment:

(1) make a cold backup of the database in advance.

(2) under seiang users, there is a test1 table that belongs to the seiang tablespace.

Locate the time when the error operation occurred or SCN:logminer

Recovery principle:

1. The cold backup data file * .dbf is older than it is now, so the scn of the data file must be smaller than it is now.

two。 Use the log mining method to find out the scn or timestamp when the table is deleted in the log

3. In the state of database shutdown, overwrite the current * .dbf file with the cold backup data file * .dbf; ensure the integrity of the data file

4. Start the database to mount and restore to the SCN or point in time of log mining (redo the data file using the log)

5. If you open the database in resetlogs, the previous log will be overwritten

Example 2: the current log group is damaged, causing the database to crash

Environment:

(1) make a cold backup of the database in advance.

(2) under seiang users, there is a test1 table that belongs to the seiang tablespace.

Example 3: missing or corrupted archive log

Environment:

(1) Cold backup of a set of completed data files in advance

(2) under seiang users, there is a test1 table that belongs to the seiang tablespace.

SYS@seiang11g > select owner,table_name,tablespace_name from dba_tables where owner='SEIANG'

OWNER TABLE_NAME TABLESPACE_NAME

-

SEIANG TEST1 SEIANG

SEIANG TEST2 WJQ

SEIANG TEST3 WJQBEST

SYS@seiang11g > select * from seiang.test1

ID NAME AGE

1 wjq 23

2 seiang 24

3 wjqdood 25

4 wjqbest 30

-- View the current log serial number is 2

SYS@seiang11g > select group#,sequence#,status from v$log

GROUP# SEQUENCE# STATUS

1 1 INACTIVE

2 2 CURRENT

3 0 UNUSED

-- modify the data and submit it (sequence no. 2)

SYS@seiang11g > update seiang.test1 set age=100 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- modify the data and submit it (sequence No. 3)

SYS@seiang11g > update seiang.test1 set age=200 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- modify the data and submit it (sequence No. 4)

SYS@seiang11g > update seiang.test1 set age=300 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- modify the data and submit it (sequence No. 5)

SYS@seiang11g > update seiang.test1 set age=400 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- modify the data and submit it (sequence No. 6)

SYS@seiang11g > update seiang.test1 set age=500 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- modify the data and submit it (sequence no. 7)

SYS@seiang11g > update seiang.test1 set age=600 where id=1

1 row updated.

SYS@seiang11g > commit

Commit complete.

SYS@seiang11g > alter system switch logfile

System altered.

-- the current online log serial number is 8

SYS@seiang11g > select group#,sequence#,status from v$log

GROUP# SEQUENCE# STATUS

1 7 ACTIVE

2 8 CURRENT

3 6 ACTIVE

-- View the corresponding archive log

SYS@seiang11g > select name from v$archived_log

NAME

/ u01/app/oracle/arch/arch_1_950962051_1.log

/ u01/app/oracle/arch/arch_1_950962051_2.log

/ u01/app/oracle/arch/arch_1_950971495_1.log

/ u01/app/oracle/arch/arch_1_950971495_2.log

/ u01/app/oracle/arch/arch_1_950972396_1.log

/ u01/app/oracle/arch/arch_1_950972396_2.log

/ u01/app/oracle/arch/arch_1_950972396_3.log

/ u01/app/oracle/arch/arch_1_950972396_4.log

/ u01/app/oracle/arch/arch_1_950972396_5.log

/ u01/app/oracle/arch/arch_1_950972396_6.log

/ u01/app/oracle/arch/arch_1_950972396_7.log

-- close the database

SYS@seiang11g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

-- simulated data file seiang corruption

SYS@seiang11g > host rm / u01/app/oracle/oradata/OraDB11g/seiang01.dbf

-- reopen the database. Due to the death of the data file, the database cannot open.

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

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

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

SYS@seiang11g > select * from v$recover_file

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

--

7 ONLINE ONLINE FILE NOT FOUND 0

-- restore the backed up data files

SYS@seiang11g > host cp / u01/app/oracle/UMAN_Backup/seiang01.dbf / u01/app/oracle/oradata/OraDB11g/

SYS@seiang11g > select * from v$recover_file

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

--

7 ONLINE ONLINE UNKNOWN ERROR 1913765 02-AUG-17

-- check the details of the archive log

SYS@seiang11g > select sequence#, name, first_change#, next_change# from v$archived_log

SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#

-

1/ u01/app/oracle/arch/arch_1_950962051_1.log 1914744 1921854

2 / u01/app/oracle/arch/arch_1_950962051_2.log 1921854 1921883

1/ u01/app/oracle/arch/arch_1_950971495_1.log 1913766 1914386

2 / u01/app/oracle/arch/arch_1_950971495_2.log 1914386 1914401

1/ u01/app/oracle/arch/arch_1_950972396_1.log 1914402 1936446

2 / u01/app/oracle/arch/arch_1_950972396_2.log 1936446 1937042

3 / u01/app/oracle/arch/arch_1_950972396_3.log 1937042 1937100

4 / u01/app/oracle/arch/arch_1_950972396_4.log 1937100 1937110

5 / u01/app/oracle/arch/arch_1_950972396_5.log 1937110 1937123

6 / u01/app/oracle/arch/arch_1_950972396_6.log 1937123 1937139

7 / u01/app/oracle/arch/arch_1_950972396_7.log 1937139 1937148

-- simulated archive log 5 is missing or damaged

SYS@seiang11g > host rm / u01/app/oracle/arch/arch_1_950972396_5.log

[oracle@seiang11g OraDB11g] $ll / u01/app/oracle/arch/arch_1_950972396_5.log

Ls: cannot access / u01/app/oracle/arch/arch_1_950972396_5.log: No such file or directory

-perform a restore operation with an error when log 5 needs to be archived

SYS@seiang11g > recover datafile 7

ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950971495_1.log

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

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

Auto

ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950971495_2.log

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

ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_1.log

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

ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_2.log

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

ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_3.log

ORA-00280: change 1937042 for thread 1 is in sequence # 3

ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_4.log

ORA-00280: change 1937100 for thread 1 is in sequence # 4

ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_5.log

ORA-00280: change 1937110 for thread 1 is in sequence # 5

ORA-00308: cannot open archived log' / u01 ActionPlus oracleThree archetype 1 "950972396" 5.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

-- check the header of the data file and find that the checkpoints are inconsistent

SYS@seiang11g > select file#,name,checkpoint_change# from v$datafile_header

FILE# NAME CHECKPOINT_CHANGE#

-

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf 1937617

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937617

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937617

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf 1937617

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf 1937617

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937617

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110

8 / u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937617

9 / u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937617muri-shut down the database and restore all cold backup data files

SYS@seiang11g > shutdown abort

ORACLE instance shut down.

SYS@seiang11g > host cp / u01/app/oracle/UMAN_Backup/*.dbf / u01/app/oracle/oradata/OraDB11g/

SYS@seiang11g > host ls-l / u01/app/oracle/oradata/OraDB11g/

Total 2279068

-rw-r- 1 oracle oinstall 7680 Aug 2 16:08 arch_1_950972396_5.log

-rw-r- 1 oracle oinstall 9781248 Aug 2 16:16 control01.ctl

-rw-r- 1 oracle oinstall 363077632 Aug 2 16:17 example01.dbf

-rw-r- 1 oracle oinstall 52429312 Aug 2 15:52 redo01.log

-rw-r- 1 oracle oinstall 52429312 Aug 2 15:58 redo02.log

-rw-r- 1 oracle oinstall 52429312 Aug 2 15:52 redo03.log

-rw-r- 1 oracle oinstall 31465472 Aug 2 16:17 rman01.dbf

-rw-r- 1 oracle oinstall 73408512 Aug 2 16:17 seiang01.dbf

-rw-r- 1 oracle oinstall 734011392 Aug 2 16:18 sysaux01.dbf

-rw-r- 1 oracle oinstall 796925952 Aug 2 16:18 system01.dbf

-rw-r- 1 oracle oinstall 30416896 Aug 2 16:18 temp01.dbf

-rw-r- 1 oracle oinstall 110108672 Aug 2 16:18 undotbs01.dbf

-rw-r- 1 oracle oinstall 5251072 Aug 2 16:18 users01.dbf

-rw-r- 1 oracle oinstall 20979712 Aug 2 16:18 wjq01.dbf

-rw-r- 1 oracle oinstall 20979712 Aug 2 16:18 wjqbest01.dbf

-- reopen the database

SYS@seiang11g > startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size 2252824 bytes

Variable Size 788533224 bytes

Database Buffers 452984832 bytes

Redo Buffers 8892416 bytes

Database mounted.

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

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

SYS@seiang11g > select file#,name,checkpoint_change# from v$datafile_header

FILE# NAME CHECKPOINT_CHANGE#

-

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf 1913765

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1913765

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1913765

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf 1913765

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf 1913765

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf 1913765

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1913765

8 / u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1913765

9 / u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1913765

9 rows selected.

-- restore the database, try a full recovery first, and also get an error when you need to archive log 5

SYS@seiang11g > recover database

ORA-00279: change 1913766 generated at 08/02/2017 11:12:22 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950971495_1.log

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

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

Auto

ORA-00279: change 1914386 generated at 08/02/2017 14:48:59 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950971495_2.log

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

ORA-00279: change 1914402 generated at 08/02/2017 14:59:56 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_1.log

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

ORA-00279: change 1936446 generated at 08/02/2017 15:39:52 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_2.log

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

ORA-00279: change 1937042 generated at 08/02/2017 15:49:42 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_3.log

ORA-00280: change 1937042 for thread 1 is in sequence # 3

ORA-00279: change 1937100 generated at 08/02/2017 15:51:34 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_4.log

ORA-00280: change 1937100 for thread 1 is in sequence # 4

ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_5.log

ORA-00280: change 1937110 for thread 1 is in sequence # 5

ORA-00308: cannot open archived log' / u01 ActionPlus oracleThree archetype 1 "950972396" 5.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

-- perform incomplete recovery with success

SYS@seiang11g > recover database until cancel

ORA-00279: change 1937110 generated at 08/02/2017 15:51:54 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/arch/arch_1_950972396_5.log

ORA-00280: change 1937110 for thread 1 is in sequence # 5

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

Cancel

Media recovery cancelled.

-- check the relevant information of the data file header again

SYS@seiang11g > select file#,name,checkpoint_change# from v$datafile_header

FILE# NAME CHECKPOINT_CHANGE#

-

1/ u01/app/oracle/oradata/OraDB11g/system01.dbf 1937110

2 / u01/app/oracle/oradata/OraDB11g/sysaux01.dbf 1937110

3 / u01/app/oracle/oradata/OraDB11g/undotbs01.dbf 1937110

4 / u01/app/oracle/oradata/OraDB11g/users01.dbf 1937110

5 / u01/app/oracle/oradata/OraDB11g/example01.dbf 1937110

6 / u01/app/oracle/oradata/OraDB11g/rman01.dbf 1937110

7 / u01/app/oracle/oradata/OraDB11g/seiang01.dbf 1937110

8 / u01/app/oracle/oradata/OraDB11g/wjq01.dbf 1937110

9 / u01/app/oracle/oradata/OraDB11g/wjqbest01.dbf 1937110

-- check the log group information and find that it is out of touch with No. 5

SYS@seiang11g > select group#,sequence#,status from v$log

GROUP# SEQUENCE# STATUS

1 7 INACTIVE

3 6 INACTIVE

2 8 CURRENT

-- use resetlogs to open the database

SYS@seiang11g > alter database open resetlogs

Database altered.

-- the log sequence number starts from 1.

SYS@seiang11g > select group#,sequence#,status from v$log

GROUP# SEQUENCE# STATUS

1 1 CURRENT

2 0 UNUSED

3 0 UNUSED

-- confirm the data information in the table and find that the changes did not take effect after the No. 5 archive log was lost.

SYS@seiang11g > select * from seiang.test1

ID NAME AGE

-

1 wjq 300

2 seiang 24

3 wjqdood 25

4 wjqbest 30

Related connection: Oracle manual full recovery case: http://blog.itpub.net/31015730/viewspace-2142669/

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

ITPUB: http://blog.itpub.net/31015730/

51CTO: http://seiang.blog.51cto.com/

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.