[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

[20181113]Logical Standby建立2.txt

相關文章