Format for LOG_ARCHIVE_FORMAT in Oracle

LOG_ARCHIVE_FORMAT parameter controls the format of the archive log file name. This parameter can only be used when the redo log is in ARCHIVELOG mode . LOG_ARCHIVE_FORMAT parameter is static in nature so it comes into action after restart of the instance. If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and in the alert log the following message would be reported .

 “ORA-00294: invalid archivelog format specifier..” 

So if we change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

The format of specifying the archive redo log filename is given below :

LOG_ARCHIVE_FORMAT = “LOG%s_%t_%r.arc”
Various parameters that can be used with the LOG_ARCHIVE_FORMAT parameter are given below:
%s – log sequence number
%S – log sequence number, padded with zero
%t – thread number
%T – thread number, padded with zero
%a – activation id
%d – database id
%r – reset logs id

Whenever uppercase is used for a variable, such as %S or %T, it forces the value of the variable to be of fixed length, and the value to the left is padded with zeros.

Below is the Demo of the log_archive_format parameters.

SQL> alter system set log_archive_dest_1='location=D:\archive\';
System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\archive\
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13
        
SQL> alter system set log_archive_format='noida_%s_%t_%r.arc' scope=spfile;
System altered.

SQL> shut immediate
SQL>startup

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\archive\
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13

SQL> alter system switch logfile;
System altered.

Now the  new archive log file  name  like  'NOIDA_13_1_749730106.ARC'

For more click here 


Enjoy       :-)

Difference Between LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1

Once  my friend call me and told that he has confusion about the  LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n . I explain him and decided to post it here .

LOG_ARCHIVE_DEST is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .

The  LOG_ARCHIVE_DEST_n initialization parameter defines up to ten (where n = 1, 2, ... 10) destinations in oracle 10g and thirty one (n=1,2....31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify a corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.

LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.

If we are using Oracle Enterprise Edition, LOG_ARCHIVE_DEST parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed but we have not specified any LOG_ARCHIVE_DEST_n parameters,then LOG_ARCHIVE_DEST parameter is valid. If we have LOG_ARCHIVE_DEST parameter set and we have to set more than two archive location then follow the below steps :

SQL> alter system reset log_archive_dest='' scope=both;

SQL> shutdown immediate

SQL> startup

SQL> alter system set log_archive_dest_1='location=C:\archive\' ;

SQL> alter system set log_archive_dest_2='location=D:\oracle\archive\' ;

Enjoy      : -)

Control File Contents

A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle Database; no database administrator or user can edit a control file. It contains (but is not limited to) the following types of information :

• Database information (RESETLOGS SCN and their time stamp)
• Archive log history
• Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not).
• Redo threads (current online redo log)
• Database's creation date
• database name
• current archive log mode
• Log records (sequence numbers, SCN range in each log)
• RMAN catalog
• Database block corruption information
• Database ID, which is unique to each DB.                                                                                                                                                                                                                                                                                              

The location of the control files is specified through the control_files init parameter file .


Size of control files :
The size of the control files is governed by the following parameters

• maxlogfiles
• maxlogmembers
• maxloghistory
• maxinstances
• control_file_record_keep_time

Sections :

The controlfile contains the following sections:

Archives Log (reusable)

Backup Corruption (reusable)

Backup Datafile (reusable)

Backup Piece (reusable)

Backup Redolog (reusable)

Backuo Set (reusable)

Backup spfile

CKPT Process

Copy Corruption (reusable)

Datafile

Datafile Copy (reusable)

Datafile History

Database Incarnation

Deleted Objects (reusable)

Filename

Flashback Log

Instance Space Reservation

Log History (reusable)

MTTR

Offline Range (reusable)

Recovery Destination

Removable Recovery Files

Rman Status

Rman Configuration

Redo Threads

Redo Logs

Tablespace

Temporary Filename

Thread Instance Name Mapping

Proxy Copy

The  minimum  number of  days that  a  reusable  record  is kept  in  the  controlfile is controlled  by  the control_file_record_keep_time parameter. These sections consist of records. The size, total number  and number of used record are exposed through v$controlfile_record_section.

To Check the information inside controlfile  use the below command  :
SQL> alter database backup controlfile to trace as   'C:\CREATE_CONTROL.sql' ;




Enjoy      :-)

What is RBA ?

An  RBA (Redo Block Address) points  to  a  specific phyical  location  within a redo logfile . The "tail of the log" is the RBA of the most recent redo entry written to the redo log file . It is ten bytes long and has  three  components .

the log file sequence number  ( 4 bytes)
the log file block number       ( 4 bytes)
the byte offset into the block at which the redo record starts (2 bytes)

