Which is the best answer given in the interview of tell about yourself ?

The first question you’re probably going to get in an interview is,

“Tell me about yourself.”

Now, this is not an invitation to recite your entire life or even to go bullet by bullet through your resume.

Instead, it’s probably your first and best chance to pitch the hiring person on WHY you’re the right fit.

A formula I really like to use is called the Present-Past-Future (PPF) formula.

So, first you start with the present—where you are right now.

Then, segue into the past—a little bit about the experiences you’ve had and the skills you gained at the previous position.

Finally, finish with the future—why you are really excited for this particular opportunity.

How to Delete LOGFILE from older date in Linux

In Oracle database there are some files which reguarly create day by day such as trace files, log files, archivelog files & Exported dump files.

In this article i will show how to remove above files as batch job.

This Task is the Housekeeping work performed by a DBA.

  Ø  Linux platform

We can use find command for this purpose.

for example:

 $ find $TRACEFILE/*.trc -mtime +7 -exec rm {} \;  

above script remove all .trc files which older than 7 days.

Schedule: Through crontab utility schedule above script.

For Reference http://www.dba-oracle.com/t_unix_script_find_files_older_date.htm

   Ø  Windows platform

We can use FORFILES.exe for this purpose

find more about forfiles.exe here(http://technet2.microsoft.com/WindowsServer/en/Library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx)

for example:

 D:\FORFILES.EXE /P D:\archive_dump_files -s /M *.* /D -5 /C "cmd /c del @FILE echo @FILE"  

D:\FORFILES.EXE /P D:\archive_dump_files -s /M *.* /D -5 /C "cmd /c del @FILE echo @FILE"

In above example files deleted older than 5 days from "archive_dump_files" location.

Schedule: We can use WINDOWS SCHEDULER TASK for this purpose.

How to find Oracle Version?

There is many way to find out.

1. Through SQLPLUS.

C:\app\oracle\test\product\11.1.0\db_1\BIN> sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 12:23:54 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

2. Through Data dictionary view

SQL> select * from v$version;

BANNER

-------Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - ProductionCORE 11.1.0.6.0 Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 – Production

3. We can also find the oracle version by

strings $ORACLE_HOME/bin/oracle | grep NLSRTL

[oracle@oraclelinux udump]$ strings $ORACLE_HOME/bin/oracle | grep NLSRTL

NLSRTL Version 10.2.0.1.0 - Production

4. We can also use the below Views

 SQL> SELECT VERSION FROM v$instance;

or

SQL> select * from product_component_version;

5. opatch lsinventory

1. Set ORACLE_HOME variable

2. go to OPATCH directory

3. run opatch lsinventory

C:\>set oracle_home=c:\app\oracle\test \product\11.1.0\db_1

C:\>cd %ORACLE_HOME%\opatch

C:\app\oracle\test \product\11.1.0\db_1\OPatch>opatch lsinventory

Invoking OPatch 11.1.0.6.0

.

.

Installed Top-level Products (1):

Oracle Database 11g 11.1.0.6.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

OPatch succeeded.

6. check SQLNET.LOG file

check SQLNET.LOG file in ORACLE_HOME\network\log

you will find below entries

VERSION INFORMATION:

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production

Windows NT TCP/IP NT Protocol Adapter for 32-bit

Windows: Version 11.1.0.6.0 - Production

How to Enable auditing for SYSDBA (sys) user in 11gr2

Database security is one of the major and important task for DBA's.

Steps:

1. logon to database with sysdba user

2. set AUDIT_SYS_OPERATIONS parameter to TRUE    //this is mandatory

Note:- Default value of this parameter is FALSE

Note:- This parameter is static means we required to bounce DATABASE to take effect of this parameter value.

3. set AUDIT_TRAIL parameter as per desired value.

Note:- Default value of this parameter is “none”

4. set AUDIT_FILE_DEST = location path for audit records log file

Note:- Default value of this parameter %ORACLE_BASE%/ORACLE_SID/adump

IF AUDIT_FILE_DEST parameter doesn't set or not point to valid directory then oracle will create audit file in %ORACLE_HOME%/rdbms/audit directory.

5. restart database

6. Setting the size or age of the operating system audit files

http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#CEGBDFCF

Example:[oracle@oracledb bin]$ ./sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 17 21:46:36 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set audit_sys_operations=TRUE scope=spfile;

System altered.

SQL> alter system set audit_trail='DB' scope=spfile;

System altered.

SQL> alter system set audit_file_dest='/db/admin/oradb/adump/' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size                  1337548 bytes

Variable Size             973080372 bytes

Database Buffers         1056964608 bytes

Redo Buffers               10858496 bytes

Database mounted.

Database opened.

SQL> ! ls -lrt /db/admin/ oradb /adump/

total 32

-rw-rw---- 1 oracle oracle 2585 Jan 17 21:48 oramfe_ora_12487_1.aud

-rw-rw---- 1 oracle oracle  750 Jan 17 21:48 oramfe_ora_12500_2.aud

-rw-rw---- 1 oracle oracle 1451 Jan 17 21:50 oramfe_ora_12602_1.aud

-rw-rw---- 1 oracle oracle  980 Jan 17 21:51 oramfe_ora_12671_1.aud

SQL> host cat /db/admin/oradb/adump/oramfe_ora_12671_1.aud

Audit file /db/admin/oradb/adump/oramfe_ora_12671_1.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /db/product/11.2.0/dbhome_1/

System name:    Linux

Node name:      oracledb.nikhil.local

Release:        2.6.18-53.el5

Version:        #1 SMP Wed Oct 10 16:34:02 EDT 2016

Machine:        i686

Instance name: oradb

Redo thread mounted by this instance: 1

Oracle process number: 20

Unix process pid: 12671, image: oracle@oracledb.nikhil.local (TNS V1-V3)

Sat Jan 17 21:50:07 2016 +04:00

LENGTH : '160'

ACTION :[7] 'CONNECT'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072'

Sat Jan 17 21:51:21 2016 +04:00

LENGTH : '173'

ACTION :[19] 'ALTER DATABASE OPEN'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/0'

STATUS:[1] '0'

DBID:[10] '3012735072

How To add swap space on linux system

 Follow Below Steps to add swap space in Linux :

1. login to root user

su root

2. create swap file for desired size

dd if=/dev/zero of=/swapfile1 bs=1024 count=524288

NOTE: bs = size in bytes and count = ( bs * desired space)

if you want to increase 1g then count will calculate in following way

1024 * 1024 = 1048576

3. setup linux swap area

# mkswap /swapfile1

4. Activiate swapfile space

# swapon /swapfile1

5. check "free-m" command on # prompt

$ free -g

check for more info: http://www.cyberciti.biz/faq/linux-add-a-swap-file-howto/

How To Take Backup Database Link In Oracle

For All DBlinks backup:

-------------------------------

SQL> set pages 0

SQL> set long 90000

SQL> set trimspool on

SQL> SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;

For a specific DBlink backup:

--------------------------------------

SQL> set pages 0

SQL> set long 90000

SQL> set trimspool on

SQL> SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links

How to Prepare for DBA Interview

Preparation for DBA Interview

Nobody can give us complete or most of questions about interview because it is like our school, college exam which we don't know what exact questions comes in.

For school, college exam we need to go through every books which related to exams topic same like any Interview not for DBA only we need to prepare our self and go through each and every topic related to Oracle Database.

So now question is change from Oracle DBA interview question TO How to prepare myself for DBA interview?

And now , here , we explained how we can prepare our self for DBA interview.

1. First understand we are going for DBA interview means Database Administrator.

“A person who’s responsible to handle and maintain database called DBA”

Means DBA position is very Hard, though, sensitive and confidential.

We can divide Oracle DBA interview in Four Major part.

1. Oracle Database Administration

2. Oracle Database Backup & Recovery Administration

3. Oracle Database Performance Tuning

4. Oracle Database Advanced administration

ü  Oracle Database Administration

1. How to install Oracle Database Software on Different platforms?

Above point include Oracle Database Server or Client installation on different flavor like LINUX/SORALIS/UNIX/WINDOWS etc. so whenever you are going for interview must go through each installation guide.

2. How to Create Database?

There is two option for create DATABASE.

DBCA or MANUALLY

3. How to create tablespace?

4. How to configure Oracle Client to connect to Oracle Server?

In Above point we need to create TNS_ENTRY for database client through NETCA tools.

5. Database User Management

Specific permission on database according user needs. User profile creation., role management, etc.

6. Database Security

In above point we need to do Database Auditing, User Logon, Logoff information.

ü   Oracle Database Backup & Recovery Administration

Ø  1. What is your backup strategy for database backup?

It is depends on many factor.

1. Database size It is in MB, GB or TB

2. Database type It is OLTP or DSS

3. Database availability It is 24x7

So whenever we design backup strategy for database keeps in mind above points. Because Oracle Expert Mr. Tom kyte said “We can FIX everything except BAD DATABASE BACKUP”.

Ø  2. What is your backup method for database backup?

It is also depend on condition.

1. Backup Type COLD backup or HOT backup

2. Backup Method RMAN or USER MANAGED

3. Backup Interval Every Day, Every Week, Every Month.

Oracle Corp. recommended to use RMAN (server managed) backup method for database backup but we can also use USER MANAGED backup if applicable.

NOTE: Always RUN production database in ARCHIVELOG mode.

After set proper backup strategy now practice on database recovery because interviewer must ask question about recovery side.

“Database Recovery is depend on Database backup”

There are so many or different types of recovery so we can divide recovery area in following.

Ø  How many types of recovery we can perform?

1. When datafile is lost

2. when controlfile is lost

3. when online redolog file is lost

Above three files is most important file for database if we lost them then we needed to perform recovery.

1. We can perform recovery at DATABASE level when complete database is lost.

2. We can perform recovery at TABLESPACE level when tablespace is lost.

3. We can perform recovery at DATAFILE level when datafile is lost.

4. We can perform recovery at DATA BLOCK level when any block is corrupted.

5. We can Multiplex CONTROLFILE or REDOLOG file for protect against LOST.

And suppose still we lost all controlfile or redolog files then recovery depends on many conditions.

NOTE: Before going for interview must go through backup and recovery user guide.

ü  Oracle Database Performance tuning administration

This is third big area where most of DBA questions come.

For database tuning we need years of experience. But we can start with bottom.

1. What are database activities?

2. What is database memory area means SGA or PGA size?

3.What is database background process like DBRW, LGWR, CKPT SMON, PMON etc?

For above question oracle providing same diagnostic tools

1. STATPACK or AWR report

2. AUTO TRACE

3. SQL trace

4.Tkprof

Above are same tool to use find out bottleneck of slow database performance so we must know below things about this tools

1. How to use?

2. How to understand?

3. How to use these tools to solve database performance issue.

Above question is simple but answer is difficult because answer want practical or years of experience in database.

Note: Search on Google or visit OTN site for starting.

ü  Oracle Database advanced administration

Oracle Advanced Features

1. Data Guard

2. Partitioning

3. Stream

4. RAC system

5. ASM

6. Others

DBA interviewer should ask question above points but it is also depend on DBA position because above points will include in senior level position.

End of the story is…

For Interview we need good knowledge about Oracle DB and Experience because if we read thousands of interview related questions without good knowledge or experience we can clear interview but can’t be become a good DBA.

See Also : What is Good DBA

General tips for DBA Interview*HOT*