database+dataguard部署
DB+DG
一、Oracle11DB+DG配置
1. 單機環境介紹(PRIMARY DATABASE)
主庫 primary
public ip 192.168.0.252
instance oracle
db_name oracle
storage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch
2. 單機環境介紹（standby database）
資料檔案可放至本地，也可以放至ASM上，本實驗中先放至本地檔案方式存放
備機:只需要裝實冽 和linux.
ip 192.168.0.253
instance oracle
storage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch
3.edit編輯主用 hosts檔案檔案後面加入主備紅色部分Ip地址
#Public Network - (eth0)
127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
192.168.0.252 primary
192.168.0.253 standby
edit編輯備用用 hosts檔案後面加入主備Ip地址
#Public Network - (eth0)
192.168.0.252 primary
192.168.0.253 standby
4. 檢查主庫環境
4.1. 啟動archivelog歸檔模式
檢視是否在歸檔模式如果沒有在歸檔模式下面就庶修改成歸檔模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Next log sequence to archive 56
Current log sequence 56
檢視當前恢復引數
SQL> show parameter RECOVERY;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DG_RECOVERY
db_recovery_file_dest_size big integer 2G
recovery_parallelism integer 0
4.2. 啟動主庫FORCE_LOGGING模式
首新查詢是啟動FORCE_LOGGING是否為YES如果不是就更改
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
exit
SQL> alter database FORCE LOGGING;
Database altered.
5. 主庫tnsnames.ora和listener.ora配置
首先配置主庫節點hisa的tnsnames.ora和listener.ora
5.1. listener.ora 主庫主庫的Listener.ora不需要改
主庫的listener.ora在/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
5.2. tnsnames.ora （PRIMARY)
cd /app/oracle/product/11.2.0/db_1/network/admin/
vi tnsnames.ora
tnsnames.ora 把以下的紅色部分內容加入到tnsnames.ora中。
# tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
6. 備庫tnsnames.ora和listener.ora配置
用oracle使用者生成備庫上的（standby）主機上的listener.ora和tnsnames.ora
6.1. listener.ora
cd /app/oracle/product/11.2.0/db_1/network/admin
vi listener.ora 把以下的配置內容拷入到新建的listener.ora中
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = oracle)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = oracle)
)
(SID_DESC =
(GLOBAL_DBNAME = PLSExtProc)
(ORACLE_HOME = /app/oracle/product/11.2.0/db_1)
(SID_NAME = PLSExtProc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
6.2. tnsnames.ora
cd /app/oracle/product/11.2.0/db_1/network/admin
vi tnsnames.ora 把以下的配置內容拷入到新建的tnsnames.ora中更改host的ip主機和備用機Ip地址
primary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
7. 備庫引數配置
準備引數檔案,先配備庫的引數，主庫引數隨後使用手動配置
以下的引數檔案是從主庫中用create pfile='/home/oracle/pfile20130627.ora' from spfile;
單機備庫引數如下：引數配置見control_file.doc
cd /app/oracle/product/11.2.0/db_1/dbs
vi initcrds3db.ora
上面這個名字會根據你設定的環境變數而變化（~/.bash_profile），然後將以下檔案拷入initcrds3db.ora中。下面的藍色部分在輝縣主機環境配置的時候要注意記憶體的大小變化。紅色部分為新增內容。
*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/controlfile/control01.ctl','/orasjrz/crds3db/oradata/controlfile/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch '
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
*.log_archive_config='dg_config=(oracle,oracledg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/tempfile','/orasjrz/crds3db/oradata/tempfile'
*.log_file_name_convert='/orasjrz/crds3db/oradata /logfile','/orasjrz/crds3db/oradata/logfile'
*.db_unique_name='oracledg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracledg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oracle'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
20130609dg-pfile
*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/control01.ctl','/orasjrz/crds3db/oradata/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=5368709120
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
*.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.log_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
vi initcrds3db.ora
*.audit_file_dest='/app/orasu cle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/control01.ctl','/orasjrz/crds3db/oradata/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crd3db'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=681574400
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/home/oracle/'
.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.log_file_name_convert='/orasjrz/crds3db/oradata','/orasjrz/crds3db/oradata'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
測試用的rac
8. 備*.audit_file_dest='/app/oracle/admin/crds3db/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/orasjrz/crds3db/oradata/controlfile/control01.ctl','/orasjrz/crds3db/oradata/controlfile/control02.ctl'
*.db_block_size=16384
*.db_domain=''
*.db_name='crds3db'
*.diagnostic_dest='/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=crds3dbXDB)'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target= 966367641
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='dg_config=(crds3db,crds3dbdg)'
*.standby_file_management='AUTO'
*.db_file_name_convert='+ORADATA/crds3db/datafile','/orasjrz/crds3db/oradata/datafile','+ORADATA/crds3db/tempfile','/orasjrz/crds3db/oradata/tempfile'
*.log_file_name_convert='+ORADATA/crds3db/onlinelog','/orasjrz/crds3db/oradata/onlinelog1','+ORAARCH/crds3db/onlinelog','/orasjrz/crds3db/oradata/onlinelog2'
*.db_unique_name='crds3dbdg'
*.log_archive_dest_1='location=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=crds3dbdg'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=crds3db'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'庫新建資料庫存放目錄
exit
在備庫的root使用者下執行
mkdir -p /orasjrz/oracle/oradata
mkdir -p /orasjrz/oracle/oraarch
chown -R oracle:oinstall /orasjrz
chmod -R 775 /orasjrz
9. 在主庫增加standby logfile
在主庫standby執行下面sql語句加重做日誌
建立原則和單例項一樣，大小相等，但日誌組數量要比主庫線上日誌多一組。如之前為3組1個單節點共3個，則現在要建立4組1個單節點共7個.
10. 主庫rman備份
在主庫hisa以oracle身份登入(2011-10-18 0:53開始)
主庫已有備份則直接跳到下面11步拷備份而不需要臨時進行備份
mkdir /home/oracle/rman
rman target /
RMAN>configure channel device type disk format '/home/oracle/rman/backup_%d_%T_%I_%u';
備份格式說明： %d specifies the name of the database
%I specifies the DBID.
%T specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.
RMAN>configure controlfile autobackup on;
RMAN>backup database;
按照下面提示輸入
啟動 backup 於 30-6月 -11
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=17 例項=crds3db1 裝置型別=DISK
通道 ORA_DISK_1: 正在啟動全部資料檔案備份集
通道 ORA_DISK_1: 正在指定備份集內的資料檔案
輸入資料檔案: 檔案號=00002 名稱=+ORADATA/crds3db/datafile/sysaux.260.755106369
輸入資料檔案: 檔案號=00001 名稱=+ORADATA/crds3db/datafile/system.259.755106357
輸入資料檔案: 檔案號=00003 名稱=+ORADATA/crds3db/datafile/undotbs1.261.755106377
輸入資料檔案: 檔案號=00004 名稱=+ORADATA/crds3db/datafile/undotbs2.263.755106393
輸入資料檔案: 檔案號=00006 名稱=+ORADATA/crds3db/datafile/rmanadm.dbf
輸入資料檔案: 檔案號=00005 名稱=+ORADATA/crds3db/datafile/users.264.755106395
通道 ORA_DISK_1: 正在啟動段 1 於 30-6月 -11
通道 ORA_DISK_1: 已完成段 1 於 30-6月 -11
段控制程式碼=/home/oracle/rman/backup_oracle_20110630_2587900074 標記=TAG20110630T104341 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:56
完成 backup 於 30-6月 -11
啟動 Control File and SPFILE Autobackup 於 30-6月 -11
段 handle=/app/oracle/product/11.2.0/db_1/dbs/c-2587900074-20110630-01 comment=NONE
完成 Control File and SPFILE Autobackup 於 30-6月 -11
RMAN>exit
檢查ramn備份,需將主庫的備份檔案拷到備庫的相同目錄下。
[oracle@hisa admin]$ cd /home/oracle/rman
[oracle@hisa rman]$ ls -al
total 1236540
1236540 -rw-r----- 1 oracle asmadmin 1264975872 Jun 30 10:44 backup_CRDS3DB_20110630_2587900074
11. 將主庫rman傳到備庫
在備庫以oracle身份先登入建rman目錄
mkdir -p /home/oracle/rman/backup
回到主庫hisa視窗上執行
[oracle@hisa rman]$ pwd
/home/oracle/rman
[oracle@hisa rman]$ scp backup_oracle_20130627_2 hisb:/home/oracle/rman
vi initoracle.ora
The authenticity of host 'hisadg (192.168.0.11)' can't be established.
RSA key fingerprint is eb:3b:c3:84:38:bf:8a:f6:d9:7c:d0:59:6e:51:61:26.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'hisadg,192.168.0.11' (RSA) to the list of known hosts.
password: 輸入備庫oracle作業系統使用者密碼,成功後進行傳輸rman備份
backup_CRDS3DB_20110630_2587900074 100% 1206MB 10.8MB/s 01:52
[oracle@hisa rman]$
12. 在主庫hisa上建立standby控制檔案
在主庫上進行幾次歸檔。
sqlplus / as sysdba
s 執行3次
/符號執行上一次命令
回到hisa建立standby控制檔案
alter database create standby controlfile as '/home/oracle/rman/standby.ctl';
exit
將檔案拷到備庫
scp /home/oracle/rman/standby.ctl hisb:/home/oracle/rman/standby.ctl vi initcrds3db.ora
相關引數及日誌目錄拷到備庫
scp -r /app/oracle/admin/ hisb:/app/oracle/admin/
密碼檔案拷到備庫，注意rac主庫上的密碼檔名上會多個數字 1 ，拷到備庫時，要重新命名將後面1去除
scp /app/oracle/product/11.2.0/db_1/dbs/orapwcrds3db hisb:/app/oracle/product/11.2.0/db_1/dbs/orapwcrds3db
在備庫建立spfile並啟動standby至nomount狀態，下面的pfile在第7步已生成。
注：如果備庫df -h看到tmpfs沒調則要先調整tmpfs大小
exit
df -h 先檢視原來大小
vi /etc/fstab 修改增加,size=6G
tmpfs /dev/shm tmpfs defaults,size=6G 0 0
mount -o remount,size=1G /dev/shm
df -h 看到已修改則ok.
su - oracle
sqlplus /nolog
conn / as sysdba
create spfile from pfile='/app/oracle/product/11.2.0/db_1/dbs/initcrds3db.ora';
startup nomount 如報ORA-00845: MEMORY_TARGET not supported on this system則檢查記憶體只能設定實體記憶體的一半或更小，超過則會報這錯誤，否則需要先調整tmpfs大小。
host lsnrctl start
13. 利用rman建立standby資料庫hisa
備註：不需要備份直接在主庫執行這個命令：rman target auxiliary
duplicate target database for standby from active database;目錄不一樣執行這個
duplicate target database for standby from active database nofilenamecheck;目錄一樣執行這個
在主庫:
過程如下：
[oracle@hisa dbs]$ rman target / auxiliary
恢復管理器: Release 11.2.0.2.0 - Production on 星期四 6月 30 11:59:10 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
已連線到目標資料庫: CRDS3DB (DBID=2587900074)
已連線到輔助資料庫: CRDS3DB (未裝載)
RMAN> duplicate target database for standby nofilenamecheck; //當主庫與備庫路徑相同是使用這個命令
RMAN> duplicate target database for standby;
啟動 Duplicate Db 於 30-6月 -11
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=771 裝置型別=DISK
記憶體指令碼的內容:
{
restore clone standby controlfile;
}
正在執行記憶體指令碼
啟動 restore 於 30-6月 -11
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在還原控制檔案
通道 ORA_AUX_DISK_1: 已複製控制檔案副本
輸入檔名=/home/oracle/rman/standby.ctl
輸出檔名=/oradata/crds3db/controlfile/control01.ctl
輸出檔名=/oradata/crds3db/controlfile/control02.ctl
完成 restore 於 30-6月 -11
記憶體指令碼的內容:
{
sql clone 'alter database mount standby database';
}
正在執行記憶體指令碼
sql 語句: alter database mount standby database
記憶體指令碼的內容:
{
set newname for tempfile 1 to
"/oradata/crds3db/tempfile/temp.262.755106379";
switch clone tempfile all;
set newname for datafile 1 to
"/oradata/crds3db/datafile/system.259.755106357";
set newname for datafile 2 to
"/oradata/crds3db/datafile/sysaux.260.755106369";
set newname for datafile 3 to
"/oradata/crds3db/datafile/undotbs1.261.755106377";
set newname for datafile 4 to
"/oradata/crds3db/datafile/undotbs2.263.755106393";
set newname for datafile 5 to
"/oradata/crds3db/datafile/users.264.755106395";
set newname for datafile 6 to
"/oradata/crds3db/datafile/rmanadm.dbf";
restore
clone database
;
}
正在執行記憶體指令碼
正在執行命令: SET NEWNAME
臨時檔案 1 在控制檔案中已重新命名為 /oradata/crds3db/tempfile/temp.262.755106379
正在執行命令: SET NEWNAME
正在執行命令: SET NEWNAME
正在執行命令: SET NEWNAME
正在執行命令: SET NEWNAME
正在執行命令: SET NEWNAME
正在執行命令: SET NEWNAME
啟動 restore 於 30-6月 -11
使用通道 ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: 正在開始還原資料檔案備份集
通道 ORA_AUX_DISK_1: 正在指定從備份集還原的資料檔案
通道 ORA_AUX_DISK_1: 將資料檔案 00001 還原到 /oradata/crds3db/datafile/system.259.755106357
通道 ORA_AUX_DISK_1: 將資料檔案 00002 還原到 /oradata/crds3db/datafile/sysaux.260.755106369
通道 ORA_AUX_DISK_1: 將資料檔案 00003 還原到 /oradata/crds3db/datafile/undotbs1.261.755106377
通道 ORA_AUX_DISK_1: 將資料檔案 00004 還原到 /oradata/crds3db/datafile/undotbs2.263.755106393
通道 ORA_AUX_DISK_1: 將資料檔案 00005 還原到 /oradata/crds3db/datafile/users.264.755106395
通道 ORA_AUX_DISK_1: 將資料檔案 00006 還原到 /oradata/crds3db/datafile/rmanadm.dbf
通道 ORA_AUX_DISK_1: 正在讀取備份片段 /home/oracle/rman/backup_CRDS3DB_20110630_2587900074
通道 ORA_AUX_DISK_1: 段控制程式碼 = /home/oracle/rman/backup_CRDS3DB_20110630_2587900074 標記 = TAG20110630T104341
通道 ORA_AUX_DISK_1: 已還原備份片段 1
通道 ORA_AUX_DISK_1: 還原完成, 用時: 00:00:55
完成 restore 於 30-6月 -11
記憶體指令碼的內容:
{
switch clone datafile all;
}
正在執行記憶體指令碼
資料檔案 1 已轉換成資料檔案副本
輸入資料檔案副本 RECID=1 STAMP=755179163 檔名=/oradata/crds3db/datafile/system.259.755106357
資料檔案 2 已轉換成資料檔案副本
輸入資料檔案副本 RECID=2 STAMP=755179163 檔名=/oradata/crds3db/datafile/sysaux.260.755106369
資料檔案 3 已轉換成資料檔案副本
輸入資料檔案副本 RECID=3 STAMP=755179163 檔名=/oradata/crds3db/datafile/undotbs1.261.755106377
資料檔案 4 已轉換成資料檔案副本
輸入資料檔案副本 RECID=4 STAMP=755179163 檔名=/oradata/crds3db/datafile/undotbs2.263.755106393
資料檔案 5 已轉換成資料檔案副本
輸入資料檔案副本 RECID=5 STAMP=755179163 檔名=/oradata/crds3db/datafile/users.264.755106395
資料檔案 6 已轉換成資料檔案副本
輸入資料檔案副本 RECID=6 STAMP=755179163 檔名=/oradata/crds3db/datafile/rmanadm.dbf
完成 Duplicate Db 於 30-6月 -11
RMAN>exit
14. 在備庫檢查standby資料庫
--sqlplus /nolog
--conn / as sysdba
SQL> select status from v$instance; 檢視當前例項狀態
STATUS
------------
MOUNTED
SQL> select member from v$logfile; 檢視當前重做日誌狀態
MEMBER
/oradata/crds3db/onlinelog1/group_1.257.755106353
/oradata/crds3db/onlinelog2/group_1.257.755106353
/oradata/crds3db/onlinelog1/group_2.258.755106355
/oradata/crds3db/onlinelog2/group_2.258.755106355
/oradata/crds3db/onlinelog1/group_3.265.755109189
/oradata/crds3db/onlinelog2/group_3.259.755109191
/oradata/crds3db/onlinelog1/group_4.266.755109191
/oradata/crds3db/onlinelog2/group_4.260.755109193
.
.
已選擇7行。 rac則為32行
SQL> select name from v$datafile; 檢視當前資料檔案的狀態
NAME
--------------------------------------------------------------------------------
/oradata/crds3db/datafile/system.259.755106357
/oradata/crds3db/datafile/sysaux.260.755106369
/oradata/crds3db/datafile/undotbs1.261.755106377
/oradata/crds3db/datafile/undotbs2.263.755106393
/oradata/crds3db/datafile/users.264.755106395
/oradata/crds3db/datafile/rmanadm.dbf
已選擇6行。
SQL> select name from v$tempfile; 檢視當前臨時檔案狀態
NAME
--------------------------------------------------------------------------------
/oradata/crds3db/tempfile/temp.262.755106379
SQL> show parameter control 檢視當前控制檔案狀態
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/crds3db/controlfile/c
ontrol01.ctl, /oradata/crds3db
/controlfile/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
15. 在主庫啟用引數
這三個引數預先配置，不需要再進行配置，只需要檢查確認 修改則要重啟資料庫，所以得預先配置
show parameter db_unique;
show parameter convert;
SQL> alter system set db_unique_name='crd3db' scope=spfile;
System altered.
SQL>alter system set db_file_name_convert='/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/datafile','/orasjrz/crds3db/oradata/tempfile','/orasjrz/crds3db/oradata/tempfile' scope=spfile;
System altered.
SQL>alter system set log_file_name_convert='/orasjrz/crds3db/oradata/logfile','/orasjrz/crds3db/oradata/logfile' scope=spfile;
System altered.
--------------------------------------------------------
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oracle,oracledg)' scope=both;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/orasjrz/crds3db/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oracle' scope=both;
*.log_archive_dest_1='LOCATION=/oracle/oraarch' /從主庫搞過來的，如上面這條命令在主庫應用後，主庫起不來，就用這條命令也可。
System altered.
實時應用SQL> alter system set log_archive_dest_2='SERVICE=TSFYHISA_231 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= oracledg' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> alter system set FAL_SERVER='standby' scope=both;
System altered.
SQL> alter system set FAL_CLIENT='primary' scope=both;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
16. 主庫啟用新增及修改的引數
改完在hisa上oracle使用者狀態下執行，啟用上面引數 如前面三個引數預先配置則不需要重啟資料庫，未配則需要
Shutdown immediate;
搭RAC+DG平臺時,因備庫沒有及時搭建時,需要將下面引數啟用,以防止主庫一直報錯.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=defer scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;
17. 在備庫開始同步
可不執行下面這二個，直接啟用下面日誌同步應用
--shutdown immediate //先關閉備庫。需要在MOUNT狀態下應用完日誌再open read only
--startup mount
啟動MRP(日誌同步應用):以下的命令為實時應用歸檔
alter database recover managed standby database using current logfile disconnect;
查詢同步狀態
set linesize 200;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
select x.thread#,x.applied_max,to_char(y.first_time,'yyyy-mm-dd hh24:mi:ss') first_time,to_char(y.next_time,'mm-dd hh24:mi:ss') next_time,to_char(y.completion_time,'yyyy-mm-dd hh24:mi:ss') completion_time from (select thread#,max(sequence#) applied_max from v$archived_log where applied='YES' group by thread#) x,v$archived_log y where x.thread#=y.thread# and x.applied_max=y.sequence# order by x.thread#;
select thread#,sequence#,applied,name from v$archived_log where applied not in('YES','NO') order by thread#,sequence#;
查還有多少未同步
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
提示沒有查詢的行就是同步完成了:no rows selected
另開一視窗跟蹤alert…log，看歸檔應用情況.
tail -f /app/oracle/diag/rdbms/unq_crds3dg/crds3db/trace/alert_crds3db.log
到6.9 15點就同步完成
再用上面查詢發現已全部同步，再用下面取消同步開啟再應用
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect;
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
下面這段跳過，因為用了上面這段已正常，直接到下面18步驟測試同步
//設定成自動應用歸檔的話需要使用以下sql
//alter database recover managed standby database disconnect from session;
檢視已應用記錄
select thread#,sequence#,applied from v$archived_log where applied='YES' order by thread#,sequence#;
檢視未應用記錄
select thread#,sequence#,applied from v$archived_log where applied='NO' order by thread#,sequence#;
應提示未選定行，如有記錄則要檢查，如沒有則再執行下面
停止MRP,即停止主備資料庫歸檔重做日誌同步。
alter database recover managed standby database cancel;
更改資料庫到只讀狀態。
alter database open read only;
檢視主備庫的資料庫執行情況
啟動MRP:
alter database recover managed standby database using current logfile disconnect;
主庫執行情況：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- -------------------- -------------------- ----------------
CRDS3DB READ WRITE MAXIMUM PERFORMANCE PRIMARY
備庫執行情況
SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- -------------------- -------------------- ----------------
CRDS3DB READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY
18. 檢查dg配置是否成功
需要資料庫在open read only下進行
在主庫執行生成一張表
create table test(a varchar2(2),b date);
在備庫查詢
select * from test
如有再回主庫執行
insert into test values('1',sysdate);
commit;
切回備庫查詢
select * from test;
切回主庫刪除測試表
drop table test;
然後在主備執行select * from test;應報錯則ok.
rac+dg 成功配置完成.
再配置自動啟動dg指令碼
19. 配置dg自動啟動
1.遠端root登入到主庫檢視啟動資訊
cat /etc/oratab將最後一行啟動資訊 crds3db:/app/oracle/product/11.2.0/db_1:Y 複製下來
2.遠端root登入到備庫增加啟動資訊
vi /etc/oratab在最後一行貼上主庫複製的資訊
3.將下面指令碼修改核對後拷入備庫
一些資訊可透過主庫檢視獲取，因為主庫一般設定了自動啟動 cat /etc/oratab
vi /etc/init.d/oracledgnew 先按i空三行再移到中間處貼上下面內容
#!/bin/bash
#chkconfig: 2345 98 01
#description: Oracle database dataguard server
#Starts the oracle database dataguard server
#
# processname: oracle
# Source function library.
. /etc/init.d/functions
ORACLE_SID=crd3db; export ORACLE_SID
ORACLE_BASE=/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG ORA_NLS33
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME_LISTNER=$ORACLE_HOME
PATH=$HOME/bin:$ORACLE_HOME/bin:/app/bin:/bin:/usr/bin:usr/ccs/bin:/usr/ucb:/etc:$PATH
export PATH
test -x $ORACLE_HOME/bin/oracle || exit 0
RETVAL=0
start() {
# Check if oracle is already running
if [ ! -f /var/lock/subsys/oracle ]; then
prog="listener"
echo -n $"Starting $prog: "
su - oracle -c "lsnrctl start" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Starting $prog: "
su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
connect / as sysdba
startup mount
! sleep 60
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v\$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v\$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
declare wyyn number;
begin
select count(1) into wyyn from v\$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES';
if wyyn=0 then
EXECUTE IMMEDIATE 'alter database open';
end if;
EXECUTE IMMEDIATE 'alter database recover managed standby database using current logfile disconnect';
end;
/
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v\$database;
select thread#,min(sequence#) no_applied_min,max(sequence#) no_applied_max,count(1) no_applied_all from v\$archived_log where applied='NO' and STANDBY_DEST='NO' and deleted<>'YES' group by thread# order by thread#;
exit
EOF
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle || RETVAL=1
fi
return $RETVAL
}
stop() {
prog="listener"
echo -n $"Stopping $prog: "
su - oracle -c "lsnrctl stop" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Stopping $prog: "
su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
connect / as sysdba
recover managed standby database cancel;
shutdown immediate
exit
EOF
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle
return $RETVAL
}
restart() {
stop
start
}
reload() {
restart
}
status_oracle() {
su - oracle -c "lsnrctl status"
su - oracle -c "sqlplus /nolog" << EOF
connect / as sysdba
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v\$database;
select process,status from v\$managed_standby;
exit
EOF
}
case "$1" in
start)
start
;;
stop)
stop
;;
reload|restart)
restart
;;
status)
status_oracle
;;
*)
echo $"Usage: $0 {start|stop|restart|status}"
exit 1
esac
exit $?
exit $RETVAL
4.授權加到服務並測試
# cd /etc/init.d
# chmod u+x oracledgnew
# chkconfig --add oracledgnew
# chkconfig --list oracledgnew
--# chkconfig --del oracledgnew 刪除服務
測試庫則可以先開一窗監控tail -f /var/log/oracle.log檔案，另一窗執行service oracledgnew stop，然後主庫做幾個日誌切換，完成後再回上面第二窗執行service oracledgnew start再看上面日誌。
測試
# service oracledgnew start #啟動服務
# service oracledgnew stop #停止服務
# service oracledgnew restart #重啟服務
# service oracledgnew status #檢查狀態 如已啟動，可用這進行檢查 而不需要啟動，如要測試可直接reboot重啟試下能否自動啟動。
作業系統reboot後大約三五分鐘(因為要啟動資料庫)再輸入service oracledgnew status
或檢視日誌cat /var/log/oracle.log 查否正常
如正常進入檢查下
su - oracle
conn / as sysdba
各節點彙總統計還有多少未應用 select thread#,count(1) from v$archived_log where applied='NO' group by thread#;
檢視未應用記錄select thread#,sequence#,applied ,substr(name,1,24) name from v$archived_log where applied='NO' order by thread#,sequence#;
看是不是提示無記錄
select thread#,count(1) from v$archived_log where applied='YES' group by thread#;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
尊重版權 轉載請與連結
技術群：132304250
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29065182/viewspace-1077851/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 灰度部署、滾動部署與藍綠部署
- [部署02] Docker 部署vue專案DockerVue
- 部署：無伺服器部署模式伺服器模式
- MySQL MHA部署 Part 5 MHA部署指南MySql
- vue 部署Vue
- nginx部署Nginx
- Harbor部署
- PalServer部署Server
- es 部署
- 部署方案
- SSIS 部署篇-如何部署SSIS包到SqlServerSQLServer
- Linux部署之Docker方式部署專案LinuxDocker
- 輕鬆部署 Laravel 應用 | 《08. 手動部署 - 部署應用程式碼》Laravel
- 【持續部署】批量部署工具，總結、對比
- Java一鍵部署包，Linux部署不用愁！！！JavaLinux
- 分散式爬蟲的部署之Scrapyd批量部署分散式爬蟲
- zabbix agent 3.0部署文件 安裝部署
- [部署01] NginxNginx
- helm部署mysqlMySql
- redmine docker部署Docker
- Openshift部署教程
- Nginx部署配置Nginx
- flowable流程部署
- docker 部署 postgresDocker
- zabbix server 部署Server
- ubuntu部署mongodbUbuntuMongoDB
- 網站部署網站
- 部署git serverGitServer
- Activiti部署zip
- docker部署MySQLDockerMySql
- 社群版部署
- 微服務部署微服務
- linux伺服器環境部署（三、docker部署nginx）Linux伺服器DockerNginx
- 容器化部署實踐之Django應用部署（二）Django
- 分散式爬蟲的部署之Scrapyd分散式部署分散式爬蟲
- 搭建自己的內網穿透，告別ngrok速度慢的問題，除錯本地服務（兩種方式，原始碼部署或者部署包部署，新手直接部署包部署）內網穿透除錯原始碼
- 微服務部署-基於DockerMaven外掛構建映象部署微服務DockerMaven
- CRM系統本地部署和雲部署的優缺點