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 08

"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:

   DBA-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:\oradata\USERS.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:\your-datafile.DBF c:\your-new-location\your-datafile.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?