Tuesday, March 24, 2015

Application Security - Database (Oracle)

more links on Application Security - Database (Oracle)

Monday, October 6, 2014

General links (Life beyond Oracle)

Hadoop FAQ – But What About the DBAs?

A few pages For MongoDB

A few pages for Ingres (INteractive Graphics REtrieval System)

A few pages for PostgreSQL

A few pages For SQL SERVER

A few pages for Powershell

A few pages for MySQL

A few pages for AWS

Tips for Searching Jobs in Singapore

Immigration to Australia

Tuesday, July 22, 2014

Here are few links/ pages for new/beginner DBAs

System Administration/ Unix Scripts

Monitoring CPU output on Solaris and report in excel
tail and grep tools for DBA
Good Linux / Unix System Admin Commands for DBA
Check Hardware configuration of Linux Box
grep next / previous few lines of key word
Collect and report Linux System Activity Information with sar
cpu vs core vs sockets - calculation 
Useful Shell commands and Scripts

Oracle Golden Gate


Oracle DBA 


Check the distribution of session on all the nodes 


SQL, PL/SQL , Automation

To put all the datafiles of the database or a tablespace in autoextend mode
Move table and dependant LOB to other tablespace
Converting Numbers to millions and more ...
Historical growth of tablespace
for the 1st time database users - How to start the database
Adding name of the table in the output from multiple tables

Performance Tuning

Explain plan for SQLs
Locks and Waits
Analyzing a Statspack Report
Schedule snapshot collection in Statspack reports
Local Indexes for Partitioning
Check maximum memory utilization for oracle Database
Identify Top CPU / Memory intensive process and map to corresponding Oracle Process/ Session
Identify Oracle Process ID and related SQL
check fragmentation of table
Identify session waiting on enq: TX - row lock contention 
Identify Sessions using an Object/ many Objects 
Detection of Deadlock errors and investigation


Oracle DBA Interview Questions
Oracle 11gr2 Database Reference
Configure FGA ( Fine Grain Auditing)
Favorite Oracle Metalink Notes
Renaming table: How does that affect the associated objects?
How to read Alert and Listener logs from SQLPLUS
Resize datafile and enable autoextend
Check Object Inventory & recently created objects
Life Without AWR
Stopping Oracle Services on Windows
Startup failure ORA-00845: MEMORY_TARGET not supported on this system
Check Tablespace Utilisation



Peformance Testing

Wednesday, November 3, 2010

Cloud Computing

This page captures the brief view of cloud services

Following are some commonly used terms:
Cloud Computing (wikipedia)
Broadly, servers, virtualisation software, networking and storage are required to form cloud. Following components form a complete cloud:
· Scalable, powerful and cheap servers
· Integrated, virtualised, and hugely scalable storage
· Virtualised 10GbE network switching infrastructure
· Fibre Channel over Ethernet capability to link servers and storage across Ethernet
· Disaster recovery capabilities
· Service-quality tracking and control
· Management of a virtualised data centre
Cloud architecture was used by Web 2.0 but due to changing economies more enterprises are adopting it due to its pay-per-usage model. Virtualization enables cloud to offer more services than cloud could do without
virtualization, e.g. PaaS.
Private Cloud
Smaller cloud than a conventional one. It is cloud like IT systems within a firewall offering similar services, but
to a closed internal network. A private cloud can be expanded to contain resources from within an enterprize (on premise) as well as from external cloud (off premise).
Public Cloud
Cloud available to anyone over the internet. Examples are, Amazon Elastic Compute Cloud (EC2), IBM's Blue Cloud, Sun Cloud, Google AppEngine and Windows Azure Services Platform.
Service Provider
Entity providing cloud services to others.
Entity consuming a cloud service.
Cloud Services
There are various cloud services:
Infrastructure as a Service (IaaS) (wikipedia)
Service user gets infrastructure to run application without spending on hardware (saving CapEx),
e.g. a configured VM.
Platform as a Service (PaaS) (wikipedia)
Service user gets platform to develop/run applications on cloud without spending on cloud infrastructure, e.g. Azure, Google AppEngine, Force.com
Software as a Service (SaaS) (wikipedia)
Service user gets a software to use without spending on software license and hardware, e.g. eXpresso, Google Apps
Storage as a Service (STaaS)
Service user gets storage to use without spending on storage hardware, e.g. Synaptic, Mozy, Box.net, also T-systems, BT, etc offer storage services for enterprizes.
NOTE: Synaptic service is offered by AT&T, a telco. It makes perfect sense for a telco to offer such a service because they already have network to deliver the service. So, they can offer better value for money for the subscribers.

