[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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 |

| 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博客

[20181113]Logical Standby建立2.txt

最新文章