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

Today, I will talk to you about how to recover based on the serial number of sequence logs. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The experiments are as follows:

-- create table ab:

SQL > create table ab as select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') as dd from dual

Table created.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

- --

1 1 1 52428800 512 1 NO CURRENT 3933621 08-DEC-17 2.8147E+14

2 1 0 52428800 512 1 YES UNUSED 00

3 1 0 52428800 512 1 YES UNUSED 00

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

SQL >

SQL > alter system switch logfile

System altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

- --

1 1 1 52428800 512 1 YES ACTIVE 3933621 08-DEC-17 3934299 08-DEC-17

2 12 52428800 512 1 NO CURRENT 3934299 08-DEC-17 2.8147E+14

3 1 0 52428800 512 1 YES UNUSED 00

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

SQL >

-- backup database, hot backup:

RMAN > backup database

Starting backup at 08-DEC-17

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf

Input datafile file number=00006 name=/u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf

Input datafile file number=00001 name=/u01/app/oracle/oradata/DBdb/system01.dbf

Input datafile file number=00003 name=/u01/app/oracle/oradata/DBdb/undotbs01.dbf

Channel ORA_DISK_1: starting piece 1 at 08-DEC-17

Channel ORA_DISK_1: finished piece 1 at 08-DEC-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T063250_f2mjdm4b_.bkp tag=TAG20171208T063250 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Input datafile file number=00002 name=/u01/app/oracle/oradata/DBdb/sysaux01.dbf

Input datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbf

Channel ORA_DISK_1: starting piece 1 at 08-DEC-17

Channel ORA_DISK_1: finished piece 1 at 08-DEC-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T063250_f2mjgy6s_.bkp tag=TAG20171208T063250 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Channel ORA_DISK_1: starting full datafile backup set

Channel ORA_DISK_1: specifying datafile (s) in backup set

Including current control file in backup set

Including current SPFILE in backup set

Channel ORA_DISK_1: starting piece 1 at 08-DEC-17

Channel ORA_DISK_1: finished piece 1 at 08-DEC-17

Piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_ncsnf_TAG20171208T063250_f2mjhrc4_.bkp tag=TAG20171208T063250 comment=NONE

Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 08-DEC-17

RMAN >

-- create table bc:

SQL > create table bc as select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') as dd from dual

Table created.

SQL > alter system switch logfile

System altered.

SQL > select * from v$log

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

- --

1 1 1 52428800 512 1 YES INACTIVE 3933621 08-DEC-17 3934299 08-DEC-17

2 12 52428800 512 1 YES ACTIVE 3934299 08-DEC-17 3934606 08-DEC-17

3 1 3 52428800 512 1 NO CURRENT 3934606 08-DEC-17 2.8147E+14

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

SQL >

SQL > select * from ab

DD

-

2017-12-08 06:32:00

SQL > select * from bc

DD

-

2017-12-08 06:35:09

-- simulated deletion of tables ab, bc:

SQL > drop table ab purge

Table dropped.

SQL > drop table bc purge

Table dropped.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

SQL >

-- start the recovery operation:

V [oracle@wang ~] $rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Fri Dec 8 06:39:20 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to target database: DBDB (DBID=3282897732, not open)

RMAN > run {

Set until sequence=2

Restore database

Recover database

Alter database open resetlogs;}

Executing command: SET until clause

Starting restore at 08-DEC-17

Using target database control file instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=20 device type=DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/DBdb/system01.dbf

Channel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/DBdb/undotbs01.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/DBdb/users01.dbf

Channel ORA_DISK_1: restoring datafile 00006 to / u01/app/oracle/fast_recovery_area/DBDB/newback/ts_xxf_01.dbf

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh3w1s_.bkp

Channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh3w1s_.bkp tag=TAG20171208T061035

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:02:25

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/DBdb/sysaux01.dbf

Channel ORA_DISK_1: restoring datafile 00005 to / u01/app/oracle/oradata/DBdb/example01.dbf

Channel ORA_DISK_1: reading from backup piece / u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh6759_.bkp

Channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/backupset/2017_12_08/o1_mf_nnndf_TAG20171208T061035_f2mh6759_.bkp tag=TAG20171208T061035

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 08-DEC-17

Starting recover at 08-DEC-17

Using channel ORA_DISK_1

Starting media recovery

Archived log for thread 1 with sequence 1 is already on disk as file / u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mh8kkg_.arc

Archived log file name=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mh8kkg_.arc thread=1 sequence=1

Archived log file name=/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2017_12_08/o1_mf_1_1_f2mjco49_.arc thread=1 sequence=1

Media recovery complete, elapsed time: 00:00:02

Finished recover at 08-DEC-17

Database opened

RMAN >

-- verify:

SQL > select status from v$instance

STATUS

-

OPEN

SQL > select * from ab

DD

-

2017-12-08 06:32:00

SQL > select * from bc

Select * from bc

*

ERROR at line 1:

ORA-00942: table or view does not exist

Conclusion: when using sequence for incomplete recovery, you need to set the sequence number to the log sequence+1 that you want to recover.

After reading the above, do you have any further understanding of how to recover based on the sequence log sequence number? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.