I am testing some RMAN backup and restore scenarios and have found something that I can't quite work out. The one I am having trouble with a scenario whereby a Unix Admin accidently renames the wrong raw volume. I have created a database on raw volumes. Each datafile points to its corresponding raw volume via a symbolic link (e.g. waitec_tbs001.dbf > /dev/vx/rdsk/tclora_dg/TPRCJW_waitec_tbs001). I have then created a table called TEST into tablespace WAITEC_TBS and inserted a couple of rows:
Code:SQL*Plus: Release 9.2.0.7.0 - Production on Fri Oct 20 14:36:31 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> create table test(id number) tablespace waitec_tbs; Table created. SQL> insert into test values(1); 1 row created. SQL> insert into test values(2); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID ---------- 1 2 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production
I have then asked my Unix Admin to rename the raw volume from TPRCJW_waitec_tbs001 to TPRCJW_waitec_tbs002. Remember, the symbolic link still points to TPRCJW_waitec_tbs001. From here I expect that I will be able to insert more rows as they will be inserted in memory:
Code:tclwmoc2-oracle> sqlplus / SQL*Plus: Release 9.2.0.7.0 - Production on Fri Oct 20 14:41:10 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> select * from test; ID ---------- 1 2 SQL> insert into test values(3); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID ---------- 1 2 3 SQL> select file_name, status from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/system001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo1001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo2001.dbf AVAILABLE FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/users001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/tools001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/waitec_tbs001.dbf AVAILABLE 6 rows selected. SQL> select * from v$recover_file; no rows selected SQL> select file#, status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 6 rows selected.
So far so good. Now, I expect that if I issue an "ALTER SYSTEM CHECKPOINT" (to flush dirty blocks and write the latest SCN in the datafile headers), Oracle should recognize that it can't find the datafile for my tablespace (WAITEC_TBS) and should then mark the datafile (number 6) as needing recovery:
Nothing doing as you can see. Now, of course as soon as I try to change the state of the tablespace (by running begin backup or even a shutdown startup) it will complain:Code:-- Four redo log groups SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. SQL> select file#, status from v$datafile; FILE# STATUS ---------- ------- 1 SYSTEM 2 ONLINE 3 ONLINE 4 ONLINE 5 ONLINE 6 ONLINE 6 rows selected. SQL> select file_name, status from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/system001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo1001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo2001.dbf AVAILABLE FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/users001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/tools001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/waitec_tbs001.dbf AVAILABLE 6 rows selected. SQL> select * from v$recover_file; no rows selected SQL> insert into test values(4); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID ---------- 1 2 3 4 SQL> alter system checkpoint; System altered. SQL> SQL> SQL> select * from v$recover_file; no rows selected SQL> select file_name, status from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/system001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo1001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/undo2001.dbf AVAILABLE FILE_NAME -------------------------------------------------------------------------------- STATUS --------- /global/archtclora1_dg/u02/oradata/TPRCJW/users001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/tools001.dbf AVAILABLE /global/archtclora1_dg/u02/oradata/TPRCJW/waitec_tbs001.dbf AVAILABLE
What I don't understand is, in a real life scenario where you perhaps lose connection to a datafile for whatever reason, you'd think that you'd want to know about it before you start a backup or shutdown the database (we only do that once in a blue moon).Code:SQL> alter tablespace waitec_tbs begin backup; alter tablespace waitec_tbs begin backup * ERROR at line 1: ORA-01116: error in opening database file 6 ORA-01110: data file 6: '/global/archtclora1_dg/u02/oradata/TPRCJW/waitec_tbs001.dbf' ORA-27041: unable to open file SVR4 Error: 2: No such file or directory Additional information: 3
So my question is WHY? Surely the datafile should be marked as UNAVAILABLE or something?...
I assume that my test is fundamentally flawed in some way, and i'd be happy if you could point that out (if thats the case).
Obviously, this question isn't RMAN related, or even recovery related as such (I haven't even go that far yet!!), but before you even connect to RMAN, you'd think that you'd need to have a reason to in the first place!! In other words, if the DBA doesn't think anything is wrong, why would he be doing anything?!
If it makes a difference, we are running Solaris 8 and Veritas Volume Manager (but then you could probably see that from the naming convention...)
Reply With Quote