For Example :  RBA [0x775.2.10]  maps to Log squence , Block number with byte offset .

There are different types of RBA available in SGA , the following are :

Low RBA : Dirty buffer contains first redo change address called Low RBA. From x$bh we can check low RBA.

High RBA : Dirty buffer contains last and most recent redo changes address called High RBA. From x$bh we can check High RBA.

Checkpoint  RBA : DBWR  has written  buffers from  checkpoint queue  are pointing  to  checkpoint  RBA while  incremental checkpoint  is  enabled. This  RBA copies  in  to  control  file’s checkpoint  progress record. When instance recovery occurs that time it starts from checkpointing  RBA from control  file. We  can check this RBA from x$targetrba (sometimes from x$kccrt).

On-disk RBA : That RBA which was flushed in to online Redo Log File on disk. This RBA recorded in to control file record  section. We can check from x$kcccp for on-disk RBA (sometimes from x$targetrba).

How RBA comes in Pictures :
CKPT records checkpoint information to controlfile for maintaining book keeping information like checkpoint  progress . Each instance checkpoint  refers  to some  RBA (called checkpoint RBA) whose  redo prior to this RBA have been written to disk. Hence recovery time is difference between checkpoint RBA and end of the redo log  .

Given a  checkpoint RBA, DBWR writes  buffers  from  the  head  of  the queue  until  low RBA of the buffer at  the head of the checkpoint queue  is greater  than  the checkpoint  RBA . At  this  point ,CKPT can  record  this checkpoint  progress  record  in  control file  (phase 3).
PHASE(1)  process  initiating  the checkpoint (checkpoiting  RBA or current RBA is marked) (The RBA of the last change made to a buffer) at the time reuqest is initiated.
PHASE (2)  DBWR  writes all  required  buffers  i.e  all  buffers  that  have  been modified at RBAs less than or equal to the checkpoint RBA. After all required buffers have been written, in
PHASE (3)  CKPT process records the completion of the checkpoint in control file.

The checkpoint  RBA  is copied  into  the  checkpoint  progress  record  of  the  controlfile by the checkpoint  heartbeat  once  every  3  seconds. Instance recovery, when needed, begins from the checkpoint  RBA  recorded  in  the  controlfile. The  target  RBA is the point up to which DBWn should seek to advance the checkpoint RBA to satisfy instance recovery objectives.

The term sync RBA is sometimes used to refer to the point up to which LGWR is required to sync the thread. However, this is not a full RBA -- only a redo block number is used at this point.


Click Here for Reference

What is codd’s rule ?

Dr. E.F. Codd, an IBM researcher, first developed the relational data model in 1970. Over time it has proved to be flexible, extensible, and robust. In 1985 Codd published a list of 12 rules known as "Codd's 12 Rules" that defined how a true RDBMS should be evaluated. Understanding these rules will greatly improve the ability to understand RDBMS's in general, including Oracle. Also note that these rules are "guidelines" because, to date, no commercial relational database system fully conforms to all 12 rules .

It is important to remember that RDBMS is not a product, it is a method of design. Database products like the Oracle Database, SQL Server, DB2, Microsoft Access, etc. all adhere to the relational model of database design. The rules are as follows :

1. )  The Information Rule:  For a database to be relational all information is represented as data values  .

2.)  The Rule of Guaranteed Access:  The data represented in table by using table name, column name and primary key value defined for that table.

3.) The systematic treatment of null values:  If a information is not present then they are represented as null values in database. But it is vital to note that primary key values cannot be not null and also nul values are different from spaces or zeroes.

4.) The database Description Rule: The description f database is also maintained in a place called as data dictionary and users can access this if they have proper authority or privilege to do the same.

5.) The comprehensive data sublanguage rule : Database must support the following namely  Data definition . 

•  View definition
•  Data manipulation
• Integrity constraints
• Authorization

6.)  The view updating rule : All views that are updatable by theory can also be updated by the system.

7.) The insert and update rule: Data manipulation commands like insert, update, delete must be operational on multiple rows rather than on single row.

8.) The physical independence rule: The database access by users must be independent of changes in storage representation or access methods to data.

9.) The logical data independence rule : The end user application programs or other activities must be independent or must be unaffected when there is a change to the design of the database.

10.) Integrity independence rule : The constraints namely the integrity constraints defined should also be stored in database as data in tables.

11.) The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully :

• when a distributed version of the DBMS is first introduced; and
• when existing distributed data are redistributed around the system.

12.) No subversion rule : If an RDBMS supports a lower level language then it should not bypass any integrity constraints defined in the higher level.

Enjoy        :-)