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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-776747/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
