oracle全库热备份,Oracle学习系列—数据库备份—热备份

SQL*Plus: Release 9.2.0.1.0

- Production on Wed Mar 14 00:31:13 2007

Copyright (c) 1982, 2002,

Oracle Corporation.All rights

reserved.

SQL> connect as sysdba;

Enter user-name: sys

Enter password:

Connected to an idle

instance.

SQL> startup;

ORACLE instance started.

Total System Global

Area152115804 bytes

Fixed Size453212 bytes

Variable Size117440512 bytes

Database Buffers33554432 bytes

Redo Buffers667648 bytes

Database mounted.

Database opened.

--查看归档状态，热备份仅在归档状态下进行

SQL> archive log list;

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationC:/oracle/ora92/database/archive

Oldest online log

sequence27

Next log sequence to

archive29

Current log sequence29

--查看需要备份的表空间，TEMP和UNDO表空间不需要备份

SQL> select Name from

v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

TEMP

MYTABLESPACE

--查看需要备份的与相关表空间关联的数据文件

SQL> select

substr(a.name,1,15) TSName,substr(b.name,1,45) DFName

2from

v$tablespace a,v$datafile b

3where a.ts#=b.ts#

4order by substr(a.name,1,15),substr(b.name,1,45);

TSNAMEDFNAME

--------------------------------------------------------------

MYTABLESPACEC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE01.DBF

MYTABLESPACEC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE02.DBF

MYTABLESPACEC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE03.DBF

SYSTEMC:/ORACLE/ORADATA/WBQ/SYSTEM01.DBF

UNDOTBS1C:/ORACLE/ORADATA/WBQ/UNDOTBS01.DBF

--进入热备份状态，首先把用户表空间置为热备份状态

SQL> alter tablespace

mytablespace begin backup;

Tablespace altered.

--热备份时，查询当前数据文件备份的状态

SQL> select

file#,status,change#,time from v$backup;

FILE# STATUSCHANGE# TIME

----------

------------------ ----------

1 NOT ACTIVE0

2 NOT ACTIVE0

3 ACTIVE127198 26-MAR-07

4 ACTIVE127198 26-MAR-07

5 ACTIVE127198 26-MAR-07

--查询FUZZY表示，如果为YES表示当前数据文件正处在热备份期间

SQL> select

file#,status,fuzzy,name from v$datafile_header;

FILE# STATUSFUZNAME

-------------------------------------------------------------

1 ONLINEC:/ORACLE/ORADATA/WBQ/SYSTEM01.DBF

2 ONLINEC:/ORACLE/ORADATA/WBQ/UNDOTBS01.DBF

3 ONLINEYESC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE01.DBF

4 ONLINEYESC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE02.DBF

5 ONLINEYESC:/ORACLE/ORADATA/WBQ/MYDATABLESPACE03.DBF

--在DOS状态进行用户表空间的物理文件拷贝

C:/>cd C:/oracle/oradata/wbq

C:/oracle/oradata/wbq>md backup

C:/oracle/oradata/wbq>copy SYSTEM01.DBF backup

1 file(s) copied.

C:/oracle/oradata/wbq>copy

MYDATABLESPACE01.DBF backup

1 file(s) copied.

C:/oracle/oradata/wbq>copy

MYDATABLESPACE02.DBF backup

1 file(s) copied.

C:/oracle/oradata/wbq>copy

MYDATABLESPACE03.DBF backup

1 file(s) copied.

--拷贝结束后，在SQL窗口中结束热备份的工作

SQL> alter tablespace

mytablespace end backup;

Tablespace altered.

--把系统表空间置为热备分状态

SQL> alter tablespace

system begin backup;

Tablespace altered.

--在DOS状态进行系统表空间的物理文件拷贝

C:/>cd C:/oracle/oradata/wbq

C:/oracle/oradata/wbq>copy

SYSTEM01.DBF backup

1 file(s) copied.

SQL> alter tablespace

system end backup;

Tablespace altered.

SQL> select

a.file#,a.checkpoint_change#,b.file_name

2from v$datafile a,dba_data_files b

3where a.file#=b.file_id;

FILE# checkpoint_change#FILE_NAME

---------------------------------------------------------------

1127684C:/ORACLE/ORADATA/WBQ/SYSTEM01.DBF

2121493C:/ORACLE/ORADATA/WBQ/UNDOTBS01.DBF

3127198C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE01.DBF

4127198C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE02.DBF

5127198C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE03.DBF

SQL> alter system

checkpoint;

System altered.

--强制系统同步

SQL> select

a.file#,a.checkpoint_change# ,b.file_name

2from v$datafile a,dba_data_files b

3where a.file#=b.file_id;

FILE#checkpoint_change#FILE_NAME

-------------------------------------------------------------------

1128084C:/ORACLE/ORADATA/WBQ/SYSTEM01.DBF

2128084C:/ORACLE/ORADATA/WBQ/UNDOTBS01.DBF

3128084C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE01.DBF

4128084C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE02.DBF

5128084C:/ORACLE/ORADATA/WBQ/MYDATABLESPACE03.DBF