Chapter10-User-Managed Backups

1、查看数据文件

SQL> select name,status from v$datafile;

NAME                                               STATUS
-------------------------------------------------- -------
/u01/oradata/poli/system01.dbf                     SYSTEM
/u01/oradata/poli/sysaux01.dbf                     ONLINE
/u01/oradata/poli/undotbs01.dbf                    ONLINE
/u01/oradata/poli/users01.dbf                      ONLINE
/u01/oradata/poli/example01.dbf                    ONLINE

2、查看控制文件信息

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/oradata/poli/control01.ctl
/u01/flash_recovery_area/poli/control02.ctl

3、查看联机重做日志文件

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/oradata/poli/redo03.log
/u01/oradata/poli/redo02.log
/u01/oradata/poli/redo01.log

4、查看表空间与数据文件的对应关系

SQL> SELECT t.name tablespace,f.name datafile
  2  FROM v$tablespace t,v$datafile f
  3  WHERE t.ts# = f.ts#
  4  ORDER BY t.name;

TABLESPACE                     DATAFILE
------------------------------ ------------------------------------
EXAMPLE                        /u01/oradata/poli/example01.dbf
SYSAUX                         /u01/oradata/poli/sysaux01.dbf
SYSTEM                         /u01/oradata/poli/system01.dbf
UNDOTBS1                       /u01/oradata/poli/undotbs01.dbf
USERS                          /u01/oradata/poli/users01.dbf

5、将数据库修改为归档模式

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

6、冷备（数据库关闭，进行手工的拷贝命令操作）

执行冷备

[oracle@DG1 coldbackup]$ cp /u01/oracle/dbs/spfilepoli.ora .
[oracle@DG1 coldbackup]$ cp /u01/oracle/dbs/orapwpoli .
[oracle@DG1 coldbackup]$ ls
orapwpoli  spfilepoli.ora
[oracle@DG1 coldbackup]$ cp -R /u01/oradata/poli/ .
[oracle@DG1 coldbackup]$ ls
orapwpoli  poli  spfilepoli.ora
[oracle@DG1 coldbackup]$ ll
total 12
-rw-r----- 1 oracle oinstall 1536 Mar 20 14:53 orapwpoli
drwxr-x--- 2 oracle oinstall 4096 Mar 20 14:55 poli
-rw-r----- 1 oracle oinstall 3585 Mar 20 14:53 spfilepoli.ora
[oracle@DG1 coldbackup]$ ls -l
total 12
-rw-r----- 1 oracle oinstall 1536 Mar 20 14:53 orapwpoli
drwxr-x--- 2 oracle oinstall 4096 Mar 20 14:55 poli
-rw-r----- 1 oracle oinstall 3585 Mar 20 14:53 spfilepoli.ora
[oracle@DG1 coldbackup]$ cd ..
[oracle@DG1 tmp]$ du -k coldbackup/
1631132 coldbackup/poli
1631144 coldbackup/
[oracle@DG1 tmp]$ 

 打包压缩

[oracle@DG1 tmp]$ du -k coldbackup/
1631132 coldbackup/poli
1631144 coldbackup/
[oracle@DG1 tmp]$ ls
coldbackup  CVU_11.2.0.1.0_oracle  initpoli.ora  linux_11gR2_database  logs  Packages  pulse-8aviC6uZUcQJ  VMwareDnD  vmware-root  vmware-root-2378961006  vmware-tools-distrib
[oracle@DG1 tmp]$ ll
total 44
drwxr-xr-x  3 oracle oinstall 4096 Mar 20 14:54 coldbackup
drwxr-xr-x  3 oracle oinstall 4096 Mar 12 10:16 CVU_11.2.0.1.0_oracle
-rw-r--r--  1 oracle oinstall 2851 Mar 13 16:38 initpoli.ora
drwxr-xr-x  2 root   root     4096 Mar 12 09:14 linux_11gR2_database
drwxr-xr-x  2 oracle oinstall 4096 Mar 12 10:16 logs
drwxr-xr-x  3 root   root     4096 Mar  8 11:33 Packages
drwx------. 2 root   root     4096 Mar  5 22:11 pulse-8aviC6uZUcQJ
drwxrwxrwt. 2 root   root     4096 Mar  5 22:04 VMwareDnD
drwxr-xr-x. 2 root   root     4096 Mar 20 10:40 vmware-root
drwx------. 2 root   root     4096 Mar 20 10:40 vmware-root-2378961006
drwxr-xr-x. 4 root   root     4096 Aug  2  2012 vmware-tools-distrib
[oracle@DG1 tmp]$ tar -zcvf coldbackup.2013.03.20.tar.gz coldbackup/
coldbackup/
coldbackup/poli/
coldbackup/poli/users01.dbf
coldbackup/poli/redo02.log
coldbackup/poli/redo03.log
coldbackup/poli/system01.dbf
coldbackup/poli/example01.dbf
coldbackup/poli/redo01.log
coldbackup/poli/undotbs01.dbf
coldbackup/poli/temp01.dbf
coldbackup/poli/control01.ctl
coldbackup/poli/sysaux01.dbf
coldbackup/spfilepoli.ora
coldbackup/orapwpoli

 7、执行把当前的联机重做日志归档

alter system archive log current;

8、自动执行冷备脚本

set feedback off heading off verfify off trimspoll off
set pagesize 0 linesize 200
define dir='/tmp/wb'
define ws='/tmp/ws.sql'
spool &ws
select '!cp ' || name || '&dir' from v$datafile order by 1;
select '!cp ' || number || '&dir' from v$logfile order by 1;
select '!cp ' || name || '&dir' from v$controlfile order by 1;
select '!cp ' || name || '&dir' from v$tempfile order by 1;
spool off
shutdown immediate
@&ws
startup

 9、热备(Open Database BackUp;Hot Backup)

热备，不需要备份online redo log files，但是必须备份archive log files;

执行热备的前提，就是数据库必须处于ARCHIVELOG MODE;

10、Manual Control File Backups

11、Using DBVERIFY

DBVERIFY只能验证数据文件，不能验证控制文件.

控制文件的备份原则.只要是控制文件发生改变，就应该进行backup.

历史遗留问题,此命令在oracle  9i中可以，在oracle 11g r2环境中则不可以,有待于解决.

[oracle@DG1 hb]$ dbv userid='sys/oracle@DB200 as sysdba' segment_id=6.5.482

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Mar 20 23:57:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBV-00112: USERID incorrectly specified