Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 9 July 2013

DBA Interview Questions with Answer Part 18

How would you decide your backup strategy and timing for backup?
In fact backup strategy is purely depends upon your organization business need. If no downtime then database must be run on archivelog mode and you have to take frequently or daily backup. If sufficient downtime is there and loss of data would not affect your business then you can run your database in archivelog mode and backup can be taken in-frequently or weekly or monthly.
In most of the case in an organization when no downtime then frequent inconsistent backup needed (daily backup), multiplex online redo log files (multiple copies), different location for redo log files, database must run in archivelog mode and dataguard can be implemented for extra bit of protection (to make less downtime during recovery).
What is Jinitiator and what its purpose?
It is a java virtual machine provided for running web based oracle forms applications inside a client web browser. It is implemented as a plug-in or ActiveX object, allows you to specify the use of oracle certified JVM instead of relying on default JVM provided by browser. It is automatically downloaded to a client machine from the application. Its installation and update is performed by standard plug-in mechanism provided by the browser.
What is the use of large pool, which case you need to set the large pool?
You need to set large pool if you are using: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.
How can you audit system operations?
Sys connection can be audited by setting init.ora parameter AUDIT_SYS_OPERATIONS=TRUE
How can you implement Encryption in database?
Data with database can be encrypted and decrypted using package: DBMS_OBFUSCATION_TOOLKIT
How do you list the folder files with hidden file in Linux
s –ltra
How to execute Linux command in Background?
Use the "&" at the end of command or use nohup command
What Linux command will control the default permission when file are created?
Umask
Give the command to display space usage on the LINUX file system?
df –lk
What is the use of iostat/vmstat/netstat command in Linux?
Iostat – reports on terminal, disk and tape I/O activity.
Vmstat – reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat – reports on the contents of network data structures.
What are the steps to install oracle on Linux system. List two kernel parameter that effect oracle installation?
Initially set up disks and kernel parameters, then create oracle user and DBA group, and finally run installer to start the installation process. The SHMMAX & SHMMNI two kernel parameter required to set before installation process.
__________ Parameter change will decrease Paging/Swapping?
Answer: Decrease_Shared_Pool_size
_______ Command is used to see the contents of SQL* Plus buffer
Answer: LIST
Transaction per rollback segment is derived from ________
Answer: Processes
LGWR process writes information into ___________
Answer: Redo log files.
A database over all structure is maintained in a file __________
Answer: Control files
What is the use of NVL function?
The NVL function is used to replace NULL values with another or given value.
For Example: NVL (Value, replace value);
What is WITH CHECK OPTION?
The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.
The concepts are different than previous concept in fact. In that case you can access the some of the concept in your mind to achieve the target.
How can you track the password change for a user in oracle?
Oracle only tracks the date that the password will expire based on when it was latest changed. Thus listing the view DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. You can also check the last password change time directly from the PTIME column in USER$ table (on which DBA_USERS view is based). But If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account.
SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#;
What is the difference between a data block/extent/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.
What is the difference between SQL*loader and Import utilities?
Both these utilities are used for loading the data into the database. The difference is that the import utility relies on the data being produced by another oracle utility Export while SQL*Loader is a high speed data loading mechanism allows data to be loaded that has been produced by other utilities from different data source. Import is mainly used reading and writing operating system files.
Can you list the Step how to create Standby database?
  1. Take a full hot backup of Primary database
  2. Create standby control file
  3. Transfer full backup, init.ora, standby control file to standby node.
  4. Modify init.ora file on standby node.
  5. Restore database
  6. Recover Standby database
  7. (Alternatively, RMAN DUPLICATE DATABASE FOR STANDBY DO RECOVERY can be also used)
  8. Setup FAL_CLIENT and FAL_SERVER parameters on both sides
  9. Put Standby database in Managed Recover mode
How would you activate Physical Standby database in oracle 9i?
Perform below on primary database if available to transfer all pending archive logs to standby:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
Now perform below on STANDBY database:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Note: Once you start the Standby DB, your relation between primary databases to standby database has been lost and at this time your standby database becomes primary database.
How to Switch from Primary to Physical Standby database?
Perform below step on Primary Database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DICONNECT FROM SESSION;
SQL> ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= DEFER SCOPE=SPFILE;
Perform below steps on Secondary Database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
SQL> ALTER SYSEM SET LOG_ARCHIVE_DEST_2_STATUS= ENABLE SCOPE=SPFILE;
How will you list only the empty lines in a file (using GREP)
GREP "^$" filename.txt
How will you shutdown your database if SHUTDOWN IMMEDIATE command is already tried and failed to shutdown the database?
Kill the SMON process.
What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. Sometimes you can force the log switch by using the command: ALTER SYSTEM LOG SWITCH.
How can you pass the HINTS to the SQL processor?
Using comment line with (+) sign you can pass the HINTS to the SQL engine: For Example: /* +PARALLEL() */
Give Example of available DB administrator utilities with their functionality?
SQL * DBA – It allows DBA to monitor and control an oracle database.
SQL * Loader – It loads data from standard OS files or flat file in oracle database tables.
Export/Import – It allows moving existing data in oracle format to and from oracle database.
Can you built indexes online?
YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
If an oracle database is crashed? How would you recover that transaction which is not in backup?
If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup.
What is the benefit of running the DB in archivelog mode over no archivelog mode?
When a database is in no archivelog mode whenever log switch happens there will be a loss of some redoes log information in order to avoid this, redo logs must be archived. This can be achieved by configuring the database in archivelog mode.
What is SGA? Define structure of shared pool component of SGA?
The system global area is a group of shared memory area that is dedicated to oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You can control the SGA memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three major area: Library cache (parse SQL statement, cursor information and execution plan), dictionary cache (contain cache, user account information, privilege user information, segments and extent information, buffer for parallel execution message and control structure.
You have more than 3 instances running on the Linux box? How can you determine which shared memory and semaphores are associated with which instance?
Oradebug is undocumented oracle supplied utility by oracle. The oradebug help command list the command available with oracle.
SQL>oradebug setmypid
SQL>oradebug ipc
SQL>oradebug tracfile_name
How would you extract DDL of a table without using a GUI tool? 
Select dbms_metadata.get_ddl('OBJECT','OBJECT_NAME') from dual;
If you are getting high “Busy Buffer waits” then how can you find the reason behind it?
Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache. There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo/data block or segment header wait.
Run the below two query to find out the P1, P2 and P3 of a session causing buffer busy wait
then after another query by putting the above P1, P2 and P3 values.
SQL> Select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait Where event = 'buffer busy waits';
SQL> Select owner, segment_name, segment_type from dba_extents
Where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
Can flashback work on database without UNDO and with rollback segments?
No, flashback query enable us to query our data as it existed in a previous state. In other words, we can query our data from a point in time before any other users made permanent changes to it.
Can we have same listener name for two databases?
No
For More Interview Preparation Click on Link:

6 comments:

  1. nice work

    for more oracle database interview questions and answers visit:DBA interview questions

    ReplyDelete
  2. For latest and updated certification dumps (for Oracle/Cisco/Microsoft/ITIL/RedHat/Salesforce/Cloudera/many more..) in PDF format contact us at completeexamcollection@gmail.com. Refer our blog for more details http://completeexamcollection.blogspot.in/

    ReplyDelete
  3. Somebody necessarily help to make severely posts I might state. This is the first time I frequented your website page and to this point? I surprised with the research you made to create this particular post extraordinary. Well done admin..
    Oracle Training in Chennai

    ReplyDelete
  4. Nice blog. Really helpful information about Oracle …. Please keep update some more…………

    ReplyDelete