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>
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
!
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle xtts資料庫遷移方法測試之一OracleTTS資料庫
- 【遷移】使用rman遷移資料庫資料庫
- 資料庫遷移資料庫
- 檢測資料庫遷移準確性資料庫
- Core Data資料遷移及單元測試
- 資料庫遷移 ：理解資料庫
- laravel資料庫遷移Laravel資料庫
- Odoo遷移資料庫Odoo資料庫
- redis資料庫遷移Redis資料庫
- Cacti 遷移資料庫資料庫
- 資料庫遷移方案資料庫
- ORACLE資料庫遷移Oracle資料庫
- 遷移資料庫成功！資料庫
- 建立資料庫遷移資料庫
- expdp 遷移測試
- 行遷移測試
- 資料庫-oracle-資料庫遷移資料庫Oracle
- Oracle資料庫資料遷移流程Oracle資料庫
- 資料庫遷移神器——Flyway資料庫
- 遷移MySQL 5.7資料庫MySql資料庫
- 用rman遷移資料庫資料庫
- 資料庫遷移手記資料庫
- django資料庫遷移-15Django資料庫
- SQL Server資料庫遷移SQLServer資料庫
- mysql資料庫遷移 mysqldumpMySql資料庫
- 遷移資料庫到ASM資料庫ASM
- 同位元組序跨平臺資料庫遷移和升級的測試資料庫
- 同/不同庫遷移資料(在同使用者及表空間)測試
- 【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM資料庫ASM
- 把正式庫的最新資料全部遷移到測試庫上
- 雲資料庫管理與資料遷移資料庫
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 測試環境的遷移式升級和資料整合
- 騰訊雲 雲資料庫遷移資料庫
- laravel 資料庫遷移時報錯Laravel資料庫
- 資料庫邏輯遷移方案資料庫
- linux mysql資料庫遷移LinuxMySql資料庫
- 遷移資料庫到SQLonLinuxDocker資料庫SQLLinuxDocker