how to return 0 if no rows

Add a union all with a query that returns the default you want when no rows in your query are returned:

select
  TARGET_NAME,
  TARGET_TYPE,
  SUBSTR(KEY_VALUE, instr(KEY_VALUE, '.', -1, 1) + 1) as CLUSTER_INSTANCES_VALUE
from DBTABLE
where METRIC_NAME = 'PARAMETER_VALUES' 
and VALUE like 'cluster_database_instances'
and TARGET_NAME like '%ORACLE12%'
union all
select null, null, 0
from dual
where not exists (
    select *
    from DBTABLE
    where METRIC_NAME = 'PARAMETER_VALUES' 
    and VALUE like 'cluster_database_instances'
    and TARGET_NAME like '%ORACLE12%'
)

Note: Other improvements left out to focus the task at hand.

One way to do this would be to have a union all which checks for the existence of a record that do not match the WHERE clauses and return 0 against the CLUSTER_INSTANCES_VALUE as below.

 select TARGET_NAME
       ,TARGET_TYPE
       ,SUBSTR(KEY_VALUE, instr(KEY_VALUE, '.', -1, 1) + 1) as CLUSTER_INSTANCES_VALUE 
  from DBTABLE  
 where METRIC_NAME = 'PARAMETER_VALUES' 
   and VALUE like 'cluster_database_instances' 
   and TARGET_NAME like '%ORACLE12%'
union all
SELECT '' as TARGET_NAME
       ,'' as TARGET_TYPE
       ,0
  FROM DUAL
 WHERE NOT EXISTS (select null      
                     from DBTABLE  
                    where METRIC_NAME = 'PARAMETER_VALUES' 
                      and VALUE like 'cluster_database_instances')
   and TARGET_NAME like '%ORACLE12%'

You are applying SUBSTR and INSTR to a field, so if the field is null, you will get null. If you want to replace it for 0, then use NVL

select  TARGET_NAME, TARGET_TYPE,SUBSTR(KEY_VALUE, instr(KEY_VALUE, '.', -1, 1) + 1) 1),0) as CLUSTER_INSTANCES_VALUE 
from DBTABLE  
where METRIC_NAME = 'PARAMETER_VALUES' 
and VALUE like 'cluster_database_instances' 
and TARGET_NAME like '%ORACLE12%'

Example

SQL> select substr('A',instr('A',-1,1) ) from dual ;

S
-
A

SQL> select substr(null,instr(null,-1,1) ) from dual ;

S
-


SQL> select nvl(substr(null,instr(null,-1,1) ),0) from dual ;

NVL(SUBSTR(NULL,INSTR(NULL,-1,1)),0)
------------------------------------
                                   0

SQL>

If you are only expecting one row, you can use aggregation. The following returns NULL in all columns:

select MAX(TARGET_NAME), MAX(TARGET_TYPE),
       MAX(SUBSTR(KEY_VALUE, instr(KEY_VALUE, '.', -1, 1) + 1)) as CLUSTER_INSTANCES_VALUE 
from DBTABLE  
where METRIC_NAME = 'PARAMETER_VALUES' and
      VALUE like 'cluster_database_instances' and
      TARGET_NAME like '%ORACLE12%';

You can use COALESCE() to convert to whatever value you want.

If you might get multiple rows, you can use a CTE and union all:

with cte as (
      select TARGET_NAME, TARGET_TYPE,
             SUBSTR(KEY_VALUE, instr(KEY_VALUE, '.', -1, 1) + 1) as CLUSTER_INSTANCES_VALUE 
      from DBTABLE  
      where METRIC_NAME = 'PARAMETER_VALUES' and
            VALUE like 'cluster_database_instances' and
            TARGET_NAME like '%ORACLE12%'
     )
select *
from cte
union all
select '', '', ''   -- replace with the values you really want
from dual
where not exists (select 1 from cte);