oracle11gR2单实例由文件系统迁移到ASM磁盘组

最新推荐文章于 2024-05-31 08:26:18 发布

kadwf123

最新推荐文章于 2024-05-31 08:26:18 发布

阅读量891

点赞数

分类专栏： oracle 文章标签： oracle asm

本文链接：https://blog.csdn.net/kadwf123/article/details/80290944

版权

oracle 专栏收录该内容

58 篇文章 0 订阅

订阅专栏

1、实验目标：
把一个单实例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{MAJOR}=="8",ENV{MINOR}=="33",RUN+="/bin/raw /dev/raw/raw1 %M %m"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="34",RUN+="/bin/raw /dev/raw/raw2 %M %m"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="35",RUN+="/bin/raw /dev/raw/raw3 %M %m"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="36",RUN+="/bin/raw /dev/raw/raw4 %M %m"
ACTION=="add", ENV{MAJOR}=="8",ENV{MINOR}=="37",RUN+="/bin/raw /dev/raw/raw5 %M %m"
ACTION=="add", ENV{MAJOR}=="8",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, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, 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, OLAP, Data Mining and Real Application Testing options

8、登录rman，备份数据库

[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, 2009, 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, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, 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#,status,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#,status,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#, status,archived,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#, status,archived,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#, status,archived,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#, status,archived,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#, status,archived,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#,status,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#, bytes/1024/1024, 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#, bytes/1024/1024, name from v$tempfile;

       TS# BYTES/1024/1024
---------- ---------------
NAME
--------------------------------------------------------------------------------
         3              30
+DG1/orcl/tempfile/temp.264.975711183


SQL>

12、迁移控制文件、数据文件。

先修改初始化参数文件到磁盘组：

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, OLAP, 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, 2009, 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, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, 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, 2009, 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

vi initorcl.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>

kadwf123

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
oracle11gR2单实例由文件系统迁移到ASM磁盘组

1、实验目标：把一个单实例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...
复制链接

扫一扫

专栏目录