In this practice you will identify all of the
files in the database. This will be used in later topics to ensure that all
database files are backed up and none are missed.
Instructions
1. Log
in to the database as a user with DBA privileges.
|
SQL> conn system/manager@orcl |
2. Query
v$datafile for the names of all the datafiles in the database.
|
SQL> select name from v$datafile; |
3. Query
v$controlfile for the names of all the controlfiles in the database.
|
SQL> select name from v$controlfile; |
4. Query
v$logfile for the names of all the logfiles in the database. (Optional)
|
SQL> select member from v$logfile; |
This completes this
practice.
ã Oracle Corporation, 2002
Instructions
1. Connect
as a privileged user and perform a clean shutdown of the database (anything
other than abort).
|
SQL> conn / @orcl as sysdba SQL> shutdown immediate |
2. Using
operating system commands, copy the data files to a backup location.
|
D:\oracle\oradata\…\> copy DRSYS01.DBF
D:\backup D:\oracle\oradata\…\> copy CWMLITE01.DBF D:\backup D:\oracle\oradata\…\>
copy TEMP01.DBF D:\backup D:\oracle\oradata\…\>
copy USERS01.DBF D:\backup D:\oracle\oradata\…\>
copy UNDOTBS01.DBF D:\backup D:\oracle\oradata\…\>
copy TOOLS01.DBF D:\backup D:\oracle\oradata\…\>
copy SYSTEM01.DBF D:\backup D:\oracle\oradata\…\>
copy INDX01.DBF D:\backup D:\oracle\oradata\…\>
copy EXAMPLE01.dbf D:\backup |
3. Using
operating system commands, copy the control files to a backup location.
|
D:\oracle\oradata\…\> copy control01.ctl
D:\backup D:\oracle\oradata\…\> copy control02.ctl
D:\backup D:\oracle\oradata\…\> copy control03.ctl
D:\backup |
4. Using
operating system commands, copy the redo log files to a backup location. This
is optional, because the redo log files can be recreated when restoring the
database.
|
D:\oracle\oradata\…\> copy REDO01.ctl D:\backup D:\oracle\oradata\…\> copy REDO02.ctl D:\backup D:\oracle\oradata\…\> copy REDO03.ctl D:\backup D:\oracle\oradata\…\> copy REDO01B.ctl
D:\backup D:\oracle\oradata\…\> copy REDO02B.ctl
D:\backup D:\oracle\oradata\…\> copy REDO03B.ctl
D:\backup |
5. After
copying all of the data, control and redo log files restart the database.
|
SQL> conn / @orcl as sysdba SQL> startup ORACLE instance started. Total System Global Area 109866920 bytes Fixed Size 282536 bytes Variable Size 83886080 bytes Database Buffers 25165824 bytes Redo Buffers 532480 bytes Database mounted. Database opened. SQL> |
This completes this
practice.
ã Oracle Corporation, 2002
In this exercise you will perform backup of the
database datafiles while the database is open and available for changes by
users.
Assumptions
·
This practice assumes that your database is in
Archivelog mode and automatic archiving is enabled.
Instructions
1. Connect
as a privileged user and confirm that the database is in archivelog mode and
automatic archival is enabled.
|
SQL> conn / @orcl as sysdba Connected. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination d:\oracle\…\arc2 Oldest online log sequence 38 Next log sequence to archive 39 Current log sequence 39 SQL>
|
2. Connect
as a regular DBA and query the control file for the name and location of all
the datafiles in the database and the tablespace to which they belong.
|
SQL> conn system/manager@orcl Connected. SQL> select t.name "TS Name",
d.file#, d.name 2
"File", bytes/1024/1024 "MB" 3
from
v$tablespace t join v$datafile d 4
using
(ts#); |
3. Put
the tablespace to be backed up in backup mode.
|
SQL> alter tablespace EXAMPLE begin
backup; Tablespace altered. SQL>
|
4. At
the operating system prompt, issue the copy command to copy the file to a
backup location. (Your OS copy command may be different.)
|
D:\Oracle\Oradata\ORCL\ORCL>dir Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\Oracle\Oradata\ORCL\ORCL 02/12/2002
04:32p <DIR> . 02/12/2002
04:32p <DIR> .. 03/04/2002
11:20p 20,975,616
DRSYS01.DBF 03/04/2002
11:20p 20,975,616
CWMLITE01.DBF 02/12/2002
04:39p 41,947,136
TEMP01.DBF 03/04/2002
11:20p 26,218,496
USERS01.DBF 03/04/2002
11:20p 209,719,296
UNDOTBS01.DBF 03/04/2002
11:20p 10,489,856
TOOLS01.DBF 03/04/2002
11:20p 340,791,296
SYSTEM01.DBF 03/04/2002
11:20p 26,218,496
INDX01.DBF 03/05/2002
01:58p 159,911,936
EXAMPLE01.dbf 9 File(s)
857,247,744 bytes 2 Dir(s)
7,443,103,744 bytes free D:\Oracle\…\ORCL> copy example01.dbf
d:\backup
1 file(s) copied. |
5. While
the files for the tablespace are being backed up, perform DML in that
tablespace.
|
SQL> conn hr/hr@orcl Connected. SQL> create table emp
2 as select * from employees; Table created. SQL> update emp
2 set salary = salary * 1.1
3 where department_id = 50; 45 rows updated. SQL> commit; Commit complete. |
6. Connect
as a user with DBA privileges and end the backup of the tablespace.
|
SQL> conn system/manager@orcl Connected. SQL> alter tablespace example end backup Tablespace altered. SQL> |
This completes this
practice.
ã Oracle Corporation, 2002
This practice you will backup the controlfile to
a binary file and to a text file while the database is open.
Instructions
1. First
connect to the database with a DBA privileged account.
|
SQL> conn system/manager@orcl Connected. SQL>
|
2. Perform
the two ALTER DATABASE commands to back up the controlfile to a binary file and
to a text file.
|
SQL> alter database backup controlfile
to 2
'D:\backup\controlORCL.bak'; Database altered. SQL> alter database backup controlfile
to trace; Database altered. SQL> |
3. Copy
the text backup from the user trace directory to the backup location.
|
SQL> show parameter user_dump_dest NAME TYPE
VALUE --------------- -------- -------------------------- user_dump_dest string
D:\oracle\admin\ORCL\udump SQL> D:\>cd oracle\admin\orcl\udump D:\Oracle\admin\ORCL\udump>dir Volume in drive D has no label. Volume Serial Number is 313A-16EC Directory of D:\Oracle\admin\ORCL\udump 02/12/2002
04:32p <DIR> . 02/12/2002
04:32p <DIR> .. 03/05/2002
03:52p 1,735
ORA02092.TRC 1 File(s)
1,735 bytes 2 Dir(s)
7,280,910,336 bytes free D:\Oracle\admin\ORCL\udump>copy
ora02092.trc d:\backup\controlORCL.sql
1 file(s) copied. D:\Oracle\admin\ORCL\udump> |
This completes this
practice.
ã Oracle Corporation, 2002
