Blog

How to Apply Database Component Of a Patch Onto ORMB Database

Always make a backup of CISADM schema or whole database before applying database component of a patch onto ORMB database.

Here is an example of applying an ORMB patch onto ORMB Oracle database.  There working environment is as below ;

Oracle Database 12c
Oracle Client 32bit 12c
ORMB 2.5.0.3
JDK 8

Before applying patching, make a backup of database in any way you prefer:

Set the environment variables:

C:\>set TNS_ADMIN=d:\oracle
C:\>set TOOLSBIN=d:\dbpatch_tools\bin
C:\>set JAVA_HOME=d:\java

Ensure to be able to connect to the database from the application server workstation:

C:\>tnsping ormb
TNS Ping Utility for 32-bit Windows: Version 12.1.0.2.0 - Production on 13-JAN-2017 10:19:11
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
d:\oracle\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ractest-scan.ractest)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVIC
E_NAME = ORMB)))
OK (10 msec)

Unzip the patch files, change to the CDXPatch directory and execute the ouafDatabasePatch.cmd utility. The utility will prompt you for the value of the following parameters:

if it prompts you for the target database type, enter O for Oracle.
Enter the username that owns the schema (e.g., CISADM)
The password for the user (in silent mode)(e.g., XXXXXXX)
Enter the name of the Oracle Database Connection String : //ractest-scan.ractest:1521/ORMB

Below is the output from applying patch 25054594 ( with three child patches -24586912, 24604908, 24963398  ).

D:\PATCHES\p25054594_25030\MultiPlatform\V2.5.0.3.0-25054594_MultiPlatform\database\ORACLE\CDXPatch>ouafDatabasePatch.cmd
"CMDLINE::: d:\java\bin\java.exe -cp d:\dbpatch_tools\lib\*;d:\dbpatch_tools\config com.oracle.ouaf.database.patch.OUAFPatch "
Enter the target database type (O/M/D) [O]: O
Enter the username that owns the schema: CISADM
Enter the password for the CISADM user: XXXXXXX
Enter the name of the Oracle Database Connection String: //ractest-scan.ractest:1521/ORMB
Target Schema is a Production Schema
Ready to process patches, Do you want to continue? (Y/N): Y
Working Directory: ORMB001
***********************************
Setting up language file: ORMB001\CDXPatch.lang
exit value: 0

Applying 24586912 ...
Writing to log file: ORMB001\log24586912.log
-----------------------------------------------------------
--Applying patch 24586912 at 01-13-2017 14:20:49using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24586912 applied successfully at 01-13-2017 14:21:20
---------------------------------------------------------------

Applying 24604908 ...
Writing to log file: ORMB001\log24604908.log
-----------------------------------------------------------
--Applying patch 24604908 at 01-13-2017 14:21:20using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24604908 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------

Applying 24963398 ...
Writing to log file: ORMB001\log24963398.log
-----------------------------------------------------------
--Applying patch 24963398 at 01-13-2017 14:21:31using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 24963398 applied successfully at 01-13-2017 14:21:31
---------------------------------------------------------------
Patch applied successfully...

Check the successful installations of the patches as per How to Check the Successful Installation of a Database Patch Onto ORMB Database

We can see only the child patches ( 24586912, 24604908, 24963398 ) are stored in repository. The parent patch ID is NOT installed into database patch repository )

SQL>  select sr_no,run_status_flg,END_DTTM 
        from cisadm.ci_ut_instl 
       where sr_no in ( '25054594','24586912','24604908','24963398') 
         and run_status_flg = '02' 
         and END_DTTM IS Not NULL;

SR_NO                          RU END_DTTM
----------------               -- ---------
24586912                       02 13-JAN-17
24604908                       02 13-JAN-17
24963398                       02 13-JAN-17

Finally run Security utility as per How to Run Security Utility in ORMB Multiple Instances Environment

ORA-16826: apply service state is inconsistent with the DelayMins property

For DataGuard Real Time Apply”, USING “CURRENT LOGFILE” option for “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” matches property “DelayMins = ‘0’”.
Non Real Time Apply : alter database recover managed standby database disconnect from session
REAL TIME APPLY : ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Achieve real time apply by using online standby redo logs

