-- 备份控制文件,备份系统表空间会自动备份控制文件
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-777152/