10g Logical Standby的建立，优化及管理 - 更新

最新推荐文章于 2024-10-06 21:49:48 发布

cuixie2370

最新推荐文章于 2024-10-06 21:49:48 发布

阅读量106

点赞数

文章标签：数据库网络内存管理

一 . 前期检查工作

主库上操作：
SELECT NAME,CREATED,LOG_MODE,OPEN_MODE,DATABASE_ROLE,FORCE_LOGGING FROM V$DATABASE; 确认数据库处于自动归档模式，如果不是，修改为自动归档模式。

执行此句 ALTER DATABASE FORCE LOGGING;
备注： ALTER DATABASE NO FORCE LOGGING; 在所有建立Logical Standby工作完成后取消force logging .

另外，在创建逻辑备库时确保在主数据库中做配置使用的账号有以下数据库角色权限：
a、logstdby_administrator 角色，用来使用逻辑备用功能
b、select_catalog_role 角色，能够访问所有数据字典视图。
这里我们选用sys 来进行操作。

我们需要通过查询主库中视图DBA_LOGSTDBY_UNSUPPORTED 来确定主数据库中是否含有不支持的对象 (如果有，可能需要做一些调整才能继续) ：
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
该视图显示包含不被逻辑STANDBY支持的数据类型的表的列名及该列的数据类型.

SELECT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER,TABLE_NAME) NOT IN (SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y';
该语句在主库中检查SQL应用能否唯一识别表列,找出不被支持的表.

确定在主数据库上，补充日志是否被启用，可以查询v$database，如下：
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database;
SUP SUP
‐‐‐
YES YES

如果主库中存在不符合要求的table, 补充日志没有被启用, 且确实没有办法加入PK 或非空唯一索引，那么在Primary DB上启用补充日志：
SQL> alter database add supplemental log data(primary key,unique index)
columns;

在主库上创建一个新的表空间，用于LogMiner，否则Logical Standby 需要的对象
将默认创建在SYSTEM 表空间中(这是比较危险的)。( 这一步在ORACLE 文档中似乎没有提到，这里是为了不影响system 表空间而引入的 ) 。
SQL> CREATE TABLESPACE logmnrts DATAFILE
'/data/mxdell/logmnrts01.DBF' SIZE 2000M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 10M ;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');
如果执行报错，可能需要增大logmnrts 表空间大小。

SQL>show parameter LOG_ARCHIVE_LOCAL_FIRST; 确认使用默认的本地先归档(true)

SQL>alter system set archive_lag_target=600 scope=both;
设定主库强制10分钟自动归档一次 (备注：这里设置这个参数是因为我们使用归档传输ARCH方式及最大性能模式，为了使Logical Standby上数据和Primary DB上只相差10分钟以内；如果不是此种需要，可以不用设置)

由于后续的BMS_LOGSTDBY.BUILD这个过程会通过闪回查询的方式来获取数据字典的一致性，因此oracle 初始化参数UNDO_RETENTION 值需要设置的足够大。
Oracle recommends setting the UNDO_RETENTION initialization parameter to 3600 on both the primary and logical standby databases ,Oracle10g 默认undo_retention为900，这里设置为3600，同样Standby上的参数也需要设置为3600。
SQL>alter system set undo_retention=3600 scope=both;

SELECT * FROM DBA_LOGSTDBY_SKIP;
查看逻辑Standby的过滤操作；对于一些系统schema，逻辑备库默认是忽略其实际变更的。逻辑备库和主库只是逻辑意义上也就是用户数据保持一致，元数据自然是不应该复制的，否则就乱套了，所以不要在系统schema上建立任何用户自己的数据表等。
SQL> select owner,name from DBA_LOGSTDBY_SKIP;

OWNER                          NAME
------------------------------ ------------------------------
SYSTEM                         %
SYS                            %
DIP                            %
OUTLN                          %

二. 在Standby Server上建立物理STANDBY

这里Physical Standby 中DB_NAME及DB_UNIQUE_NAME均为MXDELL 。spfile 是直接
拷贝主库上的参数文件，并根据具体内存修改。这里假设两台Server硬体配置一样。

热备份或RMAN建立物理Standby
Primary DB上参数修改：
*.db_name='mxdell'
*.db_unique_name='mxdell'
*.log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxdell'
*.log_archive_dest_2='service=standby valid_for=(online_logfiles,primary_role) db_unique_name=mxstandby'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'

备注：按照Oracle10g 物理Standby标准文档，建立物理Standby过程中主库中的参数还有一些用于主库备库互相切换的参数比如fal_server,fal_client等，这里不做切换打算，暂时不设置。详情参考：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm

Physical Standby上参数修改：
*.db_name='mxdell'
*.db_unique_name='mxstandby'
*.log_archive_dest_1='location=/data/mxdell/arch valid_for=(all_logfiles,all_roles) db_unique_name=mxstandby'
*.log_archive_dest_2='service=mxdell valid_for=(online_logfiles,primary_role) db_unique_name=mxdell'    -- 可以不用设置，主要用于主库备库切换
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.standby_archive_dest='/data/mxdell/arch'   -- 接收来自主库的Log用于应用SQL，最好设置与standby上自己的归档路径log_archive_dest_1不一致。
*.log_archive_min_succeed_dest=1

*.db_file_name_convert='/data/mxdell','/data/mxdell'
*.log_file_name_convert='/u01/product/oradata/mxdell','/u01/product/oradata/mxdell'
*.standby_file_management='AUTO'

*.fal_server='mxdell'
*.fal_client='standby'

Primary DB 和Physical Standby 的tnsnames.ora都加入如下两设置：
其中8.114是主库IP,130.189是物理备库IP
MXDELL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.161.8.114)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mxdell)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.134.130.189)(PORT = 1526))
    (CONNECT_DATA =
      (SERVICE_NAME = mxdell)
      (INSTANCE_NAME = mxdell)       # 可以不需要
    )
)

