AWS RDS for Oracleで変更できない(あるいはできる)初期化パラメータ一覧の取得


# やりたいこと

AWS RDS for Oracleでは初期化パラメータを変更することができますが、すべてを変更できるわけではありません。
AWS CLIで変更できない（あるいはできる）パラメータの一覧の取得しました。

Amazon RDS(Oracle)　Oracle Database 12.1インスタンスのデフォルトのパラメータグループ「default.oracle-ee-12.1」で確認。

--fileterがdescribe-db-parametersでサポートされていないようなので結果出力後grep(JSONの場合はjq)でフィルタしています。

# 変更できないパラメータ一覧（IsModifiable=false）

## コマンド

```
aws rds describe-db-parameters --db-parameter-group-name  default.oracle-ee-12.1  --query 'Parameters[].[ParameterName,IsModifiable]'   --output table | grep -v True
```

JSON形式の場合

```
aws rds describe-db-parameters --db-parameter-group-name  default.oracle-ee-12.1 --output json |jq '.Parameters[] | if .IsModifiable == false then .ParameterName else empty end'
```

## 結果

```
----------------------------------------------------------
|                  DescribeDBParameters                  |
+-----------------------------------------------+--------+
|  _gc_defer_time                               |  False |
|  archive_lag_target                           |  False |
|  audit_file_dest                              |  False |
|  audit_sys_operations                         |  False |
|  audit_syslog_level                           |  False |
|  background_dump_dest                         |  False |
|  commit_write                                 |  False |
|  connection_brokers                           |  False |
|  control_files                                |  False |
|  core_dump_dest                               |  False |
|  db_8k_cache_size                             |  False |
|  db_block_size                                |  False |
|  db_create_file_dest                          |  False |
|  db_create_online_log_dest_1                  |  False |
|  db_create_online_log_dest_2                  |  False |
|  db_create_online_log_dest_3                  |  False |
|  db_create_online_log_dest_4                  |  False |
|  db_create_online_log_dest_5                  |  False |
|  db_file_name_convert                         |  False |
|  db_name                                      |  False |
|  db_recovery_file_dest                        |  False |
|  db_unique_name                               |  False |
|  db_unrecoverable_scn_tracking                |  False |
|  dbwr_io_slaves                               |  False |
|  dg_broker_config_file1                       |  False |
|  dg_broker_config_file2                       |  False |
|  dg_broker_start                              |  False |
|  diagnostic_dest                              |  False |
|  disk_asynch_io                               |  False |
|  dst_upgrade_insert_conv                      |  False |
|  enable_pluggable_database                    |  False |
|  event                                        |  False |
|  fal_client                                   |  False |
|  fal_server                                   |  False |
|  filesystemio_options                         |  False |
|  fixed_date                                   |  False |
|  gcs_server_processes                         |  False |
|  ifile                                        |  False |
|  inmemory_max_populate_servers                |  False |
|  instance_name                                |  False |
|  java_jit_enabled                             |  False |
|  java_max_sessionspace_size                   |  False |
|  java_pool_size                               |  False |
|  java_soft_sessionspace_limit                 |  False |
|  ldap_directory_sysauth                       |  False |
|  listener_networks                            |  False |
|  local_listener                               |  False |
|  lock_sga                                     |  False |
|  log_archive_config                           |  False |
|  log_archive_dest                             |  False |
|  log_archive_dest_1                           |  False |
|  log_archive_dest_state_1                     |  False |
|  log_archive_format                           |  False |
|  log_archive_local_first                      |  False |
|  log_file_name_convert                        |  False |
|  max_enabled_roles                            |  False |
|  max_string_size                              |  False |
|  o7_dictionary_accessibility                  |  False |
|  os_authent_prefix                            |  False |
|  os_roles                                     |  False |
|  pdb_file_name_convert                        |  False |
|  processor_group_name                         |  False |
|  rdbms_server_dn                              |  False |
|  read_only_open_delayed                       |  False |
|  recovery_parallelism                         |  False |
|  redo_transport_user                          |  False |
|  remote_listener                              |  False |
|  remote_login_passwordfile                    |  False |
|  remote_os_authent                            |  False |
|  remote_os_roles                              |  False |
|  rollback_segments                            |  False |
|  service_names                                |  False |
|  shared_memory_address                        |  False |
|  spatial_vector_acceleration                  |  False |
|  spfile                                       |  False |
|  standby_archive_dest                         |  False |
|  standby_file_management                      |  False |
|  thread                                       |  False |
|  threaded_execution                           |  False |
|  undo_management                              |  False |
|  use_dedicated_broker                         |  False |
|  use_indirect_data_buffers                    |  False |
|  use_large_pages                              |  False |
|  user_dump_dest                               |  False |
|  utl_file_dir                                 |  False |
|  xml_db_events                                |  False |
+-----------------------------------------------+--------+
```

