迁移ASM磁盘组（asm磁盘组扩容）

#开启归档

SQL> startup mountORACLE instance started.Total System Global Area 1586708480 bytesFixed Size                  2253624 bytesVariable Size            1426066632 bytesDatabase Buffers          150994944 bytesRedo Buffers                7393280 bytesDatabase mounted.SQL>  alter database archivelog;Database altered.SQL> alter system set log_archive_dest_1='location=+data02';System altered.SQL> alter database open;Database altered.#启动第二个节点的实例[root@ray32 /]# /u01/app/gridhome/product/11.2.0/grid_1/bin/srvctl start instance -d racdb -n ray32

#copy数据文件到新磁盘组，并迁移

rman target / log='/home/oracle/rman.log'RMAN> backup as copy database format '+data02';#关闭数据库，并启动到mount[oracle@ray31 ~]$ sqlplus /nologSQL> conn / as sysdbaConnected to an idle instance.SQL> startup mountORACLE instance started.Total System Global Area 1586708480 bytesFixed Size                  2253624 bytesVariable Size            1426066632 bytesDatabase Buffers          150994944 bytesRedo Buffers                7393280 bytesDatabase mounted.SQL>#迁移数据文件[oracle@ray31 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 18 10:55:41 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: RACDB (DBID=1016754416, not open)RMAN> switch database to copy;using target database control file instead of recovery catalogdatafile 1 switched to datafile copy "+DATA02/racdb/datafile/system.257.1024656613"datafile 2 switched to datafile copy "+DATA02/racdb/datafile/sysaux.258.1024656647"datafile 3 switched to datafile copy "+DATA02/racdb/datafile/undotbs1.259.1024656673"datafile 4 switched to datafile copy "+DATA02/racdb/datafile/undotbs2.260.1024656699"datafile 5 switched to datafile copy "+DATA02/racdb/datafile/users.262.1024656717"

#迁移控制文件

