550 failed to change directory ftp linux

Restrict users access to only their home directory in vsftp.

If you want to restrict FTP users to have access only their home directory but not outside of their own directory. You have to setup chroot.

1. open vsftpd configuration file
$ vi /etc/vsftpd/vsftpd.conf

2. Remove comment from the below line
$ chroot_local_user=YES

3. Save and close the file. Restart vsftpd.
$ /etc/init.d/vsftpd restart

As a result FTP users can't access directories other than their home.

$ ftp> cd /home
550 Failed to change directory.

Oracle Dictionary Views

Get all the views


Tablesapces Related Views


Index and contraint related views


Database Objects Related Views


Cache Related Views


Incident Repair Related Views


How to add swap in Solaris?

This blog will help you to add swap space on Solaris. If you are installating Oracle and running out of swap than these procedure will help you to add swap.  

First login as root: and check how much swap space you have from the following command.

# swap -l

As root, use mkfile to create a swap file.

# mkfile filesize /path/file

e.g.# mkfile 2048m /usr/local/swap

Add the file create above in the swap area.

# swap -a /path/file

e.g. # swap -a /usr/local/swap

Check the swap space again

# swap -l 

You will see new swap space in output.

How to change IP address in Oracle RAC ?

Let's do the exercise to change the IP address of RAC.

Note that due to the change of the public IP address the virtual IP address (VIP) may be required to change as well as it must remain in the same subnet as the public IP address.

Current Configuration

NetworkHostCurrent IP AddressChanged IP Address
Public IP host1192.168.10.101
Public IPhost2192.168.10.100

Basic Steps
The change of the private IP address is performed in the following basic steps
1) Shut down everything except the CRS stack
2) Change the public interface
3) Modify the VIP address
4) Shut down CRS
5) Modify IP address on OS level and reconfigure /etc/hosts, listener,..
6) Restart

How to change character set from AL16UTF16 to UTF8 ?

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB. 

In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.

Before doing this activity it is recommended to take backup first.

$sqlplus / as sysdba

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';

Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.



On the other hand, oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns.

It is always not better choice to use 'ALTER DATABASE' command to change the character set. Check the Documentation for better solution:

NTP Configuration on Solaris

First rename or copy ntp.server to ntp.conf as given below

# cp /etc/inet/ntp.server /etc/inet/ntp.conf

Edit below lines

# vi /etc/inet/ntp.conf

#### NTP Server IPs ####

# svcadm enable ntp
 #svcadm restart ntp
# svcs ntp

#ntpq -p  ----To Check NTP

List of common Ports in Linux

Just giving the list of common ports.

21       FTP
22       SSH
23       Telnet
25       SMTP
53       DNS (Domain Name Service)
68       DHCP
80       HTTP (HyperText Transfer Protocol)
110     POP3 (Post Office Protocol, version 3)
115     SFTP (Secure File Transfer Protocol)
119     NNTP (Network New Transfer Protocol)
123     NTP (Network Time Protocol)
137     NetBIOS-ns
138     NetBIOS-dgm
139     NetBIOS
143     IMAP (Internet Message Access Protocol)
161     SNMP (Simple Network Management Protocol)
194     IRC (Internet Relay Chat)
220     IMAP3 (Internet Message Access Protocol 3)
389     LDAP (Lightweight Directory Access Protocol)
443     SSL (Secure Socket Layer)
445     SMB (NetBIOS over TCP)
993     SIMAP (Secure Internet Message Access Protocol)
995     SPOP (Secure Post Office Protocol)
4000   ICQ
5010   Yahoo! Messenger
5190   AOL Instant Messenger
5632   PC Anywhere
5800 + 5900   VNC (Remote Admin Software)
8080   HTTP Proxy

How to verify No Accounts Have Empty Passwords?

Below command will help you to get details of empty password for any accounts in Linux.

# awk -F: '($2 == "") {print}' /etc/shadow

Lock all empty password accounts with below command

# passwd -l accountname

Unlock users:

#pam_tally2 --user oracle --reset    --->Here Oracle is username 

