|
User-Managed Tablespaces and Datafiles
Backup
Introduction
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 value
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
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.
SQL> CONNECT
system/manager AS SYSDBA
Since we have two databases in your machine,
we need to verify that we are in the
YOURDBNAME database.
SQL> SELECT name
FROM v$database
/
You should be in the SCHOOL
database.
Tablespace
backup (ONLINE)
First, query
the DBA_DATA_FILES dictionary view to locate
all the data files associated with the USERS
tablespace.
SQL> SELECT
tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'USERS'
/
Write down the file_name value(s).
Now, perform integrity checks on the USERS'
physical datafile(s). Go to the Operating
System command mode and enter the dbverify
or dbv command.
MS-DOS> dbv
file=C:.DBF -
BLOCKSIZE=4096
Then, if 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.
SQL> ALTER
TABLESPACE users OFFLINE NORMAL
/
Note that if you use the
TEMPORARY or IMMEDIATE options for OFFLINE,
you will not be able to get the tablespace
ONLINE unless you perform a media recovery.
Now, execute the COPY command to copy all
off the associated USERS' data files to the
USERHOTBKUP destination.
SQL> HOST COPY
C:.DBF -
c:.bk
Set the USERS tablespace status, back to
ONLINE.
SQL> ALTER
TABLESPACE users ONLINE
/
Now, the tablespace backup has
been completed.
Go to MS explore and navigate to the
USERHOTBKUP directory to be sure the
USERS01.bk backup was completed
successfully.
Questions:
Q: Describe
the V$DATAFILE and DBA_DATA_FILES dictionary
views?
Q: Describe
the TOTAL PAGES FAILING output from dbv
utility.
Q: How do you
OFFLINE a tablespace?
Q: How many
different OFFLINE option do you have for a
tablespace?
Q: How do you
perform an integrity check on a datafile?
Q: What does
the dbv utility?
Q Can you
ONLINE a tablespace that was OFFLINE with
the TEMPORARY or IMMEDIATE options?
Q: 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. How do you
perform a tablespace backup? |
