Oracle Ports Scanner (Java Version)

Today I like post how to implement a very simple scanner for oracle ports, for this task we use the tnsping utility and automate it by use multiple iterations over a range of ports. In this example we going to use java but you can use a lots of languages or shells.

First of all, let me to define a core operation like a simple tnsping agaisnt a port, and with the next format;

 
tnsping (ADDRESS=(PROTOCOL=tcp)(HOST=jupiter.domain.com)(port=1523))

Okay, we have the core operation, now we need execute it 1,2,3 … and so on. This task is performed with a loop. Look below, this java code has 3 input parameters, machine, low port and upper port.

 

 
import java.io.*;

public class ListenerScanner {

  public static void main(String argv[]) {

    String s             = null;
    String Maquina       = argv [0];
    int LimiteInferior   = Integer.parseInt (argv [1]);
    int LimiteSuperior   = Integer.parseInt (argv [2]);
    String BinaryPath    = "/app/oracle/database/finalcial/bin/tnsping";

    try {

         System.out.println("Starting scan from port " + argv [1] + " to port " + argv [2]);

         /*** We'll try from lower port to upper port ***/    
         for (int i = LimiteInferior; i <= LimiteSuperior; i++) {

          Process myprocess = Runtime.getRuntime().exec (BinaryPath + " (ADDRESS=(PROTOCOL=tcp)(HOST=" + Maquina + ")(port=" + Integer.toString(i) + "))" );

           BufferedReader stdInput = new BufferedReader(new
                   InputStreamReader(myprocess.getInputStream()));     

           /*** Read the output command  ***/
           while ((s = stdInput.readLine()) != null) {
                if (s.indexOf("OK") != -1) {  /*** if OK substring is include into the string we have a hit ***/
                  System.out.println(" Reply OK for port number:   " + Integer.toString(i));
                }
           } /*** End of while ***/

         } /*** End of for ***/

         System.out.println("Terminated "); 

    } catch (Exception e) {     /*** stack trace if error ***/
         e.printStackTrace();
    }
  }

}

For compile it execute;

 
javac ListenerScanner.java

And execute in this way;

 
$ java ListenerScanner thor-1 1500 1600
Starting scan from port 1500 to port 1600
 Reply OK for port number:   1521
 Reply OK for port number:   1581
 Reply OK for port number:   1582
 Reply OK for port number:   1585
 Reply OK for port number:   1586
 Reply OK for port number:   1587
 Reply OK for port number:   1588
 Reply OK for port number:   1589
 Reply OK for port number:   1592
 Reply OK for port number:   1593
 Reply OK for port number:   1594
Terminated

HTH – Antonio NAVARRO

 

User SYS Is Immune To The Complexity Function

Today at work someone ask me about securize sys’ password by using the complexity function, but it didn’t work. First of all I have tell I need to review the documentation, this is a 12c version and new features can apply. Reading the doc I could see there are three three password verification functions;

verify_function_11G

  • Is the same function from 11g version.
  • The password is not the same as the user name.
  • The password is not too simple.
  • The password includes at least 1 numeric and 1 alphabetic character.
  • The password differs from the previous password by at least 3 characters.
  • The password contains no fewer than 8 characters

ora12c_verify_function

  • The password contains no fewer than 8 characters and includes at least 1 numeric and 1 alphabetic character.
  • The password is not the same as the user name or the user name reversed.
  • The password is not the same as the server name.
  • The password does not contain the word oracle.
  • The password differs from the previous password by at least 3 characters.
  • The password contains at least one special character.

ora12c_strong_verify_function

  • The password must contain at least two upper case characters, two lower casecharacters, two numeric characters, and two special characters. These special characters are as follows: ‘ ~ ! @ # $ % ^ & * ( ) _ – + = { } [ ] \ / < > , . ; ? ‘ : | (space) 
  • The password must differ from the previous password by at least four characters.
  • The password contains no fewer than 9 characters.

Please, for more information refer to Oracle doc at;

https://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG3225

The issue is that these functions not affect to user SYS. This user is exent of this security policy.

HTH – Antonio NAVARRO

What Is APPROX_COUNT_DISTINCT Function

Today someone ask me about approx_count_distinct function, I haven’t work with this function but I heard from it. Reading the doc it says that estimate the number of differents values based on statistics, I think that statistics must to be accuraty for a good estimate. The other way and exact is ‘select count (distinct (colname)) from …’. A simple example for a simple table created as select from dba_objects views is like show below;

 

 
database_12c> create table anr123 as select * from dba_objects ;

Tabla creada.

REM
REM get the execution plan
REM 
database_12c> explain plan for
  2  select approx_count_distinct (object_type) from anr123;

Explicado.

REM
REM Take a look at the execution plan
REM 
database_12c> sta ver_plan