Daily Oracle DBA Taks

  • Verify all database, instances, listener status
  • Identify bad growth projections
  • Clear the trace files in the udump and bdump directory as per the policy
  • Verify all the monitoring agent, including OEM agent and third party monitoring agents
  • Verify the status of daily scheduled jobs/daily backups in the morning very first hour
  • Check the database performance, periodic basis usually in the morning very first hour after the night shift schedule backup has been completed
  • Verify the success of archive log backups, based on the backup interval
  • Check the space usage of the archive log file system for both primary and standby DB
  • Check the space usage and verify all the tablespace usage is below critical level once in a day
  • Verify Rollback segments
  • Check the sync between the primary database and standby database, every 20 min
  • Make a habit to check out the new alert.log entry hourly specially if getting any error
  • Check the system performance, periodic basis
  • Check for the invalid objects
  • Check out the audit files for any suspicious activities
  • Make a daily habit to learn something new from any technology

How to check datatype consistency between two environments ?

Below script will help you to check datatype consistency between to environment.

SELECT table_name, column_name, data_type, data_length,data_precision,data_scale,nullable
FROM all_tab_columns   #### First Environment
WHERE owner = '&OWNER'
SELECT table_name,column_name,data_type,data_length,data_precision,data_scale,nullable
FROM all_tab_columns@&db_link  #### Second Environment
WHERE owner = '&OWNER2'
order by table_name, column_name;

Oracle DBA Interview Questions and Answers

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode.

A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode.

The benefit of taking a hot backup is that the database is still available
for use while the backup is occurring and you can recover the database to any point in time.

The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. 

In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks.
These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.

NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup
ALTER DATABASE MOUNT - The database is mounted
ALTER DATABASE OPEN - The database is opened

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Script to get ASM information

spool asm_info.html
set pagesize 1000
set linesize 250
set feedback off
col bytes format 999,999,999,999
col space format 999,999,999,999
col gn format 999
col name format a20
col au format 99999999
col state format a12
col type format a12
col total_mb format 999,999,999
col free_mb format 999,999,999
col od format 999
col compatibility format a12
col dn format 999
col mount_status format a12
col header_status format a12
col mode_status format a12
col mode format a12
col failgroup format a20
col label format a12
col path format a45
col path1 format a40
col path2 format a40
col path3 format a40
col bytes_read format 999,999,999,999,999
col bytes_written format 999,999,999,999,999
col cold_bytes_read format 999,999,999,999,999
col cold_bytes_written format 999,999,999,999,999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ) current_time from dual;
select group_number gn, name, allocation_unit_size au, state, type, total_mb, free_mb, offline_disks od, compatibility from v$asm_diskgroup;
select group_number gn,disk_number dn, mount_status, header_status,mode_status,state, total_mb, free_mb,name, failgroup, label, path,create_date, mount_date from v$asm_disk order by group_number, disk_number;

break on g_n skip 1
break on failgroup skip 1
compute sum of t_mb f_mb on failgroup
compute count of failgroup on failgroup

select g.group_number g_n,g.disk_number d_n,g.name , g.path , g.total_mb t_mb,g.free_mb f_mb,g.failgroup from v$asm_disk g order by g_n, failgroup, d_n;


How to change SQL prompt in Oracle?

Procedure to change SQL prompt in Oracle. 

Default SQL prompt:

Changing SQL promt to specific username

1. Go to ORACLE_HOME/sqlplus/admin.

Append below line in  glogin.sql file.

set sqlprompt '_USER>';

2. Connect to the database. You can see the prompt as a user name in below snap.

How to find tables without PK constraint?

SELECT table_name FROM all_tables
WHERE owner = '&OWNER'
SELECT table_name FROM all_constraints
WHERE owner = '&&OWNER' AND constraint_type = 'P';

Oracle DBA Interview Questions

Real time interview question which was asked to one of our group member. 

Tell me about your self

What is your roles and responsibility in your organization?

Listed down topic wise questions:

Oracle Architecture

Explain Oracle architecture

What are the background processes?

What is the physical and logical structure in oracle?

Difference between SGA and PGA.

What is the use of temporary tablespace?

What if someone drop temporary tablespace?

Which process is responsible to write data to disk?


What is OCR and voting disk files?

How many disk should be configured for OCR and Voting disk?

RAC start-up sequence

What is cache fusion?

What is split-brain situation and how to overcome from it?

Explain 5 RAC processes

Oracle Exadata Cell Commands

Cell Details

