Laurent Schneider

Itching to start playing with 18c?

Now you can on @oraclelivesql !

Here's a script to get you started: a dynamic CSV-to-columns converter using polymorphic table functionshttps://t.co/UfddLQ2tn5 pic.twitter.com/BxnXeIKoCx

— Chris Saxon (@chrisrsaxon) February 17, 2018

The ingenious solution of Anton Scheffer using Data Cartridge is now beaten in 18c using polymorphic table function

Anthologic post of Anton : forums.oracle.com
Chris magic with Oracle 18c :
livesql.oracle.com

SQL> create table t1(x number primary key);
Table created.
SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

SQL> create table t2 as select * from t1;
Table created.
SQL> desc t2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                                NUMBER

The table T2 has the column X, but not the constraint (primary key / not null).

If you want to do a create table as select but want to keep index / constraints etc, then you rather use datapump

SQL> set autop on
SQL> var job_state varchar2(30)
SQL> declare
  n number;
begin
  n := DBMS_DATAPUMP.open('IMPORT','TABLE','DB01');
  DBMS_DATAPUMP.metadata_filter(n,'NAME_LIST','''T1''');
  DBMS_DATAPUMP.metadata_remap(n,'REMAP_TABLE','T1','T3');
  DBMS_DATAPUMP.start_job(n);
  DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state);
end;
/
PL/SQL procedure successfully completed.
JOB_STATE
--------------------------------------------------
COMPLETED
SQL> desc t3
 Name                    Null?    Type
 ----------------------- -------- ----------------
 X                       NOT NULL NUMBER

The Table T3 is a copy of T1. DB01 is my implicit loopback database link (database name).

Let me today tell you, I now hate FBI for real.

Let’s start with an easy working example

SQL> CREATE TABLE t(x NUMBER PRIMARY KEY)
Table created.
SQL> insert into t(x) values (1)
1 row created.
SQL> insert into t(x) values (2)
1 row created.
SQL> insert into t(x) values (3)
1 row created.
SQL> commit
Commit complete.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN x * x;
  END;
Function created.
SQL> CREATE INDEX i
   ON t (f (x))
Index created.
SQL> select * from t where f(x)=4

         X
----------
         2
Execution Plan
------------------------------------------------
 0  SELECT STATEMENT 
 1 0  TABLE ACCESS BY INDEX ROWID BATCHED T
 2 1    INDEX RANGE SCAN I

Okay, this is a nice-working example. I can use where f(x)=4.

A non-fbi code would be something like

SQL> create or replace type tn as table of number;
Type created.
SQL> create or replace function f2(y number) 
  return tn deterministic is 
  begin
  if (y<0) then return null; end if;
  return tn (sqrt(y), -sqrt(y));
  end;
Function created.
SQL> select * from t where x  member of f2(4)

         X
----------
         2

Execution Plan
------------------------------------
 0   SELECT STATEMENT
 1  0  INDEX FULL SCAN SYS_C0026437

The reverse function is somehow more challenging to code, but the benefit is enormous, I have no more fbi.

What’s wrong with fbi?

First example : I recreate my function:

SQL> DROP FUNCTION f
Function dropped.
SQL> CREATE FUNCTION f (x NUMBER)
  RETURN NUMBER DETERMINISTIC IS
  BEGIN
    RETURN power(x,2);
  END;
Function created.
SQL> select * from t where f(x)=4
*
Error at line 0
ORA-30554: function-based index I is disabled
SQL> SELECT object_type, object_name, status
  FROM user_objects
 WHERE object_name IN ('F','I')

OBJECT_TYPE             OBJECT_NAME  STATUS 
----------------------- ------------ -------
INDEX                   I            VALID  
FUNCTION                F            VALID  
SQL> SELECT index_name,
       table_name,
       index_type,
       status,
       funcidx_status
  FROM user_indexes
 WHERE index_name = 'I'

INDEX TABLE INDEX_TYPE     STATUS FUNCIDX_STATUS
----- ----- -------------- ------ --------------
I         T FUNCTION-BASED VALID  DISABLED      

Remember this error. ORA-30554. And this not-so-well-known column, USER_INDEXES.FUNCIDX_STATUS. The behavior is pretty agressive, every object is valid, but you can no longer select from the table.

