当前位置: 首页 > DG > 正文

DG环境中表的nologging测试

        下面的测试一下在DG环境中没有开启FORCE LOGGING模式下使用NOLOGGING修改数据对数据库的影响与怎么去查看那些文件上

面的对象有发上NOLOGGING操作。

1,环境介绍

www.htz.pw > select * from v$version where rownum<3;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production

www.htz.pw > !lsb_release -a
LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch
Distributor ID: RedHatEnterpriseAS
Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Release:        4
Codename:       NahantUpdate8

这里使用了10.2.0.1环境的DG,本来想使用9I的,结果发现没有RHEL 4的环境哦。

2,搭建DG环境

这里没有配置官方要求的DG参数,值配置了归档路径参数来配置DG环境,生产环境请按官方文档要求配置。

www.htz.pw > select name,display_value from v$parameter where isdefault='FALSE';

                                         Display
NAME                                     Value
---------------------------------------- ----------------------------------------------------------------------------------------------------
processes                                150
sga_target                               1152M
control_files                            /oracle/app/oracle/oradata/orcl1021/control01.ctl, /oracle/app/oracle/oradata/orcl1021/control02.ctl
                                         , /oracle/app/oracle/oradata/orcl1021/control03.ctl

db_block_size                            8192
compatible                               10.2.0.1.0
log_archive_format                       %t_%s_%r.dbf
db_file_multiblock_read_count            16
db_recovery_file_dest                    /oracle/app/oracle/flash_recovery_area
db_recovery_file_dest_size               2G
undo_management                          AUTO
undo_tablespace                          UNDOTBS1
remote_login_passwordfile                EXCLUSIVE
db_domain
dispatchers                              (PROTOCOL=TCP) (SERVICE=orcl1021XDB)
job_queue_processes                      10
background_dump_dest                     /oracle/app/oracle/admin/orcl1021/bdump
user_dump_dest                           /oracle/app/oracle/admin/orcl1021/udump
core_dump_dest                           /oracle/app/oracle/admin/orcl1021/cdump
audit_file_dest                          /oracle/app/oracle/admin/orcl1021/adump
db_name                                  orcl1021
open_cursors                             300
pga_aggregate_target                     384M

www.htz.pw > show parameter db_unique_name;

NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
db_unique_name                       string          orcl1021

www.htz.pw > show parameter log_archive_config

NAME                                 TYPE            VALUE
------------------------------------ --------------- ------------------------------
log_archive_config                   string

配置tnsnames.ora
配置日志传输路径

www.htz.pw > select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

www.htz.pw > alter system set log_archive_dest_1='location=/tmp';

System altered.

www.htz.pw > alter system switch logfile;

System altered.

www.htz.pw > select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/tmp/1_2_877904211.dbf

www.htz.pw > alter system set log_archive_dest_2='SERVICE=orcl1021dg LGWR ASYNC reopen=60';

System altered.

下面就是备库实例的搭建,这里忽略。

www.htz.pw > select open_mode,database_role from v$database;

OPEN_MODE  DATABASE_ROLE

———- —————-

MOUNTED    PHYSICAL STANDBY

3,测试NOLOGGING属性

DG环境中,我们需要了解v$datafile中的下面几个列的属性。

UNRECOVERABLE_CHANGE#

Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOGmode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.

UNRECOVERABLE_TIME

Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

FIRST_NONLOGGED_SCN

First nonlogged SCN

FIRST_NONLOGGED_TIME

First nonlogged time

前面两个列用于主库环境,后面两个列用于备库环境,并且在10G以后才有的。

www.htz.pw > select force_logging from v$database;

FOR
---
NO

3.1 CREATE TABLE NOLOGGIN

www.htz.pw > create table scott.htz2 nologging as select * from dba_objects;

Table created.

这里我们看到了出现了值
www.htz.pw > select UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
--------------------- -------------------
                    0                   0
                    0                   0
                    0                   0
               538013                   0

马上在备库查看可以发现下面的值
www.htz.pw >  select UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
--------------------- -------------------
                    0                   0
                    0                   0
                    0                   0
                    0              537926

下面我们将备库以READ ONLY打开

www.htz.pw > recover managed standby database cancel;
Media recovery complete.

www.htz.pw > alter database open read only;

Database altered.

www.htz.pw > SELECT count(*)
FROM scott.htz;

  COUNT(*)
----------
     49745

主库
www.htz.pw > select count(*) from scott.htz;

  COUNT(*)
----------
     49745

这里看到创建表是指定了nologging选项,v$datafile中的unrecoverable_chang#也发生了变换,但是行的数据并没有丢失,按理说这里的行的数据就应该丢失,也说明,行的日志还是写了的,不然备库不会有行的记录。

3.2 DML NOLOGGING选项

我记得DML是没有NOLOGGING的提示的,如果在DMLNOLOGGING提示,ORACLE也不会报错,但是是没有任何效果的。

www.htz.pw > delete scott.htz2 where rownum<10000;

9999 rows deleted.

www.htz.pw > commit;

Commit complete.

www.htz.pw > select count(*) from scott.htz2;

  COUNT(*)
----------
     39747

www.htz.pw >  select UNRECOVERABLE_CHANGE#,FIRST_NONLOGGED_SCN from v$datafile;

UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
--------------------- -------------------
                    0                   0
                    0                   0
                    0                   0
               538013                   0

切日志,但是这里有个问题,这个unrecoverable_change#没有发生变化,按文档说明这里是会发生变化的,但是DG备库切出现坏块的提示。我们使用ctas nologging在创建一张表却是发生变化的,append插入表也是发生了变化。这里我们可以使用insert append的方式来测试,关于update在logging/nologging下生成的日志量我们将在后面详细测试。

下面看看备库
UNRECOVERABLE_CHANGE# FIRST_NONLOGGED_SCN
--------------------- -------------------
                    0                   0
                    0                   0
                    0                   0
                    0              537926

以只读模式打开

www.htz.pw > SELECT count(*)
FROM scott.htz2;
select count(*) from scott.htz2
                           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 188)
ORA-01110: data file 4: '/oracle/app/oracle/oradata/orcl1021/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

可以使用数据文件的增量备份来处理上面的报错的信息。

这里需要注意的如果主库的unrecoverable_change#大于备库的unrecoverable_change#时,我们就需要注意了。 如果unrecoverable_change#的值大于DG搭建是的scn值,我们也需要注意了。

本文固定链接: http://www.htz.pw/2015/04/25/dg%e7%8e%af%e5%a2%83%e4%b8%ad%e8%a1%a8%e7%9a%84nologging%e6%b5%8b%e8%af%95.html | 认真就输

该日志由 huangtingzhong 于2015年04月25日发表在 DG 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: DG环境中表的nologging测试 | 认真就输
关键字: ,