rman只读表空间的备份和恢复

rman只读表空间备份和恢复

当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘),

当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结不再变化.控制文件内也会记录该数据文件的冻结信息。

特点:

1) 减少启动和关闭时间:当表空间设置为只读后,在启动和关闭时就不会对只读表空间对应的数据文件进行处理。从而减少了数据库启动和关闭的时间。

2) 减少备份恢复时间:备份只读表空间后,不需要在对其进行备份,减少了备份量

3)不同分区表放在不同表空间,历史的数据做只读,便于管理

备份只读表空间

方式:

1)exclude排除不需要备份的表空间(需要手动指定,不方便)

2)skip readonly跳过只读表空间(自动跳过)

exclude方式

1创建测试的只读表空间test01

create tablespace TEST1 datafile '/u01/app/oracle/oradata/ORCL/test1.dbf' size 10m;

create table t1(a int) tablespace test1;
insert into t1 select rownum from dual connect by rownum<=10;

alter tablespace test1 read only; 

SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS              STATUS
------------------------------ --------------------- ---------
SYSTEM                         PERMANENT             ONLINE
SYSAUX                         PERMANENT             ONLINE
UNDOTBS1                       UNDO                  ONLINE
TEMP                           TEMPORARY             ONLINE
USERS                          PERMANENT             ONLINE
TEST1                          PERMANENT             READ ONLY

2.备份只读表空间

backup tablespace test1 format '/tmp/test/%d_test1_%s.bk';

RMAN> list backup of database;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
60      Full    1.12M      DISK        00:00:00     14-5月 -22
        BP 关键字: 60   状态: AVAILABLE  已压缩: NO  标记: TAG20220514T103917
段名:/tmp/test/ORCL_test1_80.bk
  备份集 60 中的数据文件列表
  File LV Type Ckp SCN    Ckp 时间 Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ---------- ----------- ------ ----
  15      Full 4202861    14-5月 -22              NO    /u01/app/oracle/oradata/ORCL/test1.dbf

3.备份时排除只读表空间

run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
configure exclude for tablespace test1;
backup as compressed backupset incremental level 0 database format '/tmp/test/%d_%s.bk';
configure exclude for tablespace test1 clear;
backup current controlfile format '/tmp/test/control_%s.bk';
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup archivelog all delete input format '/tmp/test/arch_%s';
release channel ch1;
release channel ch2;
}

skip readonly方式

backup tablespace test1 format '/tmp/test/%d_test1_%s.bk';

备份只读表空间之后

run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup as compressed backupset incremental level 0 database format '/tmp/test/%d_%s.bk' include current controlfile skip readonly;
crosscheck archivelog all;
delete noprompt expired archivelog all;
backup archivelog all delete input format '/tmp/test/arch_%s';
release channel ch1;
release channel ch2;
}

恢复只读表空间

分三种情况:

  1. 备份是read only状态,被破坏时是时read only(只需要recover)
  2. 备份是read only状态,被破坏时是时read write
  3. 备份是read write状态,被破坏时是时read only

备份是read only状态,被破坏时是时read only

只需要resotore即可

--先备份
backup tablespace test1 format '/tmp/test/test1_%s.bk';

--查看对应的检查点信息
SQL> select name,checkpoint_change# from v$datafile;
......
NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4307981
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4307981
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4307981
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4307981
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4202861


--生成一个完全检查点,强行写脏块更新数据文件和控制文件的检查点
SQL> alter system checkpoint;

--查看对比检查点变化情况,只读表空间检查点锁死不变
--同时也侧面证明了cdb的系统操作不会作用pdb的数据文件
SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4308474
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4308474
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4308474
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4308474
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4202861

--删除,模拟破坏数据文件,
rm -f /u01/app/oracle/oradata/ORCL/test1.dbf
SQL> alter system flush buffer_cache;

--数据文件15号不见了
数据文件 15: '/u01/app/oracle/oradata/ORCL/test1.dbf'

--rman恢复

run{
sql'alter database datafile 15 offline';
restore datafile 15;
sql'alter database datafile 15 online';
}

备份是read only状态,被破坏时是时read write

--备份
backup tablespace test1 format '/tmp/test/test1_%s.bk';


