Hands-On 07 (User-Managed COLD or OFFLINE backup)

You, as a DBA, are responsible to backup the database and restore the data in case of a loss of data due to media failure. Based on your organization’s business rules, the database can be shutdown every day for 5 hours. You also know the backup won’t take more than an hour. You want to use the COLD backup process. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a User-Managed COLD or OFFLINE backup

Writing a script to perform a complete, COLD, or OFFLINE backups

Using the V$DATABASE view

The ARCHIVE modes

            ARCHIVELOG mode

NOARCHIVELOG mode

Using the V$LOGFILE view

Using the V$DATAFILE view

Using the V$CONTROLFILE view

Setting a Control Command

SET ECHO

SET HEADING

SET FEEDBACK

SET PAGESIZE

SET ECHO ON

The password file and the Parameter file (PFILE)

Commands:

            SET ECHO

SET HEADING

SET FEEDBACK

SET PAGESIZE

ALTER DATABASE

-- Hands-On 07 (User-Managed COLD or OFFLINE backup)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
HOST ERASE c:\userbkup\*.*

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

--Start

CLEAR SCR
-- In this exercise you will learn how to do a COLD or 
-- OFFLINE backup.

-- You will also learn how to write a script and use it
-- to perform the complete, COLD, or offline backups.

-- Now, 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 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 call USERBKUP to 
-- perform this Hands-On activity.

pause


HOST MKDIR c:\userbkup

-- The USERBKUP folder was created.
-- Go to the MS Explore and check to see if the directory was
-- created and there is anything in it.

--\/-- USERBKUP

pause


CLEAR SCR
-- The easiest and safest backup is a COLD backup.
-- Unfortunately, we don't always have the freedom to do a
-- COLD or OFFLINE backup. 

-- If you are in a 24x7 shop and cannot shutdown the database
-- you must use HOT backup. We'll talk about HOT backups in
-- other backup hands-on exercises.

-- Now, let's do a complete, FULL COLD backup on a database.

-- Notice that a COLD backup can be done on a database that 
-- is in the ARCHIVELOG mode or the NOARCHIVELOG mode. 

pause
pause

CLEAR SCR
-- First, let us query the data dictionary views to identify the
-- location of the physical datafiles, control files, and redo log
-- files that constitute the full database backup.

-- It is good idea to also backup the parameter file and password
-- file. In this Hands-On we skip these two files.

-- Now, query a list of the physical datafiles in the database.

pause


SELECT name FROM v$datafile
/

pause


CLEAR SCR
-- Also, query a list of control files.

pause


SELECT name FROM v$controlfile
/

pause


CLEAR SCR
-- Query a list of the redo log files.

pause


SELECT member FROM v$logfile
/

pause

CLEAR SCR
-- Write a script to copy them to the newly created backup 
-- destination (USERBKUP).
-- Spool the script to a file call MY_COLD_BKUP.sql.

-- Also, don't forget to copy the parameter file pfile plus 
-- your password file.

pause
pause

CLEAR SCR
SET ECHO OFF
SET HEADING OFF 
SET FEEDBACK OFF 
SET PAGESIZE 1000 

SPOOL c:\userbkup\my_COLD_bkup.sql

SELECT 'HOST COPY ' || name || ' c:\userbkup\*;' FROM v$controlfile;
SELECT 'HOST COPY ' || name || ' c:\userbkup\*;' FROM v$datafile;
SELECT 'HOST COPY ' || member || ' c:\userbkup\*;' FROM v$logfile;

SPOOL OFF
SET HEADING ON 
SET FEEDBACK ON
SET PAGESIZE 55
SET ECHO ON

-- Now, use the MS explorer and use notepad to edit the new script.
-- If you don't edit the file you may get the UNKNOWN COMMAND 
-- error message.

-- These error messages are not harmful and can be ignored if you
-- do not want to edit the file. 

-- Since this backup is going to take some time, we are going
-- to delete some of the big datafiles from the list.

--\/-- Edit the my_COLD_bkup.sql file.

pause
pause

CLEAR SCR
-- Close the database.

pause

ALTER DATABASE close
/

pause

CLEAR SCR
-- Run the script my_COLD_bkup.sql
-- Please wait ...

pause

START c:\userbkup\my_COLD_bkup.sql;
-- Do not forget to copy the password file and the parameter file 
-- (PFILE or SPFILE).
-- Now, check your backup in the userbkup folder and startup the
-- database.

pause

CLEAR SCR
-- Go to the MS explore and navigate the USERBKUP directory to be
-- sure that the files were backed up 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