RAC环境中某数据文件（非system表空间）创建在本地，不停机迁移到ASM磁盘中

Datafiles are mistakenly built into the local file system for processing in the RAC environment

The RAC environment has been opened for archiving

• Move the local datafile by created to the shared Disk Group

1)      user add datafile for RAC database in the local node.

Node1:vastdata3
SQL> create tablespace asd datafile'/home/oracle/asd01.dbf' size 10M autoextend on;
Tablespace created.
SQL> col TABLESPACE_NAME for a20
SQL> col FILE_NAME for a50
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
 
FILE_NAME                     FILE_ID  ONLINE_STATUS  TABLESPACE_NAME
---------------------- ------   ---- ---------   --------------------
+DATA/prod/datafile/undotbs1.258.1001122667      3 ONLINE           UNDOTBS1
+DATA/prod/datafile/users.259.1001073567        4 ONLINE           USERS
+DATA/prod/datafile/sysaux.257.1001073565       2 ONLINE           SYSAUX
+DATA/prod/datafile/system.256.1001073565       1 SYSTEM           SYSTEM
+DATA/prod/datafile/example.264.1001073679       5 ONLINE           EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909      6 ONLINE           UNDOTBS2
/home/oracle/asd01.dbf                 7 ONLINE           ASD
7 rows selected.

2)      Node 2 finds an error while viewing the DBA_DATA_FILES data file.

Node2:vastdata4
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/home/oracle/asd01.dbf'
no rows selected

3)      (1)Use RMAN to move the created datafile to the shared Disk Group;

Node1:vastdata3
SQL> alter database datafile 7 offline;
Database altered.
RMAN> copy datafile '/home/oracle/asd01.dbf' to '+DATA';
Starting backup at 2019-02-28 03:13:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=PROD1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/asd01.dbf
output file name=+DATA/prod/datafile/asd.269.1001387639 tag=TAG20190228T031358 RECID=2 STAMP=1001387638
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2019-02-28 03:13:59
RMAN> switch datafile '/home/oracle/asd01.dbf' to copy;
datafile 7 switched to datafile copy "+DATA/prod/datafile/asd.269.1001387639"

      (2)Move the created datafile in ASMCMD mode by copying.

4)      Recover the datafile 7 And let it online.

Node1:vastdata3
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DATA/prod/datafile/asd.269.1001387639'
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database datafile 7 online;
Database altered.
 
Node1:vastdata3
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
 
FILE_NAME                     FILE_ID  ONLINE_STATUS  TABLESPACE_NAME
---------------------- ------   ---- ---------   --------------------
+DATA/prod/datafile/undotbs1.258.1001122667      3 ONLINE           UNDOTBS1
+DATA/prod/datafile/users.259.1001073567        4 ONLINE           USERS
+DATA/prod/datafile/sysaux.257.1001073565       2 ONLINE           SYSAUX
+DATA/prod/datafile/system.256.1001073565       1 SYSTEM           SYSTEM
+DATA/prod/datafile/example.264.1001073679       5 ONLINE           EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909      6 ONLINE           UNDOTBS2
+DATA/prod/datafile/asd.269.1001387639         7 ONLINE           ASD
7 rows selected.
 
[oracle@vastdata3 ~]$ rm asd01.dbf
 
Node2:vastdata4
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
 
FILE_NAME                     FILE_ID  ONLINE_STATUS  TABLESPACE_NAME
---------------------- ------   ---- ---------   --------------------
+DATA/prod/datafile/undotbs1.258.1001122667      3 ONLINE            UNDOTBS1
+DATA/prod/datafile/users.259.1001073567              4 ONLINE           USERS
+DATA/prod/datafile/sysaux.257.1001073565            2 ONLINE           SYSAUX
+DATA/prod/datafile/system.256.1001073565            1 SYSTEM           SYSTEM
+DATA/prod/datafile/example.264.1001073679         5 ONLINE           EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909           6 ONLINE           UNDOTBS2
+DATA/prod/datafile/asd.269.1001387639           7 ONLINE                ASD
7 rows selected.

2)     Move the datafile of shared Disk Group to the local file system.

Node1:vastdata3

1)      user add datafile for RAC database in the shared disk group.

SQL> create tablespace asd datafile'+DATA' size 10M autoextend on;
Tablespace created.
 
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
FILE_NAME                                    FILE_ID ONLINE_STATUS  TABLESPACE_NAME
-------------------------------------    ---------- --------------    ---
+DATA/prod/datafile/undotbs1.258.1001122667      3 ONLINE           UNDOTBS1
+DATA/prod/datafile/users.259.1001073567        4 ONLINE           USERS
+DATA/prod/datafile/sysaux.257.1001073565       2 ONLINE           SYSAUX
+DATA/prod/datafile/system.256.1001073565       1 SYSTEM           SYSTEM
+DATA/prod/datafile/example.264.1001073679       5 ONLINE           EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909      6 ONLINE           UNDOTBS2
+DATA/prod/datafile/asd. 269.1001415229        7 ONLINE           ASD
+DATA/prod/datafile/zxc                                            8 SYSTEM           SYSTEM

2)      Copy the datafile to the local file system in ASMCMD mode

SQL> alter database datafile 7 offline;
Database altered.
 
ASMCMD> pwd
+DATA/PROD/DATAFILE
ASMCMD> ls
ASD.269.1001415229
ASMCMD> cp ASD.269.1001415229 /tmp/asd01.dbf
copying +DATA/PROD/DATAFILE/ASD.269.1001415229 -> /tmp/asd01.dbf
Destinations in cp allow at most one level of directory structure

3)      Change the datafile path And Recovery the datafile

SQL> alter database rename file '+DATA/PROD/DATAFILE/ASD.269.1001415229' to '/tmp/asd01.dbf';
Database altered.
 
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
FILE_NAME                                      FILE_ID ONLINE_STATUS  TABLESPACE_NAME
-------------------------------------------------- ---------- -------------- --------------------
+DATA/prod/datafile/undotbs1.258.1001122667        3 ONLINE        UNDOTBS1
+DATA/prod/datafile/users.259.1001073567          4 ONLINE        USERS
+DATA/prod/datafile/sysaux.257.1001073565         2 ONLINE        SYSAUX
+DATA/prod/datafile/system.256.1001073565         1 SYSTEM        SYSTEM
+DATA/prod/datafile/example.264.1001073679         5 ONLINE        EXAMPLE
+DATA/prod/datafile/undotbs2.265.1001073909        6 ONLINE         UNDOTBS2
/tmp/asd01.dbf                       7 RECOVER         ASD
+DATA/prod/datafile/zxc                  8 SYSTEM        SYSTEM
 
8 rows selected.
 
SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01114: IO error writing block to file 7 (block # 1)
ORA-01110: data file 7: '/tmp/asd01.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
 
[root@vastdata3 ~]# ll /tmp/asd01.dbf
-rw-r----- 1 grid  oinstall 10493952 Feb 28 11:05 /tmp/asd01.dbf
[root@vastdata3 ~]# chown oracle:oinstall /tmp/asd01.dbf
 
SQL> recover datafile 7;
Media recovery complete.
 
SQL> alter database datafile 7 online;
Database altered.

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

转载于:http://blog.itpub.net/69902483/viewspace-2637346/