Monday, January 30, 2012

Change Listener Port For Oracle RAC

I think its useful to document this since maybe i will need it too :)


2 Node , Linux  .....

RAC database name: ORCL
Node 1 vip: myhost1-vip
Node 2 vip: myhost2-vip
Instance 1: ORCL1
Instance 2: ORCL2
New port: 1522


1) First, use netca to adjust the the listener port from 1521 to 1522. It's a good idea to use netca to do this cluster-wide and keep the OCR in check.  
NOTE: This will shutdown the listeners across both nodes.


2) In the tnsnames.ora for ASM and RDBMS, ensure the following entry exists (they'll probably be there but with the 1521 port, so adjust accordingly):

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost1-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost2-vip)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost2-vip)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = myhost1-vip)(PORT = 1522))

Connect to each instance and set the local_listener to parameter to either LISTENER_MYRACDB1 or 2 depending on the instance you connect to. Ensure you do this so it applies to the instance you are connected to only, and does not apply globally.

e.g. if connected to ORCL1

alter system set local_listener='LISTENER_ORCL1' sid='ORCL1';

4) Set the remote_listener parameter to LISTENERS_MYRACDB across all instances

e.g. alter system set remote_listener='LISTENERS_ORCL';

5) Repeat the above for both ASM instances

6) You should be able to start the listener on each node now.

e.g. srvctl start listener -n myhost1 

Thank you

Note :

On Single node instance all you need to do is
1-stop the listener
lsnrctl stop

2-Go to $ORACLE_HOME/network/admin/listener.ora
modified the port for new one .

3-start the listener
lsnrctl start


Osama mustafa
Oracle Database consultant

Tuesday, January 17, 2012

Resize Redolog On RAC Instance

Before I Posted "How To Resize redo log " This was on Single Instance But Now I show you how to do it on Oracle Real Application Cluster

1.First Run this Query To check size, redo On your RAC :

select, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f
where =
order by 1,2

2. Now our purpose is increase redosize from 100Mb to 300 Mb

3. We are going to add our new redo file by below command:

alter database add logfile group 5 ‘+ORADATA’ size 300M;    <<   node 2
alter database add logfile group 6 ‘+ORADATA’ size 300M;    <<   node 2
alter database add logfile group 7 ‘+ORADATA’ size 300M;    <<   node 1
alter database add logfile group 8 ‘+ORADATA’ size 300M;    <<   node 1

5. Switch until we are into log group 5,6,7,8 so we can drop log groups 1, 2,3 and 4 Until you see the new redo log used .

Thank you
Osama mustafa

Resize Redo Log

Here is a newly created database redo log information:

SQL> select * from v$log;

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;

---------- ------- ------- -------------------------------------------------
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 ('/database/data/redo04.log') size 200M;
SQL> alter database add logfile group 5 ('/database/data/redo05.log') size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/database/data/redo03.log'

you should run " alter system switch logfile;" until current log is 4 or 5. Then execute "alter system checkpoint;"

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log


SQL> alter database add logfile group 1 ('/database/data/redo01.log') size 200M;
SQL> alter database add logfile group 2 ('/database/data/redo02.log') size 200M;
SQL> alter database add logfile group 3 ('/database/data/redo03.log') size 200M;

Monday, January 16, 2012

Linux Command On Windows :)

Install Cygwin

Cygwin comes with a normal setup.exe to install in Windows, but there are a couple steps you will need to pay attention to, so we will walk you through the installation.

To keep the installation small while saving bandwidth for you and Cygwin, the default installer will download only the files you need from the internet.

The default install path is C:\Cygwin

Click next until you come to a download mirror selection. Unfortunately, the installer does not say where the mirrors are located so in most cases you might as well just guess which mirror works best.

After you have selected a mirror, the installer will download a list of available packages for you to install. Here is where things get a bit more intimidating.
There will be hundreds of packages available separated by multiple different categories. If you don’t know what the package is you can leave the default selection and install additional packages later by running the installer again.

If you know what package you need, you can search for it and the results will be automatically filtered.

Once you click next, it will take a little while to download all the selected tools and then finish the installation.

Add Cygwin Path to Windows Environment Variable

In the left column click on advanced system settings to open the system properties window we are looking for

From the advanced tab, click on environment variables at the bottom.

Then in the system variables, locate the path variable and click edit.

At the end of the variable value option, add the Cygwin bin location like so.
Note: Make sure you add a semicolon to separate it from the other values.

Click OK to close out of the window and then open a command prompt to test out a few Linux commands.


Osama Mustafa

Monday, January 9, 2012

Lot Of people Asked About DDL,DML,DCL So i Post it as new topic


Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
  • CREATE - to create objects in the database
  • ALTER - alters the structure of the database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object


Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
  • SELECT - retrieve data from the a database
  • INSERT - insert data into a table
  • UPDATE - updates existing data within a table
  • DELETE - deletes all records from a table, the space for the records remain
  • MERGE - UPSERT operation (insert or update)
  • CALL - call a PL/SQL or Java subprogram
  • EXPLAIN PLAN - explain access path to data
  • LOCK TABLE - control concurrency


