【DG】搭建（一）
DG:
DG是dataguard，也叫standby，是oracle提供的一種容災解決方案，只有企業版可用，標準版是不能用的，DG最多可以有一個主節點，9個從節點。可分為邏輯和物理兩類，這裡注意區分一下，邏輯的是透過redo轉換成SQL語句，然後再standby上執行該SQL語句實現的同步，物理standby是接受主節點的redo資料後，以介質恢復的方式進行同步，這是這兩者的本質區別。
DG的三種模式：
最大保護：主節點事務提交前，redo寫入線上日誌，而且從節點也要寫入到standby redolog中，並且保證在從庫中至少一個節點可用，在主節點才提交事務，注意，最大保護模式，從節點故障，主庫會被shutdown。
最高效能：這種模式，主節點隨時提交事務，事務提交時，redo至少寫入一個從節點，但是寫入不一定同步。
最高可用性：這種模式和最大保護有點類似，不同的是，從庫故障，主庫不會shutdown,而是轉為最高效能模式，從庫恢復後，轉回最高可用性模式。
物理standby只能read only開啟，此時只接受redo，不應用redo。應用redo，就不能開啟。11G，可以以open read only模式開啟，繼續應用redo.所以大部分時間，物理standby都是在mount狀態。
邏輯standby正常情況下就是read write模式，而且由於是應用sql語句實現同步，所以物理結構可以不一致。
硬體以及作業系統需求：
主庫和從庫執行的作業系統平臺必須相同，版本可以有差異，資料庫安裝路徑可以不同。主從伺服器的配置差異最好不要太大，要不切換角色的時候，配置差的切換後，效能會有影響。
主庫必須是歸檔模式，並且force logging模式，從庫可以是非force logging。一個主節點，可以有邏輯從節點，可以有物理從節點，但是時區和時間設定必須一致，否則同步會出現問題。
RFS（remote file server）：執行在備庫上的程式，用於在備庫上進行主庫的日誌恢復。預設，這個程式用於接收從主庫傳送過來的歸檔日誌。
當物理備庫啟用了 Redo Apply時，備庫可以透過實時應用這個特性，直接使用這個程式從 standby redo log 中直接應用 redo 日誌。
LNSn：LGWR觸發以後真正負責傳輸的程式，包括初始化網路I/O等一些列功能。
MRP：managed recovery process，簡單來說就是物理standby是透過這個程式來實現資料的同步的，直接透過standby redo log或者是歸檔日誌（取決於模式不同）來進行的一個資料恢復。
LSP:logical standby process：邏輯standby的方式，和上面的一樣，只不過當中多了一步將redo資訊轉換成sql語句再恢復。也可以從這裡看出邏輯standby和物理standby的不同。
1、準備條件：
歸檔模式;
監聽引數：local_listener 預設是1521
關閉閃回---->資料庫的bug,備庫不能開閃回
如果有外部表，外部表也要刪除
2、主要修改引數檔案---->增加dg屬性引數
一般是在主庫修改完，需要根據引數檔案建立不存在的目錄，驗證；
開啟force logging，增加standby logfile
再把引數檔案和口令檔案傳輸到備庫，更改環境變數，再次修改備庫的引數檔案及根據引數檔案建立不存在的目錄，最後驗證；
3、配置監聽網路：
需要配置主庫和備庫的靜態監聽，並且能夠互相訪問
備庫啟到Nomount時，檢視監聽狀態，會有blocked狀態。
4、複製主庫生成備庫：
11g-->rman 執行duplicate複製命令就可以完成複製主庫生成備庫操作
10g-->冷備恢復、rman備份集恢復
實驗之搭建DG如下：
1. 準備工作
Linux虛擬機器：兩臺
|
主備 |
虛擬機器名稱 |
主機名 |
IP地址 |
db_name |
db_unique_name |
備註 |
|
主庫 |
wxb |
wang |
192.168.10.2 |
ORA11GR2 |
ORA11GR2 |
|
|
備庫 |
www |
bing |
192.168.10.3 |
ORA11GR2 |
OCMU |
Oracle資料庫只安裝了軟體 |
DB_NAME,在DG所以節點中必須要一樣，而DB_UNIQUE_NAME引數值則是區分每個節點例項的唯一標識！！
——檢視是否開啟閃回資料庫功能，必須設為NO，如果開啟，備庫以後不能應用閃回資料庫功能（大bug），預設是NO的狀態。
SYS@ORA11GR2>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
——檢查引數local_listener，其值為找1521的名為LISTENER的監聽，如果埠號及監聽名不是LISTENER，需要修改此引數，預設值為空；
SYS@ORA11GR2>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
local_listener string
SYS@ORA11GR2>
注:如果主庫有外部表，必須將外部表刪除；
2. 主庫資料庫歸檔並強制生成日誌
[oracle@wang ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 20:13:16 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
——檢查主庫是否處於歸檔，必須設定為歸檔
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
——開啟主庫的force logging 模式：（預設是關閉的）
SYS@ORA11GR2>select force_logging from v$database;
FOR
---
NO
SYS@ORA11GR2>alter database force logging;
Database altered.
SYS@ORA11GR2>select force_logging from v$database;
FOR
---
YES
預設情況下資料庫操作會記錄redo log，但是在一些特定的情況下可以使用nologging來不生成redo資訊
(1)表的批次INSERT（透過/*+APPEND */提示使用“直接路徑插入“。或採用SQL*Loader直接路徑載入）,表資料不生成redo，但是所有索引修改會生成redo（儘管表不生成日誌，但這個表上的索引卻會生成redo！）。
(2)LOB操作（對大物件的更新不必生成日誌）。
(3)透過CREATE TABLE AS SELECT建立表
(4)各種altere table操作，如move和split
(5)在一些表遷移和表空間遷移中，可以使用alter table a nologging;或者alter tablespace snk nologging;在操作完成後再修改回logging狀態
3. 主庫建立standby logfile
主庫增加standby database 日誌組（此步操作是為主切備而進行的，且要求備庫日誌組要比主庫日誌多一組，並且大小要一樣）
——先檢視主庫日誌組數，及日誌路徑及日誌組大小：
SYS@ORA11GR2>select group#,member from v$logfile order by 1;
GROUP# MEMBER
---------- ---------------------------------------------
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
SYS@ORA11GR2>
SYS@ORA11GR2>select group#,bytes/1024/1024 m from v$log;
GROUP# M
---------- ----------
1 50
2 50
3 50
——在主庫上建立standby redo 日誌組：
SYS@ORA11GR2>alter database add standby logfile group 4('/u01/app/oracle/oradata/ORA11GR2/standbyredo04_a.log','/u01/app/oracle/oradata/ORA11GR2/standbyredo04_b.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 5('/u01/app/oracle/oradata/ORA11GR2/standbyredo05_a.log','/u01/app/oracle/oradata/ORA11GR2/standbyredo05_b.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 6('/u01/app/oracle/oradata/ORA11GR2/standbyredo06_a.log','/u01/app/oracle/oradata/ORA11GR2/standbyredo06_b.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/standbyredo07_a.log','/u01/app/oracle/oradata/ORA11GR2/standbyredo07_b.log') size 50m;
Database altered.
——驗證建立的日誌組：
SYS@ORA11GR2>select group#,thread#,sequence#,bytes/1024/1024 m,status from v$standby_log;
GROUP# THREAD# SEQUENCE# M STATUS
---------- ---------- ---------- ---------- -----------------------------------------------------------
4 0 0 50 UNASSIGNED
5 0 0 50 UNASSIGNED
6 0 0 50 UNASSIGNED
7 0 0 50 UNASSIGNED
SYS@ORA11GR2>col type for a10
SYS@ORA11GR2>col MEMBER for a55
SYS@ORA11GR2>select group#,type,member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ---------- -------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo03.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo04_a.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo04_b.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo05_a.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo05_b.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo06_a.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo06_b.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo07_a.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standbyredo07_b.log
11 rows selected.
注：一定要確認備庫日誌組比主庫日誌組多一組：
4. 主庫配置靜態監聽及作為客戶端的tnsnames
——配置主庫的靜態監聽：
[oracle@wang ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@wang admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@wang admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521)))
)
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=ORA11GR2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=ORA11GR2))
)
~
"listener.ora" 19L, 453C written
——配置好靜態監聽重啟一下監聽，以載入靜態監聽：
[oracle@wang admin]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:38:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
The command completed successfully
[oracle@wang admin]$
[oracle@wang admin]$ lsnrctl start（此時靜態已經起來了）
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:38:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/wang/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 20:38:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wang/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))
Services Summary...
Service "ORA11GR2" has 1 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@wang admin]$
——動態監聽註冊慢一點（1分鐘左右），也可以手動註冊動態監聽（alter system register;）
再次檢視監聽狀態：（動態監聽也起來了）
[oracle@wang admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:40:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 20:38:35
Uptime 0 days 0 hr. 1 min. 40 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wang/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))
Services Summary...
Service "ORA11GR2" has 2 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
Service "ORA11GR2XDB" has 1 instance(s).
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@wang admin]$
——配置主庫作為客戶端對備庫訪問的連線字串：
[oracle@wang admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA11GR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11GR2)
)
)
ocm=
(description=
(address=(protocol=tcp)(host=192.168.10.3)(port=1521))
(connect_data=
(server=dedicated)
(service_name=OCMU)
)
)
~
~
"tnsnames.ora" 20L, 501C written
[oracle@wang admin]$
[oracle@wang admin]$ tnsping ocm (測試一下是否暢通)
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:45:53
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (address=(protocol=tcp)(host=192.168.10.3)(port=1521)) (connect_data= (server=dedicated) (service_name=OCMU)))
OK (0 msec)
[oracle@wang admin]$
5. 備庫配置靜態監聽及作為客戶端的tnsnames
——設定環境變數：
[oracle@bing ~]$ export ORACLE_SID=OCMU
[oracle@bing ~]$ echo $ORACLE_SID
OCMU
[oracle@bing ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@bing admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
——設定備庫的靜態監聽：
[oracle@bing admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bing)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=OCMU)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=OCMU))
)
~
"listener.ora" 19L, 477C written
[oracle@bing admin]$ lsnrctl stop（同理）
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:51:11
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
The command completed successfully
[oracle@bing admin]$ lsnrctl start (同理，靜態監聽已經起來了，動態監聽雖然也配了，但是例項還沒有啟動)
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:51:21
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/bing/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 20:51:21
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/bing/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))
Services Summary...
Service "OCMU" has 1 instance(s).
Instance "OCMU", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@bing admin]$
——配置備庫對於主庫訪問的tnsnames.ora,即連線字串：
[oracle@bing admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)
ora=
(description=
(address=(protocol=tcp)(host=192.168.10.2)(port=1521))
(connect_data=
(server=dedicated)
(service_name=ORA11GR2)
)
)
~
"tnsnames.ora" 20L, 505C written
[oracle@bing admin]$
[oracle@bing admin]$ tnsping ora(測試一下是否暢通)
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 20:58:01
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (address=(protocol=tcp)(host=192.168.10.2)(port=1521)) (connect_data= (server=dedicated) (service_name=ORA11GR2)))
OK (10 msec)
[oracle@bing admin]$
6. 主庫修改引數增加DG相關的屬性引數：
6.1. 生成pfile
SYS@ORA11GR2>create pfile from spfile;
File created.
SYS@ORA11GR2>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wang ~]$
6.2. 修改引數
[oracle@wang ~]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ vi initORA11GR2.ora
ORA11GR2.__db_cache_size=373293056
ORA11GR2.__java_pool_size=4194304
ORA11GR2.__large_pool_size=8388608
ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA11GR2.__pga_aggregate_target=289406976
ORA11GR2.__sga_target=545259520
ORA11GR2.__shared_io_pool_size=0
ORA11GR2.__shared_pool_size=146800640
ORA11GR2.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11GR2,OCMU)'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11GR2,OCMU)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/ORA11GR2/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_2=
'SERVICE=ocm ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=OCMU'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=OCMU
DB_FILE_NAME_CONVERT='OCMU','ORA11GR2'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/OCMU/','/u01/app/oracle/oradata/ORA11GR2/'
STANDBY_FILE_MANAGEMENT=AUTO
~
~
"initORA11GR2.ora" 47L, 1565C written
[oracle@wang dbs]$
——根據上述引數檔案建立沒有的目錄：
[oracle@wang dbs]$ mkdir -p /u01/arch1/ORA11GR2/
[oracle@wang dbs]$
6.3. 驗證修改過的pfile，透過pfile重新生成spfile並啟動資料庫
[oracle@wang admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 21:31:10 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>create spfile from pfile;
File created.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileORA11GR2.ora
7. 複製相關檔案至備庫（即引數檔案、密碼檔案）
[oracle@wang dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initORA11GR2.ora orapwORA11GR2
init.ora lkORA11GR2 spfileORA11GR2.ora
[oracle@wang dbs]$ scp initORA11GR2.ora 192.168.10.3:$ORACLE_HOME/dbs/initOCMU.ora
The authenticity of host '192.168.10.3 (192.168.10.3)' can't be established.
RSA key fingerprint is 46:2d:74:b7:54:f9:5f:4b:13:7d:e0:0f:5a:a4:92:2f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.10.3' (RSA) to the list of known hosts.
oracle@192.168.10.3's password:
initORA11GR2.ora 100% 1762 1.7KB/s 00:00
[oracle@wang dbs]$
[oracle@wang dbs]$ scp orapwORA11GR2 192.168.10.3:$ORACLE_HOME/dbs/orapwOCMU
oracle@192.168.10.3's password:
orapwORA11GR2 100% 1536 1.5KB/s 00:00
[oracle@wang dbs]$
——到備庫去驗證：
[oracle@bing admin]$ cd $ORACLE_HOME/dbs/
[oracle@bing dbs]$ ls orapwOCMU initOCMU.ora
initOCMU.ora orapwOCMU
8. 備庫引數修改增加DG相關的屬性引數
8.1. 修改備庫pfile
[oracle@bing dbs]$ vi initOCMU.ora
OCMU.__db_cache_size=373293056
OCMU.__java_pool_size=4194304
OCMU.__large_pool_size=8388608
OCMU.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
OCMU.__pga_aggregate_target=289406976
OCMU.__sga_target=545259520
OCMU.__shared_io_pool_size=0
OCMU.__shared_pool_size=146800640
OCMU.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/OCMU/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/OCMU/control01.ctl','/u01/app/oracle/oradata/OCMU/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OCMUXDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=OCMU
LOG_ARCHIVE_CONFIG='DG_CONFIG=(OCMU,ORA11GR2)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/OCMU/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OCMU'
LOG_ARCHIVE_DEST_2=
'SERVICE=ora ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=ORA11GR2
DB_FILE_NAME_CONVERT='ORA11GR2','OCMU'
LOG_FILE_NAME_CONVERT=
'/u01/app/oracle/oradata/ORA11GR2/','/u01/app/oracle/oradata/OCMU/'
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@bing dbs]$
——根據上述引數檔案建立不存在的目錄：
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/OCMU/adump
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/OCMU/
[oracle@bing dbs]$ mkdir -p /u01/app/FRA
[oracle@bing dbs]$ mkdir -p /u01/arch1/OCMU/
8.2. 驗證，備庫透過pfile生成spfile並啟動資料庫至nomount
[oracle@bing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 21:51:37 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileOCMU.ora
——此時查驗備庫監聽狀態，動態監聽為block狀態：
（因為確實庫沒有開，監聽是由例項告訴其資料庫的狀況）
[oracle@bing admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 21:54:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 20:55:57
Uptime 0 days 0 hr. 58 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/bing/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))
Services Summary...
Service "OCMU" has 2 instance(s).
Instance "OCMU", status UNKNOWN, has 1 handler(s) for this service...
Instance "OCMU", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
9. 使用RMAN auxiliary恢復資料庫（在主庫上操作）
主庫為open狀態，備庫為nomount
監聽為啟動狀態
[oracle@wang dbs]$ rman target / auxiliary sys/oracle@ocm
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 20 22:17:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (DBID=237843809)
connected to auxiliary database: ORA11GR2 (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 20-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwOCMU' ;
}
executing Memory Script
Starting backup at 20-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=83 device type=DISK
Finished backup at 20-OCT-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/OCMU/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/OCMU/control02.ctl' from
'/u01/app/oracle/oradata/OCMU/control01.ctl';
}
executing Memory Script
Starting backup at 20-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORA11GR2.f tag=TAG20161020T221739 RECID=2 STAMP=925769860
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-OCT-16
Starting restore at 20-OCT-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 20-OCT-16
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/OCMU/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/OCMU/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/OCMU/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/OCMU/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/OCMU/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/OCMU/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/OCMU/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/OCMU/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/OCMU/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/OCMU/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/OCMU/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/OCMU/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 20-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
output file name=/u01/app/oracle/oradata/OCMU/system01.dbf tag=TAG20161020T221747
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
output file name=/u01/app/oracle/oradata/OCMU/sysaux01.dbf tag=TAG20161020T221747
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
output file name=/u01/app/oracle/oradata/OCMU/example01.dbf tag=TAG20161020T221747
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
output file name=/u01/app/oracle/oradata/OCMU/undotbs01.dbf tag=TAG20161020T221747
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
output file name=/u01/app/oracle/oradata/OCMU/users01.dbf tag=TAG20161020T221747
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-OCT-16
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=925769910 file name=/u01/app/oracle/oradata/OCMU/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=925769910 file name=/u01/app/oracle/oradata/OCMU/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=925769910 file name=/u01/app/oracle/oradata/OCMU/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=925769910 file name=/u01/app/oracle/oradata/OCMU/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=925769910 file name=/u01/app/oracle/oradata/OCMU/example01.dbf
Finished Duplicate Db at 20-OCT-16
成功，此步驟完成，標誌DG搭建完成，剩下的就是備庫應用日誌，和主庫保持一致了！！！！
10. 備庫同步資料
——檢查備庫狀態：（由nomount自動轉到mount）
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
——檢視備庫狀態：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 NO
19 NO (備庫未應用日誌)
——應用日誌：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>
SQL> set lines 100
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 IN-MEMORY
SQL> recover managed standby database cancel;（結束應用日誌）
Media recovery complete.
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
——檢視主庫狀態：
SYS@ORA11GR2>select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ WRITE MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE
SYS@ORA11GR2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
19
SYS@ORA11GR2>alter system switch logfile;（切換日誌，則歸檔日誌）
System altered.
SYS@ORA11GR2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
20
——主庫有新的歸檔產生，檢視備庫日誌：
SQL> select sequence#,applied from v$archived_log;
（新傳輸過來的20號日誌還沒有應用）
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 NO
——應用日誌：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
——再次檢視狀態：
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 IN-MEMORY（正在應用）
——結束應用日誌：
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
SQL> select name,open_mode,protection_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- -------------------- ---------------- --------------------
ORA11GR2 READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES (日誌應用結束)
主備一致！！！！！！！！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126909/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DG搭建
- DG搭建配置方案
- Oracle RAC+DG搭建Oracle
- DG RAC - 單點搭建
- RMAN不停機搭建DG
- ora11_node_dg（1）DG搭建過程
- 一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九)
- 物理DG、邏輯DG和快照DG的搭建（視訊講解）
- 一步一步搭建11gR2 rac+dg之DG 機器配置(七)
- 【DG】Data Guard搭建（physical standby）
- dg搭建 單點-單點
- Oracle DG搭建1(duplicate方式)Oracle
- ORACLE DG 11G 搭建Oracle
- 【DG】Oracle 19c使用dbca來搭建物理DGOracle
- 一步一步搭建11gR2 rac+dg之配置單例項的DG(八)單例
- 一次DG搭建過程中碰到的問題
- 【DG】備庫RMAN還原方式搭建DG（不使用duplicate命令）
- 11G RAC+DG搭建
- 在dg庫上搭建ogg
- Oracle DG搭建2(冷備方式)Oracle
- 搭建RAC到單例項DG單例
- rman duplicate搭建第二個 dg
- FAILOVER後DG的重新搭建AI
- 【DG】Oracle之級聯DG--(cascade dg) --(一主一備一級聯)Oracle
- 一步一步搭建 oracle 11gR2 rac + dg 之前傳 （一）Oracle
- 【DG】搭建（二）及相關測試
- oracle11g 搭建 rac+dgOracle
- oracle 11g dg搭建筆記Oracle筆記
- oracle DG支援搭建的平臺列表Oracle
- 一步一步搭建Oracle 11g RAC+ DG詳解Oracle
- CentOS 5.8上搭建10g物理DGCentOS
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- Oracle 11g單主搭建物理DGOracle
- 11g dg 備庫搭建多種方式
- duplicate搭建DG最大效能（rac-單例項）單例
- linux-oracle11g-dg搭建日誌LinuxOracle
- 【DATAGUARD】DG系列之RACtoONE物理備庫的搭建
- DG_安裝一