# 変更できるパラメータ一覧（IsModifiable=true）

## コマンド

```
aws rds describe-db-parameters --db-parameter-group-name  default.oracle-ee-12.1  --query 'Parameters[].[ParameterName,IsModifiable]'   --output table | grep -v False
```

JSON形式の場合

```
aws rds describe-db-parameters --db-parameter-group-name  default.oracle-ee-12.1 --output json |jq '.Parameters[] | if .IsModifiable == true then .ParameterName else empty end'
```

## 結果

```
----------------------------------------------------------
|                  DescribeDBParameters                  |
+-----------------------------------------------+--------+
|  _allow_level_without_connect_by              |  True  |
|  _always_semi_join                            |  True  |
|  _b_tree_bitmap_plans                         |  True  |
|  _cursor_features_enabled                     |  True  |
|  _cursor_obsolete_threshold                   |  True  |
|  _disable_function_based_index                |  True  |
|  _enable_shared_pool_durations                |  True  |
|  _fast_full_scan_enabled                      |  True  |
|  _fix_control                                 |  True  |
|  _gby_hash_aggregation_enabled                |  True  |
|  _ignore_desc_in_index                        |  True  |
|  _kks_obsolete_dump_threshold                 |  True  |
|  _like_with_bind_as_equality                  |  True  |
|  _no_or_expansion                             |  True  |
|  _optim_peek_user_binds                       |  True  |
|  _optimizer_adaptive_cursor_sharing           |  True  |
|  _optimizer_autostats_job                     |  True  |
|  _optimizer_cartesian_enabled                 |  True  |
|  _optimizer_extended_cursor_sharing           |  True  |
|  _optimizer_extended_cursor_sharing_rel       |  True  |
|  _optimizer_gather_feedback                   |  True  |
|  _optimizer_join_elimination_enabled          |  True  |
|  _optimizer_join_sel_sanity_check             |  True  |
|  _optimizer_max_permutations                  |  True  |
|  _optimizer_mjc_enabled                       |  True  |
|  _optimizer_sortmerge_join_enabled            |  True  |
|  _optimizer_use_feedback                      |  True  |
|  _partition_view_enabled                      |  True  |
|  _report_capture_cycle_time                   |  True  |
|  _sort_elimination_cost_ratio                 |  True  |
|  _sqlexec_progression_cost                    |  True  |
|  _unnest_subquery                             |  True  |
|  _use_single_log_writer                       |  True  |
|  aq_tm_processes                              |  True  |
|  audit_trail                                  |  True  |
|  background_core_dump                         |  True  |
|  bitmap_merge_area_size                       |  True  |
|  blank_trimming                               |  True  |
|  buffer_pool_keep                             |  True  |
|  buffer_pool_recycle                          |  True  |
|  circuits                                     |  True  |
|  client_result_cache_lag                      |  True  |
|  client_result_cache_size                     |  True  |
|  commit_logging                               |  True  |
|  commit_point_strength                        |  True  |
|  commit_wait                                  |  True  |
|  compatible                                   |  True  |
|  control_file_record_keep_time                |  True  |
|  control_management_pack_access               |  True  |
|  cpu_count                                    |  True  |
|  create_bitmap_area_size                      |  True  |
|  create_stored_outlines                       |  True  |
|  cursor_bind_capture_destination              |  True  |
|  cursor_sharing                               |  True  |
|  cursor_space_for_time                        |  True  |
|  db_16k_cache_size                            |  True  |
|  db_2k_cache_size                             |  True  |
|  db_32k_cache_size                            |  True  |
|  db_4k_cache_size                             |  True  |
|  db_big_table_cache_percent_target            |  True  |
|  db_block_buffers                             |  True  |
|  db_block_checking                            |  True  |
|  db_block_checksum                            |  True  |
|  db_cache_advice                              |  True  |
|  db_cache_size                                |  True  |
|  db_domain                                    |  True  |
|  db_file_multiblock_read_count                |  True  |
|  db_files                                     |  True  |
|  db_flashback_retention_target                |  True  |
|  db_index_compression_inheritance             |  True  |
|  db_keep_cache_size                           |  True  |
|  db_lost_write_protect                        |  True  |
|  db_recovery_file_dest_size                   |  True  |
|  db_recycle_cache_size                        |  True  |
|  db_securefile                                |  True  |
|  db_ultra_safe                                |  True  |
|  db_writer_processes                          |  True  |
|  ddl_lock_timeout                             |  True  |
|  deferred_segment_creation                    |  True  |
|  dispatchers                                  |  True  |
|  distributed_lock_timeout                     |  True  |
|  dml_locks                                    |  True  |
|  enable_ddl_logging                           |  True  |
|  enable_goldengate_replication                |  True  |
|  fast_start_mttr_target                       |  True  |
|  fast_start_parallel_rollback                 |  True  |
|  global_context_pool_size                     |  True  |
|  global_names                                 |  True  |
|  heat_map                                     |  True  |
|  hs_autoregister                              |  True  |
|  inmemory_clause_default                      |  True  |
|  inmemory_clause_default_memcompress          |  True  |
|  inmemory_clause_default_priority             |  True  |
|  inmemory_force                               |  True  |
|  inmemory_query                               |  True  |
|  inmemory_size                                |  True  |
|  inmemory_trickle_repopulate_servers_percent  |  True  |
|  job_queue_processes                          |  True  |
|  large_pool_size                              |  True  |
|  ldap_directory_access                        |  True  |
|  license_max_sessions                         |  True  |
|  license_max_users                            |  True  |
|  license_sessions_warning                     |  True  |
|  lock_name_space                              |  True  |
|  log_archive_max_processes                    |  True  |
|  log_archive_min_succeed_dest                 |  True  |
|  log_archive_trace                            |  True  |
|  log_buffer                                   |  True  |
|  log_checkpoint_interval                      |  True  |
|  log_checkpoint_timeout                       |  True  |
|  log_checkpoints_to_alert                     |  True  |
|  max_dispatchers                              |  True  |
|  max_dump_file_size                           |  True  |
|  max_shared_servers                           |  True  |
|  memory_max_target                            |  True  |
|  memory_target                                |  True  |
|  nls_date_format                              |  True  |
|  nls_length_semantics                         |  True  |
|  nls_nchar_conv_excp                          |  True  |
|  nls_time_format                              |  True  |
|  nls_time_tz_format                           |  True  |
|  nls_timestamp_format                         |  True  |
|  nls_timestamp_tz_format                      |  True  |
|  noncdb_compatible                            |  True  |
|  object_cache_max_size_percent                |  True  |
|  object_cache_optimal_size                    |  True  |
|  olap_page_pool_size                          |  True  |
|  open_cursors                                 |  True  |
|  open_links                                   |  True  |
|  open_links_per_instance                      |  True  |
|  optimizer_adaptive_features                  |  True  |
|  optimizer_adaptive_reporting_only            |  True  |
|  optimizer_capture_sql_plan_baselines         |  True  |
|  optimizer_dynamic_sampling                   |  True  |
|  optimizer_features_enable                    |  True  |
|  optimizer_index_caching                      |  True  |
|  optimizer_index_cost_adj                     |  True  |
|  optimizer_mode                               |  True  |
|  optimizer_secure_view_merging                |  True  |
|  optimizer_use_invisible_indexes              |  True  |
|  optimizer_use_pending_statistics             |  True  |
|  optimizer_use_sql_plan_baselines             |  True  |
|  parallel_adaptive_multi_user                 |  True  |
|  parallel_automatic_tuning                    |  True  |
|  parallel_degree_level                        |  True  |
|  parallel_degree_limit                        |  True  |
|  parallel_degree_policy                       |  True  |
|  parallel_execution_message_size              |  True  |
|  parallel_max_servers                         |  True  |
|  parallel_min_servers                         |  True  |
|  parallel_min_time_threshold                  |  True  |
|  parallel_servers_target                      |  True  |
|  parallel_threads_per_cpu                     |  True  |
|  permit_92_wrap_format                        |  True  |
|  pga_aggregate_limit                          |  True  |
|  pga_aggregate_target                         |  True  |
|  plscope_settings                             |  True  |
|  plsql_ccflags                                |  True  |
|  plsql_code_type                              |  True  |
|  plsql_debug                                  |  True  |
|  plsql_optimize_level                         |  True  |
|  plsql_v2_compatibility                       |  True  |
|  plsql_warnings                               |  True  |
|  processes                                    |  True  |
|  query_rewrite_enabled                        |  True  |
|  query_rewrite_integrity                      |  True  |
|  recyclebin                                   |  True  |
|  remote_dependencies_mode                     |  True  |
|  replication_dependency_tracking              |  True  |
|  resource_limit                               |  True  |
|  resource_manager_cpu_allocation              |  True  |
|  resource_manager_plan                        |  True  |
|  result_cache_max_result                      |  True  |
|  result_cache_max_size                        |  True  |
|  result_cache_mode                            |  True  |
|  result_cache_remote_expiration               |  True  |
|  resumable_timeout                            |  True  |
|  sec_case_sensitive_logon                     |  True  |
|  sec_max_failed_login_attempts                |  True  |
|  sec_protocol_error_further_action            |  True  |
|  sec_protocol_error_trace_action              |  True  |
|  sec_return_server_release_banner             |  True  |
|  serial_reuse                                 |  True  |
|  session_cached_cursors                       |  True  |
|  session_max_open_files                       |  True  |
|  sessions                                     |  True  |
|  sga_max_size                                 |  True  |
|  sga_target                                   |  True  |
|  shadow_core_dump                             |  True  |
|  shared_pool_reserved_size                    |  True  |
|  shared_pool_size                             |  True  |
|  shared_server_sessions                       |  True  |
|  shared_servers                               |  True  |
|  skip_unusable_indexes                        |  True  |
|  smtp_out_server                              |  True  |
|  sort_area_retained_size                      |  True  |
|  sort_area_size                               |  True  |
|  sql92_security                               |  True  |
|  sql_trace                                    |  True  |
|  sqltune_category                             |  True  |
|  star_transformation_enabled                  |  True  |
|  statistics_level                             |  True  |
|  streams_pool_size                            |  True  |
|  temp_undo_enabled                            |  True  |
|  timed_os_statistics                          |  True  |
|  timed_statistics                             |  True  |
|  trace_enabled                                |  True  |
|  transactions                                 |  True  |
|  transactions_per_rollback_segment            |  True  |
|  undo_retention                               |  True  |
|  undo_tablespace                              |  True  |
|  unified_audit_sga_queue_size                 |  True  |
|  workarea_size_policy                         |  True  |
+-----------------------------------------------+--------+

```

# 参考

Amazon Relational Database Serviceユーザーガイド
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Viewing

AWS CLI　コマンドリファレンス
http://docs.aws.amazon.com/cli/latest/reference/rds/describe-db-parameters.html

AWS CLIコマンドの実行オプション
http://qiita.com/takech9203/items/4b9394ddd3f190835ca5

AWS CLIのフィルターとクエリーの使い方についてまとめてみた
http://dev.classmethod.jp/cloud/aws/aws-cli-filter-and-query-howto/