Query to find ASM Freespace with Redundancy

Below Query will show how much space is available to use in case of High or Normal Redundancy

TOTAL_MB:- Refers to Total Capacity of the Diskgroup

FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.

FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))

REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.

USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.

Its calculated as :-

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy

USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy

Query to Run:

column total format 999,999 Heading "Total(G)"

column free format 999,999 Heading "Free (G)"

column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"

column Usable_GB format 999,999 Heading "Space Available |to Use(G)"

column pct format 999.0 Heading "% Free |in DG" 

column pct2 format 999.0 Heading "Real % Free |in DG" 

column type format a10

column name format a20

set linesize 200

set colsep '|'

prompt

Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

prompt

select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2  from v$asm_diskgroup;

Sample Output :

"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"

                    |          |        |        |     Space Used |Space Available |% Free |Real % Free

NAME                |TYPE      |Total(G)|Free (G)|for Mirroring(G)|       to Use(G)|  in DG|       in DG

--------------------|----------|--------|--------|----------------|----------------|-------|------------

DATA1              |HIGH      | 260,496|  57,951|          14,472|          14,493|   22.2|         5.6

REDO1              |HIGH      |  65,124|  33,322|           3,618|           9,901|   51.2|        15.2

oracle : Scheduling ASH reports through Crontab

I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab

Script to capture the ASH reports every 5 Minutes

$ cat ash.ksh

#!/bin/bash

export TZ=US/Central

dateString=`date +%d-%b-%Y_%H:%M:%S`

sqlplus -s / as sysdba << EOD1

define report_type = 'html'

define begin_time = '-5'

define duration = ''

define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'

@?/rdbms/admin/ashrpt

exit

EOF

To run it through Cron

0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

 After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with the below error

USER (ospid: 60897): terminating the instance due to error 304

Instance terminated by USER, pid = 60897

Wed Mar 06 02:14:34 2019

Starting ORACLE instance (normal)

Looking into the spfile, I noticed the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb

Database unique name: oradb

Database name: oradb

Oracle home: /opt/app/oradb/oracle/product/11.2.0.4

Oracle user: oradb

Spfile: +oradb_DATA/oradb/spfileoradb.ora

Domain: db.abc.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: oradb

Database instances: oradb1,oradb2

Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2

Mount point paths: 

Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com

Type: RAC

Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2

*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb

Hope this resolves your issue. 

ORA-12537: TNS:connection closed - Oracle RAC 11g and above

 The client was getting Below error while connecting to the RAC database

sqlplus test@RACDB_1

SQL*Plus: Release 11.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:

ORA-12537: TNS:connection closed

Issue : 

The Oracle Binary permissions got changed and were not allowing the connections

Current Permissions under Oracle Home

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwxr-sr-x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

It should be set to 6751 and should look like "-rwsr-s--x"

But doing chmod 6751 on oracle binary was not setting the correct permissions

[oracle@Node1 bin]$ chmod 6751 oracle

[oracle@Node1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-x--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Running below as RDBMS database Owner user helped, in this case, "oracle" user

1) Stop the database instance where the permissions got changed

 srvctl stop instance -d RACDB -i RACDB1

 2) Run as Oracle Database owner, in this case its Oracle OS user.  

[oracle@Node1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle

3) The permissions got changed and resolved the connection issues

