How to log entire output from a shell script

I have recently used exec to send all output from a bash script to a designated logfile.

For example, if you have a complex script which performs multiple tasks and even execute tasks under a different user along the way, all the output will go into the global logfile indicated by the $GLOGFILE variable:

#!/bin/bash
export SCRIPT_NAME=`basename $0`
export TS=`date +\%m.\%d.\%y\_%H_%M_%S`
export GLOGFILE=/u01/mylogfile_${TS}.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "Starting job at " `date`

your shell instructions here

echo "Ending job at " `date`
exit

Good sources:

Linuxfordevices.com

Geeksforgeeks.org

How to correct an identity column sequence after import

Thanks to the authors behind Grassroots-oracle for sharing this trick.

I finished an import of a table with an identity column, and for some reason, the sequence started to hand out number at the bottom of the range, although it had reached 10632546 in the source table.

My table's DDL:

CREATE TABLE MYTABLE
(
  MYID                   NUMBER GENERATED ALWAYS AS IDENTITY ( START WITH 21 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER NOKEEP NOSCALE) NOT NULL,
  EVENT_TYPE             VARCHAR2(75 BYTE)      NOT NULL,
  SOURCE                 VARCHAR2(50 BYTE)      NOT NULL,
  FORMAT                 VARCHAR2(75 BYTE)      NOT NULL,
  EVKEY                  VARCHAR2(60 BYTE)      NOT NULL,
  TITLE                  VARCHAR2(40 BYTE)      NOT NULL,
  TIMESTAMP              TIMESTAMP(6)           DEFAULT current_timestamp,
  DOCUMENT               CLOB                   NOT NULL,
  FILEREF                VARCHAR2(200 BYTE)     DEFAULT 'some text' NOT NULL,
  TRANNUM                NUMBER
)
LOB (DOCUMENT) STORE AS SECUREFILE (
  TABLESPACE  LOB_DATA
)
TABLESPACE DATA;

After import, the developers tested an insert and the row was inserted with MYID set to 1.

There is no Primary Key on the table, which I pointed out to the developers.

The sequence name was ISEQ$$_88174, and the query below returned the number 2 after import in the destination database:

select "ISEQ$$_88174".nextval
from dual;

To "start where the sequence left off" in the source database, all I had to do was to execute:

ALTER TABLE MYTABLE
MODIFY (MYID  GENERATED ALWAYS AS IDENTITY ( START WITH LIMIT VALUE) );

If I queried the sequence's next value now, it had changed from 2 to 10632547.

I then checked the maximum value of the column:

select max(MYID) from MYTABLE;

And it returned 10632546, in other words, the next value would be the current max value + 1.

Getting ORA-01031: insufficient privileges when creating a view that is refering to another schema's table

If you get PRA-0131 during creation of a view refering to a schema object in a different table, try the following:

grant select on foreing_schema.foreign_table to scott with grant option;

Listener output showing multiple instances when only one exists

In one of my multitenant setups, I saw the following output from the listener:

lsnrctl status cdb

Service "sales" has 2 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...
  Instance "sales", status UNKNOWN, has 1 handler(s) for this service...

There is only one instance in my setup, the container database called "cdb".
What's causing the listener to view the pdb "sales" as a separate instance, then?

See this post where Uwe Hesse explains:

It is nothing to worry about when you see the same instance showing up twice, once with status READY and once with status UNKNOWN. READY means listener knows this because PMON of that instance told him so (dynamic registration) UNKNOWN means listener knows this because of SID_LIST entry in the listener.ora (static registration)

Quite correctly, I had a static entry in my listener.ora file:

SID_LIST_cdb =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/orasoft/product/19c)
      (SID_NAME = sales)
    )
  )

If I remove the following part

(SID_NAME = sales)

and restart the listener, the extra instance will be removed from the listener output:

lsnrctl stop cdb
lsnrctl start cdb

Output:

Service "sales" has 1 instance(s).
  Instance "cdb", status READY, has 2 handler(s) for this service...

Workaround for error ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor) when starting Golden Gate replicate process

When trying to login to the golden gate cli interface, I got the following error:

GGSCI (myserver1.oric.no) 7> dblogin useridalias mydb  domain admin
ERROR: OCI Error DPI (status = 7-DPI-007: invalid OCI handle or descriptor).

Cause:

Incorrectly specified network configuration:
sqlnet.ora

names.default_domain = world

tnsnames.ora

mydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

To correct the error, you need to do either of the following:

1. remove the names.default_domain from sqlnet.ora

or

2. add an alias to your tnsnames.ora file

mydb.world =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )