测试nologging: 什么时候生效？

--test : When nologging table works

/*version:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
*/

/*Test plan:

1. for noarchive and no force logging database,
(1)for table logging, insert table with append
(2)for table nologging, insert table without append
(3)for table nologging, insert table with append

2. for noarchive and force logging database,
(1)for table logging, insert table with append
(2)for table nologging, insert table without append
(3)for table nologging, insert table with append

3. for archive and no force logging database,
(1)for table logging, insert table with append
(2)for table nologging, insert table without append
(3)for table nologging, insert table with append

4. for archive and force logging database,
(1)for table logging, insert table with append
(2)for table nologging, insert table without append
(3)for table nologging, insert table with append

How to check redo log:

1. check redo size:

create or replace view redo_size
as
select value
  from v$mystat, v$statname
 where v$mystat.statistic# = v$statname.statistic#
   and v$statname.name = 'redo size';

create or replace public synonym redo_size for redo_size;

grant select on redo_size to test;

2. logmnr

*/

/* Conclusion:

db force logging	table logging	insert with append	redo increased	logmnr can find
N	Y	Y	8004	N
N	N	N	155180	Y
N	N	Y	7916	N
Y	Y	Y	139380	N
Y	N	N	155276	Y
Y	N	Y	139424	N
N	Y	Y	139548	N
N	N	N	155208	Y
N	N	Y	7880	N
Y	Y	Y	139428	N
Y	N	N	157024	Y
Y	N	Y	139380	N

1. when insert operation recorded to redo log?
1). if insert is without append, whatever Archive or not, force logging or not, table logging or not, always recorded to redo log.
2). if insert is with append, whatever Archive or not, force logging or not, table logging or not, never recorded to redo log. But it only means we can’t undo the insert using logmnr. Sometimes the inserted data will be recorded to redo and can be transferred to standby. Just reference the following second point.

2. When append can decrease redo generation?
1). if db is force logging, can not decrease
2). if db is no force logging and no archive, append can decrease redo generation
3). if db is no force logging and archive, table is no logging, append can decrease redo
4). if db is no force logging and archive, table is logging, append can not decrease redo

*/

--0. create redo size view and test user(create and insert table, check redo size as test user; check database and logmnr as sys user)

create or replace view redo_size
as
select value
  from v$mystat, v$statname
 where v$mystat.statistic# = v$statname.statistic#
   and v$statname.name = 'redo size';
create or replace public synonym redo_size for redo_size;

create user test identified by test;
grant connect,resource to test;
grant select on dba_objects to test;
grant select on redo_size to test;

--1. for noarchive and no force logging database,
--(1)for table logging, insert table with append, then check redo log

select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
NOARCHIVELOG NO

create table t1_1(id number(9));

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo01.log                          CURRENT

select * from redo_size;

     VALUE
----------
      7504

insert /*+ append */ into t1_1
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
     15508

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo01.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T1_1'
group by username, table_name, operation;

no rows selected.

--(2)for table nologging, insert table without append, then check redo log

