oracle實驗記錄 (oracle 10G dataguard(11)建立logical standby)

邏輯standby

邏輯standby支援資料型別
BINARY_DOUBLE、BINARY_FLOAT、BLOB、CHAR、CLOB and NCLOB、DATE、INTERVAL YEAR TO
MONTH、INTERVAL DAY TO SECOND、LONG、LONG RAW、NCHAR、NUMBER、NVARCHAR2、RAW、
TIMESTAMP、TIMESTAMP WITH LOCAL TIMEZONE、TIMESTAMP WITH TIMEZONE、VARCHAR2 and
VARCHAR

邏輯standby 將跳過的sql語句
ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION

邏輯standby支援的schema
SQL> select username from dba_users where username not in (select owner from dba
_logstdby_skip);

USERNAME
------------------------------
SCOTT
TSMSYS
PM
IX
SH
OE
HR

已選擇7行。

邏輯standby不支援的 物件
SQL> col owner format a10
SQL> col table_name format a10
SQL> co column_type fotmat a10
SQL> col attributes format a10
SQL> col data_type format a10
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

OWNER      TABLE_NAME COLUMN_NAME                    DATA_TYPE  ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
SH         MVIEW$_EXC BAD_ROWID                      ROWID
           EPTIONS

OE         CUSTOMERS  CUST_ADDRESS                   CUST_ADDRE
                                                     SS_TYP

OE         CUSTOMERS  PHONE_NUMBERS                  PHONE_LIST
                                                     _TYP

OE         CUSTOMERS  CUST_GEO_LOCATION              SDO_GEOMET
                                                     RY

OWNER      TABLE_NAME COLUMN_NAME                    DATA_TYPE  ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------

OE         WAREHOUSES WAREHOUSE_SPEC                 XMLTYPE
OE         WAREHOUSES WH_GEO_LOCATION                SDO_GEOMET
                                                     RY

PM         ONLINE_MED PRODUCT_AUDIO                  ORDAUDIO
           IA

PM         ONLINE_MED PRODUCT_TESTIMONIALS           ORDDOC
           IA

OWNER      TABLE_NAME COLUMN_NAME                    DATA_TYPE  ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
PM         ONLINE_MED PRODUCT_PHOTO                  ORDIMAGE
           IA

PM         ONLINE_MED PRODUCT_PHOTO_SIGNATURE        ORDIMAGESI
           IA                                        GNATURE

PM         ONLINE_MED PRODUCT_THUMBNAIL              ORDIMAGE
           IA

PM         ONLINE_MED PRODUCT_VIDEO                  ORDVIDEO
           IA

OWNER      TABLE_NAME COLUMN_NAME                    DATA_TYPE  ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------

PM         PRINT_MEDI AD_TEXTDOCS_NTAB               TEXTDOC_TA
           A                                         B

PM         PRINT_MEDI AD_GRAPHIC                     BFILE
           A

PM         PRINT_MEDI AD_HEADER                      ADHEADER_T
           A                                         YP

OE         CATEGORIES CATEGORY_NAME                  VARCHAR2   Object Tab

OWNER      TABLE_NAME COLUMN_NAME                    DATA_TYPE  ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
           _TAB                                                 le

OE         CATEGORIES CATEGORY_DESCRIPTION           VARCHAR2   Object Tab
           _TAB                                                 le

OE         CATEGORIES CATEGORY_ID                    NUMBER     Object Tab
           _TAB                                                 le

OE         CATEGORIES PARENT_CATEGORY_ID             NUMBER     Object Tab
           _TAB                                                 le

已選擇19行。

確定primary 表的每一行可以被唯一標識,logical standby 通過應用sql語句維持primary與logical standby的同步，因為是邏輯standby 其物理結構 有可能和primary 不一樣 所以primary 表的rowid與logical standby的row id不同，必須使用primary key 約束，或unique index來唯一標識相關表的行資料.
具體邏輯
Oracle 使用主鍵或唯一約束/索引補充記錄( 必須開啟 補充日誌特徵supplemental logging)來邏輯地標識在邏輯備資料庫中被更改的
行。當允許資料庫範圍的主鍵和唯一約束/索引補充記錄時，每個UPDATE 語句也寫必要的
列值到重做日誌，以在邏輯備資料庫中唯一地標識被更改的行。
1. 如果表定義了主鍵，則主鍵與被更改的列一起記錄，作為 UPDATE 語句的一部分
來標識更改的行。(區別那些列被更新)
2. 如果沒有主鍵，則最短的非空唯一約束/索引與更改的行一起記錄，作為UPDATE
語句的一部分來標識更改的行。(表若有多個非空唯一index oracle 選擇最短的 非空唯一index)
3. 如果即沒有主鍵也沒有非空唯一約束/索引，則所有有界限大小的列作為UPDATE
語句的一部分記錄，以標識更改的行。換一句話說，記錄所有列除了：LONG、LOB、
LONG RAW、物件型別、和集合。

