Oracle 12c 迁移MGMTDB 到其他的磁盘组
原创 Oracle 作者：羽化残虹 时间：2015-06-15 14:15:31 3248 0
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]#