CellCLI> list cell
CellCLI> list cell detail
CellCLI> list cell attributes all
CellCLI> alter cell shutdown services rs
CellCLI> alter cell restart services rs
CellCLI> list cell attributes rsStatus
CellCLI> alter cell shutdown services MS
CellCLI> alter cell shutdown services CELLSRV
CellCLI> alter cell shutdown services all
CellCLI> alter cell restart services all
CellCLI> alter cell led on
CellCLI> drop cell
CellCLI> drop cell force

Physical Disk Details
CellCLI>list physicaldisk attributes all
CellCLI>describe physicaldisk 
CellCLI>list physicaldisk
CellCLI>list physicaldisk detail

Basic Linux Commands for Oracle

Listed down some of the basic commands which will help you while administering or implementing Oracle on linux.

Password Files: #cat /etc/passwd

Group File: #/etc/group

Create User: #useradd

Delete User: #userdel

Modify User Account : #usermod

Performance Monitor: #top

System Activity Reporter: #sar

Virtual Memory Statistics: #vmstat

IO Statistics: #iostat

Display Swap Size: #free

Activate Swap: #swapon -a

Oracle ASM on Linux

ASM RPMs would be required to configure ASM utility on Linux.

This demo has been done on RHEL 5 x86_64 bit.

1. Install RPMs.

#rpm -ivh oracleasm-2.6.18-238.el5-2.0.5-1.el5.x86_64.rpm
#rpm -ivh oracleasm-2.6.18-238.el5debug-2.0.5-1.el5.x86_64.rpm
#rpm -ivh oracleasm-2.6.18-238.el5xen-2.0.5-1.el5.x86_64.rpm
#rpm -ivh oracleasm-support-2.1.4-1.el5.x86_64.rpm

2. Configure ASM 

#/etc/init.d/oracleasm configure

Here we have to provide which user and group would be configured for ASM disk. Also it required to mention whether ASM driver should be auto-start or not.

Now ASM utility has been installed and configured on Linux.

You can also configure YUM Repository to install rpms for ASM utility.

How to kill all oracle processes in Linux ?

For Single Instance

$ps -ef|grep "ora_"|grep -v grep|grep $ORACLE_SID|awk '{print $2}'|xargs kill -9

For all instances

$ps -ef|grep "ora_"|grep -v grep|awk '{print $2}'|xargs -i kill -9 {}

YUM Configuration in Linux

Installing RPMs in Linux is very smooth but many times it headache to install all the dependency.

To remove this headache, RHEL comes with YUM utility which automatically takes all the dependency RPMs and installs it smoothly.

Step by step procedure to configure YUM

1. Mount REDHAT Operating System DVD/ISO on the server

2. Make a directory to store rpms from REDHAT DVD/ISO and copy Server directory from mounted DVD/ISO to folder created folder, which will copy all RPMs to local system.

3. Install createrepo RPM which required for YUM repository creation.

How to Modifying the Size of Redo Logs ?

We must drop and re-create the redo to re-size the redo logs and that can be done online without interrupting database service.

Step - 1 Make sure the logfile you want to change is not the current or active log file.
Step - 2 Drop the logfile group you want to change.
Step - 3 Re-create the logfile group, re-sizing it as required.

SQL> select group#, status from v$log;

---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

Here we have to change log group 3 but it seems current so we have to make it inactive by switching logfile.

A CURRENT status indicates that the resulting dirty block that are associated with the redo in the logfile have not yet been check pointed which is required before we drop the logfile.

SQL> Alter system switch logfile;

SQL> Alter system switch logfile;

Checkpoint the system. This will remove the log file we want to drop from a possible active status.

SQL> Alter system checkpoint;

Now check the status of logfile again the status of group 3 is 'INACTIVE' now

SQL> select group#, status from v$log;

---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

Once the checkpoint is complete, we drop the online redo log group, and re-create it defining a new size for the redo log files.

SQL> Alter database drop logfile group 3;

Now add the logfile group with the new sizing information.

SQL> alter database add logfile group 3
   '\ORADATA\REDO\redo03b.log') size 500m REUSE;

Script of Memory Advice Report for 11g

col buffer_cache format a16 heading "Buffer Cache|Change"
col pga format a12 heading "PGA |Change"
col current_cache_mb format 99,999 heading "Current|Cache MB"
col target_cache_mb format 99,999 heading "Target|Cache MB"
col current_pga_mb format 99,999 heading "Current|PGA MB"
col target_pga_mb format 99,999 heading "Target|PGA MB"
col pga_secs_delta format 99,999,999 heading "Pga Time|Delta (s)"
col cache_secs_delta format 99,999,999 heading "Cache Time|Delta (s)"
col total_secs_delta format 99,999,999 heading "Total Time|Delta (s)"

