移动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.
复制