RE: Accidentally created local df in RAC
Date: Fri, 9 Mar 2012 23:00:51 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB44556E1C7_at_LITIGMBCRP02.Corp.Acxiom.net>
Thanks Jon. It can get very lonely (justified or not) when you're part of the base problem.
Although the basic outline I gave previously works, it assumes that your redo logs haven't been overwritten. I created a test db and ran through those steps and it worked fine. Of course the actual problem situation involved a db that had switched it's redo logs long before I got to this solution, so there's really no way to recover. But, I have yet to figure out a situation where I'd really need to. Since the datafile is offline and the database is in noarchivelog mode, no online backup operations will raise a red flag. As long as we leave the datafile asis, there's nothing to worry about (as far as I can tell).
In case anyone cares, here's a test of this situation on a 2-node RAC (imaginatively named "dchtest").
- From NODE1 sys_at_DCHTEST> CREATE TABLESPACE dch_data DATAFILE '+DBDATA' SIZE 10M EXTENT MANAGEMENT LOCAL ;
Tablespace created.
- Now create a df locally sys_at_DCHTEST> ALTER TABLESPACE dch_data ADD DATAFILE 'DBDATA' SIZE 10M;
Tablespace altered.
- From NODE2 sys_at_DCHTEST> SELECT file_name FROM dba_data_files; ERROR: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/ora01/app/oracle/product/10.2.0/db_1/dbs/DBDATA'
- From NODE1 sys_at_DCHTEST> ALTER DATABASE DATAFILE '/ora01/app/oracle/product/10.2.0/db_1/dbs/DBDATA' OFFLINE FOR DROP;
Database altered.
SELECT thread#, sequence#, archived, status FROM v$log ORDER BY thread#, sequence#;
THREAD# SEQUENCE# ARC STATUS
------------------ ------------------ --- ----------------
1 5 NO INACTIVE
1 6 NO CURRENT
2 2 NO INACTIVE
2 3 NO CURRENT
- (the following was run once per log group, per node) sys_at_DCHTEST> ALTER SYSTEM SWITCH LOGFILE;
sys_at_DCHTEST> SELECT thread#, sequence#, archived, status FROM v$log ORDER BY thread#, sequence#;
THREAD# SEQUENCE# ARC STATUS
------------------ ------------------ --- ----------------
1 7 NO INACTIVE
1 8 NO CURRENT
2 4 NO INACTIVE
2 5 NO CURRENT
sys_at_DCHTEST> SELECT * FROM dba_data_files WHERE file_name = '/ora01/app/oracle/product/10.2.0/db_1/dbs/DBDATA';
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT
------------------ ------------------------------ ------------------ ------------------ --------- ------------------ ---
MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------ ------------------ ------------------ ------------------ ------------------ -------
/ora01/app/oracle/product/10.2.0/db_1/dbs/DBDATA
9 DCH_DATA AVAILABLE 9
RECOVER
sys_at_DCHTEST> CREATE DIRECTORY src_dbdata_dir AS '/ora01/app/oracle/product/10.2.0/db_1/dbs'; sys_at_DCHTEST> CREATE DIRECTORY dest_dbdata_dir AS '+DBDATA/dchtest/datafile';
sys_at_DCHTEST> EXEC DBMS_FILE_TRANSFER.COPY_FILE('SRC_DBDATA_DIR', 'DBDATA', 'DEST_DBDATA_DIR', 'dch_data'); sys_at_DCHTEST> ALTER DATABASE RENAME FILE '/ora01/app/oracle/product/10.2.0/db_1/dbs/DBDATA' TO '+DBDATA/dchtest/datafile/dch_data';
% srvctl stop database -d dchtest
sys_at_DCHTEST> STARTUP MOUNT;
sys_at_DCHTEST> RECOVER DATAFILE '+DBDATA/dchtest/datafile/dch_data';
ORA-00279: change 586801 generated at 03/08/2012 11:02:22 needed for thread 1 ORA-00289: suggestion : /ora01/app/oracle/product/10.2.0/db_1/dbs/arch1_6_777324831.dbf ORA-00280: change 586801 for thread 1 is in sequence #6
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
-----Original Message-----
From: CRISLER, JON A [mailto:JC1706_at_att.com]
Sent: Friday, March 09, 2012 12:42 PM
To: Herring Dave - dherri; rodrigo_at_mufalani.com.br
Cc: Lei Zeng; oracle-l_at_freelists.org
Subject: RE: Accidentally created local df in RAC
Dave-I feel your pain but this is a very interesting issue- please let us know when you get a final fix.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave - dherri
Sent: Thursday, March 08, 2012 9:09 AM
To: rodrigo_at_mufalani.com.br
Cc: Lei Zeng; oracle-l_at_freelists.org
Subject: RE: Accidentally created local df in RAC
That's the solution Oracle gave but unfortunately the database is in noarchivelog mode so it doesn't work. If I was given the opportunity to restart the db I'd then do the following:
- Use DBMS_FILE_TRANSFER to copy the file to ASM.
- Rename the datafile.
- Shutdown the db then startup mount.
- Recover the datafile, then online it.
- Open the db.
I believe that since the db is in noarchivelog, having this offline'ed datafile in an online state of RECOVER doesn't affect anything. In a way the noarchivelog mode is a hinderance and a blessing, in that no online backup operations can be done so Oracle won't complain about this datafile. Once a week BCV backups are done which require a shutdown so I may try to work with that to clean things up. But again, if I leave things as is there shouldn't be any issues either.
It's been a learning experience as I don't believe I've ever had to deal with ASM + RAC + >8i + a db in noarchivelog mode.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM<http://www.acxiom.com/>
[Friend Us on Facebook]<http://www.facebook.com/acxiomcorp> [Link Us on LinkedIn] <http://www.linkedin.com/groupRegistration?gid)01735> [Follow Us on Twitter] <http://twitter.com/acxiom>
[cid:image004.png_at_01CB84F1.26214350]
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
From: Rodrigo Mufalani [mailto:rodrigo_at_mufalani.com.br]
Sent: Thursday, March 08, 2012 7:55 AM
To: Herring Dave - dherri
Cc: Lei Zeng; oracle-l_at_freelists.org
Subject: RE: Accidentally created local df in RAC
Hi David,
Have you considered use command copy from RMAN to copy the datafile offline to ASM?
RMAN> copy datafile '/some/local/path/dt01.dbf' to '+DATA01';
Then online it.
Best Regards,
Rodrigo Mufalani
Oracle Ace Member
Tel.: +55 21 88514817
http://www.mufalani.com.br
[http://mufalani.com.br/site/wp-content/uploads/2012/01/logo.png]
Lei,
Unfortunately you can't drop a datafile that's offline when you use LMTs. So I'll have to online the datafile to drop
it, but to online it I'll have to recover and since it's noarchive mode I have nothing to recover with.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM<http://www.acxiom.com/>
[Friend Us on Facebook]<http://www.facebook.com/acxiomcorp> [Link Us on LinkedIn] <http://www.linkedin.com/groupRegistration?gid)01735> [Follow Us on Twitter] <http://twitter.com/acxiom>
[cid:image004.png_at_01CB84F1.26214350]
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
From: Lei Zeng [mailto:lei.zeng_at_yahoo.com] Sent: Wednesday, March 07, 2012 11:13 PM To: Herring Dave - dherri; oracle-l_at_freelists.org Subject: Re: Accidentally created local df in RAC
Oracle has a 'drop' datafile command since 10gR2 (if the datafile is empty file) I never tried myself but I saw it in other people's blog.
Would you please test it out on a development box first? Just be cautious.
alter tablespace <tb name> drop datafile '<file_name> ';
Lei
DBspeed http://dbspeed.com/case_study.html
From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Sent: Wednesday, March 7, 2012 3:35 PM
Subject: Accidentally created local df in RAC
I've got a potential resume-faxing situation in dealing with a 20+ TB db. This is on a 6-node RAC, Oracle 10.2.0.4, RHEL 4.x, ASM, OMF.
I added a datafile to a tablespace on node1 and unfortunately forgot the plus sign in front of the diskgroup. We use OMF so what happened is instead of a datafile getting added to ASM under <diskgroup>/<db>... it created one on node1 under $ORACLE_HOME/dbs. Of course the other 5 nodes started complaining about ORA-01157 errors. I saw this right away and quickly offlined the datafile. Now the datafile has an ONLINE_STATUS of "RECOVER".
Unfortunately the database is in noarchivelog mode and also critical production (odd combo, I know), so I'm paranoid on trying anything further. My thought is to use DBMS_FILE_TRANSFER to put the datafile in ASM, then do a rename and bring it online. Is that the best method? I'm pretty sure nothing got written to the datafile as DBA_EXTENTS shows no extents in that FILE_ID.
Thoughts?
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com<mailto:dave.herring_at_acxiom.com>
TEL 630.944.4762
MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM<http://www.acxiom.com/>
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 09 2012 - 17:00:51 CST