RAC（real application clusters），译为“实时应用集群”， 是Oracle新版数据库中采用的一项新技术，是高可用性的一种，也是Oracle数据库支持网格计算环境的核心技术。
在操作时，非DBA人员很可能把表空间等创建在本地导致其他实例无法使用，最简单的解决方法是删除重建，但生产上往往是不可以的，因为此时的数据文件很可能已经有了不能删除的数据，下面为大家介绍一种rman迁移表空间到ASM的方法，在不失去数据的情况下解决这种问题。
环境介绍
Red hat linux 5.5
Oracle 11g
Rac
[oracle@rac1 ~]$ uname -a
Linux rac1 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:43 EDT 2010 i686 i686 i386 GNU/Linux
归档模式
SYS@RACDB1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 96
Next log sequence to archive 97
Current log sequence 97
1. 表空间创建到了本地
SYS@RACDB1> select ts#,name from v$tablespace where name='LOC' union all select file#,name from v$datafile where ts# = 7;
TS# NAME
--- --------------------------------------------------------------------------------------------
7 LOC
6 /home/oracle/loc.dbf
---- -------- ------------------------------------------------- ----------- --------------------
1 67 TEMP 32767 +DATA/racdb/tempfile/temp.263.843128007
这种情况下，造成rac2无法正常操作
SYS@RACDB2> select * from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/loc.dbf'
no rows selected
并且rac无法删除此表空间
SYS@RACDB2> drop tablespace loc;
drop tablespace loc
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/home/oracle/loc.dbf'
在不删除文件的情况下解决这个问题
1. RAM 查看对象环境
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 31 09:40:42 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=840744762)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/racdb/datafile/system.260.843127999
2 660 SYSAUX *** +DATA/racdb/datafile/sysaux.261.843128003
3 690 UNDOTBS1 *** +DATA/racdb/datafile/undotbs1.262.843128005
4 200 UNDOTBS2 *** +DATA/racdb/datafile/undotbs2.264.843128015
5 5 USERS *** +DATA/racdb/datafile/users.265.843128017
6 10 LOC *** /home/oracle/loc.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
2. 离线表空间
RMAN> sql 'alter tablespace loc offline';
sql statement: alter tablespace loc offline
3.Copy类型备份表空间
RMAN> backup as copy datafile 6 format '+data';
Starting backup at 31-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/loc.dbf
output file name=+DATA/racdb/datafile/loc.290.843644629 tag=TAG20140331T094347 RECID=1 STAMP=843644629
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-MAR-14
Starting Control File and SPFILE Autobackup at 31-MAR-14
piece handle=+DATA/racdb/autobackup/2014_03_31/s_843644632.291.843644635 comment=NONE
Finished Control File and SPFILE Autobackup at 31-MAR-14
4. 切换表空间文件到copy副本
RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy "+DATA/racdb/datafile/loc.290.843644629"
5. 上线表空间
RMAN> sql 'alter tablespace loc online';
sql statement: alter tablespace loc online
6. 此时再通过ram查看对象信息
RMAN> report schema;
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
========================================================================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- -------------------------------------------------------------------------------
1 710 SYSTEM *** +DATA/racdb/datafile/system.260.843127999
2 660 SYSAUX *** +DATA/racdb/datafile/sysaux.261.843128003
3 690 UNDOTBS1 *** +DATA/racdb/datafile/undotbs1.262.843128005
4 200 UNDOTBS2 *** +DATA/racdb/datafile/undotbs2.264.843128015
5 5 USERS *** +DATA/racdb/datafile/users.265.843128017
6 10 LOC *** +DATA/racdb/datafile/loc.290.843644629
List of Temporary Files
========================================================================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ----------------------------------------------------------------------------- ----------- --------------------
1 67 TEMP 32767 +DATA/racdb/tempfile/temp.263.843128007
7. Sqlplus查看表空间
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 31 09:45:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RACDB1> select ts#,name from v$tablespace where name='LOC' union all select file#,name from v$datafile where ts#=7;
TS# NAME
--------------------------------------------------------------------------------
7 LOC
6 +DATA/racdb/datafile/loc.290.843644629
8. 因为是rac架构，我们再通过rac2查看表空间信息
[oracle@rac2 ~]$ sqlplus / as sysdba
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@RACDB2> select ts#,name from v$tablespace where name='LOC' union all select file#,name from v$datafile where ts#=7;
TS# NAME
--------------------------------------------------------------------------------
7 LOC
6 +DATA/racdb/datafile/loc.290.843644629
9.完成迁移
为节省空间，有备份的情况下，我们创建在本地的数据文件可以删除
[oracle@rac1 ~]$ rm loc.dbf
o啦
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29438052/viewspace-1133101/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29438052/viewspace-1133101/
扫一扫
1819
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
