Tuesday, July 04, 2006

Redo Log Files

Online Redo log file GROUPS:


ADDING

ALTER DATABASE ADD LOGFILE GROUP 3

('ORA_DIR\log3a.rdo', 'ORA_DIR\log3B.rdo') SIZE 10M;

DROPPING

ALTER DATABASE DROP LOGFILE GROUP 3;

Online Redo log file group Members:


ADDING

ALTER DATABASE ADD LOGFILE MEMBER

'ORA_DIR\log1d.rdo' TO GROUP 1,

'ORA_DIR\log2d.rdo' TO GROUP 2,

'ORA_DIR\log3d.rdo' TO GROUP 3;

DROPPING

ALTER DATABASE DROP LOGFILE MEMBER

'ORA_DIR\log3d.rdo';

RENAMING / RELOCATING Redo log files

ALTER DATABASE CLEAR LOGFILE 'new_ilename';

ALTER DATABASE RENAME FILE 'filename' TO 'new_filename;

Views:

V$LOG , V$LOGFILE

Parameter

MAXLOGFILES, MAXLOGMEMBERS, DB_CREATE_ONLINE_LOG_DEST_n,
LOG_ARCHIVE_START (ARCHIVELOG, NOARCHIVELOG)


Structure of Redo log files:

MAXLOGFILES

Absolute maximum of online redo log file groups.

MAXLOGMEMBERS

Maximum number of members per group.

Functioning of Redo log: Redo log files record all the changes made to the data & provide a recovery mechanism. Redo log files are only used for recovery. These are used in a situation such as instance failure to recover committed data that has not been written to the datafiles. Oracle server sequentially writes all changes made to the database to Redo log buffers.

Checkpoint: During a checkpoint DBWn writes dirty databse buffers, that are covered by the logs that is being checkpointed, to the datafiles.

Adding Online Redo log file groups:

ALTER DATABASE [database] ADD LOGFILE
[GROUP group_integer] filespec
[, GROUP group_integer] filespec]...

Note : If GROUP clause is ommitted Oracle Server will automatically generate the value.

Adding Online Redo Log file members:

ALTER DATABASE ADD LOGFILE MEMBER
[ 'filename' [REUSE] TO { GROUP group_integer
[, 'filename [REUSE]]....
| ('filename' [, 'filename']...)
}
]....

OEM : Database->Storage -> Redo Log Groups [right click] -> create -> input info under General Tab -> create


Dropping Online Redo log file groups:

ALTER DATABASE [database]

DROP LOGFILE {GROUP integer | ('filename' [,'filename']...)}
[,{GROUP integer|('filename'[,filename']...)}]...

Restrictions:

OEM : Database->Storage ->Redo Log Groups-> [right click] remove -> confirm

Dropping Online Redo Log file members:

ALTER DATABASE [database]
DROP LOGFILE MEMBER
'filename' [,'filename']...

Restrictions:

OEM : Database->Storage ->Redo Log Groups-> select redo log group->[General Tab] -> select member->[right click] remove->confirm

Relocating/Renaming Online Redo Log files:

Note: Before renaming ensure that the new redo log files exists. Oracle server only changes the pointers in the control file.

OEM : Database->Storage ->Redo Log Groups-> [select the redo log group]->modify filename/file Directory or relocate members.


CONFIGURING ONLINE REDO LOG FILE:

OMF managed ONLINE REDO LOG FILE:

Parameters: DB_CREATE_ONLINE_LOG_DEST_n

where n the multiplexed copy number.

DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2

Groups can be added without any file specifications:

ALTER DATABASE ADD LOGFILE;

Dropping a Group:

ALTER DATABASE DROP LOGFILE GROUP 3;

Archieved Redo Log Files: Can't be OMF.

ARCHIVED REDO LOG FILES:

Advantages of running database in ARCHIVELOG mode and archiving redo log files:

By default the database is created in NOARCHIVELOG mode. The redo log files are overwritten each time an online redo log file is filled & log switch occurs. The overwriting occurs only after the checkpoint for that group is completed.

In ARCHIVEMODE inactive groups of filled online redo log files must be archived. Since all the changes to database are recorded in online redo log files, so the DBA can use physical backup and the archived redo log files to recover database without losing any commited data.

Online redo log files can be archived either manually or automaticallydepending on the initialization parameter LOG_ARCHIVE_START :

TRUE

Indicates archiving is automatic. ARCn initiates archiving of the filled log groups at eveyr log switch.

FALSE

Default value. Disables. DBA must manually execute a command to back up redo log files each time. All or specific redo log files can be archived manually.

OEM : Database->Instance -> Configuration-> Recovery Tab->mode,filename format, destination directory

Views

V$LOG, V$LOGFILE

SQL>desc v$log;

Name Null? Type

----------------------- -------- --------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE

SQL> SELECT group#, sequence#, bytes, members, status from v$log;


GROUP# SEQUENCE# BYTES MEMBERS STATUS

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

1 0 104857600 1 UNUSED

2 17 104857600 1 CURRENT

3 15 104857600 1 INACTIVE

Status

Description

UNUSED

indicates that the online redo log group has never been written to. This is the state when an online redo log file is just added.

CURRENT

the current & active online redo log file group.

ACTIVE

implies active but not current. It is required for crash recovery. It may be in use for block recovery. It may or maynot be archived.

CLEARING

Indicates that the log is being recreated as an empty log after an ALTER DATABASE CLEAR LOGFILE command. Once log is cleared, the status changes to UNUSED.

CLEARING_CURRENT

Indicated that the current log is being cleared of a closed thread. The log can stay in this status for long if there is some failure in the switch such as I/O error writing the new log header.

INACTIVE

Indicates it is no longer needed for instance recovery. It may or maynot be archived.

SQL> desc v$logfile;

Name Null? Type

----------------------------------------- -------- --------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)

SQL> select group#, status, type, substr(member,1,50) from v$logfile;

GROUP# STATUS TYPE SUBSTR(MEMBER,1,50)

---------- ------- ------- ----------------------------------
3 STALE ONLINE D:\ORACLE\ORADATA\CHIK\REDO03.LOG
2 ONLINE D:\ORACLE\ORADATA\CHIK\REDO02.LOG
1 ONLINE D:\ORACLE\ORADATA\CHIK\REDO01.LOG


Status

Description

INVALID

indicates the file is inaccessable.

STALE

Indicates the contents of the file are incomplete.

DELETED

The file is no longer used.

Blank

implies file is in use.


Comments: Post a Comment



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google