11R2-DataGuard Scenarios.Failover後配置邏輯備庫
13.1 Configuring Logical Standby Databases After a Failover
前言：在11R2中的DG中有8種場景，Configuring Logical Standby Databases After a Failover是第一個場景，大體為在新主庫為物理備庫或者邏輯備庫時配置新主庫的邏輯備庫。
13.1.1 When the New Primary Database Was Formerly a Physical Standby Database
實驗環境：
一個主庫（dbname為dong_pri），2個備庫（物理備庫（dbname為mm_stb）+邏輯備庫（db_name為mm_stb2）各一個），當主庫發生failover後，讓物理備庫切換為主庫，而原來的邏輯備庫則要作為新主庫的邏輯備庫。
Step 0 Prepare the environment
首先要配置好一個主庫，2個備庫（邏輯備庫1和邏輯備庫2）。此部署省略。
模擬主庫故障，並將物理備庫failover成主庫：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> !tnsping mm_stb2---這裡mm_stb2為邏輯備庫
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-JAN-2014 15:54:37
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mm_stb2)))
OK (10 msec)
SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=/u01/app/oracle/diag/
rdbms/dong/ VALID_FOR=(ALL_LOG
FILES,ALL_ROLES) DB_UNIQUE_NA
ME=mm_stb
模擬主庫出現故障：
物理備庫：
SQL> !lsnrctl stop
SQL>shutdown immediate
SQL>startup
主庫：
SQL> !lsnrctl stop
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 107
Next log sequence to archive 109
Current log sequence 109
SQL> alter system switch logfile;
System altered.
SQL> create table t_dong13_1 as select * from dba_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 109
Next log sequence to archive 111
Current log sequence 111
物理備庫：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 107
Next log sequence to archive 0
Current log sequence 109
此時主庫故障：
SQL> shutdown abort
ORACLE instance shut down.
將備庫沒有同步的那2個歸檔檔案scp到物理備庫，並應用：
主庫伺服器：
[oracle@baobao dong]$ scp 1_109_833209595.dbf 1_110_833209595.dbf 192.168.160.128:/u01/app/oracle/diag/rdbms/dong/
oracle@192.168.160.128's password:
1_109_833209595.dbf 100% 106KB 105.5KB/s 00:00
1_110_833209595.dbf 100% 8644KB 8.4MB/s 00:00
物理備庫：
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/u01/app/oracle/diag/rdbms/dong/1_109_833209595.dbf';
Database altered.
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '/u01/app/oracle/diag/rdbms/dong/1_110_833209595.dbf';
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 107
Next log sequence to archive 0
Current log sequence 110
SQL> select thread#,DEST_ID,name,applied from v$archived_log order by RECID desc ;
THREAD# DEST_ID NAME APPLIED
---------- ---------- ------------------------------------------------------- ---------
1 0 /u01/app/oracle/diag/rdbms/dong/1_110_833209595.dbf NO
1 0 /u01/app/oracle/diag/rdbms/dong/1_109_833209595.dbf NO
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> select thread#,DEST_ID,name,applied from v$archived_log order by RECID desc ;
THREAD# DEST_ID NAME APPLIED
---------- ---------- ------------------------------------------------------- ---------
1 0 /u01/app/oracle/diag/rdbms/dong/1_110_833209595.dbf YES
1 0 /u01/app/oracle/diag/rdbms/dong/1_109_833209595.dbf YES
此時所有的歸檔都已經應用完畢。
將物理備庫變為主庫：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> alter database recover managed standby database finish force;
Database altered.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> alter database open;
Database altered.
Step 1 Configure the FAL_SERVER parameter to enable automatic recovery of log files.
On the SAT database, issue the following statement:
SQL> show parameter fal_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string mm_stb2
fal_server string dong_pri
SQL> alter system set fal_server='mm_stb';
System altered.
SQL>alter system set LOG_ARCHIVE_DEST_3='SERVICE=mm_stb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb2';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE';
SQL> !lsnrctl start
SQL> !tnsping mm_stb2
Step 2 Verify the logical standby database is capable of serving as a standby database to the new primary database.
Call the PREPARE_FOR_NEW_PRIMARY routine to verify and make ready the local logical standby for configuration with the new primary. During this step, local copies of log files that pose a risk for data divergence are deleted from the local database. These log files are then requested for re-archival directly from the new primary database.
On the SAT database, issue the following statement:
邏輯備庫：
SQL> create database link linkpri connect to mao identified by mao using 'dong_pri';
Database link created.
SQL> create database link linkphy connect to mao identified by mao using 'mm_stb';
Database link created.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY(former_standby_type => 'PHYSICAL' ,dblink => 'linkphy');
PL/SQL procedure successfully completed.
SQL> alter database START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
到此為止，新主庫（原物理備庫）和現邏輯備庫（原邏輯備庫）就為一套嶄新的DG啦。
13.1.2 When the New Primary Database Was Formerly a Logical Standby Database
實驗環境：
一個主庫，2個備庫（邏輯備庫1和邏輯備庫2），當主庫發生failover後，讓其中一個邏輯備庫（邏輯備庫1）切換為主庫，而第二個邏輯備庫（邏輯備庫2）則要作為新主庫的邏輯備庫。
Step 0 Prepare the environment
首先要配置好一個主庫，2個備庫（邏輯備庫1和邏輯備庫2）。此部署省略。
模擬主庫故障，並將邏輯備庫1 failover成主庫：
邏輯備庫1：
SQL> select database_role,force_logging from v$database;
DATABASE_ROLE FOR
---------------- ---
LOGICAL STANDBY YES
SQL> alter database activate logical standby database finish apply;
Database altered.
SQL> select database_role,force_logging from v$database;
DATABASE_ROLE FOR
---------------- ---
PRIMARY YES
SQL> select name,database_role,open_mode,protection_mode,GUARD_STATUS from v$database;
NAME DATABASE_ROLE OPEN_MODE PROTECTION_MODE GUARD_S
------------------------------ ---------------- -------------------- -------------------- -------
DONGLOG0 PRIMARY READ WRITE MAXIMUM PERFORMANCE NONE
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/u01/app/oracle/diag/
rdbms/dong/ VALID_FOR=(ALL_LOG
FILES,ALL_ROLES) DB_UNIQUE_NA
ME=mm_stb
SQL> show parameter log_archive_dest_4-----這個是以前作為邏輯備庫時設定的，現在已經變為主庫了，所以不需要在設定此目錄。
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_4 string LOCATION=/u01/app/oracle/diag/
rdbms/dong/lgc_dong0/ VALID_fo
r=(STANDBY_LOGFILES,STANDBY_RO
LE) DB_UNIQUE_NAME=mm_stb
SQL> alter system set log_archive_dest_4='SERVICE=mm_stb2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=mm_stb2';
System altered.
此步是是要設定我作為新主庫，而將我的歸檔傳到另一個service端。這個service端就是邏輯備庫2.
Step 1 Ensure the new primary database is ready to support logical standby databases.
On the NYC database, ensure the following query returns a value of READY. Otherwise the new primary database has not completed the work required to enable support for logical standby databases. For example:
SQL> SELECT VALUE FROM SYSTEM.LOGSTDBY$PARAMETERS WHERE NAME = 'REINSTATEMENT_STATUS';
SQL> SELECT name,VALUE FROM SYSTEM.LOGSTDBY$PARAMETERS ;---邏輯備庫1
NAME VALUE
------------------------------ ------------------------------
LMNR_SID 1
GUARD_STANDBY READY
FIRST_SCN 1622607
PRIMARY 2075447482
APPLY_SCN 1624021
DISABLE_APPLY_DELAY
REAL_TIME
7 rows selected.
SQL> SELECT name,VALUE FROM SYSTEM.LOGSTDBY$PARAMETERS ;--邏輯備庫2
NAME VALUE
------------------------------ ------------------------------
DISABLE_APPLY_DELAY
REAL_TIME
LMNR_SID 1
GUARD_STANDBY READY
FIRST_SCN 1541405
PRIMARY 2075447482
APPLY_SCN 1542812
7 rows selected.
Step 2 Configure the FAL_SERVER parameter to enable automatic recovery of log files.
On the SAT database, issue the following statement:
SQL> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string mm_stb2
fal_server string dong_pri---原來配置的是主庫
SQL> alter system set fal_server='mm_stb';
System altered.
SQL> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string mm_stb2
fal_server string mm_stb--由於主庫已經毀壞，所以配置新主庫的資訊
Step 3 Verify the logical standby database is capable of being a standby to the new primary.
SQL> create database link linkphy connect to mao identified by mao using 'mm_stb';
Database link created.
SQL> EXECUTE DBMS_LOGSTDBY.PREPARE_FOR_NEW_PRIMARY(former_standby_type =>'LOGICAL' ,dblink => 'linkphy');
PL/SQL procedure successfully completed.
Step 4 Start SQL Apply.
SQL> alter database start logical standby apply new primary linkphy;
Database altered.
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
現在新主庫（原邏輯備庫1）和新備庫（原邏輯備庫2）就構成了一套主庫與邏輯備庫的DG。
測試：
新主庫：
SQL> select count(*) from mao.t_mao;
COUNT(*)
----------
8
SQL> insert into mao.t_mao select * from mao.t_mao;
8 rows created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select count(*) from mao.t_mao;
COUNT(*)
----------
16
新備庫：
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
4 1640172 1652706 07-JAN-14 YES
5 1652706 1656706 07-JAN-14 YES
6 1656706 1664366 07-JAN-14 YES
7 1664366 1665662 07-JAN-14 YES
8 1665662 1665923 07-JAN-14 YES
SQL> select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
4 1640172 1652706 07-JAN-14 YES
5 1652706 1656706 07-JAN-14 YES
6 1656706 1664366 07-JAN-14 YES
7 1664366 1665662 07-JAN-14 YES
8 1665662 1665923 07-JAN-14 YES
9 1665923 1666000 07-JAN-14 YES
6 rows selected.
SQL> select count(*) from mao.t_mao;
COUNT(*)
----------
16
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24500180/viewspace-1068141/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 配置物理備庫+邏輯備庫
- 邏輯備庫Switchover
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（1）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（2）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（3）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（4）
- 單節點主庫、邏輯備庫升級為RAC、物理備庫、邏輯備庫（5）
- 11 管理邏輯備庫
- 資料庫邏輯備份(轉)資料庫
- [zt] Oracle如何配置邏輯備用資料庫(Logical Standby)Oracle資料庫
- 邏輯備份全庫或者邏輯備份多個使用者的資料
- 4節點RAC建立邏輯備庫
- oracle邏輯備用資料庫（一）Oracle資料庫
- 邏輯Data Guard主備庫的轉換
- 【DataGuarad】邏輯遷移與standby備庫
- dataguard之邏輯備庫表空間不足
- 邏輯備庫上有指定表不應用
- ORACLE資料庫的邏輯備份(轉)Oracle資料庫
- 邏輯備份--mysqldumpMySql
- 邏輯DG主備庫轉換的failoverAI
- dataguard回顧之安裝——建立邏輯備庫
- mysql 邏輯備份 （mysqldump）MySql
- mysql的邏輯備份MySql
- rac與邏輯備庫不能自動建表空間，物理備庫正常
- 11g 邏輯備庫簡單故障處理
- 資料庫（表）的邏輯備份與恢復資料庫
- dataguard之邏輯備庫移動資料檔案
- 邏輯備用資料庫主要作用是什麼。資料庫
- 認識資料庫物理備份和邏輯備份區別資料庫
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (三)
- 11R2-DataGuard Scenarios.主備庫的閃回iOS
- MongoDB 邏輯備份工具mongodumpMongoDB
- Oracle 邏輯備份 expdp/impdpOracle
- expdp 邏輯備份指令碼指令碼
- MySql邏輯備份恢復MySql
- Oracle邏輯備份指令碼Oracle指令碼