xming

a free alternative for exceed.

cygwin installation

mirrors.kernel.org(http) . make sure vim is checked.

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.

controlfile recovery for a db having few readonly tablespaces

here is the situation- The platform services took a cold backup of a UAT database before 3 weeks. and now the application group want that backup to be restored to the UAT . so I asked the platform services to go ahead and overwrite the existing UAT db related filesystems.

after they restored the cold backup I try to open the database and it complains . so I had to list out all the datafiles(including the readonly datafiles( though I dont know which datafiles are readonly from these 250+ datafiles )) . and recreate the controlfile . then i did a recover database using backup controlfile . and somehow it asks for the log file so I had to do a recovery using online redo logs . and then i opened the database with resetlogs . after that still confused about the read only tablespaces I did a status check in dba_tablespaces and I found that the data dictionary had all the readonly ts information and even though the controlfile was recreated but still the database was able to indentify properly .

-not that of a big deal but it was a good learning for me .
lesson- never keep the controlfile outside the datafile filesystem. becuase it is a lot of headache when you took a cold backup and then keep scratching your head that why you are not able to use the backup to restore it . always make sure that a cold backup backs up all the files .

The readonly tablespaces hit me back . when the user reported this error.

ORA-00376: file 71 cannot be read at this time

ORA-01111: name for data file 71 is unknown - rename to correct file

ORA-01110: data file 71: '/u1/app/oracle/product/9.2.0/dbs/MISSING00071'

so I had to to just do trial and error

select file_id,file_name from dba_data_files
where file_name like '%MISS%'

select * from v$datafile where status='OFFLINE'

SQL> alter tablespace PDB_IX_DS1 online;
alter tablespace PDB_IX_DS1 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 246 - see DBWR trace file
ORA-01111: name for data file 246 is unknown - rename to correct file
ORA-01110: data file 246: '/u1/app/oracle/product/9.2.0/dbs/MISSING00246'

doing a alter datbase backup controlfile to trace reported these errors

ALTER DATABASE RENAME FILE 'MISSING00227'
# WARNING! This command can not be completed because the file
# needs to be renamed to a real file name.

anyway I guess I was just lucky . somehow able to patch up the missing links. but I will make sure to take a alter database backup controfile to trace for any database specifically with readonly tablespace before I do a cold backup.

-

it seems oradebug dump helps to identify the tablespace/datafile relationship . thanks to Rijaj in oracle list . have to try.