RAC本地数据文件迁移至ASM的方法–非归档模式

117次阅读

共计 4850 个字符，预计需要花费 13 分钟才能阅读完成。

参考：RAC 本地数据文件迁移至 ASM 的方法 http://www.linuxidc.com/Linux/2015-06/118799.htm

系统环境：rhel6.2_x64+Oracle RAC11g

操作过程：
1. 非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/oracle/app/oracle/11.2.0/db/dbs/arch
Oldest online log sequence 303086
Current log sequence 303087
SQL>

2. 非系统表空间（当前为生产环境，节点 RAC1 可识别本地文件）
SQL> set line 180
SQL> col file_name for a60
SQL> col tablespace_name for a15
SQL> select file_name,file_id,online_status,tablespace_name from dba_data_files;
FILE_NAME FILE_ID ONLINE_ TABLESPACE_NAME
———————————————————— ———- ——- —————
+DATA/sdgdorcl/datafile/system.259.848099691 1 SYSTEM SYSTEM
+DATA/sdgdorcl/datafile/sysaux.260.848099695 2 ONLINE SYSAUX
+DATA/sdgdorcl/datafile/undotbs1.261.848099697 3 ONLINE UNDOTBS1
+DATA/sdgdorcl/datafile/undotbs2.263.848099707 4 ONLINE UNDOTBS2
+DATA/sdgdorcl/datafile/users.264.848099707 5 ONLINE USERS
+DATA/sdgdorcl/datafile/data01.268.848183595 6 ONLINE data01
+DATA/sdgdorcl/datafile/nnc_index01.269.848183657 7 ONLINE INDEX01
+DATA/sdgdorcl/datafile/data0101.268.8481835951.ora 8 ONLINE data01
+DATA/sdgdorcl/datafile/data01.271.854940577 9 ONLINE data01
+DATA/sdgdorcl/datafile/data0102.268.8481835951.ora 10 ONLINE data01
+DATA/sdgdorcl/datafile/nnc_index01.20160308.ora 11 ONLINE INDEX01

FILE_NAME FILE_ID ONLINE_ TABLESPACE_NAME
———————————————————— ———- ——- —————
/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora 12 ONLINE data01
/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora 13 ONLINE data01
+DATA/sdgdorcl/datafile/data01.274.911612215 14 ONLINE data01
+DATA/sdgdorcl/datafile/data01.275.911612497 15 ONLINE data01
+DATA/sdgdorcl/datafile/nnc_index01.276.911612519 16 ONLINE INDEX01
+DATA/sdgdorcl/datafile/nnc_index01.277.911612529 17 ONLINE INDEX01
SQL>

3. 干净关闭 RAC2，RAC1
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. 将 RAC1 启动 mount 状态
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size 2245480 bytes
Variable Size 7381978264 bytes
Database Buffers 9663676416 bytes
Redo Buffers 55263232 bytes
Database mounted.
SQL>

5. 通过 RMAN CP 命令拷贝数据文件
[oracle@sdgddb1 ora_data]$ rman target /
RMAN> copy datafile ‘/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora’ to ‘+DATA’;
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=433 instance=sdgdorcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora
output file name=+DATA/sdgdorcl/datafile/data01.278.911710731 tag=TAG20160513T045849 RECID=1 STAMP=911710866
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25
Finished backup at 13-MAY-16

RMAN> copy datafile ‘/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora’ to ‘+DATA’;
Starting backup at 13-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora
output file name=+DATA/sdgdorcl/datafile/data01.279.911710969 tag=TAG20160513T050248 RECID=2 STAMP=911711045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 13-MAY-16
RMAN>

6. 在 sqlplus 中将数据库启动到 mount 状态，rename 数据文件，并查看信息
SQL> alter database rename file ‘/u01/oracle/app/oracle/11.2.0/db/dbs/data0328.ora’ to ‘+DATA/sdgdorcl/datafile/data01.278.911710731’;
SQL> alter database rename file ‘/u01/oracle/app/oracle/11.2.0/db/dbs/data0401.ora’ to ‘+DATA/sdgdorcl/datafile/data01.279.911710969’;

7. 将 rac1，rac2 启动
#RAC1
SQL> alter database open;
Database altered.
SQL>

#RAC2
SQL> startup
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size 2245480 bytes
Variable Size 7381978264 bytes
Database Buffers 9663676416 bytes
Redo Buffers 55263232 bytes
Database mounted.
Database opened.
SQL>

附：SYSTEM 数据文件移植步骤（过程说明）：
1. Stop DB.
2. Move the datafile using asmcmd.
3. Mount the DB.
4. Rename the datafile.
5. Open the DB.
6. On other RAC nodes you still need to bounce the database because it is SYSTEM tablespace, otherwise you will keep getting errors ORA-01516 or original error ORA-01157: cannot identify/lock data file.

如何将 ASM 中的数据文件复制到操作系统中 http://www.linuxidc.com/Linux/2013-08/88418.htm

Oracle 11g RAC ASM 磁盘全部丢失后的恢复 http://www.linuxidc.com/Linux/2013-07/87000.htm

Oracle 11g 从入门到精通 PDF+ 光盘源代码 http://www.linuxidc.com/Linux/2013-06/85670.htm

RHEL6 ASM 方式安装 Oracle 11g R2 http://www.linuxidc.com/Linux/2013-06/86002.htm

Oracle 10g 手工创建 ASM 数据库 http://www.linuxidc.com/Linux/2013-01/78229.htm

Oracle 10g R2 创建 ASM 实例 Step By Step http://www.linuxidc.com/Linux/2014-05/101821.htm

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-05/131667.htm

正文完

星哥说事-微信公众号