May 10, 2022

How To Install Trace File Analyzer( TFACTL)

 

Step 1.

Download latest version of  TFACTL from Doc ID 2550798.1

 

Step 2.

Upload the downloaded zip into the server and extract it as

Run tfactl as root user

[root@jdepddb AHF]# ./ahf_setup

 

AHF Installer for Platform Linux Architecture x86_64

 

AHF Installation Log : /tmp/ahf_install_221000_32516_2022_05_09-08_29_04.log

 

Starting Autonomous Health Framework (AHF) Installation

 

AHF Version: 22.1.0 Build Date: 202204111557

 

Default AHF Location : /opt/oracle.ahf

 

Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N : y

 

AHF Location : /opt/oracle.ahf

 

AHF Data Directory stores diagnostic collections and metadata.

AHF Data Directory requires at least 5GB (Recommended 10GB) of free space.

 

Please Enter AHF Data Directory : /opt/oracle.ahf

 

AHF Data Directory : /opt/oracle.ahf/data

 

Do you want to add AHF Notification Email IDs ? [Y]|N : N

 

Extracting AHF to /opt/oracle.ahf

 

Configuring TFA Services

 

Discovering Nodes and Oracle Resources

 

Successfully generated certificates.

 

Starting TFA Services

Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

 

.----------------------------------------------------------------------------.

| Host    | Status of TFA | PID  | Port  | Version    | Build ID             |

+---------+---------------+------+-------+------------+----------------------+

| jdepddb | RUNNING       | 2192 | 36413 | 22.1.0.0.0 | 22100020220411155753 |

'---------+---------------+------+-------+------------+----------------------'

 

Running TFA Inventory...

 

Adding default users to TFA Access list...

 

.-----------------------------------------------------.

|             Summary of AHF Configuration            |

+-----------------+-----------------------------------+

| Parameter       | Value                             |

+-----------------+-----------------------------------+

| AHF Location    | /opt/oracle.ahf                   |

| TFA Location    | /opt/oracle.ahf/tfa               |

| Orachk Location | /opt/oracle.ahf/orachk            |

| Data Directory  | /opt/oracle.ahf/data              |

| Repository      | /opt/oracle.ahf/data/repository   |

| Diag Directory  | /opt/oracle.ahf/data/jdepddb/diag |

'-----------------+-----------------------------------'

 

 

Starting orachk scheduler from AHF ...

 

AHF binaries are available in /opt/oracle.ahf/bin

 

AHF is successfully installed

 

Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] :

 

Moving /tmp/ahf_install_221000_32516_2022_05_09-08_29_04.log to /opt/oracle.ahf/data/jdepddb/diag/ahf/

 


Step 3 .

 Check the all installed tfatl tools status

[root@jdepddb AHF]# tfactl toolstatus

 

.------------------------------------------------------------------.

|                   TOOLS STATUS - HOST : jdepddb                  |

+----------------------+--------------+--------------+-------------+

| Tool Type            | Tool         | Version      | Status      |

+----------------------+--------------+--------------+-------------+

| AHF Utilities        | alertsummary |       22.1.0 | DEPLOYED    |

|                      | calog        |       22.1.0 | DEPLOYED    |

|                      | dbglevel     |       22.1.0 | DEPLOYED    |

|                      | grep         |       22.1.0 | DEPLOYED    |

|                      | history      |       22.1.0 | DEPLOYED    |

|                      | ls           |       22.1.0 | DEPLOYED    |

|                      | managelogs   |       22.1.0 | DEPLOYED    |

|                      | menu         |       22.1.0 | DEPLOYED    |

|                      | orachk       |       22.1.0 | DEPLOYED    |

|                      | param        |       22.1.0 | DEPLOYED    |

|                      | ps           |       22.1.0 | DEPLOYED    |

|                      | pstack       |       22.1.0 | DEPLOYED    |

|                      | summary      |       22.1.0 | DEPLOYED    |

