利用 RMAN 进行异机全恢复

服务器资料信息

servername	role	sid	db_unique_name	db­_name	lsn name	ip addr
node222	primary	TBDB	node222	node222	node222	192.168.17.222
node173	standby	TBDB	node173	node222	node173	192.168.17.173

 

node222, node173 数据文件，日志文件，归档日志存放位置一致

 

为实现物理standby

物理standby与primary数据库完全一模一样 同一镜像数据库，则 sid, db_name 必须一致 为标示不同数据库， db­_unique_name 必须不一样 为连接不同数据库，连接器命令也必须不一样

 

目标

在 primary 中导入测试数据，创建 rman 进行全备，复制备份片至standby 然后导入 rman

 

创建数据表空间 (node173,node222)

create tablespace RECHARGE datafile  '/u01/app/oracle/oradata/node222/recharge.dbf' size 500M extent management  local;

 

创建应用连接的用户(node173,node222)

SQL> grant connect, resource to  RECHARGE identified by   "RECHARGE"; SQL> alter user RECHARGE default  tablespace RECHARGE;

 

exp数据导入

注意, 数据导入过程中需要操作系统语言, 数据库语言, 客户端语言同步

 

查询并修改数据库语言环境

查询方法

SQL> select userenv('language') from  dual;   USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.AL32UTF8

 

修改方法

SQL> SHUTDOWN immediate; SQL> STARTUP RESTRICT SQL> alter database character set  internal_use AL32UTF8 ; SQL> ALTER SYSTEM DISABLE RESTRICTED  SESSION; SQL> shutdown immediate; SQL> startup

 

确保 oracle 语言环境一致后

修改系统语言环境并导入数据 (node222)

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 imp userid=system/oracle  touser=RECHARGE  file=day_new.dmp

 

创建RMAN(node173,node222)

SQL> create tablespace rman logging datafile  '/u01/app/oracle/oradata/node222/rman.dbf' size 1024M extent management local segment space  management auto;

 

创建 RMAN 授权(node173,node222)

create user rman identified by rman  default tablespace rman; grant connect,resource to rman; grant recovery_catalog_owner to rman; grant unlimited tablespace to rman; grant sysdba to rman;

 

建立catalog数据库及注册数据库(node173,node222)

rman catalog rman/rman RMAN> create catalog tablespace  "RMAN"; RMAN> exit

 

连接 catalog(node173,node222)

rman target sys/oracle@node222 catalog  rman/rman@node222

 

         注: 需启动连接器，node222为客户端连接服务器的标识符

 

返回下面信息为正常连接到 RMAN

connected to target database: NODE222  (DBID=3694045568) connected to recovery catalog database

 

注册 catalog(node173,node222)

RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete

 

创建备份目录

mkdir /u01/app/oracle/backup cd /u01/app/oracle/backup mkdir controlfileback dbback log logback controlfileback 存放备份的控制文件 dbback        存放备份的数据文件 log           存放备份生成的日志，坚持查询该日志查看备份是否成功 logback       存放归档日志文件

 

备份数据(node222)

rman target sys/oracle@node222 catalog  rman/rman@node222

         注: @node222 是 listener 监听器连接（配置略）

 

显示一下全局配置信息

show all;

 

修改自动备份 control 功能

默认状态

CONFIGURE BACKUP OPTIMIZATION OFF; # 默认状态 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db1/dbs/snapcf_TBDB.f';  # 默认保存位置

修改

CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT  FOR DEVICE TYPE DISK TO  '/u01/app/oracle/product/11.2.0/db1/dbs/cf%F';

 

修改备份数据最长时间

CONFIGURE RETENTION POLICY TO RECOVERY  WINDOW OF 15 DAYS;

 

如果需要删除过时备份

DELETE OBSOLETE

 

全库备份

RMAN> sql 'alter system archive log  current'; RMAN> backup database format  '/u01/app/oracle/backup/dbback/TBDB_%d_%T_%U';

备份过程信息如下

Starting backup at 28-JUN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=198 device  type=DISK channel ORA_DISK_1: starting full  datafile backup set channel ORA_DISK_1: specifying  datafile(s) in backup set input datafile file number=00007  name=/u01/app/oracle/oradata/node222/rman.dbf input datafile file number=00001  name=/u01/app/oracle/oradata/node222/system01.dbf input datafile file number=00002  name=/u01/app/oracle/oradata/node222/sysaux01.dbf input datafile file number=00006  name=/u01/app/oracle/oradata/node222/recharge.dbf input datafile file number=00003  name=/u01/app/oracle/oradata/node222/undotbs01.dbf input datafile file number=00005  name=/u01/app/oracle/oradata/node222/example01.dbf input datafile file number=00004  name=/u01/app/oracle/oradata/node222/users01.dbf channel ORA_DISK_1: starting piece 1 at  28-JUN-13 channel ORA_DISK_1: finished piece 1 at 28-JUN-13 piece  handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130628_01odb0sc_1_1  tag=TAG20130628T150651 comment=NONE channel ORA_DISK_1: backup set complete,  elapsed time: 00:00:35 Finished backup at 28-JUN-13   Starting Control File and SPFILE Autobackup  at 28-JUN-13 piece  handle=/u01/app/oracle/product/11.2.0/db1/dbs/cfc-3695602788-20130628-00  comment=NONE Finished Control File and SPFILE  Autobackup at 28-JUN-13

 

