ASM迁移至文件系统

--创建pfile文件
SQL> create  pfile ='/tmp/pfile' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--修改pfile中关于asm中的内容
control_files
db_recovery_file_dest
log_archive_dest_1
指定到文件系统
--登录rman
[oracle@localhost tmp]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:48:26 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: TOS (DBID=1569606545)
--执行backup as copy datafile
RMAN>  backup as copy  datafile '+DATA/tos/datafile/users.276.754906035' format '/u01/oradata/tos/USERS01.dbf';
Starting backup at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+DATA/tos/datafile/users.276.754906035
output filename=/u01/oradata/tos/USERS01.dbf tag=TAG20110627T124853 recid=17 stamp=754922939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-JUN-11
RMAN>  backup as copy  datafile '+DATA/tos/datafile/sysaux.271.754905929' format '/u01/oradata/tos/SYSAUX01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+DATA/tos/datafile/sysaux.271.754905929
output filename=/u01/oradata/tos/SYSAUX01.dbf tag=TAG20110627T124929 recid=18 stamp=754923029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
Finished backup at 27-JUN-11
RMAN>  backup as copy  datafile '+DATA/tos/datafile/undotbs1.273.754906021' format '/u01/oradata/tos/UNDOTBS101.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+DATA/tos/datafile/undotbs1.273.754906021
output filename=/u01/oradata/tos/UNDOTBS101.dbf tag=TAG20110627T125049 recid=19 stamp=754923057
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 27-JUN-11
RMAN>  backup as copy  datafile '+DATA/tos/datafile/system.270.754905833' format '/u01/oradata/tos/SYSTEM01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+DATA/tos/datafile/system.270.754905833
output filename=/u01/oradata/tos/SYSTEM01.dbf tag=TAG20110627T125112 recid=20 stamp=754923150
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:39
ORA-01580: error creating control backup file /u01/oradata/tos/SYSTEM01.dbf
ORA-27038: created file already exists
Additional information: 1
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 27-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2011 12:52:42
ORA-19504: failed to create file "/u01/oradata/tos/SYSTEM01.dbf"
ORA-27038: created file already exists
Additional information: 1
注：因为默认情况下，备份system数据文件是，会自动备份控制文件，这里因为system01.dbf已经备份好，而控制文件再次备份为该名称所以失败
RMAN>  backup as copy  datafile '+DATA/tos/datafile/example.272.754905995' format '/u01/oradata/tos/EXAMPLE01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+DATA/tos/datafile/example.272.754905995
output filename=/u01/oradata/tos/EXAMPLE01.dbf tag=TAG20110627T125341 recid=21 stamp=754923244
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 27-JUN-11
RMAN>  backup as copy  datafile '+DATA/tos/datafile/xff.274.754906027' format '/u01/oradata/tos/XFF01.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/tos/datafile/xff.274.754906027
output filename=/u01/oradata/tos/XFF01.dbf tag=TAG20110627T125415 recid=22 stamp=754923257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11
RMAN>  backup as copy  datafile '+DATA/tos/datafile/xff.275.754906031' format '/u01/oradata/tos/XFF02.dbf';
Starting backup at 27-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=+DATA/tos/datafile/xff.275.754906031
output filename=/u01/oradata/tos/XFF02.dbf tag=TAG20110627T125507 recid=23 stamp=754923309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11
RMAN> exit
Recovery Manager complete.
--登录sqlplus
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 12:55:29 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--备份控制文件
SQL> alter database backup controlfile to '/tmp/control.ctl';
Database altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动数据库只nomount状态
SQL> startup pfile='/tmp/pfile' nomount;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1260672 bytes
Variable Size              79692672 bytes
Database Buffers           79691776 bytes
Redo Buffers                7127040 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost tmp]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 12:58:22 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: tos (not mounted)
--恢复控制文件
RMAN> restore controlfile from '/tmp/control.ctl';
Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/u01/oradata/tos/control01.ctl
output filename=/u01/oradata/tos/control02.ctl
Finished restore at 27-JUN-11
--启动数据库只mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--修改数据文件在控制文件中位置
RMAN> switch tablespace SYSTEM to copy;
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11
Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Crosschecked 15 objects
Finished implicit crosscheck copy at 27-JUN-11
searching for all files in the recovery area
cataloging files...
no files cataloged
datafile 1 switched to datafile copy "/u01/oradata/tos/SYSTEM01.dbf"
RMAN> switch tablespace UNDOTBS1 to copy;
datafile 2 switched to datafile copy "/u01/oradata/tos/UNDOTBS101.dbf"
RMAN>  switch tablespace SYSAUX to copy;
datafile 3 switched to datafile copy "/u01/oradata/tos/SYSAUX01.dbf"
RMAN> switch tablespace USERS to copy;
datafile 4 switched to datafile copy "/u01/oradata/tos/USERS01.dbf"
RMAN>  switch tablespace EXAMPLE to copy;
datafile 5 switched to datafile copy "/u01/oradata/tos/EXAMPLE01.dbf"
RMAN>  switch tablespace XFF to copy;
datafile 6 switched to datafile copy "/u01/oradata/tos/XFF01.dbf"
datafile 7 switched to datafile copy "/u01/oradata/tos/XFF02.dbf"
--恢复数据库
RMAN> recover database;
Starting recover at 27-JUN-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file +DATA/tos/onlinelog/group_6.279.754906321
archive log filename=+DATA/tos/onlinelog/group_6.279.754906321 thread=1 sequence=9
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11
--打开数据库
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/27/2011 13:00:36
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
RMAN> alter database open resetlogs;
database opened
注：不能直接使用open打开
RMAN> exit
Recovery Manager complete.
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 27 13:02:53 2011
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
--增加redo log
SQL> alter database add logfile group 1 '/u01/oradata/tos/redo01.log' size 10m;
Database altered.
SQL> alter database add logfile group 2 '/u01/oradata/tos/redo02.log' size 10m;
Database altered.
SQL> alter database add logfile group 3 '/u01/oradata/tos/redo03.log' size 10m;
Database altered.
--切换日志
SQL>  alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
--内存中数据写入硬盘
SQL> alter system checkpoint;
System altered.
--查询当前日志组状态
SQL>  select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 INACTIVE
6 rows selected.
--删除asm中日志
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
--添加临时文件
SQL> alter tablespace temp add tempfile '/u01/oradata/tos/temp01.dbf' size 30m autoextend on maxsize 1g;
Tablespace altered.
--查看临时表空间中临时文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/temp01.dbf
+DATA/tos/tempfile/temp.280.754906369
--删除asm中临时文件
SQL> alter tablespace temp drop tempfile '+DATA/tos/tempfile/temp.280.754906369';
Tablespace altered.
--查看迁移结果
SQL> set pagesize 100
SQL> select name from v$datafile
  2   union
  3   select member from v$logfile
  4   union
  5   select name from v$controlfile
  6   union
  7   select name from v$tempfile;
