centos – You must change the ownership of ORACLE_BASE directory to “oracle” user

I am trying to install Oracle 18c XE on CentOS dedibox but this message is displayed

(SEVERE) The install cannot proceed because ORACLE_BASE directory (/opt/oracle)
is not owned by "oracle" user. You must change the ownership of ORACLE_BASE
directory to "oracle" user and retry the installation.

Please advise

Best Regards

migration – Move Oracle Database to New Server with Dataguard in place

I have a live Oracle Database with Dataguard in placed. I want to migrate live database to new server as tech refresh, but only Primary database. How I can achieve this? I have ideas on how I want to do it but I’m not sure whether it is correct.

  1. Create a full backup, then restore it to new server and update the dataguard configuration. Problem is, SCN in Standby database maybe different than New Primary database due to old Primary database is still running. If this happen, can i apply log from old Primary to New Primary? I’m not sure..
  2. Switch the existing Standby database to be Primary database and create a new standby database in new server. Then switch back Primary role to new database in new server.

PS. I’m not good in English. Thanks in advance.

oracle – How to connect to specific session id & serial# ? Blocked sessions

I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How?

Background: using V$SESSION_BLOCKERS I see sessions are blocked by each other by INSERT statements to one of two tables: _REQUEST and _RESPONSE

Blocked sessions wait event: enq: TX - row lock contention

Query 1:

insert into _REQUEST (creation_date, IS_PROCESSED, name, packet, PARENT_SKID, BATCH_SKID, retry_delay, revision, SERVICE_NAME, ttl, type, REQUEST_SKID)
values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 )

Query 2a (from DB trigger, :B1 is a payload):

SELECT COALESCE(ERROR_DETAILS, ERROR_MESSAGE, FAULT_REASON, SOAP_ERROR) AS ERROR_MESSAGE, ERROR_CODE 
FROM XMLTABLE( '//*:DataHandlerWebServiceException | //*:Fault | //*:PLMDataHandlerError' 
     PASSING XMLTYPE(:B1 )
     COLUMNS ERROR_DETAILS VARCHAR2(256) PATH '*:errorDetails'
            ,ERROR_MESSAGE VARCHAR2(256) PATH '*:errorMessage'
            ,ERROR_CODE VARCHAR2(8) PATH '*:errorCode'
            ,FAULT_REASON VARCHAR2(256) PATH '*:Reason/*:Text'
            ,SOAP_ERROR VARCHAR2(256) PATH '*:COMMON_LOG_MESSAGE/*:LOG_MESSAGE' )

Query 2b (we already know APPEND is ignored here, see Note 1):

INSERT /*+ append */ INTO _response( RESPONSE_SKID, REQUEST_SKID, HAS_FAILED, WAS_TIMEOUT, IS_PROCESSED, SOAP_REQUEST_TIMESTAMP, SOAP_RESPONSE_TIMESTAMP, RESPONSE_HTTP_STATUS, ERROR_MESSAGE, RESPONSE_BODY )
values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )

How is it possible that INSERTs, to different tables, are blocking the sessions? I was looking into V$ACTIVE_SESSION_HISTORY but don’t find anything yet (I’m not a DBA yet)

Note 1: APPEND hint ignored

Oracle source with SSIS 2008

I am trying to use an Oracle OleDb source in my SSIS package with no luck, every time I try to connect to the server I get the error:

ORA-12154 tns could not resolve the connect identifier specified

, knowing that:

1 I installed Oracle 11g express edition

2 I added an entry for the alias in my tnsnames.ora file

3 ORACLE_HOME variable pointing to my Oracle installation

4 Path & TNS_ADMIN variables pointing to the directory containing the tnsnames.ora file

5 I am using a Windows Server 2012

What am I doing wrong?

After enabling SSL in Oracle user must log in twice

I’ve enabled SSL for my Oracle 12c DB. The lsnr starts up fine and I can access it via a sqlplus / as sysdba (no password auth there, it’s OS local). However, if I attempt to connect as a user which requires password authentication, then it fails the first time with the error ORA-28864: SSL connection closed gracefully and then prompts again for username/password. If I enter my username and password again, it works!

