BACKUP AND RECOVERY SCENARIOS Complete Recovery With User-managed Backup. you can perform complete recovery in the below 5 situations. User Managed Recovery Scenarios of complete recovery. 1. Complete Closed Database Recovery. System datafile is missing(with recent backups) 2. Complete Open Database Recovery. Non system datafile is missing(with backups). 3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing(with backups) 4. Recovery of a Missing Datafile that has no backups.(Disk corrupted and no backups available) restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time). you cannot recover or create datafile without backup in the following situation: view plainprint? 1. SQL> select CONTROLFILE_CREATED from v$database; 2. CONTROLFILE_CREATED 3. -------------------- 4. 07-MAY-2010 01:23:43 view plainprint? 1. SQL> select creation_time,name from v$datafile; 2. CREATION_TIME NAME 3. -------------------- --------------------------------------------- 4. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 5. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 6. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 7. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf 5. Restore and Recovery of a Datafile to a different location.(Disk corrupted having recent backup and recover the datafile in new Disk location). User Managed Recovery Scenarios User managed recovery scenarios do require that the database is in archive log mode, and that backups of all datafiles and control files are made with the tablespaces set to begin backup, if the database is open while the copy is made. At the end of the copy of each tablespace it is necessaire to take it out of backup mode. Alternatively complete backups can be made with the database shutdown. Online redologs can be optionally backed up. Files to be copied: select name from v$datafile; select member from v$logfile; # optional select name from v$controlfile; 1.Complete Closed Database Recovery. System tablespace is missing If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed. Pre requisites: A closed or open database backup and archived logs. 1. Use OS commands to restore the missing or corrupted system datafile to its original location from recent backup, ie: cp -p /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/system01.dbf 2. startup mount; 3. recover datafile 1; 4. alter database open; workshop1: system datafile recovery with recent backup view plainprint? 1. SQL> create user rajesh identified by rajesh; 2. User created. 3. SQL> grant dba to rajesh; 4. Grant succeeded. 5. SQL> shutdown immediate 6. Database closed. 7. Database dismounted. 8. ORACLE instance shut down. 9. i manually deleted the datafile system01.dbf for testing purpose only 10. SQL> startup 11. ORACLE instance started. 12. Total System Global Area 444596224 bytes 13. Fixed Size 1219904 bytes 14. Variable Size 138412736 bytes 15. Database Buffers 301989888 bytes 16. Redo Buffers 2973696 bytes 17. Database mounted. 18. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file 19. ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf' 20. 21. 22. SQL> shutdown immediate 23. ORA-01109: database not open 24. 25. 26. Database dismounted. 27. ORACLE instance shut down. 28. SQL> host cp /u01/app/oracle/oradata/backup/system01.dbf /u01/app/oracle/oradata/testdb/system01.dbf 29. system datafile restored from recent backup 30. 31. SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 7 12:51:16 2010 32. 33. Copyright (c) 1982, 2005, Oracle. All rights reserved. 34. 35. Enter user-name: sys as sysdba 36. Enter password: 37. Connected to an idle instance. 38. 39. SQL> startup mount 40. ORACLE instance started. 41. 42. Total System Global Area 444596224 bytes 43. Fixed Size 1219904 bytes 44. Variable Size 138412736 bytes 45. Database Buffers 301989888 bytes 46. Redo Buffers 2973696 bytes 47. Database mounted. 48. SQL> recover datafile 1; 49. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1 50. ORA-00289: suggestion : 51. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc 52. ORA-00280: change 454383 for thread 1 is in sequence #7 53. 54. 55. Specify log: {=suggested | filename | AUTO | CANCEL} 56. auto 57. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1 58. ORA-00289: suggestion : 59. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc 60. ORA-00280: change 456007 for thread 1 is in sequence #8 61. ORA-00278: log file 62. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery 63. . 64. . 65. . 66. ORA-00279: change 456039 generated at 05/07/2010 12:46:22 needed for thread 1 67. ORA-00289: suggestion : 68. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc 69. ORA-00280: change 456039 for thread 1 is in sequence #11 70. ORA-00278: log file 71. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7hl7dr_.arc' no longer needed for this recovery 72. 73. 74. Log applied. 75. Media recovery complete. 76. SQL> alter database open; 77. 78. Database altered. 79. 80. SQL> archive log list; 81. Database log mode Archive Mode 82. Automatic archival Enabled 83. Archive destination USE_DB_RECOVERY_FILE_DEST 84. Oldest online log sequence 12 85. Next log sequence to archive 14 86. Current log sequence 14 87. SQL> select username from dba_users 88. 2 where username='RAJESH'; 89. 90. USERNAME 91. ------------------------------ 92. RAJESH 2.Complete Open Database Recovery. Non system tablespace is missing If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open. Pre requisites: A closed or open database backup and archived logs. 1. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf 2. alter tablespace offline immediate; 3. recover tablespace ; 4. alter tablespace online; workshop2: Non-system datafile recovery from recent backup when database is open view plainprint? 1. SQL> ALTER USER rajesh DEFAULT TABLESPACE users; 2. 3. User altered. 4. 5. SQL> conn rajesh/rajesh; 6. Connected. 7. SQL> create table demo(id number); 8. 9. Table created. 10. 11. SQL> insert into demo values(123); 12. 13. 1 row created. 14. 15. SQL> commit; 16. 17. Commit complete. 18. 19. SQL> select * from demo; 20. 21. ID 22. ---------- 23. 123 24. 25. SQL> conn sys/oracle as sysdba; 26. Connected. 27. SQL> alter system switch logfile; 28. 29. System altered. 30. 31. SQL> / 32. 33. System altered. 34. 35. SQL> archive log list; 36. Database log mode Archive Mode 37. Automatic archival Enabled 38. Archive destination USE_DB_RECOVERY_FILE_DEST 39. Oldest online log sequence 14 40. Next log sequence to archive 16 41. Current log sequence 16 42. i manually deleted the datafile users01.dbf for testing purpose only 43. SQL> conn rajesh/rajesh; 44. Connected. 45. SQL> alter system flush buffer_cache; 46. 47. System altered. 48. 49. SQL> select * from demo; 50. select * from demo 51. * 52. ERROR at line 1: 53. ORA-00376: file 4 cannot be read at this time 54. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf' 55. 56. 57. SQL> conn sys/oracle as sysdba; 58. Connected. 59. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf 60. restore the users01.dbf datafile from recent backup to the testdb folder 61. 62. SQL> alter tablespace users offline immediate; 63. 64. Tablespace altered. 65. 66. SQL> recover tablespace users; 67. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1 68. ORA-00289: suggestion : 69. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc 70. ORA-00280: change 454383 for thread 1 is in sequence #7 71. 72. 73. Specify log: {=suggested | filename | AUTO | CANCEL} 74. auto 75. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1 76. ORA-00289: suggestion : 77. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc 78. ORA-00280: change 456007 for thread 1 is in sequence #8 79. ORA-00278: log file 80. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery 81. ..... 82. ...... 83. ORA-00279: change 456044 generated at 05/07/2010 12:46:28 needed for thread 1 84. ORA-00289: suggestion : 85. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_%u_.arc 86. ORA-00280: change 456044 for thread 1 is in sequence #13 87. ORA-00278: log file 88. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_12_5y7hldl2_.arc' no longer needed for this recovery 89. 90. 91. Log applied. 92. Media recovery complete. 93. SQL> alter tablespace users online; 94. 95. Tablespace altered. 96. 97. SQL> conn rajesh/rajesh; 98. Connected. 99. SQL> select * from demo; 100. 101. ID 102. ---------- 103. 123 3.Complete Open Database Recovery (when the database is initially closed).Non system datafile is missing If a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open. Pre requisites: A closed or open database backup and archived logs. 1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted) 2. alter database datafile3 offline; (tablespace cannot be used because the database is not open) 3. alter database open; 4. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf 5. recover datafile 3; 6. alter tablespace online; workshop 3:Non system datafile is missing view plainprint? 1. SQL> conn sys/oracle as sysdba; 2. Connected. 3. SQL> alter system switch logfile; 4. 5. System altered. 6. 7. SQL> select username,default_tablespace from dba_users 8. 2 where username='RAJESH'; 9. 10. USERNAME DEFAULT_TABLESPACE 11. ------------------------------ ------------------------------ 12. RAJESH USERS 13. 14. SQL> conn rajesh/rajesh; 15. Connected. 16. SQL> create table testtbl (id number); 17. 18. Table created. 19. 20. SQL> insert into testtbl values(786); 21. 22. 1 row created. 23. 24. SQL> commit; 25. 26. Commit complete. 27. 28. SQL> select * from testtbl; 29. 30. ID 31. ---------- 32. 786 33. 34. SQL> conn sys/oracle as sysdba; 35. Connected. 36. SQL> shutdown immediate; 37. Database closed. 38. Database dismounted. 39. ORACLE instance shut down. 40. SQL> --manually deleting the users01.dbf datafile from testdb folder 41. warning:for testing purpose only 42. SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf 43. 44. SQL> startup 45. ORACLE instance started. 46. 47. Total System Global Area 444596224 bytes 48. Fixed Size 1219904 bytes 49. Variable Size 142607040 bytes 50. Database Buffers 297795584 bytes 51. Redo Buffers 2973696 bytes 52. Database mounted. 53. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file 54. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf' 55. 56. 57. SQL> alter database datafile 4 offline; 58. 59. Database altered. 60. 61. SQL> alter database open; 62. 63. Database altered. 64. 65. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u01/app/oracle/oradata/testdb/users01.dbf 66. copying user01.dbf from the recent backup to the testdb folder 67. SQL> recover datafile 4; 68. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1 69. ORA-00289: suggestion : 70. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc 71. ORA-00280: change 454383 for thread 1 is in sequence #7 72. 73. 74. Specify log: { =suggested | filename | AUTO | CANCEL} auto 75. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1 76. ORA-00289: suggestion : 77. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc 78. ORA-00280: change 456007 for thread 1 is in sequence #8 79. ORA-00278: log file 80. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery 81. ...... 82. ......... 83. ORA-00279: change 456046 generated at 05/07/2010 12:46:29 needed for thread 1 84. ORA-00289: suggestion : 85. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_%u_.arc 86. ORA-00280: change 456046 for thread 1 is in sequence #14 87. ORA-00278: log file 88. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_13_5y7hlfbc_.arc' no longer needed for this recovery 89. 90. 91. Log applied. 92. Media recovery complete. 93. SQL> alter database datafile 4 online; 94. 95. Database altered. 96. 97. SQL> conn rajesh/rajesh; 98. Connected. 99. SQL> select * from testtbl; 100. 101. ID 102. ---------- 103. 786 4.Recovery of a Missing Datafile that has no backups (database is open). If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Pre requisites: All relevant archived logs. 1. alter tablespace offline immediate; 2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf'; 3. recover tablespace ; 4. alter tablespace online; If the create datafile command needs to be executed to place the datafile on a location different than the original use: alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as '/user/oradata/u02/dbtst/newdata01.dbf' restriction: datafile should be created after controlfile creation.(i.e,controlfile creation time is prior than datafile creation time). workshop 4: Missing Non-system Datafile having no backups view plainprint? 1. SQL> alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss'; 2. 3. Session altered. 4. 5. SQL> select controlfile_created from v$database; 6. 7. CONTROLFILE_CREATED 8. -------------------- 9. 07-MAY-2010 16:27:22 10. 11. SQL> col name format a45 12. SQL> select creation_time,name from v$datafile; 13. 14. CREATION_TIME NAME 15. -------------------- --------------------------------------------- 16. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 17. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 18. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 19. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf 20. you cannot re-create the any one of the listed above datafile , without backup. 21. SQL> create tablespace testing datafile 22. 2 '/u01/app/oracle/oradata/testdb/test01.dbf' size 2m; 23. 24. Tablespace created. 25. 26. SQL> select creation_time,name from v$datafile; 27. 28. CREATION_TIME NAME 29. -------------------- --------------------------------------------- 30. 30-JUN-2005 19:10:11 /u01/app/oracle/oradata/testdb/system01.dbf 31. 30-JUN-2005 19:55:01 /u01/app/oracle/oradata/testdb/undotbs01.dbf 32. 30-JUN-2005 19:10:27 /u01/app/oracle/oradata/testdb/sysaux01.dbf 33. 30-JUN-2005 19:10:40 /u01/app/oracle/oradata/testdb/users01.dbf 34. 07-MAY-2010 16:32:07 /u01/app/oracle/oradata/testdb/test01.dbf 35. we can re-create test01.dbf file without backup. 36. SQL> select controlfile_created from v$database; 37. 38. CONTROLFILE_CREATED 39. -------------------- 40. 07-MAY-2010 16:27:22 41. 42. ---we can recover the datafile test01.dbf without backup using view plainprint? 1. create datafile command in recovery 2. ---in this example i am going to create a table in testing tablespace view plainprint? 1. and deleted the test01.dbf datafile and recover it without backup and view plainprint? 1. create datafile recovery command. 2. 3. SQL> create user jay identified by jay 4. 2 default tablespace testing; 5. 6. User created. 7. 8. SQL> grant dba to jay; 9. 10. Grant succeeded. 11. 12. SQL> select username,default_tablespace from dba_users 13. 2 where username='JAY'; 14. 15. USERNAME DEFAULT_TABLESPACE 16. ------------------------------ ------------------------------ 17. JAY TESTING 18. 19. SQL> conn jay/jay; 20. Connected. 21. SQL> create table demo (id number); 22. 23. Table created. 24. 25. SQL> insert into demo values(321); 26. 27. 1 row created. 28. 29. SQL> commit; 30. 31. Commit complete. 32. 33. SQL> select * from demo; 34. 35. ID 36. ---------- 37. 321 38. 39. SQL> conn sys/oracle as sysdba; 40. Connected. 41. SQL> host rm -rf /u01/app/oracle/oradata/testdb/test01.dbf 42. ---manually deleting datafile test01.dbf for testing purpose 43. 44. SQL> conn jay/jay; 45. Connected. 46. SQL> select * from demo; 47. 48. ID 49. ---------- 50. 321 51. 52. SQL> alter system flush buffer_cache; 53. 54. System altered. 55. 56. SQL> select * from demo; 57. select * from demo 58. * 59. ERROR at line 1: 60. ORA-01116: error in opening database file 5 61. ORA-01110: data file 5: '/u01/app/oracle/oradata/testdb/test01.dbf' 62. ORA-27041: unable to open file 63. Linux Error: 2: No such file or directory 64. Additional information: 3 65. 66. 67. SQL> alter database datafile 5 offline; 68. 69. Database altered. 70. ----TO CREATE A NEW RECOVERED DATAFILE IN SAME LOCATION. 71. SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf'; 72. Database altered. 73. ----TO CREATE A NEW RECOVERED DATAFILE IN DIFFERENT LOCATION. 74. SQL> alter database create datafile '/u01/app/oracle/oradata/testdb/test01.dbf' as '/u03/oradata/test01.dbf'; 75. 76. Database altered. 77. 78. SQL> recover datafile 5; 79. ORA-00279: change 454443 generated at 05/07/2010 16:32:07 needed for thread 1 80. ORA-00289: suggestion : 81. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc 82. ORA-00280: change 454443 for thread 1 is in sequence #8 83. 84. 85. Specify log: {=suggested | filename | AUTO | CANCEL} 86. auto 87. ORA-00279: change 454869 generated at 05/07/2010 16:41:38 needed for thread 1 88. ORA-00289: suggestion : 89. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_9_%u_.arc 90. ORA-00280: change 454869 for thread 1 is in sequence #9 91. ORA-00278: log file 92. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_5y7xcbrm_.arc' no longer needed for this recovery 93. ..... 94. ....... 95. ORA-00279: change 454874 generated at 05/07/2010 16:41:45 needed for thread 1 96. ORA-00289: suggestion : 97. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_11_%u_.arc 98. ORA-00280: change 454874 for thread 1 is in sequence #11 99. ORA-00278: log file 100. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_10_5y7xck8j_.arc' no longer needed for this recovery 101. 102. 103. Log applied. 104. Media recovery complete. 105. SQL> alter database datafile 5 online; 106. 107. Database altered. 108. 109. SQL> conn jay/jay; 110. Connected. 111. SQL> select * from demo; 112. 113. ID 114. ---------- 115. 321 116. 117. SQL> 5.Restore and Recovery of a Datafile to a different location. If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed. Pre requisites: All relevant archived logs. 1. Use OS commands to restore the missing or corrupted datafile to the new location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf 2. alter tablespace offline immediate; 3. alter tablespace rename datafile '/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf'; 4. recover tablespace ; 5. alter tablespace online; workshop 5: view plainprint? 1. SQL> create user lachu identified by lachu 2. 2 default tablespace users; 3. 4. User created. 5. 6. SQL> grant dba to lachu; 7. 8. Grant succeeded. 9. 10. SQL> conn lachu/lachu; 11. Connected. 12. SQL> create table test_tb(id number); 13. 14. Table created. 15. 16. SQL> insert into test_tb values(123); 17. 18. 1 row created. 19. 20. SQL> commit; 21. 22. Commit complete. 23. 24. SQL> conn sys/oracle as sysdba; 25. Connected. 26. SQL> ---manually deleting users01.dbf datafile for testing purpose 27. SQL> host rm -rf '/u01/app/oracle/oradata/testdb/users01.dbf' 28. 29. SQL> conn lachu/lachu; 30. Connected. 31. SQL> select * from tab; 32. 33. TNAME TABTYPE CLUSTERID 34. ------------------------------ ------- ---------- 35. TEST_TB TABLE 36. SQL> select * from test_tb; 37. select * from test_tb 38. * 39. ERROR at line 1: 40. ORA-00376: file 4 cannot be read at this time 41. ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf' 42. 43. 44. SQL> conn sys/oracle as sysdba; 45. Connected. 46. SQL> alter database datafile 4 offline; 47. 48. Database altered. 49. 50. SQL> host cp -p /u01/app/oracle/oradata/backup/users01.dbf /u03/oradata/users01.dbf 51. --restore datafile user01.dbf to new disk from the recent backup of the database. 52. 53. SQL> alter tablespace users rename datafile 54. 2 '/u01/app/oracle/oradata/testdb/users01.dbf' to '/u03/oradata/users01.dbf'; 55. Tablespace altered. 56. 57. SQL> recover datafile 4; 58. ORA-00279: change 454383 generated at 05/07/2010 01:40:11 needed for thread 1 59. ORA-00289: suggestion : 60. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_%u_.arc 61. ORA-00280: change 454383 for thread 1 is in sequence #7 62. 63. 64. Specify log: {=suggested | filename | AUTO | CANCEL} 65. auto 66. ORA-00279: change 456007 generated at 05/07/2010 12:46:10 needed for thread 1 67. ORA-00289: suggestion : 68. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_8_%u_.arc 69. ORA-00280: change 456007 for thread 1 is in sequence #8 70. ORA-00278: log file 71. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_7_5y7hkty0_.arc' no longer needed for this recovery 72. .... 73. ...... 74. ORA-00279: change 457480 generated at 05/07/2010 13:09:30 needed for thread 1 75. ORA-00289: suggestion : 76. /u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_15_%u_.arc 77. ORA-00280: change 457480 for thread 1 is in sequence #15 78. ORA-00278: log file 79. '/u01/app/oracle/flash_recovery_area/TESTDB/archivelog/2010_05_07/o1_mf_1_14_5y7jxlvg_.arc' no longer needed for this recovery 80. 81. 82. Log applied. 83. Media recovery complete. 84. SQL> alter database datafile 4 online; 85. 86. Database altered. 87. 88. SQL> select name from v$datafile; 89. 90. NAME 91. --------------------------------------------- 92. /u01/app/oracle/oradata/testdb/system01.dbf 93. /u01/app/oracle/oradata/testdb/undotbs01.dbf 94. /u01/app/oracle/oradata/testdb/sysaux01.dbf 95. /u03/oradata/users01.dbf ----------restored in new location (disk) 96. 97. SQL> conn lachu/lachu; 98. Connected. 99. SQL> select * from tab; 100. 101. TNAME TABTYPE CLUSTERID 102. ------------------------------ ------- ---------- 103. TEST_TB TABLE 104. 105. SQL> select * from test_tb; 106. 107. ID 108. ---------- 109. 123