Uncategorized

Oracle RAC Rolling Patching without Downtime

By | March 5th, 2021 | Category: Uncategorized | No Comments »

As part of a customer engagement, I have documented the approach of patching a mission-critical Oracle Real Application Cluster (RAC) Database from 19.8.0 to 19.10.0 WITHOUT DOWNTIME. The critical part is the Rolling Patching Oracle Java VM (OJVM).

The paper can be downloaded here: Oracle RAC Rolling Patching without Downtime

A list of recommeded patches can be found here:
https://www.ora-solutions.net/web/resources/patch-recommendations/
If you need assistance or an updated list of recommended patches, please contact me at: martin.decker@ora-solutions.net


DOAG Conference 2019 Presentation: “LINUX Know-How for DBAs (german)”

By | November 20th, 2019 | Category: Uncategorized | No Comments »

I have uploaded my presentation plus the recorded demos to the website.


Modifications to hidden parameters between 12.1.0.2 and 12.2.0.1

By | April 26th, 2018 | Category: 12cR1, 12cR2, Oracle Database, Uncategorized | No Comments »

I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on 12.2.0.1 and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in 12.2.0.1. This made me check what other parameters were changed. In my opinion, especially interesting are parameters, that were OFF / FALSE in 12.1.0.2 and ON / TRUE in 12.2.0.1 as this probably activates new code.

This SQL Statement shows all parameters, including hidden parameters:

1
2
3
4
5
6
7
8
9

-- file pd2.sql
SET LINES 3000 pages 0 feedback off trimspool ON
SELECT
 n.ksppinm ||'|'||
  c.ksppstvl ||'|'||
  n.ksppdesc
FROM sys.x$ksppi n, sys.x$ksppcv c
WHERE n.indx=c.indx
;

Then, I spooled Pipe-seperated output to text files for both releases:

1
2
3
4
5
6
7
8
9
10
11

. ~/121.env
sqlplus / as sysdba <<EOF
spool /tmp/121.txt
@/tmp/pd2.sql
EOF
 
. ~/122.env
sqlplus / as sysdba <<EOF
spool /tmp/122.txt
@/tmp/pd2.sql
EOF

Finally, I set up External Tables for accessing these files with SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

sqlplus / as sysdba
 
drop table db122 purge;
drop table db121 purge;
 
