In March, Oracle MAA has published a white paper for implementing Data Guard on Exadata platforms. (http://www.oracle.com/technetwork/database/features/availability/maa-wp-dr-dbm-130065.pdf)

Although the paper is focused on Exadata, some information can be applied to non-Exadata systems as well:

• Corruption Protection:
  • Primary:
    • DB_BLOCK_CHECKSUM=FULL,
    • DB_BLOCK_CHECKING=FULL,
    • DB_LOST_WRITE_PROTECT=TYPICAL
  • (Physical) Standby:
    • DB_BLOCK_CHECKSUM=FULL,
    • DB_BLOCK_CHECKING=OFF,
    • DB_LOST_WRITE_PROTECT=TYPICAL
• Network Tuning:
  TCP Send/Receive Buffers: 3 x Bandwith Delay Product or 10 MB, whichever is greater
• Redo Transport Modes:
  • SYNC is recommended if round-trip-time is less than 5 ms.  Impact of SYNC Mode on primary performance has been improved because local online redo log write and redo shipping is not done sequentially anymore but in parallel.
  • ASYNC: Transport Lag is reduced because Redo is not read from online redo log from disk but from log buffer if possible. init.ora log_buffer might need to be increased for this to yield the maximum benefit.
• LOGGING/NOLOGGING: Normally, in Data Guard you set database-wide “ALTER DATABASE FORCE LOGGING”. In Data Warehouse environments, it might be advisable to set it to NOLOGGING and decide on a tablespace-basis whether the tablespace should allow or disallow NOLOGGING operations. E.g. a tablespace containing only transient, recreatable  or non-critical data might benefit from being set to NOLOGGING for certain bulk operations. (CTAS, Direct Path Inserts, index rebuilds, etc.)
• STANDBY-FIRST Patches: Some patches (PSU, CPU, PSE) will be flagged in the patch README with “Standby-First”. This means that they can be applied on a physical standby before being applied on the primary. For regular patches, this was not supported.

I was looking for a method to utilize the Grid Control Repository, which contains information about installed Oracle Homes, databases and patches, for a patch report.

With a little reverse engineering i came up with these relevant tables:

• Mgmt_Ecm_Snapshot: Every time the inventory is refreshed, a new line is inserted into this table containing the host name and the snapshot_guid. The most current snapshot has flag IS_CURRENT set to ‘Y’
• Mgmt_Inv_Container: Every Oracle Home is a container. This table contains the snapshot_guid and the container_guid along with a container_description which is basically the Oracle Home Path
• Mgmt_Inv_Patch: This table contains container_guid, Patch ID and patch installation timestamp
• Mgmt_Inv_Component: This table lists all the components of the oracle homes along with their version. There is one component per container with the flag Is_Top_Level set to ‘Y’.  We use this component for getting the base version of the installed product. (e.g. 11.2.0.2)
• Mgmt_Inv_Versioned_Patch: I am not sure if this table is needed for version information, but one of the mgmt views was using these two tables together, so I used it as a reference.

The complete statement now is:

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

CREATE OR REPLACE FORCE VIEW "SYSMAN"."RDBMS_PATCH_REPORT"
AS
SELECT
      N.Target_Name,
    S.Start_Timestamp AS Collected_Time,
    S.Target_Name     AS Host_Name,
    C.Container_Name  AS Oracle_Home_Name,
    Container_Location,
    P.Id AS Patch_Id,
    (
    CASE Id
      WHEN '10157506'
      THEN 'GI Bundle1'
      WHEN '10185523'
      THEN 'OWB Bundle'
      WHEN '10248523'
      THEN 'PSU Jan 2011'
      WHEN '11724916'
      THEN 'PSU Apr 2011'
      WHEN '12311357'
      THEN 'GI Psu Apr 2011'
      ELSE NULL
    END ) description,
    P.Timestamp AS Install_Time,
    CASE
      WHEN VP.version IS NULL
      THEN M.version
      ELSE VP.version
    END AS Version
  FROM Mgmt_Ecm_Snapshot S,
    Mgmt_Inv_Container C,
    Mgmt_Inv_Patch P,
    (SELECT T.Target_Guid,
      T.Host_Name,
      T.Target_Name,
      T.Target_Type,
      Mp.Property_Value AS Oh
    FROM Mgmt_Targets T,
      Mgmt_Target_Properties Mp
    WHERE T.Target_Guid  = Mp.Target_Guid
    AND Mp.Property_Name = 'OracleHome'
    AND Target_Type      = 'oracle_database'
    ) N,
    Mgmt_Inv_Component M ,
    MGMT_INV_VERSIONED_PATCH VP
  WHERE S.Snapshot_Guid = C.Snapshot_Guid
  AND S.Is_Current      = 'Y'
  AND C.Container_Type  = 'O'
  AND P.Container_Guid  = C.Container_Guid
  AND N.Host_Name       = S.Target_Name
  AND N.Oh              = C.Container_Location
  AND M.Component_Guid  = Vp.Component_Guid(+)
  AND M.Is_Top_Level    = 'Y'
  AND M.Container_Guid  = C.Container_Guid
  ORDER BY 1,2,3;