Monday, December 11, 2023

How to write custom messages to the alert log file

exec sys.dbms_system.ksdwrt(2,'Your message here')
The first argument can be either

  • 1 -> Write to the trace file
  • 2 -> Write to the database alert log file
  • 3 -> Write to both of the above files
  • Friday, December 8, 2023

    Syntax for applying and removing a patch on the WLS server Home in an EBS 12.2 installation

    The bsu (or "Smart update") utility:
    cd $MW_HOME/utils/bsu
    bsu.sh -version
    Oracle Smart Update. Version: 3.3.0.0
    
    Here's an example on how to install a patch:
    ./bsu.sh -remove -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=1LRI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_1LRI.log
    
    Here's an example on how to remove a patch:
    ./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=E7HI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_E7HI.log
    

    Script sniplet to differentiate between cdb and pdb environment settings in a shell script

    This little sniplet could be used if you find yourself in a situation where you need to differentiate between the cdb environment and the pdb environment on a server using multitenant setup.

    I am setting a commonly used alias for the usage of sqlplus called "sql" depending on the ORACLE_SID being set to the cdb or the pdb.

    It differs slightly since I you can only do a bequath session when connecting to the former, and not the latter:

  • When connecting to the pdb as sysdba, you need to go through the listener, and therefore supply the password.
  • When connecting to the cdb you can do a so called bequeath session and connect directly to the oracle instance through IPC.
    read -p 'Will you be administrating the cdb or the PDB? ' container
    case $container in
            CDB|cdb)
            . $ORACLE_HOME/cdb_settings.env;
            alias sql='sqlplus / as sysdba';;
            PDB1|pdb1)
            . $ORACLE_HOME/pdb1.env;
            alias sql='sqlplus sys@pdb1 as sysdba';;
            *) echo "You must select cdb or pdb1";
             exit 1;;
    esac
    
  • How to generate a cold backup script for a database

    Probably many ways to to this, but here is how I generated a simple file that after being made executable will copy all files to a specific folder. The database must be shut down before running the script, so in other words, this will be a good, old-fashioned cold backup!
    set lines 200
    set pages 0
    set trimspool on
    set heading off
    set echo off
    set feedback off
    set verify off
    
    spool cp_files.sh
    
    
    SELECT    'cp '
           || a.file_name
           || ' /u01/oracle/cold_backup/'
           || (SELECT TRIM (SUBSTR (b.file_name,
                                    (  INSTR (b.file_name,
                                              '/',
                                              -1,
                                              1)
                                     + 1)))
                 FROM cdb_data_files b
                WHERE a.file_name = b.file_name)
      FROM cdb_data_files a
    UNION
    SELECT    'cp '
           || a.MEMBER
           || ' /u01/oracle/cold_backup/'
           || (SELECT TRIM (SUBSTR (b.MEMBER,
                                    (  INSTR (b.MEMBER,
                                              '/',
                                              -1,
                                              1)
                                     + 1)))
                 FROM v$logfile b
                WHERE a.MEMBER = b.MEMBER)
    FROM v$logfile A
    UNION
    SELECT    'cp '
           || a.name
           || ' /u01/oracle/cold_backup/'
           || (SELECT TRIM (SUBSTR (b.name,
                                    (  INSTR (b.name,
                                              '/',
                                              -1,
                                              1)
                                     + 1)))
                 FROM v$tempfile b
                WHERE a.name = b.name)
      FROM v$tempfile a
    UNION
    SELECT    'cp '
           || a.name
           || ' /u01/oracle/cold_backup/'
           || (SELECT TRIM (SUBSTR (b.name,
                                    (  INSTR (b.name,
                                              '/',
                                              -1,
                                              1)
                                     + 1)))
                 FROM v$controlfile b
                WHERE a.name = b.name)
      FROM v$controlfile a;
    

    Thursday, November 23, 2023

    How to use strace to figure out what files are being accessed by a shell script

    I had a situation where an ebs-supplied script adstrtal.sh would not start - it kept throwing error
    ORA-12541: TNS:no listener
    
    Although $TNS_ADMIN was correctly set, and sqlplus and tnsping would confirm that the database was open and the listener was up, accepting connections on the desired port.

    The sqlnet.log file created in the same directory from which I executed adstrtal.sh displayed the connection being attempted:
    Fatal NI connect error 12541, connecting to:
     (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=EBS32.oric.no)(CID=(PROGRAM=sqlplus)(HOST=oric-ebsapp-utv.oric.no)(USER=ebs)))
     (ADDRESS=(PROTOCOL=TCP)(HOST=162.20.5.225)(PORT=1521)))
    
    We are not using default port 1521, but a different port.

    I then used strace to find the source of the error, like this:
    strace -f -o /tmp/strace.out ./adstrtal.sh apps/****
    
    When going through the /tmp/strace.out file, I was pointed in the right direction:
    openat(AT_FDCWD, "$INST_TOP/ora/10.1.3/network/admin/EBS32_oric-ebsapp-utv_ifile.ora", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No such file or directory)
    
    Turns out that adstrtal.sh was looking for a tnsnames.ora entry not in the tnsnames.ora in $TNS_ADMIN ($INST_TOP/ora/10.1.2/network/admin) but rather in $INST_TOP/ora/10.1.3/network/admin.

    As soon as I had added the correct tnsnames.ora entry, the adstrtall.sh script worked.

    Solution to ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    I had a situation where the error
    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
    
    was thrown when connecting to a PDB.

    The PDB was up and the listener runnning and serving the service_name which I wanted to connect to:

    Solution was to reverse the order of directory_path in sqlnet.ora from
    NAMES.DIRECTORY_PATH= (EZCONNECT,TNSNAMES)
    
    to
    NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT )
    

    Friday, November 10, 2023

    What are EBS snapshots?

    I found this info in the document Oracle® Applications Maintenance Utilities Release 12.1 Part No. E13676-02 There are two types of snapshots: APPL_TOP snapshotsand global snapshots.

  • An APPL_TOP snapshot lists patches and versions of files in the APPL_TOP.
  • A global snapshot lists patches and latest versions of files in the entire Applications system (that is, across all APPL_TOPs).

    Both APPL_TOP snapshots and global snapshots may be either current view snapshots or named view snapshots.

    A current view snapshot is created once and updated when appropriate to maintain a consistent view.

    A partial view snapshot allows you to synchronize only selected files from a current view.

    A named view snapshot is a copy of the current view snapshot at a particular time (not necessarily the latest current view snapshot), and is not updated.