The
practice covers creating and multiplexing redo log files in an Oracle9I
database.
Instructions
1.
List the number and location of existing log files and display the
number of redo log file groups and members your database has.
Hints
·
Query the
dynamic performance view V$LOGFILE.
·
Use the
dynamic performance view V$LOG.
|
SQL>
SELECT member FROM v$logfile; MEMBER -------------------------------------/u01/home/dba01/ORADATA/u03/log02a.rdo /u01/home/dba01/ORADATA/u03/log01a.rdo SQL>
SELECT group#, members FROM
v$log;
GROUP# MEMBERS ----------
----------
1
1
2
1 |
2.
In which database mode is your database configured?
Is archiving enabled?
Hints
·
Query the
dynamic performance view V$DATABASE.
·
Query the
dynamic performance view V$INSTANCE.
|
SQL>
SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG SQL>
SELECT archiver FROM
v$instance; ARCHIVE ------- STOPPED |
3.
Add a redo log member to each group in your database located on u04,
using the following naming conventions and verify the result:
Add member to Group 1: log01b.rdo
Add member to Group 2: log02b.rdo
Hints
·
Execute the
ALTER DATABASE ADD LOGFILE MEMBER command to add a redo log member to each
group.
·
Query the
dynamic performance view V$LOGFILE to verify the result.
|
SQL>
ALTER DATABASE ADD LOGFILE MEMBER
2 '$HOME/ORADATA/u04/log01b.rdo'
to Group 1,
3 '$HOME/ORADATA/u04/log02b.rdo'
to Group 2; Database
altered. SQL>
COLUMN GROUP# FORMAT 99 SQL>
COLUMN MEMBER FORMAT a40 SQL>
SELECT * FROM v$logfile; GROUP#
STATUS TYPE MEMBER ------
------ ------- ------------------------------------
2
ONLINE u01/home/dba01/ORADATA/u03/log02a.rdo
1 STALE ONLINE
u01/home/dba01/ORADATA/u03/log01a.rdo
1 INVALID ONLINE u01/home/dba01/ORADATA/u04/log01b.rdo
2 INVALID ONLINE u01/home/dba01/ORADATA/u04/log02b.rdo |
4.
Add a redo log group with two members located on u03 and u04 using the
following naming conventions and verify the result.
Add Group 3: log03a.rdo
and log03b.rdo
Hints
·
Execute the
ALTER DATABASE ADD LOGFILE command to create a new group.
·
Query the
dynamic performance view V$LOGFILE to display the name of the new members of
the new group.
·
Query the
dynamic performance view V$LOG to display the number of redo log file groups
and members.
|
SQL>
ALTER DATABASE ADD
2 LOGFILE
GROUP 3('$HOME/ORADATA/u03/log03a.rdo',
3 '$HOME/ORADATA/u04/log03b.rdo')
SIZE 1024K; Database
altered. SQL>
COLUMN GROUP# FORMAT 99 SQL>
COLUMN MEMBER FORMAT a40 SQL>
SELECT * FROM v$logfile; GROUP#
STATUS TYPE
MEMBER ------
------- ------- ------------------------------------
2
ONLINE
/u01/home/dba01/ORADATA/u03/log02a.rdo
1 STALE
ONLINE
/u01/home/dba01/ORADATA/u03/log01a.rdo
1 INVALID
ONLINE /u01/home/dba01/ORADATA/u04/log01b.rdo
2 INVALID
ONLINE /u01/home/dba01/ORADATA/u04/log02b.rdo
3
ONLINE
/u01/home/dba01/ORADATA/u03/log03a.rdo
3
ONLINE
/u01/home/dba01/ORADATA/u04/log03b.rdo 6
rows selected. SQL>
SELECT group#, members FROM
v$log; GROUP#
MEMBERS ------
----------
1
2
2
2
3
2 |