Oracle Data Guard 19c TNS-12564: TNS:connection refused

 I faced this error during the switchover in 19c version:

Make sure:

1- Have the correct services statically registered.

2- Configure the local_listener as below in both primary and standby servers:

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary)(PORT=1521)))' scope=both;

alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))' scope=both;

  

ping command not working in CentOS

 I created one VM machine on virtualbox Centos 7 and added two network adapters Bridged Adapter and NAT.

When tried ping google:

ping google.com

ping: google.com: Name or service not know

Solution:

dhclient

 

Password prompt taking long time Linux Centos 7

 RHEL7:

Symptom: 34 seconds to prompt password ssh login
Solution (my case):
- vi /etc/ssh/sshd_config
- GSSAPIAuthentication no
- service sshd restart

vi /etc/ssh/sshd_config
UseDNS no

vi /etc/resolv.conf
options single-request-reopen ;in the last line. No network restart required

Oracle Database Find blocking sessions

 select v.sql_text,v.sql_fulltext,sub.* from v$sql v,

(select sample_time,s.sql_id sql_id, session_state, blocking_session,

owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine

from dba_hist_active_sess_history s, dba_objects o

where event = 'enq: TX - row lock contention'

and o.data_object_id = s.current_obj#

order by 1 desc) sub where sub.sql_id=v.sql_id;

select s1.username || '@' || s1.machine

|| ' ( THIS SID=' || s1.sid || ' )  is blocking '

|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

SELECT 

'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''' immdiate;',sid,username,serial#,process,NVL (sql_id, 0),

sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait

FROM gv$session s WHERE blocking_session_status = 'VALID'

OR sid IN (SELECT blocking_session

FROM gv$session WHERE blocking_session_status = 'VALID');

Import an Oracle APEX Application export file from a higher version into a lower version

Follow the below process to know how and where to change required details:

• Create one dummy application in target instance and export it
• Compare export files from source instance and target instance as shown in below table. It can be seen that the p_version and p_release parameter values are different in both the files

In source instance (18.1)  begin wwv_flow_api.import_begin (  p_version_yyyy_mm_dd=>'2018.04.04' ,p_release=>'18.1.0.00.45' ,p_default_workspace_id=>XXXXXXX ,p_default_application_id=>XXXXXX ,p_default_owner=>'XXX' ); end;

In target instance (5.1.4) begin wwv_flow_api.import_begin (  p_version_yyyy_mm_dd=>'2016.08.24' ,p_release=>'5.1.4.00.08' ,p_default_workspace_id=>XXXXXXX ,p_default_application_id=>XXXXXXX ,p_default_owner=>'XXXX' ); end;

• So, change the parameter values of import_begin procedure in source export file. For e.g., the release and the version parameters in export file
  from ‘18.1.0.00.45’  to ‘5.1.4.00.08‘ and
  from    ‘2018.04.04’    to ‘2016.08.24’ respectively
• Import edited export file in target instance this time it will allow you to run the script

DUPLICATE DATABASE from backup location 11.2 and later

alter system set DB_FILE_NAME_CONVERT='/u01/oradata/psgprod/PSGPROD/datafile/','/u02/oradata/psgtest/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/oradata/psgprod/PSGPROD/datafile/','/u02/oradata/psgtest/' scope=spfile;

run
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux11 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux12 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux13 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux14 DEVICE TYPE DISK;
DUPLICATE DATABASE TO psgtest
 BACKUP LOCATION '/u02/backup';
}

R12.2 ADOP Query object compilation

select * from
(
select
eusr.user_name owner
, count(decode(obj.type#,88,NULL,decode(obj.status,1,NULL,1))) Actual
, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),NULL)) Stub
, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),decode(obj.status,1,NULL,1))) Total
from
sys.obj$ obj
, sys.obj$ bobj
, (
select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
union
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 1 or user# = 1) busr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where ed.name = 'ORA$BASE'
) eusr
where obj.owner# = eusr.user#
and bobj.obj#(+) = obj.dataobj#
and obj.type# not in (0, 10)
and ( obj.type# =88 or (obj.type# = 88 and bobj.type# =10) )
and obj.remoteowner is null
group by eusr.user_name
) x
where total > 0
order by 1;