[oracle@ray31 ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 18 11:01:44 2019Copyright (c) 1982, 2013, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected.SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL>[grid@ray31 ~]$ asmcmdASMCMD> cp +DATA01/racdb/controlfile/current.256.1019137651 +DATA02/racdb/controlfile/current.oracopying +DATA01/racdb/controlfile/current.256.1019137651 -> +DATA02/racdb/controlfile/current.ora

#编辑参数文件

SQL> create pfile ='/home/oracle/pfileracdb.ora' from spfile;File created.#编辑参数文件*.control_files=' +DATA02/racdb/controlfile/current.ora'  #磁盘组名称替换为新的磁盘组名称*.db_block_size=8192*.db_create_file_dest=' +DATA02'   #磁盘组名称替换为新的磁盘组名称SQL> create spfile='+DATA02/racdb/spfileRACDB.ora' from pfile='/home/oracle/pfileracdb.ora';File created.#更新spfile[root@ray32 /]# /u01/app/gridhome/product/11.2.0/grid_1/bin/srvctl modify database -d racdb -p  +DATA02/racdb/spfileracdb.ora[root@ray32 /]# /u01/app/gridhome/product/11.2.0/grid_1/bin/srvctl config database -d racdbDatabase unique name: RACDBDatabase name: RACDBOracle home: /u01/app/oracle/product/11.2.0/db_1Oracle user: oracleSpfile: +DATA02/racdb/spfileracdb.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: RACDBDatabase instances: RACDB1,RACDB2Disk Groups: DATA01Mount point paths:Services:Type: RACDatabase is administrator managed#更新本地init$ORACLE_SID.ora,两个节点都要更新[oracle@ray31 dbs]$ cat initRACDB2.oraSPFILE='+DATA02/RACDB/spfileRACDB.ora'[oracle@ray32 dbs]$ cat initRACDB2.oraSPFILE='+DATA02/RACDB/spfileRACDB.ora'

#迁移临时数据文件

SQL> alter database rename file '+DATA01/racdb/tempfile/temp.262.1019137685' to '+data02';Database altered.SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------+DATA02SQL>

#启动数据库

run{recover database;alter database open;}Recovery Manager complete.[oracle@ray31 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 18 13:40:42 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: RACDB (DBID=1016754416, not open)RMAN> run2> {3> recover database;4> alter database open;5>  }Starting recover at 18-NOV-19using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=36 instance=RACDB1 device type=DISKstarting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 18-NOV-19database opened#检查所有文件的位置[oracle@ray31 dbs]$ sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 18 13:41:31 2019Copyright (c) 1982, 2013, Oracle.  All rights reserved.SQL> conn / as sysdbaConnected.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DATA02/racdb/datafile/system.257.1024656613+DATA02/racdb/datafile/sysaux.258.1024656647+DATA02/racdb/datafile/undotbs1.259.1024656673+DATA02/racdb/datafile/undotbs2.260.1024656699+DATA02/racdb/datafile/users.262.1024656717SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------+DATA02/racdb/tempfile/temp.266.1024666863SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------+DATA02/racdb/controlfile/current.oraSQL> show parameter pfileNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------spfile                               string      +DATA02/racdb/spfileracdb.oraSQL> show parameter db_create_file_destNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_create_file_dest                  string      +DATA02

#迁移redo日志组

SQL> set linesize 500SQL> col group# for 999SQL> col mb for 9999SQL> col member for a60SQL> col thread# for 999SQL> col archived for a10SQL> select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP#    group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;GROUP#    MB MEMBER                                                       THREAD#  SEQUENCE#    MEMBERS ARCHIVED   STATUS           FIRST_TIM NEXT_TIME------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- --------- ---------     1    50 +DATA01/racdb/onlinelog/group_1.257.1019137653                     1         57          1 NO         CURRENT          18-NOV-19     2    50 +DATA01/racdb/onlinelog/group_2.258.1019137653                     1         56          1 YES        INACTIVE         14-NOV-19 18-NOV-19     3    50 +DATA01/racdb/onlinelog/group_3.265.1019139563                     2          5          1 YES        INACTIVE         18-NOV-19 18-NOV-19     4    50 +DATA01/racdb/onlinelog/group_4.266.1019139563                     2          6          1 YES        INACTIVE         18-NOV-19 18-NOV-19SQL> alter database add logfile thread 2 group 5 '+data02' size 200M ;Database altered.SQL> SQL> alter database add logfile thread 2 group 6 '+data02' size 200M ;Database altered.SQL> alter database drop logfile group 3;Database altered.SQL> SQL> alter database add logfile thread 1 group 3 '+data02' size 200M ;Database altered.SQL> SQL> alter database drop logfile group 2;Database altered.SQL> SQL> alter database add logfile thread 1 group 2 '+data02' size 200M ;Database altered.SQL> alter system switch logfile;System altered.SQL> alter system checkpoint;System altered.SQL> alter database drop logfile group 1;Database altered.SQL> alter database add logfile thread 1 group 1 '+data02' size 200M ;Database altered.SQL> alter database drop logfile group 4;Database altered.SQL> alter database add logfile thread 2 group 4 '+data02' size 200M ;Database altered.SQL> select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP#  2      group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#;GROUP#    MB MEMBER                                                       THREAD#  SEQUENCE#    MEMBERS ARCHIVED   STATUS           FIRST_TIM NEXT_TIME------ ----- ------------------------------------------------------------ ------- ---------- ---------- ---------- ---------------- --------- ---------     1   200 +DATA02/racdb/onlinelog/group_1.274.1024667599                     1          0          1 YES        UNUSED     2   200 +DATA02/racdb/onlinelog/group_2.272.1024667447                     1         58          1 NO         CURRENT          18-NOV-19     3   200 +DATA02/racdb/onlinelog/group_3.271.1024667425                     1          0          1 YES        UNUSED     4   200 +DATA02/racdb/onlinelog/group_4.270.1024667761                     2          0          1 YES        UNUSED     5   200 +DATA02/racdb/onlinelog/group_5.268.1024667359                     2          7          1 NO         CURRENT          18-NOV-19 18-NOV-19     6   200 +DATA02/racdb/onlinelog/group_6.269.1024667367                     2          0          1 YES        UNUSED6 rows selected.