ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用备份的控制文件恢复数据库

使用备份的控制文件恢复数据库

原创 Linux操作系统 作者:wailon 时间:2013-11-20 21:38:54 0 删除 编辑

-- 备份控制文件,备份系统表空间会自动备份控制文件
RMAN> backup tablespace system;

Starting backup at 29-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 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=00001 name=/u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: starting piece 1 at 29-SEP-13
channel ORA_DISK_1: finished piece 1 at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/backupset/2013_09_29/o1_mf_nnndf_TAG20130929T164720_94ht8s6l_.bkp tag=TAG20130929T164720 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 29-SEP-13

Starting Control File and SPFILE Autobackup at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/autobackup/2013_09_29/o1_mf_s_827426866_94ht9mkz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-SEP-13

-- 模拟数据产生并切换日志
16:48:37 SCOTT@wailon> create table a tablespace wailon as select * from tab;

Table created.

16:48:56 SCOTT@wailon> select * from a;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A                              TABLE
BONUS                          TABLE
CHECKPOINT                     TABLE
CHECKPOINT_LOX                 TABLE
DEPT                           TABLE
EMP                            TABLE
GGS_DDL_COLUMNS                TABLE
GGS_DDL_HIST                   TABLE
GGS_DDL_HIST_ALT               TABLE
GGS_DDL_LOG_GROUPS             TABLE
GGS_DDL_OBJECTS                TABLE
GGS_DDL_PARTITIONS             TABLE
GGS_DDL_PRIMARY_KEYS           TABLE
GGS_DDL_RULES                  TABLE
GGS_DDL_RULES_LOG              TABLE
GGS_MARKER                     TABLE
GGS_SETUP                      TABLE
GGS_STICK                      TABLE
GGS_TEMP_COLS                  TABLE
GGS_TEMP_UK                    TABLE
OB                             TABLE
SALGRADE                       TABLE
SYS_EXPORT_SCHEMA_01           TABLE

23 rows selected.

16:49:01 SCOTT@wailon> alter system switch logfile;

System altered.

16:49:08 SCOTT@wailon> create table b tablespace wailon as select * from a;

Table created.

16:49:32 SCOTT@wailon> alter system switch logfile;

System altered.

16:50:08 SCOTT@wailon> alter system checkpoint;

System altered.

-- 确认当前日志序号
16:50:20 SCOTT@wailon> select group#,sequence#,status from v$log;

GROUP#  SEQUENCE# STATUS
----------   --------------- ----------------
         1        244         INACTIVE
         3        246         CURRENT
         2        245         INACTIVE

16:50:30 SYS@wailon> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         2 /u01/app/oracle/oradata/wailon/redo02.log
         1 /u01/app/oracle/oradata/wailon/redo01.log
         3 /u01/app/oracle/oradata/wailon/redo03.log

16:50:52 SCOTT@wailon> col name for a50
16:51:02 SCOTT@wailon> select * from v$controlfile;

STATUS  NAME                                               IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
        /u01/app/oracle/oradata/wailon/control01.ctl       NO       16384            596
        /u01/app/oracle/oradata/wailon/control02.ctl       NO       16384            596

-- 模拟系统意外断电,并且控制文件丢失
16:51:17 SYS@wailon> shutdown abort;
ORACLE instance shut down.

16:51:31 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

16:51:38 SYS@wailon> host ls -ls /u01/app/oracle/oradata/wailon/control*
ls: cannot access /u01/app/oracle/oradata/wailon/control*: No such file or directory

-- 尝试启动数据库,找不到控制文件报错
16:51:49 SYS@wailon> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             343936304 bytes
Database Buffers           62914560 bytes
Redo Buffers                8466432 bytes
ORA-00205: error in identifying control file, check alert log for more info

[oracle@dg ~(16:54:08)]$ cd /u01/app/oracle/diag/rdbms/wailon/wailon/trace/
[oracle@dg trace(16:54:40)]$ tail -20 alert_wailon.log
Sun Sep 29 16:51:59 2013
MMNL started with pid=16, OS id=17782
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Sun Sep 29 16:51:59 2013
ALTER DATABASE   MOUNT
ORA-00210: ???????????
ORA-00202: ????: ''/u01/app/oracle/oradata/wailon/control02.ctl''
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: ???????????
ORA-00202: ????: ''/u01/app/oracle/oradata/wailon/control01.ctl''
ORA-27037: ????????
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...
Sun Sep 29 16:52:00 2013
Checker run found 2 new persistent data failures

