Oracle Net Configuration Assistant (netca) command line interface (CLI)

Everything is clear about using netca in graphical mode. Understandable questions and "transparent" interface do they job perfectly, although for comprehensive Oracle Net setup the Net Manager is more preferable (imho). But netca CLI has some specificities. 

There are several options of using netca CLI.

To configure listener and naming services via netca response file :

netca -silent -responsefile

The example of such file can be as following (the empty and commented lines was removed) :

[GENERAL]
RESPONSEFILE_VERSION="12.1"
CREATE_TYPE="CUSTOM"
LOG_FILE=""/tmp/netca.log""
[oracle.net.ca]
INSTALLED_COMPONENTS={"javavm","net8","cman","aso","client","server"}
INSTALL_TYPE=""custom""
LISTENER_NUMBER=0
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"EZCONNECT","TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"ASM"}
NSN_SERVICE={"+ASM"}
NSN_PROTOCOLS={"TCP;db-01;1521"}

In the above some fields has 2 double quotes in a row in their values. It's required, else run-time error can occur.

The following lines are required :
[GENERAL]
RESPONSEFILE_VERSION="12.1"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]

The listener only created when one of installed components is "server".

If you set INSTALL_TYPE=""minimal"" or ""typical"", then sqlnet.ora with NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
is created and default listener named LISTENER on 1521 port also is created with appropriate entries in listener.ora file (if it was already there, the file is left as is).

Creation of TNS entries is possible only when INSTALL_TYPE=""custom"".

You can override entries from response file or add some more parameters using command line options. Thus, you can change install_type on the way, listener_name and listener port (only in custom install_type) etc.

netca -h
Usage: netca [-silent] { }

Perform network configuration by specifying the following arguments:
    [-silent]
        -responsefile ]
        [-local {Perform configuration on only local node}]
    -instype
        [-listener ]
        [-lisport ]
        [-lps ]
        [-lpe ]
        [-netnum ]
        [-nostartlsnr {Do not start listener}]
    [-crsupgrade {Upgrade default listener from lower version database home to Grid Infrastructure home (only for RAC)}]
    [-inscomp ]
    [-insprtcl ]
    [-orahome ]
    [-orahnam ]
    [-log ]
    [-h|-help {Print usage}]

As we can see there are no options for tnsnames.ora or sqlnet.ora entries. When install type is custom, tns entries are taken from response file.

You can add tns entries and listeners by running netca with another content of response file or command line options. The entries are added into listener.ora and tnsnames.ora.

Naming methods are updating only in custom install type from the response file.

P.S. When I tried default value for NSN_NAMES={"EXTPROC_CONNECTION_DATA"} in the response file, tns entries were not created. If this name is changed in one char only, tnsnames.ora is populated :)

P.P.S. netca creates appropriate listener configuration in grid infrastructure if it's running on the server.

Examples :

1. Does network service name settings according to response file parameters and add new listener bbb to those already registere, try to set listening port to first free between 1500 and 1505, without starting listener bbb after creating it.

$ netca -silent -responsefile /install/oracle/response/netca5.rsp -instype custom -listener bbb -lps 1500 -lpe 1505 -nostartlsnr

2. Create default listener LISTENER (-instype typical) on default port and start it not accounting it's existence.

$ netca -silent -responsefile /install/oracle/response/netca5.rsp -instype typical -lps 1500 -lpe 1505 -nostartlsnr

How to print and type non-english locale symbols over ssh on Solaris ?

In short, it depends on the locale in both cases. This small note is not about how to configure the locale and terminal settings. There are too many dependent factors : supporting fonts on the source of connection, locale settings on source of connection, settings of the terminal, using unicode etc. Shortly:

1. To display locale symbols on the terminal, you have to set either LANG, or LC_CTYPE, or LC_ALL session variables, corresponding to your locale. You may be also required to use settings of individual program (vi(m)), implementing interim iconv operations etc.

2. To enter/type non-english symbols over ssh, you have to send locale variables from the source to the destination, using setting SendEnv in ssh client config file (SendEnv LC_* LANG for example). Without this I was unable to input non-english characters inside ssh session to Oracle Solaris (sparc) . On Linux systems all worked without it.

3. There could be other methods (echo -e \uHHHH etc.), it highly depends on your needs.

4. Do not forget to enable 'stty defeucw' setting for the terminal session. Without it sqlplus, for example, treat one non-english symbol as number of bytes (2 for russian symbols) during deleting it via backspace.

Good Luck !

How to get current or custom time in different time zone in Oracle DB ?

To get current time in another time zone use the following :

SQL> select systimestamp at time zone 'ZONE_NAME' from dual ;

ZONE_NAME can be gotten from v$timezone_names view.

To get custom time of different timezone in local time zone do the following :