New Security Features in Oracle 11g

Finding User Accounts That Have Default Passwords

When you create a database in Oracle Database 11g Release 1 (11.1), most of its default accounts are locked with the passwords expired. If you have upgraded from an earlier release of Oracle Database, you may have user accounts that have default passwords. These are default accounts that are created when you create a database, such as the HR, OE, and SCOTT accounts.
For greater security, change the passwords for these accounts. Using a default password that is commonly known can make your database vulnerable to attacks by intruders. To find both locked and unlocked accounts that use default passwords, log onto SQL*Plus using the SYSDBAprivilege and then query the DBA_USERS_WITH_DEFPWD data dictionary view.
For example to find both the names of accounts that have default passwords and the status of the account:

Enter password: password  

SELECT d.username, u.account_status 


WHERE d.username = u.username ORDER BY 2,1;  

--------- --------------------------- 
Then change the passwords for any accounts that the DBA_USERS_WITH_DEFPWD view lists. Oracle recommends that you do not assign these accounts passwords that they may have had in previous releases of Oracle Database.
Automatically Locking a User Account After a Failed Login
Oracle Database can lock a user's account after a specified number of consecutive failed log-in attempts. You can set the PASSWORD_LOCK_TIME user's profile parameter to configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator also can lock accounts manually, so that they must be unlocked explicitly by the database administrator.
You can specify the permissible number of failed login attempts by using the CREATE PROFILE statement. You can also specify the amount of time accounts remain locked.

Password Case Sensitivity

In previous releases of Oracle Database, passwords were not case sensitive. If you import user accounts from a previous release, for example, Release 10g, into the current database release, the case-insensitive passwords in these accounts remain case insensitive until the user changes his or her password. If the account was granted SYSDBA or SYSOPER privilege, it is imported to the password file.When a password from a user account from the previous release is changed, it then becomes case sensitive.

You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS view. The PASSWORD_VERSIONS column in this view indicates the release in which the password was created. For example:

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
JONES                          10G 11G
ADAMS                          10G 11G
CLARK                          10G 11G
PRESTON                        11G
BLAKE                          10G
The passwords for accounts jonesadams, and clark were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for preston. However, the account for blake is still using the Release 10g standard, so it is case insensitive. Ask him to reset his password so that it will be case sensitive, and therefore more secure.

Ensuring Against Password Security Threats by Using the SHA-1 Hashing Algorithm

The SHA-1 cryptographic hashing algorithm protects against password-based security threats by including support for mixed case characters, special characters, and multibyte characters in passwords. In addition, the SHA-1 hashing algorithm adds a salt to the password when it is hashed, which provides additional protection. This enables your users to create far more complex passwords, and therefore, makes it more difficult for an intruder to gain access to these passwords. Oracle recommends that you use the SHA-1 hashing algorithm.
Many password cracking tools rely on access to the Oracle Database data dictionary. The tool must first obtain the hash values of the password by using an administrator account or by gaining direct access to the hash values that are stored on media such as backup tapes or disk drives containing database files. (For this reason, it is a good idea to encrypt backup media that contains database files.) The cracking tools then use clear text password combinations to create the new hash, match the new hash with the existing hash, and thus obtain an existing password.
You optionally can configure Oracle Database to run in exclusive mode for Release 11 or later. When you enable exclusive mode, then Oracle Database uses the new SHA-1 hashing algorithm exclusively. Oracle Database 11g exclusive mode is compatible with Oracle Database 10g and later products that use OCI-based drivers, including SQL*Plus, ODBC, Oracle .NET, Oracle Forms, and various third-party Oracle Database adapters. However, be aware that exclusive mode for Release 11g is not compatible with JDBC type-4 (thin) versions earlier than Oracle Database 11g or Oracle Database Client interface (OCI)-based drivers earlier than Oracle Database 10g. After you configure exclusive mode, Oracle recommends that you remove the old password hash values from the data dictionary.
Follow these steps:
  1. Change all old passwords to include mixed case and special characters.
  2. Verify that the passwords in test scripts or batch jobs are consistent in their use of mixed case and special characters.
  3. Enable exclusive mode.
    1. Create a back up copy of the sqlnet.ora parameter file, by default located in the $ORACLE_HOME/network/admin directory on UNIX operating systems and the %ORACLE_HOME%\network\admin directory on Microsoft Windows operating systems.
    2. Ensure that the sqlnet.ora file has the following line:
    3. Save and exit the sqlnet.ora file.
    4. If necessary, restart the listener. At a command prompt, enter the following commands:
      lsnrctl STOP listener_name
      lsnrctl START listener_name
      listener_name is the name of the listener defined in the listener.ora file. You do not need to identify the listener if you are using the default listener, named LISTENER.