Plan de Ejecución
__________________________________________________________
Plan hash value: 2137789089

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   0 | SELECT STATEMENT                  |         |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

REM
REM get the execution plan
REM 
database_12c> explain plan for
  2  select count (distinct (object_type)) from anr123;

Explicado.

REM
REM Take a look at the execution plan, it is the same
REM 
database_12c> sta ver_plan

Plan de Ejecución
__________________________________________________________
Plan hash value: 2137789089

-----------------------------------------------------
| Id  | Operation                         | Name    |
-----------------------------------------------------
|   0 | SELECT STATEMENT                  |         |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |
-----------------------------------------------------

REM
REM Executing with approx_count_disctinct function
REM
database_12c> select approx_count_distinct (object_type) from anr123;

APPROX_COUNT_DISTINCT(OBJECT_TYPE)
__________________________________
                                42

REM
REM Executing with ISO 9075/ANSI SQL compliance 
REM  
database_12c> select count (distinct (object_type)) from anr123;

COUNT(DISTINCT(OBJECT_TYPE))
____________________________
                          42

In this case not differences found, of course it a very little table, miles of rows only. Now do you want a estimate value of differents value or usally you want the exact value?

HTH – Antonio NAVARRO

 

How Know Elapsed Times For Statistics

From a long time ago many of us, maybe all people, in our scripts include routines or code to take time executions for gather of statistics, but it is no really necessary becouse of DBA_OPTSTAT_OPERATIONS view, it exits from release 11g. This view give us a timestamp of start and finish of the stat operations.

Let me show the next example.

 
COLUMN OPERATION FORMAT A30
COLUMN TARGET FORMAT A50

REM
REM Next query return all rows from DBA_OPTSTAT_OPERATIONS
REM 
SELECT 
  OPERATION, 
  TARGET, 
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
ORDER BY 3;

REM
REM Next query return all times for global database stats
REM 
SELECT  
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
WHERE OPERATION ='gather_database_stats(auto)'
ORDER BY 1;

REM
REM Next query return all times for a table
REM Remark: Target concat schema and object name
REM 
SELECT  
  OPERATION, 
  TARGET, 
  TRUNC (START_TIME) AS "STARTED DAY", 
  TO_CHAR (END_TIME - START_TIME, 'HH24:MI:SS') AS "ELAPSED TIME"
FROM DBA_OPTSTAT_OPERATIONS 
WHERE TARGET ='REPORTS.TRACK_NAV_WEB'
ORDER BY 3;

Advantages;

– It is automatic.
– It is storage in a dba view, many user/process/third apps can use it.
– It make simple your personal scripts.

HTH – Antonio NAVARRO

Ora-12988 Error

Today a coworker ask me about an ora-12988 error when he was modifiying a table, look for error;

admin> oerr ora 12988
12988, 00000, “cannot drop column from table owned by SYS”
// *Cause: An attempt was made to drop a column from a system table.
// *Action: This action is not allowed

The error is clearly, of course, it is not a good idea create table or any object in sys schema but many times we need create temporary tables. In this case the problem was;

SQL> alter table copy_of_objects drop column object_id;
alter table unodos drop column object_id
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

Oracle must implemented this error like autodefense of views or tables property of sys schema. The solution was drop the table.

HTH – Antonio NAVARRO

 

Oracle Agent 12c Error (collections_halted by UploadMaxDiskUsedPct)

Today Enterprise Manager Cloud Control 12c has reported a error with a database. When I have executed in the host agent a “emctl status agent” it show the following error;

screenshot_agent_disk_full

In this case is very clear the error or where the problem is. Looking at OS I have seen a disk is full, after to free some space the problem was solved.

Learned lessions;

Monitoring free space into the host.

HTH – Antonio NAVARRO.

Starting Database From Windows Service Doesn’t Work

Today I have a stranger problem at work, starting a database (Release 12) on Windows 2012 R2 didn’t work from service. The solution was start service using oradim;

 
C:\Windows\system32> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 12 10:59:41 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error

It sounds like no service avaliable. I try start the Windows service and connect to database by using sqlplus;

 
C:\Windows\system32> net start oracleservicesfintest
The OracleServicesfintest service is starting.
The OracleServicesfintest service was started successfully.

C:\Windows\system32>
C:\Windows\system32>
C:\Windows\system32>
C:\Windows\system32> sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 12 11:53:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error

It fails again with the same error… I take a look at eventviewer log but not error reported.

Finally I started the service from oradim, it worked fine;

 
C:\Windows\system32>
C:\Windows\system32>

C:\Windows\system32> oradim.exe -startup -sid test12

After that I restart the windows service for twice times more, It’s worked and I can’t found the problem … I think it was on the Operating System but I’m not sure 😦

HTH – Antonio NAVARRO