* Recovery of a normal user data file B (Offline recovery) Please follow the different topics, discuss one: * Only lose a regular user data file recovery B (offline recovery) Preparation The following input, if all recovery, you should see INSERT INTO TEST1 VALUES (13) because INSERT INTO TEST1 VALUES (14) is not submitted. SQL> Conn Lunar / Lunar Connected. SQL> INSERT INTO TEST1 VALUES (13); 1 Row Created. SQL> Commit; Commit Complete. SQL> INSERT INTO TEST1 VALUES (14); 1 Row Created. Shutdown Immediate, then simulate data file Lost Single Open a session, execute Shutdown Immediate (to ensure that INSERT INTO TEST1 VALUES (14); is not submitted by hermit) Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. E: /> SQLPlus Internal SQL * Plus: Release 8.1.7.0.0 - Production on Monday October 21 02:36:07 2002 (C) Copyright 2000 Oracle Corporation. All Rights Reserved. Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production WITH The Partitioning Option JServer Release 8.1.7.0.0 - Production SQL> Shutdown Immediate Database has been closed. The database has been uninstalled. Oracle routines have been closed.
SQL> Analog data file is lost, then override this file with hot standby Mount Database E: /> SQLPlus Internal SQL * Plus: release 8.1.7.0.0 - Production on Mon Oct 21 02:42:47 2002 (c) Copyright 2000 Oracle .. Corporation All rights reserved Connected to an idle instance SQL> shutdown immediate ORA-01034:. ORACLE not available ORA-27101:. shared memory realm does not exist SQL> startup mount ORACLE instance started Total System Global Area 25856028 bytes Fixed Size 75804 bytes Variable Size 8925184 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted SQL> so corrupted data file offline SQL> alter database datafile 'D: /BACKUPDB/USERS01.DBF'. offline; Database altered recover data files SQL>. Recover DataFile 'D: /backupdb/users01.dbf'; ora-00279: Change 424116 Generated AT 10/20/2002 20:42:04 Needed for thread 1 ORA-00289: Suggestion: D: / backupdb / archive / backupt001s0000001. Arc Ora-00280: Change 424116 for Thread 1 Is in Sequence # 1 Specify Log: {
no longer needed for this recovery ORA-00279: change 424139 generated at 10/20/2002 21:57:42 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00004.ARC ORA-00280: change 424139 For thread 1 is in sequence # 4 ora-00278: log file 'd: /backupdb/archive/backupt001s00003.arc' no longer needed for this recovery ora-00279: change 424143 generated at 10/20/2002 21:57:54 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00005.ARC ORA-00280: change 424143 for thread 1 is in sequence # 5 ORA-00278: log file 'D: / BACKUPDB / ARCHIVE / BACKUPT001S00004. Arc 'no longer needed for this recovery ora-00279: change 444145 generated at 10/20/2002 22:01:23 needed for thread 1 ORA-00289: Suggestion: D: /backupdb/archive/backupt001s00006.arc ora-00280: change 444145 for thread 1 is in sequence # 6 ORA-00278: log file 'D: /BACKUPDB/ARCHIVE/BACKUPT001S00005.ARC' no longer needed for this recovery ORA-00279: change 464194 generated at 10/20/2002 22:24 : 10 Needed F or thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00007.ARC ORA-00280: change 464194 for thread 1 is in sequence # 7 ORA-00278: log file 'D: /BACKUPDB/ARCHIVE/BACKUPT001S00006.ARC 'no longer needed for this recovery ORA-00279: change 484347 generated at 10/21/2002 00:16:14 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00008.ARC ORA-00280: change 484347 for thread 1 is in sequence # 8 ora-00278: log file 'd: /backupdb/archive/backupt001s00007.arc'
no longer needed for this recovery ORA-00279: change 484401 generated at 10/21/2002 00:30:27 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00009.ARC ORA-00280: change 484401 For thread 1 is in sequence # 9 ora-00278: log file 'd: /backupdb/archive/backupt001s00008.arc' no longer needed for this recovery ORA-00279: Change 484453 generated at 10/21/2002 00:33:51 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00010.ARC ORA-00280: change 484453 for thread 1 is in sequence # 10 ORA-00278: log file 'D: / BACKUPDB / ARCHIVE / BACKUPT001S00009. ARC 'no longer needed for this recovery ORA-00279: change 484508 generated at 10/21/2002 00:38:40 needed for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00011.ARC ORA-00280: change 484508 for thread 1 is in sequence # 11 ORA-00278: log file 'D: /BACKUPDB/ARCHIVE/BACKUPT001S00010.ARC' no longer needed for this recovery ORA-00279: change 504514 generated at 10/21/2002 00:51 : 00 NEEDED for thread 1 ORA-00289: suggestion: D: /BACKUPDB/ARCHIVE/BACKUPT001S00012.ARC ORA-00280: change 504514 for thread 1 is in sequence # 12 ORA-00278: log file 'D: /BACKUPDB/ARCHIVE/BACKUPT001S00011.ARC 'no longer needed for this recovery Log applied Media recovery complete SQL> recover the data files online SQL> alter database datafile..' D: /BACKUPDB/USERS01.DBF 'online; database altered to open the database SQL> alter database open;. Database altered. SQL> This time you need to restart the database and completely restore the database SQL> Conn Lunar / Lunar Connected. SQL> Select Count (*) from test; select count (*) from test * error at line 1: ora-00942 : Table or View Does NOT SQL> CONN INTERNAL Connected. SQL>
shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. Restart the database, SQL> startup ORACLE instance started. Total System Global Area 25856028 bytes Fixed Size 75804 bytes Variable Size 8925184 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted. Database . opened SQL> using recover database recover again database SQL> conn internal Connected SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-01124: can not recover data file 1 - file is in use or recovery ORA-01110. : Data file 1: 'D: /backupdb/system01.dbf' Re-restore tablespace online SQL> ALTABASE DATAFILE 'D: /BACKUPDB / ISERS01.DBF' online; Database Altered. SQL> Conn Lunar / Lunar Connected. SQL> Select * from test1; a ---------- 1 2 3 4 5 6 7 8 9 10 11 A ---------- 12 13 13 Rows SELECTED. SQL> Verification Recovery Result: Fully Recovery Description: 1. Override this file with hot standby 2. Mount Database 3. Make damaged data files Offline 4. Restore data file 5. Enable recovered data files 6. Open Database 7. You need to restart the database and completely restore the database 8. Restart the database, 9. Restore the database again with Recover Database 10. Re-restore the recovered tablespace http://www.cnoug.org/viewthread.php ? TID = 86 & highlight =% 2blunar
