Hands-On 09 (User-Managed Tablespaces and Datafiles Backup)

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

Performing a tablespace and datafiles backup

Performing integrity checks

Using the V$DATAFILE view

Using the DBA_DATA_FILES dictionary view

Understanding the TOTAL PAGES FAILING values

Setting a tablespace while in the OFFLINE mode

The OFFLINE TEMPORARY option

The OFFLINE IMMEDIATE option

Setting a tablespace status

Commands:

ALTER TABLESPACE ONLINE

ALTER TABLESPACE OFFLINE NORMAL

dbv file= BLOCKSIZE=4096

-- Hands-On 09 (User-Managed Tablespaces and Datafiles Backup)
-- Preparation
SET ECHO ON
connect system/manager as sysdba
HOST ERASE c:\userhotbkup\*.bk

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 perform the USERS
-- tablespace and datafiles backup while the USERS tablespace
-- is in an OFFLINE mode.

-- We will also learn how to Perform integrity checks 
-- on a physical datafile(s).

-- 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
-- First, query the DBA_DATA_FILES dictionary view to 
-- locate all the data files associated with the USERS 
-- tablespace.

pause


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

-- Write down the file_name value(s).

pause

CLEAR SCR
-- Now, perform integrity checks on the USERS' physical datafile(s).

-- Go to the Operating System command mode and enter the dbverify
-- or dbv command.

--\/-- DOS> dbv file=C:\ORACLE\ORA90\SCHOOL\USERS01.DBF BLOCKSIZE=4096

pause
pause

CLEAR SCR
-- Then, if all the TOTAL PAGES FAILING values are 0, you may
-- perform the ONLINE tablespace backup.

-- To perform the online tablespace or datafile backup,
-- put the TOOLS tablespace into the OFFLINE mode.

pause


ALTER TABLESPACE users OFFLINE NORMAL
/

-- Notice that if you use either the TEMPORARY or IMMEDIATE 
-- options for OFFLINE, you will not be able to get the tablespace
-- ONLINE unless you perform a media recovery.

pause

CLEAR SCR
-- Now, execute the COPY command to copy all off the associated USERS' 
-- data files to the USERHOTBKUP destination.

pause


HOST COPY C:\ORACLE\ORA90\SCHOOL\USERS01.DBF c:\userhotbkup\USERS01.bk

pause

CLEAR SCR
-- Set the USERS tablespace status, back to ONLINE.

pause

ALTER TABLESPACE users ONLINE
/
-- Now, the tablespace backup has been completed.

pause

CLEAR SCR
-- Go to MS explore and navigate to the USERHOTBKUP directory to be sure the 
-- USERS01.bk backup was completed 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