Part-2

Can anyone tell me the main difference between the Checkpoint and SCN. How does that work in reality -

Checkpoint

Checkpoint in database is used to reduce the amount of the time for recovery. It is a background process in oracle(CKPT), which cause DBWR to write all the data blocks that have been modified since the last checkpoint to the datafile.Checkpoint is responsible for->Signaling DBWn->Updating datafile headers with checkpoint info. (i.e. SCN)->Updating Control file with checkpoint info. (i.e. scn)

SCN

SCN is the ever increasing number to determine the age of database.it's System Change Number / System Commint Number...It's used to determine the consistancy of database..It's stored at various places like control file, data files, redo log files.If it doesn't match, that means inconsistancy..

why do we add online redo group and when do we do it in real time . -

we add redolog group to give the checkpoint(CKPT) enough time to write the scn on the control and the data file header. this happens wen the redolog group is full and a log switch is occurs. to avoid the logwriter(LGWR) to overwrite the redolog groups before the scn's and the LSN's(log sequence number) are writen on the header of the controlfile and the data file by the CKPT.

Compare and contrast TRUNCATE and DELETE for a table. -

TRUNCATE:will remove the rows permanently from table without deleting the table structure. And will free up the space occupied by the data. Data can't be ROLLBACKDELETEwill remove the data from table but will not release the space occupied by rows. Can be retrieved by ROLLBACK command. Both are used to remove data from the table.But the difference between their work is:->Delete is DML statement, While Truncate is DDL statement.-> The changes done by Delete commmand are stored in redo log files, while those by Truncate, are not logged.-> So, the changes made by Delete command can be rolled back, but the Changes made by Truncate are Permanent...-> Moreover, Because of this nature, Truncate works faster than Delete command

why oracle returns the error snapshot too old? -

with the help of a example we can understand snapshot to old error .suppose scott run's a script where lot's of updation is performed. mean's lot's of undo is generated. and if your undo tablespace is small then it flush out the old values within it.And suppose there is another user try to see the value that is updated by the scott.in this case because that value is flushed by the scott so if other user is try to see the value oracle server prompt's a message "SNAPSHOT TO OLD"the solution is:-1. increase the size of undo tablespace2. check your undo retention policy.

what is the use of maxlogfiles parameter? -

MAXLOGFILES-->This parameter specifis the maximum number of redo log file groups that can ever be created for the database. Oracle Database uses this value to determine how much space to allocate in the control file for the names of redo log files. The default, minimum, and maximum values depend on your operating system.This value must be at least 2.

What is the difference between Hot and Cold backup? -

Hot backup is done when database is online while cold backup is done when database is offline.

how do we create data dictionary -

catalog.sql or catproc.sql script create all data dictionary views.

the use of control file -

It maintains the consistency and integrity of database physical files. It keeps track of the present state of database structure. It is opened in Mount state.