Oracle manual full recovery case (archive Mode)

Oracle manual full recovery case (archive mode) before doing the case, first view the v $ archived_log record, find too many, consider clearing these archives and then generate some and then experiment. Deleting these archives on the operating system does not have records in the control file. Therefore, there are still records when viewing v $ archived_log. We recommend using RMAN to delete these archives:

RMAN>delete archivelogall;

 

Then check v $ archived_log and find that there are still records. There are three methods to solve the problem after google. 1. Recreate the control file, for example, backup controlfileto trace, and then recreate the control file. However, all data files must exist. 2. Set control_file_record_keep_time = 0 and wait for the record to be reused. 3. Use the package for cleanup. Do not do this on the production database.

PROCEDURE resetCfileSection (record_typeINbinary_integer);-This procedure attemptsto reset the circular controlfilesection. -Input parameters:-record_type-The circular record type whose controlfile sectionisto be reset.exe cute sys. dbms_backup_restore.resetCfileSection (11); ==> clear the execute sys record corresponding to v $ ARCHIVED_LOG. dbms_backup_restore.resetCfileSection (28); ==> clear records corresponding to v $ rman_status

 

Case 1: media fuilure loses data files, and other files do not lose the simulated environment: first, perform a cold backup. View the current log status

Select group #, sequence #, archived, statusfrom v $ log; GROUP # SEQUENCE # arc status ----------------------- ------------------ 1 4 no current 2 2 yes inactive 3 3 yes inactive view archived log select name, sequence # from v $ archived_log; name sequence # logs/u01/fast_recovery_area/MIKE/archivelog/2013_06_28/o1_mf_1_1_8wtf58m4 _. arc 1/ U01/fast_recovery_area/MIKE/archivelog/2013_06_28/o1_mf_1_2_8wtf59cw _. arc 2/u01/fast_recovery_area/MIKE/archivelog/2013_06_28/o1_mf_1_3_8wtf5ctc _. arc 3 at this time I use the test user to create a t1 table, using the TEST tablespace create table t1 (id number); write a circular insert data begin for I in 1 .. 10 loop insert into t1 values (I); end loop; end;/submit commit; view the current log select group #, sequence #, archived, statusfrom v $ log; GROUP # SEQUENCE # arc status ------------- ---------- ---------------- 1 4 no current 2 2 yes inactive 3 3 yes inactive the previous operation changes are recorded in the log with sequence 4. Split a group of logs to archive them. Alter system switch logfile; now the simulated media fails rm-f/u01/oradata/mike /*. dbf off database shutdown abort start database startup error ORA-01157: cannot identify/lockdatafile1-see DBWR tracefile ORA-01110: datafile1: '/u01/oradata/mike/system01.dbf' view the file to be restored select FILE #, error from v $ recover_file; file # ERROR History 1 FILENOTFOUND 2 FILENOTFOUND 3 FILENOTFOUND 4 F ILENOTFOUND 5 FILENOTFOUND 8 FILENOTFOUND now begins to restore cp/backup/cold/mike /*. dbf/u01/oradata/mike/check the difference between the control file header and the data file header SCN. sys @ MIKE> selectfile #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 1553122 2 1553122 3 1553122 4 1553122 5 1553122 8 1553122 sys @ MIKE> selectfile #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE #----------------------- ----- 1 1552527 2 1552527 3 1552527 4 1552527 5 1552527 8 1552527 recovery below database recover database; ORA-00279: change1552528 generatedat06/28/201314: 57: 57 neededfor thread 1 ORA-00289: suggestion: /u01/fast_recovery_area/MIKE/archivelog/2013_06_28/o1_mf_1_1_8wtf58m4 _. arc ORA-00280: change1552528for thread1isinsequence #1 Specify log: {<RET> = suggested | filename | AUTO | CANCEL }... press enter to restore the database by archiving logs. Open the database. Alter databaseopen; finally, use the test user to check t1 data, and the data is returned. Check select file #, checkpoint_change # from v $ datafile; select file #, checkpoint_change # from v $ datafile_header; it is found that the SCN of the control file and the data file header is synchronized.

 

Case 2: recover tablespace (Restore tablespace) simulation environment:

1. log on to the test user to create a table and insert some data. Submit create table t01 (id int) tablespacetest; insert into t01 values (1); insert into t01 values (2 ); insert into t01 values (3); commit; 2. failed to simulate media shutdown abortrm-f/u01/oradata/siqian11g/test01.dbf3. database startup error: ORA-01157: cannot identify/lockdatafile6-see DBWRtracefileORA-01110: datafile6: '/u01/oradata/siqian11g/test01.dbf' view the data file to be restored: select file #, error from v $ recover_file; FILE # ERROR ------- UNKNOWN 1 unknown error 2 unknown error 3 unknown error 4 unknown error 5 unknown error 6 filenotfound4. Restore the data file cp/backup/cold/test01.dbf/u01/oradata/siqian11g/5. offline the data file (simulating high availability), and then start the database alter database datafile6offline; the database alter database open; 6. restore tablespace recover tablespacetest; ORA-00279: change2067764 generatedat06/29/201312: 11: 34 neededfor thre Ad 1ORA-00289: suggestion:/backup/arch/arch_1_819372290_12.logORA-00280: change2067764for thread1isin sequence #12 Specify log: {<RET> = suggested | filename | AUTO | CANCEL }... press enter to restore the tablespace. 7. upload the data file onlinealter database datafile6online; 8. Verify the conn test/testtest @ SIQIAN11> select * from t01; ID ---------- 1 2 3

 

