Thinking Out Loud

September 30, 2020

Automating Index Rebuild

Filed under: 12c,oracle — mdinh @ 11:28 pm

IMPORTANT: This is not a recommendation to rebuild indexes.

The post will outline SQL used to determine index to rebuild.

PL/SQL will be used to check table lock for the underlying index and if there is no lock, then rebuild index else skip rebuild for index.

1.Download Index Sizing and create copy index_est_proc_2.sql.org

2. Create table index_rebuild.

SQL> desc index_rebuild
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(20)
 LEAF_BLOCKS                                        NUMBER
 TARGET_SIZE                                        NUMBER

SQL>

3. Update index_est_proc_2.sql and include the following insert into table index rebuild.

if m_leaf_estimate < &m_scale_factor * r.leaf_blocks then
  dbms_output.put_line(
    to_char(sysdate,'hh24_mi_ss') || '|table|' ||
    trim(r.table_name) || '|index|' ||
    trim(r.index_name) || '|' || 'Current Leaf blocks|' || trim(to_char(r.leaf_blocks,'999,999,999')) || '|Target size|' || 
    trim(to_char(m_leaf_estimate,'999,999,999'))
  );

  -- Insert data into table index_rebuild as well as output to terminal.
  insert into index_rebuild(table_owner,table_name,index_name,leaf_blocks,target_size)
  values
  (UPPER('&m_owner'),trim(r.table_name),trim(r.index_name),r.leaf_blocks,m_leaf_estimate);
  dbms_output.new_line;
end if;

4. Create plsql_rebuild_idx.sql

set timing on time on serveroutput on size unlimited trimsp on tab off lines 200
col TABLE_OWNER for a30
col TABLE_NAME for a30
col INDEX_NAME for a35
col USERNAME for a10
col MACHINE for a10
col MODULE for a30
-- Display current user session info.
select s.username as Username,
       s.machine as Machine,
       s.module as Module,
       s.sid as SessionID,
       p.pid as ProcessID,
       p.spid as "UNIX ProcessID"
from
v$session s, v$process p
where s.sid = sys_context ('userenv','sid')
and s.PADDR = p.ADDR
;
set echo on
-- Rebuild indexes with LEAF_BLOCKS < 16000000 and edit as required.
select * from index_rebuild where LEAF_BLOCKS < 16000000;
exit
lock table index_rebuild in EXCLUSIVE mode WAIT 120;
DECLARE
  l_sql varchar2(1000);
  l_ct  number;
BEGIN
FOR d in (
  select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
)
LOOP
  select count(*) into l_ct
  from v$locked_object a, v$session b, dba_objects c
  where b.sid = a.session_id
  and a.object_id = c.object_id
  and c.object_type='TABLE'
  and c.owner=d.TABLE_OWNER
  and c.object_name=d.TABLE_NAME
  and d.LEAF_BLOCKS < 16000000;
  IF l_ct = 0 THEN
    dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
    l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
    dbms_output.put_line (l_sql);
    execute immediate l_sql;
    delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
  END IF;
END LOOP;
END;
/
delete from index_rebuild;
commit;
exit

5. Run plsql_rebuild_idx.sql using nohup

nohup sqlplus "/ as sysdba" @ plsql_rebuild_idx.sql > plsql_rebuild_idx.log 2>&1 &

6. Review

$ cat plsql_rebuild_idx.log
nohup: ignoring input

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 24 14:13:00 2020

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, OLAP, Advanced Analytics
and Real Application Testing options

14:13:00 SQL> select * from index_rebuild;

TABLE_OWNER                    TABLE_NAME                     INDEX_NAME           LEAF_BLOCKS TARGET_SIZE
------------------------------ ------------------------------ -------------------- ----------- -----------
XXXX                           YYYYYYYYYY1                    ZZZZZZZZZZZ_M            9721430     4328586
XXXX                           YYYYYYYYYY2                    ZZZZZZZZZZZ_MP          15865953     5848673

Elapsed: 00:00:00.00
14:13:00 SQL> lock table index_rebuild in EXCLUSIVE mode WAIT 120;

Table(s) Locked.

Elapsed: 00:00:00.00
14:13:00 SQL> DECLARE
14:13:00   2    l_sql varchar2(1000);
14:13:00   3    l_ct  number;
14:13:00   4  BEGIN
14:13:00   5  FOR d in (
14:13:00   6    select TABLE_OWNER, TABLE_NAME, INDEX_NAME, LEAF_BLOCKS from index_rebuild order by leaf_blocks asc
14:13:00   7  )
14:13:00   8  LOOP
14:13:00   9    select count(*) into l_ct
14:13:00  10    from v$locked_object a, v$session b, dba_objects c
14:13:00  11    where b.sid = a.session_id
14:13:00  12    and a.object_id = c.object_id
14:13:00  13    and c.object_type='TABLE'
14:13:00  14    and c.owner=d.TABLE_OWNER
14:13:00  15    and c.object_name=d.TABLE_NAME;
14:13:00  16    IF l_ct = 0 THEN
14:13:00  17      dbms_output.put_line( '-- Check lock for owner|table|index : ' ||d.TABLE_OWNER||'.'||d.TABLE_NAME||'.'||d.INDEX_NAME||'='||l_ct );
14:13:00  18      l_sql := 'alter index '||d.TABLE_OWNER||'.'||d.INDEX_NAME||' rebuild online parallel 4';
14:13:00  19      dbms_output.put_line (l_sql);
14:13:00  20      execute immediate l_sql;
14:13:00  21      delete from index_rebuild where TABLE_OWNER=d.TABLE_OWNER and TABLE_NAME=d.TABLE_NAME and INDEX_NAME=d.INDEX_NAME;
14:13:00  22    END IF;
14:13:00  23  END LOOP;
14:13:00  24  END;
14:13:00  25  /
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY1.ZZZZZZZZZZZ_M=0
alter index XXXX.ZZZZZZZZZZZ_M rebuild online parallel 4
-- Check lock for owner|table|index : XXXX.YYYYYYYYYY2.ZZZZZZZZZZZ_MP=0
alter index XXXX.ZZZZZZZZZZZ_MP rebuild online parallel 4

PL/SQL procedure successfully completed.

Elapsed: 04:00:23.08
18:13:23 SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
18:13:23 SQL> exit

7. Run index_est_proc_2.sql.org (screen output only) or index_est_proc_2.sql (screen output and insert into index_rebuild table) to determine if any more indexes are listed for rebuild.

Note: The first rebuild contained a few dozen of indexes for rebuild but was not automated.

Later, there were only 2 indexes for rebuild as shown above from real production environment before minor improvements, e.g. — Display current user session info.

Q.E.D.

September 20, 2020

19c New Feature DGMGRL validate database?

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 2:35 pm

Not too long ago, I had blogged about When To Use dgmgrl / vs dgmgrl sys@tns

I believe this is New Feature for 19c (but not 100% certain) may resolved the question above?.

DEMO:
Connect using OS authentication from standby host.

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@ol7-112-dg2 ~]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Sep 20 14:22:13 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...

ORA-01017: invalid username/password; logon denied

    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

DEMO:
Connect to primary using tns from standby host.

DGMGRL> connect sys/oracle@hawk
Connected to "hawk"
Connected as SYSDBA.
DGMGRL> validate database hawk;

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Managed by Clusterware:
    hawk:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

DGMGRL> validate database hawk_stby;

  Database Role:     Physical standby database
  Primary Database:  hawk

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    hawk     :  NO
    hawk_stby:  NO
    Validating static connect identifier for the primary database hawk...
    The static connect identifier allows for a connection to database "hawk".

  Log Files Cleared:
    hawk Standby Redo Log Files:       Cleared
    hawk_stby Online Redo Log Files:   Not Cleared
    hawk_stby Standby Redo Log Files:  Available

DGMGRL>

This will at least address one example for when to use TNS vs OS authentication for DGMGRL.

September 19, 2020

Monitoring LAG Using DGMGRL Is Nice And Needs Improvements

Filed under: 19c,Dataguard,dgmgrl — mdinh @ 1:53 pm

