Backup & Recovery 常用命令rman

Backup & Recovery 常用命令rman

1 . set database to archivelog mode

SQL> shutdown immediate;

 Database closed. Database dismounted. ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total

System Global Area 251658240 bytes

Fixed Size 1248356 bytes

Variable Size 83887004 bytes

Database Buffers 159383552 bytes

Redo Buffers 7139328 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1319

Next log sequence to archive 1321

Current log sequence 1321

 

2 . define flash recovery area

SQL> alter system set db_recovery_file_dest='D:\oracle\flash_recovery_area' scope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=15G scope=both;

System altered.

 

Oracle will use Oracle Managed File (OMF) for the flash recovery area. The free space in recovery area can be checked using OEM: All Metrics -> Recovery Area, or query vflash_recovery_area_usage view. To get more free space, you can use the following command to backup and then delete all the archived log files.

RMAN> backup archivelog all delete all input; 

 

3 . define multiple archived log destinations

SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest' scope=both;

System altered.

SQL> alter system set log_archive_dest_2='location=D:\oracle\archivelog_area1' scope=both;

System altered.

 

4 . CONTROL_FILE_RECORD_KEEP_TIME

 

This parameter specify the minimum days the RMAN information is stored in the control file before overwritten. The default value is 7 days. When using catalog, a smaller value should be chosen.

 

5 . starting RMAN

rman target /

rman target / nocatalog

rman target

rman target sys/change_on_install@orcl catalog

rman target sys/change_on_install log=xxx.log append

rman target sys/change_on_install cmdfile=xxx.rcv

rman target sys/sys append @'/home/oracle/XXX.rcv'

 

6 . RMAN persistent settings

 

6.1 display settings

RMAN> show all;

RMAN> show controlfile autobackup;

RMAN> show retention policy;

 

6.2 configure settings

RMAN> configure retention policy to recovery window of 7 days;

RMAN> configure backup optimization on;

RMAN> configure default device type to disk;

RMAN> configure controlfile autobackup on;

RMAN> configure controlfile autobackup format for device type disk to '%F';

RMAN> configure device type disk backup type to compressed backupset parallelism 1;

RMAN> configure datafile backup copies for device type disk to 1;

RMAN> configure archivelog backup copies for device type disk to 1;

RMAN> configure channel device type disk maxpiecesize 1 G;

RMAN> configure maxsetsize to unlimited;

RMAN> configure encryption for database off;

RMAN> configure encryption algorithm 'AES128';

RMAN> configure archivelog deletion policy to none;

RMAN> configure snapshot controlfile name to 'D:\oracle\flash_recovery_area\sncfepcit.ora';

 

If the control file autobackup is enabled, then RMAN automatically backs up the control file and spfile in one of two circumstances:
- A successful backup is recorded in the RMAN repository
- A structural change to the database affects the contents of the control file

Control file autobackups are stored in the flash recovery area, unless otherwise specified.

 

6.3 reset settings to default value

RMAN> configure controlfile autobackup clear;

RMAN> configure retention policy clear;

 

7. RMAN commands

 

7.1 backup commands

RMAN> backup database;

RMAN> backup copy of database;

RMAN> backup as compressed backupset database;

RMAN> backup as backupset format ''/BACKUP/df_%d_%s_%p.bus' tablespace users;

RMAN> backup database plus archivelog delete all input;

RMAN> backup archivelog from sequence=234 delete input;

RMAN> backup tag='month_full_backup' datafile 1, 2, 3, 4;

RMAN> backup incremental level 0 database;

RMAN> backup incremental level 1 database;

RMAN> backup incremental level 1 cumulative database;

RMAN> run {

2> allocate channel c1 device type sbt;

3> allocate channel c2 device type sbt;

4> allocate channel c3 device type sbt;

5> backup incremental level = 0

6> format '/disk1/backup/df_%d_%s_%p.bak'

7> (datafile 1,4,5 channel c1)

8> (datafile 2,3,9 channel c2)

9> (datafile 6,7,8 channel c3);

10> alter system archive log current;

11> }

 

7.2 block change tracking

SQL> alter database enable block change tracking using file ''/mydir/rman_change_track.f' reuse;

SQL> alter database disable block change tracking;

 

7.3 incrementally updating backups

RMAN> backup as copy incremental level 0 datafile 1 tag 'basecopy';

RMAN> backup incremental level 1 for recover of copy with tag 'basecopy' datafile 1;

RMAN> recover copy of datafile 1 with tag 'basecopy';

 

7.4 list commands

RMAN> list backup of database summary;

RMAN> list backup of tablespace system summary;

RMAN> list backup of datafile {n|'file_name'} summary;

RMAN> list backup of database by {file|backup};

RMAN> list copy of database;

 

7.5 report commands

RMAN> report schema;

RMAN> report need backup incremental 0 database;