SQL> select to_char ( to_timestamp_tz ('2021-07-22 02:00 pm us/eastern','yyyy-mm-dd hh:mi pm tzr') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') from dual

Thus, here we can see the local time (time in Minsk in my case) when there is 02:00 PM 22.07.2021 at US Eastern time. 

Here also the other samples :

select to_char (from_tz (to_timestamp ('2021-09-08 07:00am','yyyy-mm-dd hh:miam')  ,'America/Los_Angeles') at time zone 'Europe/Minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

select to_char (from_tz (cast (to_date ('2021-09-08 07:00am','yyyy-mm-dd hh:miam') as timestamp), 'america/los_angeles') at time zone 'europe/minsk', 'dd.mm.yyyy hh24:mi:ss') as local_time
from dual
/

Good Luck !

How does Oracle database represent numbers ?

Let consider it on small examples : we take natural number 412 and dump it like the following :


SQL> l
  1  select dump(412,16) dump_result from dual
  2  union all
  3* select dump(-412,16) dump_result from dual
SQL>

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d
Typ=2 Len=4: 3d,61,59,66


Oracle represents its numbers as the form 0.(base_100_mantissa)*(100**(base_100_exponent)).

base_100 here means numbers (unique symbols) from 00 to 99.

In both cases first byte contains the information about sign and exponent, the next are the value of mantissa.

The highest bit of the first byte is sign - 1 for positive and 0 for negative numbers.

The exponent (last 7 bits of the first byte) is in the form (base_100_exponent+64) for positive and (255-((base_100_exponent)+64),highest bit is ignored) for negative numbers.

In case of negative numbers the last byte 0x66 (102) is added. Zero is stored as 1 byte of 0x80 (128).
Positive values of bytes of mantissa is being written as (value+1), negative as (101-value).

1. 412

DUMP_RESULT
------------------------
Typ=2 Len=3: c2,5,d

1.1. c2 is 0b11000010

The highest bit is 1 - number is positive.
The exponent is 0b1000010-0b1000000 (64)=0b10 (2)


1.2. Bytes of base_100_mantissa - 0x5 and 0xd. 0x5 is 4+1, 0xd is 12+1 (i.e. similar to our number 412). But we have base_100_mantissa, so 5 transforms to 05 for Oracle.

So, the number 412 is presented as 0.0513*(100**2)

2. -412

DUMP_RESULT
------------------------
Typ=2 Len=4: 3d,61,59,66

Last 0x66 - another evidence of negative number.

2.1. 3d is 0b111101 (61)

The highest bit is 0 - number is negative.
The exponent=255-61-64=130 --> 0b10000010. The highest bit is ignored, so the exponent is 0b10 (2).

2.2. Bytes of base_100_mantissa - 0x61 (97) and 0x59 (89). 101-4=97, 101-12=89. But we have base_100_mantissa, so 4 transforms to 04 for Oracle.

So, the number -412 is presented as -0.0412*(100**2)

We can see Oracle added some tricks to present its numbers. I guess it can be for comparison, sorting, ordering etc.

Let's check our "transformation" :

SQL> select utl_raw.cast_to_number (hextoraw('c2050d')) "check" from dual
/

  check
----------
       412

SQL> select utl_raw.cast_to_number (hextoraw('3d615966')) "check" from dual
/

     check
----------
      -412


Good Luck !

Oracle RMAN - catalog backup from the sbt_tape device

Sometimes you need simply re-catalog (or something else) the backup piece of RMAN backup made to the tape device. The documentation doesn't direct you to the options mention here, but it works. Try it !

The 'catalog command of Oracle RMAN have option 'device'. Look :

RMAN> catalog ;                                                                                                                                                                             

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "archivelog, backuppiece, backup, controlfilecopy, datafilecopy, db_recovery_file_dest, device, recovery, start"
RMAN-01007: at line 1 column 9 file: standard input

When you're trying to catalog a backuppiece from the tape, the error appears :

RMAN> catalog device type sbt_tape backuppiece 'arc_db_68u7hhgd_1_1' ;

RMAN-06470: DEVICE TYPE is supported only when automatic channels are used

Configure automatic channel with parameters you're using and you will get a success :

RMAN> configure channel device type sbt_tape parms 'SBT_LIBRARY=/u01/app/oracle/hpe/HPE-Catalyst-RMAN-Plugin/bin/libisvsupport_rman.so ENV=(CONFIG_FILE=/u01/app/oracle/hpe/HPE-Catalyst-RMAN-Plugin/config/plugin_db.conf)' ;

RMAN> catalog device type sbt_tape backuppiece 'full_db_sb_9cu82od1_1_1' ;                                                                                                              
                                                                                                                                                                                            
allocated channel: ORA_SBT_TAPE_1                                                                                                                                                           
channel ORA_SBT_TAPE_1: SID=560 device type=SBT_TAPE                                                                                                                                        
channel ORA_SBT_TAPE_1: HPE StoreOnce Catalyst Plugin for RMAN                                                                                                                              
cataloged backup piece                                                                                                                                                                      
backup piece handle=full_db_sb_9cu82od1_1_1 RECID=3539 STAMP=1016455268

You can query the result and continue to work with backup as you need.

Good Luck !

Oracle proxy user

1. alter user transver grant connect through ddi ;
2. conn ddi[transver]

CRS-2730: Resource <...> depends on resource 'ora.net1.network'

The situation :

There is an Oracle GI 12.2 on two nodes.

We successfully added new public and administrative networks to all nodes (created vips, scan, scan_listeners etc.).

We migrated all resources to it (database and asm instances).

We need to stop clean up all TCP/IP addresses of the network 1 (default client's network used during GI installation).


So, you need to do the following :
1. Stop all and remove all listener(s) of network 1
2. Stop and remove scan resources of network 1
3. Stop and remove vips of network 1 (this will require higher privileges)
4. Stop network 1 (this will require higher privileges and you'll have to stop dependent resources)
5. Remove network 1

At the step 4 you can encounter into errors like :
- CRS-2730: Resource 'ora.qosmserver' depends on resource 'ora.net1.network' ;
- CRS-2730: Resource 'ora.ons' depends on resource 'ora.net1.network';
- CRS-2730: Resource 'ora.cvu' depends on resource 'ora.net1.network'

Use the following to reassign Grid dependencies to other network (network 2). Use it with caution (they are unsupported by Oracle).
# crsctl modify resource ora.ons -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported
# crsctl modify resource ora.ons -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.cvu -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported
# crsctl modify resource ora.cvu -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.qosmserver -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net2.network)" -unsupported
# crsctl modify resource ora.qosmserver -attr "START_DEPENDENCIES=hard(ora.net2.network) pullup(ora.net2.network)" -unsupported

Final statement will be successful :

# srvctl remove network -netnum 1

Aftermath run early stopped resources (if needed) :
$ onsctl start
$ srvctl start cvu
$ srvctl start qosmserver

Check that everything works.

Good Luck !

Create database link between Oracle databases with non-supported client/server connectivity

Lets consider two databases - db_a (Oracle 12.2) and db_b (Oracle 9.2). The way of connection is from A to B. I hadn't managed to connect from B to A using below steps. If you know how, please, share in the comments.

We need proxy database in order to organize supported cascading database links. In my case it was 11.2 database (let name it proxy_db).

What we need to do ? The action plan as in the following :

1. Create empty schema proxy_user in the proxy_db. 

2. Create database link proxy_to_db_b connecting newly created schema (1) to the schema in the target (9.2) database. 

3. Create private synonyms using database link (2) for all (or for all needed) objects of schema of the target (9.2) database.

4. Create database link db_a_to_proxy_db in the source (12.2) database. 

Now you can address tables via table_name@db_a_to_proxy_db clause. BUT, if you're using database links to target (9.2) database inside PL/SQL, you need to do more, as in the following, otherwise you'll not be able to compile your PL/SQL objects.

5. Create empty schema (metadata only and needed PL/SQL objects) target_user in the source database 12.2 (db_a).

6. Create database link (in source 12.2 database) from app schema to schema from item (5) using database link name as in item (2) (proxy_to_db_b). 

Now you will be able to modify and compile PL/SQL which depends (via db_link) on objects in the destination (9.2) database.

Look at My Oracle Support note 453754.1 for additional information. This document also says you
you can use dynamic SQL inside PL/SQL to work around (the database link is not resolved at compile time but at run time).

Good Luck

Random number inside sed substitution - example

Let's take an example sql file 1.sql, which contains only one line :

# cat 1.sql
   CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_ELEMENT_" ON "USER"."A_USER-2004" ("XXX")

We need to replace _ELEMENT_ to number value. Here is one of the ways to do it :

# sed "s/_ELEMENT_/_$RANDOM/ ; s/.*/echo '&'/e" 1.sql
   CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_27237" ON "USER"."A_USER-2004" ("XXX")

Good Luck !

P.S. It seems the sed's info page is quite misleading about substitution flag /e... ;-)

How to get input and make output to the same file on Linux

There can be several options. Suppose we have text file a, filled up with literal 'a', and our objection is change its contents to 'b', in other words replace every 'a' to 'b'

1. Using GNU sed to edit file in place (with backup of file a applying extension [SUFFIX] to name):

# sed -i[SUFFIX] 's/a/b/g' a

or without backup of file a :

# sed -i 's/a/b/g' a

P.S. On Solaris, it's possible to use gsed instead of sed

2. Using interim file :

# sed 's/a/b/g' a > interim_file && mv interim_file a

3. Playing with file descriptors. One possible variant is :

# exec 3<>a && sed 's/a/b/g' a >&3 && exec 3<&-

4. Using shell process substitution :

# sed 's/a/b/g' a > >(sleep 1 && cat > a)

Sleep is necessary to make some interval after sed finish its work

5. Many other alternatives, using ed, cat&echo etc.

Good Luck !