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