Understanding the basic principles of Oracle backup and recovery with SCN changes

SCN is the clock that Oracle hangs on the wall. Get up in the morning, say "Get up SCN"; eat breakfast, name "Breakfast SCN"; go to work, called "Go out SCN ". Any of our activities will correspond to an SCN. We can use an internal Oracle package to obtain the system's SCN (Note: This is only the system's SCN, because oracle also has commit SCN, checkpoint SCN, select SCN, and so on ).

SQL> select dbms_flashback.get_system_change_number "system's scn" from dual;system's scn------------      555956

There is only one SCN in Oracle, and others come from it. We can also take a look at the smallest SCN in the database.

SQL> select creation_change # "Oracle Internal minimum SCN" from V $ datafile where file # = 1; Oracle Internal minimum SCN ----------------- 9

What we add to Oracle, whether it is good or bad, Oracle will be based on SCN, one by one in mind (log), do not dare to forget. Since SCN is incremental, we can find what we do to Oracle at that time point corresponding to the relevant SCN. This is the importance of SCN.
We will keep oracle in the current log group. We can use v $ log for query.

SQL> select group#,sequence#,status from v$log;    GROUP#  SEQUENCE# STATUS---------- ---------- ----------------         1          5 CURRENT         2          3 INACTIVE         3          4 INACTIVE