Primary DB 和Physical Standby 上listener.ora如下(都一样)，并开启两台机器的监听：

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/product/oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = mxdell)
      (ORACLE_HOME = /u01/product/oracle)
      (GLOABAL_DBNAME = mxdell)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MXPSVDFMSNDB)(PORT = 1526))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

开启主库。
物理备库上操作：
SQL> startup nomount
SQL> alter database mount standby database ;
SQL> alter database recover managed standby database disconnect from session ;
测试物理Standby的恢复同步情况 (略) 。

四 . 准备将物理Standby切换为逻辑STANDBY

在主库上执行： SQL>EXECUTE DBMS_LOGSTDBY.BUILD;

提示：
Ø 该过程会自动启用primary 数据库的补充日志(supplemental logging)功能(如果未启用的话)。
Ø 该过程执行需要等待当前所有事务完成，因此如果当前有较长的事务运行，可能该过程
执行也需要多花一些等待时间。
Ø 该过程是通过闪回查询的方式来获取数据字典的一致性，因此oracle 初始化参数
UNDO_RETENTION 值需要设置的足够大。

切换物理Standby为逻辑Standby :
SQL> alter database recover managed standby database cancel ;
（如果是mananged恢复模式, 需要先cancel）

SQL> alter database recover to logical standby mxweb01;

可能会遇到两种错误：
1. sys密码不一致导致一直hand住； ---建立与主库一致的密码文件
2. 备库监听没有包含standby的instance信息。 ---监听文件中添加instance信息

重启逻辑备库
第一次启动
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open resetlogs; （这一步应该可以看到初始归档生成）
然后
SQL> shutdown immediate;
SQL> startup;

Logical Stadnby上启动sql apply
alter database start logical standby apply;

如果要启动实时应用特性，需要先在备库添加standby redo logfile，这里我们不使用。
alter database add standby logfile '/data/mxdell/redo01s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo02s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo03s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo04s.log' size 50m;
alter database add standby logfile '/data/mxdell/redo05s.log' size 50m;
启动real time sql apply
alter database stop logical standby apply;
alter database start logical standby apply immediate;

五，观察状态及视图，调整Logical Standby的Apply性能参数（重要的一步）
参考：
http://www.ningoo.net/html/2008/oracle10gr2_logical_standby_managing_and_tuning.html

1. 可以先花一点点时间认识logical Standby原理：
SQL Apply实际上是一组后台并行进程：
• reader：读取redo记录传递给preparer进程
• preparer: 根据redo记录和数据字典信息生成LCR
• builder: 将同一个事务的LCR打包。对于大事务(eager transaction)，可能被打成多个事务包(transaction chunk)，那么可能有些包里是不包含commit的，每一个事务包都可能交给不同的applier进程。
• analyzer:分析事务包之间的依赖关系
• coordinator:将分析好的事务包交给applier进程
• applier:将事务包应用，如果事务包依赖其他事务包，则需要等待相应的事务包完成。事务能否commit，可能还需要根据不同的情况从coordinator获得相应的信息。
其中，reader,builder,analyzer和coordinator只能有一个进程，而preparer和applier则可以根据需要开启多个并行进程，但是系统中所有的并行进程不能超过初始化参数parallel_max_servers的限制：

一般来说，一个preparer进程可以满足20个applier进程的需要。applier的个数，需要根据系统的情况来看，如果所有的applier进程都比较忙碌，可能要考虑增加。简单的可以通过transactions ready和transactions applied两个状态统计来判断，如果两者差值很大，applier进程数可能太少。还可以观察V$LOGSTDBY_PROCESS视图，来查看apply进程的状态，如果存在status_code的值为16116(表示无事务处理即idle状态)，则说明apply进程足够，存在idle进程，不需要增加apply进程数量。

