ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 创建logical standby

创建logical standby

原创 Linux操作系统 作者:lsq_008 时间:2013-11-16 16:42:18 0 删除 编辑

1. 检查数据库中是否存在不支持的数据类型

 
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
  2  WHERE (OWNER, TABLE_NAME) NOT IN
  3  (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
  4  AND BAD_COLUMN = 'Y';

no rows selected  

2. 检查是否存在没有主键或非空唯一约束的表

 
SQL> select owner,table_name from DBA_LOGSTDBY_NOT_UNIQUE;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          TEST
SCOTT                          OPT_TEST
--将这两个表加上主键约束
SQL>  alter table  test add constraint pk_test primary key(id);

Table altered.

SQL> alter table  opt_test add constraint pk_opt_test primary key(id);

Table altered.
---再次检查:
SQL> select owner,table_name from DBA_LOGSTDBY_NOT_UNIQUE;

no rows selected
 

3. 默认情况下一些系统用户数据是不复制的

 
SQL> select owner,name from DBA_LOGSTDBY_SKIP;

OWNER                     NAME
------------------------- --------------------
SI_INFORMTN_SCHEMA        %
MGMT_VIEW                 %
APEX_030200               %
XS$NULL                   %
ORDPLUGINS                %
APPQOSSYS                 %
ORACLE_OCM                %
XDB                       %
SYSMAN                    %
WMSYS                     %
DBSNMP                    %
DIP                       %
OUTLN                     %
EXFSYS                    %
ANONYMOUS                 %
ORDSYS                    %
ORDDATA                   %
MDSYS                     %
SYSTEM                    %
SYS                       %

20 rows selected.    

4. 创建物理standby数据库
这一步之前已经创建过了。
5. 在物理standby数据库上停止apply进程

 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.
 
  
6. 修改primary 数据库上的参数,为switch over做准备

 
SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=+ARCH/stby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sms'  scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

System altered.  

7. 创建LogMiner dictionary

 
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.  
   
8. 将物理standby转换为逻辑standby 

 
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;

Database altered.  
  
9. 调整logical standby数据库参数  

 
SQL> shutdown    
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit          
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stby ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 29 10:56:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to an idle instance.

SQL> SQL> 
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             167776248 bytes
Database Buffers          452984832 bytes
Redo Buffers                3350528 bytes
Database mounted.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/db/oracle/arch/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=smsyj'       scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=chicago ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sms'               scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/home/db/oracle/arch2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=smsyj' scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

System altered.  

10. 打开standby database并启动应用

 
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

---alert日志中可见
LOGSTDBY Analyzer process AS00 started with server id=0 pid=40 OS id=25778
Sat Jun 29 11:17:53 2013
LOGSTDBY Apply process AS01 started with server id=1 pid=41 OS id=25782
Sat Jun 29 11:17:53 2013
LOGSTDBY Apply process AS03 started with server id=3 pid=43 OS id=25790
Sat Jun 29 11:17:53 2013
LOGSTDBY Apply process AS04 started with server id=4 pid=44 OS id=25794
Sat Jun 29 11:17:53 2013
LOGSTDBY Apply process AS05 started with server id=5 pid=45 OS id=25798
Sat Jun 29 11:17:53 2013
LOGSTDBY Apply process AS02 started with server id=2 pid=42 OS id=25786
LOGMINER: End   mining logfile for session 1 thread 1 sequence 116, /home/db/oracle/arch2/arch_819190410_1_116.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 117, /home/db/oracle/arch2/arch_819190410_1_117.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 117, /home/db/oracle/arch2/arch_819190410_1_117.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 118, /home/db/oracle/arch2/arch_819190410_1_118.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 118, /home/db/oracle/arch2/arch_819190410_1_118.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 119, /home/db/oracle/arch2/arch_819190410_1_119.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 119, /home/db/oracle/arch2/arch_819190410_1_119.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 120, /home/db/oracle/arch2/arch_819190410_1_120.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 120, /home/db/oracle/arch2/arch_819190410_1_120.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 121, /home/db/oracle/arch2/arch_819190410_1_121.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 121, /home/db/oracle/arch2/arch_819190410_1_121.arc
Sat Jun 29 11:18:01 2013
Thread 1 advanced to log sequence 5 (LGWR switch)
  Current log# 2 seq# 5 mem# 0: /home/db/oracle/oradata/sms/redo02.log
Sat Jun 29 11:18:09 2013
Archived Log entry 4 added for thread 1 sequence 4 ID 0xdc9ecbb6 dest 1:
Sat Jun 29 11:19:06 2013
RFS[4]: Selected log 4 for thread 1 sequence 123 dbid 880830026 branch 819190410
Sat Jun 29 11:19:09 2013
RFS LogMiner: Registered logfile [/home/db/oracle/arch2/arch_819190410_1_122.arc] to LogMiner session id [1]
Sat Jun 29 11:19:09 2013
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 122, /home/db/oracle/arch2/arch_819190410_1_122.arc
LOGMINER: End   mining logfile for session 1 thread 1 sequence 122, /home/db/oracle/arch2/arch_819190410_1_122.arc  

11. 管理和监控

 
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> COL STATUS FOR A60
SQL> COL EVENT FOR A30
SQL> SET LINES 132 PAGES 100
SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS  ORDER BY EVENT_TIMESTAMP, COMMIT_SCN, CURRENT_SCN;

EVENT_TIME          STATUS                                                       EVENT
------------------- ------------------------------------------------------------ ------------------------------
2013-06-29 11:15:10 ORA-16111: log mining and apply setting up
2013-06-29 11:15:10 Apply LWM 901484, HWM 901484, SCN 901484
2013-06-29 11:22:08 Shutdown acknowledged
2013-06-29 11:22:08 ORA-16128: User initiated stop apply successfully completed

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS  ORDER BY EVENT_TIMESTAMP, COMMIT_SCN, CURRENT_SCN;

EVENT_TIME          STATUS                                                       EVENT
------------------- ------------------------------------------------------------ ------------------------------
2013-06-29 11:15:10 ORA-16111: log mining and apply setting up
2013-06-29 11:15:10 Apply LWM 901484, HWM 901484, SCN 901484
2013-06-29 11:22:08 Shutdown acknowledged
2013-06-29 11:22:08 ORA-16128: User initiated stop apply successfully completed
2013-06-29 11:27:13 ORA-16111: log mining and apply setting up
2013-06-29 11:27:13 Apply LWM 904643, HWM 904643, SCN 904743

6 rows selected.

SQL> col file_name for a50
SQL> COLUMN DICT_BEGIN FORMAT A10;
SQL> SET NUMF 99999999;
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#, NEXT_CHANGE# AS N_SCN#, TIMESTAMP,DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR#, APPLIED 
  2  FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

FILE_NAME                                               SEQ#    F_SCN#    N_SCN# TIMESTAMP           BEG END      THR# APPLIED
-------------------------------------------------- --------- --------- --------- ------------------- --- --- --------- --------
/home/db/oracle/arch2/arch_819190410_1_116.arc           116    900610    901470 2013-06-29 11:16:10 YES YES         1 YES
/home/db/oracle/arch2/arch_819190410_1_117.arc           117    901470    901493 2013-06-29 11:16:06 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_118.arc           118    901493    901997 2013-06-29 11:16:06 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_119.arc           119    901997    902249 2013-06-29 11:16:10 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_120.arc           120    902249    902998 2013-06-29 11:16:12 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_121.arc           121    902998    904210 2013-06-29 11:16:14 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_122.arc           122    904210    904744 2013-06-29 11:19:09 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_123.arc           123    904744    906037 2013-06-29 11:28:36 NO  NO          1 YES
/home/db/oracle/arch2/arch_819190410_1_124.arc           124    906037    906365 2013-06-29 11:32:29 NO  NO          1 CURRENT

9 rows selected.

SQL> COLUMN SERIAL# FORMAT 9999
SQL> COLUMN SID FORMAT 9999
SQL> SELECT SID, SERIAL#, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;

  SID SERIAL# SPID         TYPE                            HIGH_SCN
----- ------- ------------ ------------------------------ ---------
   41       6 25825        COORDINATOR                       906365
   48       2 25841        ANALYZER                          906271
   59       2 25845        APPLIER                           904745
   53       3 25849        APPLIER                           905025
   60       2 25853        APPLIER                           906271
   62       3 25857        APPLIER                           906038
   63       2 25861        APPLIER
   38      21 25829        READER                            906365
   44       9 25833        BUILDER                           906356
   46       7 25837        PREPARER                          906354

10 rows selected.

SQL> COLUMN STATUS FORMAT  A80
SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;

TYPE            STATUS_CODE STATUS
--------------- ----------- --------------------------------------------------------------------------------
COORDINATOR           16116 ORA-16116: no work available
ANALYZER              16117 ORA-16117: processing
APPLIER               16116 ORA-16116: no work available
APPLIER               16116 ORA-16116: no work available
APPLIER               16116 ORA-16116: no work available
APPLIER               16117 ORA-16117: processing
APPLIER               16116 ORA-16116: no work available
READER                16240 ORA-16240: Waiting for log file (thread# 1, sequence# 126)
BUILDER               16127 ORA-16127: stalled waiting for additional transactions to be applied
PREPARER              16127 ORA-16127: stalled waiting for additional transactions to be applied

10 rows selected.

SQL> SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ---------- ---------- -----------
     907438     907729     907439      906691

SQL> /

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ---------- ---------- -----------
     907438     907815     907439      906691

SQL> /                                                                                

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ---------- ---------- -----------
     907955     907968     907956      907439  

12. Switch over 
 
---1. primary 端检查状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

---2. standby 端准备switchover
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

--3. primary 端检查状态是否为TO LOGICAL STANDBY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY

--4. primary端执行switch over
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

--5. 在standby端检查,确认所有的redo都以及被应用
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

--6. 在standby端执行switch over,将standby转换为primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

--7. 在新的standby端启动apply进程
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.  

13.


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

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1241719