1、实验目标：
把一个单实例oracle11g 11.2.0.1的使用文件系统的库，迁移到asm的单实例环境中去。
2、磁盘组规划：
1.1 磁盘组规划说明：
+DG1 /dev/sdb1,/dev/sdc2 用于存放数据文件，控制文件，联机日志
+DG2 /dev/sdb3 用于存放联机日志
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/grid
原文链接：https://www.f2er.com/oracle/206006.html把一个单实例oracle11g 11.2.0.1的使用文件系统的库，迁移到asm的单实例环境中去。
2、磁盘组规划：
1.1 磁盘组规划说明：
+DG1 /dev/sdb1,/dev/sdc2 用于存放数据文件，控制文件，联机日志
+DG2 /dev/sdb3 用于存放联机日志
+RECOVERY /dev/sdb4,/dev/sdb5,/dev/sdb6 用于Recovery Area
3、文件位置说明
迁移前：
select name,'controlfile' from v$controlfile
union all
select name,status from v$datafile
union all
select member,'logfile' from v$logfile
union all
select name,status from v$tempfile;
/u01/app/oracle/product/11.2.0.1/db_home1/dbs/ 控制文件、初始化参数文件；
/u01/app/oracle/oradata/orcl/ 数据文件；
/u01/app/oracle/oradata/orcl/ 联机日志文件；
/u01/app/oracle/oradata/orcl/ 临时文件；
迁移后：
数据文件、控制文件 +DG1
联机日志 +DG1 +DG2
归档、备份 +RECOVERY
4、udev绑定
[root@srcxtts ~]# ll /dev/sdc* brw-rw----. 1 root disk 8,32 5月 10 09:19 /dev/sdc brw-rw----. 1 root disk 8,33 5月 10 09:19 /dev/sdc1 brw-rw----. 1 root disk 8,34 5月 10 09:19 /dev/sdc2 brw-rw----. 1 root disk 8,35 5月 10 09:19 /dev/sdc3 brw-rw----. 1 root disk 8,36 5月 10 09:19 /dev/sdc4 brw-rw----. 1 root disk 8,37 5月 10 09:19 /dev/sdc5 brw-rw----. 1 root disk 8,38 5月 10 09:19 /dev/sdc6 [root@srcxtts ~]#
修改/etc/udev/rules.d/60-raw.rules规则文件，新增如下内容：
[root@srcxtts ~]# cat /etc/udev/rules.d/60-raw.rules
# Enter raw device bindings here.
#
# An example would be:
# ACTION=="add",KERNEL=="sda",RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda,or
# ACTION=="add",ENV{MAJOR}=="8",ENV{MINOR}=="1",RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8,minor 1.
ACTION=="add",ENV{MINOR}=="33",RUN+="/bin/raw /dev/raw/raw1 %M %m"
ACTION=="add",ENV{MINOR}=="34",RUN+="/bin/raw /dev/raw/raw2 %M %m"
ACTION=="add",ENV{MINOR}=="35",RUN+="/bin/raw /dev/raw/raw3 %M %m"
ACTION=="add",ENV{MINOR}=="36",RUN+="/bin/raw /dev/raw/raw4 %M %m"
ACTION=="add",ENV{MINOR}=="37",RUN+="/bin/raw /dev/raw/raw5 %M %m"
ACTION=="add",ENV{MINOR}=="38",RUN+="/bin/raw /dev/raw/raw6 %M %m"
KERNEL=="raw[1-6]",OWNER="oracle",GROUP="oinstall",MODE="640"
[root@srcxtts ~]#
绑定完成后启动udev
start_udev
[root@srcxtts ~]# ll /dev/raw* 总用量 0 crw-r-----. 1 oracle oinstall 162,1 5月 10 09:19 raw1 crw-r-----. 1 oracle oinstall 162,2 5月 10 09:19 raw2 crw-r-----. 1 oracle oinstall 162,3 5月 10 09:19 raw3 crw-r-----. 1 oracle oinstall 162,4 5月 10 09:19 raw4 crw-r-----. 1 oracle oinstall 162,5 5月 10 09:19 raw5 crw-r-----. 1 oracle oinstall 162,6 5月 10 09:19 raw6 crw-rw----. 1 root disk 162,0 5月 10 09:19 rawctl [root@srcxtts ~]#一切正常。
如果只能看到rawctl，其它看不到或者看的不完整，请reboot。
5、安装grid
配置oracle用户环境变量（注意此次安装grid是以oracle用户进行的，并不是grid用户）：
vi /home/oracle/.bash_profile
新增GRID_HOME环境变量，并且在PATH中增加$GRID_HOME/bin
export GRID_HOME=/u01/app/oracle/product/11.2.0.4/grid
export PATH=$PATH:$GRID_HOME/bin
执行命令：
source .bash_profile
使profile文件立即生效。
6、启动数据库，并查看归档是否打开（因为后面需要rman备份，所以必须开归档）
[oracle@trgxtts grid]$ sqlplus / as sysdba sql*Plus: Release 11.2.0.1.0 Production on Wed May 9 22:31:06 2018 Copyright (c) 1982,2009,Oracle. All rights reserved. Connected to an idle instance. sql> startup ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2212936 bytes Variable Size 771754936 bytes Database Buffers 469762048 bytes Redo Buffers 8933376 bytes Database mounted. Database opened. sql> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 sql> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning,OLAP,Data Mining and Real Application Testing options
7、登录数据库，确定某个表，后面迁移完成后确定数据是否迁移成功。
[oracle@trgxtts grid]$ sqlplus / as sysdba sql*Plus: Release 11.2.0.1.0 Production on Wed May 9 22:32:15 2018 Copyright (c) 1982,Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning,Data Mining and Real Application Testing options sql> conn wufan/root123 Connected. sql> select * xttstest ; select * xttstest * ERROR at line 1: ORA-00923: FROM keyword not found where expected sql> select * from xttstest ; A ---------- 200 sql> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning,Data Mining and Real Application Testing options
[oracle@trgxtts grid]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 9 22:34:05 2018 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1502243959) RMAN> backup as copy database format '+DG1' ; Starting backup at 2018-05-09 22:34:26 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=141 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=20 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=146 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=23 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=144 device type=DISK allocated channel: ORA_DISK_7 channel ORA_DISK_7: SID=21 device type=DISK allocated channel: ORA_DISK_8 channel ORA_DISK_8: SID=142 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_2: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_3: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_4: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/test01.dbf channel ORA_DISK_5: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=+DG1/orcl/datafile/users.257.975710071 tag=TAG20180509T223427 RECID=3 STAMP=975710072 channel ORA_DISK_5: datafile copy complete,elapsed time: 00:00:07 output file name=+DG1/orcl/datafile/test.256.975710071 tag=TAG20180509T223427 RECID=4 STAMP=975710075 channel ORA_DISK_4: datafile copy complete,elapsed time: 00:00:07 output file name=+DG1/orcl/datafile/undotbs1.258.975710077 tag=TAG20180509T223427 RECID=5 STAMP=975710080 channel ORA_DISK_3: datafile copy complete,elapsed time: 00:00:15 output file name=+DG1/orcl/datafile/system.259.975710081 tag=TAG20180509T223427 RECID=7 STAMP=975710141 channel ORA_DISK_1: datafile copy complete,elapsed time: 00:01:16 output file name=+DG1/orcl/datafile/sysaux.260.975710087 tag=TAG20180509T223427 RECID=6 STAMP=975710135 channel ORA_DISK_2: datafile copy complete,elapsed time: 00:01:16 Finished backup at 2018-05-09 22:35:43 Starting Control File and SPFILE Autobackup at 2018-05-09 22:35:43 piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2018_05_09/o1_mf_s_975710143_fh61szw5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2018-05-09 22:35:44 RMAN> RMAN> quit Recovery Manager complete.
9、修改初始化参数文件，指定到对应的磁盘组即可（ASM会自动管理新建的磁盘组文件）
db_recovery_file_dest（归档文件、备份文件）、db_create_file_dest（数据文件）、db_create_online_log_dest_1（联机日志文件）、db_create_online_log_dest_2（联机日志文件）
[oracle@trgxtts grid]$ set ORACLE_SID=orcl [oracle@trgxtts grid]$ sqlplus / as sysdba sql*Plus: Release 11.2.0.1.0 Production on Wed May 9 22:36:16 2018 Copyright (c) 1982,Automatic Storage Management,Data Mining and Real Application Testing options sql> show parameter recovery NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 3882M recovery_parallelism integer 0 sql> alter system set db_recovery_file_dest='+RECOVERY' scope=both ; System altered. sql> show parameter recovery NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ db_recovery_file_dest string +RECOVERY db_recovery_file_dest_size big integer 3882M recovery_parallelism integer 0 sql> alter system set db_create_file_dest='+DG1' scope=both; System altered. sql> show parameter create NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ create_bitmap_area_size integer 8388608 create_stored_outlines string db_create_file_dest string +DG1 db_create_online_log_dest_1 string db_create_online_log_dest_2 string NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string sql> alter system set db_create_online_log_dest_1='+DG1' scope=both; System altered. sql> alter system set db_create_online_log_dest_2='+DG2' scope=both ; System altered.
10、迁移日志文件
sql> select group#,member from v$logfile; GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log sql> alter database add logfile member '+DG1','+DG2' to group 1 ; Database altered. sql> alter database add logfile member '+DG1','+DG2' to group 2 ; Database altered. sql> alter database add logfile member '+DG1','+DG2' to group 3 ; Database altered. sql> select group#,member from v$logfile; GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 1 +DG1/orcl/onlinelog/group_1.261.975710419 1 +DG2/orcl/onlinelog/group_1.256.975710421 2 +DG1/orcl/onlinelog/group_2.262.975710435 GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 2 +DG2/orcl/onlinelog/group_2.257.975710437 3 +DG1/orcl/onlinelog/group_3.263.975710473 3 +DG2/orcl/onlinelog/group_3.258.975710475 9 rows selected. sql> select group#,status from v$log; GROUP# STATUS ---------- -------------------------------- 1 INACTIVE 2 INACTIVE 3 CURRENT sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01.log'; alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01.log' * ERROR at line 1: ORA-00362: member is required to form a valid logfile in group 1 ORA-01517: log member: '/u01/app/oracle/oradata/orcl/redo01.log' sql> alter system switch logfile ; System altered. sql> select group#,status from v$log; GROUP# STATUS ---------- -------------------------------- 1 CURRENT 2 INACTIVE 3 ACTIVE sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02.log' ; alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02.log' * ERROR at line 1: ORA-00362: member is required to form a valid logfile in group 2 ORA-01517: log member: '/u01/app/oracle/oradata/orcl/redo02.log' sql> select group#,status,archived,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 CURRENT NO 3 2 INACTIVE YES 3 3 ACTIVE YES 3 sql> col member format a45 sql> select group#,member from v$logfile; GROUP# STATUS MEMBER ---------- -------------- --------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log 1 +DG1/orcl/onlinelog/group_1.261.975710419 1 +DG2/orcl/onlinelog/group_1.256.975710421 2 INVALID +DG1/orcl/onlinelog/group_2.262.975710435 2 INVALID +DG2/orcl/onlinelog/group_2.257.975710437 3 INVALID +DG1/orcl/onlinelog/group_3.263.975710473 3 INVALID +DG2/orcl/onlinelog/group_3.258.975710475 9 rows selected. sql> alter system switch logfile ; System altered. sql> alter system switch logfile ; System altered. sql> alter system switch logfile ; System altered. sql> alter system switch logfile ; System altered. sql> select group#,member from v$logfile; GROUP# STATUS MEMBER ---------- -------------- --------------------------------------------- 3 /u01/app/oracle/oradata/orcl/redo03.log 2 /u01/app/oracle/oradata/orcl/redo02.log 1 /u01/app/oracle/oradata/orcl/redo01.log 1 +DG1/orcl/onlinelog/group_1.261.975710419 1 +DG2/orcl/onlinelog/group_1.256.975710421 2 +DG1/orcl/onlinelog/group_2.262.975710435 2 +DG2/orcl/onlinelog/group_2.257.975710437 3 +DG1/orcl/onlinelog/group_3.263.975710473 3 +DG2/orcl/onlinelog/group_3.258.975710475 9 rows selected. sql> select group#,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 INACTIVE YES 3 2 CURRENT NO 3 3 INACTIVE YES 3 sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo01.log'; Database altered. sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo03.log'; Database altered. sql> alter system switch logfile ; System altered. sql> select group#,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 INACTIVE YES 2 2 ACTIVE YES 3 3 CURRENT NO 2 sql> alter system switch logfile ; System altered. sql> select group#,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 CURRENT NO 2 2 ACTIVE YES 3 3 ACTIVE YES 2 sql> alter system switch logfile ; System altered. sql> select group#,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 INACTIVE YES 2 2 CURRENT NO 3 3 INACTIVE YES 2 sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02.log'; alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02.log' * ERROR at line 1: ORA-01609: log 2 is the current log for thread 1 - cannot drop members ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log' ORA-00312: online log 2 thread 1: '+DG1/orcl/onlinelog/group_2.262.975710435' ORA-00312: online log 2 thread 1: '+DG2/orcl/onlinelog/group_2.257.975710437' sql> alter system switch logfile ; System altered. sql> select group#,members from v$log ; GROUP# STATUS ARCHIV MEMBERS ---------- -------------------------------- ------ ---------- 1 INACTIVE YES 2 2 INACTIVE YES 3 3 CURRENT NO 2 sql> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo02.log'; Database altered. sql> select group#,member from v$logfile; GROUP# STATUS MEMBER ---------- -------------- --------------------------------------------- 1 +DG1/orcl/onlinelog/group_1.261.975710419 1 +DG2/orcl/onlinelog/group_1.256.975710421 2 +DG1/orcl/onlinelog/group_2.262.975710435 2 +DG2/orcl/onlinelog/group_2.257.975710437 3 +DG1/orcl/onlinelog/group_3.263.975710473 3 +DG2/orcl/onlinelog/group_3.258.975710475 6 rows selected. sql>
11、迁移临时文件
sql> select ts#,bytes/1024/1024,name from v$tempfile; TS# BYTES/1024/1024 ---------- --------------- NAME -------------------------------------------------------------------------------- 3 29 /u01/app/oracle/oradata/orcl/temp01.dbf sql> select ts#,name from v$tablespace; TS# NAME ---------- ------------------------------------------------------------ 0 SYSTEM 1 SYSAUX 2 UNDOTBS1 4 USERS 3 TEMP 7 TEST 6 rows selected. sql> alter tablespace temp add tempfile size 30m; Tablespace altered. sql> select ts#,name from v$tempfile; TS# BYTES/1024/1024 ---------- --------------- NAME -------------------------------------------------------------------------------- 3 30 +DG1/orcl/tempfile/temp.264.975711183 3 29 /u01/app/oracle/oradata/orcl/temp01.dbf sql> alter database '/u01/app/oracle/oradata/orcl/temp01.dbf' drop ; alter database '/u01/app/oracle/oradata/orcl/temp01.dbf' drop * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE sql> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' drop ; Database altered. sql> select ts#,name from v$tempfile; TS# BYTES/1024/1024 ---------- --------------- NAME -------------------------------------------------------------------------------- 3 30 +DG1/orcl/tempfile/temp.264.975711183 sql>
sql> show parameter control NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ control_file_record_keep_time integer 7 control_files string /u01/app/oracle/oradata/orcl/c ontrol01.ctl,/u01/app/oracle/ flash_recovery_area/orcl/contr ol02.ctl control_management_pack_access string DIAGNOSTIC+TUNING sql> alter system set control_files='+DG1/oracle/oradata/orcl/control01.ctl','+DG2/oracle/oradata/orcl/control02.ctl' scope=spfile; System altered. sql> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sql> startup nomount ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2212936 bytes Variable Size 771754936 bytes Database Buffers 469762048 bytes Redo Buffers 8933376 bytes sql> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning,Data Mining and Real Application Testing options
在通过rman对控制文件进行恢复，从备份的原控制文件恢复到新修改的磁盘组的位置。控制文件restore后，即可mount数据库。然后switch数据库到备份的库下面去，然后进行recover前滚，前滚完成后即可打开数据库。
[oracle@trgxtts grid]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Wed May 9 23:00:47 2018 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile from '/u01/app/oracle/oradata/orcl/c 2> ontrol01.ctl'; Starting restore at 2018-05-09 23:01:16 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK channel ORA_DISK_1: copied control file copy output file name=+DG1/oracle/oradata/orcl/control01.ctl output file name=+DG2/oracle/oradata/orcl/control02.ctl Finished restore at 2018-05-09 23:01:19 RMAN> alter database mount ; database mounted released channel: ORA_DISK_1 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DG1/orcl/datafile/system.259.975710081" datafile 2 switched to datafile copy "+DG1/orcl/datafile/sysaux.260.975710087" datafile 3 switched to datafile copy "+DG1/orcl/datafile/undotbs1.258.975710077" datafile 4 switched to datafile copy "+DG1/orcl/datafile/users.257.975710071" datafile 5 switched to datafile copy "+DG1/orcl/datafile/test.256.975710071" RMAN> recover database ; Starting recover at 2018-05-09 23:02:11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=11 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=139 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=12 device type=DISK allocated channel: ORA_DISK_5 channel ORA_DISK_5: SID=140 device type=DISK allocated channel: ORA_DISK_6 channel ORA_DISK_6: SID=13 device type=DISK allocated channel: ORA_DISK_7 channel ORA_DISK_7: SID=141 device type=DISK allocated channel: ORA_DISK_8 channel ORA_DISK_8: SID=14 device type=DISK datafile 5 not processed because file is read-only starting media recovery archived log for thread 1 with sequence 6 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_6.256.975710569 archived log for thread 1 with sequence 7 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_7.257.975710795 archived log for thread 1 with sequence 8 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_8.258.975710801 archived log for thread 1 with sequence 9 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_9.259.975710803 archived log for thread 1 with sequence 10 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_10.260.975710805 archived log for thread 1 with sequence 11 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_11.261.975710893 archived log for thread 1 with sequence 12 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_12.262.975710927 archived log for thread 1 with sequence 13 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_13.263.975710945 archived log for thread 1 with sequence 14 is already on disk as file +RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_14.264.975710993 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_6.256.975710569 thread=1 sequence=6 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_7.257.975710795 thread=1 sequence=7 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_8.258.975710801 thread=1 sequence=8 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_9.259.975710803 thread=1 sequence=9 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_10.260.975710805 thread=1 sequence=10 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_11.261.975710893 thread=1 sequence=11 archived log file name=+RECOVERY/orcl/archivelog/2018_05_09/thread_1_seq_12.262.975710927 thread=1 sequence=12 media recovery complete,elapsed time: 00:00:05 Finished recover at 2018-05-09 23:02:18 RMAN> alter database open ; database opened RMAN>
13、查看所有的文件是否已经都切换到asm磁盘组了。
sql> col name format a50; sql> select name,'controlfile' from v$controlfile 2 union all 3 select name,status from v$datafile 4 union all 5 select member,'logfile' from v$logfile 6 union all 7 select name,status from v$tempfile; NAME 'CONTROLFILE' -------------------------------------------------- ---------------------- +DG1/oracle/oradata/orcl/control01.ctl controlfile +DG2/oracle/oradata/orcl/control02.ctl controlfile +DG1/orcl/datafile/system.259.975710081 SYSTEM +DG1/orcl/datafile/sysaux.260.975710087 ONLINE +DG1/orcl/datafile/undotbs1.258.975710077 ONLINE +DG1/orcl/datafile/users.257.975710071 ONLINE +DG1/orcl/datafile/test.256.975710071 ONLINE +DG1/orcl/onlinelog/group_1.261.975710419 logfile +DG2/orcl/onlinelog/group_1.256.975710421 logfile +DG1/orcl/onlinelog/group_2.262.975710435 logfile +DG2/orcl/onlinelog/group_2.257.975710437 logfile NAME 'CONTROLFILE' -------------------------------------------------- ---------------------- +DG1/orcl/onlinelog/group_3.263.975710473 logfile +DG2/orcl/onlinelog/group_3.258.975710475 logfile +DG1/orcl/tempfile/temp.264.975711183 ONLINE 14 rows selected. sql>
14、验证数据
sql> conn wufan/root123 Connected. sql> select * from xttstest ; A ---------- 200 sql>
15、关于本次迁移后ASM实例的登录说明：
因为本次的database和grid都是安装在oracle用户下的，所以要登录两个用户要区分不同环境变量：
比如，登录database实例，如下设置：
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home1
sqlplus / as sysdba
[oracle@trgxtts grid]$ export ORACLE_SID=orcl [oracle@trgxtts grid]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/db_home1 [oracle@trgxtts grid]$ echo $ORACLE_SID orcl [oracle@trgxtts grid]$ sqlplus / as sysdba sql*Plus: Release 11.2.0.1.0 Production on Sat May 12 13:20:43 2018 Copyright (c) 1982,Data Mining and Real Application Testing options sql> select instance_name,status from v$instance ; INSTANCE_NAME STATUS -------------------------------- ------------------------ orcl OPEN sql>如果要登录ASM的实例，则如下：
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/grid
sqlplus / as sysasm
[oracle@trgxtts grid]$ export ORACLE_SID=+ASM [oracle@trgxtts grid]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/grid [oracle@trgxtts grid]$ sqlplus / as sysasm sql*Plus: Release 11.2.0.1.0 Production on Sat May 12 13:22:34 2018 Copyright (c) 1982,Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Automatic Storage Management option sql> select instance_name,status from v$instance ; INSTANCE_NAME STATUS -------------------------------- ------------------------ +ASM STARTED sql>
同理，执行asmcmd也需要设置成访问ASM实例一样的设置。
16、关于spfile文件
sql> startup nomount ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2212936 bytes Variable Size 771754936 bytes Database Buffers 469762048 bytes Redo Buffers 8933376 bytes sql> show parameter spfile NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string /u01/app/oracle/product/11.2.0 .1/db_home1/dbs/spfileorcl.ora sql> create pfile='/tmp/initORCL.ora' from spfile ; File created. sql> create spfile='+DG1' from pfile='/tmp/initORCL.ora' ; File created. sql>
[oracle@trgxtts ~]$ asmcmd ASMCMD> ls DG1/ DG2/ RECOVERY/ ASMCMD> cd DG1 ASMCMD> ls ASM/ ORCL/ oracle/ ASMCMD> cd orcl ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ ASMCMD> cd PARAMETERFILE ASMCMD> ls spfile.266.975940005 ASMCMD> pwd +DG1/orcl/PARAMETERFILE ASMCMD> ls spfile.266.975940005 ASMCMD>
新建完成后，新建initorcl.ora
viinitorcl.ora
spfile=+DG1/orcl/PARAMETERFILE/spfile.266.975940005重新startup数据库指定pfile文件即可。
sql> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. sql> startup pfile=/u01/app/oracle/product/11.2.0.1/db_home1/dbs/initorcl.ora ORACLE instance started. Total System Global Area 1252663296 bytes Fixed Size 2212936 bytes Variable Size 771754936 bytes Database Buffers 469762048 bytes Redo Buffers 8933376 bytes Database mounted. Database opened. sql> show parameter pfile NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string +DG1/orcl/parameterfile/spfile .266.975940005 sql>
