Topics: User-Managed COLD or OFFLINE
backup
|
More Resources by
Google: |
|
|
|
|
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
|