NAME
------------------------------------------------------------------
/u01/oradata/tos/EXAMPLE01.dbf
/u01/oradata/tos/SYSAUX01.dbf
/u01/oradata/tos/SYSTEM01.dbf
/u01/oradata/tos/UNDOTBS101.dbf
/u01/oradata/tos/USERS01.dbf
/u01/oradata/tos/XFF01.dbf
/u01/oradata/tos/XFF02.dbf
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/temp01.dbf
13 rows selected.
--创建spfile文件
SQL> create spfile from pfile='/tmp/pfile';
File created.

create spfile to asm

--查看sid
SQL> show parameter instance_name  ;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      tos
--创建pfile
SQL> create pfile='/tmp/tospfile' from spfile;
File created.
--创建spfile in asm
SQL> create spfile='+data' from pfile='/tmp/tospfile';
File created.
--查看spfile name in asm
ASMCMD> pwd
+data/tos/parameterfile
ASMCMD> ls
spfile.282.754913039
--编辑pfile内容（如果有该文件，先删除/重命名）
[oracle@localhost ~]$ vi  $ORACLE_HOME/dbs/inittos.ora
#内容为
spfile='+data/tos/parameterfile/spfile.282.754913039'
--重命名spfile文件
[oracle@localhost dbs]$ mv spfiletos.ora spfiletos.ora_bak
--重启数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1260672 bytes
Variable Size              75498368 bytes
Database Buffers           83886080 bytes
Redo Buffers                7127040 bytes
Database mounted.
Database opened.
--查看spfile
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/tos/parameterfile/spfile
                                                 .282.754913039

普通库迁移至ASM存储

--查看当前库文件位置
SQL> select name from v$datafile
  2  union
  3  select member from v$logfile
  4  union
  5  select name from v$controlfile
  6  union
  7  select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oradata/tos/control01.ctl
