11G 新增 Physical standby 并通過 DGBroker 進行主備切換

1.PRIMARY

????原來的主庫，新增新備庫的配置信息。

SQL> alter system set log_archive_dest_state_3=defer;

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PRIM,ORA11G_STBY,ORA11G_STBY2)';

SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=ORA11G_STBY2 ASYNC DELAY=60 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_STBY2';

2.FIRST STANDBY

????第一套備庫調整配置信息。

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PRIM,ORA11G_STBY,ORA11G_STBY2)';

3.SECOND STANDBY

????配置第二套備庫。由于第一套備庫和第二套備庫在同一臺物理機上，因此需要注意目錄隔離，避免新備庫破壞就備庫的文件。

3.1 參數文件

[oracle@oradb2 ~]$ export ORACLE_SID=ora11g

SQL> create pfile='/home/oracle/11g2.pfile' from spfile;

[oracle@oradb2 ~]$ vi 11g2.pfile

*.audit_file_dest='/oracle/app/oracle/admin/ora11g2/adump'

*.control_files='/oradata/database/11g/ora11g2/control01.ctl','/oradata/database/11g/ora11g2/control02.ctl'

*.DB_UNIQUE_NAME='ORA11G_STBY2'

*.fal_server='ORA11G_PRIM'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PRIM,ORA11G_STBY,ORA11G_STBY2)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/archive/11g2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_STBY2'

*.LOG_ARCHIVE_DEST_2='SERVICE=ORA11G_PRIM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_PRIM'

*.dg_broker_start=false

*.db_file_name_convert='/oradata/database/11g/ora11g/','/oradata/database/11g/ora11g2/'

*.log_file_name_convert='/oradata/database/11g/ora11g/','/oradata/database/11g/ora11g2/'

????通過參數文件啟動新備庫到nomount狀態

[oracle@oradb2 ~]$ export ORACLE_SID=ora11g2

SQL> startup nomount pfile='/home/oracle/11g2.pfile';

SQL> create spfile from pfile='/home/oracle/11g2.pfile';

SQL> startup force nomount;

3.2 口令文件

[oracle@oradb2 ~]$ cd $ORACLE_HOME/dbs

[oracle@oradb2 dbs]$ cp orapwora11g orapwora11g2

3.3 網絡配置

[oracle@oradb2 dbs]$ cd $ORACLE_HOME/network/admin

[oracle@oradb2 admin]$ vi listener.ora?

????新增新備庫的配置信息，注冊到靜態監聽中。

? ? (SID_DESC =

? ? ? (GLOBAL_DBNAME = ORA11G_STBY2)

? ? ? (ORACLE_HOME = /oracle/app/oracle/product/database/11g)

? ? ? (SID_NAME = ora11g2)

? ? )?

? ? (SID_DESC =

? ? ? (GLOBAL_DBNAME = ORA11G_STBY2_DGMGRL)

? ? ? (ORACLE_HOME = /oracle/app/oracle/product/database/11g)

? ? ? (SID_NAME = ora11g2)

? ? )?

????重啟監聽，使配置生效，檢查所有配置的靜態監聽生效

[oracle@oradb2 ~]$ lsnrctl stop

[oracle@oradb2 ~]$ lsnrctl start

[oracle@oradb2 ~]$ lsnrctl status

Services Summary...

Service "ORA11G_STBY" has 2 instance(s).

? Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...

Service "ORA11G_STBY2" has 2 instance(s).

? Instance "ora11g2", status UNKNOWN, has 1 handler(s) for this service...

Service "ORA11G_STBY2_DGMGRL" has 1 instance(s).

? Instance "ora11g2", status UNKNOWN, has 1 handler(s) for this service...

Service "ORA11G_STBY_DGMGRL" has 1 instance(s).

? Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...

????新增新備庫tns配置信息。

[oracle@oradb2 admin]$ vi tnsnames.ora [主庫也需要新增該配置]

ORA11G_STBY2 =

? (DESCRIPTION =

? ? (ADDRESS_LIST =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = oradb2)(PORT = 1521))

? ? )

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = ORA11G_STBY2)

? ? )

? )

3.4 測試數據庫連接 [主庫和新備庫都需要進行測試]

[oracle@oradb2 ~]$ sqlplus sys/oracle@ORA11G_STBY2 as sysdba

[oracle@oradb2 ~]$ sqlplus sys/oracle@ORA11G_PRIM as sysdba? ??

3.5 新備庫進行復制主庫操作

????注意此處連接到新的備庫。

[oracle@oradb2 ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

[oracle@oradb2 ~]$ rman target sys/oracle@ORA11G_PRIM auxiliary sys/oracle@ORA11G_STBY2

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

3.6 啟動新備庫數據庫并啟動實時恢復

[oracle@oradb2 ~]$ export ORACLE_SID=ora11g2

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

4.將新備庫添加到dg broker中

[oracle@oradb2 ~]$ export ORACLE_SID=ora11g2

SQL> alter system set dg_broker_start=true;

[oracle@oradb2 ~]$ dgmgrl /

DGMGRL> show configuration;

DGMGRL> ADD DATABASE 'ORA11G_STBY2' AS CONNECT IDENTIFIER IS 'ORA11G_STBY2' MAINTAINED AS PHYSICAL;

DGMGRL> ENABLE DATABASE 'ORA11G_STBY2';

5. 進行主備切換操作

????通過DG Broker管理的DG環境，在進行主備切換過程中，參數會自動進行維護，例如fal_server，log_archive_dest_2，log_archive_dest_3等。

DGMGRL> show configuration;

Configuration - ora11g_dg

? Protection Mode: MaxPerformance

? Databases:

? ? ORA11G_PRIM? - Primary database

? ? ORA11G_STBY? - Physical standby database

? ? ORA11G_STBY2 - Physical standby database

DGMGRL> connect sys/oracle@ORA11G_STBY

DGMGRL> SWITCHOVER TO 'ORA11G_STBY';

????切記在switchover時，使用用戶名和口令連接到dgmgrl，否則會造成切換后，無法啟動遠程數據庫，導致切換失敗。