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.

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

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!

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

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"

What does the UR = A in tnsnames.ora mean?

I recently had to connect remotely as sysdba to remote instances when I created Physical Standby Databases using RMAN.

There were some some connection problems along the way, and this was solved by setting the (UR=A) attribute in the tnsnames.ora entry for the database.

What exactly is the UR=A attribute and what does it do?

This is explained very well in a blog post from Ed Chen Logic so rather than explaining the same points all over again, continue reading there.

But in short, an instance (memory, background processes, but no data files) will inform the listener to block all connections to its corresponding service by default.

To bypass this restriction, you can add UR=A to the connect descriptor. From the primary server, I will then be able to connect to the standby instance running on prodserver2.oric.no.

Let's demonstrate it:

Start the standby database in nomount:

sqlplus / as sysdba
startup nomount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size                  8635096 bytes
Variable Size            2986347816 bytes
Database Buffers         9865003008 bytes
Redo Buffers               24915968 bytes

Start a default listener on the standby server:

lsnrctl start

Make sure the default service is loaded: wait for a minute, or execute

alter system register;

Listener status is now:

Service "proddb01_stby.oric.no" has 1 instance(s).
  Instance "proddb01", status BLOCKED, has 1 handler(s) for this service...

Create a tnsnames.ora entry for connecting to the standby database from the primary server:

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

Test it:

sqlplus sys/password@proddb01_stby.oric.no as sysdba

Result:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Add the UR=A to the connect_data section:

proddb01_stby.oric.no =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = proddb01.oric.no)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = proddb01_stby.oric.no)
      (UR = A)
    )
  )

And try again:

sqlplus sys/password@proddb01_stby.oric.no as sysdba

Result:

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 9 16:43:09 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select host_name from v$instance;

HOST_NAME
-----------------
proddb02.oric.no

SQL> show parameter db_unique

NAME             TYPE      VALUE
---------------- --------- --------------
db_unique_name   string    proddb01_stby

How to solve "Can't locate TXK/ARGS.pm in @INC (you may need to install the TXK::ARGS module)" when running txkChkPDBCompatability.pl

Background:

As a part of converting your non-CDB Oracle database to a pluggable database, you are supposed to run the perl script txkChkPDBCompatability.pl

Problem:

The script fails with:

Can't locate TXK/ARGS.pm in @INC (you may need to install the TXK::ARGS module) (@INC contains: $ORACLE_HOME/perl/lib/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/5.28.1 $ORACLE_HOME/perl/lib/site_perl/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/site_perl/5.28.1 $ORACLE_HOME/perl/lib/site_perl/5.28.1/x86_64-linux-thread-multi /$ORACLE_HOME/perl/lib/site_perl/5.28.1 $ORACLE_HOME/perl/lib/5.28.1/x86_64-linux-thread-multi $ORACLE_HOME/perl/lib/5.28.1) at ./txkChkPDBCompatability.pl line 61.
BEGIN failed--compilation aborted at ./txkChkPDBCompatability.pl line 61.

Cause:

The PERL5LIB environment variable is not complete. You need to add the location of the ADK and TXK directories, which in turn contains several .pm files.
These folders can be found in $ORACLE_HOME/appsutil/perl

Solution:

Add the $ORACLE_HOME/appsutil/perl to your existing PERL5LIB variable:

export PERL5LIB=$PERL5LIB:$ORACLE_HOME/appsutil/perl

Thanks to the author of Punitoracledba for providing the solution after lots of troubleshooting.

To see the note used to convert a non-cdb EBS database to a pluggable database, go to Doc ID 2552181.1 at Oracle Support