Oracle RAC 数据文件添加成本存储的解决办法

原创 ByteHouse 2025-02-26

623

摘要：

如何解决 Oracle RAC 数据文件添加成本存储的办法？进行了总结。基本思路就是复制文件到正确的路径，然后修改控制文件。

1.问题描述

在 Oracle RAC 环境中，有时会误将数据文件创建在本地目录，导致其他节点无法访问该数据文件，从而报出 ORA-01157 和 ORA-01110 错误。

2.解决办法

将表空间或数据文件离线，复制文件到asm 磁盘组，对文件或表空间进行恢复，然后将数据文件或者表空间上线。

3.模拟添加数据库文件，指定错误的存储

Oracle rac 的一个节点在加数据文件的时候，加到了本地文件。

SQL> create tablespace test datafile '+DATA' size 10M;

Tablespace created.

SQL> alter tablespace test add datafile '/home/oracle/test1.dbf' size 10M;

Tablespace altered.

SQL> SELECT FILE#, NAME FROM V$DATAFILE;

  FILE# NAME
---------- -------------------------------------------------
      4 +DATA/orcl/datafile/users.257.901333687
      3 +DATA/orcl/datafile/undotbs1.264.901333687
      2 +DATA/orcl/datafile/sysaux.256.901333685
      1 +DATA/orcl/datafile/system.265.901333685
      5 +DATA/orcl/datafile/undotbs2.266.901333943
      6 +DATA/raac/datafile/test.269.1101356315
      7 /home/oracle/test1.dbf

使用 rman 命令 copy datafile

离线表空间

step 1.离线表空间

SQL> alter tablespace test offline;

Tablespace altered.

step 2.离线数据文件

SQL> alter database datafile 7 offline;
 
Database altered.

step 3.使用rman将错误添加的本地数据文件拷贝到asm磁盘组

