|
"Any man who is
under 30, and is not a liberal, has not heart; and any man who
is over 30, and is not a conservative, has no brains." -
Sir Winston Churchill (1874-1965) |
Read
first then play the video:
DBAx09(VIDEO)-User
Managed Tablespaces and Datafiles Backup
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 SCHOOL 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.
|
"In any contest
between power and patience, bet on patience." - W.B.
Prescott |
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?
|