Hands-On 07 (Configure the database to the archive mode)

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 07 (Configure the database to the archive mode)
-- Preparation
set echo on
connect system/manager@school as sysdba
shutdown immediate
host copy %ORACLE_HOME%\admin\school\pfile\initoriginal.ora %ORACLE_HOME%\admin\school\pfile\init.ora
connect system/manager@school as sysdba
STARTUP PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to Archive the database while
-- in the NOARCHIVELOG MODE and also learn how to maintain the online
-- Redo Log files and checkpoints.

-- Let's first, connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- check to see, if you are in the archive mode or not.

pause


ARCHIVE LOG LIST

pause

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

pause

SHUTDOWN IMMEDIATE

STARTUP MOUNT PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora

pause

CLEAR SCR
-- Now, change the archiving status of the database.

pause


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.

pause

CLEAR SCR
-- Open the SCHOOL database.

pause


ALTER DATABASE OPEN
/

pause

CLEAR SCR
-- Just query the DBA_USERS view to verify that the database is open.

pause

SELECT count(1)
FROM dba_users
/

pause

CLEAR SCR
-- Check the archive log list again.

pause


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.

pause


CLEAR SCR
-- Query the V$LOG dictionary view and take notes on the STATUS column
-- where the online redo log file is CURRENT.

pause


SELECT * FROM v$log
/

pause

CLEAR SCR
-- Make the Online redo log file switch.

pause


ALTER SYSTEM SWITCH LOGFILE
/

pause

CLEAR SCR
-- Query the V$LOG directory view again and take notes on the STATUS column.

pause


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.

pause

CLEAR SCR
-- Show all the checkpoint parameters.

pause


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

pause

CLEAR SCR
-- Now, let's try to force a checkpoint to happen manually.

pause


ALTER SYSTEM CHECKPOINT
/

pause

CLEAR SCR
-- Set the checkpoint timeout to a 2-hour time interval.

pause


ALTER SYSTEM SET log_checkpoint_timeout = 7200
/

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