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 # 33

Next >>


 

User-Managed COLD or OFFLINE backup


Introduction
You, as a DBA, are responsible to backup the database and restore the data in case of a loss of data due to media failure. Based on your organization�s business rules, the database can be shutdown every day for 5 hours. You also know the backup won�t take more than an hour. You want to use the COLD backup process. Your job responsibilities dictate that you should be at least informed of the following basic fundamental subjects:

Performing a User-Managed COLD or OFFLINE backup
Writing a script to perform a complete, COLD, or OFFLINE backups
Using the V$DATABASE view
The ARCHIVE modes
ARCHIVELOG mode
NOARCHIVELOG mode
Using the V$LOGFILE view
Using the V$DATAFILE view
Using the V$CONTROLFILE view
Setting a Control Command
SET ECHO
SET HEADING
SET FEEDBACK
SET PAGESIZE
SET ECHO ON
The password file and the Parameter file (PFILE)
Commands:
SET ECHO 
SET HEADING 
SET FEEDBACK 
SET PAGESIZE 
ALTER DATABASE 


Hands-on
In this exercise you will learn how to do a COLD or OFFLINE backup. You will also learn how to write a script and use it to perform the complete, COLD, or offline backups.

Now, connect to the SCHOOL database as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA

Check a database name
Since we have two databases in this machine, we need to verify that we are in the YOURDBNAME database.
SQL> SELECT name FROM v$database
/
You should have SCHOOL. If not then you are in a wrong directory.
 


COLD or OFFLINE Backup
Let's first create a directory call USERBKUP to perform this Hands-On activity.
SQL> HOST MKDIR c:\userbkup
The USERBKUP folder will be created.

Go to the Window MS Explore and check to see if the directory was created and there is anything in it.

The easiest and safest backup is a COLD backup. Unfortunately, we don't always have the freedom to do a COLD or OFFLINE backup. If you are in a 24x7 shop and cannot shutdown the database you must use HOT backup. We'll talk about HOT backups in other backup hands-on exercises.

Now, let's do a complete, FULL COLD backup on a database. Notice that a COLD backup can be done on a database that is in the ARCHIVELOG mode or the NOARCHIVELOG mode. 

First, let us query the data dictionary views to identify the location of the physical datafiles, control files, and redo log files that constitute the full database backup. It is good idea to also backup the parameter file and password file. 

Now, query a list of the physical datafiles in the database.
SQL> SELECT name FROM v$datafile
/

Also, query a list of control files.
SQL> SELECT name FROM v$controlfile
/

Query a list of the redo log files.
SQL> SELECT member FROM v$logfile
/
 


Create a coldbackup script
Write a script to copy them to the newly created backup destination (USERBKUP). Spool the script to a file call MY_COLD_BKUP.sql. Also, don't forget to copy the parameter file pfile plus your password file.
SQL> SET ECHO OFF
SQL> SET HEADING OFF 
SQL> SET FEEDBACK OFF 
SQL> SET PAGESIZE 1000 
SQL> SPOOL c:\userbkup\my_COLD_bkup.sql
SQL> SELECT 'HOST COPY ' || name || ' c:\userbkup\*;' FROM v$controlfile;
SQL> SELECT 'HOST COPY ' || name || ' c:\userbkup\*;' FROM v$datafile;
SQL> SELECT 'HOST COPY ' || member || ' c:\userbkup\*;' FROM v$logfile;
SQL> SELECT �HOST COPY \
�%ORACLE_BASE\admin\school\pfile\init.ora \
c:\userbkup\*;� FROM dual;
SQL>
SQL> SPOOL OFF
SQL> SET HEADING ON 
SQL> SET FEEDBACK ON
SQL> SET PAGESIZE 55
SQL> SET ECHO ON

Modify the script
Now, use the MS explorer and use notepad to edit the new script. If you don't edit the file you may get the UNKNOWN COMMAND error message. These error messages are not harmful and can be ignored if you do not want to edit the file. 

Close the database
Close the database.
SQL> ALTER DATABASE close
/
 


Run the script
Run the script my_COLD_bkup.sql
SQL> START c:\userbkup\my_COLD_bkup.sql;
Do not forget to copy the password file and the parameter file (PFILE or SPFILE). Now, check your backup in the userbkup folder and startup the database.

Go to the MS explore and navigate the USERBKUP directory to be sure that the files were backed up successfully.
 

Questions:
Q: How many backup do we have?

Q: What is a cold or offline database backup?

Q: Describe a usage of the following views:
V$DATABASE view
V$LOGFILE view
V$DATAFILE view
V$CONTROLFILE view

Q: To perform a COLD backup, does the database need to be in an archivelog mode?

Q: You, as a DBA, are responsible to backup the database and restore the data in case of a loss of data due to media failure. Based on your organization�s business rules, the database can be shutdown every day for 5 hours. You also know the backup won�t take more than an hour. You want to use the COLD backup process. Write a script to perform a complete cold backup.

 

 

     Reviews and Templates for FrontPage
     

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