[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

總結:
實際上只要概念清楚,以上的問題還是很好解決的.

[20171204]guaranteed restore point.txt

相關文章