ITPub博客

首页 > 数据库 > Oracle > [20181113]Logical Standby建立2.txt

[20181113]Logical Standby建立2.txt

原创 Oracle 作者:lfree 时间:2018-11-13 10:48:04 0 删除 编辑

[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


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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2852
  • 访问量
    6641079