|                      | tail         |       22.1.0 | DEPLOYED    |

|                      | triage       |       22.1.0 | DEPLOYED    |

|                      | vi           |       22.1.0 | DEPLOYED    |

+----------------------+--------------+--------------+-------------+

| Development Tools    | oratop       |       14.1.2 | DEPLOYED    |

+----------------------+--------------+--------------+-------------+

| Support Tools Bundle | darda        | 2.10.0.R6036 | DEPLOYED    |

|                      | oswbb        | 22.1.0AHF    | RUNNING     |

|                      | prw          | 12.1.13.11.4 | NOT RUNNING |

'----------------------+--------------+--------------+-------------'

 

Note :-

  DEPLOYED    : Installed and Available - To be configured or run interactively.

  NOT RUNNING : Configured and Available - Currently turned off interactively.

  RUNNING     : Configured and Available.


·      Tfactl print config status

tfactl print config



Check tfactl current status

[root@jdepddb AHF]# tfactl status 

.-----------------------------------------------------------------------------------------------.

| Host    | Status of TFA | PID  | Port  | Version    | Build ID             | Inventory Status |

+---------+---------------+------+-------+------------+----------------------+------------------+

| jdepddb | RUNNING       | 2192 | 36413 | 22.1.0.0.0 | 22100020220411155753 | COMPLETE         |

'---------+---------------+------+-------+------------+----------------------+------------------'

 



To stop tfactl

[root@jdepddb AHF]# tfactl stop

Stopping TFA from the Command Line

Stopped OSWatcher

Nothing to do !

Please wait while TFA stops

Please wait while TFA stops

TFA-00002 Oracle Trace File Analyzer (TFA) is not running

TFA Stopped Successfully

Telemetry adapter is not running

Successfully stopped TFA..



To start tfactl

[root@jdepddb AHF]# tfactl start

Starting TFA..

Waiting up to 100 seconds for TFA to be started..

. . . . .

Successfully started TFA Process..

. . . . .

TFA Started and listening for commands



To disable/ enable tfactl during server restart

[root@jdepddb AHF]# tfactl disable

TFA autostart has been disabled ..

 

[root@jdepddb AHF]# tfactl enable

TFA autostart has been enabled ..



·       To Collect all diagnostic report

[root@jdepddb AHF]# tfactl diagcollect -all

 

TFA will collect diagnostics for the last 1 hour(s).

Please enter the time of the incident [YYYY-MM-DD HH24:MI:SS], or <RETURN> to collect for the last 1 hour(s). (Q|q to Quit):

 

The -all switch is being deprecated as collection of all components is the default behavior. TFA will continue to collect all components.

 

Collecting data for the last 1 hours for all components...

 

Collecting data for all nodes

 

TFA is using system timezone for collection, All times shown in IST.

 

Collection Id : 20220509085039jdepddb

 

Detailed Logging at : /opt/oracle.ahf/data/repository/collection_Mon_May_09_08_50_43_IST_2022_node_all/diagcollect_20220509085039_jdepddb.log

2022/05/09 08:50:50 IST : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom

2022/05/09 08:50:50 IST : Collection Name : tfa_Mon_May_09_08_50_42_IST_2022.zip

2022/05/09 08:50:51 IST : Collecting diagnostics from hosts : [jdepddb]

2022/05/09 08:50:51 IST : Scanning of files for Collection in progress...

2022/05/09 08:50:51 IST : Collecting additional diagnostic information...

2022/05/09 08:50:56 IST : Getting list of files satisfying time range [05/09/2022 07:50:50 IST, 05/09/2022 08:50:56 IST]

2022/05/09 08:51:03 IST : Collecting ADR incident files...

2022/05/09 08:54:36 IST : Completed collection of additional diagnostic information...

2022/05/09 08:54:39 IST : Completed Local Collection

 

.-----------------------------------.

|         Collection Summary        |

+---------+-----------+------+------+

| Host    | Status    | Size | Time |

