Tag: Osama

dbstart script/ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

Posted on by Osama Mustafa in Database 

 
-bash-3.2$ dbstart
 
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
 
And i try to do the following 
 
-bash-3.2$ export ORACLE_HOME_LISTNER=$ORACLE_HOME
-bash-3.2$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
Open dbstart and check the Scripts 
 
# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
  echo "Usage: $0 ORACLE_HOME"
 
 
Try the following Solution , over ride value inside dbstart with the below command 
 
-bash-3.2$ dbstart $ORACLE_HOME
 
Enjoy 
 
Osama mustafa
 
 

Dealing With Oracle Jobs/DBMS_JOB

Posted on by Osama Mustafa in Database
scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

running_jobs.sql

set linesize 250
col sid for 9999 head ‘Session|ID’
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

session_jobs.sql

set linesize 250
col sid for 9999 head ‘Session|ID’
col spid head ‘O/S|Process|ID’
col serial# for 9999999 head ‘Session|Serial#’
col log_user for a10
col job for 9999999 head ‘Job’
col broken for a1 head ‘B’
col failures for 99 head “fail”
col last_date for a18 head ‘Last|Date’
col this_date for a18 head ‘This|Date’
col next_date for a18 head ‘Next|Date’
col interval for 9999.000 head ‘Run|Interval’
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||’:’||j.last_sec last_date,
j.this_date||’:’||j.this_sec this_date,
j.next_date||’:’||j.next_sec next_date,
j.next_date – j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won’t start again. Let’s make one thing perfectly clear, after executing this command the job is still running.

3. Kill the Oracle Session

ALTER SYSTEM KILL SESSION ‘sid,serial#’;

 4. Kill the O/S Process

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill ‘9 spid

5. Check if the Job is Still Running

If No , Then you are Done , But if Job Still Running Go To Step 6.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = ‘job_queue_processes’; 

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

 8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

Enjoy

Osama mustafa

Recursive Calls In Oracle

Posted on by Osama Mustafa in Database
you will find Recursive Call in AWR at most , and you have to understand what we mean in this , why we use it , Simple way to discuss this . its below .

WHAT ARE RECURSIVE CALLS?

In Oracle, a recursive call is a call, for example, that must be completed before the user’s SQL can be completed.

Say you wanted to order a pizza for delivery. You make one call, place your order, and wait for your pizza.

You do NOT call the kitchen staff directly and tell them how to make your pizza , call the cashier to give him your credit card number, call the delivery man and tell him to pick up your pizza and bring it to your house.

Nope, you make one call, not three. The person you place your order with makes those “recursive” calls to the kitchen and delivery staff to make sure your order is complete.

The kitchen staff may make additional “recursive” calls (“Where are the mushrooms?”) as may the delivery man (“Where is that street located?”).

You don’t care about these details, you just want your pizza.

WHAT KINDS OF RECURSIVE CALLS DOES ORACLE MAKE?

As you can tell from our pizza example, Oracle will make a recursive call whenever it has to.

Some examples of when it “has to” include:

you want to insert data into a DMT, but all extents are full and news ones need to be allocated to hold your data

you’re doing something on a table that causes a trigger to fire (e.g. – don’t allow inserts if it’s Saturday)

performing DDL

data dictionary cache needs info about objects during parsing .

you have PL/SQL units that include SQL statements (like a stored procedure that inserts into a table, or a function that selects from a table)

TUNING RECURSIVE CALLS???

I hope you can see from this brief discussion that the concept of tuning recursive calls is not as easy as configuring the Recursive Program Interface (RPI).

Tuning depends on several factors, the two biggest being what are you trying to do and what version are you on?

Sometimes recursive calls indicate a problem, sometimes they don’t. Can you see why?

I hear and am asked about the notion that recursive calls should always be less than “real” calls. I ask “Why?”

If I have a db app that only accesses the db via stored procedures and each of my procedures issues an average of 10 DML statements within it, then why would a 10/1 ratio be bad?

But that’s not to say that analysing recursive calls is not an important tuning step. You need to know what your database is doing and why. Recursive calls could be a hidden problem.

Thank you
osama mustafa

ORA-00604: error occurred at recursive SQL level 1

Posted on by Osama Mustafa in Database
Example :

SQL> alter user u1 identified by u1
  2  / 
