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