Case 3: Restore the tablespace in the database open state-recover the data file in the database open state (except the system tablespace) to simulate the environment:

1. log on to test @ SIQIAN11> select * from t01; ID ---------- 1 2 3 insert some data: insert into t01 values (4); insert into t01 values (5 ); insert into t01 values (6); commit; 2. delete test01.dbf [oracle @ siqian siqian11g] $ rm-f/u01/oradata/siqian11g/test01.dbf in the database open state and then clear data buffersys @ SIQIAN11> alter system flush buffer_cache; sys @ SIQIAN11> select * from test. t01; select * from test. t01 * ERROR at line 1: ORA-01116: errorin opening databasefile6ORA-01110: datafile6: '/u01/oradata/siqian11g/test01.dbf' ORA-27041: Unknown Error: 2: No suchfileor directoryAdditional information: 33. corrupted datafile offlinealter database datafile6offline; 4. restore the data file [oracle @ siqian siqian11g] $ cp/backup/cold/test01.dbf/u01/oradata/siqian11g/5. recover data files or tablespaces recover datafile 6 or recover tablespace test (here I use the former) sys @ SIQIAN11> recover datafile6; ORA-00279: change2067764 generatedat06/29/201312: 11: 34 neededfor thread 1ORA-00289: suggestion:/backup/arch/arch_1_819372290_12.logORA-00280: change2067764for thread1isinsequence #12 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} Log applied. media recovery complete.6. the offline data file onlinealter database datafile6online; 7. verify sys @ SIQIAN11> select * from test. t01; ID ---------- 1 2 3 4 5 66 rows selected.

 

Case 4: Restore the data file-create a tablespace without backup and delete the data file

1. create a tablespace create tablespace ts_no_test datafile '/u01/oradata/siqian11g/ts_no_test01.dbf' size10m. The control file contains information about the tablespace. 2. use the test user to add the table test @ SIQIAN11> createtable t02 (idint) tablespace ts_no_test; insert into t02 values (1); insert into t02 values (2) in the new tablespace ); insert into t02 values (3); commit; 3. shut down the database and delete the data file shutdown abortrm-f/u01/oradata/siqian11g/ts_no_test01.dbf4 for the tablespace. database startup error: ORA-01157: cannot identify/lockdatafile7-see DBWR tracefileORA-01110: datafile7: '/u01/oradata/siqian11g/ts_no_test01.dbf' view file to be restored: sys @ SIQIAN11> select File #, errorfrom v $ recover_file; FILE # error unknown 1 unknown error 2 unknown error 3 unknown error 4 unknown error 5 unknown error 6 unknown error 7 FILENOTFOUND7 rows selected.5. recover the data FILE sys @ SIQIAN11> alter database datafile7offline; sys @ SIQIAN11> alter databaseopen; (high availability) because I have not performed a backup, I cannot perform a restore (resotre), but the control file records the information of the created tablespace, therefore, you can create an original data file first. And then restore. Alter database create datafile '/u01/oradata/siqian11g/ts_no_test01.dbf'; sys @ SIQIAN11> recover datafile7; Media recovery complete. sys @ SIQIAN11> alter database datafile7online; 6. verify sys @ SIQIAN11> select * from test. t02; ID ---------- 1 2 3

 

Case 5: Restore the data file to a new location to simulate the environment:

1. use the test user to insert some data in the t01 table test @ SIQIAN11> begin 2 for iin 11 .. 15 3 loop 4 insertinto t01 values (I); 5 endloop; 6 end; 7/PL/SQLprocedure successfully completed.2. An error occurred while deleting the simulated media of the relevant data file in the database. [oracle @ siqian siqian11g] $ rm-f/u01/oradata/siqian11g/test01.dbf3. startup error: ORA-01157: cannot identify/lockdata file6-see DBWR tracefileORA-01110: data file6: '/u01/oradata/siqian11g/test01.dbf' view the file information to be restored. sys @ SIQIAN11> selectfil E #, errorfrom v $ recover_file; FILE # error unknown 1 unknown error 2 unknown error 3 unknown error 4 unknown error 5 unknown error 6 FILENOT FOUND6 rows selected.4. Restore the failed media offline and to the new location sys @ SIQIAN11> alter database datafile6 offline; sys @ SIQIAN11> alter databaseopen; (high availability) [oracle @ siqian siqian11g] $ cp/backup/cold/test01.dbf/u01/oradata/5. Rename the data file name in the control file alter database rename file '/u01/oradata/siqian11g/test01.dbf' to '/u01/oradata/test01.dbf'; 6. recover data file sys @ SIQIAN11> recover datafile6; ORA-00279: change2067764 generated at06/29/201312: 11: 34 neededfor thread 1ORA-00289: suggestion:/backup/arch/arch_1_819372290_12.logORA-00280: change2067764 for thread1 is in sequence #12 Specify log: {<RET> = suggested | filename | AUTO | CANCEL }... press enter to resume... 7. the offline data file onlinesys @ SIQIAN11> alter database datafile6 online; 8. verify sys @ SIQIAN11> select * from test. t01; ID ---------- 11 12 13 14 15 1 2 3 4 5 611 rows selected.