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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1068141/