Wednesday, November 16, 2022

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
  • Tuesday, November 15, 2022

    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.

    Friday, November 11, 2022

    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;
    

    Wednesday, November 9, 2022

    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...
    

    Tuesday, November 8, 2022

    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)
        )
      )
    

    Monday, October 31, 2022

    How to clear resolved errors from PDB_PLUG_IN_VIOLATIONS

    The query below shows 4 resolved messages in the view PDB_PLUG_IN_VIOLATIONS:
    SELECT NAME,CAUSE,TYPE,MESSAGE,STATUS
    FROM PDB_PLUG_IN_VIOLATIONS
    WHERE NAME='PDB$SEED'
    
    NAME CAUSE TYPE MESSAGE STATUS
    PDB$SEED SQL Patch ERROR Interim patch 31424070/24854845 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB RESOLVED
    PDB$SEED SQL Patch ERROR Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Not installed in the CDB but installed in the PDB RESOLVED
    PDB$SEED SQL Patch ERROR '19.16.0.0.0 Release_Update 2207030222' is installed in the CDB but no release updates are installed in the PDB RESOLVED
    PDB$SEED SQL patch error ERROR Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): ROLLBACK with status INITIALIZE in the CDB. RESOLVED

    The resolved messages can be easily removed with the procedure DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS:
    SYS@CDB$ROOT SQL> exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PDB$SEED');
    

    Useful resources:

  • Mike Dietrich on the same topic
  • The Oracle 19c Documentation for the PDB_PLUG_IN_VIOLATIONS view

    Note that there is an Unpublished Bug 16192980 : NO SIMPLE WAY TO CLEAN ROWS FROM PDB_PLUG_IN_VIOLATIONS AFTER DBMS_PDB CALL that prevents you from discarding no-impact warnings.
    According to Oracle there should be a fix for this in version 19.10, but I still strugle to remove messages like
      Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
      
    which can be ignored. Read more about Database option mismatch errors in Doc ID 2020172.1
  • Friday, October 21, 2022

    Rename schema in PostgreSQL: syntax

    \connect db01
    alter schema schema1 rename to schema1_old;
    
    Documented here