Hands-On 08 (User-Managed HOT or ONLINE backup)

You, as a DBA, are responsible to backup the database and restore data to the point of failure in case of a loss of data due to media problems. Your organization is a 24x7 day shop and you are not able to shutdown the database. You have to use the HOT or ONLINE backups. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a User-Managed Hot backup

Archiving the current Online Redo Log group

Backing-up a CONTROLFILE to a file or TRACE

Performing the HOT backup

Using the DBA_DATA_FILES dictionary view

Setting a tablespace into the backup mode

Commands:

ALTER TABLESPACE BEGIN BACKUP

HOST COPY ALTER TABLESPACE END BACKUP

ALTER SYSTEM

ALTER DATABASE

-- Hands-On 08 (User-Managed HOT or ONLINE backup)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
HOST ERASE c:\userhotbkup\*.*

SET linesize 1000 pagesize 55
COL name FORMAT a60
col description format a30
col tablespace_name format a15
col file_name format a45
pause

--Start

CLEAR SCR
-- In this exercise you will learn how to do HOT or 
-- ONLINE backup.

-- We will also learn how to archive the current Online
-- Redo Log group and backup a CONTROLFILE to a file or TRACE.

-- Connect to the SCHOOL database as the SYSTEM/MANAGER user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Since we have two databases in this machine, we always need to 
-- verify that we are in the SCHOOL database.

pause

SELECT name FROM v$database
/

-- It looks like we are in the right database.

pause

CLEAR SCR
-- Let's first create a directory called USERHOTBKUP to 
-- perform this exercise.

-- To perform the HOT backup, the database does not have to be
-- shutdown or closed.

-- Remember that you can only do an Online or HOT backup
-- if the database is in the archive log mode.

-- We have already changed the database mode from the NOARCHIVELOG
-- mode to the ARCHIVELOG mode. So we are able to do HOT or ONLINE
-- backup without any trouble.

pause


HOST MKDIR c:\userhotbkup

-- The USERHOTBKUP folder was created.
-- Go to MS Explore and check to see if the directory was created.

--\/-- Explore USERHOTBKUP

pause

CLEAR SCR

-- Backup the TOOLS tablespace.

-- First, query the DBA_DATA_FILES dictionary view to locate
-- all of the data files associated with the TOOLs tablespace.
-- 

pause

SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'TOOLS'
/

-- Write down the file_name value(s).

pause

CLEAR SCR
-- Now, set the TOOLS tablespace into the backup mode.

pause

ALTER TABLESPACE tools BEGIN BACKUP
/

pause

CLEAR SCR
-- Execute the COPY command to copy all off the associated TOOLS'
-- data files into the USERHOTBKUP destination.

pause


HOST COPY C:\ORA9I\ORADATA\SCHOOL\TOOLS01.DBF c:\userhotbkup\*

pause

CLEAR SCR
-- Now, take the TOOLS tablespace out of the backup mode.

pause


ALTER TABLESPACE tools END BACKUP
/

-- Repeat this process for each datafile until you have a full 
-- HOT backup.

pause

CLEAR SCR
-- Don't forget to archive the current online redo log group
-- and backup all of the archived Online Redo Log files.

pause


ALTER SYSTEM SWITCH LOGFILE
/

pause

CLEAR SCR
-- Backup the control file to trace and file.

pause

ALTER DATABASE BACKUP CONTROLFILE TO TRACE
/

ALTER DATABASE BACKUP CONTROLFILE TO 'c:\userhotbkup\control.bkp'
/
pause

CLEAR SCR
-- Go to MS explore and the USERHOTBKUP directory to be sure the 
-- files were backup successfully.

--\/-- MS Explore.

pause
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