Oracle备份与恢复案例(三)

6、 拷贝备份回到原地点(restore)，开始恢复数据库(recover)

restore过程:

C:\>copy D:\DATABAK\SYSTEM01.DBF D:\Oracle\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\INDX01.DBF D:\Oracle\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\TOOLS01.DBF D:\Oracle\ORADATA\TEST\

C:\>copy D:\DATABAK\TEST\RBS01.DBF.DBF D:\Oracle\ORADATA\TEST\

 

Recover过程:

SQL> recover database;

 

ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for thread 1

ORA-00289: suggestion : D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC

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

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 1

ORA-00289: suggestion : D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC

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

ORA-00278: log file 'D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00311.ARC' no

longer needed for this recovery

 

ORA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 1

ORA-00289: suggestion : D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC

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

ORA-00278: log file 'D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00312.ARC' no

longer needed for this recovery

 

ORA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 1

ORA-00289: suggestion : D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00314.ARC

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

ORA-00278: log file 'D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00313.ARC' no

longer needed for this recovery

 

Log applied.

Media recovery complete.

 

7、  打开数据库，检查数据库的数据（完全恢复）

SQL> alter database open;

Database altered.

SQL> select * from test;

                         A

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

                         1

                         2

 

说明：

1、只要有备份与归档存在，就可以实现数据库的完全恢复（不丢失数据）;

2、适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复;

3、恢复过程在mount下进行，如果恢复成功，再打开数据库，down机时间可能比较长一些。

 

4.3.2 RMAN备份方案

RMAN备份归档模式下损坏（丢失）多个数据文件，进行整个数据库的恢复

1、连接数据库，创建测试表并插入记录

 

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

 

2、备份数据库

DOS下 C:>\ rman cmdfile=bakup.rcv msglog=backup.log;

 

以下是backup.log内容。

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> #     script:bakup.rcv

2> #     creater:chenjiping

3> #     date:5.8.2003

4> #     desc:backup all database datafile in archive with rman

5>

6> #connect database

7> connect rcvcat rman/rman@back;

8> connect target internal/virpure;

9>

10> #start backup database

11> run{

12> allocate channel c1 type disk;

13> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database

14> include current controlfile;

15> sql 'alter system archive log current';

16> release channel c1;

17> }

18> #end

19>

 

RMAN-06008: connected to recovery catalog database

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=15 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03

RMAN-08010: channel c1: specifying datafile(s) in backupset

RMAN-08522: input datafile fno=00002 name=D:\Oracle\ORADATA\TEST\RBS01.DBF

RMAN-08522: input datafile fno=00001 name=D:\Oracle\ORADATA\TEST\SYSTEM01.DBF

RMAN-08011: including current controlfile in backupset

RMAN-08522: input datafile fno=00005 name=D:\Oracle\ORADATA\TEST\TOOLS01.DBF

RMAN-08522: input datafile fno=00004 name=D:\Oracle\ORADATA\TEST\TEMP01.DBF

RMAN-08522: input datafile fno=00006 name=D:\Oracle\ORADATA\TEST\INDX01.DBF

RMAN-08522: input datafile fno=00003 name=D:\Oracle\ORADATA\TEST\USER01.DBF

RMAN-08013: channel c1: piece 1 created

RMAN-08503: piece handle=D:\BACKUP\FULL04EN5UG0_4_1 comment=NONE

RMAN-08525: backup set complete, elapsed time: 00:01:16

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter system archive log current

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

Recovery Manager complete.

到这里表示备份成功。

 

3、  继续在测试表中插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

                         A

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

                         1

                         2

SQL>alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

 

4、  关闭数据库，模拟丢失数据文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down

C:\>del D:\Oracle\ORADATA\TEST\SYSTEM01.DBF

C:\>del D:\Oracle\ORADATA\TEST\INDX01.DBF

C:\>del D:\Oracle\ORADATA\TEST\TOOLS01.DBF

C:\>del D:\Oracle\ORADATA\TEST\RBS01.DBF

 

5、启动数据库，检查错误

SQL> STARTUP