+---------+-----------+------+------+

| jdepddb | Completed | 18MB | 228s |

'---------+-----------+------+------'

 

Logs are being collected to: /opt/oracle.ahf/data/repository/collection_Mon_May_09_08_50_43_IST_2022_node_all

/opt/oracle.ahf/data/repository/collection_Mon_May_09_08_50_43_IST_2022_node_all/jdepddb.tfa_Mon_May_09_08_50_42_IST_2022.zip


April 26, 2022

How to change DBID in oracle database 19c

 Objective:- Change DBID in oracle database .

For example, we will be changing existing DBID LOGCDB to a new DBID UATCDB


Step 1. Shutdown database instance

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Step 2. Start database in mount mode

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

Database mounted.


SQL> select name,open_mode,instance_name from v$database,v$instance; 

NAME      OPEN_MODE            INSTANCE_NAME

--------- -------------------- ----------------

LOGCDB    MOUNTED              LOGCDB

 


Step 3. Change DBNAME using nid utility

Syntax for changing DBNAME

 

nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

 

[logminedb@ccuine1126 dbs]$ nid target=sys/manager@LOGCDB DBNAME=UATCDB

 

DBNEWID: Release 19.0.0.0.0 - Production on Tue Apr 19 16:47:33 2022

 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

Connected to database LOGCDB (DBID=3597672493)

 

Connected to server version 19.9.0

 

Control Files in database:

    /db/app/logminedb/oradata/LOGCDB/control01.ctl

    /db/app/logminedb/oradata/LOGCDB/control02.ctl

 

Change database ID and database name LOGCDB to UATCDB? (Y/[N]) => Y

 

Proceeding with operation

Changing database ID from 3597672493 to 205605669

Changing database name from LOGCDB to UATCDB

    Control File /db/app/logminedb/oradata/LOGCDB/control01.ctl - modified

    Control File /db/app/logminedb/oradata/LOGCDB/control02.ctl - modified

    Datafile /db/app/logminedb/oradata/LOGCDB/system01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/system01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/sysaux01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/sysaux01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/undotbs01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/undotbs01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/users01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/system01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/sysaux01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/undotbs01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/users01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/temp01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/pdbseed/temp01.db - dbid changed, wrote new name

    Datafile /db/app/logminedb/oradata/LOGCDB/UAT/temp01.db - dbid changed, wrote new name

    Control File /db/app/logminedb/oradata/LOGCDB/control01.ctl - dbid changed, wrote new name

    Control File /db/app/logminedb/oradata/LOGCDB/control02.ctl - dbid changed, wrote new name

    Instance shut down

 

Database name changed to UATCDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database UATCDB changed to 205605669.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.



Step 4. Change dbname in parameter file

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

SQL> alter system set db_name=UATCDB scope=spfile; 

System altered.

 

SQL>  shutdown immediate;

ORA-01507: database not mounted 

ORACLE instance shut down.


SQL> startup nomount

ORACLE instance started.

 Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

 

 SQL> show parameter db_name 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      UATCDB

 

 

SQL> shu immediate

ORA-01507: database not mounted

 

ORACLE instance shut down.



Step 5. Create a new parameter file from old parameter file with a new db name

[logminedb@ccuine1126 ~]$ cd $ORACLE_HOME/dbs

SQL> create pfile from spfile;

 

[logminedb@ccuine1126 dbs]$ mv initLOGCDB.ora initUATCDB.ora

 

[logminedb@ccuine1126 dbs]$ export ORACLE_SID=UATCDB

 

SQL> startup nomount pfile='initUATCDB.ora'

ORACLE instance started.

 

Total System Global Area 4294964000 bytes

Fixed Size                  9143072 bytes

Variable Size             805306368 bytes

Database Buffers         3472883712 bytes

Redo Buffers                7630848 bytes

SQL> ! ps -ef | grep pmon

logmine+  4671     1  0 17:01 ?        00:00:00 ora_pmon_UATCDB

