Oracle 12c 遷移MGMTDB 到其他的磁碟組

mgmtdb 是oracle12c中 負責叢集資訊維護的新增了一個資料庫，這個資料庫檔案預設是放在ocrvote磁碟組上，所有當你安裝12c GI 的時候你會發現oracle資料要ocr磁碟明顯大了幾G ,其實主要還是這個mgmtdb原因，這次遷移我就遷移到datadg裡 其實最好還是單獨建立dg 例如建立dg 名字為MGMTDG,這裡測試的版本為12.1.0.2 ，估計到12.2的時候 GI 安裝的時候mgmtdb oracle會推薦單獨安裝到一個磁碟組裡。

這裡要注意一點就是mgmtdb 是grid使用者下面操作需要export  ORACLE_SID=-MGMTDB
這樣可以登入進資料庫
遷移重要步驟如下
1  備份資料庫
2 關閉mgmt監聽
3 還原spfile 到新dg
4 設定controlfile為新的dg
5 還原controlfile 到新dg 
6 backup db 到新的dg
7 switch copy
8 遷移臨時檔案到新dg
9 修改db_create_file_dest 為新dg
10 更新redo日誌檔案路徑
11 檢視更新叢集中mgmtdb資訊
12 重啟驗證


下面是測試主要步驟

1 檢視目前存在的磁碟組


 
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     20480    14985                0           14985              0             N  DATADG/
MOUNTED  NORMAL  N         512   4096  1048576     12288     8445             4096            2174              0             Y  OCRVOTE/
2  檢視當前mgmtdb狀態和配置
[root@qc1 bin]# ./srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node qc1
[root@qc1 bin]# ./srvctl config mgmtdb
Database unique name: _mgmtdb
Database name: 
Oracle home:
Oracle user: grid
Spfile: +OCRVOTE/_MGMTDB/PARAMETERFILE/spfile.267.882365357
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Type: Management
PDB name: qc_cluster
PDB service: qc_cluster
Cluster name: qc-cluster
Database instance: -MGMTDB
[root@qc1 bin]# 


3 檢視mgmtdb監聽
qc1[/home/grid]srvctl status mgmtlsnr
Listener MGMTLSNR is enabled
Listener MGMTLSNR is running on node(s): qc1
qc1[/home/grid]srvctl stop mgmtlsnr

4 建立臨時資料庫備份目錄
mkdir -p /home/grid/backup_mgmtdb
先做一下資料庫rman 備份
qc1[/home/grid]export ORACLE_SID=-MGMTDB
qc1[/home/grid]rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 10:03:02 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area     788529152 bytes
Fixed Size                     2929352 bytes
Variable Size                314576184 bytes
Database Buffers             465567744 bytes
Redo Buffers                   5455872 bytes

5 備份資料庫

RMAN> backup database format '/home/grid/backup_mgmtdb/rman_mgmtdb_%U' tag='bk_mgmtdb_dg';


Starting backup at 2015-06-15 10:04:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+OCRVOTE/_MGMTDB/DATAFILE/system.257.882365057
input datafile file number=00003 name=+OCRVOTE/_MGMTDB/DATAFILE/sysaux.256.882365025
input datafile file number=00004 name=+OCRVOTE/_MGMTDB/DATAFILE/undotbs1.258.882365103
channel ORA_DISK_1: starting piece 1 at 2015-06-15 10:04:40
channel ORA_DISK_1: finished piece 1 at 2015-06-15 10:05:15
piece handle=/home/grid/backup_mgmtdb/rman_mgmtdb_01q9ht9n_1_1 tag=BK_MGMTDB_DG comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+OCRVOTE/_MGMTDB/FD9AC0F7C36E4438E043B6A9E80A24D5/DATAFILE/system.264.882365201
input datafile file number=00006 name=+OCRVOTE/_MGMTDB/FD9AC0F7C36E4438E043B6A9E80A24D5/DATAFILE/sysaux.265.882365201
channel ORA_DISK_1: starting piece 1 at 2015-06-15 10:05:15
channel ORA_DISK_1: finished piece 1 at 2015-06-15 10:05:40
piece handle=/home/grid/backup_mgmtdb/rman_mgmtdb_02q9htar_1_1 tag=BK_MGMTDB_DG comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2015-06-15 10:05:40
Starting Control File and SPFILE Autobackup at 2015-06-15 10:05:40
piece handle=/grid/app/12.1/dbs/c-1086639195-20150615-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-15 10:05:43
6 遷移mgmtdb spfile 到新的dg datadg
RMAN>  restore spfile to "+datadg";