Why would this fail the first time but work the second? That behavior is so odd and really what I want to try to figure out.

plsql – How to separate a part of a string from other parts in Oracle PL-SQL using “regular_expression”

I have a string like this :

string := ' sum(x) = 12 and 
            sum(y) = 13 and
            sum(z) = 14 and
            w = 'p'   AND 
            q = 'p'   AND 
            l = 'p''

and I need to separate all sum(...) from the rest of the query:

where_Clause:=   w = 'p'   AND 
                 q = 'p'   AND 
                 l = 'p'

having_Clause :=  sum(x) = 12 and 
                  sum(y) = 13 and
                  sum(z) = 14 and

I’m sure there is a way to do that by regular_expression but cause I’m very new to oracle pl-sql , I need some help. I was wondering if you could help me with that.

Thanks on advance

oracle – ORA-12560: TNS:protocol adapter error in Windows 10

Listener and service are up and running. In SQL Plus, if I connect with no login, and execute ‘conn sys/pwd@host:port/sid as sysdba‘, connection is established successfully. But if I connect / as sysdba or with username and password , it always throws ORA-12560: TNS:protocol adapter error. What are the possible reasons for this ?

oracle – Oracle19C installation is stuck at executing datapatch in Windows 10 using dbca

I tried using dbca. There are no errors. It just hangs. I tried leaving it running for 4 hours. It is always stuck at 36% even after multiple tries.

Logs:

[progressPage.flowWorker] [ 2020-05-25 00:26:04.206 IST ] [CloneDBCreationStep.executeImpl:823] executing datapatch %ORACLE_HOME%OPatchdatapatch.bat

oracle – What is SYSRAC SESSION?

In my environment (oracle 19c) I can see several SYSRAC sessions. What is the purpose of this session?

SQL> select username,status from gv$session where type <> 'BACKGROUND';

USERNAME                       STATUS
------------------------------ ------------------------------
SYS                            INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SOE                            ACTIVE
SYSRAC                         INACTIVE
SOE                            ACTIVE
SOE                            ACTIVE
SYS                            ACTIVE
SOE                            ACTIVE

USERNAME                       STATUS
------------------------------ ------------------------------
SOE                            ACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE

I need to know because sometimes I use a script to kill all sessions and I dont know if I always need to exclude this session of my script (where username <> ‘SYSRAC’)

I’ve already research about it, but I just found about SYSRAC role from oracle 12c r2

The following is a list of new features or enhancements provided with
Oracle Database 12c Release 2 (12.2):

New Administrator Role

Oracle Database 12c Release 2 (12.2) provides support for separation
of duty for Oracle Database by introducing the SYSRAC role for Oracle
Real Application Clusters (Oracle RAC) management. SYSRAC, like SYSDG,
SYSBACKUP, and SYSKM, enables you to enforce separation of duty and
reduce reliance on the use of SYSDBA on production systems. The SYSRAC
role is the default mode and is assigned only the priveleges required
for connecting to the database by the clusterware agent on behalf of
the Oracle RAC utilities such as srvctl.

base de datos – Comprobar si una columna existe en una tabla PL/SQL Oracle

¡Buenos dias!

Necesito saber como comprobar si existe una columna en una tabla especifica. Estoy usando PLSQL en ORACLE 12c, a ser posible lo optimo sería poder usarlo en un IF THEN.

He intentado lo siguente:

ALTER TABLE tabla
    ADD (columna NUMBER(10));

Pero esto me devuelve un error si ya existe. y necesito que la cree solamente si no existe, y si no, que siga con la ejecucion del programa.

Tambien he intentado:

SELECT *
FROM DBA_CLU_COLUMNS
WHERE CLU_COLUMN_NAME = 'PRUEBA';

Pero tampoco me da el resultado que espero.

¡Un saludo!