solaris10_oracle10g_asm_non_asm迁移数据库测试

AVAILABLE DISK SELECTIONS:
       0. c0d0
          /pci@0,0/pci-ide@7,1/ide@0/cmdk@0,0
       1. c0d1
          /pci@0,0/pci-ide@7,1/ide@0/cmdk@1,0
       2. c1d1
          /pci@0,0/pci-ide@7,1/ide@1/cmdk@1,0
       3. c2t0d0
          /pci@0,0/pci1000,30@10/sd@0,0
       4. c2t1d0
          /pci@0,0/pci1000,30@10/sd@1,0
       5. c2t2d0
          /pci@0,0/pci1000,30@10/sd@2,0
Specify disk (enter its number)[3]: 4
selecting c2t1d0
[disk formatted]
format> ver
WARNING - This disk may be in use by an application that has
          modified the fdisk table. Ensure that this disk is
          not currently in use before proceeding to use fdisk.
format> fdisk
No fdisk table exists. The default partition for the disk is:

  a 100% "SOLARIS System" partition

Type "y" to accept the default partition,  otherwise type "n" to edit the
 partition table.
y
format> p


PARTITION MENU:
        0      - change `0' partition
        1      - change `1' partition
        2      - change `2' partition
        3      - change `3' partition
        4      - change `4' partition
        5      - change `5' partition
        6      - change `6' partition
        7      - change `7' partition
        select - select a predefined table
        modify - modify a predefined partition table
        name   - name the current table
        print  - display the current table
        label  - write partition map and label to the disk
        ! - execute , then return
        quit
partition> p
Current partition table (original):
Total disk cylinders available: 1302 + 2 (reserved cylinders)

Part      Tag    Flag     Cylinders        Size            Blocks
  0 unassigned    wm       0               0         (0/0/0)           0
  1 unassigned    wm       0               0         (0/0/0)           0
  2     backup    wu       0 - 1301        9.97GB    (1302/0/0) 20916630
  3 unassigned    wm       0               0         (0/0/0)           0
  4 unassigned    wm       0               0         (0/0/0)           0
  5 unassigned    wm       0               0         (0/0/0)           0
  6 unassigned    wm       0               0         (0/0/0)           0
  7 unassigned    wm       0               0         (0/0/0)           0
  8       boot    wu       0 -    0        7.84MB    (1/0/0)       16065
  9 unassigned    wm       0               0         (0/0/0)           0



非asm迁移到asm存储

      这些操作之前，请v$controlfile,v$datafile,v$logfile,v$log,v$tempfile,show parameter spfile查看记录相关信息
1,添加两个磁盘（一用data,一用recovery)
2,以root用户运行chown和chmod对用于转化存储asm的磁盘给于权限和用户及属组
 c2t1d0
          /pci@0,0/pci1000,30@10/sd@1,0
       5. c2t2d0
          /pci@0,0/pci1000,30@10/sd@2,0
