当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

Oracle备份与恢复案例二


  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、 关闭数据库,模拟丢失数据文件
  <
相关内容
赞助商链接