通过查看V$LOGSTDBY_PROCESS视图来检查PREPARE进程数是否足够，如果status_code状态不为16116 （注意： status_code字段为16116表示无事务处理即idle状态），而apply有进程存在idle状态，可能考虑增加prepare_servers数量，否则暂时不需要增加（默认preparer及appliers 为1和5 , 一般来说preparer默认足够，大多数时候appliers 需要调整）。

alter database stop logical standby apply;
execute dbms_logstdby.apply_set('PREPARE_SERVERS', 2);
execute dbms_logstdby.apply_set('APPLY_SERVERS', 8);
alter database start logical standby apply;

SQL> select name,value from v$logstdby_stats where name like 'transactions%';
NAME                           VALUE
------------------------------ -------------
transactions ready             154100
transactions applied           154058

系统中当前已有的preparer和applier进程也可以通过v$logstdby_stats来查询：
SQL> select name,value from v$logstdby_stats where name like 'number%';
NAME                           VALUE
------------------------------ --------------
number of preparers            2
number of appliers              8

各个SQL Apply进程的状态可以通过v$logstdby_process视图获得：
SQL> select sid,type,status from v$logstdby_process;

SID TYPE         STATUS
----- ------------ --------------------------------------------------------------------------------
1080 COORDINATOR ORA-16116: no work available
1085 READER       ORA-16127: stalled waiting for additional transactions to be applied
1023 BUILDER      ORA-16127: stalled waiting for additional transactions to be applied
1025 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied
1083 PREPARER     ORA-16127: stalled waiting for additional transactions to be applied
1084 ANALYZER     ORA-16120: dependencies being computed for transaction at SCN 0x0582.1294c01c
1073 APPLIER      ORA-16124: transaction 57 17 2322872 is waiting on another transaction
1049 APPLIER      ORA-16124: transaction 44 7 4445763 is waiting on another transaction
1059 APPLIER      ORA-16124: transaction 59 5 2277932 is waiting on another transaction
1092 APPLIER      ORA-16124: transaction 13 8 12274153 is waiting on another transaction
1036 APPLIER      ORA-16124: transaction 2 18 24555358 is waiting on another transaction
1051 APPLIER      ORA-16124: transaction 45 0 4338376 is waiting on another transaction
1064 APPLIER      ORA-16124: transaction 56 11 2362783 is waiting on another transaction
1071 APPLIER      ORA-16113: applying change to table or sequence "NINGOO"."TEST"

从这里可以看到，由于存在一个大事务，大部分applier进程都在等待1071事务的完成，这样应用日志的效率就非常低下。从实际情况来看，大事务对于逻辑备库的影响是非常大的，主库一条语句更新1000行，到逻辑备库就需要解析出1000条语句逐条执行，如果这个表没有主键，那么这些语句甚至需要走全表扫描，那代价就更高了，你会发现一个事务，也许一个小时都未必能应用完成。要在产品环境中使用逻辑备库，一定要想办法打散大事务。

逻辑备库上当前执行的事务可以从v$transaction中获得，然后通过关联v$session和v$sqlarea可以获得事务的SQL语句，进来获得执行计划。

二， Logical Standby Redo Log
观察Logical standby的redo日志状态，确保因不会由于切换时间等待而导致sql apply
延迟：可通过查询Oracle日志来得出结论，如出现：
Thread 1 cannot allocate new log, sequence 3834，则表示日志组不够或者日志文件过小。也可通过 Select * from v$log 查看redo的状态，如果status都处于active状态，则说明redo log大小要增加，组数也需要适当增加。

三、LCR Cache
逻辑备库需要将redo记录解析成LCR，会在shared pool里分配一部分空间来作为LCR Cache，如果cache太小，就会像OS的虚拟内存管理一样，需要做page out，这会严重影响应用日志的性能。曾经碰到过这么一个案例，由于存在一个比较大的事务，跨越了5个logfile，逻辑备库重启后，需要从最早一个没有commit的事务开始重新读取解析和应用日志，因为LCR Cache太小，重新分析这5个logfile的时候产生了大量的page out，即使只需要应用这一个事务，每解析一个日志都花了将近半个小时。后来将LCR Cache增加，一个日志只花了一分钟不到。

默认情况下，LCR Cache为Shared pool的四分之一，最少不少于30M，否则SQL Apply不能启动。如果机器的内存足够，建议将LCR Cache尽量设大一点，当然，同时share pool也要足够大。如果机器内存有限，那可以考虑将buffer cache减少一点来给LCR Cache腾出空间。