-bash-3.00# chown ora10g:oinstall /dev/rdsk/c2t1d0s1
-bash-3.00# chmod 775 /dev/rdsk/c2t1d0s1
-bash-3.00# chown ora10g:oinstall /dev/rdsk/c2t2d0s1
-bash-3.00# chmod 775 /dev/rdsk/c2t2d0s1
3,进入asm实例，创建用于转变存储的asm磁盘组
-bash-3.00# su - ora10g
make love with you
-bash: export: `/usr/bin/bash': not a valid identifier
-bash-3.00$ export ORACLE_SID=+ASM  ---启动asm实例
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 08:24:17 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area   83886080 bytes
Fixed Size                  1278640 bytes
Variable Size              57441616 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

select name,path,header_status,total_mb from v$asm_disk --查看asm发现所有磁盘

NAME            PATH                           HEADER_STATU   TOTAL_MB
--------------- ------------------------------ ------------ ----------
                /dev/rdsk/c0d1s0               FOREIGN             100
                /dev/rdsk/c0d1s3               FORMER              500
                /dev/rdsk/c0d1s5               FORMER              300
                /dev/rdsk/c0d1s6               FORMER               76
                /dev/rdsk/c0d1s7               FOREIGN             250
                /dev/rdsk/c1d1s0               FOREIGN             110
                /dev/rdsk/c1d1s1               FOREIGN             110
                /dev/rdsk/c2t2d0s1             CANDIDATE          9216
                /dev/rdsk/c2t1d0s1             CANDIDATE          9216
                /dev/rdsk/c1d1s5               FORMER              500
DATA_0000       /dev/rdsk/c0d1s1               MEMBER             1024

NAME            PATH                           HEADER_STATU   TOTAL_MB
--------------- ------------------------------ ------------ ----------
ARCH_0000       /dev/rdsk/c0d1s4               MEMBER              600
FG1_D1          /dev/rdsk/c1d1s3               MEMBER             1024
FG2_D1          /dev/rdsk/c1d1s4               MEMBER             1024

14 rows selected.

SQL> create diskgroup migration_data external redundancy disk '/dev/rdsk/c2t2d0s1';-- 建迁移用的data磁盘组

Diskgroup created.

SQL> create diskgroup migration_recovery external redundancy disk '/dev/rdsk/c2t1d0s1';

Diskgroup created.

SQL>




5,-bash-3.00$ rman target / --进入rman

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 08:34:18 2009

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

connected to target database: ORA10G (DBID=4016423416)

RMAN> backup as copy incremental level 0 database format '+migration_data' tag 'test_migration_data';--作全库0级备份到asm

Starting backup at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/oracle/oradata/ora10g/system01.dbf
output filename=+MIGRATION_DATA/ora10g/datafile/system.256.704104561 tag=TEST_MIGRATION_DATA recid=10 stamp=704104618
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/oracle/oradata/ora10g/sysaux01.dbf
output filename=+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629 tag=TEST_MIGRATION_DATA recid=11 stamp=704104660
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oracle/zxy.dbf
output filename=+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663 tag=TEST_MIGRATION_DATA recid=12 stamp=704104675
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oracle/oradata/ora10g/undotbs01.dbf
output filename=+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679 tag=TEST_MIGRATION_DATA recid=13 stamp=704104687
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oracle/oradata/ora10g/users01.dbf
output filename=+MIGRATION_DATA/ora10g/datafile/users.260.704104693 tag=TEST_MIGRATION_DATA recid=14 stamp=704104696
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-NOV-09

Starting Control File and SPFILE Autobackup at 28-NOV-09
piece handle=/oracle/c-4016423416-20091128-00 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-09

RMAN>
6,RMAN> sql 'alter system archive log current';--进行归档处理，便于一个后期完全恢复

using target database control file instead of recovery catalog
sql statement: alter system archive log current


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/10.2.0/db_1/db
                                                 s/spfileora10g.ora
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 08:41:06 2009

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

connected to target database: ORA10G (DBID=4016423416)

7,RMAN> run {
2> backup as backupset spfile;
3> restore spfile to '+migration_data/spfile';--作spfile备份然后恢复保存在asm
4> }

Starting backup at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
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 28-NOV-09
channel ORA_DISK_1: finished piece 1 at 28-NOV-09
piece handle=/oracle/auto/backup_4bkvfiec_1_1 tag=TAG20091128T084148 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 28-NOV-09

Starting Control File and SPFILE Autobackup at 28-NOV-09
piece handle=/oracle/c-4016423416-20091128-01 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-09

Starting restore at 28-NOV-09
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+migration_data/spfile
channel ORA_DISK_1: reading from backup piece /oracle/c-4016423416-20091128-01
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/c-4016423416-20091128-01 tag=TAG20091128T084150
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
Finished restore at 28-NOV-09

RMAN>


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/10.2.0/db_1/db
                                                 s/spfileora10g.ora
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
--bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 08:45:12 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


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

8,SQL> shutdown immediate  -关库
Database closed.
Database dismounted.
ORACLE instance shut down.


9,-bash-3.00$ more pfile.ora --建一个内容为spfile的pfile
spfile=+migration_data/spfile
-bash-3.00$ pwd
/oracle


-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 08:49:43 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

10,SQL> startup nomount pfile='/oracle/pfile.ora'; --用以上的spfile nomount数据库(一定要是nomount,才可以变更下列的参数）
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1284316 bytes
Variable Size             264242980 bytes
Database Buffers          805306368 bytes
Redo Buffers                2908160 bytes
SQL>

11,SQL> alter system set control_files='+migration_data/ctl1.f','+migration_data/ctl2.f','+migration_data/ctl3.f' scope=spfile;

System altered. --变更控制文件的相关初始化参数

12,SQL> alter system set db_recovery_file_dest_size=8g;--恢复目录大小及存储变更

System altered.

13,SQL> alter system set db_recovery_file_dest='+migration_recovery';

System altered.

14,SQL> shutdown immediate --关库便于上述变更生效
ORA-01507: database not mounted


ORACLE instance shut down.
15,SQL> startup nomount pfile='/oracle/pfile.ora';--启动库（nomount)
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1284316 bytes
Variable Size             264242980 bytes
Database Buffers          805306368 bytes
Redo Buffers                2908160 bytes

16,-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 08:57:20 2009

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

connected to target database: ora10g (not mounted)


RMAN> restore controlfile from '/oracle/oradata/ora10g/control01.ctl';---从原来的控制文件中恢复控制文件(上面control_files只是指一个目录，这是动真格的哟，哈哈）

Starting restore at 28-NOV-09
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output filename=+MIGRATION_DATA/ctl1.f
output filename=+MIGRATION_DATA/ctl2.f
output filename=+MIGRATION_DATA/ctl3.f
Finished restore at 28-NOV-09
17,RMAN> alter database mount;--把库mount

using target database control file instead of recovery catalog
database mounted
18,RMAN> switch database to copy;--这个命令相当牛，就是把你最先备份在asm的数据文件及undo文件在控制文件中更新(我理解就是作一个os cp)

datafile 1 switched to datafile copy "+MIGRATION_DATA/ora10g/datafile/system.256.704104561"
datafile 2 switched to datafile copy "+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679"
datafile 3 switched to datafile copy "+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629"
datafile 4 switched to datafile copy "+MIGRATION_DATA/ora10g/datafile/users.260.704104693"
datafile 5 switched to datafile copy "+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663"

19,RMAN> recover database;--恢复数据库了（这个用到了归档吧，所以上面要一个alter system archive log current)

Starting recover at 28-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
datafile 5 not processed because file is offline

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

Finished recover at 28-NOV-09

RMAN>

20,RMAN> run  ---对临时文件进行变更到asm的操作
2> {set newname for tempfile 1 to '+migration_data';
3> switch tempfile all;  --oracle10g的switch 相当牛，帅死了，
4> }

executing command: SET NEWNAME

renamed temporary file 1 to +migration_data in control file

-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 09:08:34 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


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

21,SQL> alter database open;---打开数据库

Database altered.


22,select member from v$logfile  --查看日志文件，因日志文件不能用常规方法迁移到asm，采用如下新建日志组（在asm)，切换日志，最后删除新的非asm日志组实现

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

 SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ora10g/datafile/system.256.704104561
+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679
+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629
+MIGRATION_DATA/ora10g/datafile/users.260.704104693
+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ora10g/tempfile/temp1.265.704106533


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ctl1.f
+MIGRATION_DATA/ctl2.f
+MIGRATION_DATA/ctl3.f


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +MIGRATION_DATA/spfile
SQL> show parameter control    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +MIGRATION_DATA/ctl1.f, +MIGRA
                                                 TION_DATA/ctl2.f, +MIGRATION_D
                                                 ATA/ctl3.f

SQL> show parameter recov

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +migration_recovery
db_recovery_file_dest_size           big integer 8G
recovery_parallelism                 integer     0

SQL> select member from v$logfile;

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


SQL> l
  1* alter database add logfile group 3 '+migration_data/redo1.log' size 50m
SQL> c/3/4/
  1* alter database add logfile group 4 '+migration_data/redo1.log' size 50m
SQL> l
  1* alter database add logfile group 4 '+migration_data/redo1.log' size 50m
SQL> r
  1* alter database add logfile group 4 '+migration_data/redo1.log' size 50m

Database altered.

SQL> c/4/5/
  1* alter database add logfile group 5 '+migration_data/redo1.log' size 50m
SQL> l     
  1* alter database add logfile group 5 '+migration_data/redo1.log' size 50m
SQL> c/1/2/
  1* alter database add logfile group 5 '+migration_data/redo2.log' size 50m
SQL> r
  1* alter database add logfile group 5 '+migration_data/redo2.log' size 50m

Database altered.

SQL> c/5/6/
  1* alter database add logfile group 6 '+migration_data/redo2.log' size 50m
SQL> l
  1* alter database add logfile group 6 '+migration_data/redo2.log' size 50m
SQL> c/2/3/
  1* alter database add logfile group 6 '+migration_data/redo3.log' size 50m
SQL> r
  1* alter database add logfile group 6 '+migration_data/redo3.log' size 50m

Database altered.

SQL>

 select group#,status,type,member from v$logfile
SQL> col member for a50
SQL> /

    GROUP# STATUS     TYPE    MEMBER
---------- ---------- ------- --------------------------------------------------
         3            ONLINE  /oracle/oradata/ora10g/redo03.log
         2            ONLINE  /oracle/oradata/ora10g/redo02.log
         1 STALE      ONLINE  /oracle/oradata/ora10g/redo01.log
         4            ONLINE  +MIGRATION_DATA/redo1.log
         5            ONLINE  +MIGRATION_DATA/redo2.log
         6            ONLINE  +MIGRATION_DATA/redo3.log



SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         1         50          1 YES INACTIVE
         2         50          1 YES INACTIVE
         3         50          1 NO  CURRENT
         4         50          1 YES UNUSED
         5         50          1 YES UNUSED
         6         50          1 YES UNUSED

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         1         50          1 YES INACTIVE
         2         50          1 YES INACTIVE
         3         50          1 YES ACTIVE
         4         50          1 NO  CURRENT
         5         50          1 YES UNUSED
         6         50          1 YES UNUSED

6 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> c/1/2/
  1* alter database drop logfile group 2
SQL> r
  1* alter database drop logfile group 2

Database altered.

SQL> c/2/3/
  1* alter database drop logfile group 3
SQL> r
  1* alter database drop logfile group 3  --发现没，要是某个日志组status为current or active,你是drop不掉，因为实例恢复要用，只能待到这个宝贝inactive了
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance ora10g (thread 1)
ORA-00312: online log 3 thread 1: '/oracle/oradata/ora10g/redo03.log'


SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         3         50          1 YES ACTIVE
         4         50          1 YES ACTIVE
         5         50          1 NO  CURRENT
         6         50          1 YES UNUSED

SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         3         50          1 YES ACTIVE
         4         50          1 YES ACTIVE
         5         50          1 YES ACTIVE
         6         50          1 NO  CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         3         50          1 NO  CURRENT
         4         50          1 YES INACTIVE
         5         50          1 YES INACTIVE
         6         50          1 YES INACTIVE

SQL> alter system switch logfile;

System altered.

SQL> select group#,bytes/1024/1024 mb,members,archived,status from v$log;

    GROUP#         MB    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------
         3         50          1 YES INACTIVE
         4         50          1 NO  CURRENT
         5         50          1 YES INACTIVE
         6         50          1 YES INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL>





---从asm迁移到non-asm
1,查迁移前相关信息
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ora10g/datafile/system.256.704104561
+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679
+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629
+MIGRATION_DATA/ora10g/datafile/users.260.704104693
+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ctl1.f
+MIGRATION_DATA/ctl2.f
+MIGRATION_DATA/ctl3.f

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+MIGRATION_DATA/ora10g/tempfile/temp1.265.704106533

SQL> select member from v$logfile

MEMBER
--------------------------------------------------------------------------------
+MIGRATION_DATA/redo1.log
+MIGRATION_DATA/redo2.log
+MIGRATION_DATA/redo3.log


SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +migration_recovery
db_recovery_file_dest_size           big integer 8G
recovery_parallelism                 integer     0
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +MIGRATION_DATA/spfile


2,利用rman对数据库（以asm存储）作一个全备，存储在non-asm上面
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 10:09:06 2009

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

connected to target database: ORA10G (DBID=4016423416)

RMAN> backup as copy incremental level 0 database format '/oracle/oradata/ora10g/%U9' tag 'ora_non_asm_migration';

Starting backup at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+MIGRATION_DATA/ora10g/datafile/system.256.704104561
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSTEM_FNO-1_4jkvfnm29 tag=ORA_NON_ASM_MIGRATION recid=20 stamp=704110311
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSAUX_FNO-3_4kkvfnng9 tag=ORA_NON_ASM_MIGRATION recid=21 stamp=704110345
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-ZXY_FNO-5_4lkvfnok9 tag=ORA_NON_ASM_MIGRATION recid=22 stamp=704110363
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-UNDOTBS1_FNO-2_4mkvfnp39 tag=ORA_NON_ASM_MIGRATION recid=23 stamp=704110376
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+MIGRATION_DATA/ora10g/datafile/users.260.704104693
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-USERS_FNO-4_4nkvfnpa9 tag=ORA_NON_ASM_MIGRATION recid=24 stamp=704110380
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-NOV-09

Starting Control File and SPFILE Autobackup at 28-NOV-09
piece handle=/oracle/c-4016423416-20091128-08 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-09

RMAN> sql 'alter system archive log current';--归档当前日志，便于后期完全数据库恢复

sql statement: alter system archive log current

RMAN>

3,通过rman,把spfile(asm存储）备份在non-asm上面
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 10:09:06 2009

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

connected to target database: ORA10G (DBID=4016423416)

RMAN> backup as copy incremental level 0 database format '/oracle/oradata/ora10g/%U9' tag 'ora_non_asm_migration';

Starting backup at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=+MIGRATION_DATA/ora10g/datafile/system.256.704104561
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSTEM_FNO-1_4jkvfnm29 tag=ORA_NON_ASM_MIGRATION recid=20 stamp=704110311
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=+MIGRATION_DATA/ora10g/datafile/sysaux.257.704104629
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSAUX_FNO-3_4kkvfnng9 tag=ORA_NON_ASM_MIGRATION recid=21 stamp=704110345
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-ZXY_FNO-5_4lkvfnok9 tag=ORA_NON_ASM_MIGRATION recid=22 stamp=704110363
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=+MIGRATION_DATA/ora10g/datafile/undotbs1.259.704104679
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-UNDOTBS1_FNO-2_4mkvfnp39 tag=ORA_NON_ASM_MIGRATION recid=23 stamp=704110376
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+MIGRATION_DATA/ora10g/datafile/users.260.704104693
output filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-USERS_FNO-4_4nkvfnpa9 tag=ORA_NON_ASM_MIGRATION recid=24 stamp=704110380
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-NOV-09

Starting Control File and SPFILE Autobackup at 28-NOV-09
piece handle=/oracle/c-4016423416-20091128-08 comment=NONE
Finished Control File and SPFILE Autobackup at 28-NOV-09

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN>

4,构建一个pfile文件（引用spfile,内容来自于上面的spfile备份）
bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 10:18:47 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

-bash-3.00$ more non_asm_pfile.ora
spfile=$ORACLE_HOME/dbs/spfile_nonasm.ora
-bash-3.00$

5,启动库以nomount，引用上面构建的pfile
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 10:22:38 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/oracle/non_asm_pfile.ora';
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1284316 bytes
Variable Size             264242980 bytes
Database Buffers          805306368 bytes
Redo Buffers                2908160 bytes
6,变更控制文件和恢复区域相关初始化参数
SQL> alter system set control_files='/oracle/oradata/ora10g/redo01.log','/oracle/oradata/ora10g/redo02.log','/oracle/oradata/ora10g/redo03.log' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=4g;

System altered.

SQL> alter system set db_recovery_file_dest='/oracle/newrecovery';
7,关库使上面变更生效
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit                  
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

8,通过rman把库启到nomount，准备开始恢复controlfile到non-asm,继之mount db,恢复db
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Sat Nov 28 10:30:28 2009

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

connected to target database (not started)

RMAN> startup nomount pfile='/oracle/non_asm_pfile.ora';

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     1284316 bytes
Variable Size                264242980 bytes
Database Buffers             805306368 bytes
Redo Buffers                   2908160 bytes
RMAN> restore controlfile from '+MIGRATION_DATA/ctl1.f';

Starting restore at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/oracle/oradata/ora10g/redo01.log
output filename=/oracle/oradata/ora10g/redo02.log
output filename=/oracle/oradata/ora10g/redo03.log
Finished restore at 28-NOV-09

RMAN> alter database mount;--mount数据库

database mounted
released channel: ORA_DISK_1



RMAN> run {
2> set newname for datafile 1 to '/oracle/oradata/ora10g/system01.dbf'; --多少个数据文件根据v$datafile
3> set newname for datafile 2 to '/oracle/oradata/ora10g/undotbs01.dbf';
4> set newname for datafile 3 to '/oracle/oradata/ora10g/sysaux01.dbf';
5> set newname for datafile 4 to '/oracle/oradata/ora10g/users01.dbf';
6> set newname for datafile 5 to '/oracle/oradata/ora10g/zxy.dbf';
7> restore database;--其实就是把上面作的rman全备再还原回来
8> switch datafile all;  --注意：switch all不同于switch datafile all,区别请参阅官档
9> }

executing command: SET NEWNAME
using target database control file instead of recovery catalog

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 28-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

datafile 5 not processed because file is offline
channel ORA_DISK_1: restoring datafile 00001
input datafile copy recid=20 stamp=704110311 filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSTEM_FNO-1_4jkvfnm29
destination for restore of datafile 00001: /oracle/oradata/ora10g/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output filename=/oracle/oradata/ora10g/system01.dbf recid=29 stamp=704113276
channel ORA_DISK_1: restoring datafile 00002
input datafile copy recid=23 stamp=704110376 filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-UNDOTBS1_FNO-2_4mkvfnp39
destination for restore of datafile 00002: /oracle/oradata/ora10g/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00002
output filename=/oracle/oradata/ora10g/undotbs01.dbf recid=30 stamp=704113287
channel ORA_DISK_1: restoring datafile 00003
input datafile copy recid=21 stamp=704110345 filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-SYSAUX_FNO-3_4kkvfnng9
destination for restore of datafile 00003: /oracle/oradata/ora10g/sysaux01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output filename=/oracle/oradata/ora10g/sysaux01.dbf recid=31 stamp=704113325
channel ORA_DISK_1: restoring datafile 00004
input datafile copy recid=24 stamp=704110380 filename=/oracle/oradata/ora10g/data_D-ORA10G_I-4016423416_TS-USERS_FNO-4_4nkvfnpa9
destination for restore of datafile 00004: /oracle/oradata/ora10g/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output filename=/oracle/oradata/ora10g/users01.dbf recid=32 stamp=704113332
Finished restore at 28-NOV-09

datafile 1 switched to datafile copy
input datafile copy recid=33 stamp=704113334 filename=/oracle/oradata/ora10g/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=34 stamp=704113334 filename=/oracle/oradata/ora10g/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=35 stamp=704113334 filename=/oracle/oradata/ora10g/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=36 stamp=704113334 filename=/oracle/oradata/ora10g/users01.dbf

RMAN>

RMAN> recover database;--恢复数据库，非常重要，一定要加上这个

Starting recover at 28-NOV-09
using channel ORA_DISK_1
datafile 5 not processed because file is offline

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

Finished recover at 28-NOV-09

RMAN>
RMAN> run {
2> set newname for tempfile 1 to '/oracle/oradata/ora10g/temp01.dbf';  ---to后面跟的是新的位置，切记
3> switch tempfile all;
4> }

executing command: SET NEWNAME

renamed temporary file 1 to /oracle/oradata/ora10g/temp01.dbf in control file

9,打开数据库
-bash-3.00$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Nov 28 11:05:02 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


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

SQL> alter database open;

Database altered.
10,查开迁移后相关信息
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora10g/redo01.log
/oracle/oradata/ora10g/redo02.log
/oracle/oradata/ora10g/redo03.log

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora10g/system01.dbf
/oracle/oradata/ora10g/undotbs01.dbf
/oracle/oradata/ora10g/sysaux01.dbf
/oracle/oradata/ora10g/users01.dbf
+MIGRATION_DATA/ora10g/datafile/zxy.258.704104663  ---迁移时，对于offline的表空间数据文件不进行迁移转化(所以操作前，要查开表空间的状态，是否要迁移此表空间）

SQL> select name from v$tempfile;

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

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+MIGRATION_DATA/redo1.log
+MIGRATION_DATA/redo2.log
+MIGRATION_DATA/redo3.log

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/product/10.2.0/db_1/db
                                                 s/spfile_nonasm.ora
SQL> show parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      /oracle/newrecovery
db_recovery_file_dest_size           big integer 4G
recovery_parallelism                 integer     0
SQL> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-620929/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-620929/