/u01/oradata/tos/control02.ctl
/u01/oradata/tos/control03.ctl
/u01/oradata/tos/example01.dbf
/u01/oradata/tos/redo01.log
/u01/oradata/tos/redo02.log
/u01/oradata/tos/redo03.log
/u01/oradata/tos/sysaux01.dbf
/u01/oradata/tos/system01.dbf
/u01/oradata/tos/temp01.dbf
/u01/oradata/tos/undotbs01.dbf
/u01/oradata/tos/users01.dbf
/u01/oradata/tos/xff01.dbf
/u01/oradata/tos/xff02.dbf
14 rows selected.
--备份控制文件
SQL> alter database backup controlfile to '/u01/control.ctl';
Database altered.
--修改spfile中的控制文件至asm
SQL>  alter system set control_files='+DATA' scope=spfile;
System altered.
--修改db_recovery_file_dest至asm中
SQL> alter system set db_recovery_file_dest='+FLASHBACK';
System altered.
--修改归档日志至asm中
SQL> alter system set log_archive_dest_1='location=+DATA';
System altered.
--关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--登录rman
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 08:02:37 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)
--启动数据库至nomount状态
RMAN> startup nomount;
Oracle instance started
Total System Global Area     167772160 bytes
Fixed Size                     1260672 bytes
Variable Size                 67109760 bytes
Database Buffers              92274688 bytes
Redo Buffers                   7127040 bytes
--恢复控制文件
RMAN>  restore controlfile from '/u01/control.ctl';
Starting restore at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/tos/controlfile/current.268.754905785
Finished restore at 27-JUN-11
--打开数据库至mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--copy数据文件至asm
RMAN> backup as copy database format '+DATA';
Starting backup at 27-JUN-11
Starting implicit crosscheck backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Finished implicit crosscheck backup at 27-JUN-11
Starting implicit crosscheck copy at 27-JUN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-11
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/oradata/tos/system01.dbf
output filename=+DATA/tos/datafile/system.270.754905833 tag=TAG20110627T080352 recid=2 stamp=754905928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/oradata/tos/sysaux01.dbf
output filename=+DATA/tos/datafile/sysaux.271.754905929 tag=TAG20110627T080352 recid=3 stamp=754905990
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/oradata/tos/example01.dbf
output filename=+DATA/tos/datafile/example.272.754905995 tag=TAG20110627T080352 recid=4 stamp=754906010
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/oradata/tos/undotbs01.dbf
output filename=+DATA/tos/datafile/undotbs1.273.754906021 tag=TAG20110627T080352 recid=5 stamp=754906025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/tos/xff01.dbf
output filename=+DATA/tos/datafile/xff.274.754906027 tag=TAG20110627T080352 recid=6 stamp=754906029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u01/oradata/tos/xff02.dbf
output filename=+DATA/tos/datafile/xff.275.754906031 tag=TAG20110627T080352 recid=7 stamp=754906032
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/oradata/tos/users01.dbf
output filename=+DATA/tos/datafile/users.276.754906035 tag=TAG20110627T080352 recid=8 stamp=754906035
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 27-JUN-11
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
--恢复数据库
RMAN>  recover database;
Starting recover at 27-JUN-11
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /u01/oradata/tos/redo02.log
archive log filename=/u01/oradata/tos/redo02.log thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-11
--修改数据库中数据文件路径
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/tos/datafile/system.270.754905833"
datafile 2 switched to datafile copy "+DATA/tos/datafile/undotbs1.273.754906021"
datafile 3 switched to datafile copy "+DATA/tos/datafile/sysaux.271.754905929"
datafile 4 switched to datafile copy "+DATA/tos/datafile/users.276.754906035"
datafile 5 switched to datafile copy "+DATA/tos/datafile/example.272.754905995"
datafile 6 switched to datafile copy "+DATA/tos/datafile/xff.274.754906027"
datafile 7 switched to datafile copy "+DATA/tos/datafile/xff.275.754906031"
--打开数据库
RMAN> alter database open resetlogs;
database opened
--添加日志文件
SQL> alter database add logfile group 4 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 5 '+DATA' size 10m;
Database altered.
SQL> alter database add logfile group 6 '+DATA' size 10m;
Database altered.
--添加临时文件
SQL> alter tablespace temp add tempfile '+DATA' size 30m;
Tablespace altered.
--删除原临时文件
SQL> alter tablespace temp drop tempfile '/u01/oradata/tos/temp01.dbf';
Tablespace altered.
--切换日志
SQL> alter system switch logfile; --多次
System altered.
--数据文件修改写入磁盘
SQL> alter system checkpoint;
System altered.
--检查联机日志状态
SQL> select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 CURRENT
6 rows selected.
--删除原联机日志
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
--查看修改后状态
SQL>  select name from v$datafile
  2      union
  3      select member from v$logfile
  4      union
  5      select name from v$controlfile
  6      union
  7      select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/tos/controlfile/current.268.754905785
