Wednesday, January 25, 2023

clone.pl is deprecated, substituted by runInstaller - how to use

1. create a "Golden Image" of the Oracle software home you want to clone
oracle@testserver1.oric.no:[testdb01]# /sw/oracle/product/19c_230117/runInstaller -silent -createGoldImage -destinationLocation /sw/oracle/product
Launching Oracle Database Setup Wizard...

Successfully Setup Software.
Gold Image location: /sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip
Note that I use the -silent flag. Without it, I received the following two errors:

ERROR: Could not read any NLS message catalogue!

and

Can't connect to X11 window server using 'localhost:10.0' as the value of the DISPLAY variable.

2. Transfer the zip file to your destination server using scp
ssh testserver2.oric.no
su - oracle
cd /sw/oracle/product
scp testserver1:/sw/oracle/product/db_home_2023-01-24_04-05-28PM.zip .
3. Make a directory for the new ORACLE_HOME
mkdir 19c_230117
4. Unzip the image file
unzip -d 19c_230117 db_home_2023-01-24_04-05-28PM.zip
5. create a simple script for the installation process
vi exec_runInstaller.sh; chmod 755 exec_runInstaller.sh
Add the following to exec_runInstaller.sh:
#!/bin/bash
export GLOGFILE=/sw/oracle/product/exec_runInstaller.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "starting installer at " `date`
export CV_ASSUME_DISTID=OEL8.1
export NEW_ORACLE_HOME=/sw/oracle/product/19c_230117
cd $NEW_ORACLE_HOME
./runInstaller -silent -debug -force \
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0 \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=dba \
ORACLE_HOME=$NEW_ORACLE_HOME \
ORACLE_BASE=$ORACLE_BASE \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
DECLINE_SECURITY_UPDATES=true

exit
Note that I enter the newly unpacked ORACLE_HOME to execute runInstaller.

6. execute the script
./exec_runInstaller.sh
7. Run root scripts

Upon completion, check the logfile /sw/oracle/product/install_sw_${TS}.log and you are reminded to run two root scripts:
You can find the log of this install session at:
 /home/oracle/oraInventory/logs/InstallActions2023-01-25_10-04-01AM/installActions2023-01-25_10-04-01AM.log

As a root user, execute the following script(s):
        1. /sw/oracle/product/19c_230117/root.sh

Execute /sw/oracle/product/19c_230117/root.sh on the following nodes:
[testserver02.oric.no]

8. Verify that the new ORACLE_HOME is registered in the inventory
cat  /home/oracle/oraInventory/ContentsXML/inventory.xml
Output (abbreviated):
<HOME NAME="OraDB19Home1" LOC="/sw/oracle/product/19c_230117" TYPE="O" IDX="3"/>

Tuesday, January 24, 2023

How to avoid error Perl lib version (5.28.1) doesn't match executable version (5.36.0)

When trying to use clone.pl to clone the latest version of the Oracle 19c database, the following error stopped me from proceeding:
/sw/oracle/product/19.18/clone/bin/clone.pl: 
Perl lib version (5.28.1) doesn't match executable '/sw/oracle/product/19.18/perl/bin/perl' 
version (5.36.0) at /sw/oracle/product/19.17/perl/lib/5.28.1/x86_64-linux-thread-multi/Config.pm 
line 62.
Turns out that that the error occured at the following point in my code:
export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!
Cause:
The .bash_profile of the oracle user contained a variable commonly used in EBS database environments:
export PERL5LIB=/sw/oracle/product/19.17/perl/lib/5.28.1:/sw/oracle/product/19.17/perl/lib/site_perl/5.28.1:/sw/oracle/product/19.17/appsutil/perl
and when trying to run the clone.pl program from the new ORACLE_HOME, perl was unable to find the matching libraries.

Solution:
unset the PERL5LIB before running the clone.pl program:
export NEW_ORACLE_HOME=$ORACLE_BASE/product/19.18
export PRE_ORACLE_HOME=$ORACLE_BASE/product/19.17
export NEW_ORACLE_VERSION=19C_230117
su - oracle <<!
cd $NEW_ORACLE_HOME/clone/bin
unset PERL5LIB
perl clone.pl ORACLE_HOME=$NEW_ORACLE_HOME ORACLE_HOME_NAME=$NEW_ORACLE_VERSION ORACLE_BASE=$ORACLE_BASE OSDBA_GROUP=dba
!