A small parenthese. We all know about unusable indexes. Index often get unusable due to partition maintenance and the like.

SQL> create table t2(x number)
Table created.
SQL> insert into t2 values (1)
1 row created.
SQL> create index i2 on t2(x) unusable
Index created.
SQL> SELECT index_name,
       table_name,
       status
  FROM user_indexes
 WHERE index_name = 'I2'

INDEX TABLE STATUS
----- ----- ---------
I2    T2    UNUSABLE 
SQL> insert into t2 values (2)
1 row created.
SQL> select * from t2 where x=2

         X
----------
         2

Execution Plan
---------------------------------
   0       SELECT STATEMENT
   1    0    TABLE ACCESS FULL T2

The index is not unused, but it prevents neither INSERT nor SELECT.

Let’s add a constraint

SQL> alter index i2 rebuild
Index altered.
SQL> alter table t2 add primary key (x)
Table altered.
SQL> alter index i2 unusable
Index altered.
SQL> insert into t2 values (2)
ORA-01502: index 'I2' or partition of such index is in unusable state
SQL> select * from t2 where x=2

         X
----------
         2

Okay, frequent readers may wonder why I did DROP FUNCTION and CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION.

Fine, let’s try.

SQL> CREATE or replace FUNCTION f (x NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   RETURN power(x,2);
END;
Function created.
SQL> alter index i rebuild
Index altered.
SQL> alter index i enable
Index altered.
SQL> select x, f(x) from t where f(x)=4

         X       F(X)
---------- ----------
         2          4
SQL> create or replace function f(x number) 
  return number deterministic is  
begin
  return 1;
end;
Function created.
SQL> select x, f(x), f(2) from t where f(x)=4

         X       F(X)       F(2)
---------- ---------- ----------
         2          4          1

Oh my goodness, select returns completly wrong result, but the index is valid and enabled.

There is more than way to solve this

1. rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies

SQL> select name from user_dependencies d 
  where referenced_type = 'FUNCTION' 
  and type = 'INDEX' and 
  (
    select last_ddl_time 
    from user_objects i 
    where i.object_name=d.name
  ) < (
    select last_ddl_time 
    from user_objects f 
    where f.object_name=d.referenced_name
  )
NAME
-----
I                                                                               
SQL> alter index i rebuild
Index altered.
SQL> select x, f(x), f(2) from t where f(x)=4
no rows selected.
SQL> select x, f(x), f(2) from t where f(x)=1

         X       F(X)       F(2)
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1

SQL> select name from user_dependencies d 
  where referenced_type = 'FUNCTION' 
  and type = 'INDEX' and 
  (
    select last_ddl_time 
    from user_objects i 
    where i.object_name=d.name
  ) < (
    select last_ddl_time 
    from user_objects f 
    where f.object_name=d.referenced_name
  )
no rows selected.

2. file an SR and encourage Oracle to test features before making them available
3. stop using FBI immediately

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.

CREATE FUNCTION 
  status (DIRECTORY_NAME VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF (DBMS_LOB.FILEEXISTS(
    BFILENAME (DIRECTORY_NAME, '.')) = 1)
  THEN
    RETURN 'VALID';
  ELSE
    RETURN 'INVALID';
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN SQLERRM;
END;
/

SELECT
  directory_name NAME,
  directory_path PATH,
  status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS   
---- ---- ---------
FOO  /foo INVALID  
TMP  /tmp VALID    
BAK  /u99 VALID    

To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.

CREATE FUNCTION 
  status (DIRECTORY_NAME VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  IF (DBMS_LOB.FILEEXISTS(
    BFILENAME (DIRECTORY_NAME, '.')) = 1)
  THEN
    RETURN 'VALID';
  ELSE
    RETURN 'INVALID';
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN SQLERRM;
END;
/

SELECT
  directory_name NAME,
  directory_path PATH,
  status (directory_name) STATUS
FROM dba_directories;

NAME PATH STATUS   
---- ---- ---------
FOO  /foo INVALID  
TMP  /tmp VALID    
BAK  /u99 VALID    

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba

CREATE FUNCTION dba_status
  (owner VARCHAR2, db_link VARCHAR2)
  RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  status   VARCHAR2 (4000);
BEGIN
  EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
  EXECUTE IMMEDIATE
    'begin :1 := "' || owner 
    ||'".status(''' || db_link || '''); end;'
    USING OUT status;
  EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
  COMMIT;
  RETURN status;
END;
/

SELECT 
  owner, db_link, dba_status (owner, db_link) 
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)                                   
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

followup of check invalid database link
If you need to check db link in another schema, you need to create code that run with that schema.

base on the example from yesterday, here is an extended version for the dba

CREATE FUNCTION dba_status
  (owner VARCHAR2, db_link VARCHAR2)
  RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  status   VARCHAR2 (4000);
BEGIN
  EXECUTE IMMEDIATE
'create or replace function "'
|| owner
|| '".status(db_link varchar2) return varchar2 is '
|| 'x number;'
|| 'begin execute immediate ''select 1 from dual@"''
||DB_LINK||''"'' into x;'
|| 'return ''OK'';'
|| 'exception when others then return sqlerrm;'
|| 'end;';
  EXECUTE IMMEDIATE
    'begin :1 := "' || owner 
    ||'".status(''' || db_link || '''); end;'
    USING OUT status;
  EXECUTE IMMEDIATE 'drop function "' || owner || '".status';
  COMMIT;
  RETURN status;
END;
/

SELECT 
  owner, db_link, dba_status (owner, db_link) 
FROM dba_db_links;
OWNER DB_LINK DBA_STATUS(OWNER,DB_LINK)                                   
----- ------- --------------------------------
SCOTT L3.EXAM OK
SCOTT L2.EXAM ORA-12154: TNS:could not resolve
SCOTT L1.EXAM ORA-01017: invalid username/pass

If one database link is invalid, you cannot select through it

SQL> select * from dual@z;
select * from dual@z
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.

create function status(db_link varchar2) 
return varchar2 is 
  x number;
begin
  execute immediate 'select 1 from dual@"'||
        DB_LINK||
        '"' into x;
  return 'OK';
exception
  when others then return sqlerrm;
end;
/

select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

If one database link is invalid, you cannot select through it

SQL> select * from dual@z;
select * from dual@z
                   *
ERROR at line 1:
ORA-02019: connection description for remote database not found

However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function.

create function status(db_link varchar2) 
return varchar2 is 
  x number;
begin
  execute immediate 'select 1 from dual@"'||
        DB_LINK||
        '"' into x;
  return 'OK';
exception
  when others then return sqlerrm;
end;
/

select db_link, status(db_link) from user_db_links;
DB_LINK STATUS(DB_LINK)
------- --------------------------------------------------
L1.EXAM ORA-01017: invalid username/password; logon denied
L2.EXAM ORA-12154: TNS:could not resolve the connect ident
L3.EXAM OK

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}}

— laurentsch (@laurentsch) September 19, 2017

As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target

$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.

$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout

emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, powershell.

$p=new XML::Parser;$p->setHandlers(Start=>&b);$p->parsefile("-");sub b{($i,$e,%a)=@_;if($e eq"Target"){print$a{"NAME"}.":".$a{"TYPE"}}}

— laurentsch (@laurentsch) September 19, 2017

As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target

$ emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
[srv01.example.com, host]
[srv01.example.com:3872, oracle_emd]
[agent13c2_2_srv01.example.com_3830, oracle_home]
[DB01.example.com, oracle_database]
[OraDB12Home1_14_srv01.example.com_743, oracle_home]
[DB01_srv01.example.com_CDBROOT, oracle_pdb]
[DB01_srv01.example.com_PDB01, oracle_pdb]
[LISTENER001_srv01.example.com, oracle_listener]

Not really easy to parse, and it does not contain all information. Let’s imagine I want to get the TARGET_NAME out of my SID? hard…

What is actually emctl doing ? It is parsing the targets.xml with perl. Oracle wrote a module, called ias::simpleXPath, that helps parsing the file.

$AGENT_HOME/perl/bin/perl -l -I$AGENT_HOME/sysman/admin/scripts -Mias::simpleXPath -e '
  foreach $t(
    simpleXPathQueryForNodes(
      "targets.xml","Targets/Target")){
    print 
      "[".($t->{"attributes"}->{"NAME"}).
      ", ".($t->{"attributes"}->{"TYPE"}).
      "]"}'

ias::simpleXPath is a wrapper for XML::Parser. XML::Parser is a supported perl that is included in the agent home. So no need to install your own perl modules for this purpose!

back to by example, if I want to get the target name for my SID DB01

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "SID" ) {
      $sid=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $sid eq lc "DB01"
      ) {
        print $tn . ":" . $tt;
      }
      $sid="";
    }
  }
'
DB01.example.com:oracle_database

This could be useful, for instance if you want to start a blackout

emctl start blackout db01_black DB01.example.com:oracle_database

For listener, you could retrieve the LsnrName for your listener LISTENER001

$AGENT_HOME/perl/bin/perl -l -MXML::Parser -e '
  $xmlfile = "targets.xml";
  die "Cannot find file $xmlfile"
    unless -f $xmlfile;
  $parser = new XML::Parser;
  $parser->setHandlers(
    Start => \&startElement,
    End => \&endElement);
  $parser->parsefile($xmlfile);
  sub startElement {
    ( $parseinst, $element, %attrs ) = @_;
    if ($element eq "Target") {
      $tn=$attrs{"NAME"};
      $tt=$attrs{"TYPE"};
    }
    if ($element eq "Property" &&
        $attrs{"NAME"} eq "LsnrName" ) {
      $lsn=$attrs{"VALUE"};
    }
  }
  sub endElement {
    ( $parseinst, $element ) = @_;
    if ($element eq "Target"){
      if (
        lc $lsn eq lc "LISTENER001"
      ) {
        print $tn . ":" . $tt;
      }
      $lsn="";
    }
  }
'
LISTENER001_srv01.example.com:oracle_listener

Which you could also blackout before rebooting.

The parser is not limited to Entreprise Manager targets, you could use it for oraInventory/ContentsXML/inventory.xml or whatever files.

There are plenty of other mean to read xml, from the database, xmllint, powershell.

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with

sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(service_name=DB01.example.com)))"

But one of your application has hardcoded SID=DB01

sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of 
    service requested in connect descriptor

How do you do the trick?

• Remove the domain name in the pluggable

alter session set container=DB01;
alter system set db_domain='' 
  container=current 
  scope=spfile;

• You use the listener parameter USE_SID_AS_SERVICE

listener.ora


USE_SID_AS_SERVICE_LISTENER = on

You restart the listener and the database.


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

You just connected with SID to a pluggable database

I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.

You create a container CDB01 and a pluggable DB01, you can connect with

sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(service_name=DB01.example.com)))"

But one of your application has hardcoded SID=DB01

sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
ORA-12514: TNS:listener does not currently know of 
    service requested in connect descriptor

How do you do the trick?

• Remove the domain name in the pluggable

alter session set container=DB01;
alter system set db_domain='' 
  container=current 
  scope=spfile;

• You use the listener parameter USE_SID_AS_SERVICE

listener.ora


USE_SID_AS_SERVICE_LISTENER = on

You restart the listener and the database.


sqlplus "scott/tiger@
  (description=(address=
    (host=srv01)(port=1521)(protocol=tcp))
    (connect_data=(sid=DB01)))"
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

You just connected with SID to a pluggable database

Long time ago, Maxime Yuen registered 1521 for nCube License Manager.

By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one came first, Oracle using 1521 or Larry investing in nCube, but I am pretty sure it’s related

$ curl https://www.iana.org/assignments/
  service-names-port-numbers/service-names
  -port-numbers.txt | grep 1521
ncube-lm 1521 tcp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
ncube-lm 1521 udp nCube License Manager 
  [Maxine_Yuen] [Maxine_Yuen]
$ grep -w 1521 /etc/services
ncube-lm 1521/tcp # nCube License Manager
ncube-lm 1521/udp # nCube License Manager
$ netstat -a | grep ncube
tcp 0 0 *.ncube-lm *.* LISTEN

Still, as of Oracle 12c Release 2, port 1521 is recommended.

Now, another question : do you really want to use port 1521?

On the one hand, it could be good for a hacker to know listener runs on 1521 and ssh on port 22. This is configurable of course.

On the other hand, you better use that is assigned to Oracle. RFC 6335 defines 1024-49151 as User Ports, and 49152-65535 as the Dynamic and/or Private
Ports (aka ephemeral). Remember, if a port is used before you start your listener, your listener won’t start.

Remember every network connection keeps a port busy. So if you start a network client from your database server to another server, ssh, sqlnet, mail, whatever, dns, then your port 1028 or 57313 may be busy for a client connection. Which will prevent your listener from starting. If you use port 9999, you could look on IANA and ask the owner if he plans anything on that port.

Very often, most ports are unused when you start the listener. If you find an unused port in the private range, 49152-65535, you may name it in /etc/services.

Very often I see database servers with more than one listener. Obviously, you cannot run more than one listener on port 1521. There are some case where you want different listener with different sqlnet.ora or different Oracle version. But this render consolidation (e.g. Multitenant) more painful.

The discussion on which port to use is obviously far beyond Oracle. There are gazillions of TCP/UDP servers running in the digital world and less than 65535 ports. For sure you cannot have all them on IANA.ORG, right?

In most cases, stick to Oracle recommendation, use port 1521.

In the old days, locking sys had not much effect.

SQL> alter user sys identified by *** account lock;
User altered.
SQL> select account_status 
  from dba_users 
  where username='SYS';
ACCOUNT_STATUS
--------------------------------
LOCKED
SQL> conn / as sysdba
Connected.
SQL> conn sys/** as sysdba
Connected.
SQL> conn sys/***@db01 as sysdba
Connected.

Well, in the very-old days, Oracle7, or with the in 12cR2-deprecated parameter O7_DICTIONARY_ACCESSIBILITY, SYS could be locked. But this is out of the scope of this post.

In 12cR2, it is now possible to lock SYS.

SQL> alter user sys 
  identified by *** 
  account lock;
User altered.
SQL> select account_status 
  from dba_users 
  where username='SYS';
ACCOUNT_STATUS
--------------------------------
LOCKED
SQL> conn / as sysdba
Connected.
SQL> conn sys/** as sysdba
Connected.
SQL> conn sys/***@db01 as sysdba
ERROR:
ORA-28000: the account is locked

I like it Oracle recommends you create other users to perform DBA tasks.

SQL> grant dba, sysdba 
  to user0001 
  identified by ***;
Grant succeeded.

Still, probably intentionally left so or simply forgotten, Oracle recommends to lock all Oracle supplied accounts except for SYS and SYSTEM (ref: Changing Passwords for Oracle Supplied Accounts)

Also note, you’ll get an ORA-40365 if you use an old-style password file

SQL> alter user sys identified by *** account lock;
alter user sys identified by *** account lock
*
ERROR at line 1:
ORA-40365: The SYS user cannot be locked 
  while the password file is in its current format.

If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.

I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally

sqlplus / as sysdba
SQL> select cdb from v$database;
CDB
---
YES
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
CDB$ROOT

SQL> select con_id, NAME from V$CONTAINERS

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 ST01

Then you’ll soon realise, you can no longer do what you used to do

SQL> create user u identified by u;
create user u identified by u
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Some scripts still run in the root container. SHUTDOWN ABORT, ALTER SYSTEM SWITCH LOGFILE. Doing a full backup or clone probably won’t hurt you much. Relatively..

But now let’s imagine I have a very simple and very old script to lock scott

vintage.sh:
  echo "alter user scott account lock;"|
    sqlplus -s / as sysdba

I could do this in the container

SQL> alter session set container=ST01;
  Session altered.
SQL> alter user scott account lock;
  User altered.

So fine, so good. NOW : how do I make this work without changing the script ?

Remember, non-cdb database, as they were used in Oracle 11, 10, 9, 8, 7 … are now deprecated. Remember, cdb is recommended. Now face it : it’ll break your dba scripts.

As a production dba, I don’t want to rewrite all the existing scripts. Some are ultra-old and used by people who did not write them.

One method for my script would be to change the container in a login script.

echo "alter session set container=ST01;" > /tmp/login.sql
export ORACLE_PATH=/tmp
vintage.sh
  Session altered.
  User altered.  

However, if my script must work with both CDB and non-CDB, I need to set the container in only this case.

In my login.sql, I first tried to implement some plsql logic, but alter session set container is not working (aka working with limitation) with execute immediate.

As well, I don’t want my script to break Oracle 11.

So I decide to do some sqlplus magic with defined variable.

set ver off feed off
-- 1) check if the column v$database.cdb exists
col column_name new_v cdb nopri
def cdb=null
select column_name from dba_tab_columns 
where owner='SYS' and 
table_name='V_$DATABASE' and column_name='CDB';
-- 2) if cdb is YES, then select a dynamic statement using V$PDB 
col pdb new_v pdb nopri
def pdb="null stmt from dual where 1=0"
select 
'''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED'''
  pdb 
from v$database 
where &cdb='YES';
-- 3) get a dynamic alter session statement. I use a dummy flagger for non-cdb
col stmt new_val stmt nopri
def stmt="SET FLAGGER=OFF"
select &pdb;
-- 4) alter session
alter session &stmt;
set feed 6
col column_name clear
col stmt clear
col pdb clear
undef cdb
undef stmt
undef pdb
del

Now I run my script

11g: ./vintage.sh
User altered.

12c-non-cdb: ./vintage.sh
User altered.

12cR2-single-tenant: ./vintage.sh
User altered.

DISCLAIMER: you shouldn’t use a global login.sql and you should know that secretly fixing old scripts may have side effects. Test, test and retest your code
DISCLAIMER 2: my frequent readers surely wonder if this statement generating a statement generating a statement is for an obfuscation contest

If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.

I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally

sqlplus / as sysdba
SQL> select cdb from v$database;
CDB
---
YES
SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
CDB$ROOT

SQL> select con_id, NAME from V$CONTAINERS

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 ST01

Then you’ll soon realise, you can no longer do what you used to do

SQL> create user u identified by u;
create user u identified by u
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Some scripts still run in the root container. SHUTDOWN ABORT, ALTER SYSTEM SWITCH LOGFILE. Doing a full backup or clone probably won’t hurt you much. Relatively..

But now let’s imagine I have a very simple and very old script to lock scott

vintage.sh:
  echo "alter user scott account lock;"|
    sqlplus -s / as sysdba

I could do this in the container

SQL> alter session set container=ST01;
  Session altered.
SQL> alter user scott account lock;
  User altered.

So fine, so good. NOW : how do I make this work without changing the script ?

Remember, non-cdb database, as they were used in Oracle 11, 10, 9, 8, 7 … are now deprecated. Remember, cdb is recommended. Now face it : it’ll break your dba scripts.

As a production dba, I don’t want to rewrite all the existing scripts. Some are ultra-old and used by people who did not write them.

One method for my script would be to change the container in a login script.

echo "alter session set container=ST01;" > /tmp/login.sql
export ORACLE_PATH=/tmp
vintage.sh
  Session altered.
  User altered.  

However, if my script must work with both CDB and non-CDB, I need to set the container in only this case.

In my login.sql, I first tried to implement some plsql logic, but alter session set container is not working (aka working with limitation) with execute immediate.

As well, I don’t want my script to break Oracle 11.

So I decide to do some sqlplus magic with defined variable.

set ver off feed off
-- 1) check if the column v$database.cdb exists
col column_name new_v cdb nopri
def cdb=null
select column_name from dba_tab_columns 
where owner='SYS' and 
table_name='V_$DATABASE' and column_name='CDB';
-- 2) if cdb is YES, then select a dynamic statement using V$PDB 
col pdb new_v pdb nopri
def pdb="null stmt from dual where 1=0"
select 
'''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED'''
  pdb 
from v$database 
where &cdb='YES';
-- 3) get a dynamic alter session statement. I use a dummy flagger for non-cdb
col stmt new_val stmt nopri
def stmt="SET FLAGGER=OFF"
select &pdb;
-- 4) alter session
alter session &stmt;
set feed 6
col column_name clear
col stmt clear
col pdb clear
undef cdb
undef stmt
undef pdb
del

Now I run my script

11g: ./vintage.sh
User altered.

12c-non-cdb: ./vintage.sh
User altered.

12cR2-single-tenant: ./vintage.sh
User altered.

DISCLAIMER: you shouldn’t use a global login.sql and you should know that secretly fixing old scripts may have side effects. Test, test and retest your code
DISCLAIMER 2: my frequent readers surely wonder if this statement generating a statement generating a statement is for an obfuscation contest

The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.

What’s happening then with my ultra-old-accounts?

You could well set a new password (or the same password again) to each account to be migrated in 11g/12cR1 before moving to 12cR2.

If nobody knows the password and nobody can change it because it is hardcoded in the application and neither easy to read (hidden / obfuscated /encrypted) nor to change, then, you are in TROUBLE ! This is documented in Note 2075401.1

First disclaimer : it is a good thing to achieve a better security. SHA1 and SHA2 are a lot better than the oldstyle-longly-hacked-unsalted-case-insensitive-homemade-algorythm. SHA3 has been published in 2015 and it not used in Oracle 12cR2 yet. SHA2 is a bit older (2001) but still recommended. SHA1 is oldish (1995) and no-longer-recommended, collision has been detected. Read more on wikipedia or crypto101

SHA-1 was a really huge improvement when introduced in 11gR1. The old self-made algorythm has been a torture for Oracle Security team. It has been published on Internet. Extremly powerfull password cracker can find your “not-too-long” password in notime. In 11g, Oracle removed the 10g String from the DBA_USERS view. I wrote about this here. It remained on the base table, USER$ until 12cR2. Now Oracle completly removed it by default in 12cR2. 10 years after SHA1 was introduced in 11gR1.

Still. You are the dba. You want to migrate your database not to chase passwords.

You could edit your sqlnet.ora to allow 10g strings.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

This works

SQL> sho parameter sec_case_sensitive_logon
NAME                      VALUE
------------------------- -----
sec_case_sensitive_logon  FALSE

SQL> CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B';
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> conn u/abcdefg@pdb01
Connected.
SQL> conn u/AbCdEfG@pdb01
Connected.

If you have SQLNET.ALLOWED_LOGON_VERSION_SERVER=11, then you could keep the same setting of sec_case_sensitive_logon as in 11g. I recommmend the default (true).

But, that’s it ? Wellllllll… not sure.

In 12.1

 
SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
----------------------------------
   CREATE USER "U" IDENTIFIED BY 
     VALUES 'DC6F2B33D359A95B'

Let’s try in 12.2

SQL> select dbms_metadata.get_ddl('USER','U') from dual
DBMS_METADATA.GET_DDL('USER','U')
------------------------------------
CREATE USER "U" IDENTIFIED BY VALUES 
  'S:0000000000000000000000000000000
00000000000000000000000000000'

While this is a perfectly working syntax, and IDENTIFIED BY VALUES is not supported *. So if create that user, then, obviously, the 10G string is lost.

Well, unsupported feature then? Hmm, yes. You should never have used identified by values.

Ok, so if I refresh my Test database with Production data, how can I save test passwords? You can’t. At least not in a supported way by using identified by values.

You could something like :

SQL> select 'alter user "'||name||
       '" identified by values '''||
       password||''';' txt
     from user$, v$instance 
     where version > '12.2' 
     and spare4 is null  
     and type# = 1
     and regexp_like(PASSWORD,'[A-F0-9]{16}');

txt
-----------------------------------
alter user "U" identified by values
   'DC6F2B33D359A95B';

This may work. In 12.2.0.1. Maybe not in 13. Maybe not in 12.2.0.1.0PSU July. It’s not supported. If it does not work, it is NOT-A-BUG.

The SHA1 was introduced 10 years ago in Oracle 11gR1. If you have not changed your password in ten years, and you don’t know how many employees and ex-employees know this password, and it is case-insensitive, and its “pseudo-hashing-algorythm” has been hacked for maybe two decades, yet, I can only warmly recommend to change those accounts passwords !

Again: so if I refresh my Test database with Production data, how can I save test passwords ?
If I were you I would design a better system for login. For human users, use global users and an Identity solution, like Oracle Universal directory. For technical account, build yourself a tool that generate a random password, and update the user and credentials, something like


select
substr(
REGEXP_REPLACE(
UTL_RAW.cast_to_varchar2(
SYS.DBMS_CRYPTO.RANDOMBYTES (1024)
) ,'[^!#-~]'
),
1,
20
) PW
from dual;
PW
--------------------
%K0w(^%UN.B82Yjjfu{?