set pagesize 1000
set lines 80
set echo on 
WITH db_cache_times AS 
    (SELECT current_size current_cache_mb, 
            size_for_estimate target_cache_mb,
            (estd_physical_read_time - current_time) 
       FROM v$db_cache_advice,
            (SELECT size_for_estimate current_size,
                    estd_physical_read_time current_time
               FROM v$db_cache_advice
              WHERE  size_factor = 1
                AND name = 'DEFAULT' AND block_size = 8192)
       WHERE name = 'DEFAULT' AND block_size = 8192),
 pga_times AS 
     (SELECT current_size / 1048576 current_pga_mb,
             pga_target_for_estimate / 1048576 target_pga_mb,
             estd_time-base_time pga_secs_delta 
        FROM v$pga_target_advice , 
             (SELECT pga_target_for_estimate current_size,
                     estd_time base_time
                FROM v$pga_target_advice 
               WHERE pga_target_factor = 1))
SELECT current_cache_mb||'MB->'||target_cache_mb||'MB' Buffer_cache,
       current_pga_mb||'->'||target_pga_mb||'MB' PGA,
       (pga_secs_delta+cache_secs_delta) total_secs_delta
  FROM db_cache_times d,pga_times p
 WHERE (target_pga_mb+target_cache_mb)
   AND (pga_secs_delta+cache_secs_delta) <0
 ORDER BY (pga_secs_delta+cache_secs_delta);

Multicasting in Linux

Check whether multicast is enabled or not.

Command : #ifconfig eth0

You cans see multicast is enabled in above output.

Disable Multicast

Command : #ifconfig eth0 -multicast

Enable multicast

Command : #ifconfig eth0 multicast

Script to find top consumers of PGA memory

set pagesize 1000
set lines 100
col sid format 9999
col username format a12
col module format a30
column pga_memory_mb format 9,999.99 heading "PGA MB"
column max_pga_memory_mb format 9,999.99 heading "PGA MAX|MB"
col service name format a20 
col sql_text format a70 heading "Currently executing SQL"
set echo on 

WITH pga AS 
    (SELECT sid,
            ROUND(SUM(CASE name WHEN 'session pga memory' 
                       THEN VALUE / 1048576 END),2) pga_memory_mb,
            ROUND(SUM(CASE name WHEN 'session pga memory max' 
                      THEN VALUE / 1048576  END),2) max_pga_memory_mb
      FROM v$sesstat  
      JOIN v$statname  USING (statistic#)
     WHERE name IN ('session pga memory','session pga memory max' )
     GROUP BY sid)
SELECT sid, username,s.module, 
       max_pga_memory_mb, substr(sql_text,1,70) sql_text
  FROM v$session s
  JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,
               RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking
         FROM pga)
  USING (sid)
  LEFT OUTER JOIN v$sql sql 
    ON  (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)
 WHERE pga_ranking <=5
 ORDER BY  pga_ranking

Oracle Database Startup Script

Let's create oracle start up script which will help you to start and stop database through the script. This script can help you when you have to integrate Oracle with Linux cluster.

Create a file like db_startup.sh and provide belo script code into it. 

# description: Oracle auto start-stop script.
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# Set ORACLE_OWNER to the user id of the owner of the 
# Oracle database in ORACLE_HOME.


if [ ! -f $ORACLE_HOME/bin/dbstart ]
    echo "Oracle startup: cannot start"

case "$1" in
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORA_HOME"
        touch /var/lock/subsys/dbora
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora

echo "Usage: $0 start|stop|restart|reload"
        exit 1
exit 0

Following commands will be used for DB start-up

#sh db_startup.sh start  - To start database
#sh db_startup.sh stop - To staop Database

NFS mount for Oracle Datapump

Many times we required NFS mount point on database server to do multiple activities and we face issue while mounting the NFS mount point with default settings. To overcome from this issue you can below command with parameter settings to mount the NFS mount point on DB server.

#mount -o hard,rw,noac,rsize=32768,wsize=32768,suid,proto=tcp,vers=3 -F nfs nfs_server:/file_system /dir_name


