This
practice explores the architectural components involved in the reliability and
recoverability of an Oracle database.
1.
Identify the following dynamic performance views (V$ views):
a.
What is the V$ view that you must query for the names of all data files
in the database?
b.
What are the V$ views that you must query for finding the current online
redo log and names of all redo logs in the database?
c.
What is the V$ view that you must query for the names of all control
files in the database?
d.
Name the V$ view that you should check to find the name of the database
before dropping tables or shutting down the database.
e.
Name the V$ view that you must query to locate processes still connected
to the instance before shutting down the database.
1.
Solutions a.
V$DATAFILE b.
V$LOG and V$LOGFILE c.
V$CONTROLFILE d.
V$DATABASE e.
V$PROCESS |
2.
Describe the significance of the parameters LOG_CHECKPOINT_INTERVAL and
FAST_START_IO_TARGET in
instance
recovery.
When
the LOG_CHECKPOINT_INTERVAL parameter is set, the target for checkpoint
position cannot lag the end of the log by more than the number of redo
log blocks specified by this parameter. This ensures that no more than a
fixed number of redo blocks will need to be read during instance
recovery. The dynamic initialization parameter FAST_START_IO_TARGET
allows you to limit the number of blocks that must be read for crash or
instance recovery. If the value of this parameter is smaller, the
recovery performance is better because fewer blocks need to be
recovered. |
3.
What init.ora parameter configures the memory area in the SGA that
buffers recovery information in memory
before
being written to disk?
LOG_BUFFER |
4.
What is the large pool, when is it used, and what initialization
parameter configures it?
The
large pool is an area of the SGA that can be used for buffering
information in memory for Recovery Manager when IO slaves are required.
This increases the speed and efficiency of backups and restores when
using RMAN. The LARGE_POOL_SIZE parameter specifies the number of bytes
allocated from the SGA. |
5.
Set up mirroring of control files so as to have three control files.
To
add a new control file or change the number or location of the control
file, use the following steps: A.
Shut down the database:
SQL>
SHUTDOWN NORMAL B.
Copy the existing control file to a different device using
operating
system commands: In
UNIX:
$cp
-p <path to existing file>/control0l.ctl <path to new
file>/control03.ctl
$chmod
g+wx $HOME/DATA/DISK3/control03.ctl
or
$chmod
770 $HOME/DATA/DISK3/control03.ctl In
NT: C:>
copy <path to existing file>/control0l.ctl <path to new
file>/control03.ctl C.
Edit or add the CONTROL_FILES parameter and specify names for
all
the control files: In
UNIX:
$vi
init.ora control_files=<path>/control0l.ctl,
<path>/control02.ctl,
<path>/control03.ctl In
NT:
Notepad
init.ora control_files=<path>\control0l.ctl,
<path>\control02.ctl,
<path>\control03.ctl D.
Start up the database: SQL>
STARTUP |
In
this practice you will generate archive logs. More specifically you will:
1.
If your database is not already open, then start SQL*Plus, connect as
SYSDBA and start your instance and open your database.
SQL*Plus:
Release 8.1.7.0.0 - Production on Wed Apr 10 21:55:11 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
2.
Check if your database is in archivelog mode and whether automatic
archiving has been started.
SQL>
archive log list Database
log mode
No Archive Mode Automatic
archival
Disabled Archive
destination
E:\816NF\ARCHIVE\ARCHIVE1 Oldest
online log sequence
155 Current
log sequence
157 SQL> As
we can see, the database currently is in noarchivelog mode, and
automatic archiving is disabled. The fact that it is noarchivelog mode
means that currently the open database backup is not alllowed. |
In this practice you will investigate the differences between the two modes and you will switch your database mode.
ASSUMPTIONS
1.
This practice assumes that your database is currently in noarchivelog
mode.
1.
Connect to SQL *Plus as
sysdba and place the database in mount state.
If the database is open, then shutdown the database. Exit the SQL*PLUS session, then follow these steps: 1. $ sqlplus /nolog 2.
SQL> connect / as sysdba
-- if you’re
using OS authentication of privileged users. 3.
SQL> startup mount
-- You may need to
add pfile=<location of parameter file> |
2.
List the parameters LOG_ARCHIVE_DEST, LOG_ARCHIVE_START, and
LOG_ARCHIVE_FORMAT, and note the values.
SQL>
show parameter archive
--
YOUR RESULTS WILL REFLECT YOUR OWN ENVIRONMENT NAME
TYPE
VALUE log_archive_dest
string
?/dbs/arch log_archive_dest_l
string log_archive_dest_2
string log_archive_dest_3
string log_archive_dest_4
string log_archive_dest_5
string log_archive_dest_state_l
string
enable log_archive_dest_state_2
string
enable log_archive_dest_state_3
string
enable log_archive_dest_state_4
string
enable log_archive_dest_state_5
string
enable log_archive_duplex_dest
string log_archive_format
string
%t_%s.dbf log_archive_max_processes
integer log_archive_min_succeed_dest
integer log_archive_start
boolean
FALSE standby_archive_dest
string
?/dbs/arch) |
3.
Execute the command ARCHIVE LOG LIST. Note the log mode of the database
and
whether
automatic archival is enabled.
SQL>
archive log list; --
YOUR RESULTS WILL REFLECT YOUR OWN ENVIRONMENT Database
log mode
No Archive Mode Automatic
archival
Disabled Archive
destination
/oracle/core/8.1.5/dbs/arch Oldest
online log sequence 240 Next
log sequence to archive 241
Current
log sequence
241 |
4.
Set the database in archivelog mode and Open the database.
SQL>
alter database archivelog; Database
altered. SQL>
alter database open; Database
altered. |
5.
Shut down the instance with the IMMEDIATE option.
SQL>
shutdown immediate; Database
closed. Database
dismounted. ORACLE
instance shut down. |
6.
Edit the init.ora file to:
a.
Enable automatic archiving using two archive processes.
b.
Archive log files to two destinations.
c.
Use the archiving format of arch_%s. arc
a.
log_archive_maxprocesses = 2
log_archive_start = true b.
log_archive_dest_l = "LOCATION=<path>/ARCHIVE/
MANDATORY"
log_archive_dest_2 = "LOCATION=<path>/ARCHIVE2/
OPTIONAL" c.
log_archive_format = arch_%s.arc |
7.
Set the database in archivelog mode and Open the database.
SQL>
alter database archivelog; Database
altered. SQL>
alter database open; Database
altered. |
8.
Verify that two archive processes are running.
SQL>
select * from v$archive_processes;
PROCESS STATUS
LOG_SEQUENCE STAT ---------
---------- ------------ ----
0 ACTIVE
0 IDLE
1 ACTIVE
0 IDLE
2 STOPPED
0 IDLE
3 STOPPED
0 IDLE
4 STOPPED
0 IDLE
5 STOPPED
0 IDLE
6 STOPPED
0 IDLE
7 STOPPED
0 IDLE
8 STOPPED
0 IDLE
9 STOPPED
0 IDLE 10
rows selected. |
9.
Execute the ALTER SYSTEM SWITCH LOGFILE command twice, then show the
values of the ARCHIVE parameters.
Do
you see any archived log files? What is the format of the filename?
SQL>
alter system switch logfile; System
altered. SQL>
alter system switch logfile; System
altered. SQL>
select name, value 2
from v$parameter 3
where name like 'log_archive%'; --
YOUR RESULTS WILL REFLECT YOUR OWN ENVIRONMENT
NAME
VALUE Log_archive_start
TRUE Log_archive_dest
log_archive_duplex_dest
log_archive_dest_l
LOCATION=$HOME/ARCHIVE MANDATORY log_archive_dest_2
LOCATION=$HOME/ARCHIVE2 OPTIONAL log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_state_l
enable log_archive_dest_state_2
enable log_archive_dest_state_3
enable log_archive_dest_state_4
enable log_archive_dest_state_5
enable log_archive_max_processes
2 log_archive_min_succeed_dest
1 log_archive_format
arch-%s.arc |
10.
Investigate the archive files that were generated by the log switches.
How many archive files were created?
Were the same files created in both archive destinations?
In
UNIX: $
ls -l $HOME/ARCHIVE $HOME/ARCHIVE2 /db/oracle/archive/: total
154 -rw-rw
loracle dba 77824 Mar 2303:28 arch_121.arc -rw-rw
loracle dba 1024 Mar
2303:28 arch_122.arc /db/oracle/archive2/:
total154
-rw-rw
loracle dba 77824 Mar 2303:28 arch_121.arc -rw-rw
loracle dba 1024 Mar
2303:28 arch_122.arc In
NT: Use
Windows Explorer and navigate to the archive directories.
From toolbar choose detail listing. |
3.
Place the database in archivelog mode, if it has not already been done,
and make sure to enable automatic archiving.
The
content of the init.ora parameter file has to be modified, to contain
the following line: log_archive_start
= true log_archive_format
= arch_%S.log log_archive_dest
= D:\oracle\oradata\orcl\archive In
order for these values to take effect, the instance has to be restarted.
Next, the database should be in mounted mode, in order to change it from
noarchivelog mode to archivelog mode. The shortest way to achieve all
this is the following: SQL>
shutdown Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup mount ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. SQL>
alter database archivelog; Database
altered. SQL>
alter database open; Database
altered. SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
D:\oracle\oradata\orcl\archive Oldest
online log sequence
155 Next
log sequence to archive 157 Current
log sequence
157 SQL> |
4.
Generate archive logs automatically by forcing a log switch
SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
D:\oracle\oradata\orcl\archive Oldest
online log sequence
156 Next
log sequence to archive 157 Current
log sequence
157 SQL>
alter system switch logfile; System
Altered SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
D:\oracle\oradata\orcl\archive Oldest
online log sequence
157 Next
log sequence to archive 158 Current
log sequence
158 SQL> |
5.
Generate archive logs manually by using the ALTER SYSTEM ARCHIVE command.
SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
D:\oracle\oradata\orcl\archive Oldest
online log sequence
157 Next
log sequence to archive 158 Current
log sequence
158 SQL>
alter system archive log current; System
Altered SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
D:\oracle\oradata\orcl\archive Oldest
online log sequence
158 Next
log sequence to archive 159 Current
log sequence
159 SQL> |
You
have completed the practice.