OEM alert for DataGuard status is “ORA-16826: apply service state is inconsistent with the DelayMins property”.

Oracle official explanation is as below :

$oerr ora 16826
16826, 0000, "apply service state is inconsistent with the DelayMins property"
// *Cause: This warning was caused by one of the following reasons:
// - The apply service was started without specifying the real-time
// apply option or without the NODELAY option when the DelayMins
// property was set to zero.
// - The apply service was started with the real-time apply option or
// with the NODELAY option when the DelayMins property was set to
// a value greater than zero.
// *Action: Reenable the standby database to allow the broker to restart
// the apply service with the apply options that are consistent
// with the specified value of the DelayMins property.

It seems the “DelayMins” property is not matching the option used in “alter database recover managed standby database …..”. Let’s have a detail check.

DGMGRL> show configuration;
..
Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database
 Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED
Configuration Status:
WARNING

Primary database side is good, the warning is from standby database side.

DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 13 minutes 1 second (computed 0 seconds ago)
 Apply Rate: 61.33 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
 ORA-16826: apply service state is inconsistent with the DelayMins property
DGMGRL> show database "STDBYDB" "DelayMins"
 DelayMins = '0'
DGMGRL>

On standby database applying instance STDBYDB2. From alert.log, we can see the standby database “not using Real Time Apply”.

...
..
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 16:00:06 2017
MRP0 started with pid=55, OS id=66078
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 16:00:11 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 32 slaves
Thu Jan 12 16:00:15 2017
Block change tracking file is current.
Starting background process CTWR
Thu Jan 12 16:00:15 2017
CTWR started with pid=89, OS id=66416
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_1_seq_328339.32376.933091003
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_2_seq_555739.2592.933091137
Thu Jan 12 16:00:17 2017
Completed: alter database recover managed standby database disconnect from session
...
..

Move on to any  primay database instance and run below query, we can see the same — the standby database “not using Real Time Apply”

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE 
       from V$ARCHIVE_DEST_STATUS 
       where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY MANAGED

On standby database, stop the Redo Apply and then start the redo apply in real time mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Check standby database alert.log again, we can see standby database in “Real Time Apply” by using standby online logs:

...
..
Thu Jan 12 23:01:56 2017
Managed Standby Recovery Canceled (STDBYDB2)
Completed: alter database recover managed standby database cancel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 23:02:00 2017
MRP0 started with pid=54, OS id=106637
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 23:02:05 2017
Managed Standby Recovery starting Real Time Apply
...
..
Thu Jan 12 23:02:09 2017
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Thu Jan 12 23:02:39 2017
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_4_seq_273573.33789.933116469
Media Recovery Waiting for thread 1 sequence 328381 (in transit)
Recovery of Online Redo Log: Thread 1 Group 101 Seq 328381 Reading mem 0
 Mem# 0: +DATA2/stdbydb/onlinelog/group_101.1037.903651087
....
...

On any primary database instance, run below sql, we can see the same — standby database in “Real Time Apply” by using standby online logs:

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY REAL TIME APPLY

Finally check DataGuard configurations:

DGMGRL> show configuration;

...

Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Apply Rate: 8.93 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
SUCCESS

CREATE INDEX COMPUTE STATISTICS

“COMPUTE STATISTICS” is the default behavior for “CREATE INDEX”.

When creating an index, “COMPUTE STATISTICS” option is no longer required for 10g onwards databases. Here is a quick demonstration:

Create a table and insert a couple of sample records:

SQL> create table test ( id number, name varchar(20));
Table created.

SQL> insert into test values (1,'james1');
1 row created.

SQL> insert into test values (2,'james2');
1 row created.

SQL> insert into test values (3, 'james3');
1 row created.

SQL> commit;
Commit complete.

No statistics for newly created table:

SQL> select table_name,num_rows, last_analyzed 
       from user_tables 
      where table_name='TEST';

TABLE_NAME      NUM_ROWS      LAST_ANAL
-----------     ----------     ---------
TEST

Create a new index :

SQL> create index ind_test_id on test(id);
Index created.

Check the index statistics:

SQL> select INDEX_NAME,TABLE_NAME,NUM_ROWS,LAST_ANALYZED 
      from user_indexes 
     where INDEX_NAME='IND_TEST_ID';

