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/