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.