|
"Basically, I no
longer work for anything but the sensation I have while
working." - Albert Giacometti (sculptor) |
Read
first then play the video:
DBAx08(VIDEO)-User
Managed HOT or ONLINE Backup
User-Managed HOT or ONLINE
backup
Introduction
You, as a DBA, are
responsible to backup the database and restore data to the point of
failure in case of a loss of data due to media problems. Your
organization is a 24x7 day shop and you are not able to shutdown the
database. You have to use the HOT or ONLINE backups. Your job
responsibilities dictate that you should be at least informed of the
following basic fundamental subjects:
Performing a User-Managed
Hot backup
Archiving the current
Online Redo Log group
Backing-up a CONTROLFILE to
a file or TRACE
Performing the HOT backup
Using the DBA_DATA_FILES
dictionary view
Setting a tablespace into
the backup mode
Commands:
ALTER TABLESPACE BEGIN
BACKUP
HOST COPY ALTER TABLESPACE
END BACKUP
ALTER SYSTEM
ALTER DATABASE
Hands-on
In this exercise you will
learn how to do HOT or ONLINE backup. We will also learn how to
archive the current Online Redo Log group and backup a CONTROLFILE to
a file or TRACE.
Connect to the SCHOOL database as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA
Since we have two databases in this machine, we always need to verify
that we are in the SCHOOL database.
SQL> SELECT name FROM v$database
/
You should see SCHOOL database.
HOT backup
Let's first create a
directory called USERHOTBKUP to perform this exercise.
SQL> HOST MKDIR c:
The USERHOTBKUP folder will be created. Go to MS Explore
and check to see if the directory was created.
To perform the HOT backup, the database does not have to be shutdown
or closed. Remember that you can only do an Online or HOT backup if
the database is in the archive log mode. We have already changed the
database mode from the NOARCHIVELOG mode to the ARCHIVELOG mode. So we
are able to do HOT or ONLINE backup without any trouble.
To backup the TOOLS tablespace; First, query the DBA_DATA_FILES
dictionary view to locate all of the data files associated with the
TOOLs tablespace.
SQL> SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name = 'TOOLS'
/
Write down the file_name value(s).
Now, set the TOOLS tablespace into the backup mode.
SQL> ALTER TABLESPACE tools BEGIN BACKUP
/
Execute the COPY command to copy all off the associated
TOOLS' data files into the USERHOTBKUP destination.
SQL> HOST COPY \par C:I.DBF \par C:
Now, take the TOOLS
tablespace out of the backup mode.
SQL> ALTER TABLESPACE tools END BACKUP
/
Repeat this process for
each datafile until you have a full HOT backup. Don't forget to
archive the current online redo log group and backup all of the
archived Online Redo Log files.
SQL> ALTER SYSTEM SWITCH LOGFILE
/
Backup the control file to trace and file.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO
TRACE
/
SQL> ALTER DATABASE BACKUP
CONTROLFILE TO 'c:.bkp'
/
Go to MS explore and the USERHOTBKUP directory to be sure the files
were backup successfully.
|
"Obstacles are
those frightful things you see when you take your eyes off your
goal." - Henry Ford (1863-1947) |
Questions:
Q: What is a hot or offline
backup?
Q: Describe the
DBA_DATA_FILES dictionary view.
Q: How do you perform a hot
backup on a tablespace?
Q: What are the differences
between a hot and cold backup?
Q: What do the following
SQL statements?
SQL> ALTER TABLESPACE
tools BEGIN BACKUP
/
SQL> HOST COPY \par C:I.DBF \par C:
SQL> ALTER TABLESPACE tools END BACKUP
/
|