ARCHIVE LOG FILE MANAGEMENT

                   ARCHIVE LOG FILE MANAGEMENT

Archived log files are used for media recovery.
Before overwritting online redolog file content if we take the backup of the redolog file content into the backup destination the backup file is  called Archive log file.

REDOLOG FILES  3 TYPES:
**Online Redolog files
**Archive Redologfiles
**Standby Redologfiles
As a DBA to generate archive logfiles we need to put database in Archive log mode.
By default database will be in noarchive log mode.
To put the database into Archivelog Mode use the below steps.
SQL>shut immediate;
put the database into mount state.
SQL>startup nomount;
----------------------------------------------
SQL>alter database archivelog;
---------------------------------------------
SQL>alter database open;
SQL>select log_mode from v$database;
By above commands we are instructing oracle that start generating archivelogfiles for every logswitch.
If don't specify archive destination default destination well be 
consideredas $ORACLE_HOME/DBS/ARCH
To specify explicitly as DBA use the Parameter 
LOG_ARCHIVE_DEST in pfile or spfile.
When logswitch occurs oracle perform below 2 things.
**generate the checkpoint event
**Generate archivelogfiles.

TO KNOW THE DATABASE INARCHIVE LOGMODE OR NOT
SQL>select log_mode from v$database;
SQL>archive log list;
SQL>select archiver from v$instance;

NOTE:
In real time all production databases must be in archivelogmode
MOUNT STAGE:
 it is called maintance stage.

TO KNOW ARC BG PROCESS STARTED OR NOT IN OS LEVEL
]#ps -ef |grep arc0

when you open database automatically oracle starts arc0 bg process and responibility of this process is for every log switch it will wakeup once and reads the entire content of oldest online sequence number and copies into archive destination as single file.
We can increase the count of arch bg process to copy the content of archive redo  log group (or)fast copying of redolog group content to archive destination.
 By default oracle starts 2 arc bg process in case of 10g .
11g,12c by default oracle starts 4 arch bg process.
Dynamically we can increase the content of arch bg process by specifying the high value  for the parameter.
--------------------------------------------------
LOG_ARCHIVE_MAX_PROCESSES
---------------------------------------------------
LOG_ARCHIVE_PROCESS=2

default value of this parameter 2 in 10g we can specify maximum value 10  where as 11g&12c you can specify max value 30
arc0 to arc9 and arca to arcj+10
SQL>alter system set log_archive_max_process=4 scope=spfile;

TO CHANGE THE LOG_ARCHIVE DESTINATION
SQL>alter system set log_archive_dest='E/arc' scope=both;

SQL>archive log list;
SQL>select group#,status,sequence#,archived from v$log;
SQL>alter system switch logfile;
you can check E/arc directory created files;
SQL>show parameter log_archive_start
            type      value
        boolean     false
From 10g onwards archiveing process is automatic because
 it is always (10g,11g,12c) assumes 
log_archive_start parameter as TRUE

SQL>show parameter log_archive_format
The naming convention of the archive log file decided by
 the parameter log_archive_format
universally accepted naming convention for
 archive log files is %t_%s_%r.arc(%t_%s_%r.dbf default )
 (thread no:1,sequence no,resetlogid)

SQL>alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL>select group#,status,sequence#,archived from v$log;

DATAFILE&TABLESPACES

                               DATAFILE MANAGEMENT

                              -----------------------------------------------
A datafile is a file that correspondens with a tablespace. One
datafile can be used by one tablespace, but one tablespace can
has more than one datafiles. An Oracle databae include of a 
number of physical files called datafile.
Datafiles are mandatory files inside a database.
datafiles are used to stored information of enduser (or)businessdata.
Universaly accepted naming convention for datafile .dbf.
Datafiles are Physical files.
Datafiles are made of oracle blocks where 
Each block size would be 2k,4k,8k.16k,32k  default 8k.
The blocksize is determined by  the parameter db_block_size .
For better managebility of datafilesm oracle introduced 
tablespace concept.
Tablespace is logical entity with has physical logica structure.
Tablespace is collection of physical datafiles and logical tables.
As a end users are always store data in tables.
oracle internally store those tables data into datafile blocks 
physically.
Tablespace is a collection of one or more datafiles

Tablespace are devided into 2 types.

MANDATORY TABLESPACE.
UNMANDATORY TABLESPACE.

1>MANADATORY TABLESPACE:
-------------------------------------------
This tablespaces are mandatory because without  these 
tablespace we cannot create the database.
we need to specify these during database creation.
In 9i system is mandatory and 10g system,sysaux
11g system,sysaux,undotablespace

SYSTEM TABLESPACE:
----------------------------------
This will the first tablespace which is created inside during a 
database creation.
which  ever the first datafile which specified create database 
command that datafile will be placed assigned to system tablespace.
we cannot change this tablespace name ever.
system tablespace contains datadictionary tables.

TO KNOW THE CURRENT EXISTING IN THE TABLESPACES:
SQL>select  tablespace_name,status from dba_tablespaces;