alter user u1 identified by u1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6
 

Solution :

SQL> alter system set "_system_trig_enabled"=FALSE;
 
System altered.
 
 
Enjoy 
 
Osama Mustafa

Oracle Secuirty Tips / SQLNET.ORA Part 2

Posted on by Osama Mustafa in Database
Hi All ,

I post before about sqlnet.ora with parameter called invited_list , Exclude_list , assume  that i want to prevent sysdba to access database without password Simple Way .

SQLNET.AUTHENTICATION_SERVICES=NONE 



Setting “SQLNET.AUTHENTICATION_SERVICES” parameter to “NONE” in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba)

This parameter may also have the values : NTS for Windows NT native authentication, ALL for all authentication methods.

Authentication Methods Available with Oracle Advanced Security:

  • kerberos5 for Kerberos authentication
  • cybersafe for Cybersafe authentication
  • radius for RADIUS authentication
  • dcegssapi for DCE GSSAPI authentication

If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

Enjoy

Thank you
Osama Mustafa

Script to Restart the OEM Agent When It Has Failed

Posted on by Osama Mustafa in Scripts
Gaurav Batta has published this nice script to restart the OEM agent when it has failed:

$ cat agent_check.sh
#!/usr/bin/ksh
#
agent_status=`ps -ef|awk ‘{print $8}’|grep agent10g/bin/emagent`

if [[ -z $agent_status ]]
then
$AGENT_HOME/bin/emctl start agent
echo Agent started on server `hostname` at `date`
else
echo Agent is running fine on server `hostname` at `date`
fi

It is running every 30min.
crontab –l

00,30 * * * * /home/oemagent/agent_check.sh >> /home/oemagent/agent_status.log




Enjoy 


Thank you
Osama Mustafa

Tracking Alert Log file by x$dbgalertext

Posted on by Osama Mustafa in Database
First i would thank CKPT for this amazing article that post in his blog .

Instead of reviewing whole alert log file for any Particular information, We can search using that key word from X$DBGALERTEXT Table.

Grep The word “Starting Up” of Alert log file from Instance. 



col ORIGINATING_TIMESTAMP for a40
col MESSAGE_TEXT for a80
set linesize 500
SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Starting up%';

Grep The word “Instance shutdown complete” of Alert log file from Instance.

SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Instance shutdown complete%';

again thank you CKPT for this article since its useful for all DBA’s , you can check CKPT Blog From the above link

Thank you
Osama mustafa
 

WFMLRSVCApp.ear not found

Posted on by Osama Mustafa in Database
I installed Oracle 11g and the installation didn’t find WFMLRSVCApp.ear file. I extracted two files (win64_11gR2_database_1of2.zip and win64_11gR2_database_2of2.zip) in two different folder disk1 and disk2. 

I solved the file not found issue by coping all the folders under disk2databasestageComponents to disk1databasestageComponents.

After restarting the installation it went like a charm and without any issues.

Or Maybe the error will be appear like this :

The same : ou should extract each of them and place win32_11gR2_database_2of2 content into win32_11gR2_database_1of2 folder before running installer.

Enjoy
 
Thank You 
Osama Mustafa

Flash_Recovery_Area

Posted on by Osama Mustafa in Database
Mointer Space in Flash_recovery_are

1- sqlplus / as sysdba
2- Run the Below Query 

SELECT 
    NAME, 
    TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
    TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999')
       AS SPACE_AVAILABLE,
    ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
       AS PERCENT_FULL
    FROM V$RECOVERY_FILE_DEST;

 There’s many scripts you can use to monitor FRA Or Arvhive I will post Few Of them :

Select file_type, percent_space_used from v$flash_recovery_area_usage;
SELECT NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
 
 

 Query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the Flash Recovery Area. For example :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME                        SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\Oracle\flash_recovery_area 838860800  292490752                 0              44

Query the V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files. Also, you can determine how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape. For example :

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     2                         0              22
ARCHIVELOG                 4.05                      2.01              31
BACKUPPIECE                3.94                      3.86               8
IMAGECOPY                 15.64                     10.43              66
FLASHBACKLOG                .08                         0               1
 

You Can check 
Flash Recovery area – Space management Warning & Alerts [ID 305812.1]

 thank you
Osama Mustafa