CREATE TABLE db122 (
  param      VARCHAR2(2000),
  param_val      VARCHAR2(2000),
  param_desc     VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY tmp_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
    (
      param CHAR(2000)   , 
      param_val  CHAR(2000),    
      param_desc CHAR(2000)
    )
  )
  LOCATION ('122.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
 
CREATE TABLE db121 (
  param      VARCHAR2(2000),
  param_val      VARCHAR2(2000),
  param_desc     VARCHAR2(2000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY tmp_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
    (
      param   CHAR(2000)  , 
      param_val  CHAR(2000),    
      param_desc CHAR(2000)
    )
  )
  LOCATION ('121.txt')
)
PARALLEL 1
REJECT LIMIT UNLIMITED;

The final query now compares hidden parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

SET LINES 500 pages 2000 tab off
col param format a56
col db121_value format  a20
col db122_value format  a20
col param_desc heading DESCRIPTION format a100 word_wrap
SELECT db121.param, db121.param_val AS db121_value, db122.param_val AS db122_value, db122.param_desc FROM 
(SELECT param, param_val, param_desc FROM db121) db121,
(SELECT param, param_val, param_desc FROM db122) db122
WHERE db121.param = db122.param AND db121.param_val != db122.param_val  AND db121.param LIKE '\_%' escape '\'
and db121.param not IN (
 '__db_cache_size',
 '__pga_aggregate_target',
 '__pga_aggregate_target',
 '__shared_pool_size',
 '__streams_pool_size',
 '_diag_adr_trace_dest',
 '__large_pool_size')
order by db121.param; 
 
 
PARAM                                                    DB121_VALUE          DB122_VALUE          DESCRIPTION
-------------------------------------------------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------
_adaptive_scalable_log_writer_disable_worker_threshold   90                   50                   Percentage of overlap across multiple outstanding writes
_advanced_index_compression_options_value                20                   0                    advanced index compression options2
_aq_latency_absolute_threshold                           100                  300                  Absolute threshold greater than average latency
_aq_latency_relative_threshold                           30                   100                  Relative threshold of average latency
_aq_lb_stats_collect_cycle                               120                  45                   Time(seconds) between consecutive AQ load statistics collection
_aq_lookback_size                                        30                   60                   AQ PT Look Back Size
_aq_pt_shrink_frequency                                  30                   1450                 PT shrink window Size
_aq_pt_statistics_window                                 30                   60                   PT statistics sample window Size
_asm_compatibility                                       10.1                 11.2.0.2             default ASM compatibility level
_asm_max_cod_strides                                     5                    10                   maximum number of COD strides
_asm_scrub_unmatched_dba                                 3                    1024                 Scrub maximum number of blocks with unmatched DBA
_asm_xrov_nvios                                          4                    8                    Specify number of VIO processes
_asm_xrov_single                                         TRUE                 FALSE                Enable single issues of IOs
_bigdata_external_table                                  FALSE                TRUE                 enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
_cache_fusion_pipelined_updates                          FALSE                TRUE                 Block ping without wait for log force
_catalog_foreign_restore                                 TRUE                 FALSE                catalog foreign file restore
_column_tracking_level                                   1                    21                   column usage tracking
_compression_compatibility                               12.1.0.2.0           12.2.0               Compression compatibility
_controlfile_enqueue_holding_time_tracking_size          10                   0                    control file enqueue holding time tracking size
_cursor_db_buffers_pinned                                1387                 1375                 additional number of buffers a cursor can pin at once
_cursor_obsolete_threshold                               1024                 8192                 Number of cursors per parent before obsoletion.
_db_block_buffers                                        444542               440775               Number of database blocks cached in memory: hidden parameter
_db_block_cache_protect                                  FALSE                false                protect database blocks (for debugging only)
_dbg_scan                                                0                    2048                 generic scan debug
_dbrm_dynamic_threshold                                  33490396             989922280            DBRM dynamic threshold setting
_dbrm_runchk                                             0                    32769000             Resource Manager Diagnostic Running Thread Check
_dbwr_stall_write_detection_interval                     0                    900                  dbwriter stall write detection interval
_enable_pdb_close_abort                                  FALSE                TRUE                 Enable PDB shutdown abort (close abort)
_gc_async_send                                           FALSE                TRUE                 if TRUE, send blocks asynchronously
_gc_element_percent                                      120                  105                  global cache element percent
_gc_latches                                              8                    32                   number of latches per LMS process
_gc_max_downcvt                                          256                  1024                 maximum downconverts to process at one time
_gc_msgq_buffers                                         128                  0                    set number of MSGQ buffers
_gc_policy_time                                          10                   20                   how often to make object policy decisions in minutes
_gc_trace_freelist_empty                                 TRUE                 FALSE                if TRUE, dump a trace when we run out of lock elements
_gc_transfer_ratio                                       2                    75                   dynamic object read-mostly transfer ratio
_gcs_latches                                             0                    128                  number of gcs resource hash latches to be allocated per LMS process
_hang_base_file_space_limit                              20000000             100000000            File space limit for current normal base trace file
_hang_int_spare2                                         FALSE                0                    Hang Management int 2
_hang_lws_file_space_limit                               20000000             100000000            File space limit for current long waiting session trace file
_hang_resolution_scope                                   PROCESS              OFF                  Hang Management hang resolution scope
_hang_terminate_session_replay_enabled                   FALSE                TRUE                 Hang Management terminates sessions allowing replay
_ilmstat_memlimit                                        10                   5                    Percentage of shared pool for use by ILM Statistics
_inmemory_fs_blk_inv_blk_percent                         20                   50                   in-memory faststart CU invalidation threshold(blocks)
_inmemory_fs_enable                                      FALSE                TRUE                 in-memory faststart enable
_inmemory_imcu_target_rows                               1048576              0                    IMCU target number of rows
_inmemory_private_journal_maxexts                        32000                5000                 Max number of extents per PJ
_inmemory_private_journal_sharedpool_quota               64                   20                   quota for transaction in-memory objects
_inmemory_repopulate_invalidate_rate_percent             100                  0                    In-memory repopulate invalidate rate percent
_inmemory_repopulate_threshold_scans                     2048                 0                    In-memory repopulate threshold number of scans
_inmemory_strdlxid_timeout                               327680               0                    max time to determine straddling transactions
_ipddb_enable                                            TRUE                 FALSE                Enable IPD/DB data collection
_kebm_suspension_time                                    82800                104400               kebm auto suspension time in seconds
_kgh_restricted_subheaps                                 60                   180                  number of subheaps in heap restricted mode
_kgl_message_locks                                       64                   256                  RAC message lock count
_kqr_optimistic_reads                                    FALSE                TRUE                 optimistic reading of row cache objects
_kse_snap_ring_suppress                                  608 1403 6510        942 1403             List of error numbers to suppress in the snap error history
_ksi_clientlocks_enabled                                 TRUE                 FALSE                if TRUE, DLM-clients can provide the lock memory
_ksxp_ipclw_enabled                                      0                    1                    enable ipclw for KSXP
_ktb_debug_flags                                         0                    8                    ktb-layer debug flags
_lm_comm_channel                                         ksxp                 msgq                 GES communication channel type
_lm_comm_tkts_calc_period_length                         5                    1000                 Weighted average calculation interval length (us)
_lm_drm_min_interval                                     300                  600                  minimum interval in secs between two consecutive drms
_lm_drmopt12                                             26                   56                   enable drm scan optimizations in 12
_lm_enable_aff_benefit_stats                             TRUE                 FALSE                enables affinity benefit computations if TRUE
_lm_procs                                                320                  1088                 number of client processes configured for cluster database
_lm_xids                                                 352                  1196                 number of transaction IDs configured for cluster database
_lock_next_constraint_count                              120                  3                    max number of attempts to lock _NEXT_CONSTRAINT
_lthread_cleanup_intv_secs                               5                    1                    interval for cleaning lightweight threads in secs
_max_fsu_segments                                        4096                 1024                 Maximum segments to track for fast space usage
_max_services                                            1024                 8200                 maximum number of database services
_multi_instance_pmr                                      FALSE                TRUE                 enable multi instance redo apply
_multiple_char_set_cdb                                   FALSE                TRUE                 Multiple character sets enabled in CDB
_number_cached_group_memberships                         32                   2048                 maximum number of cached group memberships
_number_group_memberships_per_cache_line                 3                    6                    maximum number of group memberships per cache line
_optimizer_ads_use_partial_results                       FALSE                TRUE                 Use partial results of ADS queries
_optimizer_dsdir_usage_control                           126                  0                    controls optimizer usage of dynamic sampling directives
_optimizer_undo_cost_change                              12.1.0.2             12.2.0.1             optimizer undo cost change
_parallel_server_sleep_time                              10                   1                    sleep time between dequeue timeouts (in 1/100ths)
_pmon_dead_blkrs_max_blkrs                               200                  50                   max blockers to check during cleanup
_posix_spawn_enabled                                     FALSE                TRUE                 posix_spawn enabled
_rcfg_parallel_verify                                    TRUE                 FALSE                if TRUE enables parallel verify at reconfiguration
_reliable_block_sends                                    TRUE                 FALSE                if TRUE, no side channel on reliable interconnect
_rowsets_max_rows                                        200                  256                  maximum number of rows in a rowset
_side_channel_batch_size                                 200                  240                  number of messages to batch in a side channel message (DFS)
_skip_trstamp_check                                      FALSE                TRUE                 Skip terminal recovery stamp check
_small_table_threshold                                   8890                 8815                 lower threshold level of table size for direct reads
_sql_plan_directive_mgmt_control                         3                    67                   controls internal SQL Plan Directive management activities
_step_down_limit_in_pct                                  1                    20                   step down limit in percentage
_target_log_write_size_timeout                           1                    0                    How long LGWR will wait for redo to accumulate (msecs)
 
90 rows selected.

Cloud Control – Privilege Delegation – so you don´t have the oracle / root password?

By | September 13th, 2017 | Category: Oracle Enterprise Manager, Uncategorized | No Comments »

Quite frequently in database environments, security policies dictate that only personalized logons to Unix / Linux are allowed and that from there, one has to “sudo” to change to the oracle account. While this adds an additional layer of security, it makes administration a little more complicated.

Oracle Enterprise Manager – Cloud Control has a feature, which allows to cope with such a sudo environment. The feature is called “Privilege Delegation”. This post describes how to set it up and for what it can be used.

  1. Setup of “sudo” by the root account

    2. In order to use privilege delegation, specific sudo rules have to be defined. This rule normally already exists:

    1

    mdecker ALL=(root) NOPASSWD:/bin/su - oracle

    In addition to this one, two additional rules are required. If only sudo to “oracle” is required, then only the first line is needed.

    1
2

    mdecker ALL=(oracle) SETENV:/u01/app/oracle/cloud/agent/sbin/nmosudo *
mdecker ALL=(root) SETENV:/u01/app/oracle/cloud/agent/sbin/nmosudo *
  2. Setup of Privilege Delegation in Cloud Control

    3. Go to Setup -> Security -> Privilege Delegation.

    Then you can either set it globally via template or individually for each host. This depends mainly on the path to “sudo” binary in the operating system. Then you choose “sudo” and provide the path to the sudo binary on your operating system (bash$ which sudo). The required parameters are then appended: /usr/bin/sudo -E -u %RUNAS% %COMMAND%

  3. The next step is to configure a Named Credential.

    4. If you are in a team of administrators, then each administrator should have his own account to log on to Cloud Contol and avoid using “sysman” user. For obvious reasons, each administrator has to create his own “named credential” (Setup->Security->Named Credential), because it contains his personalized username and password.

    Here you provide your personalized credentials (username/password) and specify that sudo should be used to change to “oracle”.

  4. Lastly, verify that it works as desired.

    5. Go to “Targets” -> “Host” and click “Run Host Command”. Then give the command to run, e.g. “id -a”, and then add a named credential as well as a specific host and click “Run”.

    If all is well, then you will get this output showing you the id of user oracle.

How does this work behind the scenes? The agent java process spawns a process “nmo”. This process was granted SETUID root Privileges by executing $ORACLE_HOME/root.sh at the time of agent deployment. This executable is calling “sudo” to run command “nmosudo” as user oracle with the “payload” command, which the user wanted to execute.

1
2
3

root   14595  3149  0 14:23 pts/0    00:00:00 /u01/app/oracle/cloud/agent/sbin/nmo
root   14598 14595  0 14:23 pts/1    00:00:00   /usr/bin/sudo -p ###AGENT-PDP-PASSWORD-PROMPT### -E -u oracle /u01/app/oracle/cloud/agent/sbin/nmosudo DEFAULT_PLUGIN DEFAULT_FUNCTIONALITY DEFAULT_SUBACTION DEFAULT_ACTION /bin/sh -c id -a
oracle 14602 14598  0 14:23 pts/1    00:00:00     sleep 300

The linux logfile /var/log/secure will contain this messages. It can be seen that the personal user “mdecker” was running the command “nmosudo” with the payload command “id -a” as attribute.

1

Sep 13 22:03:59 xxx sudo:  mdecker : TTY=pts/2 ; PWD=/u01/app/oracle/cloud/agent/agent_inst/sysman/emd ; USER=oracle ; COMMAND=/u01/app/oracle/cloud/agent/sbin/nmosudo DEFAULT_PLUGIN DEFAULT_FUNCTIONALITY DEFAULT_SUBACTION DEFAULT_ACTION /bin/sh -c id -a