第6天-3 RMAN不完全恢复】【用scn恢复到特定的时刻】【控制文件损坏】【损坏日志文件状态非INACTIVE时，数据库打不开】【增加联机日志文件】【修改所有联机日志大小同义10M】

最新推荐文章于 2021-04-12 10:57:58 发布

闭眼忘世界

最新推荐文章于 2021-04-12 10:57:58 发布

阅读量1k

点赞数

分类专栏： warehouse 10g视频笔记

本文链接：https://blog.csdn.net/hughwang1216/article/details/14112177

版权

warehouse 10g视频笔记专栏收录该内容

33 篇文章 0 订阅

订阅专栏

--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
-----------
    1914122

drop table sys.t
create 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            1912654

SQL> 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;
1	1		ONLINE	C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_954OTLCM_.LOG	YES
2	2		ONLINE	C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_954QNYS9_.LOG	YES
3	4		ONLINE	C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_4_954OZ7PV_.LOG	YES
4	3		ONLINE	C:\ORADATA\TEST\AUTOLOG\REDO03.LOG	NO

SQL> recover database until change 1914122; -- until time ,untill sequence


SQL> 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;--建议每个日志大小都相同

--修改所有联机日志大小同义10M
SQL> 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;