現在在表上沒有主鍵約束或唯一index的表
SQL> conn xh/a831115  (primary)
已連線。
SQL> create table ttt (a int , b char(10));   (primary)

表已建立。

SQL> insert into ttt values(1,'a'); (primary)

已建立 1 行。

SQL> insert into ttt values(2,'b'); (primary)

已建立 1 行。

SQL> insert into ttt values(3,'c'); (primary)

已建立 1 行。

SQL> insert into ttt values(4,'d');(primary)

已建立 1 行。

SQL> commit; (primary)

提交完成。

SQL> conn / as sysdba (primary)
已連線。
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);

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TSMSYS                         SRS$
SH                             COSTS
SH                             SUPPLEMENTARY_DEMOGRAPHICS
SCOTT                          BONUS
SCOTT                          SALGRADE
HR                             COUNTRIES
OE                             INVENTORIES
SH                             SALES
XH                             TTT

已選擇9行。～～～～～～～～～～～～～～～注意支援的sechma 中無sys schema

  1   SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE   (primary)
  2    WHERE (OWNER, TABLE_NAME) NOT IN
  3    (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
  4* AND BAD_COLUMN = 'Y'
  5  ;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TSMSYS                         SRS$

關於bad_column
Y - Table column is defined using an unbounded data type, such as LONG or BLOB. If two rows in the table match except in their LOB columns, then the table cannot be maintained properly. Log apply services will attempt to maintain these tables, but you must ensure the application does not allow uniqueness only in the unbounded columns.
()
N - Enough column information is present to maintain the table in the logical standby database but the log transport services and log apply services would run more efficiently if you added a primary key. You should consider adding a disabled RELY constraint to these tables.(表示logical standby支援這些表，但建議建立一個)

可以對查到的這些表建立 一個primary key
alter table XXXX  add primary key (id) ;
如果覺得建立primary key or unique index 維護成本高 切 可以確定表資料是唯一的 可以使用rely
alter table XXXX  add primary key (id) rely disable;
rely 約束，系統則會假定該表中的行是唯一。

 

SQL> alter table xh.ttt add primary key (a) rely disable; (primary)

表已更改。

SQL> ed
已寫入 file afiedt.buf

  1   SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE  (primary)
  2    WHERE (OWNER, TABLE_NAME) NOT IN
  3*   (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
SQL> /

OWNER                          TABLE_NAME
------------------------------ ------------------------------
TSMSYS                         SRS$
SH                             COSTS
SH                             SUPPLEMENTARY_DEMOGRAPHICS
SCOTT                          BONUS
SCOTT                          SALGRADE
HR                             COUNTRIES
OE                             INVENTORIES
SH                             SALES

已選擇8行。

SQL>

SQL> alter database recover managed standby database  disconnect from session;(standby)

資料庫已更改。

SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING               5
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      APPLYING_LOG          4

SQL> /

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING               5
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      APPLYING_LOG          4

SQL> /

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING               5
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      APPLYING_LOG          4

SQL> select applied from v$archived_log;(standby)

APP
---
YES
YES
YES
YES
YES
NO
YES
YES
YES
YES
NO

APP
---
YES
NO

已選擇13行。

SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING               5
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      APPLYING_LOG          5~~~~正在應用

SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)

PROCESS   CLIENT_P STATUS        SEQUENCE#
--------- -------- ------------ ----------
ARCH      ARCH     CLOSING               5
ARCH      ARCH     CONNECTED             0
RFS       UNKNOWN  IDLE                  0
MRP0      N/A      WAIT_FOR_LOG          6~~~~~~~~~~~~~~~~~~~~應用完畢了都

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;(standby)

資料庫已更改。

資料庫已更改。

資料庫已更改。
開啟補充日誌特徵
SQL> alter database add supplemental log data(primary key,unique index) columns; (primary)

資料庫已更改。

SQL> select db_unique_name,database_role,supplemental_log_data_pk,supplemental_l  (primary)

og_data_ui from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SUP SUP
------------------------------ ---------------- --- ---
xh                             PRIMARY          YES YES

SQL> alter database add supplemental log data(primary key,unique index) columns;(standby)

資料庫已更改。
SQL> select db_unique_name,database_role,supplemental_log_data_pk,supplemental_l  (standby)
og_data_ui from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    SUP SUP
------------------------------ ---------------- --- ---
standby1                       PHYSICAL STANDBY YES YES

 

SQL> show parameter log_archive_dest_1  (primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=G:\archivelog valid_f
                                                 r=(all_logfiles,all_roles) db
                                                 _unique_name=xh
log_archive_dest_10                  string
SQL> host mkdir g:\archivelog2  (primary)

SQL> alter system set log_archive_dest_3='location=g:\archivelog2 valid_for=(sta
ndby_logfiles,standby_role) db_unique_name=xh';

系統已更改。

在主庫設定，用於 primary database 轉換成logical standby database  valid_for看出只有是standby角色，歸檔standyb redo file時才有效

 

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;(primary)

PL/SQL 過程已成功完成。

primary database執行生成 logmnr 資料字典，翻譯redolog ,archive log用
1.執行時將等待primary database 所有事務結束
2.如果沒手動開supplemental log 那麼 這個過程將自動開
3.過程使用flashback query方式 查資料字典一致性快照,所以對undo有一定要求

 

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2; (將物理standby1 轉換為邏輯standby  xh2)
ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2
*
第 1 行出現錯誤:
ORA-19953: 不應開啟資料庫~~~~~~~~~~~~~~~~~~~~~`必須在mount狀態

SQL> shutdown immediate;
ORA-01507: 未裝載資料庫

ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size             100664000 bytes
Database Buffers          180355072 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2;

資料庫已更改。

修改引數，因為logical standby是 read write自身會產生redo，寫入歸檔dest，檢查下 是否本地歸檔於接收primary redo產生歸檔 的dest一樣，如果一樣 需要修改，避免混亂
衝突
SQL> show parameter standby_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      e:\standby\archive
standby_file_management              string      auto
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=e:\standby\archive va
                                                 lid_for=(all_logfiles,all_role
                                                 s) db_unique_name=standby1
log_archive_dest_10                  string

SQL> host mkdir e:\standby\archivelog
SQL> alter system set log_archive_dest_1='location=e:\standby\archivelog valid_f
or=(online_logfiles,all_roles) db_unique_name=standby1';

系統已更改。 （本地online redo log file歸檔 位置）

SQL> host mkdir e:\standby\archivelog2

SQL> alter system set log_archive_dest_3='location=e:\standby\archivelog2 valid_
for=(standby_logfiles,standby_role) db_unique_name=standby1';

系統已更改。 (接收primary 傳遞的redo  到本地的歸檔位置)

SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=e:\standby\archivelog
                                                  valid_for=(online_logfiles,al
                                                 l_roles) db_unique_name=standb
                                                 y1
log_archive_dest_10                  string
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=xh valid_for=(online_l
                                                 ogfiles,primary_role) db_uniqu
                                                 e_name=xh
SQL> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string      location=e:\standby\archivelog
                                                 2 valid_for=(standby_logfiles,
                                                 standby_role) db_unique_name=s
                                                 tandby1
SQL> show parameter standby_ar

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      e:\standby\archive
SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      e:\standby\archive
db_recovery_file_dest_size           big integer 2G

 

SQL> shutdown immediate;
ORA-01507: 未裝載資料庫

ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size             100664000 bytes
Database Buffers          180355072 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
SQL> alter database open resetlogs;
SQL>alter database start logical standby apply immediate; (physical standby)

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- ----------
XH2       standby1                       LOGICAL STANDBY  READ WRITE

~~~~可以看到  如果 物理standby有spfile 那麼執行ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2,時oracle 會更新spfile,如果沒有spfile 那麼在關閉後oracle發一條資訊提示 修改db_name

SQL> select count(*) from v$standby_log;~~~~~~~~~~必須要有standby logfile

  COUNT(*)
----------
         1

SQL>

SQL> conn  xh/a831115（primary）
已連線。
SQL> insert into ttt values(2,'b');（primary）

已建立 1 行。

SQL> commit;（primary）

提交完成。

SQL> alter system switch logfile;（primary）

系統已更改。

SQL> show user (standby)
USER 為 "XH"
SQL> select * from ttt;(standby)

         A B
---------- ----------
         1 a
         2 b

 

角色轉換

先檢查下 primary 引數
SQL> show parameter name_convert(primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
SQL> show parameter fal   (primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      xh
fal_server                           string      standby1
SQL> alter system set db_file_name_convert='e:\standby\datafile','g:\oracle\prod    (primary)
uct\10.2.0\oradata\xh' scope=spfile;

系統已更改。

SQL> alter system set log_file_name_convert='e:\standby\datafile','g:\oracle\pro    (primary)
duct\10.2.0\oradata\xh' scope=spfile;

系統已更改。

 
SQL> show parameter log_archive_dest   (primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string      location=G:\archivelog valid_f
                                                 r=(all_logfiles,all_roles) db
                                                 _unique_name=xh
log_archive_dest_10                  string
log_archive_dest_2                   string      service=standby1 valid_for=(on
                                                 line_logfiles,primary_role) db
                                                 _unique_name=standby1
log_archive_dest_3                   string      location=g:\archivelog2 valid_
                                                 for=(standby_logfiles,standby_   ~~~~~~~~~~~~必須要設
                                                 role) db_unique_name=xh
SQL> show parameter log_archive_c  (primary)

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_config                   string      dg_config=(xh,standby1)

SQL> startup force (primary) 重啟下, 讓 SCOPE=SPFILE的 引數生效
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              83886784 bytes
Database Buffers          197132288 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。

SQL> select count(*) from v$standby_log; （primary） 邏輯standby必須要standby logfile，所以必須新增一個

  COUNT(*)
----------
         0

 

SQL> alter database add standby logfile group 4 'G:\ORACLE\PRODUCT\10.2.0\ORADAT  （primary）
A\XH\standby04.log' size 50m;

資料庫已更改。

SQL> select count(*) from v$standby_log;（primary）

  COUNT(*)
----------
         1

 

檢查邏輯standby 引數

 

SQL> show parameter name_convert  (standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      g:\oracle\product\10.2.0\orada
                                                 ta\xh, e:\standby\datafile
log_file_name_convert                string      g:\oracle\product\10.2.0\orada
                                                 ta\xh, e:\standby\datafile
SQL> show parameter fal   (standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      standby1
fal_server                           string      xh
SQL> show parameter log_archive_dest_   (standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=e:\standby\archivelog
                                                  valid_for=(online_logfiles,al
                                                 l_roles) db_unique_name=standb
                                                 y1
log_archive_dest_10                  string
log_archive_dest_2                   string      service=xh valid_for=(online_l
                                                 ogfiles,primary_role) db_uniqu
                                                 e_name=xh
log_archive_dest_3                   string      location=e:\standby\archivelog
                                                 2 valid_for=(standby_logfiles,
                                                 standby_role) db_unique_name=s
SQL> show parameter log_archive_c   (standby)

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_config                   string      dg_config=(xh,standby1)

 

SQL> select process from v$managed_standby;(standby)

PROCESS
---------
ARCH
ARCH
RFS

SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;    (standby)

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2       standby1                       READ WRITE NOT ALLOWED

SQL> select name ,db_unique_name,open_mode,switchover_status from v$database; (primary)

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH        xh                             READ WRITE SESSIONS ACTIVE

 

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

資料庫已更改。

SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH        xh                             READ WRITE PREPARING SWITCHOVER

 

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

資料庫已更改。

SQL>  select name ,db_unique_name,open_mode,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2       standby1                       READ WRITE PREPARING SWITCHOVER

 

SQL>  ALTER DATABASE PREPARE TO SWITCHOVER cancel; （可以取消準備狀態）

資料庫已更改。

SQL>  select name ,db_unique_name,open_mode,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2       standby1                       READ WRITE NOT ALLOWED

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

SQL> select switchover_status from v$database; (primary) 再次檢查確認狀態,必須要到這個狀態

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY

logical standby  執行ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 後，會生成相應的LogMiner 字典資料，只有當它正常的生成且送至當前的primary,操作才可繼續
(to logical standby 表示已經傳到primary)

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;（primary）

資料庫已更改。

這個操作要等待primary的 所有事務完成 查詢v$transaction來 看是否有活動事務.

 

SQL>  select name ,db_unique_name,open_mode,switchover_status from v$database;（standby ）

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2       standby1                       READ WRITE PREPARING SWITCHOVER

SQL> select switchover_status from v$database;（standby  ,一定要等standby 到這個狀態）

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;(standby)

資料庫已更改。

SQL>  select name ,db_unique_name,open_mode,switchover_status,database_role from
 v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
DATABASE_ROLE
----------------
XH2       standby1                       READ WRITE SESSIONS ACTIVE
PRIMARY

SQL> select name ,db_unique_name,open_mode,switchover_status,database_role from
 v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
DATABASE_ROLE
----------------
XH        xh                             READ WRITE NOT ALLOWED
LOGICAL STANDBY

切換完成

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-619721/，如需轉載，請註明出處，否則將追究法律責任。