Oracle instance started.

Total System Global Area  102020364 bytes

Fixed Size                    70924 bytes

Variable Size              85487616 bytes

Database Buffers           16384000 bytes

Redo Buffers                  77824 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'D:\Oracle\ORADATA\TEST\SYSTEM01.DBF'

 

查询v$recover_file

SQL> select * from v$recover_file;

 

     FILE# ONLINE  ERROR                 CHANGE# TIME

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

         1 ONLINE  FILE NOT FOUND              0

         2 ONLINE  FILE NOT FOUND              0

         5 ONLINE  FILE NOT FOUND              0

         6 ONLINE  FILE NOT FOUND              0

可以知道有四个数据文件需要恢复.

 

6、利用RMAN进行恢复

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> restore database;

4> recover database;

5> sql 'alter database open';

6> release channel c1;

7> }

 

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=17 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00001 to D:\Oracle\ORADATA\TEST\SYSTEM01.DBF

RMAN-08523: restoring datafile 00002 to D:\Oracle\ORADATA\TEST\RBS01.DBF

RMAN-08523: restoring datafile 00003 to D:\Oracle\ORADATA\TEST\USER01.DBF

RMAN-08523: restoring datafile 00004 to D:\Oracle\ORADATA\TEST\TEMP01.DBF

RMAN-08523: restoring datafile 00005 to D:\Oracle\ORADATA\TEST\TOOLS01.DBF

RMAN-08523: restoring datafile 00006 to D:\Oracle\ORADATA\TEST\INDX01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:\BACKUP\FULL04EN5UG0_4_1 tag=DBFULL params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 327 is already on disk as file D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00327.ARC

RMAN-06050: archivelog thread 1 sequence 328 is already on disk as file D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00328.ARC

RMAN-06050: archivelog thread 1 sequence 329 is already on disk as file D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00329.ARC

RMAN-06050: archivelog thread 1 sequence 330 is already on disk as file D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00330.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00327.ARC thread=1 sequence=327

RMAN-08515: archivelog filename=D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00328.ARC thread=1 sequence=328

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database open

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

 

7、  检查数据库的数据（完全恢复）

SQL> select * from test;

                         A

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

                         1

                         2

 

说明：

1、只要有备份与归档存在，RMAN也可以实现数据库的完全恢复（不丢失数据）;

2、同OS备份数据库恢复，适合于丢失大量数据文件，或包含系统数据文件在内的数据库的恢复;

3、目标数据库在mount下进行，如果恢复成功，再打开数据库;

4、RMAN的备份与恢复命令相对比较简单并可靠，建议有条件的话，都采用RMAN进行数据库的备份。

 

4.4 不完全恢复案例

 

4.4.1 OS备份下的基于时间的恢复

 

不完全恢复可以分为基于时间的恢复，基于改变的恢复与基于撤消的恢复，这里已基于时间的恢复为例子来说明不完全恢复过程。

 

基于时间的恢复可以不完全恢复到现在时间之前的某一个时间，对于某些误操作，如删除了一个数据表，可以在备用恢复环境上恢复到表的删除时间之前，然后把该表导出到正式环境，避免一个人为的错误。

 

1、  连接数据库，创建测试表并插入记录:

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

 

2、  备份数据库，这里最好备份所有的数据文件，包括临时数据文件:

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql

或冷备份也可以

 

3、  删除测试表，假定删除前的时间为T1，在删除之前，便于测试，继续插入数据并应用到归 

档。

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

                         A

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

                         1

                         2

SQL> alter system switch logfile;

Statement processed.

SQL> alter system switch logfile;

Statement processed.

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

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

2003-05-21 14:43:01

SQL> drop table test;

Table dropped.

 

4、  准备恢复到时间点T1，找回删除的表，先关闭数据库:

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

 

5、  拷贝刚才备份的所有数据文件回来

C:\>copy D:\DATABAK\*.DBF D:\Oracle\ORADATA\TEST\

 

6、  启动到mount下

SQL> startup mount;

Oracle instance started.

Total System Global Area  102020364 bytes

