Make DBA Life Easy

Create a Metric Extension (ME) to Monitor FRA Usage on OEM

OEM Metric Extension (ME) is a great feature for developing customized metrics, which are unavailable by default.

As we know “Archive Area % Used” metric does not work for using FRA as archive destination when the destination is set to USE_DB_RECOVERY_FILE_DEST. We only receive after “Recovery Area % Used” metric triggers when it hits a Warning threshold of 85% full, and Critical of 97% full.

But the fixed thresholds of 85( Warning )/97( Critical) is not good enough for handling FRA full. So we developed a customized more flexible way to monitor and alert when FRA is used as archive destination.

Read more of this content when you subscribe today.

So, the Metric Extension ( ME) is created successfully, in another post, we will create a Incident Rule which will create an incident and notification for all the over threshold Warning and Critical alerts.

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

Further more, we will create a Corrective Action ( CA ) against this Metric Extension ( ME ) to start a archive log backup automatically.

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

ORA-01105 ORA-19808 When Start Up RAC Instance

When tries to start up one instance of the RAC , the following errors occur.

.....
....
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch
...

Subscribe to get access

Read more of this content when you subscribe today.

How to Install Database Component of a Patch onto ORMB Database

Since de-installation of the patch is not supported, DBA should develop a backup/recovery strategy before applying ORMB patches in case rollback is required.

Be aware of the below warning when applying database component of a patch onto ORMB database.

Warning: De-installation of the patch is not supported. Make sure that you have a backup of the database schema you are installing into. It is recommended that you first install and test patches on a non-production environment.

DBA should develop a backup/recovery strategy before applying ORMB patches in case rollback is required if patching fails. The suggested backup strategy could be either of the below:

RMAN Database Backup.
DataPump Exporting Schema.
Create a GRP ( Guarantee Restore Point ) as Flashback RAC Database to Guarantee Restore Point ( GRP )

The following is a practical example of applying patch 25187694 onot ORMB 2.5.0.3.0 from a different server other than an application server.

1)Create Database Patching Utility

Ask ORMB application server administrator to run “createDBStandlone.sh” to create a jar file called “db_patch_standalone.jar”.

2) Copy and Cecompress Database Patching Utility

D:\dbpatch_tools
 config
         log4j.properties
 bin
         ouafDatabasePatch.sh
         ouafDatabasePatch.cmd
 lib
         commons-cli-1.1.jar
         commons-codec-1.6
         commons-collections-3.2.1
         commons-io-1.3.2
         log4j-1.2.17
         ojdbc7-12.1.0.2
         spl-dbpatch-4.3.0.1.0
         spl-shared-4.3.0.1.0

3) Set Windows Environment Variables

set TNS_ADMIN=D:\oracle
set JAVA_HOME=D:\java
set TOOLSBIN=D:\dbpatch_tools\bin

4) Apply Database Component of the Patch

D:\PATCHES\p25187694_25030_PDEV\MultiPlatform\V2.5.0.3.0-25187694_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:
Enter the name of the Oracle Database Connection String: //RACTEST-SCAN.ractest.local: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 25187694 ...
Writing to log file: ORMB001\log25187694.log
-----------------------------------------------------------
--Applying patch 25187694 at 12-08-2016 15:31:50using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information
-----------------------------------------------------------
--Patch 25187694 applied successfully at 12-08-2016 15:31:52
---------------------------------------------------------------
Patch applied successfully..
Enter a comma-separated list of Oracle users in which synonyms need to be created (e.g. CISUSER,CISREAD): CISUSER,CISREAD
Enter a database role which has a read-write privilege (e.g. CIS_USER): CIS_USER
Enter a database role which has a read only privilege (e.g. CIS_READ): CIS_READ
Generating Security ...
-----------------------------------------------------------
--Security is successfully applied at 12-08-2016 15:37:54
---------------------------------------------------------------
D:\PATCHES\p25187694_25030_PDEV\MultiPlatform\V2.5.0.3.0-25187694_MultiPlatform\database\ORACLE\CDXPatch>

5) Review Patch Log File Including Security Generation Log

—
— log25187694

