Prerequisite Check “CheckActiveFilesAndExecutables” Failed

While applying a patch to oracle binary using opatch utility, got below error.

Error - 

$ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.32

Copyright (c) 2022, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/19.0.0mydb

Central Inventory : /u01/app/oracle/product/19.0.0mydb/oraInventory

   from           : /u01/app/oracle/product/19.0.0mydb/oraInst.loc

OPatch version    : 12.2.0.1.32

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0mydb/cfgtoollogs/opatch/opatch2022-08-08_22-55-58PM_1.log

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckActiveFilesAndExecutables" failed.

The details are:

Following active files/executables/libs are used by ORACLE_HOME :/u01/app/oracle/product/19.0.0mydb

/u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1

/u01/app/oracle/product/19.0.0mydb/lib/libsqlplus.so

Log file location: /u01/app/oracle/product/19.0.0mydb/cfgtoollogs/opatch/opatch2022-08-08_22-55-58PM_1.log

OPatch failed with error code 73

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

The details are:

Following active files/executables/libs are used by ORACLE_HOME :/u01/app/oracle/product/19.0.0mydb

/u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1

/u01/app/oracle/product/19.0.0mydb/lib/libsqlplus.so

log file location: /u01/app/oracle/product/19.0.0mydb/cfgtoollogs/opatch/opatch2022-08-08_22-55-58PM_1.log

OPatch failed with error code 73

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

Solution - 

Following executables are active :

/u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1

/u01/app/oracle/product/19.0.0mydb/lib/libsqlplus.so

/sbin/fuser /u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1

/u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1: 29785m 

ps -ef |grep 29785

oracle 29785 29255 0 15:02:33 pts/2 0:00 sqlplus / as sysdba

kill -9 29785

Now Check again

/sbin/fuser /u01/app/oracle/product/19.0.0mydb/lib/libclntsh.so.19.1

Nothing for this executables...

Now check for another file -

/sbin/fuser /u01/app/oracle/product/19.0.0mydb/lib/libsqlplus.so

It will show any process id kill that also and keep checking till no process are running from those executables and then check again.

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

Now check again - 

$ORACLE_HOME/OPatch/opatch apply 

It will work.

Thanks

DB Tech Solutions Team

Oracle Server Description

What is Oracle Server ?

Oracle server includes an Oracle Database and Oracle Instance.

Oracle Database -

                                    Oracle database includes several different types of files.

                                            1. Data Files

                                            2. Control Files
                                            3. Redo Log Files

                                            4. Archive Log Files

                                            5. Parameter File

                                            6. Password File

                                            7. Alert Log Files

                                            8. Trace Log Files

The Oracle Server also accesses Parameter Files and Password Files. An Oracle database consists of files.

Sometimes there are referred to as operating systems files. But they are actually 'database files' that store the database information that a firm or organization needs in order to operate.

• The redo log are used ot recover the database in the vent of application program failure, instance failures and other minor failures.
• The archived redo log files are used to recover the database if a disk fails.
• The required parameter file that is used to specify parameters for configuring an oracle instance when it starts up.
• The optional password file authenticates special users of the database - these are termed privileged users and include database administrators.
• 'Alerts' and 'Trace Log Files' - these file store information about errors and actions taken that affect the configuration of the database.

Oracle Instance -

                                    An oracle instance consists of two different sets of components -

                                    1. Background Processes

                                    2. Memory Structures

Background Processes -

                                    SMON, PMON, DBW0, LGWR, CKPT, RECO, D000 and others.

These Processes perform input/output and monitor other oracle processes to provide good performance and database reliability.

Memory Structures -

                                When an instance starts up, a memory structure called the System Global Area (SGA) is allocated.

At this point the background processes also starts. An oracle instance provides access to one and only one oracle database.

User and Server Processes - 

                            The processes shown in the figure are called ' User' and 'Server' Processes. These processes are used to manage the execution of SQL Statements.

A Shared Server Process can share memory and variable processing for multiple user processes.

A Dedicated Server Process manages memory and variable for a single user process.

Connecting to an Oracle Instance - Creating a session -

Connecting to an oracle instance

        - Establishing a user connection

        - Creating a session

System  User connect to an oracle database through SQLPlus or through an application program like Internet Developer Suite. This connection enables users to execute SQL statements. The User process communicates with the oracle server through a server process. The User process executes on the client computer. The Server Process executes on the server computer, and actually executes SQL Statements submitted by the system user. 
The figure shows a one-to-one correspondence between te user and server process. This is called a 'Dedicated Server' connection, An alternate configuration is to use a 'Shared Server' where more than one user process shares a Server Process.
Sessions - When a User connects to an Oracle server, this termed a session. The 'User Global Area' is session Memory.

