Oracle manual recovery case (non-archive Mode)

Oracle manual recovery case 1: Historical logs are not overwritten

1. first, cut the database into non-archive mode: startup mountforcealter database noarchivelog; alter database open; view database mode again sys @ SIQIAN11> archive log listDatabase log mode No Archive ModeAutomaticarchival DisabledArchivedestination/backup/archOldest online log sequence 15 Current log sequence 172. cold backup 3. view the current log information sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; GROUP # SEQUENCE # arc status ------------------------------- -------- 1 19 no current 2 17 no inactive 3 18 NO INACTIVE4. Log On with the test user to create a table and insert data test @ SIQIAN11> create table t01 (id int) tablespace test; test @ SIQIAN11> begin 2 for I in 1 .. 10 3 loop 4 insert into t01 values (I); 5 end loop; 6 end; 7/commit; view the current log sys @ SIQIAN11> select group #, sequence #, archived, status from v $ log; GROUP # SEQUENCE # arc status ---------- ------------- ------------------ 1 19 no current 2 17 NO INACTIVE 3 18 no inactive indicates that the change was recorded in 1st sets of logs, and the logs were not overwritten. 5. close the database and delete the data file shutdown abort [oracle @ siqian siqian11g] $ rm-f/u01/oradata/siqian11g/test01.dbf6. startup error: ORA-01157: cannot identify/lock data file 6-see DBWR trace fileORA-01110: data file 6: '/u01/oradata/siqian11g/test01.dbf' view the data file to be restored sys @ SIQIAN11> select file #, error from v $ recover_file; FILE # ERROR ---------- unknown 1 UNKNOWNERROR 2 UNKNOWNERROR 3 UNKNOWNERROR 4 UNKNOWNERROR 5 UNKNOWNERROR 6 file not FOUND6 rows selected.7. restore the data FILE and restore it [oracle @ siqian Resume] $ cp/backup/cold/ test01.dbf/u01/oradata/siqian11g/sys @ SIQIAN11> recover datafile 6; media recoverycomplete. sys @ SIQIAN11> alter database open; 8. verify sys @ SIQIAN11> select * from test. t01; ID ---------- 1 2 3 4 5 6 7 8 9 1010 rows selected.

 

Case 2: When a log is switched, the historical log is overwritten (only incomplete recovery is allowed) and the previous example is taken. After some operations are completed, the log is switched.

Simulation Environment: 1. log on as the test user and insert some data in the t01 table. test @ SIQIAN11> begin 2 for I in 11 .. 20 3 loop 4 insert into t01 values (I); 5 end loop; 6 end; 7/PL/SQL procedure successfully completed. commit; 2. view the current log information sys @ SIQIAN11> select group #, sequence #, archived, first_change # from v $ log; GROUP # SEQUENCE # ARC FIRST_CHANGE # ------------------ --- ------------- 1 19 NO 2200111 2 20 NO 2225498 3 18 NO 2179197 that is to say, the change was recorded in the 1st GROUP 3. switch logs to be overwritten Cover alter system switch logfile; switch multiple times and view again: sys @ SIQIAN11> select group #, sequence #, archived, first_change # from v $ log; GROUP # SEQUENCE # ARC FIRST_CHANGE # ---------- ------------- 1 22 NO 2226207 2 23 NO 2226211 3 21 NO 2226205 now the SEQUENCE 19 has been overwritten. In addition, the t01 table has 20 records. 4. shut down the database and delete the corresponding data file shutdown abort [oracle @ siqian siqian11g] $ rm-f/u01/oradata/siqian11g/test01.dbf5. ORA-01157: cannot identify/lock data file 6-see DBWR trace fileORA-01110: data file 6: '/u01/oradata/siqian11g/test01.dbf' sys @ SIQIAN11> select file #, error from v $ recover_file; FILE # ERROR details 1 unknown error 2 UNKNOWN ERR OR 3 unknown error 4 unknown error 5 unknown error 6 file not FOUND6 rows selected.6. Restore the data FILE and restore it [oracle @ siqian siqian11g] $ cp/backup/cold/test01.dbf/u01/oradata/ siqian11g/sys @ SIQIAN11> recover datafile 6; ORA-00279: change 2201687 generated at 06/29/2013 18:36:58 needed for thread 1ORA-00289: suggestion:/backup/arch/arch_1_819372290_19.logORA-00280: change 2201687 for thread 1 is in sequence # 19Sp Ecify log: {<RET> = suggested | filename | AUTO | CANCEL} autoORA-00308: cannot open archived log '/backup/arch/arch_1_819372290_19.log' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00308: cannot open archived log '/backup/arch/arch_rj819372290_19.log' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAddition Al information: 3 because the logs in sequence 19 are not archived, an error is reported during recovery. Therefore, Incomplete recovery is required. 7. restore all control files and data files for Incomplete recovery [oracle @ siqian siqian11g] $ cp/backup/cold /*. ctl/u01/oradata/siqian11g/[oracle @ siqian siqian11g] $ cp/backup/cold /*. dbf/u01/oradata/siqian11g/8. mount the database and view the SCN information of the data file header and control file header. startup mount force: View SCNsys @ SIQIAN11> select file #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE # -------------------------- 1 2201687 2 2201687 3 2201687 4 2201687 5 2201687 6 2201 6876 rows selected. sys @ SIQIAN11> select file #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # ---------------------------- 1 2201687 2 2201687 3 2201687 4 2201687 5 2201687 6 22016876 rows selected. however, the SCN in redo is not synchronized with the data file and the SCN in the control file, so now the database is opened with the problem of alter database open; ERROR at line 1: ORA-00338: log 1 of thread 1 is more recent than control fileORA-00312: online log 1 thread 1: '/u01/oradata/siqia N11g/redo01.log' 7. incomplete recovery of the entire database recover database untilcancel; 8. use resetlogs to open the database alter database open resetlogs; 9. verify select * from test. t01; only 10 records are found. 10. Cold backup

 

Because the database is opened in resetlogs mode, it is recommended that a database be fully standby at this time.