移动system本地数据文件到ASM

参考MOS

How to move a SYSTEM datafile from filesystem to the ASM diskgroup using ASMCMD on RAC. (Doc ID 1607292.1)

现象描述

• 1）RAC一节点添加数据文件时，误将文件添加到本地文件系统

alter tablespace SYSTEM add datafile '/dev/shm/systemadd1' size 100m autoextend on next 32m maxsize 1024m;
alter tablespace SYSAUX add datafile '/dev/shm/sysauxadd1' size 100m autoextend on next 32m maxsize 1024m;

[oracle@rac1 shm]$ ll sys*
-rw-r----- 1 oracle asmadmin 104865792 Sep 26 20:07 sysauxadd1
-rw-r----- 1 oracle asmadmin 104865792 Sep 26 20:07 systemadd1

• 2）二节点查询数据文件状态，会报ORA-01157/ORA-01110

SQL> select TABLESPACE_NAME, file_name, file_id, online_status from dba_data_files where TABLESPACE_NAME='SYSTEM';
ERROR:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/dev/shm/systemadd1'



no rows selected

移动本地数据文件到ASM

• 1）停止一节点实例

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

• 2）使用ASMCMD cp 命令移动文件

[grid@rac1 ~]$ asmcmd
ASMCMD> cp /dev/shm/systemadd1 +DATA/orcl/datafile/system2
copying /dev/shm/systemadd1 -> +DATA/orcl/datafile/system2
ASMCMD> cp /dev/shm/sysauxadd1 +DATA/orcl/datafile/sysaux2
copying /dev/shm/sysauxadd1 -> +DATA/orcl/datafile/sysaux2
ASMCMD> cd +DATA/orcl/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
                                            N    system2 => +DATA/ASM/DATAFILE/system2.276.1084306471
                                            N    sysaux2 => +DATA/ASM/DATAFILE/sysaux2.277.1084306505
DATAFILE  UNPROT  COARSE   SEP 26 20:00:00  Y    USERS.267.1084304205
DATAFILE  UNPROT  COARSE   SEP 26 20:00:00  Y    UNDOTBS2.272.1084305053
DATAFILE  UNPROT  COARSE   SEP 26 20:00:00  Y    UNDOTBS1.266.1084304205
DATAFILE  UNPROT  COARSE   SEP 26 20:00:00  Y    SYSTEM.264.1084304203
DATAFILE  UNPROT  COARSE   SEP 26 20:00:00  Y    SYSAUX.265.1084304205

• 3）将实例启动到mount阶段

SQL> startup mount
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size          2255832 bytes
Variable Size         297796648 bytes
Database Buffers      322961408 bytes
Redo Buffers            3313664 bytes
Database mounted.

• 4）rename数据文件

SQL> alter database rename file '/dev/shm/systemadd1' to '+DATA/orcl/datafile/system2';

Database altered.

SQL> alter database rename file '/dev/shm/sysauxadd1' to '+DATA/orcl/datafile/sysaux2';

Database altered.

• 5）打开一节点数据库实例

SQL> alter database open;

Database altered.
SQL> select TABLESPACE_NAME, file_name, file_id from dba_data_files where file_id in(6,7);

TABLESPACE_NAME      FILE_NAME                         FILE_ID
-------------------- ------------------------------ ----------
SYSTEM               +DATA/orcl/datafile/system2             6
SYSAUX               +DATA/orcl/datafile/sysaux2             7

• 6）重启二节点数据库实例

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size          2255832 bytes
Variable Size         297796648 bytes
Database Buffers      322961408 bytes
Redo Buffers            3313664 bytes
Database mounted.
Database opened.