第6天-3 RMAN不完全恢复】【用scn恢复到特定的时刻】【控制文件损坏】【损坏日志文件状态非INACTIVE时,数据库打不开】【增加联机日志文件】【修改所有联机日志大小同义10M】
来源:互联网 发布:伺服电机怎么编程控制 编辑:程序博客网 时间:2024/04/30 08:23
--6-3 RMAN不完全恢复select * from dba_tables where tablespace_name='USERS';select * from dba_tablespaces;select * from sys.t--误删除一张表,恢复到表存在的时刻--用scn恢复到特定的时刻S1.SQL:startup mount;S2.RMAN> restore database;S3.SQL> recover database until change 1914122; S4.SQL:alter database open resetlogs;SQL> select current_scn from v$database;CURRENT_SCN----------- 1914122drop table sys.tcreate table t2 (int id) ;insert into t2 values(1);startup mount;RMAN> restore database;SQL> select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 1915802 2 1915802 3 1915802 4 1915802 5 1915802 6 1915802 7 1915802 8 1915802 9 1915802 10 1915802 11 1915802已选择11行。SQL> select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#---------- ------------------ 1 1912654 2 1912654 3 1912654 4 1912654 5 1912654 6 1912654 7 1912654 8 1912654 9 1912654 10 1912654 11 1912654SQL> recovery database until change 1914122; --sql下关键字是change,在rman下是scn,因为change是rman中的一条命令select sequence#,first_change#,next_change# from v$archived_log where first_change#<1914122 and next_change#>1914122;--控制文件损坏select * from v$logfile;11ONLINEC:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_954OTLCM_.LOGYES22ONLINEC:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_954QNYS9_.LOGYES34ONLINEC:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_4_954OZ7PV_.LOGYES43ONLINEC:\ORADATA\TEST\AUTOLOG\REDO03.LOGNOSQL> recover database until change 1914122; -- until time ,untill sequenceSQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- 1 1 2 104857600 1 NO CURRENT 1918102 27-10月-13 4 1 1 104857600 1 YES INACTIVE 1916959 27-10月-13 3 1 0 52428800 1 YES UNUSED 0 2 1 0 104857600 1 YES UNUSED 0--当损坏日志文件状态非INACTIVE时,数据库打不开,S1. startup mount ;S2. SQL> alter database drop logfile group 4;S3. alter database open;--增加联机日志文件SQL> alter database add logfile group 4 'C:\oradata\test\redo04.log' size 50m;--建议每个日志大小都相同--修改所有联机日志大小同义10MSQL> alter database add logfile 'C:\oradata\test\redo04.log' size 50m;SQL> alter database add logfile 'C:\oradata\test\redo05.log' size 50m;SQL> alter database add logfile 'C:\oradata\test\redo06.log' size 50m;SQL> alter system switch logfile;SQL> alter system switch logfile;SQL> alter system switch logfile;SQL> select * from v$log;SQL> alter system checkpoint;SQL> alter database drop logfile group 1;