+DATA/tos/datafile/example.272.754905995
+DATA/tos/datafile/sysaux.271.754905929
+DATA/tos/datafile/system.270.754905833
+DATA/tos/datafile/undotbs1.273.754906021
+DATA/tos/datafile/users.276.754906035
+DATA/tos/datafile/xff.274.754906027
+DATA/tos/datafile/xff.275.754906031
+DATA/tos/onlinelog/group_4.277.754906309
+DATA/tos/onlinelog/group_5.278.754906319
+DATA/tos/onlinelog/group_6.279.754906321
+DATA/tos/tempfile/temp.280.754906369

asm数据文件迁移(asm–>os)

--查看当前情况
SQL> select count(*) from hr.a;
  COUNT(*)
----------
      1580
SQL>  select name from v$DATAFILE;
NAME
-----------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754902279
6 rows selected.
--创建目录
SQL>  create directory asmsrc as '+DG2/TASM/datafile';
Directory created.
SQL>  create directory osdesc as '/u01/oradata';
Directory created.
--表空间离线
SQL> alter tablespace xff offline;
Tablespace altered.
--数据文件迁移
SQL> begin
  2      dbms_file_transfer.copy_file('ASMSRC',
  3      'xff.256.754902279',
  4      'OSDESC',
  5      'xff_new_2.dbf');
  6  END;
  7  /
PL/SQL procedure successfully completed.
--修改数据库中datafile路径
SQL> alter database rename file
  2             '+DG2/tasm/datafile/xff.256.754902279'
  3             to '/u01/oradata/xff_new_2.dbf';
Database altered.
--表空间在线
SQL> alter tablespace xff online;
Tablespace altered.
--测试迁移结果
SQL> select name from v$DATAFILE;
NAME
-------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xff_new_2.dbf
6 rows selected.
SQL> select count(*) from hr.a;
  COUNT(*)
----------
      1580
--删除asm中文件
ASMCMD> rm XFF.256.754902279
ORA-15032: not all alterations performed
ORA-15028: ASM file '+dg2/tasm/datafile/XFF.256.754902279' not dropped;
currently being accessed (DBD ERROR: OCIStmtExecute)
--不能直接删除，关闭数据库后可以删除（应该是bug）

asm数据文件迁移(asm–>asm)

rman迁移操作

[oracle@localhost oradata]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 05:50:03 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: TASM (DBID=3032096031)
RMAN> sql ‘alter tablespace xff offline’;
using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline
RMAN> backup device type disk as copy datafile 6 format '+DG2';
Starting backup at 27-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=+DATA/tasm/xff01.dbf
output filename=+DG2/tasm/datafile/xff.256.754899605 tag=TAG20110627T062003 recid=4 stamp=754899608
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-JUN-11
RMAN> switch tablepspace xff  to copy;
datafile 6 switched to datafile copy "+DG2/tasm/datafile/xff.256.754899605"
RMAN> sql 'alter tablespace xff online';
sql statement: alter tablespace xff online

sql验证操作

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DG2/tasm/datafile/xff.256.754899605
6 rows selected.
SQL> select count(*) from hr.a;
  COUNT(*)
----------
      1580

asm数据文件迁移(os–>asm)

--添加测试表空间
SQL> create tablespace xff datafile '/u01/oradata/xifenfei.dbf' size 10m autoextend on maxsize 100m;
Tablespace created.
--查看数据文件位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
/u01/oradata/xifenfei.dbf
6 rows selected.
--创建测试表
SQL> create table hr.a tablespace xff
  2  as
  3  select * from dba_tables;
Table created.
SQL> select count(*) from hr.a;
  COUNT(*)
----------
      1580
