Wednesday, October 5, 2022

ORA-01017: invalid username/password; logon denied in oracle 12c Dataguard DG broker Suddenly

 It's been long time everything was working fine with oracle DG broker (dgmgrl> ).  Suddenly we faced this error in DG broker while trying to valid network configuration before a SWITCHOVER operation, even though no one change any credentials. 


After sometime, we realized that we were connected to DG broker as SYSDG user (normally connect with SYS). and then found that, the SYSDG user's credential are not same in both PRIMARY and STANDBY password file. that's why it is not able to connect. Here is the query to check this in both environments -   

SQL> select * from v$pwfile_users;

Then logout and connect back with SYS user, everything worked perfectly.

 dgmgrl> connect sys 

So solution is- 1) Either you need to change SYSDG user's credentials in both database same (since remote_login_passwordfile is set to EXCLUSIVE) . so it will saved and match in Password file.

2) OR login with a different super user (like SYS) who's credentials are same for both databases. 


For details you may read- ora-01017



 

 



Monday, August 8, 2022

Test Endpoint failed: Application-Status: 1020912, Application-Message: Network error has occurred” - Amazon Redshift as the target endpoint for AWS DMS

Of course this is a network issue but couldn’t figurable easily. I had to spent quite a good amount of time to find the issue while setting up the Redshift as a Target endpoint in AWS DMS.  

As per below docs, in #6 they mentioned- 6. Enter your Amazon Redshift endpoint for the Server name”  - https://aws.amazon.com/premiumsupport/knowledge-center/dms-redshift-target-endpoint/

Actually it won’t work (and you will get above error message) if you just copy and paste the whole Redshift cluster endpoint from Redshift Cluster general information page.  Normally Redshift cluster endpoint consist of cluster identifier + Region Identifier for cluster + database name + port.

So, the solution is for this error, you need to remove the database name and port number from the actual Redshift cluster endpoint and use this(rest of parts) in the “Server name:” field on AWS DMS endpoint creation page.

 

Hope this will save your valuable time and efforts! 

 

Wednesday, March 10, 2021

Alter a trigger by a procedure ORA-01031: insufficient privileges

Tried to disable and enable a trigger (which is own by another user)  by a procedure but we are getting error  "ORA-01031: insufficient privileges" event though calling user(current user) have all the neccesary proviliges like "alter any trigger"

Solution:

I hav added  "AUTHID CURRENT_USER" clause in the procedure then it started working as below - 

Just as example-

CREATE OR REPLACE PROCEDURE test
   AUTHID CURRENT_USER  ----set Invoker Rights not
definer rights
is

begin

EXECUTE IMMEDIATE 'alter trigger tri_name disable'; 
.......
........
EXECUTE IMMEDIATE 'alter trigger tri_name enable'; 

 end ; 


  For Details

Alter Trigger fails with ORA-01031: insufficient privileges in Oracle

A small thing but if you don't know it might make you confused and need to spend lots of times.

 Today, we are trying to disable a trigger which is own by a differen user but we are keep geting error "ORA-01031: insufficient privileges" even though we are trying with SYS user.  

The Trigger was a "after logon on database"  trigger and database version 19c

Cause:   

Oracle enforced administer database trigger for both Current_User and Trigger Owner (provably from oracle database 12.2 onwards) .

 

Solution:  

Need to provide a direct grant of "administer database trigger" for the trigger owner and may be also for current user.     

In our case below example worked. 

example- 

Trigger owner:  SCOTT     

Then scott needs have below privilage directly (even from a ROLE which has this privilege won't work, need to provide directly)

SQL> grant administer database trigger to SCOTT ;  


 

 

 

 

 

Monday, March 8, 2021

Is APEX installed by default in all latest Oracle Databases.

Just for clearification- I was under impression that, Apex is installed in all latest oracle versions but that's not true.

 Till oracle database 12.1.0.2, Apex schemas were installed by default. But from version 12.2.0.1 onwards they are not installed by default (that's a good idea) but it come with following supplied versions-

 
With Database 12.2.0.1 is Application Express 5.0.4. APEX is not installed by default.
With Database 18.0.0.0 is Application Express 5.1.3. APEX is not installed by default.
With Database 19.0.0.0 is Application Express 18.2. APEX is not installed by default.
With Database 21c is Application Express 20.1. APEX is not installed by default.