Fixed Size                    70924 bytes

Variable Size              85487616 bytes

Database Buffers           16384000 bytes

Redo Buffers                  77824 bytes

Database mounted.

 

7、  开始不完全恢复数据库到T1时间

SQL> recover database until time '2003-05-21:14:43:01';

ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1

ORA-00289: suggestion : D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC

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

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

8、  打开数据库，检查数据

SQL> alter database open resetlogs;

 

Database altered.

SQL> select * from test;

                         A

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

                         1

                         2

 

说明：

1、不完全恢复最好备份所有的数据，冷备份亦可，因为恢复过程是从备份点往后恢复的，如果因为其中一个数据文件的时间戳(SCN)大于要恢复的时间点，那么恢复都是不可能成功的;

2、不完全恢复有三种方式，过程都一样，仅仅是recover命令有所不一样，这里用基于时间的恢复作为示例;

3、不完全恢复之后，都必须用resetlogs的方式打开数据库，建议马上再做一次全备份，因为resetlogs之后再用以前的备份恢复是很难了;

4、以上是在删除之前获得时间，但是实际应用中，很难知道删除之前的实际时间，但可以采用大致时间即可，或可以采用分析日志文件(logmnr)，取得精确的需要恢复的时间;

5、一般都是在测试机后备用机器上采用这种不完全恢复，恢复之后导出/导入被误删的表回生产系统.

 

4.4.2 RMAN备份下的基于改变的恢复

以上用OS备份说明了一个基于时间的恢复，现在用RMAN说明一个基于改变的恢复

 

1、  连接数据库，创建测试表并插入记录

 

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

 

2、  备份数据库

C:\>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=874705288)

 

RMAN> run{

2> allocate channel c1 type disk;

3> backup full tag 'dbfull' format 'd:\backup\full%u_%s_%p' database

4> include current controlfile;

5> sql 'alter system archive log current';

6> release channel c1;

7> }

 

   //屏幕输出内容冗长，省略--编辑

RMAN>

 

3、  删除测试表，在删除之前，便于测试，继续插入数据并应用到归档，并获取删除前的scn号。

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

                         A

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

                         1

                         2

SQL> alter system switch logfile;

Statement processed.

SQL> alter system switch logfile;

Statement processed.

 

SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;

     SCN

----------

  31014

SQL> drop table test;

Table dropped.

 

4、  准备恢复到SCN 31014，先关闭数据库，然后启动到mount下

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

SQL> startup mount;

 

5、  开始恢复到改变点SCN 31014

RMAN> run{

2>      allocate channel c1 type disk;

3>      restore database;

4>      recover database until scn 31014;

5>      sql 'ALTER DATABASE OPEN RESETLOGS';

6>      release channel c1;

7> }

 

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=10 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=1 set_stamp=494613682 creation_time=21-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00001 to D:\Oracle\ORADATA\TEST\SYSTEM01.DBF

RMAN-08523: restoring datafile 00002 to D:\Oracle\ORADATA\TEST\RBS01.DBF

RMAN-08523: restoring datafile 00003 to D:\Oracle\ORADATA\TEST\USERS01.DBF

RMAN-08523: restoring datafile 00004 to D:\Oracle\ORADATA\TEST\TEMP01.DBF

RMAN-08523: restoring datafile 00005 to D:\Oracle\ORADATA\TEST\TOOLS01.DBF

RMAN-08523: restoring datafile 00006 to D:\Oracle\ORADATA\TEST\INDX01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:\BACKUP\FULL01ENMD5I_1_1 tag=DBFULL params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 191 is already on disk as file D:\ORACL

E\ORADATA\TEST\ARCHIVE\TESTT001S00191.ARC

RMAN-06050: archivelog thread 1 sequence 192 is already on disk as file D:\ORACL

E\ORADATA\TEST\ARCHIVE\TESTT001S00192.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00191.AR

C thread=1 sequence=191

RMAN-08515:archivelog filename=D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00192.ARC

Thread=1 sequence=192

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: ALTER DATABASE OPEN RESETLOGS

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1