Data Control Language (DCL) statements. Some examples:
  • GRANT - gives user's access privileges to database
  • REVOKE - withdraw access privileges given with the GRANT command


Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
  • COMMIT - save work done
  • SAVEPOINT - identify a point in a transaction to which you can later roll back
  • ROLLBACK - restore database to original since the last COMMIT
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Thank You
Osama mustafa

Sunday, January 8, 2012

Threats to Database Security Part Two

There's lot kind of database threats but we going to talk about the top 10 popular threats today in database

Top Ten Database Security Threats:

1. Excessive Privilege Abuse.
2. Legitimate Privilege Abuse.
3. Privilege Elevation.
4. Database Platform Vulnerabilities.
5. SQL Injection.
6. Weak Audit Trail.
7. Denial of Service.
8. Database Communication Protocol Vulnerabilities.
9. Weak Authentication.
10. Backup Data Exposure.

Now let's talk about every one of these threats shortly since I take lot time talking about Database
Security , as we see the previously we can merge the ten point in only five point since they are
talking on the same subject so it going to be like this :

My Five Point for Database Threats:
1. Privilege abuse.
2. Operating System vulnerabilities.
3. Database root kits.
4. Weak authentication.
5. Weak audit trails

Types of threats to database security

1. Privilege abuse:

When database users are provided with privileges that exceed their day-to-day job requirement, these privileges may be abused intentionally or unintentionally.Take, for instance, a database administrator in a financial institution. What will happen if he turns off audit trails or create bogus accounts? He will be able to transfer money from one account to another thereby abusing the excessive privilege intentionally.Having seen how privilege can be abused intentionally, let us see how privilege can be abused unintentionally. A company is providing a “work from home” option to its employees and the employee takes a backup of sensitive data to work on from his home. This not only violates the security policies of the organization, but also may result in data security breach if the system at home is compromised.

In this the pervious picture I connect as normal user but if we check the privileges you will know he takes all DBA (database administrator) privileges and that so wrong , actually there's 161 privileges was given to this normal user but I didn't know how to display it all in this picture .

2. Operating System vulnerabilities:

Vulnerabilities in underlying operating systems like Windows, UNIX, Linux, etc., and the services that are related to the databases could lead to unauthorized access. This may lead to a Denial of Service (DoS) attack. This could be prevented by updating the operating system related security patches as and when they become available.This example: the below test show the advantages and disadvantages for Oracle database on windows and Linux once you installed it.

  • Advantages of Oracle UNIX:
          o Significant performance improvement
          o Provides High Availability
          o Contains in-depth system utilities and open-source code
          o Highly respected by Oracle personnel

  • Advantages of Oracle Windows:
         o Very easy to deploy and support
         o Requires far less IT training
         o Simple interface to Microsoft tools such as ODBC and .NET.

And sure there's Disadvantage for both operating system

  • Disadvantages of Oracle UNIX:
        o Required specialized skills (vi editor, shell scripting, etc.)
        o Required highly-technical Systems Administrators and DBA
        o Contains in-depth system utilities and open-source code
        o Security Holes (if mis-configured)
        o Susceptible to root kit attacks

  •  Disadvantages of Oracle Windows:
        o Slower than Linux
        o Less glamorous for the SA and DBA
        o History of poor reliability (bad reputation)
        o Security Holes (if mis-configured)
        o Susceptible to Internet viruses

But if you need to keep your computer fast without effecting on your work then we talk about linux but to make your computer slower then we talking about Windows. Because you don't have any idea about how oracle database need resources.

3. Database root kits:

A database root kit is a program or a procedure that is hidden inside the database and that provides administrator-level privileges to gain access to the data in the database. These root kits may even turn off alerts triggered by Intrusion Prevention Systems (IPS). It is possible to install aroot kit only after compromising the underlying operating system. This can be avoided by periodical audit trails; else the presence of the database root kit may go undetected.

Some day while I am doing my job as Oracle Database Consultant, I found this produce hidden in
database for company (Guys without name please); and guess what? Its work after the DBA who
responsible for this database resign from his work as Job (in specific date and time) .it will me
disaster because this is two major table in the company .

4. Weak authentication:

Weak authentication models allow attackers to employ strategies such as social engineering and brute force to obtain database login credentials and assume the identity of legitimate database users.Look for the following figures it's contain username/password for some users but unfortunately it's too weak and so easy to Decryption and in some cases it's contain only one letter.

Weak audit trails:

A weak audit logging mechanism in a database server represents a critical risk to an organization
especially in retail, financial, healthcare, and other industries with stringent regulatory compliance. Regulations such as PCI, SOX, and HIPAA demand extensive logging of actions to
reproduce an event at a later point of time in case of an incident. Logging of sensitive or unusual
transactions happening in a database must be done in an automated manner for resolving
incidents. Audit trails act as the last line of database defense. Audit trails can detect the existence
of a violation that could help trace back the violation to a particular point of time and a particular

In below picture we the DBA has been disable something called Audit trial (Monitoring the database)

The Audit trial is features in the database give you monitor the database and when I talk about monitoring i mean save the query, update and insert (For each user in the database). In this case we enable audit Trail with DB attribute.

Even if you any one connect to database you can see when; user test connect to database and lets see how the DBA can manage this???

Finally Thank you to give me this change to express and share my knowledge with others Professional I.T people .

Thank you 
Osama mustafa 

Follow me On Twitter Or Linked-In