溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
Datagard算是Oracle企業版的一種容災方案，在企業中廣泛應用，我就將搭建過程記錄下來以作備用。
主機名 數據庫版本 實例名 IP
db1 Oracle 11G R2 member 172.16.1.250
db2 Oracle 11G R2 member 172.16.1.251
默認情況下以上都已經安裝好了Oracle數據庫，但是只在db1上建立了數據庫和監聽，db2只安裝Oracle軟件不建庫，不建監聽。
目錄：
打開強制歸檔日志
增加standby日志組
修改主備啟動參數
密碼文件的處理
修改監聽
復制監聽文件、參數文件、密碼文件到備庫
創建備庫控制文件
復制主庫數據文件和日志文件到備庫
初始化及配置備庫做standby
DataGuard測試
主備切換測試
基礎工作:
a.安裝CentOS 5.11 x86_64,關閉selinux，iptables，自動對時
b.安裝Oracle 11G R2,db1安裝軟件、監聽及建庫,db2只安裝軟件不建庫
可以參考:http://fengwan.blog.51cto.com/508652/1330122
在db1的/etc/hosts里增加
127.0.0.1 db1
172.16.1.251 db2
在db2的/etc/hosts里增加
127.0.0.1 db2
172.16.1.250 db1
打開強制歸檔(db1)
(db1)SQL >shutdown immediate;
(db1)SQL >startup mount;
(db1)SQL >alter database force logging;
(db1)SQL >alter database archivelog;
2.創建重做日志組(必須要比原來的redo log多一組或多組,standby redo log是使用Real Time Apply的必要條件)
(db1)SQL> select group#,member from v$logfile;
GROUP# MEMBER
------------- -----------------------------------------------------------------
3 /opt/oracle/oradata/member/redo03.log
2 /opt/oracle/oradata/member/redo02.log
1 /opt/oracle/oradata/member/redo01.log
從上面可以看出現在已經有3組redo log.
(db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby04.log') size 50m;
(db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby05.log') size 50m;
(db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby06.log') size 50m;
(db1)SQL >alter database add standby logfile ('/opt/oracle/oradata/member/standby07.log') size 50m;
再查下日志組是否創建成功
(db1)SQL> select group#,member from v$logfile;
GROUP# MEMBER
------------- -----------------------------------------------------------------
3 /opt/oracle/oradata/member/redo03.log
2 /opt/oracle/oradata/member/redo02.log
1 /opt/oracle/oradata/member/redo01.log
4 /opt/oracle/oradata/member/standby04.log
5 /opt/oracle/oradata/member/standby05.log
6 /opt/oracle/oradata/member/standby06.log
7 /opt/oracle/oradata/member/standby07.log
可以看到我們創建的4組日志
3.修改主備庫的啟動參數
生成參數文件
(db1)SQL> create pfile='/tmp/member.pfile' from spfile;
退出sqlplus,用編輯器打開/tmp/member.pfile
[oracle@db1 ~]$ vi /tmp/member.pfile
member.__db_cache_size=331350016
member.__java_pool_size=4194304
member.__large_pool_size=4194304
member.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
member.__pga_aggregate_target=339738624
member.__sga_target=503316480
member.__shared_io_pool_size=0
member.__shared_pool_size=150994944
member.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/member/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/member/control01.ctl','/opt/oracle/flash_recovery_area/member/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='member'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=memberXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#增加一下部分
*.db_unique_name='db1'
*.archive_lag_target=1800
*.fal_client='db1'
*.fal_server='db2'
*.log_archive_config='DG_CONFIG=(db1,db2)'
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR=(all_logfiles,all_roles) db_unique_name=db1'
*.log_archive_dest_2='service=db2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db2'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.standby_file_management='auto'
*.db_file_name_convert='/opt/oracle/flash_recovery_area',' /opt/oracle/flash_recovery_area'
*.log_file_name_convert='/opt/oracle/flash_recovery_area ','/opt/oracle/flash_recovery_area'
并拷貝一個作為備機db2的啟動參數文件
[oracle@db1 ~]$ cp /tmp/member.pfile /tmp/db2.pfile
[oracle@db1 ~]$ vim /tmp/db2.pfile
則將上面增加的部分修改為
*.db_unique_name='db2'
*.archive_lag_target=1800
*.fal_client='db2'
*.fal_server='db1'
*.log_archive_config='DG_CONFIG=(db1,db2)'
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/ VALID_FOR=(all_logfiles,all_roles) db_unique_name=db2'
*.log_archive_dest_2='service=db1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=db1'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.standby_file_management='auto'
*.db_file_name_convert='/opt/oracle/flash_recovery_area',' /opt/oracle/flash_recovery_area'
*.log_file_name_convert='/opt/oracle/flash_recovery_area ','/opt/oracle/flash_recovery_area'
在db1上以修改過的/tmp/member.pfile啟動
(db1)SQL >shutdown immediate;
(db1)SQL> startup pfile='/tmp/member.pfile' nomount;
(db1)SQL> create spfile from pfile='/tmp/member.pfile';
(db1)SQL >shutdown immediate;
(db1)SQL> startup;
4.主庫密碼文件:
[1]存在密碼文件
[oracle@db1 dbs]$ ls $ORACLE_HOME/dbs
hc_DBUA0.dat hc_member.dat init.ora lkDB1 lkMEMBER orapwmember spfilemember.ora
看到上面有一個密碼文件orapwmember，在建庫的時候默認會創建一個
=================================================================================
[2]不存在密碼文件
如果沒有的話可以手動創建一個。
[oracle@db1 dbs]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ orapwd file=orapwmember password=123456 entries=3
#注意以上需要根據SID名建立的，file=orapwSID
===================================================================================
5.修改監聽
[oracle@db1 dbs]$ cd $ORACLE_HOME/network/admin
[oracle@db1 admin]$ mv listener.ora listener.ora.default
[oracle@db1 admin]$ vim listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = member)
(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)
(SID_NAME = member)
)
)
#注意以上的HOST，就是HOSTNAME
[oracle@db1 admin]$ vim tnsnames.ora
MEMBER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = member)
)
)
db1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1)
)
)
db2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2)
)
)
重啟下監聽
[oracle@db1 admin]$ lsnrctl stop
[oracle@db1 admin]$ lsnrctl start
6.復制監聽文件、參數文件、密碼文件到備庫
#注意一下我這邊$ORACLE_HOME
[oracle@db1 ~]$ echo $ORACLE_HOME
/opt/oracle/product/11.2.0/db_1
#傳輸啟動參數文件
[oracle@db1 ~]$ scp /tmp/db2.pfile db2:~
#傳輸密碼文件
[oracle@db1 ~]$ scp /opt/oracle/product/11.2.0/db_1/dbs/orapwmember db2:/opt/oracle/product/11.2.0/db_1/dbs/orapwmember
#傳輸監聽文件
[oracle@db1 ~]$ scp -r /opt/oracle/product/11.2.0/db_1/network/admin/{listener.ora,tnsnames.ora} db2:/opt/oracle/product/11.2.0/db_1/network/admin/
在db2上修改/opt/oracle/product/11.2.0/db_1/network/admin/listener.ora,將db1修改為db2
[oracle@db2 ~]$ vim /opt/oracle/product/11.2.0/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = member)
(ORACLE_HOME = /opt/oracle/product/11.2.0/db_1)
(SID_NAME = member)
)
)
#只需要修改listener.ora即可，tnsnames.ora不需要動
7.主庫創建standby控制文件，我們這邊利用scp傳送全部文件
查看下控制文件的路徑
(db1)SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/member/control01.ctl
/opt/oracle/flash_recovery_area/member/control02.ctl
(db1)SQL> shutdown immediate;
(db1)SQL> startup mount;
(db1)SQL> alter database create standby controlfile as '/opt/oracle/oradata/member/standby.ctl';
#在/opt/oracle/oradata/member/目錄下創建standby.ctl備機控制文件
8.復制主庫數據文件和日志文件到備庫
[oracle@db1 ~]$ scp -r /opt/oracle/flash_recovery_area/ /opt/oracle/admin/ /opt/oracle/diag/ /opt/oracle/oradata/ db2:/opt/oracle
9.初始化備庫
在備機上使用standby的控制文件覆蓋原有的控制文件，覆蓋的路徑可以通過上一步查找控制文件的路徑了解到
[oracle@db2 ~]$ cp /opt/oracle/oradata/member/standby.ctl /opt/oracle/oradata/member/control01.ctl
[oracle@db2 ~]$ cp /opt/oracle/oradata/member/standby.ctl /opt/oracle/flash_recovery_area/member/control02.ctl
使用db2.pfile之前修改過的參數文件進行db2
(db2)SQL> startup pfile='/home/oracle/db2.pfile' nomount;
(db2)SQL> create spfile from pfile='/home/oracle/db2.pfile';
(db2)SQL> shutdown immediate;
(db2)SQL> startup nomount;
(db2)SQL> alter database mount standby database;
(db2)SQL> alter database open read only;
以下3種應用日志的方法：（a和b選一）
a.開啟實時應用日志，這樣在主庫插入立馬就可以在備機上查找到
(db2)SQL> alter database recover managed standby database using current logfile disconnect from session;
b.開啟redolog應用日志，時間較長才能查詢到
(db2)SQL> alter database recover managed standby database disconnect from session;
c.停止應用redolog，只接受日志，不重做
(db2)SQL> alter database recover managed standby database cancel;
至此，DataGuard搭建成功，在db1上創建表并插入數據，然后在db2上進行查詢就可以查到了。一開始做的時候總是查不到數據，最后發現是上面應用日志的方法問題。
10.Dataguard測試：
查看Standby管理進程
(db1)SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
LNS WRITING
(db2)SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
MRP0 APPLYING_LOG
RFS IDLE
RFS IDLE
以上需要看到在主機上需要有LNS進程，在備機上需要RFS進程用來接收redo日志，MRP0進程就是負責將日志寫入數據庫中
在db1上進行切換日志，然后在db2上查看日志是否正常
(db1)SQL> select sequence#,applied from v$archived_log;
(db1)SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
11
(db2)SQL> select sequence#,applied from v$archived_log;
(db2)SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
11
(db1)SQL> alter system switch logfile;
(db1)SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
12
(db2)SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
12
從上面可以看出日志切換成功，DataGuard正常運行
11.主備切換測試：
db1---primary/db2---standby ===》db2--primary/db1--standby
[oracle@db1 ~]$ lsnrctl stop
(db1)SQL> alter database commit to switchover to physical standby with session shutdown;
(db1)SQL> shutdown immediate;
(db1)SQL> startup mount;
(db1)SQL> alter database open read only;
(db1)SQL> alter database recover managed standby database using current logfile disconnect from session;
//在執行這條的時候，如果出現
ERROR at line 1:
ORA-01665: control file is not a standby control file
則是沒有執行alter database commit to switchover to physical standby with session shutdown;
[oracle@db1 ~]$ lsnrctl start
(db2)SQL> alter database commit to switchover to primary;
注意： 若出現ORA-16139: media recovery required，執行如下語句: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SQL> alter database commit to switchover to primary; 如果出現,則可能是已打開了會話,加上with session shutdown強制關閉繪畫 ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected SQL> alter database commit to switchover to primary WITH SESSION SHUTDOWN; |
(db2)SQL> shutdown immediate;
(db2)SQL> startup;
以上就是主備切換的流程
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
