Node 1 Does Not Support Connections (RAC)

This morning I have been reported a problem in a test environment. Some connections were hung when trying to enter. After connecting and taking a look, the environment, it is a cluster of two nodes version 12c, note that node one is the one that does not support connections and the connections were left is waiting for a change of log. It’s funny but it did not give any error, it’s usually this situations shows a ora-00257, but this is not the case, maybe because of the engine version.

Checking the situation at the instance level;

 
SQL> select inst_id, version, status, thread#, archiver, log_switch_wait, logins from gv$instance

   INST_ID VERSION           STATUS          THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS
---------- ----------------- ------------ ---------- ------- --------------- ----------
         2 12.1.0.2.0        OPEN                  2 STARTED                 ALLOWED
         1 12.1.0.2.0        OPEN                  1 STARTED ARCHIVE LOG     ALLOWED

 
Sessions are waiting on event “log file switch (archiving needed)” like show below, first column is node (in this case the problem is in node 1);

 
N     SID TIME    SQL_ID            EXECS EVENT                              Marker
- ------- ------- -------------- -------- ---------------------------------- --------
1      22 0       6vm7g6qj4mqhd        0  PX Deq: Execution Msg
2     327 0       6vm7g6qj4mqhd        4  PX Deq: Execution Msg
2     649 0       6vm7g6qj4mqhd        4  PX Deq: Execute Reply
1     361 1471    6228pzdt28kzd     1008  log file switch (archiving needed)      <<<
2     121 106     61tssjb6hj8x7      329  gc buffer busy acquire
1      88 1645    9zg9qd9bm4spu     7787  log file switch (archiving needed)      <<<
1     804 1704    3mptsg6h27zg9        1  log file switch (archiving needed)      <<<
1      89 1705    0bfdn75zn75pw        2  log file switch (archiving needed)      <<<
2      20 1699    6nauzjpthp1w7        3  db file sequential read
1       3 +1H     1aa2fpqtx557g     3877  log file switch (archiving needed)      <<<
1     328 1705    g8bkp70myp46t        2  log file switch (archiving needed)      <<<
1     257 +1H     dr6d1upgkc1g3        1  log file switch (archiving needed)      <<<
2     615 505     aq8yqxyyb40nn     1255  gc current request
1     800 817     aq8yqxyyb40nn     1282  buffer busy waits
1     480 817     aq8yqxyyb40nn     1282  buffer busy waits
2     342 +1H     5ms6rbzdnq16t    15644  gc buffer busy acquire
1     377 817     aq8yqxyyb40nn     1282  buffer busy waits
1     345 817     aq8yqxyyb40nn     1282  log file switch (archiving needed)      <<<
1      21 817     aq8yqxyyb40nn     1282  buffer busy waits

 
After perform archive backup (with delete option);

 
SQL> select inst_id, version, status, thread#, archiver, log_switch_wait, logins from gv$instance

   INST_ID VERSION           STATUS          THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS
---------- ----------------- ------------ ---------- ------- --------------- ----------
         2 12.1.0.2.0        OPEN                  2 STARTED                 ALLOWED
         1 12.1.0.2.0        OPEN                  1 STARTED                 ALLOWED

 
HTH – Antonio NAVARRO

 

Advertisements

ORA-00245 Error

Today when I was executing a archive backup after of full refresh (by OGG) I get the next error;

 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 04/10/2019 09:26:58
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 04/10/2019 09:26:58
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

The same is generated in the first sentence of the RMAN script (resyn catalog). The problem is that snap CF backup is in a DG, which is not permited. It must be out of DG. In this case ACFS or local disk (ZFS) is possible.

Like first action I performed a specified backup like show below;

 

SQL> alter database backup controlfile to ‘/prod/backup/CONTROLFILE/snapcf_nube.f’ reuse;

 
The second action is make this change permanent. From RMAN you can use;

 

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

CONFIGURE SNAPSHOT CONTROLFILE NAME clear;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/prod/backup/CONTROLFILE/snapcf_nube.f’;

 
HTH – Antonio NAVARRO

Error ORA-01924

Recently I have been migrating a database from version 10g to 12c. In the functionality testing phase the error ORA-01924 arose. This occurs (in version 12.1) when the 600 roles assigned to a user are exceeded, Oracle has a specific oneoff collected in bug 18934948. But after applying this oneoff, the problem. After doing several tests we saw that it was a problem of lack of privileges. The user who made the mistake He lacked the ability to interact with roles.

In version 10g I did not have any problem with the level of privileges assigned, but in version 12c I needed to add two new permits. It may seem strange the difference of operation between versions, but it really makes sense if you look at the model of continuity. Oracle in the last has accelerated this part of the databases, in general it has been a leader in many aspects, but here has always been one step behind other  rdbms such as SQL Server.

In this case giving these two privileges to the user solved the problem;

  • drop role
  • grant any role

HTH – Antonio NAVARRO.

 

How To Change ASMSNMP Password (12c and 18c)

Today I was configuring a cluster in the Cloud Control and in one of the steps is to configure access to the ASM part. For This part is used by the asmsnmp user, which is the one that must be configured. As in this case, I did not know the password of this user opted to change it.

To make this password change we have two options;

# 1 Connecting to the asm instance and executing an alter user

 
$ sqlplus / as sysasm
Alter user asmsnmp identified by xxxxxxx;

#2 From asmcmd command with orapwusr

 
ASMCMD> orapwusr --modify asmsnmp
Enter password: *********
ASMCMD>

HTH – Antonio NAVARRO

 

Crawler Network (Pl Sql)

In some free time, every time I have less, I have been doing a small spider (programmed in PL SQL) for once we have access to a database to launch a discovery process on the network in which the machine that hosts the server is located database. This code is thought as a PoC (Proof of Concept).

Basically what I’m going to do is use a few packages that Oracle usually brings by default (it can vary by version). What if I manage to execute them in the database, this by default will give me access to the network where the database server is and make a discovery of machines / servers in that piece of network. It would be similar to using nmap (for example, as an nmap -sn 173.101.0.0/24) but from Oracle itself.

Indicate that it depends on how the network is configured, its security level (use of ACL), etc. They can ban us and we can not see anything.

Picture show the banner for the tool 🙂

crawler_plsql_drei

This small script receives four parameters

# 1 Prev_range: n IPs to try before the IP of the machine where the database is.
# 2 post_range: n IPs to try after the IP of the machine where the database is.

Suppose that the machine where the database is located is in the ip 173.120.0.50

if we execute: crawler_plsql 5 6

the process will try to discover if it exists and alias the IPs;

173.120.0.45
173.120.0.46
173.120.0.47
173.120.0.48
173.120.0.49
173.120.0.50
173.120.0.51
173.120.0.52
173.120.0.53
173.120.0.54
173.120.0.55
173.120.0.56

The next picture show the start and check process, to verify privileges and permissions for the user that we are using (Actual IP has been pixeled);

crawler_plsql_eins

In the next step the crawler show a list with all host that it a discoverd (inside the range used in param #1 and param #2).  The ollowing picture has been pixeled.

crawler_plsql_zwei

#3 Try dblink: This parameter will try to create a dblink to the destination, against port 1521, the idea is to try to identify if there is another Oracle engine, configured by default on port 1521 (the next version could do a vertical scan 1024 – 65535), and if there is to see how far we get.
if we execute: crawler_plsql 5 6 Y

The process will try to create a database link against the IPs. If we use the previous example, try to create a dblink against each of the IPs in the range 173.120.0.45 – 173.120.0.56.
#4 send_mail: Send all the information you have collected and send it by mail, if you are doing something that you should not do not send it to a address that may be associated with you. It would be an exfiltration of data.

if we execute: crawler_plsql 5 6 Y Y

Will send the mail based on the configuration of the section “Settings for email” the parameters to be configured are

CMailIp        -- IP where start mail
CPort          -- Port to start resend, by default 25
CFromName      -- Name (sender)
CFromEmail     -- Boxmail (sender)
CToName        -- Name (receipent)
CToEmail       -- Boxmail (receipent)
CSubject       -- Issue

finally, the link to the code is this.

CRAWLER_PLSQL (github download

 
Any comment is welcome.

HTH – Antonio NAVARRO

New Version Of PUTTY (update fast)

Putty has presented a new version, the 0.71, of this popular, simple and useful open source software, which solves the day to day to many DBAs, Sysadmin, Developers, etc. Having options from telnet (it should no longer be used) to the possibility of making tunnels through making connections in RAW mode.

This version, does not really present great news, if not, that a bit in the Microsoft line is a patch to correct important security vulnerabilities.

Some of the most dangerous vulnerabilities are;

  • DSA signature check bypass (MITM)
  • Integer overflow (Over RFC 4432)
  • Potential Malicious code execution (from help files .chm)
  • Buffer Overflow in Unix PuTTY (over active Unix file descriptors by using poll() system call)
  • DoS if Many Unicode is used

You can download the new version from its official website
Putty 0.71 download

HTH – Antonio NAVARRO

Generating Hash Passwords In Oracle

I have shared in my github a small code fragment, programmed in java, that from a database user and its password generates the hash that Oracle would generate (in versions 11 and below). This is just an example to see how easy it is to get the data. Logically we can play with the username and hash to get the password.

From version 12 onwards, the form and algorithms that Oracle uses to generate the hash (or encrypted password), including the use of cryptographic salt, have changed. The concept and use of cryptographic salt I promise to see more in detail in another post, although I advance you that its main function is to shield the hash in the face of possible attacks by dictionaries.

Please, for see the code follow the next link;

Generate Hash Code (Java implementation)

HTH – Antonio NAVARRO