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 06

 

“Gravity. It keeps you rooted to the ground. In space, there's not any gravity. You just kind of leave your feet and go floating around. Is that what being in love is like?” Josh Brand and John Falsey, Northern Exposure, The Pilot, 1990

 

Read first then play the video:

   DBA-VIDEO -User Managed COLD or OFFLINE Backup

   

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 SCHOOL 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 dom 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.

 

"He who asks a question is a fool for five minutes; he who does not ask a question remains a fool forever." -Chinese Proverb


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.