归档备份方法

sql 'alter system archive log current'; backup archivelog all format '/u01/app/oracle/backup/logback/arc_%t_%s'  delete all input;

 

控制文件手动备份方法

backup current controlfile format  '/u01/app/oracle/backup/controlfileback/cntrl_%s_%p_%s';

 

查询备份信息

RMAN> list backup;

 

备份信息显示

List of Backup Sets ===================   BS Key   Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- -----------  ------------ --------------- 159      Full    1.56G      DISK        00:01:02     27-JUN-13          BP Key: 162   Status:  AVAILABLE  Compressed: NO  Tag: TAG20130627T121557          Piece Name:  /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1    List of Datafiles in backup set 159    File LV Type Ckp SCN    Ckp  Time  Name    ---- -- ---- ---------- --------- ----    1       Full 3122357    27-JUN-13 /u01/app/oracle/oradata/node222/system01.dbf    2       Full 3122357    27-JUN-13  /u01/app/oracle/oradata/node222/sysaux01.dbf    3       Full 3122357    27-JUN-13  /u01/app/oracle/oradata/node222/undotbs01.dbf    4       Full 3122357    27-JUN-13 /u01/app/oracle/oradata/node222/users01.dbf    5       Full 3122357    27-JUN-13  /u01/app/oracle/oradata/node222/example01.dbf    6       Full 3122357    27-JUN-13  /u01/app/oracle/oradata/node222/recharge.dbf    7       Full 3122357    27-JUN-13  /u01/app/oracle/oradata/node222/rman.dbf   BS Key   Type LV Size       Device Type  Elapsed Time Completion Time ------- ---- -- ---------- -----------  ------------ --------------- 160      Full    9.36M      DISK        00:00:02     27-JUN-13          BP Key: 163   Status:  AVAILABLE  Compressed: NO  Tag: TAG20130627T121557          Piece Name:  /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_02od82hv_1_1    SPFILE Included: Modification time: 27-JUN-13    SPFILE db_unique_name: NODE222    Control File Included: Ckp SCN: 3122444      Ckp time: 27-JUN-13   BS Key   Size       Device Type Elapsed  Time Completion Time ------- ---------- -----------  ------------ --------------- 191      746.56M    DISK        00:00:26     27-JUN-13         BP Key: 194   Status: AVAILABLE  Compressed: NO  Tag: TAG20130627T121920          Piece Name: /u01/app/oracle/backup/logback/arc_819202760_3      List of Archived Logs in backup set 191    Thrd Seq     Low SCN    Low Time   Next SCN   Next Time    ---- ------- ---------- --------- ---------- ---------    1    85      2901879    25-JUN-13 2936275    25-JUN-13    1    86      2936275    25-JUN-13 2952511    25-JUN-13    1    87      2952511    25-JUN-13 2974058    26-JUN-13    1    88      2974058    26-JUN-13 3000002    26-JUN-13    1    89      3000002    26-JUN-13 3018605    26-JUN-13    1    90      3018605    26-JUN-13 3046423    26-JUN-13    1    91      3046423    26-JUN-13 3056627    27-JUN-13    1    92      3056627    27-JUN-13 3080492    27-JUN-13    1    93      3080492    27-JUN-13 3094323    27-JUN-13    1    94      3094323    27-JUN-13 3098061    27-JUN-13    1    95      3098061    27-JUN-13 3098704    27-JUN-13    1    96      3098704    27-JUN-13 3099248    27-JUN-13    1    97      3099248    27-JUN-13 3099871    27-JUN-13    1    98      3099871    27-JUN-13 3100505    27-JUN-13    1    99      3100505    27-JUN-13 3101436    27-JUN-13    1    100     3101436    27-JUN-13 3101973    27-JUN-13    1    101     3101973    27-JUN-13 3107360    27-JUN-13    1    102     3107360    27-JUN-13 3120392    27-JUN-13    1    103     3120392    27-JUN-13 3122322    27-JUN-13    1    104     3122322    27-JUN-13 3122845    27-JUN-13    1    105     3122845    27-JUN-13 3122892    27-JUN-13   BS Key   Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- -----------  ------------ --------------- 221      Full    9.33M      DISK        00:00:02     27-JUN-13          BP Key: 243   Status:  AVAILABLE  Compressed: NO  Tag: TAG20130627T121956         Piece Name:  /u01/app/oracle/backup/controlfileback/cntrl_4_1_4    Control File Included: Ckp SCN: 3123051      Ckp time: 27-JUN-1

 

把 node222 RMAN 备份数据复制至 node173 然后做全库恢复

按照默认位置，把文件存放 /u01/app/oracle/backup/dbback 对应目录下

