What is a deadlock and what is a live lock? How will you go about resolving deadlocks? Answer
Deadlock is a situation when two processes, each having a
lock on one piece of data, attempt to acquire a lock on the
other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user
processes is terminated. SQL Server detects deadlocks and
terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared
locks keeps interfering. SQL Server detects the situation
after four denials and refuses further shared locks. A
livelock also occurs when read transactions monopolize a
table or page, forcing a write transaction to wait
indefinitely
Re: How to start SQL Server in minimal configuration mode? Answer
In a Command Prompt window, you can start the SQL server in
minimal configuration mode by typing: sqlservr -c -f
This command also starts the SQL server in single-user mode.
Also, the server will not run any startup stored procedures.
Additionally, when you start the SQL server in minimum
configuration mode, then SQL Server automatically sets the
size of tempdb to 2 MB on its default device. You must then
manually increase the size of tempdb as necessary.
Re: What is RAID and what are different types of RAID configurations? Answer
RAID: For FUN it is called Redundant Array of INEXPENSIVE
Disks.
In Reality it is called Redundant Array of Independent Disks.
Following 3 are most popular and still in use in different ways.
1. RAID 0: Disk Striping
2. RAID 1: Disk Mirroring
3. RAID 5: Disk Striping with Parity Bit.
Re: What are the different ways of moving data or databases between servers and databases in SQL Server? Answer
export/import...attach/detach...DTS....backup/restore!
BCP (BULK COPY PROGRAM)
Re: Explain the difference between a hot backup and a cold backup and the benefits associated with each? Answer
Cold Backup - In this case, they take the database down
then they have backup. this is called cold backup.
Hot Backup - In this case, they take running database
backup (like SQL Server) this is called hot backup.
Re: What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace? Answer
Temporary Tablespace is in generally used for sorting
purpose while Permanent tablespace is used to sore
permanent objects
Re: How do you resize a data file? Answer
in SQL Server 2005:
ALTER DATABASE <Database Name> MODIFY FILE
(NAME =<File Name>, MAXSIZE = <Size>)
Re: Explain what partitioning is and what its benefit is. Answer
Partitioning is a method of taking large tables and indexes and then splitting them into smaller and more manageable pieces. And the Advantage are
1.Maintaincace :-Can Take Partioned tables's tablespace
offline for recovery
while other partitions are still avialable to users
Hence the Avialability
2.Performace :As the Objects are plcaed in Diffrent
Tablespaces of Diffrent Servers/locations
Re: Compare and contrast TRUNCATE and DELETE for a table. Answer
Basically truncate is a DDL and Delete is DML.
You can use delete to remove rows depending on certain criteria and it
generated redo logs and can be rolled back before you
commit the transaction,
Where as the truncate is used to remove all the data in the table. Once you execute this command u can not rollback this transaction.and commit is not required for the truncate statement
TRUNCATE RESETS IDENTITY VALUE OF COLUMN
WHILE DELETE DOESN'T RESETS IDENTITY VALUE OF COLUMN
Re: 7. Where should you place Archive logfiles, in the same disk where DB is or another disk?
Keep Archives in separate mount points / Disks are the preferred solution, so incase of any curruption / media corruption, you may recover your DB from that location.
8. Can you take online backup of a Control file if yes, how?
backup controlfile to '<location>' or trace (check with the latest file in trace directory)
9. What is a Logical Backup?
Export is a logical backup.
10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
Not mendatory.
11. Why do you take tablespaces in Backup mode?
If RMAN is not implemented or only one tablespace needs
to backup (because, it may contain the dynamic data), you
need to go for 'alter tablespace <tbs_name> begin backup';
12. What is the advantage of RMAN utility?
Oh man, i'm tired to post that... check google yaar...
13. How RMAN improves backup time?
RMAN will not take backup of free blocks and will have
multiple channels so it would be fastest than any other
old / traditional ways.
14. Can you take Offline backups using RMAN?
Yes... we can take backup offline with RMAN. (Thats why
I love RMAN)
15. How do you see information about backups in RMAN?
see the command help in RMAN... its damn easy mate.
16. What is a Recovery Catalog?
Catalog is an inventory of the backup taken by RMAN for the said database.
17. Should you place Recovery Catalog in the Same DB?
Well, depends upon resources. there is no harm to place catalog into the same DB.
18. Can you use RMAN without Recovery catalog?
Yes, very much... it will use control file to store the required information.
19. Can you take Image Backups using RMAN?
Not sure on image part. (may be question need to
rephrase. :)
20. Can you use Backupsets created by RMAN with any other utility? Answer
well... depends upon the utility / tool... But probably
NO.. Because, RMAN uses different algorithm to decode /
encode the backups.
21. Where RMAN keeps information of backups if you are using RMAN without Catalog? Control files.
22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it? Not sure
23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN? you need to configure retention period in RMAN.
24. Which is more efficient Incremental Backups using RMAN or Incremental Export? Obviously RMAN incremental backup...
25. Can you start and shutdown DB using RMAN? Yes.
26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode? you can recover from cold backup only but it wouldnt be
consistent... i.e. you need to restore complete cold backup 27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation? Simply, recreate the new datafile... :D
28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now? Well, you can recreate that but data would be lost. (I
pressume that, Datafile is NON-SYSTEM)
29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode? Ohh... from recover from .... and apply for the
archives.
30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file? No matter here... You have archives, you can select any
good backup to recover from.
31. You loss controlfile how do you recover from this? Create the control file again... and place it to the
location (where spfile is pointing to).
32. The current logfile gets damaged. What you can do now? Create new one and drop the currupted one.
33. What is a Complete Recovery? I doubt... you are appearing for DBA interview... ???
34. What is Cancel Based, Time based and Change Based Recovery? Oh my poor boy... Pl. read documentation before
applying for any interviews.
35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode? If its 10g and flashback is configured then you can
recover immediately from recycle bin... if its 9i then you
may need to check log miner and do point-in time recovery..
36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN? Nope.
37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups? Couldnt understand question... Pl. re-post if you want.
38. How do you recover from the loss of a controlfile if you have backup of controlfile? Sweetheart... read documentation (RMAN part)
39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN? Yes
40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation? Depends upon the nature of those datafiles. if its non-
system and non-critical, you can put them on offline mode.
Else, you need to contact me on +91 974 212 7827... :)
41. Have you faced any emergency situation. Tell us how you resolved it? Every day in morning, I face the emergency in my
bathroom... (Dont ask me now which kind of emergency) hee
hee hee... LOL :P 42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.
From Alertlog file...
Last but not least... Above answers are very generic for
the common problems. If you still need any assistance, Pl.
drop an email / call (details are in my signature below). I
provide the consultancy also... :)
Re: Name two files used for network connection to a database. Answer
Listener.ora and TNSnames.ora and sqlnet.ora
Re: What type of index should you use on a fact table? Answer
IN SQL SERVER 2005 USING NON CLUSTERED INDEX WOULD BE BETTER.
Re: How would you determine the time zone under which a database was operating? Answer
select * from V$NLS_Parameters or select DBTIMEZONE from dual
it show the Time Zone Format
Re: what is the difference between off-line back up and on-line back up in ibm db2 udb? Answer
online backup: offline:1.Inconsistent 1.consistent2.database in open state 2.db in close3.Archive mode must set 3.No need archive modeRe: Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each. Answer
archivelog mode:
archivelog mode means that online redlogfilecopied to another location before overwritten.mainly it use
for recover purpose.
noarchivelog mode:
noarchive logmode means online redlogfilenot copied to another location each time overwritten by lgwr.
Re: How can you enable a trace for a session? Answer
Enable trace at session level
EXECUTE dbms_support.start_trace;
(B) to stop trace:
EXECUTE dbms_support.stop_trace;
Re: When creating a user, what permissions must you grant to allow them to connect to the database? Answer
Grant Create session to <UserName>
Re: Can you take Image Backups using RMAN? . Can you use Backupsets created by RMAN with any other utility Answer
Backup sets, which are only created and accessed through
RMAN, are the only form in which RMAN can write backups to
media managers such as tape drives and tape libraries.
Re: Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan? Answer
A full database backup is a full copy of the database.
A transaction log backup copies only the transaction log.
A differential backup copies only the database pages modified after the last full database backup.
A file or filegroup restore allows the recovery of just the portion of a database that was on the failed disk.
Re: Name three advisory statistics you can collect. Answer
Buffer Cache Advice, Segment Level Statistics and Timed Statistics
Re: How can you gather statistics on a table? Answer
We can gather statistics on a table through>>
analyze table TABLE_NAME compute statistics , or
analyze table TABLE_NAME estimate statistics , or
you can use dbms_stats package
No comments:
Post a Comment