[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 oracle asmadmin 243043788 Jun  2 01:12 oracle

Hope this resolves your issue.. 

exadbcpatchmulti : Conflicts and ERROR: apply_async blocked on node

 Now a days i'm facing multiple issues on ExaCM boxes for patching on database nodes.

Sharing one scenario, Get request from customer apply APRIL_PSU2021 on ExaCM db boxes

ENV:
DB: 11204 version
GRID have 19c with APRIL2021 PSU.


*: Issue comes while running : exadbcpatchmulti -precheck_async 32537095 , failed with conflicts.
 Get details from exadbcpatch log / conflict log to identify more details.

*: How to resolve conflicts ?
Share the all details and conflict with oracle, They provide me the solution below
Edit /var/opt/oracle/exapatch/exadbcpatch.cfg - #For this file mostly root ownership, need to take help from GRID owner team or any other script available to update cfg file.

add the Cloud patch which needs to be rolled back as in below.
rollback_needed_before_apply="32328629::normal"

Refer Doc:
Exadbcpatchmulti patch conflict on cloud MLR "ERROR: precheck failed in conflict check" (Doc ID 2492936.1)

*: Now Pre-checks successfully done without any issue and move forward to apply the patch on next step.

*: Now facing the issue again : Node_1 successfully applied the patch but another issue occur on Node_2 below the details here.

++++++Error getting when apply patch: On Node_1 successfully done but having issue on Node_2
INFO: running on the node Xxx3a02c02vm01-adm
INFO: check for this action apply_async
INFO: check for this action rollback_async
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
ERROR: apply_async blocked on node Xxx3a02c02vm01-adm: as Exadbcpatch operation already running on requested home
ERROR: apply_async blocked on node Xxx3a02c02vm01-adm: as Exadbcpatch operation already running on requested home

++++++Solution
execute on all the nodes then run exadbcpatchmulti again.
mv /var/opt/oracle/cstate.xml /var/opt/oracle/cstate.xml_old

Refer Doc:
Exadbcpatchmulti fails with Apply_async Blocked On Node X As Another Instance Of Exadbcpatch Running (Doc ID 2530468.1)

*: Now run exadbcpatchmulti  again and successfully applied the patch on ExaCM dbs.

ORA-20001: Latest xml inventory is not loaded into table

Symptoms: As i faced during execute datapatch verbose 19c.
During execution of datapatch or DBMS_QOPATCH following error seen

ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1448
ORA-06512: at "SYS.DBMS_QOPATCH", line 122

Solution: 

1. oraInventory issue
2. Recreate oraceinventory sometime works
3. 19c not using softlink so need to set correct entry into oratabs and run datapatch -verbose

-Because ORACLE_HOME using softlink that's why need to set this parameter for temporary purpose once datapatch done remove this parameter from pfile.

ORA-20001: Latest xml inventory is not loaded into table --->
1.
Startup upgrade
create or replace directory OPATCH_LOG_DIR as '$OH/QOpatch'; ## don't using ORACLE_HOME softlink
create or replace directory OPATCH_SCRIPT_DIR as '$OH/QOpatch';
 
ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;
shutdown immediate
startup upgrade
 
 
cd $ORACLE_HOME/OPatch
 
./datapatch -verbose
 
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_disable_direc%' escape '/';

Ref Doc:
datapatch fails with "ORA-20009:" “ORA-20013: DBMS_QOPATCH ran mostly in non install area” ORACLE_HOME is a SYMBOLIC LINK (Doc ID 2033620.1)
Queryable Patch Inventory - Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)

GoldenGate 12.1.2 not firing insert trigger

Getting report of goldengate Replicate process which is shows after upgrade from 11g to 12c.

Even though checkpoint table also upgraded. But Replicate process unable to start. 

------------------------------------------------------------------------------------------------------------------------

2021-02-11 04:13:31  INFO    OGG-06451  Triggers will be suppressed by default.

--More--(36%)
2021-02-11 04:13:31  INFO    OGG-01815  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /opt/app/p0bfo3d1/ggs/12.1.2.1.0/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE:                                2G
CACHEPAGEOUTSIZE (default):               8M
PROCESS VM AVAIL FROM OS (min):           4G
CACHESIZEMAX (strict force to disk):   3.41G

Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0    Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Database Language and Character Set:
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************
 
Opened trail file /opt/app/ORCL/ggs/11.2.1.0.3/trails/y1000331 at 2021-02-11 05:01:20
 
2021-02-11 05:01:20  INFO    OGG-03506  The source database character set, as determined from the trail file, is we8iso8859p1.
 
