oracle归档日志及补充日志存储占用测试

一、归档模式(不开启补充日志)
1.将日志模式切换为手工归档模式
ALTER DATABASE ARCHIVELOG MANUAL;
2.打开数据库
ALTER DATABASE OPEN;
3.对所有的日志进行归档
ALTER SYSTEM ARCHIVE LOG ALL;
4.删除所有归档日志
rman target /
delete archivelog until time ‘sysdate+3’;
5.数据库增删改操作
CREATE TABLE CHECK_5W(
ID NUMBER,
CREATE_TIME VARCHAR2(32),
RANDOM_ID NUMBER,
RANDOM_STRING VARCHAR2(32),
CREATE_PRE VARCHAR2(32));

INSERT INTO CHECK_5W
SELECT ROWNUM AS ID,
TO_CHAR(SYSDATE + ROWNUM/24/3600, ‘yyyy-mm-dd hh24:mi:ss’) AS INC_DATETIME,
TRUNC(DBMS_RANDOM.VALUE(0, 100)) AS RANDOM_ID,
DBMS_RANDOM.STRING(‘x’, 20) RANDOM_STRING,
‘批量插入’ AS CREATE_PRE
FROM DUAL
CONNECT BY LEVEL <= 50000;
COMMIT;

UPDATE CHECK_5W T SET T.CREATE_TIME=SYSDATE WHERE T.CREATE_PRE=‘批量插入’;
COMMIT;

DELETE CHECK_5W WHERE ID>=100;
COMMIT;

6.对所有的日志进行归档
ALTER SYSTEM ARCHIVE LOG ALL;
7.查看归档日志空间占用(记录下) 147.47M
SELECT ROUND(T.SPACE_USED/1024/1024,2)||‘M’ SPACE_USED FROM V$RECOVERY_FILE_DEST T;

二、归档模式(开启补充日志)alter database add supplemental log data;
1.对所有的日志进行归档
DROP TABLE CHECK_5W PURGE;
ALTER SYSTEM ARCHIVE LOG ALL;
2.删除所有归档日志
rman target /
delete archivelog until time ‘sysdate+3’;
3.数据库增删改操作
CREATE TABLE CHECK_5W(
ID NUMBER,
CREATE_TIME VARCHAR2(32),
RANDOM_ID NUMBER,
RANDOM_STRING VARCHAR2(32),
CREATE_PRE VARCHAR2(32));

INSERT INTO CHECK_5W
SELECT ROWNUM AS ID,
TO_CHAR(SYSDATE + ROWNUM/24/3600, ‘yyyy-mm-dd hh24:mi:ss’) AS INC_DATETIME,
TRUNC(DBMS_RANDOM.VALUE(0, 100)) AS RANDOM_ID,
DBMS_RANDOM.STRING(‘x’, 20) RANDOM_STRING,
‘批量插入’ AS CREATE_PRE
FROM DUAL
CONNECT BY LEVEL <= 50000;
COMMIT;

UPDATE CHECK_5W T SET T.CREATE_TIME=SYSDATE WHERE T.CREATE_PRE=‘批量插入’;
COMMIT;

DELETE CHECK_5W WHERE ID>=100;
COMMIT;

4.对当前的日志进行归档
ALTER SYSTEM ARCHIVE LOG CURRENT;
5.查看归档日志空间占用(记录下)，和未开启补充日志时所占空间对比
SELECT ROUND(T.SPACE_USED/1024/1024,2)||‘M’ SPACE_USED FROM V$RECOVERY_FILE_DEST T; --301.58M 352.79M 356.26M 401.09M