[20181113]Logical Standby建立2.txt

[20181113]Logical Standby建立2.txt --//做数据库这么久,自己从来没有做过Logical Standby.原因主要几点: --//1.11g 支持 在只读下日志应用(当然要有许可),这一定程度限制Logical Standby的应用. --//2.Logical Standby限制多多,实际上sql apply.存在一些限制,数据类型不支持等待. --//3.另外就是goldergate完全可以取代Logical Standby,应用更广. --//自己在测试环境有个dg,转换成Logical Standby看看,顺便做1个记录. 1.环境: SYS@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//备库日志应用正常. SYS@bookdg> @ &r/dg/dg PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS --------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ---------- ARCH        30507 CONNECTED    ARCH     N/A          0          0          0          0          0 ARCH        30509 CONNECTED    ARCH     N/A          0          0          0          0          0 RFS         30522 IDLE         UNKNOWN  N/A          0          0          0          0          0 RFS         30520 IDLE         LGWR     1            1        698        525          1          0 ARCH        30505 CLOSING      ARCH     4            1        695          1        154          0 ARCH        30511 CLOSING      ARCH     6            1        697          1        139          0 MRP0        30534 APPLYING_LOG N/A      N/A          1        698        525     102400          0 7 rows selected. --//检查那些表不支持. SYS@book> select * from dba_logstdby_unsupported_table; OWNER  TABLE_NAME ------ ------------------------------ IX     AQ$_ORDERS_QUEUETABLE_G IX     AQ$_STREAMS_QUEUE_TABLE_I IX     AQ$_STREAMS_QUEUE_TABLE_C IX     AQ$_ORDERS_QUEUETABLE_H IX     AQ$_STREAMS_QUEUE_TABLE_G IX     AQ$_STREAMS_QUEUE_TABLE_H IX     AQ$_ORDERS_QUEUETABLE_I IX     AQ$_ORDERS_QUEUETABLE_T IX     AQ$_STREAMS_QUEUE_TABLE_T SH     DIMENSION_EXCEPTIONS OE     WAREHOUSES OE     CUSTOMERS PM     PRINT_MEDIA PM     ONLINE_MEDIA IX     AQ$_STREAMS_QUEUE_TABLE_L IX     AQ$_STREAMS_QUEUE_TABLE_S IX     STREAMS_QUEUE_TABLE IX     AQ$_ORDERS_QUEUETABLE_L IX     AQ$_ORDERS_QUEUETABLE_S IX     ORDERS_QUEUETABLE OE     PURCHASEORDER OE     CATEGORIES_TAB 22 rows selected. SYS@book> select table_name,column_name,attributes,data_type from dba_logstdby_unsupported; ... --//太多,不在列出. 2.建立逻辑standby: --//首先在备库停止日志应用,然后在主库执行execute dbms_logstdby.build,构造Log Miner Dictionary在日志文件,这样sql apply能 --//适当解析日志内容. SYS@bookdg> alter database recover managed standby database cancel ; Database altered. --//在主库执行前检查,检查SUPPLEMENTAL_LOG_*字段信息(在v$database视图). $ sqlplus -S -L  / as sysdba  <<< '@ pt2 "select * from v$database"' | grep -i SUPP                    30 SUPPLEMENTAL_LOG_DATA_MIN      NO                    31 SUPPLEMENTAL_LOG_DATA_PK       NO                    32 SUPPLEMENTAL_LOG_DATA_UI       NO                    40 SUPPLEMENTAL_LOG_DATA_FK       NO                    41 SUPPLEMENTAL_LOG_DATA_ALL      NO                    47 SUPPLEMENTAL_LOG_DATA_PL       NO SYS@book> execute dbms_logstdby.build; PL/SQL procedure successfully completed. --//alert.log文件出现如下内容: Tue Nov 13 10:24:31 2018 Logminer Bld: Done LOGMINER: Dictionary Build: Waiting for txns in-flight at scn 0x0003.175df064 [13276934244] to complete LOGMINER: Dictionary Build: All in-flight txns at scn 0x0003.175df064 [13276934244] completed Thread 1 cannot allocate new log, sequence 701 Checkpoint not complete   Current log# 3 seq# 700 mem# 0: /mnt/ramdisk/book/redo03.log Tue Nov 13 10:24:31 2018 Completed checkpoint up to RBA [0x2bb.2.10], SCN: 13276934244 Beginning log switch checkpoint up to RBA [0x2bd.2.10], SCN: 13276935100 Thread 1 advanced to log sequence 701 (LGWR switch)   Current log# 1 seq# 701 mem# 0: /mnt/ramdisk/book/redo01.log Archived Log entry 1245 added for thread 1 sequence 700 ID 0x4fb7d86e dest 1: LNS: Standby redo logfile selected for thread 1 sequence 701 for destination LOG_ARCHIVE_DEST_2 Tue Nov 13 10:25:03 2018 ARC1: Standby redo logfile selected for thread 1 sequence 700 for destination LOG_ARCHIVE_DEST_2 $ sqlplus -S -L  / as sysdba  <<< '@ pt2 "select * from v$database"' | grep -i SUPP                    30 SUPPLEMENTAL_LOG_DATA_MIN      IMPLICIT                    31 SUPPLEMENTAL_LOG_DATA_PK       YES                    32 SUPPLEMENTAL_LOG_DATA_UI       YES                    40 SUPPLEMENTAL_LOG_DATA_FK       NO                    41 SUPPLEMENTAL_LOG_DATA_ALL      NO                    47 SUPPLEMENTAL_LOG_DATA_PL       YES --//可以发现执行完成后,一些SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_PL已经enable. 3.在备库转换或者建立logical standby: --//备库: SYS@bookdg> recover to logical standby book; ORA-19953: database should not be open --//备库现在处于open状态. SYS@bookdg> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SYS@bookdg> startup mount ORACLE instance started. Total System Global Area  634732544 bytes Fixed Size                  2255792 bytes Variable Size             197133392 bytes Database Buffers          427819008 bytes Redo Buffers                7524352 bytes Database mounted. SYS@bookdg> recover to logical standby book; Media recovery complete. --//检查dg的alert.log Tue Nov 13 10:28:21 2018 ALTER DATABASE RECOVER  to logical standby book Media Recovery Start: Managed Standby Recovery (bookdg)  started logmerger process Tue Nov 13 10:28:22 2018 Managed Standby Recovery not using Real Time Apply Parallel Media Recovery started with 4 slaves Media Recovery Log /u01/app/oracle/archivelog/book/1_698_896605872.dbf Media Recovery Log /u01/app/oracle/archivelog/book/1_699_896605872.dbf Media Recovery Log /u01/app/oracle/archivelog/book/1_700_896605872.dbf Incomplete Recovery applied until change 13276935094 time 11/13/2018 10:24:31 Media Recovery Complete (bookdg) Killing 2 processes with pids 30621,30619 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 30615 Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 13276935094 Resetting resetlogs activation ID 1337448558 (0x4fb7d86e) Online log /mnt/ramdisk/book/redo01.log: Thread 1 Group 1 was previously cleared Online log /mnt/ramdisk/book/redo02.log: Thread 1 Group 2 was previously cleared Online log /mnt/ramdisk/book/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 13276935092 Tue Nov 13 10:28:28 2018 Setting recovery target incarnation to 3 RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes *** DBNEWID utility started *** DBID will be changed from 1337401710 to new DBID of 1432849420 for database BOOK DBNAME will be changed from BOOK to new DBNAME of BOOK Starting datafile conversion Datafile conversion complete Database name changed to BOOK. Modify parameter file and generate a new password file before restarting. Database ID for database BOOK changed to 1432849420. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open with RESETLOGS option. Succesfully changed database name and ID. *** DBNEWID utility finished succesfully *** Shutting down archive processes Archiving is disabled Tue Nov 13 10:28:28 2018 ARCH shutting down Tue Nov 13 10:28:28 2018 ARCH shutting down Tue Nov 13 10:28:28 2018 ARCH shutting down ARC3: Archival stopped ARC0: Archival stopped ARC1: Archival stopped Tue Nov 13 10:28:28 2018 ARCH shutting down ARC2: Relinquishing active heartbeat ARCH role ARC2: Archival stopped Completed: ALTER DATABASE RECOVER  to logical standby book --//数据库dbid已经发生了改变. 4.打开logical standby数据库: SYS@bookdg> shutdown immediate ; ORA-01507: database not mounted ORACLE instance shut down. SYS@bookdg> startup mount ORACLE instance started. Total System Global Area  634732544 bytes Fixed Size                  2255792 bytes Variable Size             197133392 bytes Database Buffers          427819008 bytes Redo Buffers                7524352 bytes Database mounted. SYS@bookdg> select DATABASE_ROLE from v$database; DATABASE_ROLE ---------------- LOGICAL STANDBY SYS@bookdg> alter database open ; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SYS@bookdg> alter database open resetlogs; Database altered. --//启动日志应用: SYS@bookdg> @ &r/dg/dg PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS --------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ---------- ARCH        30680 CONNECTED    ARCH     N/A          0          0          0          0          0 ARCH        30682 CONNECTED    ARCH     N/A          0          0          0          0          0 ARCH        30684 CONNECTED    ARCH     N/A          0          0          0          0          0 RFS         30690 IDLE         UNKNOWN  N/A          0          0          0          0          0 ARCH        30686 CLOSING      ARCH     1            1          1          1        500          0 RFS         30692 IDLE         LGWR     3            1        703         14          1          0 6 rows selected. SYS@bookdg> alter database start logical standby apply immediate; Database altered. SYS@bookdg> @ &r/dg/dg PROCESS       PID STATUS       CLIENT_P GROUP# THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS --------- ------- ------------ -------- ------ ------- ---------- ---------- ---------- ---------- ARCH        30680 CONNECTED    ARCH     N/A          0          0          0          0          0 ARCH        30682 CONNECTED    ARCH     N/A          0          0          0          0          0 ARCH        30684 CONNECTED    ARCH     N/A          0          0          0          0          0 RFS         30690 IDLE         UNKNOWN  N/A          0          0          0          0          0 ARCH        30686 CLOSING      ARCH     1            1          1          1        500          0 RFS         30692 IDLE         LGWR     3            1        703         21          1          0 6 rows selected. SYS@bookdg> select name,open_mode,database_role,db_unique_name,dbid from v$database; NAME                 OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                       DBID -------------------- -------------------- ---------------- ------------------------------ ---------- BOOK                 READ WRITE           LOGICAL STANDBY  bookdg                         1432849420 --//主库: SYS@book> select name,open_mode,database_role,db_unique_name,dbid from v$database; NAME                 OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                       DBID -------------------- -------------------- ---------------- ------------------------------ ---------- BOOK                 READ WRITE           PRIMARY          book                           1337401710 --//可以发现dbid已经发生变化. 6.简单测试: --//主库: SCOTT@book> create table empx as select * from emp; Table created. SCOTT@book> set autot traceonly; SCOTT@book> update empx set ename=lower(ename) ; 14 rows updated. Execution Plan ---------------------------------------------------------- Plan hash value: 960980050 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | UPDATE STATEMENT   |      |    14 |   532 |     3   (0)| 00:00:01 | |   1 |  UPDATE            | EMPX |       |       |            |          | |   2 |   TABLE ACCESS FULL| EMPX |    14 |   532 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Statistics ----------------------------------------------------------           3  recursive calls          15  db block gets           7  consistent gets           0  physical reads        5368  redo size         840  bytes sent via SQL*Net to client         788  bytes received via SQL*Net from client           3  SQL*Net roundtrips to/from client           2  sorts (memory)           0  sorts (disk)          14  rows processed SCOTT@book> commit ; Commit complete. --//备库执行: SYS@bookdg> select * from scott.empx where rownum=1;      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------       7369 smith      CLERK           7902 1980-12-17 00:00:00        800                    20 --//一些dml操作logical standby是禁止的. SCOTT@bookdg> update empx set ename=upper(ename) where empno=7369; update empx set ename=upper(ename) where empno=7369        * ERROR at line 1: ORA-16224: Database Guard is enabled