RMAN> report need backup days 3;

RMAN> report need backup redundancy 2;

RMAN> report need backup recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report obsolete recovery window of 3 days;

 

7.6 delete commands

RMAN> delete noprompt expired copy;

RMAN> delete noprompt expired backup;

RMAN> delete noprompt obsolete;

RMAN> delete obsolete redundancy 2;

RMAN> delete obsolete recovery window of 3 days;

RMAN> delete backupset 4;

 

8. RMAN v$ views

V$ARCHIVED_LOG

V$BACKUP_CORRUPTION

V$BACKUP_DEVICE

V$BACKUP_FILES

V$BACKUP_PIECE

V$BACKUP_REDOLOG

V$BACKUP_SET

V$BACKUP_SPFILE

V$COPY_CORRUPTION

V$RMAN_CONFIGURATION

 

 

Oracle backup&Restore(2)

第四章. 数据库恢复案例

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> @coldbak.sql 或在 DOS 下 svrmgrl @coldbak.sql

 

3.  再插入记录

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

                         A

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

                         1

                         2

4.  关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

 

5.  毁坏一个或多个数据文件，如删除 user01.dbf

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

模拟媒体毁坏。

 

6.  重新启动数据库，会发现如下错误

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 3 - see DBWR trace file

ORA-01110: data file 3: 'D:\Oracle\ORADATA\TEST\USERS01.DBF'

 

在报警文件中，会有更详细的信息

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 3: 'D:\Oracle\ORADATA\TEST\USERS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

 

7.  拷贝备份复原到原来位置 (restore 过程 )

C:\>xcopy d:\database\*.* d:\Oracle\oradata\test/H/R/S

 

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

SQL> alter database open;

Database altered.

SQL> select * from test;

                         A

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

                         1

 

这里可以发现，数据库恢复成功，但在备份之后与崩溃之前的数据丢失了。

说明：

1 、非归档模式下的恢复方案可选性很小，一般情况下只能有一种恢复方式，就是数据库的冷备
份的完全恢复，仅仅需要拷贝原来的备份就可以 (restore) ，不需要 recover ；

2 、这种情况下的恢复，可以完全恢复到备份的点上，但是可能是丢失数据的，在备份之后与崩溃之前的数据将全部丢失；

3 、不管毁坏了多少数据文件或是联机日志或是控制文件，都可以通过这个办法恢复，因为这个恢复过程是 Restore 所有的冷备份文件，而这个备份点上的所有文件是一致的，与最新的数据库没有关系，就好比把数据库又放到了一个以前的 " 点 " 上；

4 、对于非归档模式下，最好的办法就是采用 OS 的冷备份，建议不要用 RMAN 来作冷备份，效果不好，因为 RMAN 不备份联机日志， restore 不能根本解决问题；

5 、如果没有备份联机日志，如 RMAN 的备份，就需要利用不完全恢复 (until cancel) 的方法来重新创建联机日志文件。

4.2 归档模式下丢失或损坏一个数据文件

4.2.1 OS 备份方案

在归档方式下损坏或丢失一个数据文件，如果存在相应的备份与该备份以来的归档日志，恢复还是比较简单的，可以作到尽量少的 Down 机时间，并能作到数据库的完全恢复。

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

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int) tablespace users;

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

 

2 、 备份数据库

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

 

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\USERS01.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 3 - see DBWR trace file

ORA-01110: data file 3: 'D:\Oracle\ORADATA\TEST\USERS01.DBF'

还可以查看报警文件（见上一个恢复案例）或动态视图 v$recover_file

如 SQL> select * from v$recover_file;

 

     FILE# ONLINE  ERROR                    CHANGE#    TIME

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

         3   ONLINE                        1013500    2003-05-07

 

脱机数据文件

SQL> alter database datafile 3 offline drop;

Database altered.

 

6 、 打开数据库，拷贝备份回来 (restore) ，恢复 (recover) 该数据文件，并联机 :

SQL> alter database open;

Database altered.

拷贝备份从备份处

copy d:\databak\ users01.dbf d:\Oracle\oradata\test;

恢复该数据文件

SQL> recover datafile 3;

ORA-00279: change 1053698 generated at 05/07/2003 17:51:26 needed for

thread 1

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

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

 

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

AUTO

ORA-00279: change 1053701 generated at 05/07/2003 17:51:39 needed for

thread 1

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

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

ORA-00278: log file 'D:\Oracle\ORADATA\TEST\ARCHIVE\TESTT001S00304.ARC' no longer needed for this recovery Log applied.

Media recovery complete.

恢复成功，联机该数据文件

SQL> alter database datafile 3 online;

Database altered.

 

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

SQL> select * from test;

                         A

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

                         1

                         2

说明：