logback/arc_819202760_3               (archive log backup) controlfileback/cntrl_4_1_4             (control file backup) dbback/TBDB_NODE222_20130627_01od82ft_1_1  (datafile backup) dbback/TBDB_NODE222_20130627_02od82hv_1_1  (datafile backup)

 

执行 node173 恢复(下面所有操作在 node173 执行)

恢复控制文件，必须以 nomount 启动

shutdown immediate; startup nomount;

注: node222, node173当前 db_name,sid, 数据存放位置一致，否则需要重写 pfile(略)

 

恢复控制文件过程

rman target /   RMAN> restore controlfile from  '/u01/app/oracle/backup/controlfileback/cntrl_4_1_4';

 

恢复过程

channel ORA_DISK_1: restoring control  file channel ORA_DISK_1: restore complete,  elapsed time: 00:00:01 output file  name=/u01/app/oracle/oradata/node222/control01.ctl output file  name=/u01/app/oracle/flash_recovery_area/node222/control02.ctl Finished restore at 28-JUN-13

 

恢复数据，切换数据库至 mount 状态

SQL> alter database mount;

 

直接恢复数据库则可

RMAN> restore database;

 

恢复过程

Starting restore at 28-JUN-13 Starting implicit crosscheck backup at  28-JUN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device  type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at  28-JUN-13   Starting implicit crosscheck copy at  28-JUN-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at  28-JUN-13   searching for all files in the recovery  area cataloging files... no files cataloged   using channel ORA_DISK_1   channel ORA_DISK_1: starting datafile  backup set restore channel ORA_DISK_1: specifying  datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile  00001 to /u01/app/oracle/oradata/node222/system01.dbf channel ORA_DISK_1: restoring datafile  00002 to /u01/app/oracle/oradata/node222/sysaux01.dbf channel ORA_DISK_1: restoring datafile  00003 to /u01/app/oracle/oradata/node222/undotbs01.dbf channel ORA_DISK_1: restoring datafile  00004 to /u01/app/oracle/oradata/node222/users01.dbf channel ORA_DISK_1: restoring datafile  00005 to /u01/app/oracle/oradata/node222/example01.dbf channel ORA_DISK_1: restoring datafile  00006 to /u01/app/oracle/oradata/node222/recharge.dbf channel ORA_DISK_1: restoring datafile  00007 to /u01/app/oracle/oradata/node222/rman.dbf channel ORA_DISK_1: reading from backup  piece /u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1 channel ORA_DISK_1: piece  handle=/u01/app/oracle/backup/dbback/TBDB_NODE222_20130627_01od82ft_1_1  tag=TAG20130627T121557 channel ORA_DISK_1: restored backup piece  1 channel ORA_DISK_1: restore complete,  elapsed time: 00:00:55 Finished restore at 28-JUN-13

 

修复数据库 (主要利用 archive log 进行修复)

RMAN> restore database;

 

修复过程

Starting restore at 28-JUN-13 using channel ORA_DISK_1   skipping datafile 1; already restored to file  /u01/app/oracle/oradata/node222/system01.dbf skipping datafile 2; already restored to file  /u01/app/oracle/oradata/node222/sysaux01.dbf skipping datafile 3; already restored to file  /u01/app/oracle/oradata/node222/undotbs01.dbf skipping datafile 4; already restored to file  /u01/app/oracle/oradata/node222/users01.dbf skipping datafile 5; already restored to file  /u01/app/oracle/oradata/node222/example01.dbf skipping datafile 6; already restored to file /u01/app/oracle/oradata/node222/recharge.dbf skipping datafile 7; already restored to file  /u01/app/oracle/oradata/node222/rman.dbf restore not done; all files read only, offline, or already  restored Finished restore at 28-JUN-13

修复完成后启动数据库

SQL> alter database open resetlogs;   ERROR at line 1: ORA-01152: file 1 was not restored from a  sufficiently old backup ORA-01110: data file 1:  '/u01/app/oracle/oradata/node222/system01.dbf'

 

检测系统 SCN

SQL> select checkpoint_change# from  v$database;   CHECKPOINT_CHANGE# ------------------             3122322

 

检测数据文件 SCN

SQL> select checkpoint_change# from  v$datafile;   CHECKPOINT_CHANGE# ------------------             3122357             3122357             3122357             3122357             3122357             3122357             3122357   7 rows selected.   SQL> select checkpoint_change# from  v$datafile_header;   CHECKPOINT_CHANGE# ------------------             3122357             3122357             3122357             3122357             3122357             3122357             3122357   7 rows selected.

 

结论: 系统 SCN < 数据文件 SCN 导致报错

解决方法, 重新恢复一下控制文件

SQL> recover database using backup  controlfile until cancel; ORA-00279: change 3122357 generated at  06/27/2013 12:15:57 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/NODE173/archivelog/2013_06_28/o1_mf_1_104_%u _.arc ORA-00280: change 3122357 for thread 1 is  in sequence #104   Specify log: {<RET>=suggested |  filename | AUTO | CANCEL} cancel Media recovery cancelled.

 

重新启动数据库

SQL> alter database open resetlogs;   Database altered.

 

检测 node173, node222 数据，同步成功, 建议对服务器进行全备.