RAC環境下建立本地資料檔案的解決方法

charmesp發表於2014-02-18
同事不小心，在RAC環境下建立了本地資料檔案，這個肯定會出問題的，節點2不能訪問此資料檔案。其實發現做錯了，立馬刪掉應該沒有問題。資料檔案還沒有資料。
下面演示一下錯誤的解決方法，思路就是，把本地檔案COPY到共享磁碟中（ASM），在rename即可。

點選(此處)摺疊或開啟

  1. SQL> alter tablespace USERS add datafile '/home/oracle/user01.dbf' size 10m;//建立錯誤過程

  3. Tablespace altered.

  5. SQL> alter database datafile 8 offline;

  7. Database altered.

  9. SQL>
  10. SQL>
  11. SQL>
  12. SQL> exit
  13. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  14. With the Partitioning, Real Application Clusters, OLAP, Data Mining
  15. and Real Application Testing options
  16. oracle@zbdb1:~/product/10.2.0/db_1/bin> rman target /

  18. Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 18 16:17:54 2014

  20. Copyright (c) 1982, 2007, Oracle. All rights reserved.


  23. connected to target database: QT0000J2 (DBID=3109660417)

  25. RMAN>
  26. RMAN>

  28. RMAN>

  30. RMAN>

  32. RMAN> copy datafile '/home/oracle/user01.dbf' to '+RACDG';

  34. Starting backup at 18-FEB-14
  35. using target database control file instead of recovery catalog
  36. allocated channel: ORA_DISK_1
  37. channel ORA_DISK_1: sid=1060 instance=qt0000j21 devtype=DISK
  38. channel ORA_DISK_1: starting datafile copy
  39. input datafile fno=00008 name=/home/oracle/user01.dbf
  40. output filename=+RACDG/qt0000j2/datafile/users.270.839866683 tag=TAG20140218T161802 recid=1 stamp=839866682
  41. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
  42. Finished backup at 18-FEB-14

  44. RMAN> exit


  47. Recovery Manager complete.
  48. oracle@zbdb1:~/product/10.2.0/db_1/bin> sqlplus / as sysdba;

  50. SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 18 16:19:02 2014

  52. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


  55. Connected to:
  56. Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  57. With the Partitioning, Real Application Clusters, OLAP, Data Mining
  58. and Real Application Testing options

  60. SQL>
  61. SQL>
  62. SQL> alter database rename file '/home/oracle/user01.dbf' to '+RACDG/qt0000j2/datafile/users.270.839866683';

  64. Database altered.

  66. SQL>
  67. SQL>
  68. SQL>
  69. SQL> select name from v$datafiles;
  70. select name from v$datafiles
  71.                  *
  72. ERROR at line 1:
  73. ORA-00942: table or view does not exist


  76. SQL> select name from v$datafile;

  78. NAME
  79. --------------------------------------------------------------------------------
  80. +RACDG/qt0000j2/datafile/system.259.730655367
  81. +RACDG/qt0000j2/datafile/undotbs1.260.730655371
  82. +RACDG/qt0000j2/datafile/sysaux.261.730655371
  83. +RACDG/qt0000j2/datafile/undotbs2.263.730655377
  84. +RACDG/qt0000j2/datafile/users.264.730655377
  85. +RACDG/qt0000j2/datafile/ciimss_ts.268.733087301
  86. +RACDG/qt0000j2/datafile/ciimss_idx_ts.269.733087365
  87. +RACDG/qt0000j2/datafile/users.270.839866683

  89. 8 rows selected.

  91. SQL> select file_name,file_id,ONLINE_STATUS from dba_data_files;

  93. +RACDG/qt0000j2/datafile/users.270.839866683     8 RECOVER

  95. SQL> recover datafile 8;
  96. ORA-00279: change 225618407 generated at 02/18/2014 16:17:27 needed for thread
  97. 1
  98. ORA-00289: suggestion :
  99. /home/oracle/product/10.2.0/db_1/dbs/arch1_2667_730655361.dbf
  100. ORA-00280: change 225618407 for thread 1 is in sequence #2667


  103. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  104. auto
  105. Log applied.
  106. Media recovery complete.
  107. SQL> select file_name,file_id,ONLINE_STATUS from dba_data_files;
  108. +RACDG/qt0000j2/datafile/users.270.839866683     8 OFFLINE

  110.  SQL> alter database datafile \'+RACDG/qt0000j2/datafile/users.270.839866683\' online;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/501889/viewspace-1083311/，如需轉載，請註明出處，否則將追究法律責任。

相關文章