-- 恢复之前备份的控制文件
[oracle@dg trace(16:54:49)]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Sep 29 16:55:07 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: WAILON (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/WAILON/autobackup/2013_09_29/o1_mf_s_827426866_94ht9mkz_.bkp';

Starting restore at 29-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

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/wailon/control01.ctl
output file name=/u01/app/oracle/oradata/wailon/control02.ctl
Finished restore at 29-SEP-13

-- 查看系统启动状态及日志序号,日志序号已变成旧的
16:56:10 SYS@wailon> select status from v$instance;

STATUS
------------------------
STARTED

16:56:20 SYS@wailon> alter database mount;

Database altered.

16:56:29 SYS@wailon> select group#,sequence#,status from v$log;

GROUP#  SEQUENCE# STATUS
----------   --------------- --------------------------------
         1        244         CURRENT
         3        243         INACTIVE
         2        242         INACTIVE

-- 尝试恢复数据库,提示需要使用BACKUP CONTROLFILE

-- 两种情况需要使用BACKUP CONTROLFILE恢复数据
-- 1. 使用备份的控制文件
-- 2. 使用resetlogs重建控制文件

16:57:47 SYS@wailon> recover database;
ORA-00283: 恢复会话因错误而取消 ORA-01610:
使用 BACKUP CONTROLFILE 选项的恢复必须已完成

-- 提示输入归档日志时,直接按回车键会自动查找相应的归档日志;如果找不到,就从联机日志文件里面找

16:59:13 SYS@wailon> recover database using backup controlfile;
ORA-00279: change 3001398 generated at 09/29/2013 16:49:08 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_245_94htf3fq_.arc
ORA-00280: change 3001398 for thread 1 is in sequence #245


16:59:18 Specify log: {=suggested | filename | AUTO | CANCEL}

-- 找到一个归档日志并应用成功,找另外一个归档日志时没有找到,需要从联机日志中找
ORA-00279: change 3001442 generated at 09/29/2013 16:49:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_246_%u_.arc
ORA-00280: change 3001442 for thread 1 is in sequence #246
ORA-00278: log file '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_245_94htf3fq_.arc' no
longer needed for this recovery


16:59:22 Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: 无法打开归档日志 '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_246_%u_.arc'
ORA-27037: 无法获得文件状态
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

16:59:57 SYS@wailon> recover database using backup controlfile;
ORA-00279: change 3001442 generated at 09/29/2013 16:49:39 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_246_%u_.arc
ORA-00280: change 3001442 for thread 1 is in sequence #246

-- 输入正确的联机日志后,恢复成功
17:00:03 Specify log: {=suggested | filename | AUTO | CANCEL}
 /u01/app/oracle/oradata/wailon/redo03.log
Log applied.
Media recovery complete.

-- 虽然是完全恢复,没有数据丢失,但由于使用了BACKUP CONTROLFILE必须使用OPEN RESETLOGS打开数据库
17:00:05 SYS@wailon> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- 使用OPEN RESETLOGS打开数据库的情况
-- 1. 使用备份的控制文件
-- 2. 不完全恢复(介质恢复)

17:00:21 SYS@wailon> alter database open resetlogs;

Database altered.

-- 使用OPEN RESETLOGS后,联机日志序号重新从1开始编号
17:00:50 SYS@wailon> select group#,sequence#,status from v$log;

GROUP#  SEQUENCE# STATUS
----------   --------------- ------------------------------------------------
         1          1           CURRENT
         3          0           UNUSED
         2          0           UNUSED

-- 验证数据恢复
17:01:50 SYS@wailon> col tname for a30
17:01:58 SYS@wailon> select * from scott.a;

TNAME                          TABTYPE                CLUSTERID
------------------------------ --------------------- ----------
A                              TABLE
BONUS                          TABLE
CHECKPOINT                     TABLE
CHECKPOINT_LOX                 TABLE
DEPT                           TABLE
EMP                            TABLE
GGS_DDL_COLUMNS                TABLE
GGS_DDL_HIST                   TABLE
GGS_DDL_HIST_ALT               TABLE
GGS_DDL_LOG_GROUPS             TABLE
GGS_DDL_OBJECTS                TABLE
GGS_DDL_PARTITIONS             TABLE
GGS_DDL_PRIMARY_KEYS           TABLE
GGS_DDL_RULES                  TABLE
GGS_DDL_RULES_LOG              TABLE
GGS_MARKER                     TABLE
GGS_SETUP                      TABLE
GGS_STICK                      TABLE
GGS_TEMP_COLS                  TABLE
GGS_TEMP_UK                    TABLE
OB                             TABLE
SALGRADE                       TABLE
SYS_EXPORT_SCHEMA_01           TABLE

23 rows selected.

17:01:59 SYS@wailon> select * from scott.b;

TNAME                          TABTYPE                CLUSTERID
------------------------------ --------------------- ----------
A                              TABLE
BONUS                          TABLE
CHECKPOINT                     TABLE
CHECKPOINT_LOX                 TABLE
DEPT                           TABLE
EMP                            TABLE
GGS_DDL_COLUMNS                TABLE
GGS_DDL_HIST                   TABLE
GGS_DDL_HIST_ALT               TABLE
GGS_DDL_LOG_GROUPS             TABLE
GGS_DDL_OBJECTS                TABLE
GGS_DDL_PARTITIONS             TABLE
GGS_DDL_PRIMARY_KEYS           TABLE
GGS_DDL_RULES                  TABLE
GGS_DDL_RULES_LOG              TABLE
GGS_MARKER                     TABLE
GGS_SETUP                      TABLE
GGS_STICK                      TABLE
GGS_TEMP_COLS                  TABLE
GGS_TEMP_UK                    TABLE
OB                             TABLE
SALGRADE                       TABLE
SYS_EXPORT_SCHEMA_01           TABLE

23 rows selected.

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-777152/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290734