Thursday, January 19, 2023

What is the ifile directive sometimes used in tnsnames.ora?

My customer is using Oracle E-Business Suite, and during a so-called "autoconfig", some files in the Oracle RDBMS home on the database server is overwritten.

Oracle eBS actually warns the DBA against changing these files manually. Here is an example from the $TNS_ADMIN/tnsnames.ora:
###############################################################
#
# Do not edit settings in this file manually. They are managed
# automatically and will be overwritten when AutoConfig runs.
# For more information about AutoConfig, refer to the Oracle
# E-Business Suite Setup Guide.
###############################################################
Since we are using multitenant architecture, and also use Golden Gate to extract data, we need to tnsnames.ora entries for the Golden Gate setup:

  • One entry for the pluggable database
  • One entry for the container database

    After the initial setup, before the Golden Gate installation was ready, the $TNS_ADMIN/tnsnames.ora contained only the first of these two entries:
    ebsref=
            (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs-ref-db01.oric.no)(PORT=1537))
                (CONNECT_DATA=
                    (SERVICE_NAME=ebs_ebsref)
                    (INSTANCE_NAME=cdb)
                )
            )
    
    This entry is fine for the first of our two required connections. But there was no one supporting a direct connection to the root container, in my case called "cdb".
    The listener process already supports a service called "cdb":
    Services Summary...
    Service "cdb" has 1 instance(s).
      Instance "cdb", status READY, has 1 handler(s) for this service...
    Service "ebsref" has 1 instance(s).
      Instance "ebsref", status UNKNOWN, has 1 handler(s) for this service...
    
    But we cannot add the necessary Golden Gate configuration unless we add another entry in the tnsnames.ora file.

    How to do this, when we are not supposed to edit the tnsnames.ora because it eventually will be overwritten by autoconfig?

    The answer was actually already in the tnsnames.ora file.

    Oracle has included an example of how to use an include file in the tnsnames.ora file, so we can add our entries according to our needs. At the bottom of the file, you can see this string:
    IFILE=/u01/oracle/sw/19c/network/admin/ebsref_ebs-ref-db01/ebsref_ebs-ref-ref-db01_ifile.ora
    
    The file does not exist, so you need to create it:
    vi /u01/oracle/sw/19c/network/admin/ebsref_ebs-ref-db01/ebsref_ebs-ref-ref-db01_ifile.ora
    
    Add the following:
    cdb = (DESCRIPTION=
              (ADDRESS=(PROTOCOL=tcp)(HOST=ebs-ref-db01.skead.no)(PORT=1537))
                    (CONNECT_DATA= (SERVICE_NAME=cdb)
                    (INSTANCE_NAME=cdb))
          )
    
    And you've solved your problem, the connect identifier "cdb" can now be used by Golden Gate connections to the container database. Burleson Consulting has an article about ifile that actually points out eBS as the very reason for the ifile directive:

    According to some Oracle experts, the ifile facility was designed for the init.ora file and ifile is not supported for the listener.ora, sqlnet.ora and protocol.ora files, but Oracle eBusiness Suite (EBS) release 11 uses ifile in their parameter files, ostensibly for reasons of customizations. With a ifile, you can leave the original file intact and not wonder what was added later.
  • Wednesday, January 11, 2023

    More on setting redo routes property when adding a terminal standby database to your data guard broker configuration

    As described in an earlier post, it is possible to set up a "terminal standby database" which fetches its redo information from another standby database, rather than directly from the primary database.

    It is quite facinating to see how the data guard broker simplifies this setup for the DBA.

    In a recent excercise @ work, I had a data guard configuration consisting of 1 primary and 3 physical standby database.

    A fourth was to be added as a terminal standby database.

    First, clone the new database for standby.

    You can use any of the databases in the configuration as a source, from which to create a terminal database, both primary or any of the mounted physical standby databases.

    When the clone is finished, add the new database to the broker config:
    dgmgrl -echo sys/password@primdb_dgmgrl.oric.no as sysdba
    
    Welcome to DGMGRL, type "help" for information.
    Connected to "primdb"
    Connected as SYSDBA.
    
    Add the new database:
    DGMGRL> add database 'tstby' as connect identifier is tstby.oric.no maintained as physical;
    add database 'tstby' as connect identifier is tstb.oric.no maintained as physical;
    Database "tstby" added
    DGMGRL> show configuration;
    show configuration;
    
    Configuration - dgconfig1
    
      Protection Mode: MaxPerformance
      Members:
      primdb     - Primary database
        stby1     - Physical standby database
        stby2     - Physical standby database
        stby3     - Physical standby database
        tstby    - Physical standby database (disabled)
    
    Fast-Start Failover: DISABLED
    
    Notice how all the standby databases are indented directly underneath the primary database, indicating that they receive their redo information directly from the primary database.

    I then add the redoroutes property to the primary:
    DGMGRL> edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
    edit database primdb set property redoroutes='(LOCAL:stby1,stby2,stby3 ASYNC)(stby1:tstby ASYNC)';
    Property "redoroutes" updated
    
    Add the redoroutes property for when the primary and the choosen standby switches roles:
    DGMGRL> edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
    edit database stby1 set property redoroutes='(LOCAL:primdb,stby2,stby3 ASYNC)(primdb:tstb ASYNC)';
    Property "redoroutes" updated
    
    Note that both of these rules must be set, otherwise your terminal standby database will not receive logs. You will see messages like the follwing:
    Configuration - dgconfig1
    
      Protection Mode: MaxPerformance
      Members:
      primdb     - Primary database
        stby1  - Physical standby database
        stby2  - Physical standby database
        stby3  - Physical standby database
    
      Members Not Receiving Redo:
      tstby  - Physical standby database
        Error: ORA-16685: database does not receive redo data
    

    View the configuration again
    You will see that the broker has understood that the tstb is acting like a terminal standby database for physical standby database "stby2":
    DGMGRL> show configuration;
    show configuration;
    
    Configuration - dgconfig1
    
      Protection Mode: MaxPerformance
      Members:
      primdb     - Primary database
        stby1  - Physical standby database
          tstby  - Physical standby database (disabled)
        stby2 - Physical standby database
        stby3 - Physical standby database
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    SUCCESS   (status updated 27 seconds ago)
    
    Finally, enable the database:
    DGMGRL> enable database 'tstb';
    enable database 'tsbt';
    Enabled.
    
    The output you should see at the end is:
    DGMGRL> show configuration;
    show configuration;
    
    Configuration - dgconfig1
    
      Protection Mode: MaxPerformance
      Members:
      primdb     - Primary database
        stby1  - Physical standby database
          tstby  - Physical standby database (receiving current redo)
        stby2 - Physical standby database
        stby3 - Physical standby database
    

    Friday, January 6, 2023

    How to solve script error $'\r': command not found syntax error: unexpected end of file

    when trying to execute a script from root, like the following:
     su - oracle -c "/tmp/set_params.sh"
    
    You may see errors like the following:
    set_params.sh: line 6: $'\r': command not found
    set_params.sh: line 42: syntax error: unexpected end of file
    
    Root cause:

    You have windows-style formatting in your file

    Solution:
    dos2unix set_params.sh
    dos2unix: converting file flexpod_params.sh to Unix format...
    
    When executed again, the above errors should be gone!

    Wednesday, January 4, 2023

    Check if your CDB is set up for local undo

    col PROPERTY_NAME format a20
    col PROPERTY_VALUE format a20
    col DESCRIPTION format a40
    col LOCAL_UNDO_ENABLED format a20
    select * from database_properties where property_name like '%UNDO%';
    
    PROPERTY_NAME        PROPERTY_VALUE       DESCRIPTION
    -------------------- -------------------- ----------------------------------------
    LOCAL_UNDO_ENABLED   TRUE                 true if local undo is enabled
    

    Monday, December 12, 2022

    Some useful ADRCI commands

    The ADR (Automatic Diagnostic Repository) and its command-line tool adrci was introduced in Oracle11g and is still available.

    I still find it incredibly useful when troubleshooting problems, particulary ORA-600 errors which needs to be sent to Oracle Support.

    An ADR may contain file from many homes, or components. You need to set the correct home before you start working with ADR:
    show homes
    set homepath 
    
    exampel:
    adrci> show homes
    ADR Homes:
    diag/tnslsnr/zus15d-0110/listener_pzhhtet2
    diag/tnslsnr/zus15d-0110/listener_pzhhtet1
    diag/rdbms/pzhhtet1/PZHHTET1
    diag/rdbms/pzhhtet2/PZHHTET2
    
    adrci> set homepath diag/rdbms/pzhhtet1/PZHHTET1
    adrci> show home
    ADR Homes:
    diag/rdbms/pzhhtet1/PZHHTET1
    
    help show tracefile
    show tracefile 
    
    order by timestamp:
    show tracefile -t 
    
    order by reversed timestamp:
    show tracefile -rt
    
    filter on tracefile name:
    show tracefile %m00% -rt
    
    alert log operations
    show alert
    show alert -tail -f
    show alert -tail 50
    
    show alert -p "module_id='DBMS_SCHEDULER'"
    show alert -p "module_id != 'DBMS_SCHEDULER'"
    show alert -p "module_id LIKE '%SCHEDULER%'"
    
    If you want to extract the Streams related statements from the alert log, you would use:
    show alert -p "message_text like '%STREAM%'"
    
    Show all messages related to a group. For example, a startup:
    show alert -p "MESSAGE_GROUP LIKE 'startup'"
    
    spool 
    spool off
    
    show incident
    show incident -mode basic 
    show incident -mode detail
    show incident -mode detail -p "incident_id=incident_ID;"
    show incident -last 5
    
    create a package from an incident:
    adrci> ips create package incident 1697
    Created package 1 based on incident id 1697, correlation level typical
    
    view its contents:
    adrci> ips show files package 1
       FILE_ID                1
       FILE_LOCATION          /incident/incdir_1697
       FILE_NAME              +ASM1_lmhb_15352_i1697.trc
       LAST_SEQUENCE          0
       EXCLUDE                Included
    
       FILE_ID                2
       FILE_LOCATION          /incident/incdir_1697
       FILE_NAME              +ASM1_lmhb_15352_i1697.trm
       LAST_SEQUENCE          0
       EXCLUDE                Included
    
       FILE_ID                3
       FILE_LOCATION          /trace
       FILE_NAME              +ASM1_lmhb_15352.trc
       LAST_SEQUENCE          0
       EXCLUDE                Included
    
       FILE_ID                4
       FILE_LOCATION          /trace
       FILE_NAME              +ASM1_lmhb_15352.trm
       LAST_SEQUENCE          0
       EXCLUDE                Included
    
    generate zip-files on disk:
    adrci> ips generate package 1 in /cs/oracle/grid/packages
    Generated package 1 in file /cs/oracle/grid/packages/ORA29770_20100427133326_COM_1.zip, mode complete
    
    To remove old incidents, use
    purge -age 2 -type incident
    
    where -age 2 means older than two days.

    Automatic purging:
    adrci> purge -age 2880 -type trace
    
    --> will automatically remove all trace files older than 2880 minutes (2 days) type "help purge" on the adrci prompt for more options.

    Other examples:
    purge -age 129600 -type alert
    purge -age 129600 -type incident
    purge -age 129600 -type cdump
    purge -age 129600 -type stage
    purge -age 129600 -type sweep
    purge -age 129600 -type hm
    
    129600 = 90 days 43200 = 30 days or
    purge -age 2880 -type trace
    purge -age 2880 -type alert
    purge -age 2880 -type cdump
    purge -age 2880 -type stage
    purge -age 2880 -type sweep
    purge -age 2880 -type hm
    
    you can create a batch file and run all ov the above in one bulk:
    adrci script=adrci_settings.cmd
    
    You can spool from within an adrci script:
    spool myfile.log
    
    There are two policies in place; the LONGP_POLICY and the SHORTP_POLICY. Events such as incidents will fall into the LONGP policy and trace files and dumps will fall into the SHORTP policy. You can configure the policies using the commands below, which set the short and long retention periods to 3 months and 6 months, respectively. Note that set control accepts the time in hours
    show control
    set control (SHORTP_POLICY = 2160)
    set control (LONGP_POLICY = 4320)
    
    Run in batch mode using the "exec" instruction"
    adrci exec="command1; command2 ... command n"