【若泽大数据】Oracle 备份恢复生产实战

对整个数据库进行完全的热备

开始数据库热备

SQL>alter database begin backup;

使用操作系统命令进行数据文件的备份。

SQL> host cp /public/oradata/cuug/* /public/backup

结束数据库热备

SQL> alter database end backup;

对整个数据库进行完全的冷备

以NORMAL,IMMEDIATE,TRANSACTIONAL 方式关闭数据库，保证数据库的一致性。

使用操作系统命令复制所有的数据文件，控制文件，日志文件。

对数据库进行表空间的备份

开始数据库热备

SQL>alter tablespace users beigin backup;

使用操作系统命令进行数据文件的备份。

SQL> host cp /public/oradata/cuug/cwxusers /public/backup

结束数据库热备

SQL>alter tablespace users end backup;

对一个普通数据文件进行完全恢复 ( 联机 / 脱机恢复 )

（例如，丢失/public/oradata/cuug/users01 ）

准备工作：

SQL> conn scott/tiger

SQL> select * from tab;

TESTBACKUP3 TABLE

SQL> create table test1 (a number);

SQL> insert into test1 values(1);

SQL> alter system switch logfile;

SQL> commit;

SQL> alter system switch logfile;

SQL> insert into test1 values(2);

SQL> commit;

SQL> alter system switch logfile;

SQL> conn internal

SQL> archive log list

数据库日志模式 存档模式

自动存档 启用

存档终点 /public/arch

最早的概要信息日志序列 3

下一个存档日志序列 5

当前日志序列 5

shutdown abort 关闭例程，

SQL> shutdown abort

ORACLE 例程已经关闭。

模拟数据文件丢失，把/public/oradata/cuug/users01 删除

Mount 数据库

SQL> startup mount  

数据库装载完毕。

 

使损坏的数据文件脱机

SQL> alter database datafile '/public/oradata/cuug/users01' offline;

打开数据库

SQL> alter database open;

拷贝刚才热备的数据文件（USERS01.DBF ）

恢复损坏的数据文件

SQL> recover datafile '/public/oradata/cuug/users01';

ORA-00279: ?? 424116 (? 10/20/2002 20:42:04 ??) ???? 1 ????

ORA-00289: ??: /public/arch/ACKUPT001S00001.ARC

ORA-00280: ?? 424116 ???? 1 ???? # 1 ???

 

指定日志: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: ?? 424125 (? 10/20/2002 20:44:14 ??) ???? 1 ????

ORA-00289: ??: /public/arch/BACKUPT001S00002.ARC

ORA-00280: ?? 424125 ???? 1 ???? # 2 ???

ORA-00278: ??????????? '/public/arch/BACKUPT001S00001.ARC'

……………………..

已应用的日志。

完成介质恢复。

 

使恢复完成的数据文件联机

SQL> alter database datafile '/public/oradata/cuug/users01' online;

验证恢复的结果：完全恢复

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

SQL> select * from test1;

 

联机说明：

1.  shutdown abort 关闭例程，模拟数据文件丢失  

2.  Mount 数据库  

3.  使损坏的数据文件脱机  

4.  打开数据库  

5.  拷贝刚才热备的数据文件（USERS01.DBF ）  

6.  恢复损坏的数据文件  

使恢复完成的数据文件联机

 

脱机说明：

1.  shutdown abort 关闭例程，模拟数据文件丢失  

2.  Mount 数据库  

3.  拷贝刚才热备的数据文件（USERS01.DBF ）  

4.  恢复损坏的数据文件  

5.  打开数据库  

对全部数据文件进行完全恢复 ( 不包括 control 和 redo)

（把热备的数据文件拷贝回来，不包括 control 和 redo ）

复制全部热备的数据文件过来（完全恢复成功！）

mount 数据库

SQL> startup mount

完全恢复数据库

SQL> recover database;

ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1

ORA-00289: suggestion : /public/arch/BACKUPT001S00001.ARC

ORA-00280: change 424112 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1

ORA-00289: suggestion : /public/arch/BACKUPT001S00002.ARC

ORA-00280: change 424125 for thread 1 is in sequence #2

ORA-00278: log file '/public/arch/BACKUPT001S00001.ARC' no longer needed

for this recovery

……………………………………………..

Log applied.

Media recovery complete.

打开数据库

SQL> alter database open;

验证恢复结果：完全恢复

SQL> conn scott/tiger

SQL> select * from test1;

完全恢复成功！

说明：

1.   复制全部热备的数据文件过来

2.  mount 数据库   

3.   完全恢复数据库   

4.   打开数据库

 

对全部控制文件和数据文件进行完全恢复 ( 不包括 redo)

准备工作：删除原来的全部控制文件和数据文件

拷贝所有的控制文件和数据文件（不包括 redo ）

这时，试图完全恢复数据库是不成功的

需要重建控制文件：

SQL> alter database backup controlfile to trace;

 

Database altered.

 

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  117440512 bytes

Fixed Size                   777972 bytes

Variable Size              82845964 bytes

Database Buffers           33554432 bytes

Redo Buffers                 262144 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "CUUG" NORESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 30

  3      MAXLOGMEMBERS 5

  4      MAXDATAFILES 80

  5      MAXINSTANCES 1

  6      MAXLOGHISTORY 454

  7  LOGFILE

  8    GROUP 1 '/public/oradata/cuug/redo1a.log'  SIZE 10M,

  GROUP 2 '/public/oradata/cuug/redo2a.log'  SIZE 10M

  9   10  -- STANDBY LOGFILE

DATAFILE

  '/public/oradata/cuug/system01.dbf',

 11   12   13    '/public/oradata/cuug/undotbs01.ora',

  '/public/oradata/cuug/sysaux01.ora',

 14   15    '/public/oradata/cuug/cwxusers'

 16  CHARACTER SET ZHS16GBK

; 17 

 

Control file created.

 

SQL> recover database;

ORA-00279: change 463513 generated at 07/10/2005 15:51:05 needed for thread 1

ORA-00289: suggestion : /public/arch/1_1_563298529.dbf

ORA-00280: change 463513 for thread 1 is in sequence #1

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 482769 generated at 07/10/2005 15:54:57 needed for thread 1

ORA-00289: suggestion : /public/arch/1_2_563298529.dbf

ORA-00280: change 482769 for thread 1 is in sequence #2

ORA-00278: log file '/public/arch/1_1_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 502258 generated at 07/10/2005 15:55:16 needed for thread 1

ORA-00289: suggestion : /public/arch/1_3_563298529.dbf

ORA-00280: change 502258 for thread 1 is in sequence #3

ORA-00278: log file '/public/arch/1_2_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 521761 generated at 07/10/2005 15:55:35 needed for thread 1

ORA-00289: suggestion : /public/arch/1_4_563298529.dbf

ORA-00280: change 521761 for thread 1 is in sequence #4

ORA-00278: log file '/public/arch/1_3_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 541289 generated at 07/10/2005 15:55:51 needed for thread 1

ORA-00289: suggestion : /public/arch/1_5_563298529.dbf

ORA-00280: change 541289 for thread 1 is in sequence #5

ORA-00278: log file '/public/arch/1_4_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 560807 generated at 07/10/2005 15:56:07 needed for thread 1

ORA-00289: suggestion : /public/arch/1_6_563298529.dbf

ORA-00280: change 560807 for thread 1 is in sequence #6

ORA-00278: log file '/public/arch/1_5_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 580329 generated at 07/10/2005 15:56:25 needed for thread 1

ORA-00289: suggestion : /public/arch/1_7_563298529.dbf

ORA-00280: change 580329 for thread 1 is in sequence #7

ORA-00278: log file '/public/arch/1_6_563298529.dbf' no longer needed for this

recovery

 

ORA-00279: change 599829 generated at 07/10/2005 15:56:41 needed for thread 1

ORA-00289: suggestion : /public/arch/1_8_563298529.dbf

ORA-00280: change 599829 for thread 1 is in sequence #8

ORA-00278: log file '/public/arch/1_7_563298529.dbf' no longer needed for this

recovery

 

Log applied.

Media recovery complete.

SQL> alter database open;

打开数据库

SQL> alter database open ;

说明：

1.   拷贝所有的控制文件和数据文件（不包括 redo ）   

2.  mount 数据库

3. 重建控制文件

3.   完全恢复数据库   

4.   打开数据库   

丢失全部文件，进行不完全恢复 ( 除了 archive log 和 init.ora)

即，丢失了全部：数据文件、控制文件和 redo log file

准备工作： 删除原来的数据文件和控制文件、 REDO 文件；

mount 数据库

SQL>ALTER DATABASE MOUNT;

SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

说明：

1.   把热备的数据文件和控制文件拷贝过来   

2.  mount 数据库   

3.   用 using backup controlfile 进行不完全恢复   

5.   用 Open Resetlog 打开数据库   

对没有备份的数据文件进行恢复

准备工作：删除某个数据文件

SQL> create table tt (b number) tablespace userdata;

create table tt (b number) tablespace userdata

*

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace USERDATA

 

SQL>  create table tt (b number) tablespace userdata;

 create table tt (b number) tablespace userdata

*

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace USERDATA

 

SQL> insert into test2 values (5);

insert into test2 values (5)

            *

ERROR at line 1:

ORA-00376: file 5 cannot be read at this time

ORA-01110: data file 5: '/public/oradata/cuug/userdata01'

 

SQL> alter database create datafile '/public/oradata/cuug/userdata01';

 

Database altered.

 

SQL> alter database datafile '/public/oradata/cuug/userdata01' online;

alter database datafile '/public/oradata/cuug/userdata01' online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/public/oradata/cuug/userdata01'

 

 

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 1 - file is in use or recovery

ORA-01110: data file 1: '/public/oradata/cuug/system01.dbf'

 

SQL> recover datafile '/public/oradata/cuug/userdata01';

Media recovery complete.

SQL> alter database datafile '/public/oradata/cuug/userdata01' online;

 

Database altered.

 

说明：

1.   如果数据文件建立在文件系统上，则要立刻重建数据文件，否则如果操作系统重新建立文件可能就会覆盖原来的文件内容

2.   使用 create datafile 命令立即建立数据文件

3.   对该文件进行恢复   

5.   使该文件联机

对当前重做日志文件的恢复（ 1 ）

准备工作：使用SHUTDOWN ABORT 关闭数据库，并删除所有的日志文件

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  117440512 bytes

Fixed Size                   777972 bytes

Variable Size              82845964 bytes

Database Buffers           33554432 bytes

Redo Buffers                 262144 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/public/oradata/cuug/redo2a.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

使用 clear unarchived 命令重建当前的日志文件：

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance cuug (thread 1)

ORA-00312: online log 2 thread 1: '/public/oradata/cuug/redo2a.log'

恢复数据库：

SQL> recover database until cancel;

ORA-00279: change 818639 generated at 07/12/2005 13:45:48 needed for thread 1

ORA-00289: suggestion : /public/arch/arch_1_1_563458206.arc

ORA-00280: change 818639 for thread 1 is in sequence #1

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/public/arch/arch_1_1_563458206.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log '/public/arch/arch_1_1_563458206.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/public/oradata/cuug/system01.dbf'

注：由于数据库在不一致的状态下被关闭，所以需要 RECOVER ，但是需要的日志文件丢失，所以恢复失败，此时，数据库是没有办法正常打开的。

从以前的备份中把所有的数据文件转储到原处，然后使用不完全恢复。

SQL>RECOVER DATABASE UNTIL CANCEL ;

SQL>ALTER DATABASE OPEN RESETLOGS;

打开数据库后对数据库进行完全备份。以前的备份和归档日志不能再使用。

 

说明：

1 ）从最近备份中转储数据文件。

2 ）使用 until cancel 命令进行不完全恢复。

3 ）使用 open resetlogs 打开数据库。

4 ）重新备份数据库。

 

对当前重做日志文件的恢复（ 2 ）

准备工作：正常关闭数据库，删除所有的日志文件。

SQL> startup

ORACLE instance started.

 

Total System Global Area  117440512 bytes

Fixed Size                   777972 bytes

Variable Size              82845964 bytes

Database Buffers           33554432 bytes

Redo Buffers                 262144 bytes

Database mounted.

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/public/oradata/cuug/redo1a.log'

在对当前的日志组进行 clear unarchived 时出错：

SQL> alter database clear unarchived logfile group 2;

alter database clear unarchived logfile group 2

*

ERROR at line 1:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/public/oradata/cuug/redo2a.log'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

 

备份数据库控制文件到跟踪文件中：

CREATE CONTROLFILE REUSE DATABASE "CUUG" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 30

    MAXLOGMEMBERS 5

    MAXDATAFILES 80

    MAXINSTANCES 1

    MAXLOGHISTORY 454

LOGFILE

  GROUP 1 '/public/oradata/cuug/redo1a.log'  SIZE 10M,

  GROUP 2 '/public/oradata/cuug/redo2a.log'  SIZE 10M

-- STANDBY LOGFILE

DATAFILE

  '/public/oradata/cuug/system01.dbf',

  '/public/oradata/cuug/undotbs01.ora',

  '/public/oradata/cuug/sysaux01.ora',

  '/public/oradata/cuug/cwxusers',

  '/public/oradata/cuug/userdata01'

CHARACTER SET ZHS16GBK

;

 

查看日志文件状态：

SQL> select * from v$log;

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

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

FIRST_CHANGE# FIRST_TIM

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

         1          1          0   10485760          1 YES UNUSED

            0

 

         2          1          0   10485760          1 YES INVALIDATED

          0

 

打开数据库：

SQL> alter database open resetlogs;

 

 

说明：

1 ）把数据库打开到 MOUNT 状态下。

2 ）重建控制文件。

3 ）使用 RESETLOGS 打开数据库。

4 ）对数据库重新备份。

 

 

 

裸设备数据文件备份方法

创建自动备份脚本：

SQL> set FEED off 

SQL> set ECHO off

SQL> set HEA off

SQL> set HEADS off

SQL>spool osbackup

SQL>ALTER TABLESPACE SYSTEM BEGIN BACKUP;

select 'host dd if='||file_name||' of=/newdir/backup'||file_name||' bs=8192' from dba_data_files

where tablespace_name=’SYSTEM’;

SQL>ALTER TABLESPACE SYSTEM END BACKUP;

………

SQL>spool off

修改自动备份脚本，最后结果如下：

#more osbackup.sh(osbackup.sh 内容如下)

 

sqlplus '/as sysdba' <<EOF

 

alter tablespace system begin backup;

host dd if=/oracle/oradata/rawdb/system01.dbf of=/oracle/rawbackup/oracle/oradata/rawdb/system01.dbf bs=8192

alter tablespace system end backup;

 

alter tablespace sysaux begin backup;

host dd if=/oracle/oradata/rawdb/sysaux01.dbf of=/oracle/rawbackup/oracle/oradata/rawdb/sysaux01.dbf bs=8192                                                                                alter tablespace sysaux end backup;

 

alter tablespace undotbs1 begin backup;

host dd if=/oracle/oradata/rawdb/undotbs01.dbf of=/oracle/rawbackup/oracle/oradata/rawdb/undotbs01.dbf bs=8192                                                                                

alter tablespace undotbs1 end backup;

exit;

EOF

恢复时还是使用dd 命令进行恢复。

说明：

1）   使用dd 命令对裸设备文件进行读写。

2）   编辑脚本文件，使之能够自动运行。

 

 

数据库部分恢复法：

由于有时恢复条件不满足数据库的全库恢复，如磁盘空间不够，但是还要进行恢复测试，这时可以采用部分恢复法，对备份的数据进行可用性检查。

1) 获取需要恢复的表空间所包含的数据文件：

SQL> spool needdbname

SQL> select d.name from v$datafile d,v$tablespace t

  2   where d.ts#=t.ts#

  3  and t.name in ('SYSTEM','SYSAUX','UNDOTBS1');

 

NAME

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

/oracle/oradata/cuug/system01.dbf

/oracle/oradata/cuug/undotbs1.ora

/oracle/oradata/cuug/sysaux01.ora

 

SQL> spool off

 

2) 获取不需要恢复的表空间所包含的数据文件：

SQL> spool nodbname

SQL> select d.name from v$datafile d,v$tablespace t

  2  where d.ts#=t.ts#

  3  and t.name not in ('SYSTEM','SYSAUX','UNDOTBS1');

 

NAME

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

/oracle/oradata/cuug/users.dbf

 

SQL> spool off

 

3) 对文件内容就行提炼：

[oracle@Oracle oracle]$ grep oracle needdbname.lst >onlinename

[oracle@Oracle oracle]$ grep oracle nodbname.lst > offlinename

 

4) 创建该名的脚本：

                                      

[oracle@Oracle oracle]$ vi rename.sh

 

for i in `cat onlinename`

do

sqlplus '/as sysdba' <<EOF

alter database rename file '$i' to '/oracle/new$i';

exit

EOF

done

 

5) 创建使数据文件脱机的脚本：

[oracle@Oracle oracle]$ vi offline.sh

 

for i in `cat offlinename`

do

sqlplus '/as sysdba' <<EOF

alter database datafile '$i' offline;

exit

EOF

done

 

6) 启动实例，执行rename.sh 和offline.sh 脚本，然后recover database ，最后打开数据库。