In
this you will perform a closed database backup. More specifically you will:
1.
Find out the names of all controlfiles, redo log files and datafiles of
your database.
2.
Create an SQL-script that, when executed, will create an operating
system batch script containing operating system COPY commands to copy the
controlfiles, redo log files and datafiles.
3.
Stop the instance.
4.
Take a closed database backup by execute the operating system script
created earlier.
1.
We assume you have a proper Oracle8i database. In our solutions
we use Oracle8i Release 2.
2.
The directory and filenames referenced in the commands in this practice
reference the NT operating system. However, simply changing the directory and
filename references, as well as the operating system commands to match the
operating system you are using (ie. UNIX, LINUX, etc.) will allow all the
steps to work properly on your operating system platform.
3.
During some of these practices you may be asked to cause severe damage
to your database. Please, do not perform these practices on a production
system.
1.
If your database os not already open, then start SQL*Plus, connect as SYSDBA
and start your instance and open your database.
|
SQL*Plus:
Release 8.1.6.0.0 - Production on Thu Mar 15 13:48:03 2001 (c)
Copyright 1999 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size 70924
bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
2.
Using the appropriate dynamic performance view find out the names and
locations of the controlfiles of your database.
|
SQL>
desc v$controlfile Name
Null? Type -------------------------------
-------- ---- STATUS
VARCHAR2(7) NAME
VARCHAR2(513) SQL>
select name from v$controlfile; NAME -------------------------------------------------------------------------------- E:\816NF\DATA\CONTROL1816.CTL E:\816NF\DATA\CONTROL2816.CTL SQL> |
3.
Using the appropriate data dictionary view find out the names and
locations of the datafiles of your database.
|
SQL>
desc dba_data_files Name
Null? Type -------------------------------
-------- ---- FILE_NAME
VARCHAR2(513) FILE_ID
NUMBER TABLESPACE_NAME
VARCHAR2(30) BYTES
NUMBER BLOCKS
NUMBER STATUS
VARCHAR2(9) RELATIVE_FNO
NUMBER AUTOEXTENSIBLE
VARCHAR2(3) MAXBYTES
NUMBER MAXBLOCKS
NUMBER INCREMENT_BY
NUMBER USER_BYTES
NUMBER USER_BLOCKS
NUMBER SQL>
select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- E:\816NF\DATA\SYSTEM01.DBF E:\816NF\DATA\RCVCAT01.DBF E:\816NF\DATA\TEMP01.DBF E:\816NF\DATA\RBS01.DBF E:\816NF\DATA\DATA01_01.DBF E:\816NF\DATA\DATA02_01.DBF E:\816NF\DATA\DATA03_01.DBF E:\816NF\DATA\DATA04_01.DBF E:\816NF\DATA\INDX01.DBF D:\GARBAGE.DBF 10
rows selected. SQL> |
4.
Using the appropriate dynamic performance view find out the names and
locations of your online redo log files.
|
SQL>
desc v$logfile Name
Null? Type -------------------------------
-------- ---- GROUP#
NUMBER STATUS
VARCHAR2(7) MEMBER
VARCHAR2(513) SQL>
select member from v$logfile; MEMBER -------------------------------------------------------------------------------- E:\816NF\DATA\LOG1A.RDO E:\816NF\DATA\LOG2A.RDO E:\816NF\DATA\LOG3A.RDO SQL> |
5.
Calculate the total space consumption of your database. Note: the size
of your datafiles can be seen from DBA_DATA_FILES,
the size of your redo log files can be obtain from V$LOG,
while the size of your controlfiles can be checked at the operating system
level, but it is usually negligable compared to the size of the datafiles and
redo log files.
|
SQL>
select sum(bytes) from dba_data_files; SUM(BYTES) ---------- 317280256 SQL>
desc v$log Name
Null? Type -------------------------------
-------- ---- GROUP#
NUMBER THREAD#
NUMBER SEQUENCE#
NUMBER BYTES
NUMBER MEMBERS
NUMBER ARCHIVED
VARCHAR2(3) STATUS
VARCHAR2(16) FIRST_CHANGE#
NUMBER FIRST_TIME
DATE SQL>
select sum(bytes)
2 from
v$log, v$logfile
3 where
v$log.group# = v$logfile.group#; SUM(BYTES) ----------
24772608 At the operating system level: E:\>dir
e:\816nf\data\*.ctl Volume
in drive E has no label. Volume
Serial Number is 780D-1590 Directory
of e:\816nf\data 03/15/01
01:47p
2,797,568 CONTROL1816.CTL 03/15/01
01:47p
2,797,568 CONTROL2816.CTL
2 File(s)
5,595,136 bytes
4,487,299,072 bytes free From the above results we can see, that we need 317 megabytes for backing up the datafiles, 24 megabytes for backing up the redo log files and less than 6 megabytes for backing up the controlfiles in our case. That is about 350 megabytes. Of course, your results may be quite different than these. |
6.
Create a new directory on your machine that is going to be used as a
target location for your closed database backup. Choose the name closed_backup
for this directory. Take care that you have enough storage space for the
backup.
|
E:\>mkdir
e:\closed_backup |
7.
Instead of taking a closed database backup manually, you are going to
use a semi-automatic approach: Using a text editor create an SQL*Plus script
file with the name closed_copy.sql.
Let this file have the following or very similar content:
SET
ECHO OFF
SET HEADING OFF
SET FEEDBACK OFF
SPOOL E:\closed_copy.cmd
SELECT
'copy ' || name || ' e:\closed_backup' FROM v$controlfile;
SELECT
'copy ' || file_name || ' e:\closed_backup' FROM dba_data_files;
SELECT
'copy ' || member || ' e:\closed_backup' FROM v$logfile;
SPOOL OFF
Caution: this content may have to be modified slightly. You may want to
change the location and name of the spool file in the 4th line, and
also the directory name in the SELECT commands in lines 5, 6 and 7.
After creating the above SQL*Plus script execute it in order to create the
spool file.
|
The content of the closed_copy.sql script may be similar to this one: SET ECHO
OFF SQL>
@closed_copy copy
E:\816NF\DATA\CONTROL1816.CTL e:\closed_backup copy
E:\816NF\DATA\CONTROL2816.CTL e:\closed_backup copy
E:\816NF\DATA\SYSTEM01.DBF e:\closed_backup copy
E:\816NF\DATA\RCVCAT01.DBF e:\closed_backup copy
E:\816NF\DATA\TEMP01.DBF e:\closed_backup copy
E:\816NF\DATA\RBS01.DBF e:\closed_backup copy
E:\816NF\DATA\DATA01_01.DBF e:\closed_backup copy
E:\816NF\DATA\DATA02_01.DBF e:\closed_backup copy
E:\816NF\DATA\DATA03_01.DBF e:\closed_backup copy
E:\816NF\DATA\DATA04_01.DBF e:\closed_backup copy
E:\816NF\DATA\INDX01.DBF e:\closed_backup copy
D:\GARBAGE.DBF e:\closed_backup copy
E:\816NF\DATA\LOG1A.RDO e:\closed_backup copy
E:\816NF\DATA\LOG2A.RDO e:\closed_backup copy
E:\816NF\DATA\LOG3A.RDO e:\closed_backup SQL> At the same time, a closed_copy.cmd file will be created, containing the same copy commands. |
8.
Shutdown your instance using the normal
or immediate
option.
|
SQL>
SHUTDOWN NORMAL Database
closed. Database
dismounted. ORACLE
instance shut down. SQL> |
9.
Execute the closed_copy.cmd
script from the operating system prompt, in order to create a closed database
backup of the database.
|
E:\>closed_copy E:\>copy
E:\816NF\DATA\CONTROL1816.CTL e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\CONTROL2816.CTL e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\SYSTEM01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\RCVCAT01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\TEMP01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\RBS01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\DATA01_01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\DATA02_01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\DATA03_01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\DATA04_01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\INDX01.DBF e:\closed_backup
1 file(s) copied. E:\>copy
D:\GARBAGE.DBF e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\LOG1A.RDO e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\LOG2A.RDO e:\closed_backup
1 file(s) copied. E:\>copy
E:\816NF\DATA\LOG3A.RDO e:\closed_backup
1 file(s) copied. E:\> |
10.
Since the closed database backup have been made, you may now restart
your instance.
|
SQL*Plus:
Release 8.1.6.0.0 - Production on Sun Mar 18 21:55:11 2001 (c)
Copyright 1999 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
In
this practice you will perform an open database backup. More specifically you
will:
1.
Take the database into archivelog mode and ensure that automatic
archiving takes place.
2.
Find out the names of all controlfiles and datafiles of your database.
3.
Perform an open database backup of each tablespace, one after the
other.
4.
Perform an open backup of your controlfile.
1.
We assume you have a proper Oracle8i database. In our solutions
we use Oracle8i Release 2.
2.
The directory and filenames referenced in the commands in this practice
reference the NT operating system. However, simply changing the directory and
filename references, as well as the operating system commands to match the
operating system you are using (ie. UNIX, LINUX, etc.) will allow all the
steps to work properly on your operating system platform.
3.
During some of these practices you may be asked to cause severe damage
to your database. Please, do not perform these practices on a production
system.
1.
If your database is not already open, then start SQL*Plus, connect as SYSDBA
and start your instance and open your database.
|
SQL*Plus:
Release 8.1.6.0.0 - Production on Sun Mar 18 21:55:11 2001 (c)
Copyright 1999 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
2.
Check if your database is in archivelog mode and whether automatic
archiving has been started.
|
SQL>
archive log list Database
log mode
No Archive Mode Automatic
archival
Disabled Archive
destination
E:\816NF\ARCHIVE\ARCHIVE1 Oldest
online log sequence
155 Current
log sequence
157 SQL> As
we can see, the database currently is in noarchivelog mode, and
automatic archiving is disabled. The fact that it is noarchivelog mode
means that currently the open database backup is not alllowed. |
3.
Place the database in archivelog mode, if it has not already been done,
and make sure to enable automatic archiving.
|
The
content of the init.ora parameter file has to be modified, to contain
the following line: log_archive_start
= true In
order for this value to take effect, the instance has to be restarted.
Next, the database should be in mounted mode, in order to change it
from noarchivelog mode to archivelog mode. The shortest way to achieve
all this is the following: SQL>
shutdown Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>
startup mount ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. SQL>
alter database archivelog; Database
altered. SQL>
alter database open; Database
altered. SQL>
archive log list Database
log mode
Archive Mode Automatic
archival
Enabled Archive
destination
E:\816NF\ARCHIVE\ARCHIVE1 Oldest
online log sequence
155 Next
log sequence to archive 157 Current
log sequence
157 SQL> |
4.
Using the appropriate data dictionary view find out the names and
locations of the datafiles of your database, as well as the tablespaces where
they belong.
|
SQL>
desc dba_data_files Name
Null?
Type -------------------------------
-------- ---- FILE_NAME
VARCHAR2(513) FILE_ID
NUMBER TABLESPACE_NAME
VARCHAR2(30) BYTES
NUMBER BLOCKS
NUMBER STATUS
VARCHAR2(9) RELATIVE_FNO
NUMBER AUTOEXTENSIBLE
VARCHAR2(3) MAXBYTES
NUMBER MAXBLOCKS
NUMBER INCREMENT_BY
NUMBER USER_BYTES
NUMBER USER_BLOCKS
NUMBER SQL>
column file_name format a40 SQL>
column tablespace_name format a20 SQL>
select file_name, tablespace_name from dba_data_files; FILE_NAME
TABLESPACE_NAME ----------------------------------------
-------------------- E:\816NF\DATA\SYSTEM01.DBF
SYSTEM E:\816NF\DATA\RCVCAT01.DBF
RCVCAT E:\816NF\DATA\TEMP01.DBF
TEMP E:\816NF\DATA\RBS01.DBF
RBS E:\816NF\DATA\DATA01_01.DBF
DATA01 E:\816NF\DATA\DATA02_01.DBF
DATA02 E:\816NF\DATA\DATA03_01.DBF
DATA03 E:\816NF\DATA\DATA04_01.DBF
DATA04 E:\816NF\DATA\INDX01.DBF
INDX01 D:\GARBAGE.DBF
TO_BE_CORRUPTED 10
rows selected. SQL> |
5.
Calculate the total space consumption of all your datafiles and one
controlfile. Note: the size of your datafiles can be seen from DBA_DATA_FILES,
while the size of your controlfile can be checked at the operating system
level, but it is usually negligable compared to the size of the datafiles.
|
SQL>
select sum(bytes) from dba_data_files; SUM(BYTES) ---------- 317280256 SQL>
select name from v$controlfile; NAME -------------------------------------------------------------------------------- E:\816NF\DATA\CONTROL1816.CTL E:\816NF\DATA\CONTROL2816.CTL SQL> At the operating system level: E:\>dir
e:\816nf\data\*.ctl Volume
in drive E has no label. Volume
Serial Number is 780D-1590 Directory
of e:\816nf\data 03/15/01
01:47p
2,797,568 CONTROL1816.CTL 03/15/01
01:47p
2,797,568 CONTROL2816.CTL
2 File(s)
5,595,136 bytes
4,487,299,072 bytes free From the above results we can see, that we need 317 megabytes for backing up the datafiles, and less than 3 megabytes for backing up the controlfile in our case. That is about 320 megabytes. Of course, your results may be quite different than these. |
6.
Create a new directory on your machine that is going to be used as a
target location for your open database backup. Choose the name open_backup
for this directory. Take care that you have enough storage space for the
backup.
|
E:\>mkdir
e:\open_backup |
7.
Using a text editor create an SQL*Plus script file called open_copy.sql
that, for each tablespace:
a) takes the tablespace into a backup mode,
b) performs an operating system copy command to copy it’s datafiles into the
e:\open_backup
directory,
c) takes the tablespace back to normal mode.
After creating the above SQL*Plus script execute it in order to perform an
open database backup of your datafiles.
|
The required SQL*Plus script should look similar to the following one: ALTER
TABLESPACE SYSTEM BEGIN BACKUP; host
copy E:\816NF\DATA\SYSTEM01.DBF e:\open_backup ALTER
TABLESPACE SYSTEM END BACKUP; ALTER
TABLESPACE RCVCAT BEGIN BACKUP; host
copy E:\816NF\DATA\RCVCAT01.DBF e:\open_backup ALTER
TABLESPACE RCVCAT END BACKUP; ALTER
TABLESPACE TEMP BEGIN BACKUP; host
copy E:\816NF\DATA\TEMP01.DBF e:\open_backup ALTER
TABLESPACE TEMP END BACKUP; ALTER
TABLESPACE RBS BEGIN BACKUP; host
copy E:\816NF\DATA\RBS01.DBF e:\open_backup ALTER
TABLESPACE RBS END BACKUP; ALTER
TABLESPACE DATA01 BEGIN BACKUP; host
copy E:\816NF\DATA\DATA01_01.DBF e:\open_backup ALTER
TABLESPACE DATA01 END BACKUP; ALTER
TABLESPACE DATA02 BEGIN BACKUP; host
copy E:\816NF\DATA\DATA02_01.DBF e:\open_backup ALTER
TABLESPACE DATA02 END BACKUP; ALTER
TABLESPACE DATA03 BEGIN BACKUP; host
copy E:\816NF\DATA\DATA03_01.DBF e:\open_backup ALTER
TABLESPACE DATA03 END BACKUP; ALTER
TABLESPACE DATA04 BEGIN BACKUP; host
copy E:\816NF\DATA\DATA04_01.DBF e:\open_backup ALTER
TABLESPACE DATA04 END BACKUP; ALTER
TABLESPACE INDX01 BEGIN BACKUP; host
copy E:\816NF\DATA\INDX01.DBF e:\open_backup ALTER
TABLESPACE INDX01 END BACKUP; ALTER
TABLESPACE TO_BE_CORRUPTED BEGIN BACKUP; host
copy D:\GARBAGE.DBF e:\open_backup ALTER
TABLESPACE TO_BE_CORRUPTED END BACKUP; After having this script you execute it from SQL*Plus: SQL>
@e:\open_copy SQL> |
8.
Perform an open database backup of your controlfile. As a target use
the same directory as in the previous step.
|
SQL>
alter database backup controlfile to 'e:\open_backup\control1816.ctl'; Database
altered. SQL> |
9.
Finally, archive the current online redo log file. This way you will
become protected even against the loss of all redo log files.
|
SQL>
ALTER SYSTEM ARCHIVE LOG CURRENT; System
altered SQL> |
In
this practice you will experiment with datafile synchronization during
instance recovery. More specifically you will:
1.
Issue a SHUTDOWN
ABORT during
some heavy work.
2.
Restart the instance and mount the database.
3.
Check the content on V$DATAFILE_HEADER
view before and after opening the database.
1.
We assume you have a proper Oracle8i database. In our solutions
we use Oracle8i Release 2.
2.
The directory and filenames referenced in the commands in this practice
reference the NT operating system. However, simply changing the directory and
filename references, as well as the operating system commands to match the
operating system you are using (ie. UNIX, LINUX, etc.) will allow all the
steps to work properly on your operating system platform.
3.
During some of these practices you may be asked to cause severe damage
to your database. Please, do not perform these practices on a production
system.
1.
If your database is not already open, then start SQL*Plus,
connect as SYSDBA
and start your instance and open your database.
|
SQL*Plus:
Release 8.1.6.0.0 - Production on Mon Mar 19 08:55:11 2001 (c)
Copyright 1999 Oracle Corporation.
All rights reserved. Connected
to an idle instance. SQL>
startup ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. Database
opened. SQL> |
2.
Start another SQL*Plus
session as SYSDBA,
and perform some heavy work in the SYSTEM
tablespace. For example you may want to create a table, insert a row into it,
and double the number of rows in that table several times, until the next
duplication of rows takes at least 10 seconds. While this INSERT
command is still running, go back to your original session and issue a SHUTDOWN
ABORT command
over there to simulate an instance failure.
|
SQL>
create table probe ( a number ); Table
created. SQL>
insert into probe values (1); 1
row created. SQL>
insert into probe select * from probe; 1
row created. SQL>
/ 2
rows created. SQL>
/ 4
rows created. SQL>
/ 8
rows created. SQL>
/ 16
rows created. SQL>
/ 32
rows created. SQL>
/ 64
rows created. SQL>
/ 128
rows created. SQL>
/ 256
rows created. SQL>
/ 512
rows created. SQL>
/ 1024
rows created. SQL>
/ 2048
rows created. SQL>
/ 4096
rows created. SQL>
/ 8192
rows created. SQL>
/ 16384
rows created. SQL>
/ 32768
rows created. SQL>
/ 65536
rows created. SQL>
/ |
3.
In the SQL*Plus
session where the SHUTDOWN
ABORT command
was issued restart the instance with the MOUNT
option.
This option will disallow instance recovery to take place, so we can focus on
the synchronization numbers before and after rollforward recovery.
|
SQL>
startup mount ORACLE
instance started. Total
System Global Area 40449292
bytes Fixed
Size
70924 bytes Variable
Size
32108544 bytes Database
Buffers
8192000 bytes Redo
Buffers
77824 bytes Database
mounted. SQL> |
