【菜鸟入职篇】单实例下数据库备份操作(—)

测试环境：

OS版本：Red Hat Enterprise Linux Server release 5.4 (Tikanga)

DB版本：Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

全库冷备份：

需要备份的文件包括：数据文件、控制文件、日志文件、参数文件

1.查看要备份文件的位置

SQL> select name from v$datafile union all select name from v$controlfile

  2  union all select member from v$logfile;

NAME

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

/u01/oracle/oradata/orcl/system01.dbf

/u01/oracle/oradata/orcl/undotbs01.dbf

/u01/oracle/oradata/orcl/sysaux01.dbf

/u01/oracle/oradata/orcl/users01.dbf

/u01/oracle/oradata/orcl/control01.ctl

/u01/oracle/oradata/orcl/control02.ctl

/u01/oracle/oradata/orcl/control03.ctl

/u01/oracle/oradata/orcl/redo03.log

/u01/oracle/oradata/orcl/redo02.log

/u01/oracle/oradata/orcl/redo01.log

10 rows selected.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/oracle/db_1/dbs/spfileorcl.ora

2.创建备份文件存放的目录

[oracle@primary oracle]$ mkdir orcl_bk

3.关闭数据库，将需要备份的文件拷贝到指定目录

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@primary orcl]$ pwd

/u01/oracle/oradata/orcl

[oracle@primary orcl]$ ll

total 944856

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:39 control01.ctl

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:39 control02.ctl

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:39 control03.ctl

-rw-r----- 1 oracle oinstall  52429312 Oct 22 23:39 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Oct 22 22:39 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Oct 22 22:50 redo03.log

-rw-r----- 1 oracle oinstall 251666432 Oct 22 23:39 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Oct 22 23:39 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 23 22:00 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 Oct 22 23:39 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Oct 22 23:39 users01.dbf

[oracle@primary orcl]$ cp * /u01/oracle/orcl_bk/

[oracle@primary orcl_bk]$ cp /u01/oracle/db_1/dbs/spfileorcl.ora /u01/oracle/orcl_bk/

[oracle@primary orcl]$ cd /u01/oracle/orcl_bk/ 

[oracle@primary orcl_bk]$ ll

total 944856

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:40 control01.ctl

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:40 control02.ctl

-rw-r----- 1 oracle oinstall   7061504 Oct 22 23:40 control03.ctl

-rw-r----- 1 oracle oinstall  52429312 Oct 22 23:40 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Oct 22 23:40 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Oct 22 23:40 redo03.log

-rw-r----- 1 oracle oinstall      2560 Oct 22 23:42 spfileorcl.ora

-rw-r----- 1 oracle oinstall 251666432 Oct 22 23:41 sysaux01.dbf

-rw-r----- 1 oracle oinstall 503324672 Oct 22 23:41 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Oct 22 23:41 temp01.dbf

-rw-r----- 1 oracle oinstall  26222592 Oct 22 23:41 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Oct 22 23:41 users01.dbf

热备份：

1.设置数据库处于归档模式下

[oracle@primary oracle]$ mkdir arch_dir     --创建归档路径

[oracle@primary oracle]$ mkdir orcl_bk2     --创建备份路径

[oracle@primary oracle]$ pwd

/u01/oracle

SQL> alter system set log_archive_dest_1=‘location=/u01/oracle/arch_dir‘ scope=both;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size              71305460 bytes

Database Buffers           92274688 bytes

Redo Buffers                2973696 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/oracle/arch_dir

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL> alter system switch logfile;

System altered.

2.备份所需文件

SQL> alter database begin backup;    --告知数据库即将进行热备

Database altered.

1)备份数据文件，联机日志文件

[oracle@primary orcl]$ cp *.log /u01/oracle/orcl_bk2/

[oracle@primary orcl]$ cp *.dbf /u01/oracle/orcl_bk2/

2)备份控制文件(有两种方式)

生成备份的trace文件：

SQL> alter database backup controlfile to trace as ‘/u01/oracle/orcl_bk2/control.trace‘ reuse;      

Database altered.

生成的备份的二进制文件：

SQL> alter database backup controlfile to ‘/u01/oracle/orcl_bk2/control.bak‘ reuse;

Database altered.

3)备份参数文件

SQL> create pfile=‘/u01/oracle/orcl_bk2/initorcl.ora‘ from spfile;

File created.

SQL> alter database end backup;    --告知数据库热备结束

Database altered.

批注：如果是备份表空间，先将表空间置于OFFLINE状态

RMAN备份：

备份脚本：

[oracle@primary rman_sh]$ cat bkfull.sh 

#!/bin/bash

# name:bkfull.sh

# date:2014/10/23

# desc: backup incremental level 0 database with rman

#this script will execute in the end of each Sunday

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=/u01/oracle/db_1

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$PATH

export LANG=en_US

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

rman target/ << EOF_RMAN

run{

     configure retention policy to recovery window of 7 days;

     configure controlfile autobackup on;

     allocate channel c1 type disk;

     backup incremental level 0 tag ‘db0‘ format

     ‘/u01/oracle/rman_bk/db_file/db0_%d_%T_%s‘ database skip inaccessible

     include current controlfile;

     backup archivelog all format ‘/u01/oracle/rman_bk/log/log_%u‘ delete all input;

     crosscheck backup;

     delete noprompt expired backup;

     delete noprompt obsolete;

     release channel c1;

}

#end

本文出自 “Dayonger成长笔记” 博客，请务必保留此出处http://dayonger.blog.51cto.com/9385723/1566964