06 February 2011

How to Add Log file in Oracle Database

If you are executing below query you will get the current log file and location below query and sample output
Query:- 
select * from v$logfile;


Output:-
GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE
2,,ONLINE,/testdata/r02/oradata/log02a.dbf,NO
2,,ONLINE,/testdata/r02/oradata/log02b.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01a.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01b.dbf,NO

Adding LogFile

ALTER DATABASE ADD LOGFILE MEMBER '/testdata/r03/oradata/log01c.dbf' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/testdata/r03/oradata/log02c.dbf' TO GROUP 2;


GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE

select * from v$logfile;

2,,ONLINE,/testdata/r02/oradata/log02a.dbf,NO
2,,ONLINE,/testdata/r02/oradata/log02b.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01a.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01b.dbf,NO
2,,INVALID,/testdata/r02/oradata/log02c.dbf,NO
1,,INVALID,/testdata/r03/oradata/log01c.dbf,NO
Your logfile is added but status is INVALID so please execute below Query

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM CHECKPOINT;

select * from v$logfile;
Output:-
GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE
2,,ONLINE,/testdata/r02/oradata/log02a.dbf,NO
2,,ONLINE,/testdata/r02/oradata/log02b.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01a.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01b.dbf,NO
2,,ONLINE,/testdata/r02/oradata/log02c.dbf,NO
1,,ONLINE,/testdata/r03/oradata/log01c.dbf,NO

No comments: