Recovery Based on log serial numbers based on incomplete recovery of Oracle Database RMAN

I have previously introduced:OracleDatabaseRMAN Incomplete recoveryThis document describes how to restore an Oracle database based on SCN.Log serial number recoveryTo help you.

Log serial number recovery refers to restoring the database to the status of the specified log serial number.

 
 

  
  
-- View archived log information
  
  
SQL> select * from t_user;
  
  
TEXT
  
  
--------------------
  
  
Java _
  
  
Spring _
  
  
Spring mvc _
  
  
SQL> insert into t_user select 'oracle _ 'from dual;
  
  
1 row created.
  
  
SQL> commit;
  
  
Commit complete.
  
  
SQL> alter system switch logfile;
  
  
System altered.
  
  
SQL> alter system checkpoint;
  
  
System altered.
  
  
SQL> select sequence #, name, first_change # from v $ archived_log where status = 'A' order by sequence #;
  
  
SEQUENCE # NAME FIRST_CHANGE #
  
  
---------- Certificate ---------------------------------------------------------------------------------------------------------------------------
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487088.arclog 1214497
  
  
1/oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214498
  
  
5q9bh9d _. arc
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/log/archive_log2/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214497
  
  
5q95ksf _. arc
  
  
6 rows selected.
  
  
SQL> insert into t_user select 'oracle _ seq3 'from dual;
  
  
1 row created.
  
  
SQL> commit;
  
  
Commit complete.
  
  
SQL> alter system switch logfile; -- generate an archive log whose log serial number is 2
  
  
System altered.
  
  
SQL> alter system checkpoint;
  
  
System altered.
  
  
SQL> select sequence #, name, first_change # from v $ archived_log where status = 'A' order by sequence #;
  
  
SEQUENCE # NAME FIRST_CHANGE #
  
  
---------- Certificate ---------------------------------------------------------------------------------------------------------------------------
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487088.arclog 1214497
  
  
1/oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214497
  
  
5q95ksf _. arc
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/log/archive_log2/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214498
  
  
5q9bh9d _. arc
  
  
2/oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog 1216167
  
  
SEQUENCE # NAME FIRST_CHANGE #
  
  
---------- Certificate ---------------------------------------------------------------------------------------------------------------------------
  
  
2/oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog 1216167
  
  
2/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_1_2_7 1216167
  
  
5q9cvt1 _. arc
  
  
9 rows selected.
  
  
SQL> insert into t_user select 'oracle _ seq3_act 'from dual;
  
  
1 row created.
  
  
SQL> commit;
  
  
Commit complete.
  
  
SQL> alter system switch logfile; -- generate an archive log whose log serial number is 3
  
  
System altered.
  
  
SQL> alter system checkpoint;
  
  
System altered.
  
  
SQL> select sequence #, name, first_change # from v $ archived_log where status = 'A' order by sequence #;
  
  
SEQUENCE # NAME FIRST_CHANGE #
  
  
---------- Certificate ---------------------------------------------------------------------------------------------------------------------------
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487088.arclog 1214497
  
  
1/oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214497
  
  
5q95ksf _. arc
  
  
1/oracle/10g/oracle/log/archive_log/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/log/archive_log2/archive_00000000760487985.arclog 1214498
  
  
1/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_000000007 1214498
  
  
5q9bh9d _. arc
  
  
2/oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog 1216167
  
  
SEQUENCE # NAME FIRST_CHANGE #
  
  
---------- Certificate ---------------------------------------------------------------------------------------------------------------------------
  
  
2/oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog 1216167
  
  
2/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_1_2_7 1216167
  
  
5q9cvt1 _. arc
  
  
3/oracle/10g/oracle/log/archive_log/archive_1_3_760487985.arclog 1216186
  
  
3/oracle/10g/oracle/log/archive_log2/archive_1_3_760487985.arclog 1216186
  
  
3/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/20151108_29/o2017mf_1_3_7 1216186
  
  
5q9f4d6 _. arc
  
  
12 rows selected.
  
  
-- Restore to the status when the log serial number is 3
  
  
[Oracle @ localhost ~] $ Rman target sys/oracle @ oralife nocatalog
  
  
RMAN> run {
  
  
Startup force mount;
  
  
Set until sequence = 3;
  
  
Restore database;
  
  
Recover database;
  
  
SQL 'alter database open resetlogs ';
  
  
}
  
  
-- View. The archived log information oracle_seq3_act with the log serial number 3 is not included. That is, the archived log with the log serial number 2 is restored.
  
  
SQL> conn sys/oracle @ oralife as sysdba
  
  
Connected.
  
  
SQL> select * from t_user;
  
  
TEXT
  
  
--------------------
  
  
Java _
  
  
Spring _
  
  
Oracle _
  
  
Oracle_seq3
  
  
Spring mvc _
 
 

It can be seen that the archived log information oracle_seq3_act with the log serial number 3 is not included, that is, the archived log with the log serial number 2 is restored.

After Incomplete recovery is performed, we recommend that you delete all the earlier backups and back up the database again.

Here is an introduction to Oracle Database RMAN Incomplete recovery and log serial number-based recovery. I hope this introduction will help you gain some benefits!