2021-02-11 05:01:20  INFO    OGG-06505  MAP resolved (entry ggsuser.ggs_heartbeat): MAP "GGSUSER"."GGS_HEARTBEAT", TARGET ggsuser.ggs_lagtime, KEY
COLS (ID), INSERTALLRECORDS, COLMAP (USEDEFAULTS, id = 0, committime = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), groupname  = @GETENV ('GGENVIRONME
NT', 'GROUPNAME'), ).
Using the following default columns with matching names:
  SOURCETIME=SOURCETIME, TARGETTIME=TARGETTIME, LAGTIME=LAGTIME
 
2021-02-11 05:01:21  INFO    OGG-06510  Using the following key columns for target table GGSUSER.GGS_LAGTIME: ID.
 
 
2021-02-11 05:01:21  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "G
GSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
 
2021-02-11 05:01:21  WARNING OGG-01004  Aborted grouped transaction on 'GGSUSER.GGS_LAGTIME', Database error 1 (OCI Error ORA-00001: unique constr
aint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)
>).
 
2021-02-11 05:01:21  WARNING OGG-01003  Repositioning to rba 16083229 in seqno 331.
 
2021-02-11 05:01:21  WARNING OGG-01154  SQL error 1 mapping GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME OCI Error ORA-00001: unique constraint (G
GSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
 
2021-02-11 05:01:21  WARNING OGG-01003  Repositioning to rba 16083229 in seqno 331.
Source Context :
  SourceModule            : [er.errors]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150384/oggcore/OpenSys/src/app/er/errors.cpp]
  SourceFunction          : [take_rep_err_action]
  SourceLine              : [701]
  ThreadBacktrace         : [13] elements
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7f69e005bdae]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x340
) [0x7f69e0056ac0]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualD
BObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x7f69e004fcff]
]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*,
 char*, file_def*, bool)+0xdda) [0x580b82]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_record(file_def*&, short&, extr_ptr_def*&, extr_ptr_def*&, int&, bo
ol&, int&, int&, bool)+0xcaa) [0x5d0f5a]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_replicat_loop()+0xe11) [0x5ad7f1]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(replicat_main(int, char**)+0x5ca) [0x5c6b7a]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x699c0f]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::T
hread::ThreadArgs*)+0x104) [0x699f44]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x69a04b]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(main+0x3f) [0x5c580f]]
                          : [/lib64/libc.so.6(__libc_start_main+0x100) [0x3b5481ed20]]
                          : [/opt/app/ORCL/ggs/12.1.2.1.0/replicat() [0x517099]]
 
2021-02-11 05:01:21  ERROR   OGG-01296  Error mapping from GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME.

------------------------------------------------------------------------------------------------------------------------

 Solution:

I had to troubleshoot a situation, after GoldenGate capture some DML and replicate that, Oracle database needs to run insert trigger making some business integration.

After to upgrade this enviroment from GG 11.1.1.1 to 12.1.2 and DB 11.2.0.3 to 12.1.0.2, was identified that GoldenGate wasn’t running this triggers

So, found interesting resolution on Oracle Docs:

SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS

Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)

SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.

So, added 'DBOPTIONS NOSUPPRESSTRIGGERS' in the replicat parameter file.

OR

However, altough you probably want most of the triggers not to be executed on the target, it can be possible to override this setting, for example when you implement a heartbeat table.

The way to exclude the trigger from the SUPRRESSTRIGGERS setting is to grant the trigger an exception. This can be done as follows on the target:

SQL> EXEC dbms_ddl.set_trigger_firing_property('HR','trg_tbl_heartbeat', FALSE);'

The result of the statement above is that trigger hr.trg_tbl_heartbeat will go off in the target database.

=================================================================================

2nd.

If above steps are not working and still Replicate process Abended then go for below the steps.

 upgrade Goldengate from 11g to 12c ?

If yes, you have two options :

1) Change the double quotes to single quotes

2) Add NOUSEANSISQLQUOTES into your GLOBALS file and restart the processes
/
NOUSEANSISQLQUOTES is intended for backward compatibility with the parameter files of Oracle GoldenGate versions that predate version 12c, where strings in double quotes are intended to be literals and case-sensitive column names are not supported (whether or not they are within quotes).

Reference : https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters183.htm#GWURF728