|
"The nice thing
about being a celebrity is that if you bore people they think
it's their fault." - Henry Kissinger (1923) |
Read
first then play the video:
DBA007(VIDEO)-Configure
the database to the archive mode
Configure the database to
the archive mode
Introduction
As a DBA, you are
responsible to recover any failures to a point of failure and also to
perform your backup while online. Your organization is a 24x7 shop and
you are not able to shutdown the database. Therefore, the database has
to be in an archive mode. Your job’s responsibilities dictate that
you should at least be informed of the following basic fundamental
subjects:
Archiving the database
Maintaining the Online Redo
Log files
Maintaining the checkpoints
Using the NOARCHIVELOG mode
Using the ARCHIVELOG mode
Using the DBA_USERS view
Using the V$LOG dictionary
view
The STATUS column
Using the
LOG_CHECKPOINT_INTERVAL parameter
Using the
LOG_CHECKPOINT_TIMEOUT parameter
Using the
LOG_CHECKPOINTS_TO_ALERT parameter
Commands:
ARCHIVE LOG LIST
SHUTDOWN IMMEDIATE
STARTUP MOUNT PFILE=init.ora
ALTER DATABASE ARCHIVELOG
ALTER DATABASE OPEN
ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM CHECKPOINT
SHOW PARAMETER checkpoint
ALTER SYSTEM SET
log_checkpoint_timeout = 7200
Hands-on
In this exercise you will
learn how to Archive the database while the database is in the
NOARCHIVELOG MODE and also learn how to maintain the online Redo Log
files and checkpoints.
Connect to a database
Let's first, connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS
SYSDBA
Check archive mode
Check to see, if you are in the archive mode or not.
SQL> ARCHIVE LOG LIST
Change a database to archive mode
First you should change the
following parameters in the parameter file.
log_archive_dest =
/u01/app/oracle/admin/<database_name>/arch
log_archive_start = true
log_archive_format =
log%s.arc
Shutdown the database and
then start the instance and mount but do not open the database. To
change the database mode to the NOARCHIVELOG or the ARCHIVELOG modes,
you must shutdown and then startup the database with the MOUNT option.
When you change the database mode, make sure to take a complete
offline backup of the database. You will learn more about how to take
a complete OFFLINE backup during the backup and recovery Hands-On
exercises.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
PFILE=%ORACLE_HOME%.ora
/
Now, change the archiving status of the database.
SQL> ALTER DATABASE ARCHIVELOG
/
Notice that this command will modify the contents of the
control files. You can change it back by using the ALTER DATABASE
NOARCHIVELOG statement.
Open a database
Open the SCHOOL database.
SQL> ALTER DATABASE OPEN
/
Check a database is open
Just query the DBA_USERS
view to verify that the database is open.
SQL> SELECT count(1)
FROM dba_users
/
Check an archive log mode
Check the archive log list again.
SQL> ARCHIVE LOG LIST
Notice that the database mode was changed. It is very
important that after changing the database mode, to shutdown the
database and then, take a complete OFFLINE backup.
Check Log file status
Query the V$LOG dictionary view and take notes on the STATUS
column where the online redo log file is CURRENT.
SQL> SELECT * FROM v$log
/
Switch an Online Redo Log
To change the Online Redo Log file, use the ALTER SYSTEM SWITCH
command. Now, switch the Online Redo Log file to the next Online Redo
Log file.
SQL> ALTER SYSTEM SWITCH LOGFILE
/
Check log status
Query the V$LOG directory
view again and take notes on the STATUS column.
SQL> SELECT * FROM v$log
/
Notice that the CURRENT value, is on a different group
number. Remember that anytime the Online Redo Log file switches the
CHECKPOINT PROCESS it tells the DB Writer to write all of the dirty
blocks in the database.
View checkpoint parameters
Show all the checkpoint parameters.
SQL> SHOW PARAMETER
checkpoint
Notice that the LOG_CHECKPOINT_INTERVAL is zero. That
means, you have a checkpoint whenever the Online Redo Log file
switches. The default LOG_CHECKPOINT_TIMEOUT is 1800, and it means
that if the Redo Log file did not fill up within 30 minutes, the
checkpoint will tell the DB Writer to write all of the dirty blocks in
the database. If the LOG_CHECKPOINTS_TO_ALERT value is true, then any
occurrence of checkpoint will be written in the database alert file.
Checkpoint Manually
Now, let's try to force a checkpoint to happen manually.
SQL> ALTER SYSTEM CHECKPOINT
/
Checkpoint every … hours
Set the checkpoint timeout to a 2-hour time interval.
SQL> ALTER SYSTEM SET
log_checkpoint_timeout = 7200
|
"Education is a
progressive discovery of our own ignorance." - Will Durant |
Questions:
Q: How do you configure a
database to an archive mode?
Q: What are the benefits of
changing a database mode to the archive mode?
Q: How do you maintain an
Oracle Online redo log file?
Q: How do you monitor and
maintain the checkpoint process?
Q: Describe the database
NOARCHIVELOG mode.
Q: Describe the DBA_USERS
view.
Q: Describe the V$LOG
dictionary view.
Q: What does the STATUS
column indicate in the V$LOG view?
Q: Describe the following
parameters:
LOG_CHECKPOINT_INTERVAL
parameter
LOG_CHECKPOINT_TIMEOUT
parameter
LOG_CHECKPOINTS_TO_ALERT
parameter
Q: Describe the ARCHIVE LOG
LIST command.
Q: What does the following
statement do?
SQL> STARTUP MOUNT
PFILE=%ORACLE_HOME%.ora
/
Q: How do you switch an online redo log file?
Q: How do you perform a
checkpoint manually?
Q: How do you perform a
checkpoint automatically?
|