Next, let's do something about oracle. We create a table t with two fields. The field SCN can be approximately the SCN at the beginning of the transaction.

  SQL> create table t(id int,scn number) tablespace users;Table created.SQL> insert into t values(1,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> select * from t;        ID        SCN---------- ----------         1     585887

Let's take a look at first_change # In V $ log #.

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';Session altered.SQL> select group#,status,first_change#,first_time from v$log;    GROUP# STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------------- ------------- -------------------         1 CURRENT                 583374 2012/07/17 19:59:23         2 INACTIVE                560959 2012/07/17 17:13:32         3 INACTIVE                560981 2012/07/17 17:14:33

Here first_change # is the same as first_time, both of which are two manifestations of SCN. First_change # Is the SCN of the system in which logs are made up of the current log group. It serves as the minimum or initial SCN of this group. What we do, the corresponding SCN, will be larger than first_change.
To continue, We archive the current log group.

SQL> alter system switch logfile;System altered.

Look at first_change in V $ log #

SQL> select group#,status,first_change#,first_time from v$log;    GROUP# STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------------- ------------- -------------------         1 ACTIVE                  583374 2012/07/17 19:59:23         2 CURRENT                 586090 2012/07/18 09:35:40         3 INACTIVE                560981 2012/07/17 17:14:33

Now the current log group is changed to 2nd groups, and first_change # also changes.
Let's continue with our unfinished work.

SQL> insert into t values(2,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> select * from t;        ID        SCN---------- ----------         1     585887         2     586129

From this we can see that 586129 is larger than first_change # (586090) of the current log Group 2. Therefore, it is proved that first_change # is the smallest SCN in the current log group. After that, the SCN generated by anything we do will be larger than this one.
However, we archive the log group 2.

SQL> alter system switch logfile;System altered.SQL> select group#,status,first_change#,first_time from v$log;    GROUP# STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------------- ------------- -------------------         1 ACTIVE                  583374 2012/07/17 19:59:23         2 ACTIVE                  586090 2012/07/18 09:35:40         3 CURRENT                 586181 2012/07/18 09:39:21

Now, log group 3 is changed to the current log group, and the corresponding first_change # also changes.
Let's continue. To generate more archive logs, we constantly insert, submit, but change.

SQL> insert into t values(3,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> insert into t values (4,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> insert into t values (5,dbms_flashback.get_system_change_number);1 row created.SQL> commit;     Commit complete.SQL> alter system switch logfile;System altered.SQL> insert into t values(6,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> insert into t values (7,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> insert into t values (8,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered.SQL> select * from t;        ID        SCN---------- ----------         1     585887         2     586129         3     586643         4     586666         5     586692         6     586722         7     586751         8     586805

Let's take a look at which group is the current log group?

SQL> select group#,status,first_change#,first_time from v$log;    GROUP# STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------------- ------------- -------------------         1 ACTIVE                  586734 2012/07/18 09:45:12         2 ACTIVE                  586762 2012/07/18 09:46:15         3 CURRENT                 586816 2012/07/18 09:47:15

The current log group is 3. So, we will insert it again.

SQL> insert into t values(9,dbms_flashback.get_system_change_number);1 row created.SQL> commit;Commit complete.

At this time, we did not change the log group. And then insert.

SQL> insert into t values(10,dbms_flashback.get_system_change_number);1 row created.

Note: At this time, we have not submitted or changed. Then, the 9th and 10th pieces of data are on the log group 3.
Here, we simulate an experiment to illustrate the basic principles of backup and recovery.
Experiment: when the data file is properly shut down, it is completely restored.

[oracle@localhost ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 20:48:19 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.[oracle@localhost ORCL]$ cd datafile/[oracle@localhost datafile]$ lso1_mf_example_8050jhm7_.dbf  o1_mf_temp_8050j34j_.tmpo1_mf_sysaux_8050fk3w_.dbf   o1_mf_undotbs1_8050fkc6_.dbfo1_mf_system_8050fk2z_.dbf   o1_mf_users_8050fkdh_.dbf[oracle@localhost datafile]$ rm o1_mf_system_8050fk2z_.dbf[oracle@localhost datafile]$ rm o1_mf_sysaux_8050fk3w_.dbf[oracle@localhost datafile]$ rm o1_mf_users_8050fkdh_.dbf [oracle@localhost datafile]$ rm o1_mf_undotbs1_8050fkc6_.dbf

In this case, what error will be reported if we want to start the database?

[oracle@localhost ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 17 20:57:00 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area  419430400 bytesFixed Size                  1219760 bytesVariable Size             142607184 bytesDatabase Buffers          272629760 bytesRedo Buffers                2973696 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1:'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8050fk2z_.dbf'

Because there is a control file, we will go to the Mount state, which is an oracle mediation state. In this status, we can do a lot of things. In this case, it indicates that file 1 cannot be locked. Then, let's start one by one. First, copy the cold backup file 1.
[Oracle @ localhost datafile] $ CP o1_mf_system_8050fk2z _. DBF/u01/APP/Oracle/oradata/orcl/datafile
Then, open the database to see what error will be reported?

SQL> alter database open;alter database open*ERROR at line 1:ORA-01113: file 1 needs media recoveryORA-01110: data file 1:'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_8050fk2z_.dbf'

The errors reported at this time are different. File 1 must be restored by media. Why Does Oracle report this error? To understand this, we need to use two views.

SQL> select file#,checkpoint_change# from v$datafile;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1             587004         2             587004         3             587004         4             587004         5             587004SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1             583375         2                  0         3                  0         4                  0         5             587004

The two views have different information sources. The V $ datafile information comes from the control file; the V $ datafile_header information comes from the file header of each data file. We have just copied file 1 back, so Oracle can read the SCN on it. However, 2, 3, and 4 cannot be read if they have been deleted. However, the SCN of file 1 is inconsistent between the two. Remember, Oracle will compare horizontally and vertically. That is, file 1 and file 3 are not compared. The necessary condition for opening oracle is that the SCN of the control file and the data file header must be consistent. If the number is greater than 583375, and the number is less than 585469, the SCN is in the archive log. Operations related to each SCN.

SQL> select sequence#,first_change#,next_change# from v$archived_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------         5        544404       558719         6        558719       559931         7        559931       560709         8        560709       560959         9        560959       560981        10        560981       583374

What is next_change #? When a log group is changed from the current log group to a non-current log group, the obtained system SCN is used as its maximum SCN. First_change # is the beginning of its current career, while next_change # is a sign that it has ended its current career.
We know that SCN smaller than 583375 has been written into data files. Now, we need to determine which pair of first_change # And next_change # falls in 583375. To determine the starting point of generalized rollback.

SQL> select sequence#,first_change#,next_change# from v$archived_log  2  where 583375>=first_change# and  3  583375<=next_change#; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------        11        583374       586090

As a result, we know that 583375 falls between first_change # And next_change # of archive log 11. When we recover, we start from archiving log 11. How many archive logs do we need?

SQL> select sequence#,first_change#,next_change# from v$archived_log  2  where sequence#>=11; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#---------- ------------- ------------        11        583374       586090        12        586090       586181        13        586181       586656        14        586656       586676        15        586676       586704        16        586704       586734        17        586734       586762        18        586762       586816

We can see from the above that if we want to retrieve all the data, we need to use archive log 18. Let's take a look at the characteristics of these first_change # And next_change?
Next_change # of archive log 11 is the first_change # of archive log 12 #. And so on. Therefore, the logic of so many archived logs is simply an archive log. Therefore, the archived logs must be continuous! If your archived log 13 is broken, you can only restore it to next_change # of 12 #. There are more archives in the future.
Next, we start to recover.

SQL> recover datafile 1;ORA-00279: change 583375 generated at 07/17/2012 19:59:23 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_%u_.arcORA-00280: change 583375 for thread 1 is in sequence #11Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Oracle tells us that 583375 is required for instances. In addition, the archived log 11 is in the flash back area. If you press enter, we recommend that you use Oracle. Oracle will find it in the flash back area. Let's press the Enter key to adopt the Oracle suggestion. The second option is not in the default path. You can tell oracle where the archive log is. You only need to tell Oracle the absolute path + name of the archived log. The third option, if there are a lot of archived logs, it is very troublesome to find them one by one, so we will go to auto. The fourth option, if you restore to half, or if there is no archived log, you can tap cancel.

ORA-00279: change 586090 generated at 07/18/2012 09:35:40 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_%u_.arcORA-00280: change 586090 for thread 1 is in sequence #12ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_80d4qdmh_.arc' no longer needed for this recoverySpecify log: {<RET>=suggested | filename | AUTO | CANCEL}

At this time, Oracle will tell us that log 12 is required by the instance. Let's put this aside first. First go to the data file header and fetch the SCN.

SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1             586090         2                  0         3                  0         4                  0         5             587004

Found? The SCN of file 1 becomes 586090. And 586090 is the first_change # of archive log 12 #. No wonder oracle tells us that log 12 is required by the instance. Next, we will press auto.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00279: change 586181 generated at 07/18/2012 09:39:21 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_%u_.arcORA-00280: change 586181 for thread 1 is in sequence #13ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_80d4y9fp_.arc' no longer needed for this recoveryORA-00279: change 586656 generated at 07/18/2012 09:42:06 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_%u_.arcORA-00280: change 586656 for thread 1 is in sequence #14ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_80d53g42_.arc' no longer needed for this recoveryORA-00279: change 586676 generated at 07/18/2012 09:42:59 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_%u_.arcORA-00280: change 586676 for thread 1 is in sequence #15ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_80d5541h_.arc' no longer needed for this recoveryORA-00279: change 586704 generated at 07/18/2012 09:44:03 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_16_%u_.arcORA-00280: change 586704 for thread 1 is in sequence #16ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_80d57315_.arc' no longer needed for this recoveryLog applied.Media recovery complete.

Found? Oracle only uses archive logs to 16. There will be no prompt for the next 17th and 18 th. Why? Let's take a look at the data file header and get the SCN of file 1.

SQL> select file#,checkpoint_change# from v$datafile_header;     FILE# CHECKPOINT_CHANGE#---------- ------------------         1             587003         2                  0         3                  0         4                  0         5             587004

587003 is better than next_change # (586816) of log 18. Let's take a look at which group is in the current day.

SQL> select group#,sequence#,status,first_change# from v$log;    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE#---------- ---------- ---------------- -------------         1         17 INACTIVE                586734         3         19 CURRENT                 586816         2         18 INACTIVE                586762

It can be seen that the first_change # of archive log 19 is 586816. The SCN of the data file header is 587003. When we press recover datafile 1, Oracle is completely restoring it. The start point and end point of full recovery have been determined. The start point is in the data file header, and the end point is obtained in the control file. Because the archived redo log files 17 and 18 are read from the online redo log files 1 and 2. Oracle will first find online redo log files. In other words, Oracle will find the online redo log file itself during full recovery. If the file is not completely restored, we can enter the absolute path and name of the online redo log file. The current log group is 3, and its first_change # Is 586816, while 587003 is larger than this number. It can be seen that Oracle has used the current log file.
Then recover. This time, we copied all the remaining data files back. Then we can move forward together until the pace is consistent, so that Oracle is in a consistent state.

SQL> recover database;ORA-00279: change 583375 generated at 07/17/2012 19:59:23 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_%u_.arcORA-00280: change 583375 for thread 1 is in sequence #11Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00279: change 586090 generated at 07/18/2012 09:35:40 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_%u_.arcORA-00280: change 586090 for thread 1 is in sequence #12ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_11_80d4qdmh_.arc' no longer needed for this recoveryORA-00279: change 586181 generated at 07/18/2012 09:39:21 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_%u_.arcORA-00280: change 586181 for thread 1 is in sequence #13ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_12_80d4y9fp_.arc' no longer needed for this recoveryORA-00279: change 586656 generated at 07/18/2012 09:42:06 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_%u_.arcORA-00280: change 586656 for thread 1 is in sequence #14ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_13_80d53g42_.arc' no longer needed for this recoveryORA-00279: change 586676 generated at 07/18/2012 09:42:59 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_%u_.arcORA-00280: change 586676 for thread 1 is in sequence #15ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_14_80d5541h_.arc' no longer needed for this recoveryORA-00279: change 586704 generated at 07/18/2012 09:44:03 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_16_%u_.arcORA-00280: change 586704 for thread 1 is in sequence #16ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_18/o1_mf_1_15_80d57315_.arc' no longer needed for this recoveryLog applied.Media recovery complete.

Next, we can open the database.

SQL> alter database open;Database altered.

If the SCN corresponding to the transaction falls into which archivelog, this archivelog will be used for recovery.

To sum up, in this blog, I used SCN and full recovery of corrupted data files under normal shutdown to help myself and everyone understand the principles of Oracle backup and recovery. If there are enough netizens who want to pass by, they will be criticized. Oracle backup and recovery is an art. Grow together. Go for it.