RAC环境下创建本地数据文件的解决方法

同事不小心，在RAC环境下创建了本地数据文件，这个肯定会出问题的，节点2不能访问此数据文件。其实发现做错了，立马删掉应该没有问题。数据文件还没有数据。
下面演示一下错误的解决方法，思路就是，把本地文件COPY到共享磁盘中（ASM），在rename即可。

点击(此处)折叠或打开

1. SQL> alter tablespace USERS add datafile '/home/oracle/user01.dbf' size 10m;//创建错误过程
   
2. 
   
3. Tablespace altered.
   
4. 
   
5. SQL> alter database datafile 8 offline;
   
6. 
   
7. Database altered.
   
8. 
   
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 /
    
17. 
    
18. Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 18 16:17:54 2014
    
19. 
    
20. Copyright (c) 1982, 2007, Oracle. All rights reserved.
    
21. 
    
22. 
    
23. connected to target database: QT0000J2 (DBID=3109660417)
    
24. 
    
25. RMAN>
    
26. RMAN>
    
27. 
    
28. RMAN>
    
29. 
    
30. RMAN>
    
31. 
    
32. RMAN> copy datafile '/home/oracle/user01.dbf' to '+RACDG';
    
33. 
    
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
    
43. 
    
44. RMAN> exit
    
45. 
    
46. 
    
47. Recovery Manager complete.
    
48. oracle@zbdb1:~/product/10.2.0/db_1/bin> sqlplus / as sysdba;
    
49. 
    
50. SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 18 16:19:02 2014
    
51. 
    
52. Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
    
53. 
    
54. 
    
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
    
59. 
    
60. SQL>
    
61. SQL>
    
62. SQL> alter database rename file '/home/oracle/user01.dbf' to '+RACDG/qt0000j2/datafile/users.270.839866683';
    
63. 
    
64. Database altered.
    
65. 
    
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
    
74. 
    
75. 
    
76. SQL> select name from v$datafile;
    
77. 
    
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
    
88. 
    
89. 8 rows selected.
    
90. 
    
91. SQL> select file_name,file_id,ONLINE_STATUS from dba_data_files;
    
92. 
    
93. +RACDG/qt0000j2/datafile/users.270.839866683     8 RECOVER
    
94. 
    
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
     
101. 
     
102. 
     
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
     
109. 
     
110.  SQL> alter database datafile \'+RACDG/qt0000j2/datafile/users.270.839866683\' online;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/501889/viewspace-1083311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/501889/viewspace-1083311/