[oracle@raac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Apr 7 04:29:30 202
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved
connected to target database: RAAC (DBID=2061915051

RMAN> copy datafile '/home/oracle/test1.dbf' to '+data'
Starting backup at 2022-04-07 05:51:0
using target database control file instead of recovery catalo
allocated channel: ORA_DISK_
channel ORA_DISK_1: SID=52 instance=raac1 device type=DIS
channel ORA_DISK_1: starting datafile cop
input datafile file number=00007 name=/home/oracle/test1.db
output file name=+DATA/bigdata/datafile/test.270.1101361871 tag=TAG20220407T055111 RECID=1 STAMP=110136187
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:0
Finished backup at 2022-04-07 05:51:12

step 4.登录磁盘组查看

[grid@raac2 ~]$ asmcmd
ASMCMD> ls
SYSAUX.260.1056189427
SYSTEM.259.1056189423
TEST.269.1101356315
TEST.270.1101361871
UNDOTBS1.261.1056189431
UNDOTBS2.263.1056189443
USERS.264.1056189445

step 5.数据文件重命名

SQL> alter database rename file '/home/oracle/test1.dbf' to '+data/bigdata/datafile/TEST.270.1101361871';

step 6.将表空间上线

SQL> alter tablespace test online;

Tablespace altered.

离线数据文件

查询数据文件路径

SQL> SELECT FILE#, NAME FROM V$DATAFILE;

  FILE# NAME
---------- -------------------------------------------------
      4 +DATA/orcl/datafile/users.257.901333687
      3 +DATA/orcl/datafile/undotbs1.264.901333687
      2 +DATA/orcl/datafile/sysaux.256.901333685
      1 +DATA/orcl/datafile/system.265.901333685
      5 +DATA/orcl/datafile/undotbs2.266.901333943
      6 +DATA/raac/datafile/test.269.1101356315
      7 /home/oracle/test1.dbf

step 1.确定数据文件的位置,错误创建数据文件在哪个节点上
step 2.将数据文件下线

SQL> ALTER DATABASE DATAFILE 7 OFFLINE;

确认数据文件状态

SQL> COL NAME FOR A45
SQL> SET LINESIZE 234
SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

step 3.复制数据文件到共享目录

RMAN> COPY DATAFILE '/home/oracle/test1.dbf' TO '+DATA';

step 4.更改数据文件路径,重命名数据文件

SQL> ALTER DATABASE RENAME FILE '/home/oracle/test1.dbf TO '+DATA/bigdata/datafile/testnew.339.895952649';

确认数据文件状态

SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

step 5.恢复数据文件
恢复数据文件

SQL> RECOVER DATAFILE '+DATA/bigdata/datafile/testnew.339.895952649';

确认数据文件状态

SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

step 6.将数据文件上线
将数据文件上线

SQL> ALTER DATABASE DATAFILE 7 ONLINE;

确认数据文件状态

SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

特殊情况：系统表空间数据文件创建在本地
在节点一上创建系统表空间数据文件

SQL> ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/datafile/system02.dbf' SIZE 10M AUTOEXTEND OFF;

检查数据文件

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;
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

  FILE# NAME
---------- -------------------------------------------------
      4 +DATA/orcl/datafile/users.257.901333687
      3 +DATA/orcl/datafile/undotbs1.264.901333687
      2 +DATA/orcl/datafile/sysaux.256.901333685
      1 +DATA/orcl/datafile/system.265.901333685
      5 +DATA/orcl/datafile/undotbs2.266.901333943
      6 +DATA/raac/datafile/test.269.1101356315
      7 /u01/app/oracle/datafile/system02.dbf

step 1.将系统表空间数据文件离线

SQL> ALTER DATABASE DATAFILE 7 OFFLINE;

注意：系统表空间数据文件无法下线，需要停机时间。

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

step 2.关闭问题节点

SQL> SHUTDOWN IMMEDIATE;

step 3.在另一个节点上检查数据文件状态

SQL> SELECT OPEN_MODE FROM GV$DATABASE;

step 4.使用 ASM 命令将本地文件移动到 ASM 磁盘组

ASMCMD [+data/bigdata/datafile] > CP /u01/app/oracle/datafile/system02.dbf ./
copying /u01/app/oracle/datafile/system02.dbf -> +data/orcl/datafile/system02.dbf

step 5.启动节点一数据库到 mount 状态

SQL> STARTUP MOUNT;

step 6.修改数据文件路径

SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/datafile/system02.dbf' TO '+data/orcl/datafile/system02.dbf';

step 7.启动数据库

SQL> ALTER DATABASE OPEN;

step 8.在另一个节点上检查数据文件状态

SQL> SELECT FILE_NAME, FILE_ID, ONLINE_STATUS, TABLESPACE_NAME FROM DBA_DATA_FILES;

step 9.重启另一个节点

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

step 10.确认数据文件状态

SQL> SELECT FILE_NAME, FILE_ID, ONLINE_STATUS, TABLESPACE_NAME FROM DBA_DATA_FILES;

backup as copy datafile

数据库运行在归档模式，可以利用rman的 “backup as copy datafile xxx format ‘’” 以及 “switch datafile xxx to copy ”，这样来解决是最简单且最不容易误操作的。

模拟误操作添加一个数据文件到本地存储目录下

SQL> SELECT FILE#, NAME FROM V$DATAFILE;

  FILE# NAME
---------- -------------------------------------------------
      4 +DATA/orcl/datafile/users.257.901333687
      3 +DATA/orcl/datafile/undotbs1.264.901333687
      2 +DATA/orcl/datafile/sysaux.256.901333685
      1 +DATA/orcl/datafile/system.265.901333685
      5 +DATA/orcl/datafile/undotbs2.266.901333943
      6 +DATA/raac/datafile/test.269.1101356315
      7 /home/oracle/test1.dbf.

查到这个误操作新增的数据文件号是15.

step 1.RMAN 把 7 号文件备份副本(backup as copy)

RMAN> backup as copy datafile 7 format '+data';

Starting backup at 05-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 instance=newdb11 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=/home/oracle/test1.dbf
output file name=+DATA/bigdata/DATAFILE/test1.279.961935881 tag=TAG20171205T122439 RECID=4 STAMP=961935880
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-DEC-17

Starting Control File and SPFILE Autobackup at 05-DEC-17
piece handle=+FRA/bigdata/AUTOBACKUP/2017_12_05/s_961935881.262.961935883 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-17

注：数据库文件命名有规范时，backup as copy时直接将数据文件名改成规范的名字：

RMAN> backup as copy datafile 7 format ‘+data/NEWDB1/DATAFILE/test1.DBF’;

实际上users02.dbf这种也只是个别名，底层还是OMF命名的文件。

step 2.将15号数据文件离线
可以只将错误的15号数据文件离线，减小影响；

RMAN> alter database datafile 7 offline;

Statement processed

step 3.切换15号数据文件到copy副本

RMAN> switch datafile 7 to copy;
datafile 7 switched to datafile copy "+DATA/bigdata/DATAFILE/test1.279.961935881"

step 4.恢复15号数据文件

RMAN> recover datafile 7;

Starting recover at 05-DEC-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-DEC-17

step 5.将15号数据文件上线

RMAN> alter database datafile 7 online;

dbms_file_transfer

SQL> SELECT FILE#, NAME FROM V$DATAFILE;

  FILE# NAME
---------- -------------------------------------------------
      4 +DATA/orcl/datafile/users.257.901333687
      3 +DATA/orcl/datafile/undotbs1.264.901333687
      2 +DATA/orcl/datafile/sysaux.256.901333685
      1 +DATA/orcl/datafile/system.265.901333685
      5 +DATA/orcl/datafile/undotbs2.266.901333943
      6 +DATA/raac/datafile/test.269.1101356315
      7 /home/oracle/test1.dbf.

step 1.先创建两个目录
— 创建指定为当前加错的数据文件路径

SQL> create directory backup_file as '/u01/app/oracle/product/11.2.0/db_1/dbs/';
Directory created.

— 创建指定为想要放置的共享存储的路径

SQL> create directory backup_asm as '+DATADG/bigdata/datafile/';
Directory created.

step 2.offline数据文件

SQL> alter database datafile 7 offline;
Database altered.

step 3.进行文件传输copy

/*Syntax
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object       IN  VARCHAR2,
source_file_name              IN  VARCHAR2,
destination_directory_object  IN  VARCHAR2,
destination_file_name         IN  VARCHAR2);
*/
SQL> exec dbms_file_transfer.copy_file('backup_file','test1.dbf','backup_asm','test1.dbf');
PL/SQL procedure successfully completed.

step 4.grid进入asmcmd查看一下

ASMCMD> pwd
+DATADG/bigdata/DATAFILE
ASMCMD> ls
test1.dbf

step 5.rename

SQL> alter database rename file '/home/oracle/test1.dbf' to '+DATADG/bigdata/datafile/test1.dbf';
Database altered.

step 6.recover&online

SQL> alter database recover datafile '+DATADG/bigdata/datafile/test1.dbf';
Database altered.
SQL> alter database datafile '+DATADG/bigdata/datafile/test1.dbf' online;
Database altered.

asmcmd 复制文件

step 1.oracle 用户offline表空间数据文件

SQL> alter database datafile '/home/oracle/test1.dbf' offline;
Database altered.

step 2.修改权限

[root@node02 grid]# chown grid:asmadmin test1.dbf
[root@node02 grid]# ls -lrt test1.dbf 
-rw-r----- 1 grid asmadmin 104865792 Jul  5 21:36 test1.dbf

step 3.asmcmd cp 文件到ASM中

[grid@node02 ~]$ id
uid=501(grid) gid=501(oinstall) groups=501(oinstall),504(asmadmin),506(asmdba),507(asmoper)
[grid@node02 ~]$ asmcmd
ASMCMD> cd +DATADG/RAC11G/DATAFILE
ASMCMD> pwd
+DATADG/RAC11G/DATAFILE
ASMCMD> ls
........
test1.dbf
ASMCMD>  cp /home/grid/DATADG test1.dbf
copying /home/grid/DATADG -> +DATADG/bigdata/DATAFILE/test1.dbf
ASMCMD> ls
...................
test1.dbf

step 4.rename 文件

SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/DATADG' to '+DATADG/rac11g/datafile/test1.dbf';  
Database altered.

step 5.recover 文件

SQL> alter database recover datafile '+DATADG/bigdata/datafile/test1.dbf';
Database altered.

step 6.数据文件上线

SQL>  alter database datafile '+DATADG/bigdata/datafile/test1.dbf' online;
Database altered.

墨力计划 oracle

最后修改时间：2025-02-27 09:48:49

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle RAC 数据文件添加成本存储的解决办法

摘要：

1.问题描述

2.解决办法

3.模拟添加数据库文件，指定错误的存储

使用 rman 命令 copy datafile

离线表空间

离线数据文件

backup as copy datafile

dbms_file_transfer

asmcmd 复制文件

评论