Redo Log

sql> spool d:\oracleerror\redolog.txt sql> ---see the redolog group sql> select * from v$log; group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 0 104857600 1 no unused 717467 11-jan-08 2 1 763138 11-jan-08

19

104857600

1 no

inactive

3 1 764014 11-jan-08

20

104857600

1 no

current

sql> ------see the group member path sql> select * from v$logfile; group# status type ---------- ------- ------member -------------------------------------------------------------------------------3 online d:\oracle\oradata\sue\redo03.log 2 online d:\oracle\oradata\sue\redo02.log 1 online d:\oracle\oradata\sue\redo01.log sql> -----create the redolog group sql> alter database add logfile group 4 'd:\oracle\oradata\sue\redolog4.1.sql' size 100k; database altered. sql> select * from v$log; group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 0 104857600 1 no unused 717467 11-jan-08 2 1 763138 11-jan-08

19

104857600

1 no

inactive

3 1 764014 11-jan-08

20

104857600

1 no

current

group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------4 1 0 102400 1 yes unused 0 sql> -------change inactivestage sql> alter system switch logfile; system altered. sql>

select * from v$log;

group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 21 104857600 1 no current 766246 11-jan-08 2 1 763138 11-jan-08

19

104857600

1 no

inactive

3 1 764014 11-jan-08

20

104857600

1 no

active

group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------4 1 0 102400 1 yes unused 0 sql> alter system checkpoint; system altered. sql> select * from v$log; group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 21 104857600 1 no current 766246 11-jan-08 2 1 763138 11-jan-08

19

104857600

1 no

inactive

3 1 764014 11-jan-08

20

104857600

1 no

inactive

sequence#

bytes

group#

thread#

members arc status

---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------4 1 0 102400 1 yes unused 0 sql> set linesize 250; sql> / group# thread# sequence# bytes members arc first_change# first_tim ---------- ---------- ---------- ---------- ---------- --------------- --------1 1 21 104857600 1 no 766246 11-jan-08 2 1 19 104857600 1 no 763138 11-jan-08 3 1 20 104857600 1 no 764014 11-jan-08 4 1 0 102400 1 yes 0

status ---------------current inactive inactive unused

sql> alter system switch logfile; system altered. sql> alter system checkpoint; system altered. sql> select * from v$log; group# thread# sequence# bytes members arc first_change# first_tim ---------- ---------- ---------- ---------- ---------- --------------- --------1 1 21 104857600 1 no 766246 11-jan-08 2 1 19 104857600 1 no 763138 11-jan-08 3 1 20 104857600 1 no 764014 11-jan-08 4 1 22 102400 1 no 766482 11-jan-08

status ---------------inactive inactive inactive current

sql> ------------addmember to group 4 sql> alter database add logfile member ''d:\oracle\oradata\sue\redolog4.2.sql' to group 4; alter database add logfile member ''d:\oracle\oradata\sue\redolog4.2.sql' to group 4 * error at line 1: ora-00946: missing to keyword sql> alter database add logfile member 'd:\oracle\oradata\sue\redolog4.2.sql' to group 4;

database altered. sql> select * from v$logfile; group# status type ---------- ------- ------member -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------3 online d:\oracle\oradata\sue\redo03.log 2 online d:\oracle\oradata\sue\redo02.log 1 online d:\oracle\oradata\sue\redo01.log group# status type ---------- ------- ------member -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------4 online d:\oracle\oradata\sue\redolog4.1.sql 4 invalid online d:\oracle\oradata\sue\redolog4.2.sql sql> ----------drop member in group4 sql> ----but group4 as current stage so can't delete so switch the syetem sql> alter system switch logfile; system altered. sql> alter system checkpoint; system altered. sql> select * from v 2 sql> sql> sql> sql> select * from v$log; group# thread# sequence# bytes members arc first_change# first_tim ---------- ---------- ---------- ---------- ---------- --------------- --------1 1 21 104857600 1 no 766246 11-jan-08 2 1 23 104857600 1 no

status ---------------inactive inactive

766757 11-jan-08 3 766902 11-jan-08 4 766482 11-jan-08

1

24

104857600

1 no

current

1

22

102400

2 no

inactive

sql> -------drop the group 4 member sql> alter database drop logfile member 'd:\oracle\oradata\sue\redolog4.2.sql'; database altered. sql> select * from v$logfile; group# status type ---------- ------- ------member -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------3 online d:\oracle\oradata\sue\redo03.log 2 online d:\oracle\oradata\sue\redo02.log 1 online d:\oracle\oradata\sue\redo01.log group# status type ---------- ------- ------member -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------4 online d:\oracle\oradata\sue\redolog4.1.sql sql> -drop the member sp2-0734: unknown command beginning "-drop the ..." - rest of line ignored. sql> ----drop the group sql> alter database drop logfile group 4; database altered. sql>

select * from v$log;

group# thread# sequence# bytes members arc first_change# first_tim ---------- ---------- ---------- ---------- ---------- --------------- --------1 1 21 104857600 1 no 766246 11-jan-08 2 1 23 104857600 1 no 766757 11-jan-08 3 1 24 104857600 1 no 766902 11-jan-08

status ---------------inactive inactive current

sql> sql> sql> sql>

----------clear the redologfile -----current file can't clear -----clear group 1 alter database clear logfile group 1;

database altered. sql>

select * from v$log;

group# thread# sequence# bytes members arc first_change# first_tim ---------- ---------- ---------- ---------- ---------- --------------- --------1 1 0 104857600 1 no 766246 11-jan-08 2 1 23 104857600 1 no 766757 11-jan-08 3 1 24 104857600 1 no 766902 11-jan-08

status ---------------unused inactive current

sql> -----------------recovery the redolofile sql> --------physicaly delete then recover for ex:goto delete the redo01.ora in d;\oracle\oradata\sue\ sql> -------now shutdown & start sql> shutdown immediate database closed. database dismounted. oracle instance shut down. sql> startup ora-32004: obsolete and/or deprecated parameter(s) specified oracle instance started. total system global area 135338868 bytes fixed size 453492 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes database mounted. ora-00313: open failed for members of log group 1 of thread 1 ora-00312: online log 1 thread 1: 'd:\oracle\oradata\sue\redo01.log' sql> ----now recovery sql> alter database clear unarchived logfile group 1; database altered. sql> alter database open 2 ; database altered. sql> ---now see the d:\oracle\.. sql> spool off sql> spool d:\oracleerror\redolog.txt sql> shutdown immediate database closed.

database dismounted. oracle instance shut down. sol>----now open in pfile no error occur don't modify the pfile sql> startup pfile=d:\oracle\admin\sue\pfile\init.ora'; lrm-00109: could not open parameter file 'd:\oracle\admin\sue\pfile\init.ora'' ora-01078: failure in processing system parameters sql> startup pfile='d:\oracle\admin\sue\pfile\init.ora'; ora-32006: mts_servers initialization parameter has been deprecated oracle instance started. total system global area fixed size variable size database buffers redo buffers database mounted. database opened. sql> spool off

135338868 453492 109051904 25165824 667648

bytes bytes bytes bytes bytes