logmine+  4752  4654  0 17:01 pts/5    00:00:00 /bin/bash -c  ps -ef | grep pmon

logmine+  4754  4752  0 17:01 pts/5    00:00:00 grep pmon

 

SQL> create spfile from pfile='initUATCDB.ora';

 

SQL> alter database mount; 

Database altered.

 

SQL> alter database open resetlogs; 

Database altered.

 

SQL> select name,open_mode,instance_name from v$database,v$instance;


NAME      OPEN_MODE            INSTANCE_NAME

--------- -------------------- ----------------

UATCDB    READ WRITE           UATCDB





April 19, 2022

How to permanent change hostname in OCI for OEL 7 and later version

 

Step 1.

Update the /etc/hostname file following command.

 

[root@uat-database-dr rman_bkp]# hostnamectl set-hostname ccuine1126.xxx.co.in

 

 

 

 Step 2.

 Edit the oci configuration file for hostnames and set the value to 2.
    vi /etc/oci-hostname.conf
    PRESERVE_HOSTINFO=2
    save the changes.

 

Note:- By setting Value 2, it preserve user configured hostname across instance reboots; no custom changes to /etc/hosts and /etc/resolv.conf from the metadata service,

but dhclient will still overwrite /etc/resolv.conf

  

Step 3.

Update /etc/hosts file with following

192.168.10.126 ccuine1126.xxx.co.in        ccuine1126

And save & exit.

 

Step 4.

Update FQDN in OCI Console

Login to OCI console and navigate to compute instance


Select the compute instance where the FQDN will be updated and click on Attached VNICs and click on the Edit vnic 



This open a window like below where we need to set the updated hostname.

Note: Only provide the sub domain name . Do not enter entire FQDN as it doesn’t accept it.


And click on save changes. 

Once done, bounce the instance from and it will show updated FQDN from OCI console as well.

 Check the hostname with hostname or hostnamectl command.

 

[root@ccuine1126]# hostname

ccuine1126.xxx.co.in

 

[root@ccuine1126]# hostnamectl

   Static hostname: ccuine1126.xxx.co.in

         Icon name: computer-vm

           Chassis: vm

        Machine ID: aef0b6747ebeb75af2d053485bed

           Boot ID: 3a4b979bdf3b4a3591328acc3e362

    Virtualization: kvm

  Operating System: Oracle Linux Server 7.9

       CPE OS Name: cpe:/o:oracle:linux:7:9:server

            Kernel: Linux 5.4.17-2136.304.4.1.el7uek.x86_64

      Architecture: x86-64







April 12, 2022

Query to check currently running concurrent program in EBS R12.1.3

 

Login to apps user

 

sqlplus apps/<apps_passwd>

 

SELECT sess.sid,

   sess.serial#,

   oracle_process_id OS_PROCESS_ID,

   fusr.description user_name,

   fcp.user_concurrent_program_name progName,

   TO_CHAR (actual_Start_date, 'DD-MON-YYYY HH24:MI:SS')

   StartDate,

   request_id RequestId,

   (SYSDATE - actual_start_date) * 24 * 60 * 60 ETime

   FROM fnd_concurrent_requests fcr,

   fnd_concurrent_programs_tl fcp,

   fnd_user fusr,

   v$session sess

   WHERE fcp.concurrent_program_id = fcr.concurrent_program_id

   AND fcr.program_application_id = fcp.application_id

   AND fcp.language = 'US'

   AND fcr.phase_code = 'R'

   AND fcr.status_code = 'R'

   AND fcr.requested_by = fusr.user_id

   AND fcr.oracle_session_id = sess.audsid(+)

   ORDER BY 8;

 






Oracle Database 26ai Installation Using RPM on Oracle Linux 9 (OEL 9) – Step-by-Step Guide

  In this post I will describe the installation of Oracle Database 26ai 64-bit on Oracle Linux 9 (OL8) 64-bit. The installation requires a m...