Note - If a system users attempts to connect and the Oracle server is not running, the system user receives the "Oracle Not Available" error message.

Thanks

DB Tech Solutions

 Oracle Database Complete Architecture -

Thanks

DB Tech Solutions 

Oracle Database architecture

An Overview of Oracle Database Architecture

Thanks

DB Tech Solutions Team

How to install 11g Prerequities packages in Linux System

Run below command from root user -

yum install oracle-rdbms-server-11gR2-preinstall

Thanks

Tech Solutions Team

How to create alias in Oracle Database

How to create alias in oracle database -


. oraenv
<sid>

alias ua_dba='sqlplus "/ as sysdba"'
alias ua_oper='sqlplus "/ as sysoper"'
alias ua_sp='sqlplus "/ as sysdba"'
alias ua_pfile='cd ${ORACLE_HOME}/dbs'
alias ll='ls -alrt'
alias ua_diag='cd `orabase`/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace'
alias ua_alert='tail -100 `orabase`/diag/rdbms/${ORACLE_SID,,}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log'
alias ua_lsinv='${ORACLE_HOME}/OPatch/opatch lsinventory -invPtrLoc ${ORACLE_HOME}/oraInst.loc'
alias ua_util_cleanup='${ORACLE_HOME}/OPatch/opatch util cleanup -invPtrLoc ${ORACLE_HOME}/oraInst.loc'
alias ua_dgconf='dgmgrl / "show configuration;"'
alias ua_dbinfo='echo -e "col name for A10 \n select name, open_mode, database_role, log_mode, current_scn from v\$database;" | sqlplus -s / as sysdba'

Thanks

DB Tech Solutions Team

Oracle Database Physical files

Oracle Database Physical File Structure-

1. Data files -
Each tablespace in an Oracle database consists of one or more files called datafiles.

Note : - (All tablespace having there datafiles is depends upon the tablespace required size.)

2. Redo log files -
Redo log is used for recovery a database after Failures like hardware, software, or media failure.
Note :- (Minimum 2 redo log groups with one member in each group, Oracle recommanded 3 redo log groups with 2 members with multiplexing.)

3. Control files -
Control file is a small binary file that records the physical structure of the database.
The control file includes:
1. The database name.
2. Names and locations of associated datafiles and redo log files.
3. The timestamp of the database creation.
4. The current redo log sequence number.
5. Checkpoint information.

Note :- (Minimum 2 controlfiles are required to run a database, Oracle recommanded 3 controlfiles with multiplexing.)

Thanks
DB Tech Solutions

Linux: Set Environment Variable

Step -1

Need to vi (Editor) to .bashrc

vi .bashrc

Now check prior to add it is showing blank 

Step -2

Add the environment variable which we want to add like

export my_path=/home/user

Now save and exit

Esc
:wq!

Now update the .bashrc

. .bashrc  enter

Now check echo $my_path








All Steps -

1. vi .bashrc
2. export my_path=/home/user
3. Esc :wq!
4.  . .bashrc
5. echo $my_path

Thanks
DB Tech Solutions

Show all installed packages or software in Linux all flavours...

Below mentioned command to get list of softwares and packages installed on linux operation system with all flavours -

First need to root access or sudo su with sudo access user -


Red Hat/Fedora Core/CentOS Linux
# rpm -qa | less

Core/CentOS Linux
# rpm -qa | less

Debian Linux
# dpkg --get-selections

Ubuntu Linux
# sudo dpkg --get-selections

FreeBSD
# pkg_info | less
# pkg_info apache

pkg_version command to summarises the versions of all installed packages
# pkg_version | less
# pkg_version | grep 'lsof'


OpenBSD
# pkg_info | less
# pkg_info apache

Thanks
DB Tech Solutions

How to export between releases of oracle

Below script to export from 12c and import that dump into 11g to use version parameter -


expdp user/password@db directory=<directory_name> dumpfile=<dumpfile_name>.dmp logfile=<logfile_name>.log tables=<table_name> job_name=<job_name> version=11.2

how to import multiple partition using impdp

Dump for entire schema available now we require to import 3 partition only into the another schema

Script-

impdp test/test
directory=dumpfile
dumpfile=user_full_schema.dmp
logfile=multiple_partition_import_remap_schema.log
tables=user.tablename1:partitionname1,user.tablename1:partitionname2,user.tablename1:partitionname3
remap_schema=user:test


