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