-- 模拟交易产生
22:44:06 SYS@wailon> create table scott.a4 as select * from scott.a;
Table created.
22:45:21 SYS@wailon> alter system switch logfile;
System altered.
22:45:30 SYS@wailon> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 ACTIVE
3 0 UNUSED
2 2 CURRENT
22:45:41 SYS@wailon> insert into scott.a4 select * from scott.a4; -- 此事务未提交
23 rows created.
22:46:07 SYS@wailon> alter system switch logfile;
System altered.
22:46:15 SYS@wailon> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 ACTIVE
3 3 CURRENT
2 2 ACTIVE
22:46:18 SYS@wailon> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- --------------------------------------------- --- ---------- --------------
/u01/app/oracle/oradata/wailon/control01.ctl NO 16384 614
/u01/app/oracle/oradata/wailon/control02.ctl NO 16384 614
-- 插入a4表的23行未提交,数据库意外断电,并且控制文件丢失
22:47:06 SYS@wailon> shutdown abort;
ORACLE instance shut down.
22:47:14 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl
22:47:28 SYS@wailon> host ls /u01/app/oracle/oradata/wailon/control*.ctl
ls: cannot access /u01/app/oracle/oradata/wailon/control*.ctl: No such file or directory
-- 启动数据库到NOMOUNT状态
22:47:32 SYS@wailon> startup nomount;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 360713520 bytes
Database Buffers 46137344 bytes
Redo Buffers 8466432 bytes
-- 使用RESETLOGS创建控制文件
22:47:41 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
22:47:45 2 MAXLOGFILES 16
22:47:45 3 MAXLOGMEMBERS 3
22:47:45 4 MAXDATAFILES 100
22:47:45 5 MAXINSTANCES 8
22:47:45 6 MAXLOGHISTORY 292
22:47:45 7 LOGFILE
22:47:45 8 GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/system01.dbf',
'/u01/app/oracle/oradata/sysaux01.dbf',
'/u01/app/oracle/oradata/undotbs01.dbf',
'/u01/app/oracle/oradata/users01.dbf',
'/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
CHARACTER SET ZHS16GBK
22:47:46 19 ;
Control file created.
-- 尝试加载数据库,创建控制文件后已经自动加载
22:47:51 SYS@wailon> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
-- 打开数据库,提示需要恢复
22:47:58 SYS@wailon> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf'
-- 使用USING BACKUP CONTROLFILE恢复,由于有活动事务未提交,需要指定联机日志执行不完全恢复
22:48:16 SYS@wailon> recover database using backup controlfile;
ORA-00279: change 3028602 generated at 09/29/2013 22:25:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_94jh8b2c_.arc
ORA-00280: change 3028602 for thread 1 is in sequence #1
22:48:24 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo01.log
ORA-00279: change 3030164 generated at 09/29/2013 22:45:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_2_94jh9qxy_.arc
ORA-00280: change 3030164 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo01.log' no longer needed for this recovery
22:48:58 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo02.log
ORA-00279: change 3030195 generated at 09/29/2013 22:46:15 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_3_%u_.arc
ORA-00280: change 3030195 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo02.log' no longer needed for this recovery
22:49:08 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo03.log
Log applied.
Media recovery complete.
-- 恢复完成后,使用OPEN RESETLOGS打开数据库
22:49:12 SYS@wailon> alter database open resetlogs;
Database altered.
-- 未提交的数据已经丢失
22:52:59 SYS@wailon> select count(*) from scott.a4;
COUNT(*)
----------
23
-- 添加临时表空间
22:41:02 SYS@wailon> select * from v$tempfile;
no rows selected
22:42:00 SYS@wailon> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP01
WAILON
6 rows selected.
22:42:33 SYS@wailon> select name from v$datafile;
NAME
---------------------------------------------
/u01/app/oracle/oradata/system01.dbf
/u01/app/oracle/oradata/sysaux01.dbf
/u01/app/oracle/oradata/undotbs01.dbf
/u01/app/oracle/oradata/users01.dbf
/u01/app/oracle/oradata/wailon/WAILON/datafil
e/o1_mf_wailon_94g6p2k8_.dbf
22:43:46 SYS@wailon> alter tablespace temp01 add tempfile '/u01/app/oracle/oradata/wailon/temp01.dbf' size 10m reuse;
Tablespace altered.
22:43:57 SYS@wailon> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIM TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------
BLOCK_SIZE NAME
---------- ---------------------------------------------
1 3030098 29-SEP-13 7 1 ONLINE READ WRITE 10485760 1280 10485760
8192 /u01/app/oracle/oradata/wailon/temp01.dbf
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-777275/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-777275/