How to set parallelism for a data guard recovery process

If you need to manually set the apply-process DOP (Degree of Parallelism), you can do this by setting the property ApplyParallel=num where num is the number of parallel processes. 

In my setup, I have three databases: 

•  A primary database (hdal) 
• A physical standby database (hdal_stb) which serves as a "cascading" database 
• A terminal standby database (hdal_tstb) which receives redo from the cascading standby database  

Logged in on any one of the participating servers of your data guard configuration, start dgmgrl: 

dgmgrl / as sysdba
Connected to "hdal_stb"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

DGMGRL> edit database 'hdal_stb' set property ApplyParallel=4;

In the database's alert log, we can se that Oracle is cancelling the ongoing recovery process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2020-08-10T09:26:24.249847+02:00
MRP0: Background Media Recovery cancelled with status 16037
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 27754456
2020-08-10T09:26:24.510262+02:00
ORA-16037: user requested cancel of managed recovery operation
2020-08-10T09:26:24.617519+02:00
MRP0: Background Media Recovery process shutdown (hdal)
2020-08-10T09:26:25.250904+02:00
Managed Standby Recovery Canceled (hdal)

And then starting it again:

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT PARALLEL 4 NODELAY
2020-08-10T09:26:25.284547+02:00
Attempt to start background Managed Standby Recovery process (hdal)
Starting background process MRP0
2020-08-10T09:26:25.306150+02:00
MRP0 started with pid=77, OS id=115854
2020-08-10T09:26:25.311038+02:00
MRP0: Background Managed Standby Recovery process started (hdal)
2020-08-10T09:26:30.339894+02:00
 Started logmerger process
2020-08-10T09:26:30.378497+02:00
Managed Standby Recovery starting Real Time Apply
2020-08-10T09:26:30.515607+02:00
Parallel Media Recovery started with 4 slaves
2020-08-10T09:26:30.741846+02:00
Media Recovery Waiting for thread 1 sequence 8299 (in transit)

Verify the new setting by looking at the properties (output truncated for brevity):

DGMGRL> show database verbose hdal_stb

Database - hdal_stb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 0 Byte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    hdal

  Properties:
    DGConnectIdentifier             = 'hdal_stb'
    LogXptMode                      = 'ASYNC'
    ApplyParallel                   = '4'

The change will be propagated to the other members of the data guard configuration. The data guard broker logfile, located in $ORACLE_BASE/diag/rdbms/$UNIQUE_NAME/$ORACLE_SID/trace, shows:

08/10/2020 09:26:24
Forwarding EDIT_RES_PROP operation to member hdal for processing
08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_stb.dat"
08/10/2020 09:42:35
Forwarding MON_PROPERTY operation to member hdal_tstb for processing

The cascading database hdal_tstb simply confirms that the broker configuration has been updated:

08/10/2020 09:26:31
Updated broker configuration file available, loading from "$ORACLE_HOME/dbs/dr2hdal_tstb.dat"

The primary database is also verifying the change, although a bit more detailed:

08/10/2020 09:26:24
EDIT DATABASE hdal_stb SET PROPERTY applyparallel = 4
08/10/2020 09:26:31
EDIT INSTANCE hdal ON DATABASE hdal_stb SET PROPERTY applyparallel = 4 completed successfully

Documentation for dgmgrl version 19c can be found here

What constitute "mandatory auditing" under Unified Auditing?

• Activities from administrative users such as SYSDBA, SYSBACKUP, and SYSKM. 
•  The following audit-related activities are mandatorily audited:

CREATE AUDIT POLICY	AUDIT	EXECUTE of the DBMS_FGA PL/SQL package
ALTER AUDIT POLICY	NOAUDIT	EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package
DROP AUDIT POLICY	Access to sensitive columns in the optimizer dictionary tables.	ALTER TABLE attempts on the AUDSYS audit trail table
Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens	All user-issued DML statements on the SYS.AUD$ and SYS.FGA_LOG$ dictionary tables	Any attempts to modify the data or metadata of the unified audit internal table. SELECT statements on this table are not audited by default or mandatorily.
All configuration changes that are made to Oracle Database Vault

The audit information can be found in the view UNIFIED_AUDIT_TRAIL.
Documentation for Mandatory Unified Auditing in Oracle 12.2 can be found here

How to find offending process when getting error umount.nfs: /data1: device is busy

As root, use lsof ("list open files"):

[root@myserver /home/oracle]# lsof | grep '/data1'
extract    14041        oracle   25r      REG               0,23      2896 1235955379 /data1/goldengate/dirdat/et000000127 (storage1:/Oracle/myserver/data1)

You will see a list of processes. Terminate these with the kill-command:

kill 13859 14041 

After this is done, you can unmount the nfs file system:

 umount -f /data1

How to connect to and start a specific database using dgmgrl

The data guard broker allows you to connect to any of the participating members of the configuration:

oracle@myserver:[hdal]# dgmgrl / as sysdba
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jul 31 10:44:47 2020

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

Welcome to DGMGRL, type "help" for information.
Connected to "hdal"
Connected as SYSDBA.
DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  hdal      - Primary database
    hdal_stb  - Physical standby database
      hdal_tstb - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL> connect sys@hdal_stb as sysdba
Password:
Connected to "hdal_stb"
Connected as SYSDBA.

Note that the password files must be identical for all members of the configuration.
You can startup and shutdown individual members of your configuration without leaving the data guard broker interface, for example:

DGMGRL> startup mount
ORACLE instance started.
Database mounted.

Documentation on how to use Oracle 12.2 version of dgmgrl is found here

How to rebuild an index partition

To limit a rebuild to a specific index partition only, use the following syntax:

ALTER INDEX SH.SALES_IDX4 
REBUILD PARTITION SYS_P51490 
TABLESPACE DATA04 ONLINE;

Notice the use of the ONLINE keyward, which will allow DML against the table (and thus update the index). To generate rebuild-statements, one index at a time, I have used the script below. It takes as arguments

1. The index owner 
2. The index name 
3. The new tablespace where you want to place your partitions 
4. The old tablespace from which you want to move out

set termout off
select 'alter session set nls_language=''american'';' from dual;
set termout on
accept index_owner prompt 'Index owner: '
accept index_name prompt 'Index name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_move_part_&&index_owner..&&index_name..sql
select 'alter session set nls_language=''american'';' from dual;

select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_rebuild_part_&&index_owner..&&index_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''Rebuild_part_&&index_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''Rebuild_part_t_&&index_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''Rebuild_part_&&index_name''); ' from dual;

select 'prompt moving the index partitions from &&old_tabspc to &&new_tabspc,' from dual;
select 'prompt and setting default attributes for the table ' from dual;
SELECT 'alter index ' || idx.table_owner || '.' ||  idx.index_name || ' rebuild partition ' || idxpart.partition_name || ' TABLESPACE &&new_tabspc ONLINE;'
FROM    dba_indexes idx, dba_ind_partitions idxpart
WHERE   idx.table_owner = '&&index_owner'
and     idxpart.TABLESPACE_NAME = '&&old_tabspc'
AND     idx.index_name = idxpart.index_name
AND     idx.index_name = '&&index_name'
AND     idxpart.subpartition_count=0
ORDER BY idx.table_owner, idx.index_name;

select distinct 'alter index ' ||   IDXPART.INDEX_OWNER || '.' || IDXPART.INDEX_NAME || ' modify default attributes tablespace &&new_tabspc;'
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON     (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='&&index_name'
AND    IDXPART.TABLESPACE_NAME='&&old_tabspc';

select 'exit' from dual;
exit

Run it as a user with access to the data dictionary: 

sqlplus / as sysdba @gen_move_idx_part.sql 

It will generate a new script called exec_move_part_<username>.<index_name>.sql which will perform the actual rebuild: 

sqlplus / as sysdba @exec_move_part_<username>.<index_name>.sql

I also added som calls to dbms_application_info so that the session can be easily identfied in v$session. Remember, sometimes it's desirable to set the PARALLEL degree and the NOLOGGING options during rebuild, to complete it as fast as possible. See this post for more info.

How to list index partitions and their sizes

To list index partitions for index SALES_IDX1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='SALES_IDX1'
AND    S.SEGMENT_TYPE='INDEX PARTITION'
-- To only list partitions in tablespace DATA1, uncomment the following line:
-- AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	SYS_P177667	DATA1	8
SH	SALES_IDX1	SYS_P177518	DATA1	5
SH	SALES_IDX1	SYS_P44844	DATA1	3
SH	SALES_IDX1	SYS_P44663	DATA1	2
SH	SALES_IDX1	SYS_P177677	DATA1	2

To list all index partitions for tablespace DATA1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
AND    S.SEGMENT_TYPE='INDEX PARTITION'
AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	GB
SH	SALES_IDX1	SYS_P177667	8
SH	SALES_IDX1	SYS_P177518	5
SH	SALES_IDX1	SYS_P44844	3
SH	SALES_IDX1	SYS_P177677	2
SH	SALES_IDX1	SYS_P44663	2
SH	SALES_IDX2	SYS_P179608	1
SH	SALES_IDX2	SYS_P178334	1
SH	SALEX_IDX2	SYS_P178459	1
SH	SALES_IDX3	SYS_P28534	0
SH	SALES_IDX3	SYS_P50905	0

To list the names of all partitioned indexes and their total sizes, in a particular tablespace:

SELECT DISTINCT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.TABLESPACE_NAME, 
    (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024) 
     FROM DBA_SEGMENTS S
     WHERE S.SEGMENT_NAME = IDXPART.INDEX_NAME
     ) GB
FROM   DBA_IND_PARTITIONS IDXPART 
WHERE  IDXPART.TABLESPACE_NAME='DATA1'
ORDER BY 4 DESC;

INDEX_OWNER	INDEX_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	DATA1	567
SH	SALES_IDX2	DATA1	511
SH	SALES_IDX3	DATA1	331

To check which tablespaces the different partitions in an index reside in:

SELECT TABLESPACE_NAME,COUNT(*)
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='SALES_IDX4'
GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME	COUNT(*)
DATA1	13
DATA2	832