Time goes on and on , never to an end but crossings(时间一直走,没有尽头,只有路口)--------------克莱尔.麦克福
Oracle数据库也是一个脾气很大的主子,发起脾气来,后果很严重。所以我们需要摸清楚他的习性,安抚他的脾气,解决问题。
(1)
----------------spfile丢失---------------
startup nomount;
set dbid 3988862108;
restore spfile from autobackup;
---或者通过某个文件-------
restore spfile from '/path/file_name';
shutdown immediate;
set dbid 3988862108;
startup
(2)
--------------控制文件丢失---------------
RMAN>set dbid 3988862108;
RMAN>startup nomount;
RMAN>restore controlfile from autobackup;
-----或者-----------
RMAN>restore controlfile from 'file_name';
RMAN>alter database mount;
RMAN>recover database;
RMAN>alter database open resetlogs;
(3)
-----------UNDO损坏-----------
----method 一-----------------
----1,用spfile创建pfile,然后修改参数-----------
#*.undo_tablespace='UNDOTBS1'
#*.undo_management='AUTO'
#*.undo_retention
undo_management='MANUAL'
rollback_segments='SYSTEM'
----2,用修改的pfile重启DB--------
SQL>startup mount pfile='F:\initorcl.ora';
----3,删除原来的表空间,创建新的UNDO表空间--------
SQL>drop tablespace untotbs1;
SQL>create undo tablespace undotbs1 datafile '/u01/oradata/undotbs1.dbf' size 10M;
----4,关闭数据库,修改pfile参数,然后用新的pfile创建spfile,再正常启动数据库。
*.undo_tablespace='UNDOTBS1'
*.undo_management='AUTO'
#undo_management='MANUAL'
#rollback_segments='SYSTEM'
---method 二,跳过损坏的segment------------
---1,修改pfile,添加参数------------------
*._corrupted_rollback_segments='_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
---这些字段的值,我们通过alert log查看,也可以通过如下命令查看-----
strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
---2,用修改之后的pfile启动DB--------------
---3,创建新的undo表空间,并切换过来-------
SQL>create undo tablespace undotbs2 datafile '/u01/oradata/undotbs2.dbf' size 10M;
SQL>alter system set undo_tablespace=undotbs2;
SQL>drop tablespace undotbs1;
---4,修改pfile,创建spfile,并正常启动----------
#*._corrupted_rollback_segments='_SYSSMU11$','_SYSSMU12$','_SYSSMU13$'
(4)
----------Redo Log File损坏--------------
---------current损坏情况-----------------
---有归档和备份----------------
SQL>startup mount;
SQL>recover database until cancel;
---先选择auto,尽量恢复可以利用的归档日志,然后重新执行---------
SQL>recover database until cancel;
---这次输入cancel,完成不完全恢复-------------------------------
SQL>alter database open resetlogs;
---------强制恢复---------------
SQL>startup mount;
SQL>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL>recover database until cancel;
SQL>alter database open resetlogs;
---逻辑导出数据,重建实例-----------
----------非current情况---------------------
----如果status是inactive-------------------
SQL>startup mount;
SQL>alter database clear logfile group 3;
SQL>alter database open;
----如果status是active,表示正在归档--------
SQL>startup mount;
SQL>alter database clear unarchived logfile group 3;
SQL>alter database open;
(5)
----------非系统表空间损坏----------------
----可以在open或mount状态下--------------
---1,将该表空间置于offline状态----------
---2,修复表空间数据---------------------
---3,恢复表空间并处于一致性-------------
---4,将表空间online---------------------
RMAN>sql 'alter tablespace dave offline';
RMAN>sql 'alter tablespace dave offline immediate';
RMAN>restore tablespace dave;
RMAN>recovery tablespace dave;
RMAN>sql 'alter tablespace dave online';
----------数据文件损坏---------------
RMAN>sql 'alter datafile 8 offline';
RMAN>restore datafile 8;
RMAN>recovery datafile 8;
RMAN>sql 'alter datafile 8 online';
(6)
---------基于时间点/SCN/日志序列的不完全恢复-----------
---基于时间点--------
run{
set until time "to_date('2016-07-08 12:00:00','yyyy-mm-dd hh24:mi:ss');
restore database;
recover database;
alter database open resetlogs;
}
SQL>startup nomount;
SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL>recover database until time '2016-07-08 12:00:00';
SQL>alter database open resetlogs;
---或者------------
SQL>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL>startup mount;
SQL>restore database until time "to_date('2010-12-29 23:19:00','YYYY-MM-DD HH24:MI:SS')";
SQL>recover database until time "to_date('2010-12-29 23:19:00','YYYY-MM-DD HH24:MI:SS')";
SQL>alter database open resetlogs;
---------基于SCN---------------------
SQL>startup mount;
SQL>restore database until scn 10000;
SQL>recover database until scn 10000;
SQL>alter database open resetlogs;
---------基于日志序列----------------
SQL>startup mount;
SQL>restore database until SEQUENCE 100 thread 1;
SQL>restore database until SEQUENCE 100 thread 1;
SQL>alter database open resetlogs;
---------非catalog下完全恢复----------
SQL>startup nomount;
SQL>restore controlfile from autobackup;
SQL>alter database mount;
SQL>restore database;
SQL>recover database;
SQL>alter database open resetlogs;
喜欢的朋友可以扫描以下二维码进行关注,公众号将每天更新文章: