Oracle 12c中的MGMTDB
MGMTDB系列
1 MGMTDB说明
Every Oracle Standalone Cluster and Oracle Domain Services Cluster contains a Grid Infrastructure Management Repository (GIMR), or the Management Database (MGMTDB).
The Grid Infrastructure Management Repository (GIMR) is a multitenant database with a pluggable database (PDB) for the GIMR of each cluster. The GIMR stores the following information about the cluster:
?Real time performance data the Cluster Health Monitor collects
?Fault, diagnosis, and metric data the Cluster Health Advisor collects
?Cluster-wide events about all resources that Oracle Clusterware collects
?CPU architecture data for Quality of Service Management (QoS)
?Metadata required for Rapid Home Provisioning
The Oracle Standalone Cluster locally hosts the GIMR on an Oracle ASM disk group; this GIMR is a multitenant database with a single pluggable database (PDB).
The global GIMR runs in an Oracle Domain Services Cluster. Oracle Domain Services Cluster locally hosts the GIMR in a separate Oracle ASM disk group. Client clusters, such as Oracle Member Cluster for Database, use the remote GIMR located on the Domain Services Cluster. For two-node or four-node clusters, hosting the GIMR for a cluster on a remote cluster reduces the overhead of running an extra infrastructure repository on a cluster. The GIMR for an Oracle Domain Services Cluster is a multitenant database with one PDB, and additional PDB for each member cluster that is added.
When you configure an Oracle Domain Services Cluster, the installer prompts to configure a separate Oracle ASM disk group for the GIMR, with the default name as MGMT.
常用命令
srvctl stop mgmtdb
srvctl status mgmtdb
srvctl config mgmtdb
srvctl disable mgmtdb
srvctl disable mgmtlsnr
srvctl add mgmtdb
srvctl config mgmtdb
srvctl disable mgmtdb
srvctl enable mgmtdb
srvctl getenv mgmtdb
srvctl modify mgmtdb
srvctl relocate mgmtdb
srvctl remove mgmtdb
srvctl setenv mgmtdb
srvctl start mgmtdb
srvctl status mgmtdb
srvctl stop mgmtdb
srvctl unsetenv mgmtdb
srvctl add mgmtlsnr
srvctl config mgmtlsnr
srvctl disable mgmtlsnr
srvctl enable mgmtlsnr
srvctl getenv mgmtlsnr
srvctl modify mgmtlsnr
srvctl remove mgmtlsnr
srvctl setenv mgmtlsnr
srvctl start mgmtlsnr
srvctl status mgmtlsnr
srvctl stop mgmtlsnr
srvctl unsetenv mgmtlsnr
必须使用grid用户进行登录：
[grid@raclhr-12cR1-N1 ~]$ export ORACLE_SID=-MGMTDB
[grid@raclhr-12cR1-N1 ~]$ sqlplus / as sysdba
MGMTDB数据库的信息：
Database unique name: _mgmtdb
Database name: _mgmtdb
Oracle user: grid
Database instance: -MGMTDB
service_names：_mgmtdb
[root@raclhr-12cR1-N1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.FRA.dg
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.OCR.dg
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.asm
ONLINE ONLINE raclhr-12cr1-n1 Started,STABLE
ONLINE ONLINE raclhr-12cr1-n2 Started,STABLE
ora.net1.network
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.ons
ONLINE ONLINE raclhr-12cr1-n1 STABLE
ONLINE ONLINE raclhr-12cr1-n2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE raclhr-12cr1-n1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE raclhr-12cr1-n1 169.254.20.104 192.1
68.2.100,STABLE
ora.cvu
1 ONLINE ONLINE raclhr-12cr1-n1 STABLE
ora.lhrrac.db
1 ONLINE ONLINE raclhr-12cr1-n1 Open,STABLE
2 ONLINE ONLINE raclhr-12cr1-n2 Open,STABLE
ora.mgmtdb
1 ONLINE ONLINE raclhr-12cr1-n1 Open,STABLE
ora.oc4j
1 ONLINE ONLINE raclhr-12cr1-n1 STABLE
ora.raclhr-12cr1-n1.vip
1 ONLINE ONLINE raclhr-12cr1-n1 STABLE
ora.raclhr-12cr1-n2.vip
1 ONLINE ONLINE raclhr-12cr1-n2 STABLE
ora.scan1.vip
1 ONLINE ONLINE raclhr-12cr1-n1 STABLE
--------------------------------------------------------------------------------
[root@raclhr-12cR1-N1 ~]#
使用crsctl 命令可以看到ora.mgmtdb 和ora.MGMTLSNR。
在操作系统层面，也可以查看到有2个对应的进程：
[root@raclhr-12cR1-N1 ~]# ps -ef| grep pmon_-MGMTDB
grid 36338 1 0 16:50 ? 00:00:00 mdb_pmon_-MGMTDB
root 38490 46150 0 16:58 pts/0 00:00:00 grep pmon_-MGMTDB
[root@raclhr-12cR1-N1 ~]# ps -ef| grep MGMTLSNR
grid 729 1 0 06:11 ? 00:00:01 /u01/app/12.1.0/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
root 38779 46150 0 16:58 pts/0 00:00:00 grep MGMTLSNR
[root@raclhr-12cR1-N1 ~]#
以下内容转载于David Dai：http://www.cndba.cn/dave/article/248
1 MGMTDB 说明
在Oracle 12.1.0.1 的Grid Infrastructure 的安装中，可以选择是否安装Grid Infrastructure Management Repository (GIMR) 数据库：MGMTDB. 如下图：
在Grid Infrastructure 12.1.0.2 中，已经没有改选项，MIMR 数据库已经变成了强制选项。
在Oracle 12c 中Management Database 用来Cluster Health Monitor(CHM/OS,ora.crf) ，Oracle Database QoS Management，Rapid Home Provisioning和其他的数据。
Management Repository 是受12c Clusterware 管理的一个单实例，在Cluster 启动的时会启动MGMTDG并在其中一个节点上运行，并受GI 管理，如果运行MGMTDG的节点宕机了，GI 会自动把MGMTDB 转移到其他的节点上。
默认情况，MGMTDB 数据库的数据文件存放在共享的设备，如OCR/Voting 的磁盘组中，但后期可以移动位置。
在12.1.0.1 中，GIMR 是可选的，如果在安装GI的时候，没有选择Management Database 数据库，那么所有依赖的特性，如Cluster Health Monitor (CHM/OS) 就会被禁用。
当然，在12.1.0.2 中，可以忽略这个问题，因为是强制安装GIMR了。
另外，对于MGMT 数据库，在目前的版本中，也不需要手工对其进行备份。
2 MGMTDB 基本操作
2.1 查看Management DB 相关的资源:
[root@rac1 ~]# crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- …… ora.MGMTLSNR 1 ONLINE ONLINE rac2 169.254.142.79 192.1 68.57.6,STABLE ora.mgmtdb 1 ONLINE ONLINE rac2 Open,STABLE …… [root@rac1 ~]#
使用crsctl 命令可以看到ora.mgmtdb 和ora.MGMTLSNR。
在操作系统层面，也可以查看到有2个对应的进程：
[root@rac2 ~]# ps -ef| grep pmon_-MGMTDB grid 7452 1 0 14:59 ? 00:00:00 mdb_pmon_-MGMTDB root 7756 7727 0 15:02 pts/4 00:00:00 grep pmon_-MGMTDB [root@rac2 ~]# ps -ef| grep MGMTLSNR grid 7411 1 0 14:58 ? 00:00:00 /u01/gridsoft/12.1.0/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit root 7758 7727 0 15:02 pts/4 00:00:00 grep MGMTLSNR [root@rac2 ~]#
2.2 启动和关闭MGMT
正常情况下，MGMTDB 会在GI 启动的时候，会自动启动，但也可以手工管理，直接使用srvctl 操作即可：
Usage: srvctl start mgmtdb [-startoption ] [-node ]
Usage: srvctl start mgmtlsnr [-node ]
2.3 查看Management Database 的log 和trace 文件
一般情况下，是不需要查看MGMT DB的trace的，如果要查看，用如下命令：
[grid@rac2 _mgmtdb]$ pwd /u01/gridbase/diag/rdbms/_mgmtdb [grid@rac2 _mgmtdb]$ ls i_1.mif -MGMTDB [grid@rac2 _mgmtdb]$
进入$ORACLE_BASE下的trace目录。 但是进入-MGMTDB时，要注意，不能直接cd：
[grid@rac2 _mgmtdb]$ cd -MGMTDB -bash: cd: -M: invalid option cd: usage: cd [-L|-P] [dir] [grid@rac2 _mgmtdb]$
必须使用./-MGMTDB,如：
[grid@rac2 _mgmtdb]$ cd ./-MGMTDB [grid@rac2 -MGMTDB]$ ls alert hm incpkg lck metadata metadata_pv sweep cdump incident ir log metadata_dgif stage trace [grid@rac2 -MGMTDB]$ [grid@rac2 trace]$ pwd /u01/gridbase/diag/rdbms/_mgmtdb/-MGMTDB/trace [grid@rac2 trace]$ ls alert_-MGMTDB.log -MGMTDB_ckpt_4772.trm -MGMTDB_lgwr_7475.trc -MGMTDB_m001_10288.trm -MGMTDB_ora_10486.trc -MGMTDB_p001_5645.trm cdmp_20140807064254 -MGMTDB_ckpt_7477.trc -MGMTDB_lgwr_7475.trm -MGMTDB_m001_10330.trc -MGMTDB_ora_10486.trm -MGMTDB_p001_7523.trc cdmp_20141208110548 -MGMTDB_ckpt_7477.trm -MGMTDB_m000_10101.trc -MGMTDB_m001_10330.trm -MGMTDB_ora_10830.trc -MGMTDB_p001_7523.trm cdmp_20141208110550 -MGMTDB_dbrm_4764.trc -MGMTDB_m000_10101.trm -MGMTDB_m001_8055.trc -MGMTDB_ora_10830.trm -MGMTDB_rbal_10460.trc cdmp_20141208110553 -MGMTDB_dbrm_4764.trm -MGMTDB_m000_10202.trc -MGMTDB_m001_8055.trm -MGMTDB_ora_3770.trc -MGMTDB_rbal_10460.trm cdmp_20141208110555 -MGMTDB_dbrm_7469.trc -MGMTDB_m000_10202.trm -MGMTDB_mark_10484.trc -MGMTDB_ora_3770.trm -MGMTDB_rbal_4782.trc
MGMT DB 的日志和trace 都在这个里。
3 MGMTDB是带一个PDB的CDB实例
前面说了，MGMTDB 是一个实例，实际上，MGMTDB是带一个PDB的CDB库，我们可以使用GI的命令直接去操作MGMTDB 对应的PDB。
--启动MGMTDB： [root@rac1 ~]# srvctl status mgmtdb Database is enabled Database is not running. [root@rac1 ~]# srvctl start mgmtdb [root@rac1 ~]# [root@rac1 ~]# srvctl status mgmtdb Database is enabled Instance -MGMTDB is running on node rac1 [root@rac1 ~]# --查看MGMTDB当前节点： [grid@rac2 /]$ oclumon manage -get master Master = rac1 --查看状态： [grid@rac2 /]$ srvctl status mgmtdb Database is enabled Instance -MGMTDB is running on node rac2 --查看配置信息： [grid@rac2 /]$ srvctl config mgmtdb Database unique name: _mgmtdb Database name: Oracle home: /u01/gridsoft/12.1.0 Oracle user: grid Spfile: +OCR_VOTING/_mgmtdb/spfile-MGMTDB.ora Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Database instance: -MGMTDB Type: Management --连接MGMTDB实例 [grid@rac2 /]$ export ORACLE_SID=-MGMTDB [grid@rac2 /]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 8 15:24:37 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management and Advanced Analytics options SQL> select file_name from dba_data_files union select member file_name from V$logfile; FILE_NAME -------------------------------------------------------------------------------- +OCR_VOTING/_MGMTDB/DATAFILE/sysaux.258.854939615 +OCR_VOTING/_MGMTDB/DATAFILE/sysgridhomedata.261.854939891 +OCR_VOTING/_MGMTDB/DATAFILE/sysmgmtdata.260.854939737 +OCR_VOTING/_MGMTDB/DATAFILE/system.259.854939661 +OCR_VOTING/_MGMTDB/DATAFILE/undotbs1.257.854939605 +OCR_VOTING/_MGMTDB/ONLINELOG/group_1.263.854940051 +OCR_VOTING/_MGMTDB/ONLINELOG/group_2.264.854940053 +OCR_VOTING/_MGMTDB/ONLINELOG/group_3.265.854940057 8 rows selected. 这里查询的是MGMTDB的路径，也可以直接用如下命令查询： [grid@rac2 /]$ oclumon manage -get reppath CHM Repository Path = +OCR_VOTING/_MGMTDB/DATAFILE/sysmgmtdata.260.854939737 [grid@rac2 /]$ --查询MGMTDB用户： SQL> select username,account_status from dba_users where username like 'CH%'; USERNAME ACCOUNT_STATUS ------------- ---------------- CHM OPEN CHA OPEN
4 移动MGMTDB 数据文件位置
默认情况下，MGMTDB 的数据文件是存放在OCR voting disk的磁盘组里的，为了节省OCR 磁盘组空间，我们也可以把MGMTDB 转移走。
当然，这里的移动位置，也是从一个共享位置移动到另一个共享位置。
具体操作如下。
在12.1.0.1 版本中有有问题，升级到12.1.0.2 解决。
4.1 停止并禁用ora.crf 资源
这里的ora.crf就是CHM。
在所有节点使用root用户执行如下命令：
[root@rac1 ~]# crsctl stop res ora.crf -init CRS-2673: Attempting to stop 'ora.crf' on 'rac1' CRS-2677: Stop of 'ora.crf' on 'rac1' succeeded [root@rac1 ~]# crsctl modify res ora.crf -attr ENABLED=0 -init [root@rac1 ~]# [root@rac2 ~]# crsctl stop res ora.crf -init CRS-2673: Attempting to stop 'ora.crf' on 'rac2' CRS-2677: Stop of 'ora.crf' on 'rac2' succeeded [root@rac2 ~]# crsctl modify res ora.crf -attr ENABLED=0 -init [root@rac2 ~]# 注意：ora.mgmtlsnr 和 ora.mgmtdb 资源不能停，否则DBCA 时会报错。
4.2 执行DBCA 删除management database
--查看MGMTDB的运行节点：
[root@rac1 ~]# srvctl status mgmtdb Database is enabled Instance -MGMTDB is running on node rac2
这里显示在节点2上运行，那么在节点2上，用grid用户，执行dbca 命令，删除MGMTDB。
[grid@rac2 ~]$ dbca -silent -deleteDatabase -sourceDB -MGMTDB Connecting to database 4% complete 9% complete 14% complete 19% complete 23% complete 28% complete 47% complete Updating network configuration files 48% complete 52% complete Deleting instance and datafiles 76% complete 100% complete Look at the log file "/u01/gridbase/cfgtoollogs/dbca/_mgmtdb.log" for further details. [grid@rac2 ~]$
注意：
如果是使用DBCA 手工创建的MGMTDB，则可能出现不能删除的情况，具体处理过程可以参考MOS： 1631336.1。
4.3 重建MGMTDB的CDB
4.3.1 12.1.0.1 执行如下命令
用grid用户，在任意节点，执行如下命令，重建CDB。
$ /bin/dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType ASM -diskGroupName <+NEW_DG> -datafileJarLocation /assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal
注意：
这里新的磁盘组，建议compatible.asm 和 compatible.rdbms 属性都设置为12.1。
上面的命令使用的是磁盘组，如果是使用共享的NFS/CFS， 则使用如下命令：
/bin/dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType FS -datafileDestination -datafileJarLocation /assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal
我们这里数据库版本是12.1.0.1,并且使用的是磁盘组，所以执行第一个命令：
[grid@rac1 /]$ dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType ASM -diskGroupName OCR -datafileJarLocation /u01/gridsoft/12.1.0/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal Copying database files 1% complete 3% complete 10% complete 17% complete 24% complete 31% complete 35% complete Creating and starting Oracle instance 37% complete 42% complete 47% complete 52% complete 53% complete 56% complete 58% complete Registering database with Oracle Grid Infrastructure 64% complete Completing Database Creation 68% complete 78% complete 89% complete 100% complete Look at the log file "/u01/gridbase/cfgtoollogs/dbca/_mgmtdb/_mgmtdb0.log" for further details. [grid@rac1 /]$
4.3.2 12.1.0.2 执行如下命令
在任意节点，用grid用户执行如下命令：
--ASM 磁盘组： $ /bin/dbca -silent -createDatabase -sid -MGMTDB -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -gdbName _mgmtdb -storageType ASM -diskGroupName <+NEW_DG> -datafileJarLocation $GRID_HOME/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck --共享的NFS/CFS ： $/bin/dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType FS -datafileDestination -datafileJarLocation /assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal --示例： [grid@rac1 templates]$ dbca -silent -createDatabase -sid -MGMTDB -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -gdbName _mgmtdb -storageType ASM -diskGroupName +OCR -datafileJarLocation /u01/gridsoft/12.1.0.2/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck Registering database with Oracle Grid Infrastructure 5% complete Copying database files 7% complete 9% complete 16% complete 23% complete 30% complete 41% complete Creating and starting Oracle instance 43% complete 48% complete 49% complete 50% complete 55% complete 60% complete 61% complete 64% complete Completing Database Creation 68% complete 79% complete 89% complete 100% complete Look at the log file "/u01/gridbase/cfgtoollogs/dbca/_mgmtdb/_mgmtdb0.log" for further details. [grid@rac1 templates]$
4.4 使用DBCA 创建PDB
[grid@rac1 templates]$ srvctl status mgmtdb Database is enabled Instance -MGMTDB is running on node rac1 [grid@rac1 templates]$
在任意节点，用grid用户执行dbca 创建PDB，命令如下：
$ /bin/dbca -silent -createPluggableDatabase -sourceDB -MGMTDB -pdbName -createPDBFrom RMANBACKUP -PDBBackUpfile /assistants/dbca/templates/mgmtseed_pdb.dfb -PDBMetadataFile /assistants/dbca/templates/mgmtseed_pdb.xml -createAsClone true –internalSkipGIHomeCheck
--查询集群的名称：
[grid@rac1 /]$ cemutlo -n
rac-scan
[grid@rac1 /]$
注意：默认情况CLUSTER_NAME 都是-，这里比如换成_
我们这里就要换成rac_scan
[grid@rac1 templates]$ dbca -silent -createPluggableDatabase -sourceDB -MGMTDB -pdbName rac_scan -createPDBFrom RMANBACKUP -PDBBackUpfile /u01/gridsoft/12.1.0.2/assistants/dbca/templates/mgmtseed_pdb.dfb -PDBMetadataFile /u01/gridsoft/12.1.0.2/assistants/dbca/templates/mgmtseed_pdb.xml -createAsClone true -internalSkipGIHomeCheck Creating Pluggable Database 4% complete 12% complete 21% complete 38% complete 55% complete 85% complete Completing Pluggable Database Creation 100% complete Look at the log file "/u01/gridbase/cfgtoollogs/dbca/_mgmtdb/rac_scan/_mgmtdb0.log" for further details. [grid@rac1 templates]$
4.5 验证MGMTDB
用grid用户执行如下命令，验证MGMTDB 运行情况：
[grid@rac1 ~]$ srvctl status MGMTDB
Database is enabled
Instance -MGMTDB is running on node rac1
这里显示的是节点1，那么在节点1上在执行：
[grid@rac1 ~]$ mgmtca [grid@rac1 ~]$ crsctl stat res -t …… ora.MGMTLSNR 1 ONLINE ONLINE rac1 169.254.193.105 192. 168.57.5,STABLE ora.mgmtdb 1 ONLINE ONLINE rac1 Open,STABLE …… [grid@rac1 ~]$ [grid@rac1 templates]$ srvctl config mgmtdb Database unique name: _mgmtdb Database name: Oracle home: Oracle user: grid Spfile: +OCR/_MGMTDB/PARAMETERFILE/spfile.268.865977817 Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Type: Management PDB name: rac_scan PDB service: rac_scan Cluster name: rac-scan Database instance: -MGMTDB [grid@rac1 templates]$ [grid@rac1 templates]$ export ORACLE_SID=-MGMTDB [grid@rac1 templates]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 10 21:39:13 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management and Advanced Analytics options SQL> select file_name from dba_data_files union select member file_name from V$logfile; FILE_NAME -------------------------------------------------------------------------------- +OCR/_MGMTDB/DATAFILE/sysaux.257.865977463 +OCR/_MGMTDB/DATAFILE/system.258.865977473 +OCR/_MGMTDB/DATAFILE/undotbs1.259.865977489 +OCR/_MGMTDB/ONLINELOG/group_1.261.865977635 +OCR/_MGMTDB/ONLINELOG/group_2.262.865977635 +OCR/_MGMTDB/ONLINELOG/group_3.263.865977635 6 rows selected. SQL>
数据文件已经转移到OCR 磁盘组了。
4.6 启用并启动ora.crf 资源
在所有节点，用root用户执行：
[root@rac1 u01]# crsctl modify res ora.crf -attr ENABLED=1 -init [root@rac1 u01]# crsctl start res ora.crf -init CRS-2672: Attempting to start 'ora.crf' on 'rac1' CRS-2676: Start of 'ora.crf' on 'rac1' succeeded [root@rac1 u01]# oclumon manage -get master Master = rac1 [root@rac1 u01]#
MOS
1、How to Move/Recreate GI Management Repository to Different Shared Storage (Diskgroup, CFS or NFS etc) (文档 ID 1589394.1)
Information in this document applies to any platform.
2、12.1.0.2 GI GIMR/MGMTDB SYSMGMTDATA Tablespace Increasing Rapidly (文档 ID 2177879.1)
Information in this document applies to any platform.
In this Document
| Goal |
| Solution |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and laterInformation in this document applies to any platform.
GOAL
This note outlines the steps to move 12c GIMR (Grid Infrastructure Management Repository) to a different shared storage.
This procedure will NOT preserve Management Database contents. To back up manually in text format, as grid user on any node:
$ /bin/oclumon dumpnodeview -allnodes -v >
SOLUTION
1. Stop and disable ora.crf resource.
On each node, as root user:
# /bin/crsctl stop res ora.crf -init
# /bin/crsctl modify res ora.crf -attr ENABLED=0 -init
# /bin/crsctl modify res ora.crf -attr ENABLED=0 -init
Do not stop ora.mgmtlsnr or ora.mgmtdb resource or Step 2 will fail with the following:
Oracle Grid Management database is running on node "". Run dbca on node "" to delete the database.
2. Issue the DBCA command to delete the management database
As Grid User, locate the node that the Management Database is running by executing:
$ /bin/srvctl status mgmtdb
As Grid User on the node where ora.mgmtdb resource is running execute:
$ /bin/dbca -silent -deleteDatabase -sourceDB -MGMTDB
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/home/grid/app/grid/cfgtoollogs/dbca/_mgmtdb.log" for further details.
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/home/grid/app/grid/cfgtoollogs/dbca/_mgmtdb.log" for further details.
Note:
If MGMTDB is not starting for any reason, dbca will fail:
Oracle Grid Management database is running on node "". Run dbca on node "" to delete the database.
The solution is to delete it manually by:
- remove the MGMTDB related files manually with OS command and remove the oratab entry
- remove the resource with srvctl: srvctl remove mgmtdb
3. Recreate the MGMTDB
3A. For 12.1.0.1 only:
As grid user, on any node execute the following dbca command:
$ /bin/dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType ASM -diskGroupName <+NEW_DG> -datafileJarLocation /assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
31% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Grid Infrastructure
64% complete
Completing Database Creation
68% complete
78% complete
89% complete
100% complete
Look at the log file "/home/grid/app/grid/cfgtoollogs/dbca/_mgmtdb/_mgmtdb1.log" for further details.
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
31% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Grid Infrastructure
64% complete
Completing Database Creation
68% complete
78% complete
89% complete
100% complete
Look at the log file "/home/grid/app/grid/cfgtoollogs/dbca/_mgmtdb/_mgmtdb1.log" for further details.
NOTE: It's recommended to set both compatible.asm and compatible.rdbms to 12.1 for the new ASM diskgroup when creating or altering and existing diskgroup.
Also, this example created the Management Database in a new ASM Diskgroup, to create the database in shared NFS/CFS location:
/bin/dbca -silent -createDatabase -templateName MGMTSeed_Database.dbc -sid -MGMTDB -gdbName _mgmtdb -storageType FS -datafileDestination -datafileJarLocation /assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -oui_internal
3B. For 12.1.0.2 only:
a. As Grid User on any node execute the following DBCA command with the desired :
$ /bin/dbca -silent -createDatabase -sid -MGMTDB -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -gdbName _mgmtdb -storageType ASM -diskGroupName <+NEW_DG> -datafileJarLocation $GI_HOME/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
31% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Grid Infrastructure
64% complete
Completing Database Creation
68% complete
78% complete
89% complete
100% complete
Look at the log file "/cfgtoollogs/dbca/_mgmtdb/_mgmtdb1.log" for further details.
NOTE: It's recommended to set both compatible.asm and compatible.rdbms to 12.1 for the new ASM diskgroup when creating or altering and existing diskgroup.
Ex: CREATE DISKGROUP … ATTRIBUTE 'compatible.rdbms' = '12.1', 'compatible.asm' = '12.1';
The Management Database may be moved to an ASM Diskgroup, in a shared NFS/CFS location with the following command line:
$ /bin/dbca -silent -createDatabase -sid -MGMTDB -createAsContainerDatabase true -templateName MGMTSeed_Database.dbc -gdbName _mgmtdb -storageType FS -datafileDestination -datafileJarLocation $GI_HOME/assistants/dbca/templates -characterset AL32UTF8 -autoGeneratePasswords -skipUserTemplateCheck
b. Create a PDB within the MGMTDB using DBCA.
As Grid User on any node execute the following DBCA command:
NOTE: The CLUSTER_NAME needs to have any hyphens (“-“) replaced with underscores (“_”)
$ /bin/dbca -silent -createPluggableDatabase -sourceDB -MGMTDB -pdbName -createPDBFrom RMANBACKUP -PDBBackUpfile /assistants/dbca/templates/mgmtseed_pdb.dfb -PDBMetadataFile /assistants/dbca/templates/mgmtseed_pdb.xml -createAsClone true
Creating Pluggable Database
4% complete
12% complete
21% complete
38% complete
55% complete
85% complete
Completing Pluggable Database Creation
100% complete
Look at the log file "/cfgtoollogs/dbca/_mgmtdb//_mgmtdb2.log" for further details.
Creating Pluggable Database
4% complete
12% complete
21% complete
38% complete
55% complete
85% complete
Completing Pluggable Database Creation
100% complete
Look at the log file "/cfgtoollogs/dbca/_mgmtdb//_mgmtdb2.log" for further details.
4. Secure that the Management Database credential:
As Grid User, confirm the node on which MGMTDB is running by executing.
$ /bin/srvctl status MGMTDB
Database is enabled
Instance -MGMTDB is running on node
Database is enabled
Instance -MGMTDB is running on node
On :
$ /bin/mgmtca
5. Enable and start ora.crf resource.
On each node, as root user:
# /bin/crsctl modify res ora.crf -attr ENABLED=1 -init
# /bin/crsctl start res ora.crf -init
# /bin/crsctl start res ora.crf -init
2、12.1.0.2 GI GIMR/MGMTDB SYSMGMTDATA Tablespace Increasing Rapidly (文档 ID 2177879.1)
In this Document
| Symptoms |
| Cause |
| Solution |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
SYMPTOMS
12.1.0.2 GIMR database (-MGMTDB) SYSMGMTDATA tablespace keeps increasing with the same retention size:
$ oclumon manage -get reppath
CHM Repository Path = +CRS/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.917710623
Tablespace Name File Name Size (M) Used (M) Free (M) % Used
-------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
SYSMGMTDATA +CRS/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.917710623 16894 16032 521 95
Expired CHM data are not purged:
SQL> select trunc(BEGINTIME), count(*) from chm.CHMOS_PROCESS_INT_TBL group by trunc(BEGINTIME) order by trunc(BEGINTIME);
TRUNC(BEG COUNT(*)
--------- ----------
25-JUL-16 1824587
26-JUL-16 3019039
27-JUL-16 3236612
28-JUL-16 3559627
29-JUL-16 3687053
....
09-AUG-16 3626059
10-AUG-16 3638210
11-AUG-16 3665800
12-AUG-16 3565969
13-AUG-16 3650727
14-AUG-16 3638103
15-AUG-16 3622208
16-AUG-16 3680251
17-AUG-16 2321889
CAUSE
Issue was investigated in:
Bug 23563549 - -MGMTDB SYSMGMTDATA IS FILLING UP WHICH CAUSES GRID1 DISK TO REACH 100%.
Bug 24331121 - OCR_VOTE DISK GROUP IS 99% FULL, HIGH UNDO USED BY MGMTDB
The cause is due to autoextend for the tablespace being set to on.
SOLUTION
Solution 1:
Recreate GIMR which will drop the existing database and set autoextend back to off.
note 1589394.1 - How to Move/Recreate GI Management Repository to Different Shared Storage (Diskgroup, CFS or NFS etc)
Or
Solution 2:
Manually perform the following steps to shrink:
1. Stop and disable ora.crf resource.
On each node, as root user:
# /bin/crsctl stop res ora.crf -init
# /bin/crsctl modify res ora.crf -attr ENABLED=0 -init
# /bin/crsctl modify res ora.crf -attr ENABLED=0 -init
2. Identify huge objects from dba_segments and truncate them:
Connect to MGMTDB:
export ORACLE_HOME=
export ORACLE_SID=-MGMTDB
$ORACLE_HOME/bin/sqlplus / as sysdba
SQL> select pdb_name from dba_pdbs where pdb_name!='PDB$SEED';
PDB_NAME
--------------------------------------------------------------------------------------------------------------------------------
CEHAOVMSP1CLU68 ====>>>> this is your PDB name, use it in next command
SQL> alter session set container=CEHAOVMSP1CLU68;
Session altered.
SQL> col obj format a50
SQL> select owner||'.'||SEGMENT_NAME obj, BYTES from dba_segments where owner='CHM' order by 2 asc
Likely you are going to see the following as the biggest table:
CHM.CHMOS_PROCESS_INT_TBL
CHM.CHMOS_DEVICE_INT_TBL
Truncate the table, i.e.:
SQL> truncate table CHM.CHMOS_PROCESS_INT_TBL;
SQL> truncate table CHM.CHMOS_DEVICE_INT_TBL;
..
export ORACLE_SID=-MGMTDB
$ORACLE_HOME/bin/sqlplus / as sysdba
SQL> select pdb_name from dba_pdbs where pdb_name!='PDB$SEED';
PDB_NAME
--------------------------------------------------------------------------------------------------------------------------------
CEHAOVMSP1CLU68 ====>>>> this is your PDB name, use it in next command
SQL> alter session set container=CEHAOVMSP1CLU68;
Session altered.
SQL> col obj format a50
SQL> select owner||'.'||SEGMENT_NAME obj, BYTES from dba_segments where owner='CHM' order by 2 asc
Likely you are going to see the following as the biggest table:
CHM.CHMOS_PROCESS_INT_TBL
CHM.CHMOS_DEVICE_INT_TBL
Truncate the table, i.e.:
SQL> truncate table CHM.CHMOS_PROCESS_INT_TBL;
SQL> truncate table CHM.CHMOS_DEVICE_INT_TBL;
..
3. Shrink the tablespace:
https://docs.oracle.com/database/121/ADMIN/schema.htm#ADMIN11600
4. Enable and start ora.crf resource.
On each node, as root user:
# /bin/crsctl modify res ora.crf -attr ENABLED=1 -init
# /bin/crsctl start res ora.crf -init
# /bin/crsctl start res ora.crf -init
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2132763/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(642808185)，注明添加缘由
● 于 2017-01-15 09:00 ~ 2017-01-17 19:00 在农行完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2132763/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(642808185)，注明添加缘由
● 于 2017-01-15 09:00 ~ 2017-01-17 19:00 在农行完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
正文到此结束
- 本文标签： node tar sql 空间 list src Enterprise IO remote 集群 API XML client ACE NIO db2 CDN cat 管理 实例 example Word 图片 tab https 进程 IDE Select schema 博客 ORM 备份 struct 数据库 数据 DOM QQ群 删除 Service root ip Oracle UI Document 同步 http 微信公众号 目录 mail grep 配置 HTML 操作系统 DDL 文章 id 云 db nfs 安装 App 二维码 core Architect Master
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
