Hands-On 04 (Configuring the Database Archive mode)

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:\archivelogs'

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 04 (Configuring the Database Archive mode)
-- Preparation
SET ECHO ON
ALTER DATABASE CLOSE;
ALTER DATABASE NOARCHIVELOG;
ALTER SYSTEM SET log_archive_start=false SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest='';
ALTER SYSTEM SET log_archive_format='' SCOPE=spfile;

shutdown immediate
connect system/manager as sysdba
startup

SET linesize 1000 pagesize 55
COL name FORMAT a30
col description format a30
pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Let's first check if the database is in the ARCHIVELOG mode.

pause


ARCHIVE LOG LIST

-- This result, indicates that the database is not currently
-- in the ARCHIVELOG mode.

pause

CLEAR SCR
-- 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.

pause

-------------->>> Close the database.
ALTER DATABASE CLOSE
/

-------------->>> Set the database to the ARCHIVELOG mode.
ALTER DATABASE ARCHIVELOG
/

pause

CLEAR SCR
-- Query the archive log information.

pause

ARCHIVE LOG LIST

-- Notice that the Database Log Mode was changed to
-- the Archive Mode.

pause

CLEAR SCR
-- Now, make a directory called archivelogs and then enable the 
-- automatic archival to be performed in the new archive
-- destination.

pause

---------->>> First make a directory.
HOST MKDIR c:\archivelogs

---------->>> And then enable automatic archival process 
---------->>> and set the archive destination.
ALTER SYSTEM ARCHIVE LOG START TO 'c:\archivelogs'
/

pause


CLEAR SCR
-- Query the archive log information.

pause

ARCHIVE LOG LIST

-- Notice that the Automatic Archival was enabled and the
-- archivelog destination was changed to new the new location.

pause


CLEAR SCR
-- Make sure that the automatic archival process, the archive log
-- destinations, and the archive format are all set in the 
-- Server Parameter File (SPFILE).

pause
ALTER SYSTEM SET log_archive_start=true SCOPE=spfile
/
ALTER SYSTEM SET log_archive_dest='c:\archivelogs'
/
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.
pause

CLEAR SCR
-- 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.

pause
------------>>> Shutdown
SHUTDOWN IMMEDIATE
------------>>> Connect
CONNECT system/manager AS SYSDBA
------------>>> Startup
STARTUP
pause


CLEAR SCR
-- Query the archive log information again.

pause


ARCHIVE LOG LIST

-- The database is now in the ARCHIVE mode.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause