Oracle Linux7下部署oralce 11gr2 dataguard

hongxiang22355發表於2022-12-05

oralce 11gr2 dataguard 測試遷移-by yuhongxiang E-mail: 15005002056@139.com

oracle11gR2 部署DataGuard

1.1測試環境說明

* 作業系統 oracle linux 7.6

* 虛擬化軟體系統 vmware workstion 17
* 資料庫版本 oracle database 11g R2
* 主機名 dg11g.dgtest1.com  dg11g.dgtest2.com
* ip 規劃 192.0.2.21 (DG1) 192.0.2.12(DG2)
* Home目錄規劃  ORACLE_BASE=/u01/app/oracle                ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/  
* 單臺vmware 虛擬機器配置 2C 4G
* 主庫備庫目錄一致

1.2 部署oracle databas 11gR2

  1 部署後按照資料庫軟體和dbca建立資料庫例項 orcl

 2 同理部署第二臺作業系統和安裝資料庫軟體(不需要建立例項)

1.2.1 準備工作

1.2.1.1 檢視記憶體
  grep SwapTotal /proc/meminfo

 grep MemTotal /proc/meminfo
1.2.1.2 安裝缺失的軟體包
 yum install -y libstdc* libaio* make* sysstat*
1.2.1.3 修改引數
cat /proc/sys/net/ipv4/ip_local_port_range

echo 9000 65500 > /proc/sys/net/ipv4/ip_local_port_range
1.2.1.3 建立使用者組和使用者,目錄
# /usr/sbin/groupadd oinstall

# /usr/sbin/groupadd -g 502 dba
# /usr/sbin/useradd -u 502 -g oinstall -G dba  oracle
# passwd oracle
# /usr/sbin/usermod -g oinstall -G dba oracle
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle
# chmod -R 775 /u01/app/oracle

1.3 主庫配置

1.3.1  主庫配置dataguard引數
alter database archivelog;

alter system set standby_file_management=auto;
alter system set dg_broker_start=true;
alter system set temp_undo_enabled=true;
alter system set local_listener='';
1.3.2 主庫修改db_unique為orcl1
alter system set db_unique_name=orcl1 scope=spfile;
1.3.3 開歸檔,開閃回,強記日誌
alter database flashback on;

alter database force logging;
alter database open;
select log_mode,flashback_on,force_logging from v$database;
1.3.4 新增standby 日誌檔案
col member for a60;

select group #,member from v$logfile;
SQL> select group#,bytes/1024/1024 Mb from v$log;

   GROUP#       MB
---------- ----------
    1       50
    2       50
    3       50
alter database add standby logfile group 4 '/u01/app/oracle/oradata/PROD5/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/PROD5/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/PROD5/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/PROD5/redo07.log' size 50m;
檢視
SQL> select group#,member from v$logfile;

   GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
    3
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_3_krkr8b9r_.log

    2
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_2_krkr89oh_.log

    1
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_1_krkr8977_.log


   GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
    4
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_4_krkr8bsl_.log

    5
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_5_krkr8cd9_.log

    6
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_6_krkr8co8_.log


   GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
    7
/u01/app/oracle/flash_recovery_area/ORCL2/onlinelog/o1_mf_7_krkr8cyh_.log


1.3.5 複製主庫檔案到備庫主機

主庫生成pfile檔案

greate pfile from spfile;

複製pfile檔案,密碼檔案到備用節點主機.

SCP /u01/app/oracle/product/11.2.0/dbhome_1/dbs initorcl.ora orapworcl 192.0.2.12:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

1.4 備庫配置

1.4.1修改pfile檔案

oracle@dg11 dbs]$ cat initorcl.ora 

orcl.__db_cache_size=620756992
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=620756992
orcl.__sga_target=922746880
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=251658240
orcl.__streams_pool_size=0
*.audit_file_dest='oracle@dg11 dbs]$ cat initorcl.ora
orcl.__db_cache_size=620756992
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=620756992
orcl.__sga_target=922746880
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=251658240
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain='test1.com'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='ORCL2' 修改此引數ORCL2
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1542455296
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

1.4.2 建立備庫相關路徑

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/flash_recovery_area/orcl/
mkdir -p /u01/app/oracle/flash_recovery_area/

1.4.3 備庫建立Spfile檔案

 create spfile from pfile

1.4.4 備庫啟動到nomount模式

startup nmount

1.5 配置監聽

1.5.1 靜態靜聽(主備庫)

1.5.1.1 備庫配置
LISTENER =

 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521))
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_listener=
(SID_LIST=
 (SID_DESC=
   (GLOBAL_DBNAME=ORCL2.test2.com)
   (SID_NAME=orcl)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
 )
 (SID_DESC=
  (GLOBAL_DBNAME=ORCL2_DGMGRL.test2.com)
  (SID_NAME=orcl)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
 )
)
1.5.1.2 主庫配置
LISTENER =

 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521))
   )
 )

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_listener=
(SID_LIST=
 (SID_DESC=
   (GLOBAL_DBNAME=ORCL1.test1.com)
   (SID_NAME=orcl)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
 )
 (SID_DESC=
  (GLOBAL_DBNAME=ORCL1_DGMGRL.test1.com)
  (SID_NAME=orcl)
  (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
 )
)

1.5.2 動態靜聽配置

1.5.2.1 主庫配置
ORCL =

 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl.test1.com)
   )
 )

ORCL1 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL1.test1.com)
   )
 )

ORCL2 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL2.test2.com)
   )
 )

1.5.2.2 備庫配置
ORCL =

 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = orcl.test1.com)
   )
 )

ORCL1 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test1.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL1.test1.com)
   )
 )

ORCL2 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dg11.test2.com)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL2.test2.com)
   )
 )

1.6 Rman恢復備庫

connect sys/oracle@orcl1 auxiliary database sys/oracle@orcl2

DUPLICATE TARGET DATABASE
 FOR STANDBY
 FROM ACTIVE DATABASE
 DORECOVER  NOFILENAMECHECK;

1.7 配置DG_BROKER

1.7.1 配置主庫配置

dgmgrl connect sys/oracle@orcl1

CREATE CONFIGURATION DG11 AS
   PRIMARY DATABASE IS orcl1
   CONNECT IDENTIFIER IS orcl1

1.7.2 配置備庫配置

ADD DATABASE orcl2

   AS CONNECT IDENTIFIER IS orcl2

1.7.3 檢查配置

DGMGRL> show configuration


Configuration - dg11

 Protection Mode: MaxAvailability
 Databases:
   orcl2 - Primary database
   orcl1 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

DGMGRL>

1.8 測試主備切換

1.8.1 檢查備庫情況

SQL> select PROTECTION_MODE,SWITCHOVER_STATUS,GUARD_STATUS,OPEN_MODE from v$database;                    


PROTECTION_MODE      SWITCHOVER_STATUS   GUARD_S OPEN_MODE
-------------------- -------------------- ------- --------------------
MAXIMUM PERFORMANCE  NOT ALLOWED   NONE   READ ONLY WITH APPLY

1.8.2 測試主庫情況




SQL> select PROTECTION_MODE,SWITCHOVER_STATUS,GUARD_STATUS,OPEN_MODE from v$database;

PROTECTION_MODE      SWITCHOVER_STATUS   GUARD_S OPEN_MODE
-------------------- -------------------- ------- --------------------
MAXIMUM AVAILABILITY TO STANDBY    NONE   READ WRITE

1.8.3 測試切換

檢視現在狀態

DGMGRL> show configuration

Configuration - dg11

 Protection Mode: MaxAvailability
 Databases:
   orcl1 - Primary database
   orcl2 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS
切換主備庫
DGMGRL> switchover to orcl2
Performing switchover NOW, please wait...
New primary database "orcl2" is opening...
Operation requires shutdown of instance "orcl" on database "orcl1"
Shutting down instance "orcl"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl1"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "orcl2"

檢視最終狀態
DGMGRL> show configuration;

Configuration - dg11

 Protection Mode: MaxAvailability
 Databases:
   orcl2 - Primary database
   orcl1 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

1.9 配置自動故障切換

1.9.1 開啟observer

DGMGRL> startup observer

1.9.2 配置faill-over

 ENABLE FAST_START FAILOVER

1.9.3 檢視配置狀態

DGMGRL> show configuration


Configuration - dg11

 Protection Mode: MaxAvailability
 Databases:
   orcl2 - Primary database
   orcl1 - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:
SUCCESS

1.10 部署遇到的問題

1.10.1 Question 1

Linux centos7安裝Oracle 11g 報錯解決方案

問題 

Error in invoking target ‘install’ of makefile ‘/opt/oracle/11g/ctx/lib/ins_ctx.mk’. See ‘/opt/oraInventory/logs/installActions2016-08-07_02-58-49AM.log’ for details.
檢視日誌後注意到日誌有以下錯誤:
INFO: /lib64/libstdc++.so.5: undefined reference to `memcpy@GLIBC_2.14'
INFO: collect2: error: ld returned 1 exit status
解決方案:
在makefile中新增連結libnnz11庫的引數
修改$ORACLE_HOME/sysman/lib/ins_emagent.mk,將
$(MK_EMAGENT_NMECTL)修改為:$(MK_EMAGENT_NMECTL) -lnnz11
建議修改前備份原始檔案
[oracle@ysserver ~]$ cd $ORACLE_HOME/sysman/lib
[oracle@ysserver lib]$ cp ins_emagent.mk ins_emagent.mk.bak
[oracle@ysserver lib]$ vi ins_emagent.mk
進入vi編輯器後 命令模式輸入/NMECTL 進行查詢,快速定位要修改的行
在後面追加引數-lnnz11 第一個是字母l 後面兩個是數字1

1.10.2 Question 2

RMAN-05541: no archived logs found in target database

RMAN> duplicate target databaseto orcl from active database nofilenamecheck;


Starting Duplicate Db at 23-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/201510:57:12
RMAN-05541: no archived logs found in target database
出現這種錯誤很可能是你剛剛將主資料庫切換為歸檔模式然後緊接著就利用RMAN的duplicate建立standby資料庫導致的。原因就在於主資料庫切換為歸檔模式之後還未發生過切換,手動在主資料庫上切換一次就可以了
SYS@orcl>alter system switch logfile;

1.10.3 Question 3

ORA-16826: apply service state is inconsistent

SOLUTION

Workaround :
Check on which node MRP process is running.
On Standby
Make sure  Standby redo logs files are created on standby.
Please reference Interaction Between the Data Guard Broker and a Data Guard Configuration Doc ID 249703.1
DGMGRL> edit database  set state=apply-off;
DGMGRL> edit database  set state=apply-on;
or
SQL>alter database recover managed standby database cancel;
SQL> alter database recover managed standby database using current logfile disconnect;
Verify Broker Configuration
DGMGRL> show configuration  
Configuration - orcl1_site  
 Protection Mode: MaxPerformance
 Databases:
   orcl1   - Primary database
   dgorcl1 - Physical standby database  
Fast-Start Failover: DISABLED  
Configuration Status:
**SUCCESS                         <<<<<<<<<<<<<<<<<<
**
DGMGRL> show database dgorcl1  
Database - dgorcl1  
 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: OFF
 Instance(s):
   dgorcl11 (apply instance)
   dgorcl12**  
Database Status:
SUCCESS  <<<<<<<<<<<<<<<<<<<<<<**
How to avoid this issue

1. This Problem occurs if Managed Recovery is started manually which should not be done at all if there is an active Data Guard Broker Configuration**.
**
   Typically the Data Guard Broker should start Managed Recovery .  
2. It can also happen if there are no Standby Redo Logs in Place or they are incorrect configured - reference  Usage, Benefits and Limitations of Standby Redo Logs (SRL) Doc ID 219344.1



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

相關文章