By default tablespace status is online i.e datafile is open.
By default we will be create a tablespace is READ WRITE mode.
If tablespace is in READ WRITE mode all the tables and datafiles 
present under the tablespace will be READWRITE mode.
----------------------------------------------------------
SQL>select name,enabled from v$datafile;
----------------------------------------------------------
------------------------------
SYSAUX TABLESPACE:
--------------------------
It is new manadatory tablespace from 10g.
it is an auxiliary tablespace for the system and it is introduced 
by the burden of system tablespace.
From 10g all metadata table which are introduced will be 
automatically stored in sysaux tablespaces.
To know the all new features information which are supported 
by sysaux tablespace.
we need to query           V$SYSAAUX_OCCUPANTS
we cannot drop sysaux tablespace.
sysaux tablespace cannot made read only because it is exist 
inside a database it is always in READWRITE mode.
we cannot rename this one.
sysaux tablespace can be made offline.


---------------------------------------------
UNDO TABLESPACE:
------------------------------------
From g it is mandatory tablespace.
It is used basically support rollback operations it holds data only.

UNMANDATORY TABLESPACES::

------------------------------------------
These tablespaces holds enduser data interms of table
we create these tablespace while creating database or even 
after creating a database.

SQL>create tablespace tablespacename datafile'------' size=memory;

SYNTAX TO ADD DATAFILE:
------------------------------------------
SQL>alter tablespace  tablespacename add datafile 'path location' size=memory;

RENAMING TABLESPACE:
SQL>alter tablespace existing tablespacename rename to newname;
note:To rename a tablespace status must online

when tablespace is empty this syntax use
SQL>drop tablespace tablepacename;

SQL>drop tablespace tablespacename including contents;
only tablespace will be deleted not datafile.

SQL>drop tablespace tablespacename including contents and datafiles;
physical datafiles also deleted.

REDOLOGFILES

                   REDO LOG FILE MANAGEMENT

when ever any concurrent user says commit or Rollback oracle immediately invokes the LGWR bg proccess to flush the entire content of Redollog buffer to Redologfile.

Redolog file holds Redorecords which are moved from redologbuffer .

During the time logwriter copies the data from Redologbuffer to redologfiles  it wakes on wait event called 'logfile sync'.

Redlogfile use for instance Recovery.

While the database opening SMON bg process cross check start scn value with the stop _scn value if both are not matching the smon initites instance recovery.

As a part of instance recovery SMON reads  and apply all redologrecords which are generated between previous checkpoint scn number to the current scn number and top of the corresponding datafile Blocks.after its open the database.Redologfiles are also used for Media Recovery.

Redologfile structure:

------------------------------

Redologfile is Made up of o.s blocks.

if the first two blocks inside a redologfile is denoted as redologfile header.

every Redologfile in a database will have it's own header.

The first block inside a header containts information like o.s block size and total no of blocks allocated to that file and o.s name.

second block of header contains information like database name,databaseid,thread number(1),start scn,,start time,endscn,endtime etc.

All the remaining blocks inside a Redologfile are used fir holding the Redorecords.

If redorecords size is smallerthan pair of osblock inside a redologfile than that can be accomodated perfectly in that pair otherwise a redorecord can span across multiple o.s block if redorecord size is very high.

structure of Redorecord.

-------------------------------

Every Redorecord will have it's own header. The first 20bytes of space it's reserved fo it's header.

Every Redorecord holds below imformation.

They are Thread number,scnnumber,Redobyteaddress(RBA)

Thread number is always 1.

SCN number indicates at what time record got generated .

RBA specefies as address of redorecord.

RBA is a collection of logsequence number(LSN)+os block number+offset.

LOG SEQUENCE NUMBER:

----------------------------------------

When we start database first time log sequence number will be 1 for every logswitch LSN number will keep on incrementing by 1.

Log sequence number is used to identify a group of records inside an a database.

LSN plays major role in case of recoveries.

There are 3 types of logswitches in

1>Automatic

2>Manual

3>Time based logswitch.

Automatic:

when Redologfile is completely full it automatically switches to next redologfile.

Manual:

As a DBA we can issue below command to force on immediate log switch.

SQL>alter system switch logfile;

Time based logswitch:

To perform logswitch based on a time we used the parameter archive_log_target dynamic parameter in pfile.

SQL>alter system set archive_log_target=1800 scope=spfile;

This parameter guarantee logswitch for the time specified by this parameter.

In real time we 30min logswitch occurs.In day 48 log switch occurs.

Logwriter cannot able to write into corrupted redologfiles.

If unable to write than database will go into hangstate,To overcome to this problem we use multiplex the redlogfiles.

If you maintain morethan one redologfile even one redologfile gets corrupted in that oracle can still operate with remaining corrupted files.

To bring all redologfiles under a single unit we use concept called redolog group.

Redolog group is a logical entity which is collection of one or more redologfiles.