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.***

Wednesday, 9 May 2012

DBA Interview Questions with Answers Part7

My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.
One can select from V$BACKUP to see which datafiles are in backup mode From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:
Note: This command must be issued when the database is mounted, but not yet opened.

Does Oracle write to data files in begin/hot backup mode?

When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files. When in backup mode, Oracle will write complete changed blocks to the redo log files. Because of this, increased log activity and archiving during on-line backups. To solve this problem, simply switch to RMAN backups.
Difference Consistent and Inconsistent Backup
The backup taken in shutdown state or in same point in time are referred to as consistent. Unlike an inconsistent backup, a consistent whole database backup does not require recovery after it is restored, here all header of datafile belongs to writable tablespace have the same SCN. These datafile donot have any change past this check point SCN. The SCN of datafile header matches exactly controlfile checkpoint.
An inconsistent backup is a backup of one or more database files that you make while the database is open or after the database has shut down abnormally. This means that the files in the backup contain data taken from different points in time. This can occur because the datafiles are being modified as backups are being taken. Not any of the above mentioned properties are exist here.  A recovery (Applying all the archive and online redo logs) is needed in order to make the backup consistent.

Difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
Difference between Complete and Incomplete Recovery?
Complete recovery involves using redo data or incremental backups combined with a backup of a database, tablespace, or datafile to update it to the most current point in time. It is called complete because Oracle applies all of the redo changes contained in the archived and online logs to the backup. Typically, you perform complete media recovery after a media failure damages datafiles or the control file.
Incomplete recovery, or point-in-time recovery we do not apply all of the redo records generated after the most recent backup or when archive redo log is missing.
Because you are not completely recovering the database to the most current time, you must tell Oracle when to terminate recovery. You can perform the following types of media recovery.
Time based Recovery, Cancel based Recovery, Change based Recovery, Log sequence Recovery
What happens when we open the database with Resetlogs option after incomplete recovery?
The RESETLOGS operation creates a new incarnation of the database—in other words, a database with a new stream of log sequence numbers starting with log sequence 1.
Before using the OPEN RESETLOGS command to open the database in read/write mode after an incomplete recovery, it is a good idea to first open the database in read-only mode, and inspect the data to make sure that the database was recovered to the correct point. If the recovery was done to the wrong point, then it is easier to re-run the recovery if no OPENRESETLOGS has been done.

Difference between online and offline backups?

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode. A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.
It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and doesn't require database downtime.
Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
What is the difference between Views and Materialized Views in Oracle?
Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.
Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query result set has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. Materialized views having several other advantages over simple view.
What happens when you set CONTROL_FILE_RECORD_KEEP_TIME to 0
Never set CONTROL_FILE_RECORD_KEEP_TIME to 0. If you do, then backup records may be overwritten in the control file before RMAN is able to add them to the catalog. As we know that The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter determines the minimum number of days that records are retained in the control file before they are candidates for being overwritten.
How to find the last refresh of your database (when the recovery with resetlogs performed)?
If the cloned database has been opened with RESETLOGS option, you can try checking out V$DATABASE.RESETLOGS_TIME. if the V$DATABASE.CREATED is not equal to V$DATABASE.RESETLOGS_TIME...there is a possibility that it might be opened with resetlogs option. I don't have the required set up to check and confirm this myself....but this is something you can get it a shot. 

Command to find files created a day before

find . -type f -mtime 1 -exec ls -lth ‘{}’ \;
Initially Flashback Database was enabled but noticed Flashback was disabled automatically long time ago. What is the Issue?
It could be because the flashback area 100% Once Flashback Area become 100% full then oracle will log in Alert that Flashback will be disabled and it will automatically turn off Flash Back without user intervention.

How can I check if there is anything rolling back?

It depends on how you killed the process. If you did and alter system kill session you should be able to look at the used_ublk block in v$transaciton to get an estimate for the rollback being done. If you killed to server process in the OS and pmon is recovering the transaction you can look at V$FAST_START_TRANSACTIONS view to get the estimate

How do you see how many instances are running?

In Linux, Unix the command: ps -ef|grep pmon
In Windows: services.msc

Which is more efficient Incremental Backups using RMAN or Incremental Export?


The current logfile gets damaged. What you can do now?

Once current redolog file is damaged, instance is aborted and it needs recovery upto undamaged part. Only undamaged part can be recovered. Here DBA must apply time based recovery, means it can be a point in time or specified by SCN. It leads to incomplete recovery
Where should the tuning effort be directed?
Consider the following areas for tuning in order to increase performance of DB
Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning:
Properly size your database buffers (shared pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.
Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc
Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.
Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.
What are the common Import/ Export problems?
ORA-00001: Unique constraint (...) violated - You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).
ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO
ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y while importing
IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
By mistake a use drop or truncate a Table then what is the best method to recover it?
There are several methods possibly through RMAN such as:
Restore and recover the primary database to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time.
Restore and recover the tablespace to a point in time before the drop. This is a better option, but again, it takes the entire tablespace back in time.
Restore and recover a subset of the database as a DUMMY database to export the table data and import it into the primary database. This is the best option as only the dropped table goes back in time to before the drop.
How to find running jobs in oracle database
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; --all active users
select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS; --for oracle 10g
How to find long running jobs in oracle database
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops 
where time_remaining = 0 order by time_remaining desc
Login without password knowledge
This is not the genuine approach consider it as a practice.
SQL> CONNECT / as sysdba
SQL> SELECT password FROM dba_users WHERE  username='SCOTT';
--------------- ---------------
User altered.
SQL> CONNECT scott/anything
OK, we're in. Let's quickly change the password back before anybody notices.
User altered.
While applying the CPU Patch why we need to update the Oracle Inventory?
Because when you apply the CPU it updates the oracle binaries.