“My mother drew a
distinction between achievement and success. She said that
'achievement is the knowledge that you have studied and worked
hard and done the best that is in you. Success is being praised
by others, and that's nice, too, but not as important or
satisfying. Always aim for achievement and forget about
success.' “ Helen Hayes (1900 - 1993) |
Read
first then play the video:
DBA-VIDEO -Configuring
the Database Archive mode (Advanced)
Configuring the Database
Archive mode
Introduction
You, as a DBA, are
responsible to recover any failures to a point of failure and also to
perform your backup while in an online status. Your shop is 24x7 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 be at least informed of the following basic fundamental
subjects:
Configuring the Database
Archive Mode
Setting the database to the
ARCHIVELOG mode
Querying archive log
information
Making a directory.
Enabling the automatic
archival process
Setting an archive
destination.
Using the Server Parameter
File (SPFILE)
Commands:
ARCHIVE LOG LIST
ALTER DATABASE CLOSE
ALTER SYSTEM SET
log_archive_start=true SCOPE=spfile
ALTER SYSTEM SET
log_archive_dest='c:'
ALTER SYSTEM SET
log_archive_format='arc%S.%T' SCOPE=spfile
The %S entry
The %T entry
Shutdown and Startup
Database
SHUTDOWN IMMEDIATE
STARTUP
Hands-on
In this exercise you will learn how to change the database mode
from the NOARCHIVELOG mode to the ARCHIVELOG mode.
Now, connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Check a database is in
archive mode
Let's first check if the database is in the ARCHIVELOG mode.
SQL> ARCHIVE LOG LIST
This result indicates that the database is not currently in
the ARCHIVELOG mode.
Change a database to an
archive mode
First, close the database if the database is open. If the database
is not open then startup the database with the MOUNT option. Since the
database is open, we should close the database, and then change the
database mode from the NOARCHIVELOG mode to the ARCHIVELOG mode.
SQL> --- Close the database.
SQL> ALTER DATABASE CLOSE
/
SQL> ------ Set the database to the ARCHIVELOG mode.
SQL> ALTER DATABASE ARCHIVELOG
/
Query the archive log information.
SQL> ARCHIVE LOG LIST
Notice that the Database Log Mode was changed to the
Archive Mode.
Now, make a directory called archivelogs and then enable the automatic
archival to be performed in the new archive destination.
SQL> ----- First make a directory.
SQL> HOST MKDIR c:
SQL> -- And then enable automatic archival process and set the
archive destination.
SQL> ALTER SYSTEM ARCHIVE LOG START TO
'c:'
/
Query the archive log information.
SQL> ARCHIVE LOG LIST
Notice that the Automatic Archival was enabled and the
archivelog destination was changed to a new location.
Make sure that the automatic archival process, the archive log
destinations, and the archive format are all set in the Server
Parameter File (SPFILE).
Note that the Server
Parameter File is the same as the database parameter file. The only
difference is: it is in a binary format code and can not be read or
edited. It is used to change the Oracle system parameters dynamically
by using the ALTER SYSTEM SET command.
.
ALTER SYSTEM SET log_archive_start=true
SCOPE=spfile
/
ALTER SYSTEM SET log_archive_dest='c:' SCOPE=spfile
/
ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile
/
-- Note that the %S entry includes the online redo log
sequence number. The %T entry includes the database instance or thread
number. Remember that the SCOPE parameter will guarantee that the new
configurations will be written in the Server Parameter File.
Activate archive mode
Now, shutdown the database, then connect to the system as the
SYSTEM/MANAGER user, and startup the database using the Server
Parameter File (SPFILE). Notice that we get the (ORA-01109: database
not open) error message. We got this message because we closed the
database but not the instance.
SQL> ------------>>> Shutdown
SQL> SHUTDOWN IMMEDIATE
SQL> ------------>>> Connect
SQL> CONNECT system/manager AS SYSDBA
SQL> ------------>>> Startup
SQL> STARTUP
Query the archive log information again.
SQL> ARCHIVE LOG LIST
The database is now in the ARCHIVE mode.
“There are more
things in heaven and earth, Horatio, Than are dreamt of in your
philosophy.” William Shakespeare (1564 - 1616),
"Hamlet", Act 1 scene 5 |
Questions:
Q: How do you configure
your database to an archivelog mode?
Q: How do you query your
database’s archive log information?
Q: How do you set an
archive log destination?
Q: What is the Server
Parameter file (SPFILE)?
Q: What do the following
statements do?
ALTER SYSTEM SET
log_archive_start=true SCOPE=spfile
/
ALTER SYSTEM SET log_archive_dest='c:' SCOPE=spfile
/
ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile
/
Q: You, as a DBA, are
responsible to recover any failures to a point of failure and also to
perform your backup while in online status. Your shop is 24x7 and you
are not able to shutdown the database. Therefore, the database has to
be in an archive mode. You should change you database mode from
noarchivelog to archivelog mode. What are the steps that you should
perform to change your database mode?
|