iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals II

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 | Lesson 27 | Lesson 28 | Lesson 29 |

 

Lesson 07

"Basically, I no longer work for anything but the sensation I have while working." - Albert Giacometti (sculptor)

 

Read first then play the video:

   DBA-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\USERHOTBKUP:
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 C:\TOOLS*.dbf  C:\USERHOTBKUP\.


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:\xxxxx.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
/