容災技術Data Guard搭建
Oracle Data Guard容災技術是一種備份容災策略，簡稱DG。以下是DG的搭建過程：
準備條件：兩臺虛擬機器，一臺裝載主庫，另一臺裝載備庫（備庫為空庫，只有安裝好oracle軟體）
----主庫： PROD/IP:192.168.2.6
----備庫： PROD/IP:192.168.2.4 # 備庫名PROD（提前定義）要與主庫名一致。
--關閉主庫，從spfile建立pfile：
SQL> create pfile from spfile;
File created.
--在主庫修改引數檔案：
[oracle@enmo dbs]$ vi initPROD.ora
PROD.__db_cache_size=352321536
PROD.__java_pool_size=4194304
PROD.__large_pool_size=8388608
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=335544320
PROD.__sga_target=503316480
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=130023424
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump' #審計檔案目錄
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/PROD/ora_control1.ctl','/u01/app/oracle/oradata/PROD/ora_control2.ctl' #控制檔案物件
*.db_block_size=8192
*.db_domain='oracle.com' #domain名
*.db_name='PROD'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PROD #主庫唯一庫名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch/PROD/ #主庫本地歸檔日誌存放目錄
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_2=
'SERVICE=ENMO ASYNC #此處ENMO只是作為連線備庫ENMO庫的網路連結串（tnsnames)
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ENMO'
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=ENMO #備庫庫名
DB_FILE_NAME_CONVERT='ENMO','PROD'
LOG_FILE_NAME_CONVERT=
'/home/oracle/arch/ENMO/','/home/oracle/arch/PROD/' #備庫主庫存放日誌檔案目錄的交換，可以簡寫為：'ENMO','PROD'
STANDBY_FILE_MANAGEMENT=AUTO
--從pfile生成spfile，測試開啟主庫，並強制開啟force logging：
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL>
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
--檢視主庫的重做日誌檔案組：
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/redo01.log
2 /u01/app/oracle/oradata/PROD/redo02.log
3 /u01/app/oracle/oradata/PROD/redo03.log
--在主庫新增standby日誌組（新增規則:普通日誌檔案<=standby日誌檔案，且檔案大小對應一致：
SQL> alter database add standby logfile group 4
2 ('/u01/app/oracle/oradata/PROD/redo04_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo05_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 5
2 ('/u01/app/oracle/oradata/PROD/redo06_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo07_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 6
2 ('/u01/app/oracle/oradata/PROD/redo08_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo09_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 7
2 ('/u01/app/oracle/oradata/PROD/redo010_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo011_staby.log')
4 size 10M;
Database altered.
--增加後檢視所有的日誌檔案：
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/redo01.log
2 /u01/app/oracle/oradata/PROD/redo02.log
3 /u01/app/oracle/oradata/PROD/redo03.log
4 /u01/app/oracle/oradata/PROD/redo04_staby.log
4 /u01/app/oracle/oradata/PROD/redo05_staby.log
5 /u01/app/oracle/oradata/PROD/redo06_staby.log
5 /u01/app/oracle/oradata/PROD/redo07_staby.log
6 /u01/app/oracle/oradata/PROD/redo08_staby.log
6 /u01/app/oracle/oradata/PROD/redo09_staby.log
7 /u01/app/oracle/oradata/PROD/redo010_staby.log
7 /u01/app/oracle/oradata/PROD/redo011_staby.log
--從主庫複製pfile引數檔案與密碼檔案到備庫的主機上：
[oracle@enmo dbs]$ ls
hc_OCMU.dat initOCMU.ora init.ora.bck initPROD.ora.bck lkPROD snapcf_OCMU.f spfileOCMU.ora
hc_PROD.dat init.ora initPROD.ora lkORA11GR2 orapwPROD snapcf_PROD.f spfilePROD.ora
[oracle@enmo dbs]$ scp initPROD.ora orapwPROD 192.168.2.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@192.168.2.4's password:
initPROD.ora 100% 1432 1.4KB/s 00:00
orapwPROD 100% 1536 1.5KB/s 00:00
----配置靜態監聽，相互訪問：
---主庫PROD庫靜態監聽配置：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD))
)
--主庫PROD庫tns配置：
ENMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ENMO.oracle.com)
)
)
--主庫PROD主庫啟動並註冊監聽：
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 18-OCT-2016 16:40:46
Uptime 1 days 3 hr. 2 min. 57 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/enmo/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmo.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLXDB.oracle.com" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PROD.oracle.com" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully
--備庫ENMO庫靜態監聽配置：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ENMO.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORA11GR2 ))
)
--備庫ENMO庫tns配置:
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
)
)
--備庫ENMO備庫啟動並註冊監聽：
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 19:51:01
Uptime 0 days 0 hr. 2 min. 2 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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO.oracle.com" has 2 instance(s).
Instance "ENMO", status BLOCKED, has 1 handler(s) for this service...
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
監聽的配置與備庫引數檔案的先後順序沒有要求，這都是自己安排設計的。
--在備庫ENMO庫的引數檔案修改：
*.audit_file_dest='/u01/app/oracle/admin/enmo/adump' #備庫的審計檔案目錄
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/ENMO/ora_control1.ctl','/u01/app/oracle/oradata/ENMO/ora_control2.ctl' #備庫ENMO庫控制檔案物件
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='PROD' #備庫名與主庫名保持一致
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=ENMO #備庫的唯一庫名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch/ENMO/ #歸檔日誌檔案存放目錄
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD ASYNC #此處PROD只是作為連線備庫PROD庫的網路連結串（tnsnames)
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD'
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=PROD #備庫庫名
DB_FILE_NAME_CONVERT='PROD','ENMO'
LOG_FILE_NAME_CONVERT='PROD','ENMO'
STANDBY_FILE_MANAGEMENT=AUTO
在備庫修改pfile引數檔案後，從pfile檔案生成spfile檔案，並啟動例項到nomount狀態：
--在主庫複製檔案到備庫：
[oracle@enmo ~]$ rman target / auxiliary sys/oracle@enmo
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 19 20:22:10 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=336361349)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 19-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/orapwPROD' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENMO' ;
}
executing Memory Script
Starting backup at 19-OCT-16
... ...
executing Memory Script
executing command: SET until clause
Starting recover at 19-OCT-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file /home/oracle/arch/ENMO/1_96_924523013.arc
archived log for thread 1 with sequence 97 is already on disk as file /home/oracle/arch/ENMO/1_97_924523013.arc
archived log file name=/home/oracle/arch/ENMO/1_96_924523013.arc thread=1 sequence=96
archived log file name=/home/oracle/arch/ENMO/1_97_924523013.arc thread=1 sequence=97
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-OCT-16
Finished Duplicate Db at 19-OCT-16 #完成把主庫所有檔案複製到備庫
RMAN>
完成檔案的移動。
--備庫同步資料：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; #開啟使用日誌檔案功能，是主庫備庫保持同步。
Media recovery complete.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> recover managed standby database cancel; #關閉使用日誌檔案功能
Media recovery complete.
--檢視備庫使用日誌的狀況：
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
97 YES
96 YES
98 YES
99 YES
100 YES
1 NO
101 YES
2 NO
3 NO
105 YES
106 YES
SEQUENCE# APPLIED
---------- ---------
103 YES
102 YES
104 YES
107 YES
108 YES
109 YES
110 YES
111 NO
111 YES
112 NO
112 YES
22 rows selected.
--snapshot standby：
--Oracle 11g物理Data Guard之Snapshot Standby資料庫功能：
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED SNAPSHOT STANDBY
備庫的角色有兩種：一種是PHYSICAL STANDBY，另一種是SNAPSHOT STANDBY。
備庫的這兩種角色可以透過alter database convert to snapshot standby;
與alter database convert to physical standby;相互轉換。snapshot standby角色
只是作為測試角色，沒有使用日誌檔案的功能，所以一般是保持physical standby角色。
--開啟備庫：
SQL> alter database open;
Database altered.
--備庫的狀態資訊：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED SNAPSHOT STANDBY
--主庫的狀態資訊：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
--備庫不開啟（或者監聽不啟動）時主庫的狀態：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY
到這裡，Datab Guard（簡稱DG）已經搭建完成。
準備條件：兩臺虛擬機器，一臺裝載主庫，另一臺裝載備庫（備庫為空庫，只有安裝好oracle軟體）
----主庫： PROD/IP:192.168.2.6
----備庫： PROD/IP:192.168.2.4 # 備庫名PROD（提前定義）要與主庫名一致。
--關閉主庫，從spfile建立pfile：
SQL> create pfile from spfile;
File created.
--在主庫修改引數檔案：
[oracle@enmo dbs]$ vi initPROD.ora
PROD.__db_cache_size=352321536
PROD.__java_pool_size=4194304
PROD.__large_pool_size=8388608
PROD.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD.__pga_aggregate_target=335544320
PROD.__sga_target=503316480
PROD.__shared_io_pool_size=0
PROD.__shared_pool_size=130023424
PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump' #審計檔案目錄
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/PROD/ora_control1.ctl','/u01/app/oracle/oradata/PROD/ora_control2.ctl' #控制檔案物件
*.db_block_size=8192
*.db_domain='oracle.com' #domain名
*.db_name='PROD'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=PROD #主庫唯一庫名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch/PROD/ #主庫本地歸檔日誌存放目錄
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD'
LOG_ARCHIVE_DEST_2=
'SERVICE=ENMO ASYNC #此處ENMO只是作為連線備庫ENMO庫的網路連結串（tnsnames)
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ENMO'
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=ENMO #備庫庫名
DB_FILE_NAME_CONVERT='ENMO','PROD'
LOG_FILE_NAME_CONVERT=
'/home/oracle/arch/ENMO/','/home/oracle/arch/PROD/' #備庫主庫存放日誌檔案目錄的交換，可以簡寫為：'ENMO','PROD'
STANDBY_FILE_MANAGEMENT=AUTO
--從pfile生成spfile，測試開啟主庫，並強制開啟force logging：
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL>
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
--檢視主庫的重做日誌檔案組：
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/redo01.log
2 /u01/app/oracle/oradata/PROD/redo02.log
3 /u01/app/oracle/oradata/PROD/redo03.log
--在主庫新增standby日誌組（新增規則:普通日誌檔案<=standby日誌檔案，且檔案大小對應一致：
SQL> alter database add standby logfile group 4
2 ('/u01/app/oracle/oradata/PROD/redo04_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo05_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 5
2 ('/u01/app/oracle/oradata/PROD/redo06_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo07_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 6
2 ('/u01/app/oracle/oradata/PROD/redo08_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo09_staby.log')
4 size 10M;
Database altered.
SQL> alter database add standby logfile group 7
2 ('/u01/app/oracle/oradata/PROD/redo010_staby.log',
3 '/u01/app/oracle/oradata/PROD/redo011_staby.log')
4 size 10M;
Database altered.
--增加後檢視所有的日誌檔案：
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/redo01.log
2 /u01/app/oracle/oradata/PROD/redo02.log
3 /u01/app/oracle/oradata/PROD/redo03.log
4 /u01/app/oracle/oradata/PROD/redo04_staby.log
4 /u01/app/oracle/oradata/PROD/redo05_staby.log
5 /u01/app/oracle/oradata/PROD/redo06_staby.log
5 /u01/app/oracle/oradata/PROD/redo07_staby.log
6 /u01/app/oracle/oradata/PROD/redo08_staby.log
6 /u01/app/oracle/oradata/PROD/redo09_staby.log
7 /u01/app/oracle/oradata/PROD/redo010_staby.log
7 /u01/app/oracle/oradata/PROD/redo011_staby.log
--從主庫複製pfile引數檔案與密碼檔案到備庫的主機上：
[oracle@enmo dbs]$ ls
hc_OCMU.dat initOCMU.ora init.ora.bck initPROD.ora.bck lkPROD snapcf_OCMU.f spfileOCMU.ora
hc_PROD.dat init.ora initPROD.ora lkORA11GR2 orapwPROD snapcf_PROD.f spfilePROD.ora
[oracle@enmo dbs]$ scp initPROD.ora orapwPROD 192.168.2.4:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@192.168.2.4's password:
initPROD.ora 100% 1432 1.4KB/s 00:00
orapwPROD 100% 1536 1.5KB/s 00:00
----配置靜態監聽，相互訪問：
---主庫PROD庫靜態監聽配置：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD))
)
--主庫PROD庫tns配置：
ENMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ENMO.oracle.com)
)
)
--主庫PROD主庫啟動並註冊監聽：
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 18-OCT-2016 16:40:46
Uptime 1 days 3 hr. 2 min. 57 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/enmo/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmo.oracle.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLXDB.oracle.com" has 1 instance(s).
Instance "PROD", status READY, has 1 handler(s) for this service...
Service "PROD.oracle.com" has 2 instance(s).
Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
Instance "PROD", status READY, has 1 handler(s) for this service...
The command completed successfully
--備庫ENMO庫靜態監聽配置：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ENMO.oracle.com)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=ORA11GR2 ))
)
--備庫ENMO庫tns配置:
PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD.oracle.com)
)
)
--備庫ENMO備庫啟動並註冊監聽：
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 19-OCT-2016 19:51:01
Uptime 0 days 0 hr. 2 min. 2 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/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ENMO.oracle.com" has 2 instance(s).
Instance "ENMO", status BLOCKED, has 1 handler(s) for this service...
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
監聽的配置與備庫引數檔案的先後順序沒有要求，這都是自己安排設計的。
--在備庫ENMO庫的引數檔案修改：
*.audit_file_dest='/u01/app/oracle/admin/enmo/adump' #備庫的審計檔案目錄
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/ENMO/ora_control1.ctl','/u01/app/oracle/oradata/ENMO/ora_control2.ctl' #備庫ENMO庫控制檔案物件
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_name='PROD' #備庫名與主庫名保持一致
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=800M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=ENMO #備庫的唯一庫名
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,ENMO)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch/ENMO/ #歸檔日誌檔案存放目錄
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ENMO'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD ASYNC #此處PROD只是作為連線備庫PROD庫的網路連結串（tnsnames)
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD'
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=PROD #備庫庫名
DB_FILE_NAME_CONVERT='PROD','ENMO'
LOG_FILE_NAME_CONVERT='PROD','ENMO'
STANDBY_FILE_MANAGEMENT=AUTO
在備庫修改pfile引數檔案後，從pfile檔案生成spfile檔案，並啟動例項到nomount狀態：
--在主庫複製檔案到備庫：
[oracle@enmo ~]$ rman target / auxiliary sys/oracle@enmo
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 19 20:22:10 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=336361349)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 19-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/orapwPROD' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwENMO' ;
}
executing Memory Script
Starting backup at 19-OCT-16
... ...
executing Memory Script
executing command: SET until clause
Starting recover at 19-OCT-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file /home/oracle/arch/ENMO/1_96_924523013.arc
archived log for thread 1 with sequence 97 is already on disk as file /home/oracle/arch/ENMO/1_97_924523013.arc
archived log file name=/home/oracle/arch/ENMO/1_96_924523013.arc thread=1 sequence=96
archived log file name=/home/oracle/arch/ENMO/1_97_924523013.arc thread=1 sequence=97
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-OCT-16
Finished Duplicate Db at 19-OCT-16 #完成把主庫所有檔案複製到備庫
RMAN>
完成檔案的移動。
--備庫同步資料：
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; #開啟使用日誌檔案功能，是主庫備庫保持同步。
Media recovery complete.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL> recover managed standby database cancel; #關閉使用日誌檔案功能
Media recovery complete.
--檢視備庫使用日誌的狀況：
SQL> select SEQUENCE#,APPLIED from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
97 YES
96 YES
98 YES
99 YES
100 YES
1 NO
101 YES
2 NO
3 NO
105 YES
106 YES
SEQUENCE# APPLIED
---------- ---------
103 YES
102 YES
104 YES
107 YES
108 YES
109 YES
110 YES
111 NO
111 YES
112 NO
112 YES
22 rows selected.
--snapshot standby：
--Oracle 11g物理Data Guard之Snapshot Standby資料庫功能：
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED SNAPSHOT STANDBY
備庫的角色有兩種：一種是PHYSICAL STANDBY，另一種是SNAPSHOT STANDBY。
備庫的這兩種角色可以透過alter database convert to snapshot standby;
與alter database convert to physical standby;相互轉換。snapshot standby角色
只是作為測試角色，沒有使用日誌檔案的功能，所以一般是保持physical standby角色。
--開啟備庫：
SQL> alter database open;
Database altered.
--備庫的狀態資訊：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED SNAPSHOT STANDBY
--主庫的狀態資訊：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
--備庫不開啟（或者監聽不啟動）時主庫的狀態：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY
到這裡，Datab Guard（簡稱DG）已經搭建完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2126841/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 12c 容災之Active Data Guard本機搭建和基礎測試
- Data guard搭建
- 容災技術介紹
- 資料容災技術及容災方案分類
- 四、備份容災技術
- 技術白皮書：Oracle Data Guard 11gOracle Data Guard 理論知識OracleGo
- 單機搭建Data Guard
- 【DG】Data Guard搭建（physical standby）
- RedHat搭建物理Data GuardRedhat
- 搭建Active Data Guard環境
- 天翼雲混合雲容災技術解析
- Data Guard搭建困境突圍（一）
- Oracle RAC + Data Guard 環境搭建Oracle
- 異地容災系統技術概述（轉載）
- Oracle 12c Data Guard搭建（一）Oracle
- 搭建邏輯Data Guard 12c
- Tech Talk · 雲技術有話聊 | 深信服混合雲容災技術解析
- opengauss雙region流式容災搭建
- Data guard 配置之搭建物理備庫
- DATA GUARD部署模式——DATA GUARD概念和管理模式
- 搭建Oracle Data Guard 11g（物理備用）Oracle
- 介紹ORACLE DATA GUARD——DATA GUARD概念和管理Oracle
- oracle data guard!!Oracle
- 搭建11g data guard(duplicate from active database方式)Database
- Data Guard 之RMAN備份線上搭建物理standby
- 單機上快速搭建一個Data Guard環境
- 容災備份技術有效保證受損資料恢復資料恢復
- Data Guard Broker系列之二：Data Guard Broker配置實戰
- Oracle 9i Data Guard進行資料庫的災難防護（轉）Oracle資料庫
- 前端容災前端
- 容災方案
- DATA GUARD 簡介
- Data Guard 建立（ASM)ASM
- Oracle Data Guard配置Oracle
- 杉巖資料：5種常見容災複製技術圖解圖解
- Oracle Data Guard Linux 平臺 Physical Standby 搭建例項OracleLinux
- 不同於傳統容災災備的雲容災解決方案
- 淺談容災與容災方案設計薦