nfs_server - Hostname or IP address of NFS server
file_system - Directory or mount point or file system of NFS server which is going to mount on DB server.
dir_name - Directory name on DB server on which NFS file system would be placed.

Bonding Configuration in Linux

1. Create ifcfg-bond0 file as given below.

#vi /etc/sysconfig/network-scripts/ifcfg-bond0


2. Append below lines in ifcfg-eth0 and ifcfg-eth1

#vi /etc/sysconfig/network-scripts/ifcfg-eth0


############# Bond Configuration Parameter #########


#vi /etc/sysconfig/network-scripts/ifcfg-eth1

Exclude directory and files in tar

First create text file which include all the directories and files which needed to exclude from tar. Here we are creating exclude file.

#vi exclude   

Execute below command to make tar which will exclude the files and directory which is available in exclude file.

# tar -cvfX test.tar ./exclude *

Master Note for Oracle Performance Tuning and Troubleshooting

  • Master Note for Diagnosing ORA-600 [1092832.1]
  • Master Note for Diagnosing ORA-7445 [1092855.1]
  • Master Note: How to diagnose Database Performance – FAQ [402983.1]
  • Master Note: Query Tuning Overview [199083.1]
  • FAQ: Query Tuning Frequently Asked Questions [398838.1]
  • Diagnostics for Query Tuning Problems [68735.1]
  • Master Note for Diagnosing ORA-4030 [1088267.1]
  • Master Note for Diagnosing ORA-4031 [1088239.1]
  • ORA-4031 Common Analysis/Diagnostic Scripts [Video][430473.1]
  • 11g Understanding Automatic Diagnostic Repository. [422893.1]
  • 11g Diagnosability: Frequently Asked Questions [453125.1]
  • Master Note for Troubleshooting Oracle Managed Distributed Transactions [100664.1]

Master note for Oracle Backup & Recovery

  • Master Note For Oracle Flashback Technologies [1138253.1]
  • Master Note For Oracle Backup And Recovery [1199803.1]
  • Master Note For Oracle Recovery Manager (RMAN) [1116484.1]
  • Master Note for Data Guard [1101938.1]
  • Master Note for Export and Import [1264691.1]
  • Master Note for Data Pump [1264715.1]
  • Master Note for SQL*Loader [1264730.1]
  • Master Note for LogMiner [1264738.1]

Find Top 5 Datafiles with highest I/O activity

col name format a40
set linesize 140
select * from (select name,phyrds, phywrts,readtim,writetimfrom v$filestat a, v$datafile b 
where a.file#=b.file#order by readtim desc) where rownum <6; 

Find Top Undo Consumers in Oracle

SELECT TO_CHAR(s.sid) || ',' || TO_CHAR(s.serial#) sid_serial, 
NVL(s.username, 'None') orauser, s.program, r.name undoseg, 
t.used_ublk * TO_NUMBER(x.value) / 1024 || 'K' "Undo" 
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) 
AND x.name = 'db_block_size'; 

Install Apache HTTP Server on Linux

  • Required Packages as prerequisites


  • Download and Copy apache binary and extract it on Linux machine
Steps to Install HTTPD 

1. go to HTTPD directory
2. #./configure
3. #make
4. #make install

Test whether it has been installed successfully or not  : 

In browser provide below link 
http://localhost:80 ----It will show "IT WORKS"

Start Apache Server

# /usr/local/apache2/bin/apachectl start

Stop Apache Server

# /usr/local/apache2/bin/apachectl stop

Location of Apache configuration file

#more /usr/local/apache2/conf/httpd.conf

Oracle Database 12c: Installation and Administration Exam Topics (1Z0-062)

Oracle Database Administration

Exploring the Oracle Database Architecture
         List the architectural components of Oracle Database
         Explain the memory structures
         Describe the background processes
         Explain the relationship between logical and physical storage structures

Oracle Database Management Tools
         Use database management tools

Oracle Database Instance
         Understand initialization parameter files
         Start up and shut down an Oracle database instance
         View the alert log and access dynamic performance views

Configuring the Oracle Network Environment
         Configure Oracle Net Services
         Use tools for configuring and managing the Oracle network
         Configure client-side network
         Configure communication between databases

Managing Database Storage Structures
         Describe the storage of table row data in blocks
         Create and manage tablespaces