Starting restore at 2015-06-15 10:07:24
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+datadg
channel ORA_DISK_1: reading from backup piece /grid/app/12.1/dbs/c-1086639195-20150615-00
channel ORA_DISK_1: piece handle=/grid/app/12.1/dbs/c-1086639195-20150615-00 tag=TAG20150615T100540
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2015-06-15 10:07:25
7 檢視驗證spfile 是否在新的dg上
qc1[/home/grid] srvctl config mgmtdb |grep Spfile
Spfile: +DATADG/_MGMTDB/PARAMETERFILE/spfile.263.882439645
qc1[/home/grid]

RMAN> shutdown immediate

database dismounted
Oracle instance shut down
8 遷移controlfile 到新的dg
grid使用者下

qc1[/home/grid]echo $ORACLE_SID
-MGMTDB
qc1[/home/grid]sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 15 10:10:17 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  788529152 bytes
Fixed Size                  2929352 bytes
Variable Size             314576184 bytes
Database Buffers          465567744 bytes
Redo Buffers                5455872 bytes
SQL> 
SQL> show parameter spfile
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
spfile                               string
+DATADG/_MGMTDB/PARAMETERFILE/
spfile.263.882439645
SQL> 
SQL> show parameter contro

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
+OCRVOTE/_MGMTDB/CONTROLFILE/c
urrent.259.882365147
control_management_pack_access       string
DIAGNOSTIC+TUNING
SQL> 
SQL>  alter system set control_files='+DATADG' scope=spfile ;
System altered.
SQL> 
關閉例項
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Advanced Analytics options


9 rman 進行還原
qc1[/home/grid]rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jun 15 10:13:08 2015

Coyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area     788529152 bytes

Fixed Size                     2929352 bytes
Variable Size                314576184 bytes
Database Buffers             465567744 bytes
Redo Buffers                   5455872 bytes
RMAN> RESTORE CONTROLFILE FROM '+OCRVOTE/_MGMTDB/CONTROLFILE/current.259.882365147';
Starting restore at 2015-06-15 10:14:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATADG/_MGMTDB/CONTROLFILE/current.270.882440047
Finished restore at 2015-06-15 10:14:08

10 還原之後mount db
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
RMAN> 
11 到這裡控制檔案和spfile都遷移到新的datadg上來了，下面我們將遷移資料檔案到datadg
進入rman
grid 使用者 export ORACLE_SID=-MGMTDB

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE FORMAT '+DATADG';

Starting backup at 2015-06-15 10:16:01
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+OCRVOTE/_MGMTDB/DATAFILE/system.257.882365057
output file name=+DATADG/_MGMTDB/DATAFILE/system.276.882440163 tag=TAG20150615T101601 RECID=3 STAMP=882440175
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+OCRVOTE/_MGMTDB/DATAFILE/sysaux.256.882365025
output file name=+DATADG/_MGMTDB/DATAFILE/sysaux.275.882440177 tag=TAG20150615T101601 RECID=4 STAMP=882440192
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+OCRVOTE/_MGMTDB/FD9AC0F7C36E4438E043B6A9E80A24D5/DATAFILE/system.264.882365201
output file name=+DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/system.274.882440193 tag=TAG20150615T101601 RECID=5 STAMP=882440199
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+OCRVOTE/_MGMTDB/FD9AC0F7C36E4438E043B6A9E80A24D5/DATAFILE/sysaux.265.882365201
output file name=+DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/sysaux.273.882440201 tag=TAG20150615T101601 RECID=6 STAMP=882440207
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+OCRVOTE/_MGMTDB/DATAFILE/undotbs1.258.882365103
output file name=+DATADG/_MGMTDB/DATAFILE/undotbs1.293.882440207 tag=TAG20150615T101601 RECID=7 STAMP=882440211
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2015-06-15 10:16:54
Starting Control File and SPFILE Autobackup at 2015-06-15 10:16:54
piece handle=/grid/app/12.1/dbs/c-1086639195-20150615-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2015-06-15 10:16:57


RMAN>  SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATADG/_MGMTDB/DATAFILE/system.276.882440163"
datafile 3 switched to datafile copy "+DATADG/_MGMTDB/DATAFILE/sysaux.275.882440177"
datafile 4 switched to datafile copy "+DATADG/_MGMTDB/DATAFILE/undotbs1.293.882440207"
datafile 5 switched to datafile copy "+DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/system.274.882440193"
datafile 6 switched to datafile copy "+DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/sysaux.273.882440201"


RMAN>


12 檢查檔案
RMAN> report schema;

Report of database schema for database with db_unique_name _MGMTDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     +DATADG/_MGMTDB/DATAFILE/system.276.882440163
3    400      SYSAUX               ***     +DATADG/_MGMTDB/DATAFILE/sysaux.275.882440177
4    55       UNDOTBS1             ***     +DATADG/_MGMTDB/DATAFILE/undotbs1.293.882440207
5    160      PDB$SEED:SYSTEM      ***     +DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/system.274.882440193
6    150      PDB$SEED:SYSAUX      ***     +DATADG/_MGMTDB/187498613A9C6285E0536302000AD902/DATAFILE/sysaux.273.882440201


