dataguard 搭建 oracle_sid相同 2節點 primary+standby

	primary 庫	standby 庫
Ip	10.8.98.103	10.8.98.104
hostname	dbserver01	dbserver02
Oracle_sid	prod1	prod1
資料庫版本	11.2.0.4	11.2.0.4
Db_name	prod	prod
Db_unique_name	prodpri	proddg
tnsnames	tns_primary	tns_standby

主機安裝oracle，備機只需要安裝資料庫軟體，不需要建庫。

1、主機設定歸檔模式

SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       /oracle/app/oracle/oradata/prod/arch
Oldest online log sequence     60
Next log sequence to archive   62
Current log sequence	       62

主機開啟force logging 模式

SQL> alter database force logging;

2、主機新增standby log

alter database add standby logfile group 21('/oracle/app/oracle/oradata/prod/redo21_standby.log') size 50M;
alter database add standby logfile group 22('/oracle/app/oracle/oradata/prod/redo22_standby.log') size 50M;
alter database add standby logfile group 23('/oracle/app/oracle/oradata/prod/redo23_standby.log') size 50M;

3、建立主備庫的引數檔案，lisenter.ora,tnsnames.ora,並複製主機上密碼檔案到備機

主庫

[oracle@dbserver01 dbs]$ cat initprod1.ora 
prod1.__db_cache_size=1358954496
prod1.__java_pool_size=16777216
prod1.__large_pool_size=603979776
prod1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
prod1.__pga_aggregate_target=1610612736
prod1.__sga_target=2415919104
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=402653184
prod1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/prod/control01.ctl','/oracle/app/oracle/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod1XDB)'
*.memory_target=4017094656
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
#dg add
*.DB_UNIQUE_NAME=prodpri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prodpri,proddg)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/prod/arch  valid_for=(all_logfiles,all_roles)  db_unique_name=prodpri'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_standby LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_standby
*.FAL_CLIENT=tns_primary
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=30
*.db_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
*.log_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'

lisenter.ora

[oracle@dbserver01 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
      (SID_NAME = prod1))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.103)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@dbserver01 admin]$

tnsnames.ora( 主機備機相同)

[oracle@dbserver01 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
tns_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
tns_standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.104)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prod)
    )
  )
[oracle@dbserver01 admin]$

備機

初始化引數

[oracle@dbserver02 dbs]$ cat initprod1.ora 
prod1.__db_cache_size=1929379840
prod1.__java_pool_size=16777216
prod1.__large_pool_size=33554432
prod1.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
prod1.__pga_aggregate_target=1610612736
prod1.__sga_target=2415919104
prod1.__shared_io_pool_size=0
prod1.__shared_pool_size=402653184
prod1.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/app/oracle/oradata/prod/control01.ctl','/oracle/app/oracle/oradata/prod/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod1XDB)'
*.memory_target=4017094656
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.undo_tablespace='UNDOTBS1'
#dg add
*.DB_UNIQUE_NAME=proddg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(prodpri,proddg)'
*.log_archive_format='ARC_%T_%S_%R.arc'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/app/oracle/oradata/prod/arch  valid_for=(all_logfiles,all_roles)  db_unique_name=proddg'
*.LOG_ARCHIVE_DEST_2='SERVICE=tns_primary LGWR  SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodpri'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=tns_primary
*.FAL_CLIENT=tns_standby
*.STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_max_processes=30
*.db_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
*.log_file_name_convert='/oracle/app/oracle/oradata/prod/','/oracle/app/oracle/oradata/prod/'
[oracle@dbserver02 dbs]$

lisenter.ora

[oracle@dbserver02 admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oracle/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
      (SID_NAME = prod1))
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.98.104)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /oracle/app/oracle
[oracle@dbserver02 admin]$

複製主機的密碼檔案到備機相應目錄

主機備機根據引數檔案initprod1.ora建立相應的資料夾

mkdir -p /oracle/app/oracle/admin/prod/adump
mkdir -p /oracle/app/oracle/oradata/prod/
mkdir -p /oracle/app/oracle/oradata/prod/arch

4、測試tnsping  ok，主備機都啟動到nomount狀態，均建立spfile

然後主機開啟資料庫，備機開啟到nomount狀態。

SQL> create spfile from pfile;

5、實施資料庫克隆透過rman duplicate (在備機操作)

[oracle@dbserver02 prod]$ rman target sys/oracle@tns_primary auxiliary sys/oracle@tns_standby
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Aug 27 15:04:42 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PROD (DBID=493196996)
connected to auxiliary database: PROD (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
恢復完成之後，開啟資料庫，並且開啟實時應用同步
SQL> alter database open;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

主機檢視狀態

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      FAILED DESTINATION   MAXIMUM PERFORMANCE

如果報

SWITCHOVER_STATUS   為   FAILED DESTINATION

重啟下主機的資料庫服務

6、檢視主機備機狀態，確認dataguard搭建完成。

主機

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ WRITE	     PRIMARY	      TO STANDBY	   MAXIMUM PERFORMANCE

備機

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE;
OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_LEVEL
-------------------- ---------------- -------------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED	   MAXIMUM PERFORMANCE

7、驗證

主機建立一個tablespace，一個賬戶，一個表，插入一條資料，到備機檢視

主機

SQL> create  tablespace test datafile '/oracle/app/oracle/oradata/prod/test01.dbf'  size 100m  autoextend on next 100m;
Tablespace created.
SQL> create user test identified by test default tablespace test;
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test
Enter password: 
Connected.
SQL> create table tt(id int);
Table created.
SQL> insert into tt values(10);
1 row created.
SQL> commit;
Commit complete.

備機查詢

SQL> conn test
Enter password: 
Connected.
SQL> select * from tt;
	ID
----------
	10
SQL>

如果文章對你有用，給點個贊吧。