[20171204]guaranteed restore point.txt

[20171204]guaranteed restore point.txt

--//昨天帮别人升级再次遇到关于相关问题,实际上主要问题在于升级文档没有完成后取消restore point的设置.
--//比如我以前写的做dg的文档没有 alter database  force logging;这个步骤,我包括我同事做dg经常忘记这个步骤.
--//从另外一个方面也说明,安装文档很重要,包括更改与更新文档的工作.
--//自己也在测试环境做一个记录与总结:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立guarantee restore point:

CREATE TABLESPACE T01 DATAFILE '/mnt/ramdisk/book/T01.dbf' SIZE 11M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT
LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

SYS@book> create restore point book1204 guarantee flashback database;
Restore point created.

3.测试1:
--//无法删除表空间以及数据文件:

SCOTT@book> drop tablespace t01 including contents and datafiles;
drop tablespace t01 including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace T01 on primary database due to guaranteed restore points.

4.测试2:
--//测试建立新表空间与数据文件.
SCOTT@book> CREATE TABLESPACE T06 DATAFILE '/mnt/ramdisk/book/T06.dbf' SIZE 11M AUTOEXTEND OFF LOGGING ONLINE EXTENT
            MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
Tablespace created.

--//建立新的表空间与数据文件ok.shrink也可以.
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T06.dbf' RESIZE 10M ;
Database altered.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T06.dbf' RESIZE 8M ;
Database altered.

--//如果使用一些数据块后呢?
SCOTT@book> create table t06 tablespace t06 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> truncate table t06 ;
Table truncated.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T06.dbf' RESIZE 5M ;
Database altered.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T06.dbf' RESIZE 2M ;
Database altered.
--//OK,只要在guaranteed restore points建立的数据文件,shrink是没有问题的.删除在guaranteed restore points后建立的表空间看看:

SCOTT@book> drop table t06 purge ;
Table dropped.

SCOTT@book> drop tablespace t06 including contents and datafiles;
drop tablespace t06 including contents and datafiles
*
ERROR at line 1:
ORA-38881: Cannot drop tablespace T06 on primary database due to guaranteed restore points.

--//无法删除.

5.测试3:
--//测试shrink已经存在的数据文件呢?
SCOTT@book> create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=2e5;
Table created.

SCOTT@book> truncate table t01 ;
Table truncated.

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where file_id=7;
OWNER  SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T01                  TABLE              T01                     0          7        128      65536          8            7

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 8M ;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 8M
*
ERROR at line 1:
ORA-38883: Cannot shrink data file /mnt/ramdisk/book/T01.dbf on primary database due to guaranteed restore points.

--//因为那些块已经使用,在建立guaranteed restore points后无法shrink.继续测试数据文件增大在回收呢?

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 12M ;
Database altered.

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 11M ;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/T01.dbf' RESIZE 11M
*
ERROR at line 1:
ORA-38883: Cannot shrink data file /mnt/ramdisk/book/T01.dbf on primary database due to guaranteed restore points.

--//增加可以,再回收not way..

--//实际上主要理解guaranteed restore points的内在原理,上面的操作遇到的问题就很好理解.

6.备份变大.
--//这个问题以前已经遇到过.再做1次.
SCOTT@book> truncate table t01 ;
Table truncated.

SCOTT@book> select * from dba_extents where file_id=7;
OWNER  SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  T01                  TABLE              T01                                     0          7        128      65536          8            7

--//T01表空间占用空间现在仅仅64K,加上os,文件,位图区1M,看看备份大小.
RMAN> backup  datafile 7 format '/home/oracle/backup/T01_%U' ;

Starting backup at 2017-12-04 09:10:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=119 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/mnt/ramdisk/book/T01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-12-04 09:10:26
channel ORA_DISK_1: finished piece 1 at 2017-12-04 09:10:27
piece handle=/home/oracle/backup/T01_gbsl8uo2_1_1 tag=TAG20171204T091026 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-04 09:10:27

Starting Control File and SPFILE Autobackup at 2017-12-04 09:10:27
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_12_04/o1_mf_s_961837827_f29843yf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-12-04 09:10:28

$ ls -lh /home/oracle/backup/T01_gbsl8uo2_1_1
-rw-r----- 1 oracle oinstall 11M 2017-12-04 09:10:26 /home/oracle/backup/T01_gbsl8uo2_1_1
--//文件大小11M.
--//删除restore point,以上问题就不存在.

SCOTT@book> set numw 12
SCOTT@book> select * from V$RESTORE_POINT;
         SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                          RESTORE_POINT_TIME PRE NAME
------------ --------------------- --- ------------ ----------------------------- ------------------ --- --------------------
13278252070                     2 YES     52428800 2017-12-04 08:49:49.000000000                    YES BOOK1204

SYS@book> drop restore point book1204;
Restore point dropped.

RMAN> backup  datafile 7 format '/home/oracle/backup/T01x_%U' ;
Starting backup at 2017-12-04 09:14:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/mnt/ramdisk/book/T01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-12-04 09:14:57
channel ORA_DISK_1: finished piece 1 at 2017-12-04 09:14:58
piece handle=/home/oracle/backup/T01x_gdsl8v0h_1_1 tag=TAG20171204T091457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-04 09:14:58

$ ls -lh /home/oracle/backup/T01*
-rw-r----- 1 oracle oinstall  11M 2017-12-04 09:10:26 /home/oracle/backup/T01_gbsl8uo2_1_1
-rw-r----- 1 oracle oinstall 1.6M 2017-12-04 09:14:57 /home/oracle/backup/T01x_gdsl8v0h_1_1

总结:
实际上只要概念清楚,以上的问题还是很好解决的.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2148230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2148230/