List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    40       TEMP                 32767       +OCRVOTE/_MGMTDB/TEMPFILE/temp.263.882365191
2    40       PDB$SEED:TEMP        32767       +OCRVOTE/_MGMTDB/FD9AC0F7C36E4438E043B6A9E80A24D5/DATAFILE/pdbseed_temp012015-06-14_01-27-20-pm.dbf


13 由於臨時檔案rman是不會遷移，要手動遷移一下
Move Tempfile to Diskgroup MGMTDB.
RMAN> 
RMAN> run {
2> SET NEWNAME FOR TEMPFILE 1 TO '+DATADG';
3> SWITCH TEMPFILE ALL;
4>  }

executing command: SET NEWNAME
renamed tempfile 1 to +DATADG in control file

RMAN> 
14 redo日誌要 重新增加（datadg）刪除一下
qc1[/home/grid]export ORACLE_SID=-MGMTDB






SQL>  select lf.group#,lf.member,lg.status from v$logfile lf, v$log lg where  lf.GROUP#=lg.GROUP#  order by 1;


SQL> /


    GROUP# MEMBER                                   STATUS
---------- ---------------------------------------- --------------------------------
         1 +OCRVOTE/_MGMTDB/ONLINELOG/group_1.260.8 INACTIVE
           82365151


         2 +OCRVOTE/_MGMTDB/ONLINELOG/group_2.261.8 INACTIVE
           82365159


         3 +OCRVOTE/_MGMTDB/ONLINELOG/group_3.262.8 CURRENT
           82365173


 ALTER DATABASE ADD LOGFILE MEMBER '+DATADG' TO GROUP 1;
 ALTER DATABASE ADD LOGFILE MEMBER '+DATADG' TO GROUP 2;
 ALTER DATABASE ADD LOGFILE MEMBER '+DATADG' TO GROUP 3;
SQL> /
    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ --------------------------------
         1 +DATADG/_MGMTDB/ONLINELOG/group_1.283.882443911              CURRENT
         1 +OCRVOTE/_MGMTDB/ONLINELOG/group_1.260.882365151             CURRENT
         2 +DATADG/_MGMTDB/ONLINELOG/group_2.282.882443911              INACTIVE
         2 +OCRVOTE/_MGMTDB/ONLINELOG/group_2.261.882365159             INACTIVE
         3 +DATADG/_MGMTDB/ONLINELOG/group_3.284.882443913              INACTIVE


SQL>   ALTER DATABASE DROP LOGFILE MEMBER '+OCRVOTE/_MGMTDB/ONLINELOG/group_3.262.882365173';


Database altered.


SQL>   ALTER DATABASE DROP LOGFILE MEMBER '+OCRVOTE/_MGMTDB/ONLINELOG/group_2.261.882365159';

Database altered.
SQL>   ALTER DATABASE DROP LOGFILE MEMBER '+OCRVOTE/_MGMTDB/ONLINELOG/group_1.260.882365151';
Database altered.
SQL> 
    GROUP# MEMBER                                                       STATUS
---------- ------------------------------------------------------------ --------------------------------
         1 +DATADG/_MGMTDB/ONLINELOG/group_1.283.882443911              INACTIVE
         2 +DATADG/_MGMTDB/ONLINELOG/group_2.282.882443911              INACTIVE
         3 +DATADG/_MGMTDB/ONLINELOG/group_3.284.882443913              CURRENT
SQL> 
15 檢視mtmgdb配置
qc1[/home/oracle]srvctl config mgmtdb
Database unique name: _mgmtdb
Database name: 
Oracle home:
Oracle user: grid
Spfile: +DATADG/_MGMTDB/PARAMETERFILE/spfile.263.882439645
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Type: Management
PDB name: qc_cluster
PDB service: qc_cluster
Cluster name: qc-cluster
Database instance: -MGMTDB


16 srvctl 重啟驗證
root@qc1 bin]# ./srvctl stop mgmtdb 
[root@qc1 bin]# ./srvctl start mgmtdb
[root@qc1 bin]# ./srvctl status mgmtlsnr
Listener MGMTLSNR is enabled
Listener MGMTLSNR is running on node(s): qc1
[root@qc1 bin]# ./srvctl status mgmtdb  
Database is enabled
Instance -MGMTDB is running on node qc1
[root@qc1 bin]# ./srvctl config mgmtdb
Database unique name: _mgmtdb
Database name: 
Oracle home:
Oracle user: grid
Spfile: +DATADG/_MGMTDB/PARAMETERFILE/spfile.263.882439645
Password file: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Type: Management
PDB name: qc_cluster
PDB service: qc_cluster
Cluster name: qc-cluster
Database instance: -MGMTDB
[root@qc1 bin]# 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26175573/viewspace-1699935/，如需轉載，請註明出處，否則將追究法律責任。