单实例oracle从文件系统迁移到ASM

今天需要在asm环境下测试一个工具，手头缺少asm环境的测试库，故把一个文件系统上的单实例迁移到asm上来做测试，顺便记录一下

安装配置asm实例的步骤这里不再重复

1,查看asm实例状态
[oracle@goolen ~]$ export ORACLE_SID=+ASM                

[oracle@goolen ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 14 18:51:48 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                    STATUS
-------------------------------- ------------------------
+ASM                             STARTED

SQL> col name for a35
SQL> set lines 120
SQL> select name,state from v$asm_diskgroup;

NAME                                STATE
----------------------------------- ----------------------
DATA01                              MOUNTED
DATA02                              MOUNTED

2，查看数据库信息：
[oracle@goolen ~]$ export ORACLE_SID=ora10g
[oracle@goolen ~]$ sqlplus / as sysdba     

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/system01.dbf
/opt/app/oracle/oradata/ora10g/undotbs01.dbf
/opt/app/oracle/oradata/ora10g/sysaux01.dbf
/opt/app/oracle/oradata/ora10g/user01.dbf
/opt/app/oracle/oradata/ora10g/goolen01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/redo01.log
/opt/app/oracle/oradata/ora10g/redo02.log
/opt/app/oracle/oradata/ora10g/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/control01.ctl

SQL> select name from  v$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/temp01.dbf

3，更改spfile文件，修改参数
SQL> show parameter control_file

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time        integer                7
control_files                        string                 /opt/app/oracle/oradata/ora10g
                                                            /control01.ctl
SQL> show parameter file_dest

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
audit_file_dest                      string                 /opt/app/oracle/admin/ora10g/adump
db_create_file_dest                  string
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer            0

SQL> alter system set control_files='+DATA01' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DATA01' scope=spfile;

System altered.

SQL> shut immediate

4，利用rman迁移数据文件和控制文件
[oracle@goolen ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Dec 14 19:01:15 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     289406976 bytes

Fixed Size                     1219016 bytes
Variable Size                234882616 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2973696 bytes

RMAN> restore controlfile from '/opt/app/oracle/oradata/ora10g/control01.ctl';

Starting restore at 2013-12-14 19:01:56
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA01/ora10g/controlfile/backup.256.834174129
Finished restore at 2013-12-14 19:02:12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> run{backup as copy database format '+DATA01';} 

Starting backup at 2013-12-14 19:03:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/opt/app/oracle/oradata/ora10g/system01.dbf
output filename=+DATA01/ora10g/datafile/system.257.834174201 tag=TAG20131214T190320 recid=1 stamp=834174219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/opt/app/oracle/oradata/ora10g/sysaux01.dbf
output filename=+DATA01/ora10g/datafile/sysaux.258.834174225 tag=TAG20131214T190320 recid=2 stamp=834174234
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/opt/app/oracle/oradata/ora10g/undotbs01.dbf
output filename=+DATA01/ora10g/datafile/undotbs1.259.834174241 tag=TAG20131214T190320 recid=3 stamp=834174243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/opt/app/oracle/oradata/ora10g/user01.dbf
output filename=+DATA01/ora10g/datafile/users.260.834174245 tag=TAG20131214T190320 recid=4 stamp=834174245
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/opt/app/oracle/oradata/ora10g/goolen01.dbf
output filename=+DATA01/ora10g/datafile/goolen.261.834174245 tag=TAG20131214T190320 recid=5 stamp=834174246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA01/ora10g/controlfile/backup.262.834174247 tag=TAG20131214T190320 recid=6 stamp=834174247
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
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 2013-12-14 19:04:07
channel ORA_DISK_1: finished piece 1 at 2013-12-14 19:04:08
piece handle=+DATA01/ora10g/backupset/2013_12_14/nnsnf0_tag20131214t190320_0.263.834174247 tag=TAG20131214T190320 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2013-12-14 19:04:09

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA01/ora10g/datafile/system.257.834174201"
datafile 2 switched to datafile copy "+DATA01/ora10g/datafile/undotbs1.259.834174241"
datafile 3 switched to datafile copy "+DATA01/ora10g/datafile/sysaux.258.834174225"
datafile 4 switched to datafile copy "+DATA01/ora10g/datafile/users.260.834174245"
datafile 5 switched to datafile copy "+DATA01/ora10g/datafile/goolen.261.834174245"

RMAN> recover database;

Starting recover at 2013-12-14 19:04:34
using channel ORA_DISK_1

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

Finished recover at 2013-12-14 19:04:41

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.


5，迁移redo log 和tempfile
[oracle@goolen ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Dec 14 19:05:02 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME                    STATUS
-------------------------------- ------------------------
ora10g                           OPEN

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/ora10g/redo01.log
/opt/app/oracle/oradata/ora10g/redo02.log
/opt/app/oracle/oradata/ora10g/redo03.log

SQL> select bytes from v$log;

     BYTES
----------
  52428800
  52428800
  52428800


SQL> alter database add logfile '+DATA02' size 50m;

Database altered.

SQL> alter database add logfile '+DATA02' size 50m;

Database altered.

SQL> alter database add logfile '+DATA02' size 50m;

Database altered.

SQL> col member for a55
SQL> set lines 120
SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- -------------------------------------------------------
         1 /opt/app/oracle/oradata/ora10g/redo01.log
         2 /opt/app/oracle/oradata/ora10g/redo02.log
         3 /opt/app/oracle/oradata/ora10g/redo03.log
         4 +DATA02/ora10g/onlinelog/group_4.256.834174527
         5 +DATA02/ora10g/onlinelog/group_5.257.834174533
         6 +DATA02/ora10g/onlinelog/group_6.258.834174539

6 rows selected.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance ora10g (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/ora10g/redo01.log'

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ora10g (thread 1)
ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/ora10g/redo01.log'

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- -------------------------------------------------------
         4 +DATA02/ora10g/onlinelog/group_4.256.834174527
         5 +DATA02/ora10g/onlinelog/group_5.257.834174533
         6 +DATA02/ora10g/onlinelog/group_6.258.834174539

SQL> alter tablespace temp add tempfile '+DATA02' size 10m autoextend on maxsize 2G;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/opt/app/oracle/oradata/ora10g/temp01.dbf';

Tablespace altered.

SQL> col name for a55
SQL> select name,status from v$tempfile;

NAME                                                    STATUS
------------------------------------------------------- --------------
+DATA02/ora10g/tempfile/temp.259.834174669              ONLINE

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select name from v$datafile;

NAME
-------------------------------------------------------
+DATA01/ora10g/datafile/system.257.834174201
+DATA01/ora10g/datafile/undotbs1.259.834174241
+DATA01/ora10g/datafile/sysaux.258.834174225
+DATA01/ora10g/datafile/users.260.834174245
+DATA01/ora10g/datafile/goolen.261.834174245