Oracle | RAC Management Database

Oracle RAC Management Database Version 12.1

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose With the introduction of Oracle Database 12c the Oracle Corporation has changed the nature of the management database installed as part of Clusterware.
In 12cR1 the database is an Oracle Database that is licensed as part of Clusterware and does not require separate, licensing or a support agreement.

The Managment database is not RAC or RAC One Node and yet it fails over to other nodes: Currently I do not know by what mechanism this is being accomplished but strongly suspect it is a new, currently undocumented, Oracle transparent failover technology.

The information on this page is intended to help those installing 12cR1 RAC so that they better understand what this database is and are in a position to better manage both the instance and its space requirements.

As SYS to CDB$ROOT [oracle@rac20a ~]$ . oraenv ORACLE_SID = [oracle] ? -MGMTDB The Oracle base has been set to /u01/app/oracle [oracle@rac20a ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 23 15:56:56 2015 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

To the PDB SQL> conn / as sysdba SQL> alter session set container = RAC20_CLUSTER;

Database

Database Configuration SQL> SELECT name, log_mode, controlfile_type, database_role, 2 current_scn, cdb, con_id, force_full_db_caching 3 FROM v$database; NAME LOG_MODE CONTROL DATABASE_ROLE CURRENT_SCN CDB CON_ID FOR --------- ------------ ------- ---------------- ----------- --- ---------- --- _MGMTDB NOARCHIVELOG CURRENT PRIMARY 995974 YES 0 NO

Containers SQL> SELECT con_id, name, open_mode 2 FROM v$containers; CON_ID NAME OPEN_MODE ------- -------------------- ---------- 1 CDB$ROOT READ WRITE 2 PDB$SEED READ ONLY 3 RAC20_CLUSTER READ WRITE

Control Files SQL> SELECT name, value 2 FROM v$parameter 3 WHERE name LIKE '%control_file%'; NAME VALUE ------------------------------ ------------------------------------------------ control_files +DATA/_MGMTDB/CONTROLFILE/current.260.875037839 control_file_record_keep_time 7

Redo Logs SQL> SELECT group#, member 2 FROM v$logfile 3 ORDER BY 1; GROUP# MEMBER ---------- ----------------------------------------------- 1 +DATA/_MGMTDB/ONLINELOG/group_1.261.875037839 2 +DATA/_MGMTDB/ONLINELOG/group_2.262.875037841 3 +DATA/_MGMTDB/ONLINELOG/group_3.263.875037841 SQL> SELECT BYTES/1024/1024 SIZE_MB 2 FROM v$log; SIZE_MB ---------- 50 50 50

SPFILE # Oracle init.ora parameter file generated by instance -MGMTDB on 03/23/2015 16:16:04 __data_transfer_cache_size=0 __db_cache_size=524M __java_pool_size=4M __large_pool_size=8M __oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment __pga_aggregate_target=328M __sga_target=752M __shared_io_pool_size=28M __shared_pool_size=180M __streams_pool_size=0 _adaptive_window_consolidator_enabled=TRUE _aggregation_optimization_settings=0 _always_anti_join='CHOOSE' _always_semi_join='CHOOSE' _and_pruning_enabled=TRUE _b_tree_bitmap_plans=TRUE _bloom_filter_enabled=TRUE _bloom_folding_enabled=TRUE _bloom_pruning_enabled=TRUE _bloom_serial_filter='ON' _catalog_foreign_restore=FALSE _complex_view_merging=TRUE _compression_compatibility='12.1.0.2.0' _connect_by_use_union_all='TRUE' _convert_set_to_join=FALSE _cost_equality_semi_join=TRUE _cpu_to_io=0 _diag_adr_trace_dest='/u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/trace' _dimension_skip_null=TRUE _disable_txn_alert=3 _distinct_agg_optimization_gsets='CHOOSE' _eliminate_common_subexpr=TRUE _enable_type_dep_selectivity=TRUE _fast_full_scan_enabled=TRUE _first_k_rows_dynamic_proration=TRUE _gby_hash_aggregation_enabled=TRUE _gby_vector_aggregation_enabled=TRUE _generalized_pruning_enabled=TRUE _globalindex_pnum_filter_enabled=TRUE _gs_anti_semi_join_allowed=TRUE _improved_outerjoin_card=TRUE _improved_row_length_enabled=TRUE _index_join_enabled=TRUE _kolfuseslf=TRUE _ksb_restart_policy_times='0' _ksb_restart_policy_times='60' _ksb_restart_policy_times='120' _ksb_restart_policy_times='240' # internal update to set default _left_nested_loops_random=TRUE _local_communication_costing_enabled=TRUE _minimal_stats_aggregation=TRUE _mmv_query_rewrite_enabled=TRUE _new_initial_join_orders=TRUE _new_sort_cost_estimate=TRUE _nlj_batching_enabled=1 _optim_adjust_for_part_skews=TRUE _optim_enhance_nnull_detection=TRUE _optim_new_default_join_sel=TRUE _optim_peek_user_binds=TRUE _optimizer_adaptive_cursor_sharing=TRUE _optimizer_adaptive_plans=TRUE _optimizer_aggr_groupby_elim=TRUE _optimizer_ansi_join_lateral_enhance=TRUE _optimizer_ansi_rearchitecture=TRUE _optimizer_batch_table_access_by_rowid=TRUE _optimizer_better_inlist_costing='ALL' _optimizer_cbqt_no_size_restriction=TRUE _optimizer_cluster_by_rowid=TRUE _optimizer_cluster_by_rowid_batched=TRUE _optimizer_cluster_by_rowid_control=129 _optimizer_coalesce_subqueries=TRUE _optimizer_complex_pred_selectivity=TRUE _optimizer_compute_index_stats=TRUE _optimizer_connect_by_combine_sw=TRUE _optimizer_connect_by_cost_based=TRUE _optimizer_connect_by_elim_dups=TRUE _optimizer_correct_sq_selectivity=TRUE _optimizer_cost_based_transformation='LINEAR' _optimizer_cost_hjsmj_multimatch=TRUE _optimizer_cost_model='CHOOSE' _optimizer_cube_join_enabled=TRUE _optimizer_dim_subq_join_sel=TRUE _optimizer_distinct_agg_transform=TRUE _optimizer_distinct_elimination=TRUE _optimizer_distinct_placement=TRUE _optimizer_dsdir_usage_control=126 _optimizer_eliminate_filtering_join=TRUE _optimizer_enable_density_improvements=TRUE _optimizer_enable_extended_stats=TRUE _optimizer_enable_table_lookup_by_nl=TRUE _optimizer_enhanced_filter_push=TRUE _optimizer_extend_jppd_view_types=TRUE _optimizer_extended_cursor_sharing='UDO' _optimizer_extended_cursor_sharing_rel='SIMPLE' _optimizer_extended_stats_usage_control=192 _optimizer_false_filter_pred_pullup=TRUE _optimizer_fast_access_pred_analysis=TRUE _optimizer_fast_pred_transitivity=TRUE _optimizer_filter_pred_pullup=TRUE _optimizer_fkr_index_cost_bias=10 _optimizer_full_outer_join_to_outer=TRUE _optimizer_gather_feedback=TRUE _optimizer_gather_stats_on_load=TRUE _optimizer_group_by_placement=TRUE _optimizer_hybrid_fpwj_enabled=TRUE _optimizer_improve_selectivity=TRUE _optimizer_inmemory_access_path=TRUE _optimizer_inmemory_autodop=TRUE _optimizer_inmemory_bloom_filter=TRUE _optimizer_inmemory_cluster_aware_dop=TRUE _optimizer_inmemory_gen_pushable_preds=TRUE _optimizer_inmemory_minmax_pruning=TRUE _optimizer_inmemory_table_expansion=TRUE _optimizer_interleave_jppd=TRUE _optimizer_join_elimination_enabled=TRUE _optimizer_join_factorization=TRUE _optimizer_join_order_control=3 _optimizer_join_sel_sanity_check=TRUE _optimizer_max_permutations=2000 _optimizer_mode_force=TRUE _optimizer_multi_level_push_pred=TRUE _optimizer_multi_table_outerjoin=TRUE _optimizer_native_full_outer_join='FORCE' _optimizer_new_join_card_computation=TRUE _optimizer_nlj_hj_adaptive_join=TRUE _optimizer_null_accepting_semijoin=TRUE _optimizer_null_aware_antijoin=TRUE _optimizer_or_expansion='DEPTH' _optimizer_order_by_elimination_enabled=TRUE _optimizer_outer_join_to_inner=TRUE _optimizer_outer_to_anti_enabled=TRUE _optimizer_partial_join_eval=TRUE _optimizer_proc_rate_level='BASIC' _optimizer_push_down_distinct=0 _optimizer_push_pred_cost_based=TRUE _optimizer_reduce_groupby_key=TRUE _optimizer_rownum_bind_default=10 _optimizer_rownum_pred_based_fkr=TRUE _optimizer_skip_scan_enabled=TRUE _optimizer_sortmerge_join_inequality=TRUE _optimizer_squ_bottomup=TRUE _optimizer_star_tran_in_with_clause=TRUE _optimizer_strans_adaptive_pruning=TRUE _optimizer_system_stats_usage=TRUE _optimizer_table_expansion=TRUE _optimizer_transitivity_retain=TRUE _optimizer_try_st_before_jppd=TRUE _optimizer_undo_cost_change='12.1.0.2' _optimizer_unnest_corr_set_subq=TRUE _optimizer_unnest_disjunctive_subq=TRUE _optimizer_unnest_scalar_sq=TRUE _optimizer_use_cbqt_star_transformation=TRUE _optimizer_use_feedback=TRUE _optimizer_use_gtt_session_stats=TRUE _optimizer_use_histograms=TRUE _optimizer_vector_transformation=TRUE _or_expand_nvl_predicate=TRUE _ordered_nested_loop=TRUE _parallel_broadcast_enabled=TRUE _partition_large_extents='false' _partition_view_enabled=TRUE _pivot_implementation_method='CHOOSE' _pre_rewrite_push_pred=TRUE _pred_move_around=TRUE _push_join_predicate=TRUE _push_join_union_view=TRUE _push_join_union_view2=TRUE _px_adaptive_dist_method='CHOOSE' _px_concurrent=TRUE _px_cpu_autodop_enabled=TRUE _px_external_table_default_stats=TRUE _px_filter_parallelized=TRUE _px_filter_skew_handling=TRUE _px_groupby_pushdown='FORCE' _px_join_skew_handling=TRUE _px_minus_intersect=TRUE _px_object_sampling_enabled=TRUE _px_parallelize_expression=TRUE _px_partial_rollup_pushdown='ADAPTIVE' _px_partition_scan_enabled=TRUE _px_pwg_enabled=TRUE _px_replication_enabled=TRUE _px_scalable_invdist=TRUE _px_single_server_enabled=TRUE _px_ual_serial_input=TRUE _px_wif_dfo_declumping='CHOOSE' _px_wif_extend_distribution_keys=TRUE _query_rewrite_setopgrw_enable=TRUE _remove_aggr_subquery=TRUE _replace_virtual_columns=TRUE _right_outer_hash_enable=TRUE _selfjoin_mv_duplicates=TRUE _sql_model_unfold_forloops='RUN_TIME' _sqltune_category_parsed='DEFAULT' # parsed sqltune_category _subquery_pruning_enabled=TRUE _subquery_pruning_mv_enabled=FALSE _table_scan_cost_plus_one=TRUE _union_rewrite_for_gs='YES_GSET_MVS' _unnest_subquery=TRUE _use_column_stats_for_function=TRUE audit_trail='NONE' cluster_database=FALSE compatible='12.1.0.2.0' connection_brokers='((TYPE=DEDICATED)(BROKERS=1))' connection_brokers='((TYPE=EMON)(BROKERS=1))' # connection_brokers default value control_files='+DATA/_MGMTDB/CONTROLFILE/current.260.875037839' core_dump_dest='/u01/app/oracle/diag/rdbms/_mgmtdb/-MGMTDB/cdump' cpu_count=2 db_block_size=8192 db_create_file_dest='+DATA' db_domain='' db_name='_mgmtdb' diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=-MGMTDBXDB)' enable_pluggable_database=TRUE job_queue_processes=0 listener_networks='(( NAME=private_network) (LOCAL_LISTENER="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=169.254.123.194)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.1)(PORT=1521)))"))' local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.50.130)(PORT=1521))' log_buffer=5064K # log buffer update open_cursors=100 optimizer_dynamic_sampling=2 optimizer_mode='ALL_ROWS' parallel_min_servers=0 pga_aggregate_target=325M plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora processes=300 query_rewrite_enabled='TRUE' remote_listener=' rac20-scan:1521' remote_login_passwordfile='EXCLUSIVE' result_cache_max_size=3872K sga_target=752M skip_unusable_indexes=TRUE statistics_level='TYPICAL' undo_tablespace='UNDOTBS1'

Tablespaces SQL> SELECT con_id, tablespace_name 2 FROM cdb_tablespaces 3 ORDER BY 1,2; CON_ID TABLESPACE_NAME ---------- ---------------- 1 SYSAUX 1 SYSTEM 1 TEMP 1 UNDOTBS1 3 SYSAUX 3 SYSGRIDHOMEDATA 3 SYSMGMTDATA 3 SYSMGMTDATADB 3 SYSTEM 3 TEMP 3 USERS

New Tablespace Usage SQL> SELECT owner, tablespace_name, COUNT(*) 2 FROM cdb_tables 3 WHERE tablespace_name IN ('SYSGRIDHOMEDATA', 'SYSMGMTDATA', 'SYSMGMTDATADB') 4 GROUP BY owner, tablespace_name 5* ORDER BY 1,2; OWNER TABLESPACE_NAME COUNT(*) ------ ---------------- ---------- CHM SYSMGMTDATA 4 -- CHM stands for Cluster Health Monitor

Data Files SQL> SELECT con_id, file_name, tablespace_name, bytes/1024/1024/1024 GB 2 FROM cdb_data_files; CON_ID FILE_NAME ------ ---------------------------------------------------------------------------------- TABLESPACE_NAME GB ---------------- ----- 1 +DATA/_MGMTDB/DATAFILE/sysaux.257.875037751 SYSAUX .391 1 +DATA/_MGMTDB/DATAFILE/system.258.875037771 SYSTEM .488 1 +DATA/_MGMTDB/DATAFILE/undotbs1.259.875037797 UNDOTBS1 .078 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysaux.271.875038167 SYSAUX .146 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysgridhomedata.272.875038167 SYSGRIDHOMEDATA .098 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmgmtdata.269.875038167 SYSMGMTDATA 2.000 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/sysmem.270.875038167 SYSMGMTDATADB .098 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/systgmtdatadb.273.875038167 SYSTEM .156 3 +DATA/_MGMTDB/FD9B43BF6A646F8CE043B6A9E80A2815/DATAFILE/users.274.875038167 USERS .005

Free Space SQL> SELECT tablespace_name, SUM(bytes/1024/1024) AS FREE_MB 2 FROM dba_free_space 3 GROUP BY tablespace_name 4* ORDER BY 1; CON_ID TABLESPACE_NAME FREE_MB ---------- ------------------------------ ---------- 1 SYSAUX 130.6875 1 SYSTEM 139.75 1 UNDOTBS1 57.75 3 SYSAUX 7.625 3 SYSGRIDHOMEDATA 99 3 SYSMGMTDATA 1067 3 SYSMGMTDATADB 93 3 SYSTEM 9.8125 3 USERS 4

Instance

O/S processes [oracle@rac20a ~]$ ps -ef | grep pmon oracle 3595 1 0 08:34 ? 00:00:02 asm_pmon_+ASM1 oracle 4186 1 0 08:35 ? 00:00:01 mdb_pmon_-MGMTDB oracle 17867 1 0 15:34 ? 00:00:00 ora_pmon_rac20ctr1 oracle 23323 23282 0 15:50 pts/3 00:00:00 grep pmon

Instance Information SQL> SELECT instance_name INST_NAME, host_name, version, status, 2 database_status DB_STATUS, instance_role INST_ROLE, active_state, instance_mode 3 FROM v$instance; INST_NAME HOST_NAME VERSION STATUS DB_STATUS INST_ROLE ACTIVE_ST INSTANCE_MO --------- --------- ---------- ------ --------- ---------------- --------- ----------- -MGMTDB rac20a 12.1.0.2.0 OPEN ACTIVE PRIMARY_INSTANCE NORMAL REGULAR

Services SQL> SELECT con_id, name, network_name, enabled, pdb 2 FROM cdb_services 3* ORDER BY con_id, name; CON_ID NAME NETWORK_NAME ENA PDB ------- --------------- ------------- --- ------------- 1 -MGMTDBXDB -MGMTDBXDB NO CDB$ROOT 1 SYS$BACKGROUND NO CDB$ROOT 1 SYS$USERS NO CDB$ROOT 1 _mgmtdb_ NO CDB$ROOT 3 rac20_cluster rac20_cluster NO RAC20_CLUSTER

SQL*Net

SQLNET.ORA [oracle@rac20a admin]$ more sqlnet.ora # sqlnet.ora.rac20a Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/sqlnet.ora.rac20a # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

LISTENER.ORA [oracle@rac20a admin]$ more listener.ora MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent # listener.ora Network Configuration File: /u01/app/12.1.0.2/grid/network/admin/listener.ora # Generated by Oracle configuration tools. ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM = SUBNET ASMNET1LSNR_ASM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = ASMNET1LSNR_ASM)) ) ) VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM = ON ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON LISTENER_SCAN3 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3)) ) ) LISTENER_SCAN2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2)) ) ) LISTENER_SCAN1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent

Database

Feature Usage Report SQL> spool /home/oracle/fur.txt SQL> SELECT output FROM TABLE(dbms_feature_usage_report.display_text); OUTPUT DB Name DB Id Release Total Samples Last Sample Time ---------------------------- -------- ----------- ----------- ------------- ------------------ DB FEATURE USAGE report for _MGMTDB 1079311886 12.1.0.2.0 1 23-Mar-15 00:08:48 DB Feature Usage Summary DB/Release: _MGMTDB/12.1.0.2.0 -> This section displays the summary of Usage for Database Features. -> The Currently Used column is TRUE if usage was detected for the feature at the last sample time. Currently Detected Total Last Usage Feature Name Used Usages Samples Time ----------------------------------------- ----- ------- -------- -------------- Adaptive Plans TRUE 1 1 03/23/15 00:08 Automatic Maintenance - Optimizer Statist TRUE 1 1 03/23/15 00:08 Automatic Maintenance - SQL Tuning Adviso TRUE 1 1 03/23/15 00:08 Automatic Maintenance - Space Advisor TRUE 1 1 03/23/15 00:08 Automatic Reoptimization TRUE 1 1 03/23/15 00:08 Automatic SGA Tuning TRUE 1 1 03/23/15 00:08 Automatic SQL Execution Memory TRUE 1 1 03/23/15 00:08 Automatic Segment Space Management (syste TRUE 1 1 03/23/15 00:08 Automatic Storage Management TRUE 1 1 03/23/15 00:08 Automatic Undo Management TRUE 1 1 03/23/15 00:08 Bigfile Tablespace TRUE 1 1 03/23/15 00:08 Character Set TRUE 1 1 03/23/15 00:08 Deferred Segment Creation TRUE 1 1 03/23/15 00:08 Locally Managed Tablespaces (system) TRUE 1 1 03/23/15 00:08 Locally Managed Tablespaces (user) TRUE 1 1 03/23/15 00:08 Oracle Pluggable Databases TRUE 1 1 03/23/15 00:08 Parallel SQL Query Execution TRUE 1 1 03/23/15 00:08 Partitioning (system) TRUE 1 1 03/23/15 00:08 Real-Time SQL Monitoring TRUE 1 1 03/23/15 00:08 Result Cache TRUE 1 1 03/23/15 00:08 SQL Plan Directive TRUE 1 1 03/23/15 00:08 SecureFiles (system) TRUE 1 1 03/23/15 00:08 SecureFiles (user) TRUE 1 1 03/23/15 00:08 Server Parameter File TRUE 1 1 03/23/15 00:08 Services TRUE 1 1 03/23/15 00:08 Unified Audit TRUE 1 1 03/23/15 00:08 ADDM FALSE 0 1 ASO native encryption and checksumming FALSE 0 1 AWR Baseline FALSE 0 1 AWR Baseline Template FALSE 0 1 AWR Report FALSE 0 1 Active Data Guard - Real-Time Query on Ph FALSE 0 1 Advanced Index Compression FALSE 0 1 Advanced Replication FALSE 0 1 Application Express FALSE 0 1 Automatic Memory Tuning FALSE 0 1 Automatic SQL Tuning Advisor FALSE 0 1 Automatic Segment Space Management (user) FALSE 0 1 Automatic Workload Repository FALSE 0 1 BA Owner FALSE 0 1 Backup BASIC Compression FALSE 0 1 Backup BZIP2 Compression FALSE 0 1 Backup Encryption FALSE 0 1 Backup HIGH Compression FALSE 0 1 Backup LOW Compression FALSE 0 1 Backup MEDIUM Compression FALSE 0 1 Backup Rollforward FALSE 0 1 Backup ZLIB Compression FALSE 0 1 Backup and Restore of plugged database FALSE 0 1 Baseline Adaptive Thresholds FALSE 0 1 Baseline Static Computations FALSE 0 1 Block Media Recovery FALSE 0 1 CONVERT command FALSE 0 1 CSSCAN FALSE 0 1 Change Data Capture FALSE 0 1 Change-Aware Incremental Backup FALSE 0 1 Character Semantics FALSE 0 1 Client Identifier FALSE 0 1 Clusterwide Global Transactions FALSE 0 1 Compression Advisor FALSE 0 1 Concurrent Statistics Gathering FALSE 0 1 Container Usage FALSE 0 1 Cross-Platform Backups FALSE 0 1 Crossedition Triggers FALSE 0 1 DBFS Content FALSE 0 1 DBFS HS FALSE 0 1 DBFS SFS FALSE 0 1 DBMS_STATS Incremental Maintenance FALSE 0 1 Data Guard FALSE 0 1 Data Mining FALSE 0 1 Data Recovery Advisor FALSE 0 1 Data Redaction FALSE 0 1 Database Migration Assistant for Unicode FALSE 0 1 Database Replay: Workload Capture FALSE 0 1 Database Replay: Workload Replay FALSE 0 1 Database Resident Connection Pooling (DRC FALSE 0 1 Deferred Open Read Only FALSE 0 1 Direct NFS FALSE 0 1 Duplicate Db from Active Db using BackupS FALSE 0 1 Dynamic SGA FALSE 0 1 EM Cloud Control FALSE 0 1 EM Express FALSE 0 1 EM Performance Page FALSE 0 1 Editioning Views FALSE 0 1 Editions FALSE 0 1 Encrypted Tablespaces FALSE 0 1 Exadata FALSE 0 1 Extensibility FALSE 0 1 File Mapping FALSE 0 1 Fine Grained Audit FALSE 0 1 Flashback Data Archive FALSE 0 1 Flashback Database FALSE 0 1 GDS Catalog FALSE 0 1 Gateways FALSE 0 1 Global Data Services FALSE 0 1 GoldenGate FALSE 0 1 HeapCompression FALSE 0 1 Heat Map FALSE 0 1 Hybrid Columnar Compression FALSE 0 1 Hybrid Columnar Compression Row Level Loc FALSE 0 1 INSTANT RESTORE command FALSE 0 1 In-Memory Aggregation FALSE 0 1 In-Memory Column Store FALSE 0 1 Index Organized Tables FALSE 0 1 Information Lifecycle Management FALSE 0 1 Instance Caging FALSE 0 1 Internode Parallel Execution FALSE 0 1 JSON FALSE 0 1 Job Scheduler FALSE 0 1 LOB FALSE 0 1 Label Security FALSE 0 1 Locator FALSE 0 1 Logfile Multiplexing FALSE 0 1 Long-term Archival Backup FALSE 0 1 MTTR Advisor FALSE 0 1 Materialized Views (User) FALSE 0 1 Messaging Gateway FALSE 0 1 Multi Section Backup FALSE 0 1 Multiple Block Sizes FALSE 0 1 OLAP - Analytic Workspaces FALSE 0 1 OLAP - Cubes FALSE 0 1 Object FALSE 0 1 Online Move Datafile FALSE 0 1 Online Redefinition FALSE 0 1 Oracle Advanced Network Compression Servi FALSE 0 1 Oracle Database Vault FALSE 0 1 Oracle In-Database Hadoop FALSE 0 1 Oracle Java Virtual Machine (system) FALSE 0 1 Oracle Java Virtual Machine (user) FALSE 0 1 Oracle Managed Files FALSE 0 1 Oracle Multimedia FALSE 0 1 Oracle Multimedia DICOM FALSE 0 1 Oracle Secure Backup FALSE 0 1 Oracle Text FALSE 0 1 Oracle Utility Datapump (Export) FALSE 0 1 Oracle Utility Datapump (Import) FALSE 0 1 Oracle Utility External Table FALSE 0 1 Oracle Utility Metadata API FALSE 0 1 Oracle Utility SQL Loader (Direct Path Lo FALSE 0 1 PL/SQL Native Compilation FALSE 0 1 Parallel SQL DDL Execution FALSE 0 1 Parallel SQL DML Execution FALSE 0 1 Partitioning (user) FALSE 0 1 Pillar Storage FALSE 0 1 Pillar Storage with EHCC FALSE 0 1 Privilege Capture FALSE 0 1 Quality of Service Management FALSE 0 1 RMAN - Disk Backup FALSE 0 1 RMAN - Tape Backup FALSE 0 1 Read Only Tablespace FALSE 0 1 Real Application Cluster One Node FALSE 0 1 Real Application Clusters (RAC) FALSE 0 1 Real Application Security FALSE 0 1 Recover Table FALSE 0 1 Recover Until Snapshot FALSE 0 1 Recovery Area FALSE 0 1 Recovery Manager (RMAN) FALSE 0 1 Resource Manager FALSE 0 1 Restore Point FALSE 0 1 Rules Manager FALSE 0 1 SPM Evolve Advisor FALSE 0 1 SQL Access Advisor FALSE 0 1 SQL Monitoring and Tuning pages FALSE 0 1 SQL Performance Analyzer FALSE 0 1 SQL Plan Management FALSE 0 1 SQL Profile FALSE 0 1 SQL Repair Advisor FALSE 0 1 SQL Tuning Advisor FALSE 0 1 SQL Tuning Set (system) FALSE 0 1 SQL Tuning Set (user) FALSE 0 1 SQL Workload Manager FALSE 0 1 SecureFile Compression (system) FALSE 0 1 SecureFile Compression (user) FALSE 0 1 SecureFile Deduplication (system) FALSE 0 1 SecureFile Deduplication (user) FALSE 0 1 SecureFile Encryption (system) FALSE 0 1 SecureFile Encryption (user) FALSE 0 1 Segment Advisor (user) FALSE 0 1 Segment Maintenance Online Compress FALSE 0 1 Segment Shrink FALSE 0 1 Semantics/RDF FALSE 0 1 Server Flash Cache FALSE 0 1 Shared Server FALSE 0 1 Spatial FALSE 0 1 Streams (system) FALSE 0 1 Streams (user) FALSE 0 1 Sun ZFS with EHCC FALSE 0 1 TRANSPORT TABLESPACE command FALSE 0 1 Traditional Audit FALSE 0 1 Transparent Data Encryption FALSE 0 1 Transparent Gateway FALSE 0 1 Transparent Sensitive Data Protection FALSE 0 1 Transportable Tablespace FALSE 0 1 Tune MView FALSE 0 1 Undo Advisor FALSE 0 1 Very Large Memory FALSE 0 1 Virtual Private Database (VPD) FALSE 0 1 Workspace Manager FALSE 0 1 XDB FALSE 0 1 XStream In FALSE 0 1 XStream Out FALSE 0 1 XStream Streams FALSE 0 1 ZFS Storage FALSE 0 1 Zone maps FALSE 0 1 ---------------------------------------------------------------- DB Feature Usage Details DB/Release: _MGMTDB/12.1.0.2.0 -> This section displays the detailed usage data for the features -> that have at least one detected usage Feature Name Detected Usages Total Samples First Usage Date Last Usage Date Aux Count Feature Info ---------------- --------------- -------------- ----------------- ----------------- ---------- -------- Adaptive Plans 1 1 03/23/15 00:08 03/23/15 00:08 N/A Total number of queries: 300 Number of queries with an adaptive plan: 10 Per ... Automatic Maintenance 1 1 03/23/15 00:08 03/23/15 00:08 0 - Optimizer Statistics Gathering Automatic Maintenance 1 1 03/23/15 00:08 03/23/15 00:08 0 - SQL Tuning Advisor Automatic Maintenance 1 1 03/23/15 00:08 03/23/15 00:08 0 - Space Advisor Automatic Reoptimization 1 1 03/23/15 00:08 03/23/15 00:08 N/A Total number of queries: 293 Number of reoptimizable queries: 7 Percentage o ... Automatic SGA Tuning 1 1 03/23/15 00:08 03/23/15 00:08 0 :sga_target:788529152:sga_max_size:788529152:comp:shared pool:cur:197132288: ... Automatic SQL Execution Memory 1 1 03/23/15 00:08 03/23/15 00:08 0 Automatic Segment Space Management (system) 1 1 03/23/15 00:08 03/23/15 00:08 1 (Segment Space Management: MANUAL, TS Count: 2, Size MB: 580) (Segment Space ... Automatic Storage Management 1 1 03/23/15 00:08 03/23/15 00:08 8,188 Redundancy:EXTERN=1:total_diskgroup_size:8188:max_diskgroup_size:8188:min_di ... Automatic Undo Management 1 1 03/23/15 00:08 03/23/15 00:08 1 (Retention: NOGUARANTEE, TS Count: 1, Size MB: 80) (Undo Blocks: 11142, Max ... Bigfile Tablespace 1 1 03/23/15 00:08 03/23/15 00:08 N/A Character Set 1 1 03/23/15 00:08 03/23/15 00:08 N/A AL32UTF8 Deferred Segment Creation 1 1 03/23/15 00:08 03/23/15 00:08 45 Deferred Segment Creation Parameter:1 Total Deferred Segments:45 Total Crea ... Locally Managed Tablespaces (system) 1 1 03/23/15 00:08 03/23/15 00:08 3 (Extent Management: LOCAL, TS Count: 3, Size MB: 980) Locally Managed Tablespaces (user) 1 1 03/23/15 00:08 03/23/15 00:08 N/A Oracle Pluggable Databases 1 1 03/23/15 00:08 03/23/15 00:08 1 Parallel SQL Query Execution 1 1 03/23/15 00:08 03/23/15 00:08 N/A Partitioning (system) 1 1 03/23/15 00:08 03/23/15 00:08 6 1:T:INTERVAL::1048575:0:1:::::3::0:ON-0::N:N:|1:I:INTERVAL::1048575:0:1::L:N ... Real-Time SQL Monitoring 1 1 03/23/15 00:08 03/23/15 00:08 23 <sqlmon_usage> <num_em_reports>0</num_em_reports> <first_db_report_time> ... Result Cache 1 1 03/23/15 00:08 03/23/15 00:08 134 SQL Plan Directive 1 1 03/23/15 00:08 03/23/15 00:08 78 Number of directives with type, DYNAMIC_SAMPLING: 78 Number of Directives wi ... SecureFiles (system) 1 1 03/23/15 00:08 03/23/15 00:08 371 SecureFiles (user) 1 1 03/23/15 00:08 03/23/15 00:08 9 Server Parameter File 1 1 03/23/15 00:08 03/23/15 00:08 N/A Services 1 1 03/23/15 00:08 03/23/15 00:08 1 num_clb_long: 1 num_clb_short: 0 num_goal_service_time: 0 num_goal_throughp ... Unified Audit 1 1 03/23/15 00:08 03/23/15 00:08 0 Number of Unified Audit policies=7; Number of Enabled Unified Audit policies ... ------------------------------------------------------------- High Water Mark Statistics DB/Release: _MGMTDB/12.1.0.2.0 -> This section displays the Database High Water Mark Statistics Name High Water Mark ----------------------------------------------------------- --------------- Maximum Number of Active Sessions seen in the system 0 Maximum Number of CPUs 1 Maximum Number of Concurrent Sessions seen in the database 10 Maximum Number of Datafiles 3 Maximum Number of Materialized Views (User) 0 Maximum Number of Partitions belonging to an User Index 0 Maximum Number of Partitions belonging to an User Table 0 Maximum Number of SQL NCHAR Columns 105 Maximum Number of Tablespaces 4 Maximum Query Length 1,552 Maximum Size of the Database (Bytes) 1,027,604,480 Number of User Indexes 469 Number of User Tables 115 Number of global services 0 Number of job runs per day 11 Number of physical disks N/A Oracle Database instances 1 Size of Largest Segment (Bytes) 47,185,920 --------------------------------------------------------------------------- CPU Usage Statistics DB/Release: _MGMTDB/12.1.0.2.0 -> This section displays the Database CPU Usage Statistics Timestamp CPU Core CPU Socket CPU Count -------------- -------- ---------- --------- 03/22/15 19:08 1 1 1 03/23/15 19:35 2 1 1 ------------------------------------------------------------- End of Report

Database Users SQL> SELECT con_id, username, account_status 2 FROM cdb_users 3* ORDER BY 1,2; CON_ID USERNAME ACCOUNT_STATUS ------- ------------------ ----------------- 1 ANONYMOUS EXPIRED & LOCKED 1 APPQOSSYS EXPIRED & LOCKED 1 AUDSYS EXPIRED & LOCKED 1 DBSNMP EXPIRED & LOCKED 1 DIP EXPIRED & LOCKED 1 GSMADMIN_INTERNAL EXPIRED & LOCKED 1 GSMCATUSER EXPIRED & LOCKED 1 GSMUSER EXPIRED & LOCKED 1 ORACLE_OCM EXPIRED & LOCKED 1 OUTLN EXPIRED & LOCKED 1 SYS OPEN 1 SYSBACKUP EXPIRED & LOCKED 1 SYSDG EXPIRED & LOCKED 1 SYSKM EXPIRED & LOCKED 1 SYSTEM OPEN 1 WMSYS EXPIRED & LOCKED 1 XDB EXPIRED & LOCKED 1 XS$NULL EXPIRED & LOCKED 3 ANONYMOUS EXPIRED & LOCKED 3 APPQOSSYS EXPIRED & LOCKED 3 AUDSYS EXPIRED & LOCKED 3 CHA EXPIRED & LOCKED 3 CHM OPEN 3 DBSNMP EXPIRED & LOCKED 3 DIP EXPIRED & LOCKED 3 EMUSER EXPIRED & LOCKED 3 GHSUSER EXPIRED & LOCKED 3 GSMADMIN_INTERNAL EXPIRED & LOCKED 3 GSMCATUSER EXPIRED & LOCKED 3 GSMUSER EXPIRED & LOCKED 3 ORACLE_OCM EXPIRED & LOCKED 3 OUTLN EXPIRED & LOCKED 3 PCMRADMIN OPEN 3 PDBADMIN OPEN 3 SYS OPEN 3 SYSBACKUP EXPIRED & LOCKED 3 SYSDG EXPIRED & LOCKED 3 SYSKM EXPIRED & LOCKED 3 SYSTEM OPEN 3 WMSYS EXPIRED & LOCKED 3 XDB EXPIRED & LOCKED 3 XS$NULL EXPIRED & LOCKED 42 rows selected.

Object Count SQL> SELECT owner, object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE con_id = 3 4 AND owner NOT IN ( 5 SELECT username 6 FROM cdb_users 7 WHERE con_id IN (1,2)) 8 GROUP BY owner, object_type 9* ORDER BY 1,2; OWNER OBJECT_TYPE COUNT(*) ---------- ----------------- ---------- CHM INDEX 1 CHM INDEX PARTITION 54 CHM TABLE 10 CHM TABLE PARTITION 324 CHM VIEW 9 PCMRADMIN TABLE 2 PUBLIC SYNONYM 5178

Table Count SQL> SELECT con_id, owner, COUNT(*) 2 FROM cdb_tables 3 GROUP BY con_id, owner 4* ORDER BY 1,2; CON_ID OWNER COUNT(*) ---------- -------------------- ---------- 1 APPQOSSYS 4 1 DBSNMP 20 1 GSMADMIN_INTERNAL 19 1 OUTLN 3 1 SYS 1224 1 SYSTEM 178 1 WMSYS 40 1 XDB 29 3 APPQOSSYS 4 3 CHM 10 3 DBSNMP 20 3 GSMADMIN_INTERNAL 19 3 OUTLN 3 3 PCMRADMIN 2 3 SYS 1223 3 SYSTEM 178 3 WMSYS 40 3 XDB 29

New Owner Objects SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'CHA' 4 GROUP BY object_type; no rows selected SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'CHM' 4* GROUP BY object_type; OBJECT_TYPE COUNT(*) ---------------- ---------- TABLE 10 INDEX PARTITION 26 TABLE PARTITION 156 INDEX 1 VIEW 9 SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'EMUSER' 4* GROUP BY object_type; no rows selected SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'GHSUSER' 4* GROUP BY object_type; no rows selected SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'PCMRADMIN' 4* GROUP BY object_type; OBJECT_TYPE COUNT(*) ----------------------- ---------- TABLE 2 SQL> SELECT object_type, COUNT(*) 2 FROM cdb_objects 3 WHERE owner = 'PDBADMIN' 4* GROUP BY object_type; no rows selected

CHA Tables conn / as sysdba SQL> SELECT table_name, table_type, tablespace_name 2 FROM cdb_all_tables 3 WHERE owner = 'CHM' 4 ORDER BY 1; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ CHMOS_ACTIVE_CONFIG_INT_TBL SYSMGMTDATA CHMOS_ASM_CONFIG_INT_TBL SYSMGMTDATA CHMOS_CPU_INT_TBL CHMOS_DEVICE_INT_TBL CHMOS_FILESYSTEM_INT_TBL CHMOS_NIC_INT_TBL CHMOS_PROCESS_INT_TBL CHMOS_STATIC_CONFIG_INT_TBL SYSMGMTDATA CHMOS_SYSTEM_PERIODIC_INT_TBL SYSMGMTDATA CHMOS_SYSTEM_SAMPLE_INT_TBL

CHA Views conn / as sysdba SQL> SELECT view_name 2 FROM cdb_views 3 WHERE owner = 'CHM' 4* ORDER BY 1; VIEW_NAME ------------------------------ CHMOS_CPU_DATA CHMOS_DEVICE_CONFIGURATION CHMOS_DEVICE_DATA CHMOS_FILESYSTEM_DATA CHMOS_NETPROTOCOL_DATA CHMOS_NIC_CONFIGURATION CHMOS_NIC_DATA CHMOS_PROCESS_DATA CHMOS_SYSTEM_DATA

CHM Schema Tables conn / as sysdba SQL> alter session set container = RAC20_CLUSTER; SQL> SELECT COUNT(*) 2 FROM chm.chmos_cpu_int_tbl; COUNT(*) ---------- 50016 SQL> SELECT table_name, num_rows 2 FROM dba_tables 3 WHERE owner = 'CHM'; TABLE_NAME NUM_ROWS ------------------------------ ---------- CHMOS_STATIC_CONFIG_INT_TBL CHMOS_ACTIVE_CONFIG_INT_TBL CHMOS_SYSTEM_PERIODIC_INT_TBL CHMOS_ASM_CONFIG_INT_TBL CHMOS_SYSTEM_SAMPLE_INT_TBL CHMOS_CPU_INT_TBL CHMOS_PROCESS_INT_TBL CHMOS_DEVICE_INT_TBL CHMOS_NIC_INT_TBL CHMOS_FILESYSTEM_INT_TBL -- collect schema stats on CHM SQL> exec dbms_stats.gather_schema_stats('CHM', CASCADE=>TRUE); -- takes an extraordinary amount of time SQL> SELECT table_name, num_rows 2 FROM dba_tables 3 WHERE owner = 'CHM'; TABLE_NAME NUM_ROWS ------------------------------ ---------- CHMOS_STATIC_CONFIG_INT_TBL 2 CHMOS_ACTIVE_CONFIG_INT_TBL 13 CHMOS_SYSTEM_PERIODIC_INT_TBL 5800 CHMOS_ASM_CONFIG_INT_TBL 0 CHMOS_SYSTEM_SAMPLE_INT_TBL 30391 CHMOS_CPU_INT_TBL 50070 CHMOS_PROCESS_INT_TBL 1912830 CHMOS_DEVICE_INT_TBL 485744 CHMOS_NIC_INT_TBL 91107 CHMOS_FILESYSTEM_INT_TBL 30363 -- if some job doesn't purge this it is going to eat a lot of space very quickly SQL> SELECT MIN(sampletime), MAX(sampletime) 2 FROM chm.chmos_process_int_tbl; MIN(SAMPLETIME) MAX(SAMPLETIME) -------------------- -------------------- 22-MAR-2015 17:41:45 23-MAR-2015 18:02:43

CHMOS_PROCESS_INT_TBL desc chm.chmos_process_int_tbl SQL> SELECT hostname, TRUNC(sampletime) AS SAMPLETIME, COUNT(*) 2 FROM chm.chmos_process_int_tbl 3 GROUP BY hostname, TRUNC(sampletime) 4* ORDER BY 2,1; HOSTNAME SAMPLETIM COUNT(*) --------- --------- --------- rac20a 23-MAR-15 1124591 rac20b 23-MAR-15 720261 rac20a 24-MAR-15 1434824 rac20b 24-MAR-15 1189507 rac20a 25-MAR-15 1436943 rac20b 25-MAR-15 1217354 rac20a 26-MAR-15 1082837 rac20b 26-MAR-15 1029808

Hopefully one of these jobs purges CHM data SQL> show con_id 3 SQL> SELECT owner, job_name, state, failure_count 2 FROM dba_scheduler_jobs 3* ORDER BY 1,2; OWNER JOB_NAME STATE FAILURE_COUNT ------ ------------------------------ ---------- ------------- SYS BSLN_MAINTAIN_STATS_JOB SCHEDULED 0 SYS CLEANUP_NON_EXIST_OBJ SCHEDULED 0 SYS CLEANUP_ONLINE_IND_BUILD SCHEDULED 0 SYS CLEANUP_ONLINE_PMO SCHEDULED 0 SYS CLEANUP_TAB_IOT_PMO SCHEDULED 0 SYS CLEANUP_TRANSIENT_PKG SCHEDULED 0 SYS CLEANUP_TRANSIENT_TYPE SCHEDULED 0 SYS DRA_REEVALUATE_OPEN_FAILURES SCHEDULED 0 SYS FGR$AUTOPURGE_JOB DISABLED 0 SYS FILE_SIZE_UPD SCHEDULED 0 SYS FILE_WATCHER DISABLED 0 SYS HM_CREATE_OFFLINE_DICTIONARY DISABLED 0 SYS LOAD_OPATCH_INVENTORY DISABLED 0 SYS ORA$AUTOTASK_CLEAN SCHEDULED 0 SYS PMO_DEFERRED_GIDX_MAINT_JOB SCHEDULED 0 SYS PURGE_LOG SCHEDULED 0 SYS RSE$CLEAN_RECOVERABLE_SCRIPT SCHEDULED 0 SYS SM$CLEAN_AUTO_SPLIT_MERGE SCHEDULED 0 SYS XMLDB_NFS_CLEANUP_JOB DISABLED 0 -- each of these jobs is a standard 12cR1 installation job so none of these jobs purges CHM or other tables

PCRADMIN tables SQL> SELECT table_name, tablespace_name 2 FROM cdb_tables 3 WHERE owner = 'PCMRADMIN' 4 ORDER BY 1; TABLE_NAME TABLESPACE_NAME ---------------- --------------- GIMR_CONFIG_TBL SYSTEM GIMR_VERSION_TBL SYSTEM