DBA Fundamentals

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:

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 YOURDBNAME database.
SQL> SELECT name FROM v$database
/
You should see SCHOOL database.

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.

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 END BACKUP
/