INDEX_NAME    TABLE_NAME   NUM_ROWS  LAST_ANAL
-----------   ------------ --------- --------
IND_TEST_ID    TEST         3        05-JAN-17

From the example, we can see even there is no statics for the table, but the statistics is collected for the index when it is created.

Use Corrective Action ( CA ) to Automate Archivelog Backup When FRA Usage is Over Threshold of Metric Extension ( ME ) in OEM

Take advantage of OEM Corrective Action ( CA ) to automate archive logs backup after FRA usage over threshold.

In Previous posts, we have created following related posts:

  1. Create a Metric Extension (ME) to Monitor FRA Usage on OEM
  2. Create an Incident Rule for Metric Extension ( ME ) to Monitor FRA Used in OEM

Now and then, FRA usage is easily over threshold, DBA needs logon system and manually run an archivelog backup.  It is easily to make database hung if FRA usage is 100%.

Here we use “Corrective Actions” to run a RMAN archivelog backup when the FRA usage is over warning / Critical threshold.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

10) After a little while, an alert is received about “Corrective action status=Succeed”

Corrective action=RMAN_AUTO_BACKUP_ARCHIVELOGS
Corrective action owner=SYSMAN 
Corrective action status=Succeeded 
Corrective action output=SID: RACTEST1
HOME: /u01/app/oracle/product/11.2.0/dbhome_1
BASE: /u01/app/oracle
SID: RACTEST1
HOME: /u01/app/oracle/product/11.2.0/dbhome_1
BASE: /u01/app/oracle

Host=RACTEST1.ractest 
Target type=Cluster Database 
Target name=RACTEST-CLUSTER 
Categories=Capacity 
Message=The value of Flash Recovery Area % Used for +FRA is 82
Severity=Warning 
Event reported time=Dec 22, 2016 9:01:54 PM EST 
Target Lifecycle Status=Development 
Comment=RAC TEST
Operating System=Linux
Platform=x86_64
Associated Incident Id=209879 
Associated Incident Status=New 
Associated Incident Acknowledged By Owner=No 
Associated Incident Priority=High 
Associated Incident Escalation Level=0 
Event Type=Metric Alert 
Event name=ME$RACRecoveryAreaSpace:FRA_PERCENT_USED 
Metric Group=ME$RACRecoveryAreaSpace
Metric=FRA_PERCENT_USED
Metric value=82
Key Value=+FRA
Key Column 1=FRA_NAME
Rule Name=Flash Recovery Area Percent Used for ME

Create an Incident Rule for Metric Extension ( ME ) to Monitor FRA Used in OEM

Step by step demonstrates how to create an incident rule for a Metric Extension ( ME).

In another post, we created a Metric Extension (ME) to Monitor FRA Usage on OEM. Here we need create an Incident Rule for creating an incident and notification for all events of Warning or Critical.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

8) After a couple of minutes, the below alert is received:

Host=ractest1 
Target type=Cluster Database 
Target name=RACTEST
Categories=Capacity 
Message=The value of Flash Recovery Area % Used for +FRA is 80 
Severity=Warning 
Event reported time=Dec 22, 2016 3:08:43 AM EST 
Target Lifecycle Status=TEST
Comment=RACTEST CLuster
Operating System=Linux
Platform=x86_64
Associated Incident Id=209693 
Associated Incident Status=New 
Associated Incident Owner=TESTUSER
Associated Incident Acknowledged By Owner=No 
Associated Incident Priority=High 
Associated Incident Escalation Level=0 
Event Type=Metric Alert 
Event name=ME$RACRecoveryAreaSpace:FRA_PERCENT_USED 
Metric Group=ME$RACRecoveryAreaSpace
Metric=FRA_PERCENT_USED
Metric value=80
Key Value=+FRA
Key Column 1=FRA_NAME
Rule Name=RACTEST Ruleset,Flash Recovery Area Percent Used for ME 
Rule Owner=TESTUSER
Update Details:
The value of Flash Recovery Area % Used for +FRA is 80
Incident created by rule (Name = RACTEST Ruleset, Flash Recovery Area Percent Used for ME; Owner = TESTUSER).