create table t1_2(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo02.log                          CURRENT

select * from redo_size;

     VALUE
----------
     23700

insert into t1_2
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    178880

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo02.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    5
UPDATE                                    2
INTERNAL                                 10
UNSUPPORTED                               3
INSERT                                 9490

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T1_2'
group by username, table_name, operation;

USERNAME                       TABLE_NAME
------------------------------ --------------------------------
OPERATION                          COUNT(*)
-------------------------------- ----------
UNKNOWN                        T1_2
INSERT                                 9490

 

--(3)for table nologging, insert table with append, then check redo log

create table t1_3(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo03.log                          CURRENT

select * from redo_size;

     VALUE
----------
    185988

insert /*+ append */ into t1_3
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;
  
     VALUE
----------
    193904

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo03.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T1_3'
group by username, table_name, operation;

no rows selected.

--2. for noarchive and force logging database,
--(1)for table logging, insert table with append, then check redo log

select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
NOARCHIVELOG YES

create table t2_1(id number(9));

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo01.log                          CURRENT

select * from redo_size;

     VALUE
----------
    201188

insert /*+ append */ into t2_1
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    340568

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo01.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T2_1'
group by username, table_name, operation;

now rows selected.

--(2)for table nologging, insert table without append, then check redo log

create table t2_2(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo02.log                          CURRENT

select * from redo_size;

     VALUE
----------
    347720

insert into t2_2
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    502996

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo02.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    5
UPDATE                                    2
INTERNAL                                 10
UNSUPPORTED                               3
INSERT                                 9490

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T2_2'
group by username, table_name, operation;

USERNAME                       TABLE_NAME
------------------------------ --------------------------------
OPERATION                          COUNT(*)
-------------------------------- ----------
UNKNOWN                        T2_2
INSERT                                 9490

--(3)for table nologging, insert table with append, then check redo log

create table t2_3(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo03.log                          CURRENT

select * from redo_size;

     VALUE
----------
    510244

insert /*+ append */ into t2_3
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    649668

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo03.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T2_3'
group by username, table_name, operation;

no rows selected.

--3. for archive and no force logging database,
--(1)for table logging, insert table with append, then check redo log

select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   NO

create table t3_1(id number(9));

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo01.log                          CURRENT

select * from redo_size;

     VALUE
----------
      8540

insert /*+ append */ into t3_1
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    148088

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo01.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T3_1'
group by username, table_name, operation;

no rows selected.

--(2)for table nologging, insert table without append, then check redo log

create table t3_2(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo02.log                          CURRENT

select * from redo_size;

     VALUE
----------
    155452

insert into t3_2
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    310660

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo02.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    5
UPDATE                                    2
INTERNAL                                 10
UNSUPPORTED                               3
INSERT                                 9490

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T3_2'
group by username, table_name, operation;

USERNAME                       TABLE_NAME
------------------------------ --------------------------------
OPERATION                          COUNT(*)
-------------------------------- ----------
UNKNOWN                        T3_2
INSERT                                 9490

 

--(3)for table nologging, insert table with append, then check redo log

create table t3_3(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo03.log                          CURRENT

select * from redo_size;

     VALUE
----------
    317812

insert /*+ append */ into t3_3
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    325692

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo03.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    1
INTERNAL                                 14
UNSUPPORTED                               3

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T3_3'
group by username, table_name, operation;

no rows selected.

--4. for archive and force logging database,
--(1)for table logging, insert table with append, then check redo log

select log_mode,force_logging from v$database;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES

create table t4_1(id number(9));

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo01.log                          CURRENT

select * from redo_size;

     VALUE
----------
    333016

insert /*+ append */ into t4_1
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    472444

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo01.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     4
COMMIT                                    4
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T4_1'
group by username, table_name, operation;

no rows selected.

--(2)for table nologging, insert table without append, then check redo log

create table t4_2(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo02.log                          CURRENT

select * from redo_size;

     VALUE
----------
    479596

insert into t4_2
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    636620

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo02.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     6
COMMIT                                    5
ROLLBACK                                  2
UPDATE                                    2
INTERNAL                                 14
UNSUPPORTED                               3
INSERT                                 9490

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T4_2'
group by username, table_name, operation;

USERNAME                       TABLE_NAME
------------------------------ --------------------------------
OPERATION                          COUNT(*)
-------------------------------- ----------
UNKNOWN                        T4_2
INSERT                                 9490

 

--(3)for table nologging, insert table with append, then check redo log

create table t4_3(id number(9)) nologging;

alter system switch logfile;

select b.member, a.status from v$log a, v$logfile b
where a.group#=b.group#
and a.status='CURRENT';

MEMBER                                                       STATUS
------------------------------------------------------------ ----------------
/opt/oracle/oradata/test/redo03.log                          CURRENT

select * from redo_size;

     VALUE
----------
    651328

insert /*+ append */ into t4_3
select object_id from dba_objects where rownum<=10000;

commit;

select * from redo_size;

     VALUE
----------
    790708

exec dbms_logmnr.add_logfile('/opt/oracle/oradata/test/redo03.log',dbms_logmnr.new);

exec dbms_logmnr.start_logmnr(options=>16);

select operation, count(*) from v$logmnr_contents
group by operation;

OPERATION                          COUNT(*)
-------------------------------- ----------
START                                     7
COMMIT                                    7
UPDATE                                    6
INTERNAL                                 17
UNSUPPORTED                               2
INSERT                                    2

select username, table_name, operation, count(*) from v$logmnr_contents
where table_name='T4_3'
group by username, table_name, operation;

no rows selected.

 

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

转载于:http://blog.itpub.net/8684388/viewspace-620567/