--Applying patch 25187694 at 12-08-2016 15:31:50using $LastChangedRevision: 42479 $
---------------------------------------------------------------
--Copying language information 
insert into ci_ut_instl(instl_log_id,target_ver_id,upg_count,prev_ver_id,start_dttm,run_status_flg,sr_no,owner_flg) values ( 51, 'V2.5.0.3.0', 1, 'V2.5.0.3.0', sysdate, '00', '25187694', 'C1' )
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
INSERT INTO CI_UT_INSTL_DTL(INSTL_LOG_ID,SEQ_NUM,SCRIPT_FILE_NAME,START_DTTM,RUN_STATUS_FLG,OWNER_FLG) VALUES ( 51, 1, '25187694_RS1.SQL', sysdate, '00', 'C1' )
;
--Executed Successfully ...
COMMIT
;

-- RUNNING SCRIPTS HERE
....
...
..
.

--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
UPDATE CI_UT_INSTL_DTL SET END_DTTM=sysdate, RUN_STATUS_FLG='02' WHERE INSTL_LOG_ID=51 AND SEQ_NUM = 1
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
UPDATE CI_UT_INSTL SET END_DTTM=sysdate, RUN_STATUS_FLG='02' WHERE INSTL_LOG_ID=51
;
--Executed Successfully ...
COMMIT
;

--Executed Successfully ...
-----------------------------------------------------------
--Patch 25187694 applied successfully at 12-08-2016 15:31:52
---------------------------------------------------------------

6) Check the Patch has been Applied Successfully

Check the patch status as per How to Check the Successful Installation of a Database Patch Onto ORMB Database

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

SR_NO     RU   END_DTTM
--------- --  -------------------
25187694  02   20161208-15:31:52

Check Tablespace Space Usage by Querying DBA_TABLESPACE_USAGE_METRICS

Quick way to know tablespace space usage by querying “DBA_TABLESPACE_USAGE_METRICS”.

DBA need know a tablespace space usage. There are a number of ways to do so by joining below different views:

DBA_TABLESPACE
DBA_DATA_FILES
DBA_FREE_SPACE
…
..
.

There is a quicker way to do so by querying DBA_TABLESPACE_USAGE_METRICS.

SQL> desc DBA_TABLESPACE_USAGE_METRICS
 Name               Null?   Type
 ----------------  -------  -----------
 TABLESPACE_NAME            VARCHAR2(30)
 USED_SPACE                 NUMBER
 TABLESPACE_SIZE            NUMBER
 USED_PERCENT               NUMBER

SQL> select * from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME  USED_SPACE   TABLESPACE_SIZE   USED_PERCENT
---------------- ------------ ----------------- ------------
PSAPP                6255360            7635328 81.9265394
PSAPPIDX             7025984           11028864 63.7054188
PSHUGE01            28452352           41633280 68.3404046
PSHUGE01IDX         33479424           46721792 71.6569775
PSHUGE02             4750856           11776000 40.3435462
PSHUGE02IDX          3838600            9728000 39.4592928
PSIBTRAN             1822832            2621440 69.5355225

Please note :

USED_SPACE and TABLESPACE_SIZE are in blocks.
TABLESPACE_SIZE is the maximum possible size if AUTO extended on, not the current size. The same applies to USED_PERCENT.

Joining DBA_TABLESPACE_USAGE_METRICS with DBA_TABLESPACE, we can get easily readable size in MB instead of default blocks.

SQL>SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;
 

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT
---------------- -------------- ------------------- ------------
PSHUGE02IDX            29989.06            76000           39.46
PSHUGE02               37116.06            92000           40.34
PSAPPIDX               54890.5             86163           63.71
PSHUGE01              222284              325260           68.34
...
..
.

Sometimes we see “USED_PERCENT” is not refreshed immediately after datafiles are added or dropped. in this case, alter tablespace offline then online helps, if only you can do it.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL       23.06          123                 18.75

SQL> alter tablespace TESTUSER_TBL offline;
Tablespace altered.

TABLESPACE_NAME  USED_SPACE(MB) TABLESPACE_SIZE(MB) USED_PERCENT 
---------------- -------------- ------------------- ------------
TESTUSER_TBL        22                  22            100

How to Check the Successful Installation of a Database Patch Onto ORMB Database

Query table cisadm.ci_ut_instl to confirm successfully applied a ORMN patch or not.

After applied the database component of a patch, we can run the following sql to check whether the database component was installed successfully , or skipped because it has been installed before.

Here both ‘22505470’ and ‘9999999″ are ORMB patch id.

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

SR_NO    RU  END_DTTM
-------- --- ------------------
22505470 02  20161125-09:20:34

If the database component was not included or was not installed, an empty result set is returned.

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

no rows selected