Note: It will create a table with all partitions but data will import only above mentioned partitioned. Don't Worrry..

If any additional help is required Please write us at : dbtechsolutions17@gmail.com

Thanks
DB Tech Solutions

Voting Disk

Voting Disk in Oracle RAC

Voting Disk, is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster registers their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the  split-brain syndrome.

For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of voting disks.


Thanks
DB Tech Solutions

/bin/rm: Argument list too long

$ rm test_*.aud
-bash: /usr/bin/rm: Argument list too long



for i in /u01/app/oracle/admin/test/adump/test_*.aud            -->>This is the path of the file where he is located.
do
echo rm $i >> /u01/app/oracle/admin/test/adump/test.log -->>This is the parameter which generate the log file which we can check which file is going to be deleted.
rm $i    -->>It will delete the files to read $i where in "i" we have inserted the file with first command.
done

Thanks

DB Tech Solution

ORA-14460: Only one COMPRESS or NOCOMPRESS clause may be specified

ORA-14460: only one COMPRESSor NOCOMPRESS clause may be specified


Just use "transform=segment_attributes:n" parameter in import

impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept -->>Error

ORA-14460: only one COMPRESSor NOCOMPRESS clause may be specified.........

Two solutions are there -

Solution -1
impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept transform=segment_attributes:n


Solution -2
Just create a table prior to import, Get the script from above dump with use parameter "sqlfile" or the db where we have exported that table then

impdp user/pass@ directory=dump_loc dumpfile=users_tb.dmp logfile=users_tb.log tables=dept table_exists_action=append



Thanks
DB Tech Solutions

How to find default Permanent Tablespace

Query to Find Default Permanent Tablespace seperately -

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';


Query to Find Default Temporary Tablespace seperately -

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

Query to Find Both Default Tablespaces (Permanent and Temporary)

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');


Thanks

set sqlplus prompt name same as instance name

How to set sqlplus prompt name same as Database/Instance name


We need to change one file called glogin.sql, steps are as below...

Path where glogin.sql 

cd $ORACLE_HOME/sqlplus/admin

/u01/app/oracle/product/12.1.0/dbhome_1/sqlplus/admin

set below mention line at the end of the file

vi glogin.sql

SET SQLPROMPT 'PROD-SQL>'

save and exit

Now next login you can see your db name like PROD

Thanks

-bash: /usr/bin/rm: Argument list too long

To delete a large number of files we have to use loop in shell script like below-

for i in /u01/app/files/*.txt
do
echo rm $i >> /u01/app/files/output.log -->>this will generate the logfile which we can check which files is going to be deleted
rm $i
done

Thanks

ORA-00059: maximum number of DB_FILES exceeded

How to increase database files in Oracle Database -

SQL> show parameter db_files;

NAME      TYPE       VALUE
------------------------------------ -------------------------------- ------------------------------
db_files      integer       200 (Default Value)


SQL> alter system set db_files=300 scope=spfile;

SQL> shu immediate;

SQL> startup

SQL> show parameter db_files;

NAME      TYPE       VALUE
------------------------------------ -------------------------------- ------------------------------
db_files      integer       300



Thanks

How reclaim the space after a large delete in oracle

10 Steps to reclaim the space in oracle -

1. CREATE TABLE USER.USER_TB AS SELECT * FROM ...


2. Now check the size of that created table -

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
  13631488


3. Now check the tablespace size which using that segment -

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14 .875       6 94


4. Now delete the records from the table -

SQL> delete from USER.USER_tb;

91108 rows deleted.


5. Again check the size of that created table on which we have deleted the records -
Note - As we can see there is not space released after to delete the records..

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
  13631488

6. Again check the tablespace size which using that segment -
Note - As we can see there is not space released after to delete the records..

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14 .875       6 94


7. Now to shrink the table but before to that we need to enable row movement else it will throws an error -

SQL> ALTER TABLE USER.USER_TB ENABLE ROW MOVEMENT;

Table altered.

8. Now space reclaim -

SQL> ALTER TABLE USER.USER_TB SHRINK SPACE CASCADE;

Table altered.

9. Now check the size of that created table on which we have deleted the records -
Note - It is showing size has reclaimed..

SQL> SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER='USER' AND SEGMENT_NAME='USER_TB';

SUM(BYTES)
----------
     65536

10. Now check the tablespace size which using that segment -
Note - It is showing size has reclaimned..

Tablespace Size (MB)  Free (MB) % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
USER_TABLESPACE        14    13.8125      99 1

Thanks

DB Tech Solutions