--转化为read write
SQL> alter tablespace test1 read write;
SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS              STATUS
------------------------------ --------------------- ---------
SYSTEM                         PERMANENT             ONLINE
SYSAUX                         PERMANENT             ONLINE
UNDOTBS1                       UNDO                  ONLINE
TEMP                           TEMPORARY             ONLINE
USERS                          PERMANENT             ONLINE
TEST1                          PERMANENT             ONLINE

SQL> col table_name for a30;
SQL> select table_name,tablespace_name from user_tables where table_name='T1';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             TEST1


--修改表,同时检查点号改变
SQL> insert into t1 select * from t1;
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> select count(*) from t1;

  COUNT(*)
----------
        20


--删除,模拟破坏数据文件,
rm -f /u01/app/oracle/oradata/ORCL/test1.dbf
SQL> alter system flush buffer_cache;



--rman恢复,可以从告警日志中查看详细动作,利用归档或者没有日志切换就使用redo进行了进行恢复
run{
sql'alter database datafile 15 offline';
restore datafile 15;
recover datafile 15;
sql'alter database datafile 15 online';
}

备份是read write状态,被破坏时是时read only

SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS              STATUS
------------------------------ --------------------- ---------
SYSTEM                         PERMANENT             ONLINE
SYSAUX                         PERMANENT             ONLINE
UNDOTBS1                       UNDO                  ONLINE
TEMP                           TEMPORARY             ONLINE
USERS                          PERMANENT             ONLINE
TEST1                          PERMANENT             ONLINE

--先做备份
backup tablespace test1 format '/tmp/test/test1_%s.bk';

RMAN> list backup of tablespace test1;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
117     Full    1.12M      DISK        00:00:00     15-5月 -22
        BP 关键字: 117   状态: AVAILABLE  已压缩: NO  标记: TAG20220515T110328
段名:/tmp/test/test1_139.bk
  备份集 117 中的数据文件列表
  File LV Type Ckp SCN    Ckp 时间 Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ---------- ----------- ------ ----
  15      Full 4833216    15-5月 -22              NO    /u01/app/oracle/oradata/ORCL/test1.dbf



SQL> insert into t1 select * from t1;
SQL> commit;
SQL> select count(*) from t1;

  COUNT(*)
----------
     40960

--变为read only状态,注意检查点
SQL> select name,checkpoint_change# from v$datafile;
NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4829465
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4829465
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4829465
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4829465
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4833216

SQL> alter tablespace test1 read only;

SQL> select name,checkpoint_change# from v$datafile;
NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4829465
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4829465
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4829465
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4829465
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4833415


--生成完全检查点
SQL> alter system checkpoint;
SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4833517
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4833517
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4833517
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4833517
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4833415

--现在是read only
SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME                CONTENTS              STATUS
------------------------------ --------------------- ---------
SYSTEM                         PERMANENT             ONLINE
SYSAUX                         PERMANENT             ONLINE
UNDOTBS1                       UNDO                  ONLINE
TEMP                           TEMPORARY             ONLINE
USERS                          PERMANENT             ONLINE
TEST1                          PERMANENT             READ ONLY
SQL> select count(*) from t1;

  COUNT(*)
----------
     40960



--删除,模拟破坏数据文件,
rm -f /u01/app/oracle/oradata/ORCL/test1.dbf
SQL> alter system flush buffer_cache;


SQL> select count(*) from t1;

  COUNT(*)
----------
     40960

SQL> insert into t1 select * from t1;
insert into t1 select * from t1
*
第 1 行出现错误:
ORA-00372: 此时无法修改文件 15
ORA-01110: 数据文件 15: '/u01/app/oracle/oradata/ORCL/test1.dbf'


--rman恢复
run{
sql'alter database datafile 15 offline';
restore datafile 15;
recover datafile 15;
sql'alter database datafile 15 online';
}

--对比检查点,没有变化
SQL> select name,checkpoint_change# from v$datafile;

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf                     4833517
/u01/app/oracle/oradata/ORCL/sysaux01.dbf                     4833517
/u01/app/oracle/oradata/ORCL/undotbs01.dbf                    4833517
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf             2159413
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf             2159413
/u01/app/oracle/oradata/ORCL/users01.dbf                      4833517
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf            2159413
/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf             3272499
/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf            3272499
/u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf              3272499
/u01/app/oracle/oradata/ORCL/test1.dbf                        4833415

posted @ 2022-05-15 11:51  EverEternity  阅读(165)  评论(0编辑  收藏  举报