Sunday, May 17, 2009

Set Up Direct NFS

Set Up Direct NFS
Oracle Direct NFS setup involves manipulating the oranfstab file. The oranfstab file can reside in several locations. Oracle will always check the $ORACLE_HOME/dbs directory first. If the oranfstab file does not exist, it will search in the /etc directory.
If an oranfstab file does not exist, Oracle will use the operating system /etc/mtab file.
Oracle’s Direct NFS client looks for mount point settings in the following order:$ORACLE_HOME/dbs/oranfstab
If there are duplicate entries in these files, Oracle Direct NFS client will use the first entry found.
To implement Oracle Direct NFS, an existing NFS mount point must already exist. The mount options for NFS are irrelevant since Oracle Direct NFS will override and configure settings optimally.
The NFS mount options in the /etc/fstab file for this particular example are as follows: nas103:/apps/oracle/share /oradata nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp, actimeo=0,vers=3,timeo=600
It is important to note that in order to enable Direct NFS, you must disable reserved port settings on the NFS server.
Oracle uses the Oracle Disk Manager (ODM) to control NFS. To enable Direct NFS, you must replace the standard ODM driver with the ODM NFS library. For the client to work, you need to shut down the database and the relevant ASM instance and create a symbolic link from the standard ODM library to point to the NFS ODM library. Before you create the symbolic link, you will rename the original file libodm11.so to libodm11.so.ORIG. Next, you will create the symbolic link libodm11.so to point to libnfsodm11.so.
Here are the steps to set up the NFS ODM library file:
1. cd $ORACLE_HOME/lib
2. mv libodm11.so libodm11.so.ORIG
3. ln -s libnfsodm11.so libodm11.so
Once the setup is complete, you will list the file to confirm that it is pointing to the right file: DBA11g1 > ls -l libodm11.so lrwxrwxrwx 1 oracle oinstall 14 Sep 5 09:02 libodm11.so -> libnfsodm11.so
Start the asm instances on all the cluster nodes and then the databases.
As a verification step query the v$dnfs_servers and it should list some records.col filename for a60
set lines 120
set pages 70
select * from v$dnfs_files;
SQL> select * from v$dnfs_files;
-------------------------------------------------- ---------- ---------- ----------
/mnt/fresno_11g_std_controlfiles/control01.ctl 8142848 11 1
/mnt/fresno_11g_std_controlfiles/control02.ctl 8142848 11 1
/mnt/fresno_11g_std_controlfiles/control03.ctl 8142848 11 1
/mnt/fresno_11g_std_datafiles/system01.dbf 943726592 9 1
/mnt/fresno_11g_std_datafiles/sysaux01.dbf 943726592 9 1
/mnt/fresno_11g_std_datafiles/undotbs01.dbf 277880832 9 1
/mnt/fresno_11g_std_datafiles/users01.dbf 5251072 9 1
/mnt/fresno_11g_std_datafiles/example01.dbf 104865792 9 1
/mnt/fresno_11g_std_datafiles/newts01.dbf 5251072 9 1
/mnt/fresno_raw_nfs/disk1 102400000 20 11
/mnt/fresno_raw_nfs/disk2 1073741824 20 11
/mnt/fresno_ora11g_asm26g/disk1 5368709120 20 11
/mnt/fresno_ora11g_asm26g/disk2 1.0737E+10 20 11
/mnt/fresno_ora11g_asm26g/disk3 1.0737E+10 20 11
/mnt/fresno_ora11g_asm26g/disk4 2147483648 20 11
/mnt/fresno_11g_std_datafiles/temp01.dbf 20979712 9 1
/mnt/fresno_11g_std_redologs/redo01.log 52429312 10 1
/mnt/fresno_11g_std_redologs/redo02.log 52429312 10 1
/mnt/fresno_11g_std_redologs/redo03.log 52429312 10 1
19 rows selected.
col svrname for a20 col DIRNAME for a50 select * from v$dnfs_servers;
-------------------- -------------------------------------------------- ---------- ---------- ---------- ----------
1 mosel /vol/fresno_11g_std_controlfiles 4046 2049 65536 65536
3 mosel /vol/fresno_11g_std_datafiles 4046 2049 0 0
10 mosel /vol/fresno_11g_oracle_home 4046 2049 0 0
11 mosel /vol/fresno_raw_nfs 4046 2049 65536 65536
12 mosel /vol/fresno_ora11g_asm26g 4046 2049 0 0
13 mosel /vol/fresno_11g_std_redologs 4046 2049 0 0
6 rows selected.
select * from v$dnfs_channels
---------- -------------------- ---------------------------------------- ---------- ---------- ---------- ---------- ----------
5 mosel mosel 0 1 0 0 0
9 mosel mosel 0 1 21 42 0
10 mosel mosel 0 1 39 78 0
11 mosel mosel 0 1 66 279 0
12 mosel mosel 0 1 0 0 0
13 mosel mosel 0 1 0 0 0
14 mosel mosel 0 2 13 233 0
18 mosel mosel 0 1 0 0 0
20 mosel mosel 0 11 0 0 0
22 mosel mosel 0 1 13 38 0
23 mosel mosel 0 1 38 112 0
24 mosel mosel 0 1 13 38 0
25 mosel mosel 0 1 13 38 0
26 mosel mosel 0 1 0 0 0
28 mosel mosel 0 1 0 0 0
29 mosel mosel 0 1 0 0 0
30 mosel mosel 0 1 0 0 0
34 mosel mosel 0 1 0 0 0
35 mosel mosel 0 1 0 0 0
36 mosel mosel 0 1 0 0 0

