everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 34

Next >>


 

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 YOURDBNAME 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.

 

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
/

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.