用rman从文件系统迁移数据库到asm
用rman从文件系统迁移数据库到asm
1、备份数据库
[oracle@haozg dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 5 14:12:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11 (DBID=729090162)
RMAN> run{
allocate channel c1 type disk maxpiecesize=500m;
backup current controlfile format '/oracle/backup/ctl_%d_%s';
backup full database format '/oracle/backup/db_%d_%s_%p_%t';
sql 'alter system archive log current';
release channel c1;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=36 device type=DISK
Starting backup at 05-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/ctl_ORA11_1 tag=TAG20120605T141421 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-12
Starting backup at 05-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/ora11/system01.dbf
input datafile file number=00002 name=/oracle/oradata/ora11/sysaux01.dbf
input datafile file number=00003 name=/oracle/oradata/ora11/undotbs01.dbf
input datafile file number=00004 name=/oracle/oradata/ora11/users01.dbf
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_1_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: starting piece 2 at 05-JUN-12
channel c1: finished piece 2 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_2_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: starting piece 3 at 05-JUN-12
channel c1: finished piece 3 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_2_3_785168069 tag=TAG20120605T141429 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:09
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 05-JUN-12
channel c1: finished piece 1 at 05-JUN-12
piece handle=/oracle/backup/db_ORA11_3_1_785168198 tag=TAG20120605T141429 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-JUN-12
sql statement: alter system archive log current
released channel: c1
RMAN>
2、修改控制文件位置参数
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oracle/oradata/ora11/control0
1.ctl, /oracle/flash_recovery_
area/ora11/control02.ctl
SQL> alter system set control_files='+DGASM/controlfile/control01.ctl' scope=spfile;
System altered.
3、恢复控制文件 在nomount 下
注意oracle 用户必须在asmadmin、asmdba、等grid用户所在组，否则在转储控制文件会提示权限不足
RMAN> restore controlfile from '/oracle/oradata/ora11/control01.ctl';
Starting restore at 05-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DGASM/controlfile/control01.ctl
Finished restore at 05-JUN-12
RMAN>
4、RMAN copy数据库到ASM
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
backup as copy database format '+DGASM';
RMAN> backup as copy database format '+DGASM';
Starting backup at 05-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/oradata/ora11/system01.dbf
output file name=+DGASM/ora11/datafile/system.257.785186755 tag=TAG20120605T192553 RECID=2 STAMP=785186834
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:29
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/ora11/sysaux01.dbf
output file name=+DGASM/ora11/datafile/sysaux.258.785186845 tag=TAG20120605T192553 RECID=3 STAMP=785186894
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/oradata/ora11/undotbs01.dbf
output file name=+DGASM/ora11/datafile/undotbs1.259.785186901 tag=TAG20120605T192553 RECID=4 STAMP=785186908
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DGASM/ora11/controlfile/backup.260.785186917 tag=TAG20120605T192553 RECID=5 STAMP=785186918
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/oradata/ora11/users01.dbf
output file name=+DGASM/ora11/datafile/users.261.785186921 tag=TAG20120605T192553 RECID=6 STAMP=785186920
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-JUN-12
channel ORA_DISK_1: finished piece 1 at 05-JUN-12
piece handle=+DGASM/ora11/backupset/2012_06_05/nnsnf0_tag20120605t192553_0.262.785186923 tag=TAG20120605T192553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUN-12
RMAN>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora11/system01.dbf
/oracle/oradata/ora11/sysaux01.dbf
/oracle/oradata/ora11/undotbs01.dbf
/oracle/oradata/ora11/users01.dbf
SQL>
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DGASM/ora11/datafile/system.257.785186755"
datafile 2 switched to datafile copy "+DGASM/ora11/datafile/sysaux.258.785186845"
datafile 3 switched to datafile copy "+DGASM/ora11/datafile/undotbs1.259.785186901"
datafile 4 switched to datafile copy "+DGASM/ora11/datafile/users.261.785186921"
RMAN>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DGASM/ora11/datafile/system.257.785186755
+DGASM/ora11/datafile/sysaux.258.785186845
+DGASM/ora11/datafile/undotbs1.259.785186901
+DGASM/ora11/datafile/users.261.785186921
SQL>
RMAN> alter database open;
database opened
5、切换日志文件 在open下
alter database drop logfile group 1;
Alter database add logfile group 1 ('+dgasm/ora11/redofile') size 50m;
alter database rename file '/oracle/oradata/ora11/redo01.log' to '+dgasm/ora11/redofile';
alter database rename file '/oracle/oradata/ora11/redo02.log' to '+DGASM/redofile';
alter database rename file '/oracle/oradata/ora11/redo03.log' to '+DGASM/redofile';
alter database drop logfile group 1;
Alter database add logfile group 1 ('+dgasm/ora11/redo') size 50m;
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
SQL> alter database drop logfile group 1;
Database altered.
SQL> Alter database add logfile group 1 ('+dgasm') size 50m;
Database altered.
SQL>
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('+dgasm') size 50m;
Database altered.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 ACTIVE
SQL>
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('+dgasm') size 50m;
Database altered.
SQL>
SQL>
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 CURRENT
SQL>
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DGASM/ora11/onlinelog/group_3.265.785192859
+DGASM/ora11/onlinelog/group_2.264.785192615
+DGASM/ora11/onlinelog/group_1.263.785192081
SQL>
6、迁移参数文件spfile
SQL> create spfile='+dgasm' from pfile;
File created.
[oracle@haozg dbs]$ rm -rf initora11.ora
[oracle@haozg dbs]$ rm -rf spfileora11.ora
[oracle@haozg dbs]$ vi initora11.ora
spfile='+dgasm/DB_UNKNOWN/PARAMETERFILE/SPFILE.266.785194197'
[oracle@haozg dbs]$ ls
hc_DBUA0.dat init.ora initora11.ora.bak orapwora11 peshm_ora11_0 spfileora11.ora.bak
hc_ora11.dat initora11.ora lkORA11 peshm_DBUA0_0 snapcf_ora11.f
[oracle@haozg dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 5 21:33:23 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
Database mounted.
Database opened.
SQL>
7、修改归档位置
略
8、增加temp文件
alter tablespace temp add tempfile '+dgasm';
alter tablespace temp drop tempfile '/oracle/oradata/ora11/temp01.dbf';
过程如下：
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/ora11/temp01.dbf
SQL> alter tablespace temp add tempfile '+dgasm';
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/oracle/oradata/ora11/temp01.dbf';
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DGASM/ora11/tempfile/temp.267.785196481
SQL>
SQL>
到此完成数据库的迁移，归档就不做修改了