20 rows selected.
select * from v$dnfs_stats;
---------- ----------- ----------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ----------- --------------- ---------- ---------- ------------
2 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0
0 0

3 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0
0 0

Next, create the oranfstab file in the $ORACLE_HOME/dbs directory. Since Oracle first looks for the oranfstab file in the $ORACLE_HOME/dbs directory, DBAs can implement Direct NFS without the Unix system administrator’s intervention.
The oranfstab file is not a requirement to implement Direct NFS. The oranfstab file is a special file to list additional options specific for Oracle Database to Direct NFS. The oranfstab file has the following attributes for each NFS server to be accessed using Direct NFS:Server: NFS server name
Path: IP or hostname of up to four network paths to the NFS server
Export: Exported path from the NFS server
Mount: Local mount point for the NFS server
Note For RAC implementations, you must use the oranfstab file from /etc. The oranfstab file must be synchronized across all the RAC servers.
to set up oranfstab for dnfs get the information on the below 1. ethernet ports used in the host 2. Filer that is used to create the volumes
create the file oranfstab in $ORACLE_HOME/dbs as a oracle user. this does not the privilege of a system adminstrator
--Kumarks 18:41, 16 May 2009 (UTC)

Setup oranfstab for DNFS

check the ethenet ports used
ifconfig -l
en0 en1 lo0
ifconfig en0
en0: flags=5e080863,c0 inet netmask 0xffffff00 broadcast
tcp_sendspace 131072 tcp_recvspace 65536
ifconfig en1
en1: flags=5e080863,c0 inet netmask 0xffffff00 broadcast
tcp_sendspace 131072 tcp_recvspace 65536
The IP address of eth0 is (in host fresno.rtp.netapp.com)
The filer used is mosel.rtp.netapp.com
Add these entries in $ORACLE_HOME/dbs/oranfstab
server: mosel path: export: /vol/fresno_11g_std_archivelogs mount:/mnt/fresno_11g_std_archivelogs export: /vol/fresno_11g_std_controlfiles mount:/mnt/fresno_11g_std_controlfiles export: /vol/fresno_11g_std_redologs mount:/mnt/fresno_11g_std_redologs export:/vol/fresno_11g_std_datafiles mount:/mnt/fresno_11g_std_datafiles export:/vol/fresno_ora11g_asm26g mount:/mnt/fresno_ora11g_asm26g export:/vol/fresno_ora11g_asm25g mount:/mnt/fresno_ora11g_asm25g export:/vol/fresno_raw_nfs mount:/mnt/fresno_raw_nfs export:/vol/fresno_11g_oracle_home mount:/mnt/fresno_11g_oracle_home
restart the database to get the oranfstab to come into effect