1 、采用热备份，需要运行在归档模式下，可以实现数据库的完全恢复，也就是说，从备份后到数据库崩溃时的数据都不会丢失 ;

2 、可以采用全备份数据库的方式备份，对于特殊情况，也可以只备份特定的数据文件，如只备份用户表空间（一般情况下对于某些写特别频繁的数据文件，可以单独加大备份频率）；

3 、如果在恢复过程中，发现损坏的是多个数据文件，即可以采用一个一个数据文件的恢复方法（第 5 步中需要对数据文件一一脱机，第 6 步中需要对数据文件分别恢复），也可以采用整个数据库的恢复方法；

4 、如果是系统表空间的损坏，不能采用此方法。

 

4.2.2 RMAN 备份方案

RMAN 也可以进行联机备份，而且备份与恢复方法将比 OS 备份更简单可靠。

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

SQL> connect internal/password as sysdba;

Connected.

 

SQL> create table test(a int) tablespace users;

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

 

2 、 备份数据库表空间 users

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> backup tag 'tsuser' format 'd:\backup\tsuser_%u_%s_%p'

4> tablespace users;

5> release channel c1;

6> }

 

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

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

 

RMAN-03022: compiling command: backup

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-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03

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

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\TSUSER_05EN93AC_5_1 comment=NONE

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

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

 

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>r

1* alter system switch logfile;

System altered.

 

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

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down

 

C:\>del D:\Oracle\ORADATA\TEST\USER01.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 3 - see DBWR trace file

ORA-01110: data file 3: 'D:\Oracle\ORADATA\TEST\USER01.DBF'

 

6 、 先打开数据库

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

 

7 、 恢复该表空间

恢复脚本可以是恢复单个数据文件

run{

allocate channel c1 type disk;

restore datafile 3;

recover datafile 3;

sql 'alter database datafile 3 online';

release channel c1;

}

也可以是 , 恢复表空间

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 3 online';

release channel c1;

}

过程如下：

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 datafile 3;

4> recover datafile 3;

5> sql 'alter database datafile 3 online';

6> release channel c1;

7> }

 

// 输出内容冗长，省略 -- 编者

RMAN>

 

8 、 检查数据是否完整

SQL> alter database open;

Database altered.

 

SQL> select * from test;

                         A

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

                         1

                         2

说明：

1 、 RMAN 也可以实现单个表空间或数据文件的恢复，恢复过程可以在 mount 下或 open 方式下，如果在 open 方式下恢复，可以减少 down 机时间 ;

2 、如果损坏的是一个数据文件，建议 offline 并在 open 方式下恢复 ;

3 、这里可以看到， RMAN 进行数据文件与表空间恢复的时候，代码都比较简单，而且能保证备份与恢复的可靠性，所以建议采用 RMAN 的备份与恢复 .

 

4.3 丢失多个数据文件，实现整个数据库的恢复 .

 

4.3.1 OS 备份方案

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 、 继续在测试表中插入记录

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'

 

详细信息可以查看报警文件

ORA-1157 signalled during: ALTER DATABASE OPEN...

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

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

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

 

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 2: 'D:\Oracle\ORADATA\TEST\RBS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

 

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 5: 'D:\Oracle\ORADATA\TEST\TOOLS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

 

Thu May 08 09:39:36 2003

Errors in file D:\Oracle\admin\test\bdump\testDBW0.TRC:

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

ORA-01110: data file 6: 'D:\Oracle\ORADATA\TEST\INDX01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系统找不到指定的文件。

 

通过查询 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 、 拷贝备份回到原地点 (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 [0001191]   ;

 

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

 

6 、 检查数据

Database altered.

SQL> select * from test;

                         A

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

                         1

                         2

可以看到，表依然存在。

 

说明：

1 、 RMAN 也可以实现不完全恢复，方法比 OS 备份恢复的方法更简单可靠；

2 、 RMAN 可以基于时间，基于改变与基于日志序列的不完全恢复，基于日志序列的恢复可以指定恢复到哪个日志序列，如

run { 

        allocate channel ch1 type disk; 

        allocate channel ch2 type 'sbt_tape';

        set until logseq 1234 thread 1;

        restore controlfile to '$Oracle_HOME/dbs/cf1.f' ; 

        replicate controlfile from '$Oracle_HOME/dbs/cf1.f';

         alter database mount; 

        restore database; 

        recover database; 

        sql "ALTER DATABASE OPEN RESETLOGS";

}

3 、 与所有的不完全恢复一样，必须在 mount 下， restore 所有备份数据文件，需要 resetlogs ；

4 、 基于改变的恢复比基于时间的恢复更可靠，但是可能也更复杂，需要知道需要恢复到哪一个改变号 (SCN) ，在正常生产中，获取 SCN 的办法其实也有很多，如查询数据库字典表 (V$archived_log or v$log_history) ，或分析归档与联机日志 (logmnr) 等