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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 12c RAC: MGMTDBOracle
- 遷移ASM磁碟組ASM
- 資料檔案遷移至其他磁碟組
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 遷移WSL Ubuntu到其他目錄Ubuntu
- WSL遷移到其他磁碟
- Oracle 12c和18c中的MGMTDB(下)Oracle
- Oracle 12c PDB遷移（一）Oracle
- 遷移ocr/votedisk/asm spfile所在磁碟組ASM
- 如何移動asm磁碟組內的資料檔案到另外一個磁碟組ASM
- 零當機時間遷移 ASM 磁碟組到另一個 SAN/磁碟陣列/DAS 的準確步驟ASM陣列
- 通過遷移的方式修改ASM磁碟組的冗餘屬性ASM
- 遷移OCR和VotingDisk並刪除原ASM磁碟組ASM
- oracle 遷移資料庫到asmOracle資料庫ASM
- 【Oracle 12c Flex Cluster專題 】— Leaf Node的故障遷移OracleFlex
- oracle asm 儲存 a磁碟組中的資料檔案 遷移到b磁碟組實施步驟OracleASM
- sqlldr 完成mysql到oracle的資料遷移MySqlOracle
- 將工作負荷組移動到其他資源池
- 【MOS】零當機遷移ASM磁碟組到另一個SAN/磁碟陣列/DAS的準確步驟 (文件 ID 1946664.1)ASM陣列
- .NET框架下Oracle到SQL Server遷移框架OracleSQLServer
- yugong之多張表oracle到mysql遷移GoOracleMySql
- WSL子系統檔案遷移至其他磁碟
- Azure ASM到ARM遷移 （三） Reserved IP的遷移ASM
- Oracle 12C 新特性之資料檔案線上遷移Oracle
- 使用GoldenGate 遷移Oracle到PostgreSQL/LightDBGoOracleSQL
- oracle 資料遷移案例 從 8.1.7.4到9.2.0.8Oracle
- yugong之單張表oracle到mysql遷移GoOracleMySql
- 從MySQL到ORACLE程式遷移的注意事項(轉)MySqlOracle
- Oracle 12c ASM專題｜Flex磁碟組到底有多Fexible？OracleASMFlex
- Oracle 12c 使用（Full Transportable Export/Import）進行升級/遷移OracleExportImport
- 藉助ogg完成oracle到mysql的資料遷移OracleMySql
- 1.0 ORACLE到MYSQL資料遷移方式選型OracleMySql
- 模擬11G單例項到12C的資料遷移過程單例
- Oracle 12C 新特性之表分割槽或子分割槽的線上遷移Oracle
- docker 遷移映象到其他機器執行報錯OCI 問題處理Docker
- ORACLE 10.2.0.5 RAC OCR&vote disk 磁碟遷移 DG Rebalance測試Oracle
- 使用SQL Developer 遷移異構資料庫到OracleSQLDeveloper資料庫Oracle
- 12c跨平臺完成PDB的備份遷移