可以根据系统中page out的情况来调整LCR Cache的大小：
SQL> select name,value from v$logstdby_stats where name like '%page%';
NAME                           VALUE
------------------------------ --------------
bytes paged out                0
seconds spent in pageout       0
如果Paged Out 大于0，可能需要考虑调整Logical Standby中的MAX_SGA（见下面）。

下面的语句将LCR cache设置为1000M：
alter database stop logical standby apply;
EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1000);
alter database start logical standby apply;

如果LCR Cache设置太大也是浪费，下面的SQL可以查询其使用率：
select name,(least(max_sga,bytes)/max_sga) * 100 pct_utilization
from ( select * from v$sgastat where name = 'Logminer LCR c'
),(select value*(1024*1024) max_sga from dba_logstdby_parameters
where name = 'MAX_SGA');

NAME PCT_UTILIZATION
------------------------------ ---------------
Logminer LCR c 100

四、忽略不需要应用日志的对象

逻辑备库一般是用来做报表，或者做读写分离来分担主库的读压力。对于很多系统，实际上不需要将主库上所有对象的变更都应用到逻辑备库上。比如OLTP上可能有些表是定期从数据仓库里回流过来的，这些表在数据仓库中都已经存在，没有必要到逻辑备库上查询。而且这些回流的表一般都是通过批量的方式拉过来的，都是大事务，对于逻辑备库的性能有很大的影响，那么能在逻辑备库上忽略掉是最好的了。注意参数需要大写。

alter database stop logical standby apply;
--忽略某个表上的DML
execute dbms_logstdby.skip (stmt => 'DML', schema_name => 'HR',
object_name => 'EMPLOYEES', proc_name => null);
--忽略某个表上的DDL
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL', schema_name => 'HR',
object_name => 'EMPLOYEES', proc_name => null);
--忽略某个Schema的DML
execute dbms_logstdby.skip (stmt => 'DML',
schema_name => 'NINGOO', object_name => '%', proc_name => null);
--忽略某个Schema的DDL
execute dbms_logstdby.skip (stmt => 'SCHEMA_DDL',
schema_name => 'NINGOO', object_name => '%', proc_name => null);

alter database start logical standby apply;

五、事务一致性
逻辑备库在恢复的过程中，可以设置不同的事务一致性级别，一共有三种（9i）：

Full:这是默认的级别，事务应用的顺序和主库完全一致
Read Only：这个级别的性能要比full好一些，并且对于select等到的数据还是一致的。但是事务在应用过程中可能和主库中commit的顺序是不一致的。
NONE：完全不管主库的事务顺序，这个级别性能最好，但是可能读取到不一致的数据。
alter database stop logical standby apply;
exec dbms_logstdby.apply_set('TRANSACTION_CONSISTENCY','READ_ONLY');
alter database start logical standby apply;

Oracle10gR2已经不建议使用TRANSACTION_CONSISTENCY，而是使用另外一个替代参数PRESERVE_COMMIT_ORDER，设置为TRUE相当于TRANSACTION_CONSISTENCY=FULL ，而设置设置为FALSE相当于TRANSACTION_CONSISTENCY=NONE，而READ_ONLY模式在10g实际上已经被取消了（Metalink：387450.1）。据文档说PRESERVE_COMMIT_ORDER=FALSE可以提升应用日志50%的性能，但不保证此时读取的数据是一致的。

六、自动删除已经应用过的日志
默认情况下，SQL Apply会在日志应用完成，并且日志涉及到的事务都已经全部commit的情况下，自动删除日志。因为逻辑备库一方面需要接收主库传过来的日志，一方面自己也会产生日志，不及时删除，可能很快归档空间就要爆掉了。这个行为也可以通过修改apply的参数来改变：
alter database stop logical standby apply;
execute dbms_logstdby.apply_set('LOG_AUTO_DELETE', FALSE);
alter database start logical standby apply;

七、更改logminer的默认表空间
逻辑备库使用logminer技术来获取logfile中的redo记录，logminer需要保存很多的元数据，在10gR2中，logminer默认使用sysaux表空间。一般的系统中，sysaux都不会给太大，可能很快就被logminer撑爆了，可以考虑修改logminer的默认表空间：
exec DBMS_LOGMNR_D.set_tablespace('TBS_NINGOO_DAT');

其他：
Logical standby 开启关闭的步骤：
SQL> startup
SQL> alter database start logical standby apply;

SQL> alter database stop logical standby apply;
SQL> shutdown immediate

Logical Standby管理：
www.ningoo.nethtml2008oracle10gr2_logical_standby_managing_and_tuning.html

常见Logical Standby 故障的处理
http://blog.oracle.com.cn/html/63/t-51963.html

注意事项：
1. 赋予sysdba给任何用户会导致logical standby 应用停止，但是归档可以传输过去(如果使用的是arch传输的话) 。

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

转载于:http://blog.itpub.net/35489/viewspace-622532/