On November 2, 2017, I had blogged about Monitoring Standby – SQLPlus or DGMGRL

Since the post, I do not recall using dgmgrl much for monitoring lag.

Almost 3 years later and 19c, let’s revisit the topic.

Here is what monitoring lag looks like from SQLPlus.
Notice BLOCK# increased which mean transfer is working.

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8540      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8540      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          175     8554      0
9059                         1        1 N/A          MRP0      APPLYING_LOG       175     8554      0

SQL> 

For 19c, show configuration lag will provide info on lag and is knowing Lag is 0 seconds good enough?

[oracle@ol7-112-dg2 sql]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Sep 19 12:50:58 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "hawk_stby"
Connected as SYSDG.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 9 seconds ago)
                Apply Lag:          0 seconds (computed 9 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

DGMGRL> show configuration lag verbose

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 12 seconds ago)
                Apply Lag:          0 seconds (computed 12 seconds ago)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = ''

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS
DGMGRL>

Using SendQEntries shows LOG_SEQ but RecvQEntries does not.

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  175  09/19/2020 12:32:29                                   2984164                                 23586

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)

DGMGRL>

Disable apply and compare differences between SQLPlus and DGMGRL.

DGMGRL> edit database hawk_stby set state=APPLY-OFF
> ;
Succeeded.
DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 8 seconds ago)
  Apply Lag:          0 seconds (computed 8 seconds ago)
  Average Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

### From Primary:

*** gv$managed_standby ***

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1311      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9030                         1        1 LNS          LNS       WRITING            180     1314      0

SQL>

### From Standby:

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             179 19-SEP-2020 13:12:25         5 39.93333
       1        1 YES            174 19-SEP-2020 12:32:29

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      284      0

SQL> r
  1  select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where BLOCK#>1
  4  and status not in ('CLOSING','IDLE')
  5  order by status desc, thread#, sequence#
  6*

                                        CLIENT                                                  DELAY
PID                       INST  THREAD# PROCESS      PROCESS   STATUS       SEQUENCE#   BLOCK#   MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
9589                         1        1 LGWR         RFS       RECEIVING          180      298      0

SQL>

From DGMGRL:

DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 6 seconds ago)
                Apply Lag:          6 minutes 48 seconds (computed 6 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> show database hawk SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
                          CURRENT       1047346434                1                  180  09/19/2020 13:12:25                                   2992416                                   270

DGMGRL> show database hawk_stby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
   PARTIALLY_APPLIED       1047346434                1                  175  09/19/2020 12:32:29  09/19/2020 13:12:16              2984164              2992388            24709
         NOT_APPLIED       1047346434                1                  176  09/19/2020 13:12:16  09/19/2020 13:12:17              2992388              2992393                1
         NOT_APPLIED       1047346434                1                  177  09/19/2020 13:12:17  09/19/2020 13:12:20              2992393              2992400                2
         NOT_APPLIED       1047346434                1                  178  09/19/2020 13:12:20  09/19/2020 13:12:20              2992400              2992403                1
         NOT_APPLIED       1047346434                1                  179  09/19/2020 13:12:20  09/19/2020 13:12:25              2992403              2992416                3

DGMGRL>

APPLY-ON

DGMGRL> edit database hawk_stby set state=APPLY-ON;
Succeeded.
DGMGRL> show configuration lag

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 3 seconds ago)
                Apply Lag:          0 seconds (computed 3 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 41 seconds ago)

DGMGRL> /

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
                Transport Lag:      0 seconds (computed 4 seconds ago)
                Apply Lag:          0 seconds (computed 4 seconds ago)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 56 seconds ago)

DGMGRL> show database hawk_stby

Database - hawk_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 6 seconds ago)
  Apply Lag:          0 seconds (computed 6 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    hawk

Database Status:
SUCCESS

DGMGRL>

It would be nice if show configuration lag is able to provide some high level info frequently asked by management.

What is lag time, how many sequence is the standby behind, what is the apply rate, what is LOG_SEQ at primary and standby?

Create a free website or blog at WordPress.com.