How to enable trace for a Session?

Use the below steps to trace the Sessions.

Step 1: Login as sysdba
$sqlplus “/as sysdba”
Step 2: Execute the below query to get sid and serial#
SQL> select sid, serial# FROM v$session where username = 'USER_NAME';
Step 3: Enable trace Session using the below
SQL>exec dbms_system.set_sql_trace_in_session(sid,serial#,TRUE)
e.g: SQL> exec dbms_system.set_sql_trace_in_session(100,2018,TRUE)
Step 4: Check the trace files from user_dump directory.
SQL> select value from v$parameter where name = 'user_dump_dest';
Step 5: Check the trace (.trc) files from user_dump_dest directory.
Step 6: Disable trace session using the below
SQL>exec dbms_system.set_sql_trace_in_session(sid,serial#,FALSE)
e.g: SQL> exec dbms_system.set_sql_trace_in_session(100,2018,FALSE)

Set Environment Variable for Multiple Databases

You can use the below method to Set Environment Variable for Multiple Databases.

Step 1: Edit the .profile with the following information.
########## --Script starts Here -- ###############
print
print "Available SIDs to choose from:"
cat /var/opt/oracle/oratab | grep -v ^#
print
#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very often. Only one SID is enough

TERM=vt220; export TERM
. $HOME/.All_Profile
export ORACLE_TERM=vt100;
stty erase ^h
#stty erase ^?
EDITOR=vi; export $EDITOR
set -o vi
########## --Script Ends Here -- ###############

Step 2: Create .All_Profile with the following information.

########## --Script starts Here -- ###############
ORAENV_ASK=YES
export ORAENV_ASK
#To Set ORACLE Base
export ORACLE_BASE=/export/home/oracle1
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

#Set Oracle SID
export ORACLE_SID=testdb -- enter the SID which you connect very ofte. Only one SID is enough
#Set PATH to executable directories
PATH=:$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/lib:/bin
export PATH
. oraenv
#Set PATH for LD_LIBRARY PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
# Set Oracle user umask
umask 022
# Ensure that TWO_TASK is not set
unset TWO_TASK
# Ensure that CLASS_PATH is not set
unset CLASS_PATH

export ORACLE_PATH=$HOME
########## --Script Ends Here -- ###############

Step 3: Logout the session and Login again.

Above steps I tested in Solaris Environment.

Rebuild Database

Description:
We can use the below approaches to rebuild the entire database.

Approach #1: For Smaller Database.
Step 1: Export the whole database using export utility (exp).
Step 2: Drop the whole database.
Step 3: Create the database.
Step 4: Import the whole database using import utility (imp).

Approach #2:
Step 1: Create the additional tablespace to move all the objects to this tablespace. e.g. if all the objects exists in “USERS” tablespace, we can create “USERS1” tablespace.
Step 2: Move all the objects to newly created additional tablespace using ALTER TABLESPACE command (REBUILD option).
Step 3: Again move all the objects to original tablespace.

Approach #3:
Step 1: We can use transportable tablespace to move one database to another database (only for 10g)

Approach #4:
Step 1: Take Hotbackup (Data files and control file backup) of the existing server. Rebuild the whole database using Hotbackup files.

Approach #5:
Step 1: Take Cold backup of the existing server. Rebuild the whole database using Cold backup files.

Note1: Approach #1 will avoid row chaining and fragmentation of the table and also easiest method to rebuild the whole database.
Note2: Approach #2 we need to additional space in servers. This method also avoid row chaining and fragmentation

Oracle Process Manager and Notification Server (OPMN)

Description:
Oracle Process Manager and Notification Server (OPMN) is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server.
Command List:
To Get opmnctl Help :
$opmnctl help
To Start OPMN:
$opmnctl start
To Start OPMN and all managed processes:
$opmnctl startall
To Stop OPMN and all managed processes:
$ opmnctl stopall
To get the OPMN status:
$opmnctl status
We can use the following commands to start or stop component level
To Start component Level Service:
$opmnctl startproc ias-component=SERVICE_NAME
e.g:
$opmnctl startproc ias-component=HTTP_Server
To Stop Component Level Service:
$opmnctl stopproc ias-componenet=SERVICE_NAME
e.g:
$opmnctl stopproc ias-component=HTTP_Server

Dependencies Error on Oracle Client Installation

Description:
I faced the below error at the time of Installation of Oracle BI Products Installation.
Error:
Dependencies
The ORACLE_HOME environment variable is currently set. This will prevent proper use of multiple Oracle Homes, and since it is not required for any Oracle products to function, it must be unset in your environment. Please remove this environment variable setting using the System Control Panel, restart the system, then restart the installation.
Solution I tried:
I removed the ORACLE_HOME Entry from Environment Variables.
My Computer -> Properties -> Advanced -> Environment Variables -> System Variables ->
Restarted BI Products Installation. It was successful.

Unable to View Procedure Source in TOAD - 'IN' is not a valid integer value

Description:
I unable to procedure source from Toad Schema browser. Below error message I got,
'IN' is not a valid integer value

Solution Tried:
Step 1: I logged in as Schema Owner
Step 2: Created the below view.
CREATE OR REPLACE FORCE VIEW schema_owner.all_arguments
AS
SELECT owner, object_name, package_name, object_id, overload,
argument_name, POSITION, SEQUENCE, data_level, data_type,
DEFAULT_VALUE, default_length, in_out, data_length, data_precision,
data_scale, radix, character_set_name, type_owner, type_name,
type_subname, type_link, pls_type, char_length, char_used,
subprogram_id
FROM SYS.all_arguments;
Step 3: Refresh schema browser.
Finally, I was able to view the procedure source

Script to Analyze whole Database

Description:
Below script can be used for Analyze Whole Database.

Script:

#!/bin/sh

. .profile

# Print Start Time
startTime=`date`
echo
echo "Starting Time $startTime"
Analyze1=/tmp/analyze.tmp
sqlplus -s system/manager << ! > $Analyze1
set pagesize 0
set feedback off
select 'analyze table', owner||'.'||table_name||
' estimate statistics;'
from dba_tables
where owner not in ('SYSTEM','SYS');
!
sqlplus -s system/manager << !
set feedback off
set pagesize 0
set echo on
set time on
set timing on
@$Analyze1
exit
!
# Print End Time
endTime=`date`
echo
echo "Analyze Schema Successfully Completed"
echo "Ending Time $endTime"