--转移数据文件位置
[oracle@localhost oradata]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 27 04:30:22 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: TASM (DBID=3032096031)
RMAN> sql 'alter tablespace xff offline';
using target database control file instead of recovery catalog
sql statement: alter tablespace xff offline
RMAN> backup as copy tablespace xff format '+DATA';
Starting backup at 27-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=132 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/oradata/xifenfei.dbf
output filename=+DATA/tasm/datafile/xff.269.754893121 tag=TAG20110627T043200 recid=2 stamp=754893123
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-JUN-11
RMAN> switch tablespace xff to copy;
datafile 6 switched to datafile copy "+DATA/tasm/datafile/xff.269.754893121"
RMAN> sql 'alter tablespace xff online';
sql statement: alter tablespace xff online
--查看转移后的数据文件位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/datafile/xff.269.754893121
6 rows selected.
--测试其中数据是否存在
SQL> select count(*) from hr.a;
  COUNT(*)
----------
      1580
--创建asm中文件别名
ASMCMD> mkalias  +DATA/tasm/datafile/xff.269.754893121 +DATA/tasm/xff01.dbf
--文件重命名
SQL> alter tablespace xff offline;
Tablespace altered.
SQL> alter database rename file '+DATA/tasm/datafile/xff.269.754893121' to '+DATA/tasm/xff01.dbf';
Database altered.
SQL> alter tablespace xff online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tasm/system01.dbf
+DATA/tasm/undotbs01.dbf
+DATA/tasm/sysaux01.dbf
+DATA/tasm/users01.dbf
+DATA/tasm/example01.dbf
+DATA/tasm/xff01.dbf
6 rows selected.
--手工删除原来数据
[oracle@localhost oradata]$ rm xifenfei.dbf

说明：可以在rman移植数据文件位置的时候，同时处理好别名，然后对表空间重命名，实现只需要表空间离线一次

Oracle数据文件大小的限制

如果你比较细心，会注意到两件事情：
1、添加一个32g的数据文件会报错
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g;
alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 32g
*
ERROR at line 1:
ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks
SQL> !oerr ora 1144
01144, 00000, “File size (%s blocks) exceeds maximum of %s blocks”
// *Cause: Specified file size is larger than maximum allowable size value.
// *Action: Specify a smaller size.
2、添加一个maxsize为unlimited的数据文件
SQL> alter tablespace users add datafile ‘/opt/oracle/oradata/test/user32g.dbf’ size 10M AUTOEXTEND ON maxsize unlimited;
Tablespace altered.
SQL> select MAXBYTES/1024/1024/1024 from dba_data_files where file_name=’/opt/oracle/oradata/test/user32g.dbf’;
MAXBYTES/1024/1024/1024
———————–
31.9999847
你是不是有疑惑，为什么我添加32g的数据文件报错，为什么我添加一个数据文件maxsize设置为unlimited了，还是没有突破32g这个坎
原因分析：
由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1(4194303)个数据块，而在我们一般情况下使用的数据块大小为8k，所以数据文件的理论大小最大为： 31.9999924G
至于maxsize为unlimited时候，数据文件的大小为什么只有31.9999847G(blocks：4194301.99)，比最大块数(4194303)少了一块，也许是和数据库和系统之间的衔接原因导致。
由上面的分析我们可以知道，数据文件大小和db_block_size有关，那我们可以得到这样的数据文件最大理论值

数据块 数据文件
2KB 8GB
4KB 16GB
8KB 32GB
16KB 64GB
32KB 128GB

注：以上规则适用于smallfile tablespace 下的数据文件，bigfile tablespace 下的数据文件不受此限制

ASM简单管理(2)

1、磁盘组卸载/挂载

--查看当前磁盘组状态
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           4 DG2                  MOUNTED                      5114       5012
--卸载dg2磁盘组
SQL> alter diskgroup dg2 dismount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           0 DG2                  DISMOUNTED                      0          0
--挂载dg2磁盘组
SQL> alter diskgroup dg2 mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           1 ARCHIVELOG           MOUNTED                     12637      12585
           2 DATA                 MOUNTED                     10228       7644
           3 FLASHBACK            MOUNTED                      7836       7786
           4 DG2                  MOUNTED                      5114       5012
--卸载所有磁盘组
SQL> alter diskgroup all dismount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           0 DATA                 DISMOUNTED                      0          0
           0 DG2                  DISMOUNTED                      0          0
           0 FLASHBACK            DISMOUNTED                      0          0
           0 ARCHIVELOG           DISMOUNTED                      0          0
