Tag: oracle blogs

Data Masking In Oracle/Column Masking

Posted on by Osama Mustafa in Database
Or We Can Call it VPD : Virtual Private Database

What is Data Masking Mean ? 

simple way to hide you valuable data from certain users without having to apply encrypt/decrypt techniques and increase the column width to accommodate the new string like the old times. Through some simple configuration you can create policies to show your important columns as null without rewriting a single line of code on your application side.

There are 3 steps for accomplish column masking:

  1. A function to be used by the policy (function policy) created in next step.
  2. Use dbms_rls package to create the policy.
  3. Assign “exempt access policy” to users to be excluded from the policy. These users can see all data with no masking.

Step1 : Create Function Policy 

CREATE OR REPLACE
FUNCTION vpd_function (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN ‘rowid = ”0”’;
END vpd_function;

/

The Above Function is Used for Column Masking , If you set this function to True All User will be able to see the correct Data , But the above function Is to False (rowid=0).


Step2: Create Policy

BEGIN
DBMS_RLS.ADD_POLICY(object_schema=> ‘SCOTT’,
object_name=> ‘EMP’,
policy_name=> ‘scott_emp_policy’,
function_schema=> ‘SYSTEM’,
policy_function=> ‘vpd_function’,
sec_relevant_cols=> ‘JOB’,
policy_type => DBMS_RLS.SHARED_STATIC,
sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;
/

exempt access policy : Use to Exclude Some Users to See All the Correct Data .

Important Views :

dba_policies
v$vpd_policy

Enjoy with Security

Osama Mustafa

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