recovery using online redo logs for a norarchivelog database

after the platform services did the san migration somehow one of the datafile became corrupted and while i try to open the database it complains. this database is a norachivelog database . so I just felt lucky on how it worked . the platform services had taken a o/s backup while the database was running the around 1am before they did the san migration.

Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01201: file 7 header failed to write correctly

so at this point I asked them to restore the datafile 7 that they took from last night . even though this waas a production database but there were no activity during the night on this weekend .

so once they restored the file . I mounted

SQL> recover database using backup controlfile;
ORA-00279: change 2213499829905 generated at 02/21/2009 08:43:13 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10088_%u_
.arc
ORA-00280: change 2213499829905 for thread 1 is in sequence #10088


Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo02.log
ORA-00279: change 2213502398486 generated at 02/21/2009 22:07:33 needed for
thread 1
ORA-00289: suggestion :
/rgw/ora_bkup/flash_recovery_area/RGWP20/archivelog/2009_02_22/o1_mf_1_10089_%u_
.arc
ORA-00280: change 2213502398486 for thread 1 is in sequence #10089
ORA-00278: log file '/rgw/u2/oradata/rgwp20/redo02.log' no longer needed for
this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/rgw/u2/oradata/rgwp20/redo03.log
Log applied.
Media recovery complete.

--what a relief it was for me . because if it would have any issue further then probably i would have to go through a export restore . then I did

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> select * from v$recover_file;

no rows selected

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10087
Current log sequence 10089
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
0

SQL> alter database open resetlogs;

Database altered.

the database is back up and i am a happy man.

**** alert log .
alter database open resetlogs
Sun Feb 22 13:02:00 2009
RESETLOGS after complete recovery through change 2213530385545
Resetting resetlogs activation ID 1283279895 (0x4c7d4c17)
Sun Feb 22 13:02:05 2009
Setting recovery target incarnation to 2
Sun Feb 22 13:02:05 2009
Assigning activation ID 1331518248 (0x4f5d5b28)

***

but even though this production database was running in noarchivelog but still this database is a very highly visible database so I just did few homeowrk to make sure that everything is fine .

1-
SQL> begin
for r in( select owner,table_name from dba_tables where owner='RGWP20') loop
dbms_output.put_line( r.table_name );
dbms_stats.gather_table_stats( r.owner, r.table_name, estimate_percent => 5,
degree => 16, granularity => 'ALL', cascade => true,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1' );
end loop;
end; 2 3 4 5 6 7 8
9 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


2#took a export backup and compare the the backup before and now for
the tables /records

my lesson- a production database should always be in archivelog if you want life to be less stressful. but it is hard here to convince the application manager that we should convert it to archivelog. but will try again.