--挂载所有磁盘组
SQL> alter diskgroup all mount;
Diskgroup altered.
SQL> select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME                 STATE                    TOTAL_MB    FREE_MB
------------ -------------------- ---------------------- ---------- ----------
           2 DATA                 MOUNTED                     10228       7644
           3 DG2                  MOUNTED                      5114       5012
           4 FLASHBACK            MOUNTED                      7836       7786
           1 ARCHIVELOG           MOUNTED                     12637      12585
--如果当前磁盘组有实例在访问，则不能被卸载
SQL> alter diskgroup data dismount;
alter diskgroup data dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount

2、磁盘组目录

--创建目录
SQL> alter diskgroup dg2 add directory '+DG2/CHENGFEI';
Diskgroup altered.
--使用asmcmd查看（export ORACLE_SID=+ASM;asmcmd进入)
ASMCMD> pwd
+DG2
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        N    CHENGFEI/
--目录重命名
SQL> alter diskgroup dg2 rename directory '+DG2/CHENGFEI' TO '+DG2/XIFENFEI';
Diskgroup altered.
ASMCMD> ls
XIFENFEI/
--删除目录
SQL> alter diskgroup dg2 drop directory '+DG2/xifenfei';
Diskgroup altered.
ASMCMD> ls
ASMCMD>

3、文件别名

--添加别名
SQL> alter diskgroup dg2 add alias '+dg2/XIFENFEI' for '+dg2/tasm/datafile/XFF.256.754832383';
Diskgroup altered.
--使用asmcmd查看别名是否成功
ASMCMD> pwd
+dg2
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    TASM/
                                            N    XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
--别名重命名
SQL> alter diskgroup dg2 rename alias '+DG2/XIFENFEI' TO '+DG2/FEIFEI.DBF';
Diskgroup altered.
                                            N    XIFENFEI => +DG2/TASM/DATAFILE/XFF.256.754832383
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            N    FEIFEI.DBF => +DG2/TASM/DATAFILE/XFF.256.754832383
                                           Y    TASM/
--删除别名
SQL> alter diskgroup dg2 drop alias '+dg2/feifei.dbf';
Diskgroup altered.
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    TASM/

说明：
1）asmcmd命令行操作中，目录文件名不区分大小写，命令关键字区分大小写
2）磁盘组中的一个文件，最多只能建立一个别名可以通过v$asm_alias视图查看别名的相关信息
ASM简单管理(1)

Oracle RAC10g UNKNOWN解决

1、表现出来的现象
rac2-> crs_stat -t
Name Type Target State Host
————————————————————
ora.rac1.gsd application ONLINE UNKNOWN rac1
ora.rac1.ons application ONLINE UNKNOWN rac1
ora.rac1.vip application ONLINE UNKNOWN rac1
ora.rac2.gsd application ONLINE UNKNOWN rac2
ora.rac2.ons application ONLINE UNKNOWN rac2
ora.rac2.vip application ONLINE UNKNOWN rac1
可以各种原因导致服务呈现unknown 状态，比如两个节点时间差太多，比如1分钟，可能导致这个异常
2、解决方法
因为UNKNOWN不能正常的被启动或者关闭（crs_start -all/crs_stop -all），所以解决方法有两种：
2.1）先禁用crs服务，后启用crs服务(root用户，两个节点上都要执行)
/etc/init.d/init.crs stop
/etc/init.d/init.crs start
2.2）单个服务关闭，后启动（oracle用户，一个节点上执行）
crs_stop ora.rac2.gsd（crs_stop -f ora.rac2.gsd强制关闭）
crs_start ora.rac2.gsd（crs_start -f ora.rac2.gsd强制开启）
该方法有变通，如一个个关闭服务，然后使用crs_start -all开启所有服务器

crs 10.2.0.1 bug（公网ip为私有ip时）

当crs10.2.0.1公用网卡IP段使用10、172、192段（非路由网段）的时候
1、会出现以下两种情况：
1）最后检查时出现如下错误
Checking existence of VIP node application (required)
Check failed.
Check failed on nodes:
rac1,rac2
2）在rac2上执行root.sh脚本最后
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
rac1
rac2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Waiting for the Oracle CRSD and EVMD to start
……
Timed out waiting for the CRS stack to start.
2、解决方法：
在root.sh在第二个节点执行完之后，以 root 用户身份在第二个节点上手动调用 VIPCA
# /u01/app/oracle/product/10.2.0/crs_1/bin/vipca
配置虚拟ip