oracle active data guard,Oracle Active DataGuard配置手册（一）
第一章 搭建Active DG
開始配置dg
Primary db:
1、 判斷dataguard是否安裝
SQL> select * from v$option where parameter = 'Oracle Data Guard';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Oracle Data Guard
TRUE
2、 修改主庫處于歸檔模式
SQL> startup mount
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode????????????? Archive Mode
Automatic archival???????????? Enabled
Archive destination??????????? USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence???? 34
Next log sequence to archive?? 36
Current log sequence?????????? 36
3、 將primary 數(shù)據(jù)庫置為FORCE LOGGING 模式。通過下列語句：
SQL> select FORCE_LOGGING? from? v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
4、 主庫創(chuàng)建密碼文件
同一個(gè)Dataguard中所有數(shù)據(jù)庫必須擁有獨(dú)立的密碼文件，并且保證sys用戶擁有相同密碼來保證redo的傳輸。
$ orapwd file=orapworcl password=welcome entries=3
5、 主庫配置standby redo log
SQL> startup mount
Database mounted.
SQL> alter database add standby logfile group 4
2? ('/u4/ora11/app/orcl/oradata/orcl/dg_redo1.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5
2? ('/u4/ora11/app/orcl/oradata/orcl/dg_redo2.log') size 50m;
Database altered.
6 配置監(jiān)聽
主庫：
$ cat listener.ora
ORCL=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.190)(PORT = 1522))
)
)
ADR_BASE_LISTENER = /u4/ora11/app/orcl
$ cat tnsnames.ora
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.tools.com)
)
)
DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg.tools.com)
)
)
備庫：
$ cat lis*.ora
DG=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
)
)
ADR_BASE_LISTENER = /u4/dg/app/oradg
$ cat tnsnames.ora
LISTENER_DG =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1524))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dg.tools.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = redhat1.tools.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.tools.com)
)
)
7、修改Primary 數(shù)據(jù)庫客戶端初始化參數(shù)文件
注：主要此處修改項(xiàng)較多，為了方便，我們首先創(chuàng)建并修改pfile，然后再通過pfile 重建spfile，你當(dāng)然也可以通過alter system set 命令直接修改spfile 內(nèi)容。
SQL> create pfile from spfile;
File created.
修改主庫初始化參數(shù)文件，增加下列內(nèi)容
*.DB_UNIQUE_NAME=orcl
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u4/ora11/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#--------配置standby 角色的參數(shù)用于角色轉(zhuǎn)換
*.FAL_SERVER=dg
*.FAL_CLIENT=orcl
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/u4/dg/app/oradg/oradata/dg','/u4/ora11/app/orcl/oradata/orcl '
*.LOG_FILE_NAME_CONVERT= '/u4/dg/app/oradg/oradata/dg','/u4/ora11/app/orcl/oradata/orcl '
然后將Pfile 拷貝到備庫上
$ cp /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/initorcl.ora .
$ pwd
/u4/dg/app/oradg/product/11.2.0/dbhome_1/dbs
$ id
uid=1010(oradg) gid=110(dba) groups=110(dba)
8 在主庫上創(chuàng)建備份庫需要的控制文件
SQL> startup mount
SQL> ALTER? DATABASE? CREATE? STANDBY? CONTROLFILE? AS? '/tmp/orcl.ctl';
Database altered.
9、用修改后的init.ora啟動(dòng)主庫，然后創(chuàng)建主庫的spfile
SQL> create spfile from pfile;
File created.
10、復(fù)制數(shù)據(jù)文件到standby 服務(wù)器(方式多樣，不詳述)
注意需要復(fù)制所有數(shù)據(jù)文件，備份的STANDBY控制文件及客戶端初始化參數(shù)文件(這里最好把密碼文件也復(fù)制到備庫，否則后面有可能會(huì)報(bào)錯(cuò)，具體錯(cuò)誤后面會(huì)體現(xiàn))
在standby庫操作：
$ cp -r /u4/ora11/app/orcl/oradata/orcl .
$ pwd
/u4/dg/app/oradg/oradata
$ ls
dg_bak? orcl
$ mv orcl dg
$ cp /tmp/orcl.ctl /u4/dg/app/oradg/oradata/dg
$ cd /u4/dg/app/oradg/oradata/dg
$ ls
control01.ctl? dg_redo2.log? redo01.log? redo03.log??? system01.dbf? undotbs01.dbf
dg_redo1.log?? orcl.ctl????? redo02.log? sysaux01.dbf? temp01.dbf??? users01.dbf
復(fù)制主庫參數(shù)文件：
$ cp /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/initorcl.ora .
$ pwd
/u4/dg/app/oradg/product/11.2.0/dbhome_1/dbs
Standby db配置
1、?? 創(chuàng)建控制文件
$ mv orcl.ctl control01.ctl
$ cp control01.ctl control02.ctl
$ pwd
/u4/dg/app/oradg/oradata/dg
2、配置listener,tnsname
同前面
3、設(shè)置備庫參數(shù)文件
$ cat initdg.ora
dg.__db_cache_size=603979776
dg.__java_pool_size=16777216
dg.__large_pool_size=16777216
dg.__oracle_base='/u4/dg/app/oradg'#ORACLE_BASE set from environment
dg.__pga_aggregate_target=872415232
dg.__sga_target=1308622848
dg.__shared_io_pool_size=0
dg.__shared_pool_size=654311424
dg.__streams_pool_size=0
*.audit_file_dest='/u4/dg/app/oradg/admin/dg/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/u4/dg/app/oradg/oradata/dg/control01.ctl','/u4/dg/app/oradg/oradata/dg/control02.ctl'
*.db_block_size=8192
*.db_domain='tools.com'
*.db_name='dg'? ---這里其實(shí)應(yīng)該設(shè)置為orcl，否則后面會(huì)報(bào)錯(cuò)，具體看后面文檔
*.db_recovery_file_dest='/u4/dg/app/oradg/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u4/dg/app/oradg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgXDB)'
*.local_listener='LISTENER_DG'
*.memory_target=2177892352
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=dg
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/u4/dg/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=orcl? LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#--------配置standby 角色的參數(shù)用于角色轉(zhuǎn)換
*.FAL_SERVER=orcl
*.FAL_CLIENT=dg
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT ='/u4/ora11/app/orcl/oradata/orcl','/u4/dg/app/oradg/oradata/dg'
*.LOG_FILE_NAME_CONVERT='/u4/ora11/app/orcl/oradata/orcl','/u4/dg/app/oradg/oradata/dg'
4、創(chuàng)建密碼文件：---這里其實(shí)最好直接復(fù)制主庫密碼文件，免得出錯(cuò)
$ orapwd file=orapwdg password=welcome entries=3
5、在備庫上建立spfile
SQL> startup nomount
SQL> create spfile from pfile;
File created.
6、 嘗試啟動(dòng)備庫
SQL> startup nomount
SQL>? alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01103: database name 'ORCL' in control file is not 'DG'
故障處理：在standby環(huán)境中db_name在主庫和從庫必須是一樣的
SQL> show parameter db_name
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_name????????????????????????????? string????? DG
SQL> show parameter db_uni
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_unique_name?????????????????????? string????? dg
SQL> alter system set db_name='orcl' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup nomount
SQL> show parameter db_name
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_name????????????????????????????? string????? orcl
SQL> alter database mount standby database;
Database altered.
SQL> show parameter db_uni
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_unique_name?????????????????????? string????? dg
SQL>
但是在open的時(shí)候報(bào)錯(cuò)了
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u4/dg/app/oradg/oradata/dg/system01.dbf'
故障處理：
查看備庫：
SQL>? SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
查看主庫：
SQL>? SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
FAILED DESTINATION
狀態(tài)怎么是這個(gè)呢？
再次查看主庫相關(guān)參數(shù)
SQL> show parameter log_archive_dest_2;
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2?????????????????? string????? ???SERVICE=dg? LGWR ASYNC VALID_F
OR=(ONLINE_LOGFILES,PRIMARY_RO
LE) DB_UNIQUE_NAME=dg
沒看出有什么問題
察看主庫alert.log，發(fā)現(xiàn)錯(cuò)誤如下
------------------------------------------------------------
Errors in file /u4/ora11/app/orcl/diag/rdbms/orcl/orcl/trace/orcl_arc1_12785.trc:
ORA-16191: Primary log shipping client not logged on standby
PING[ARC1]: Heartbeat failed to connect to standby 'dg'. Error is 16191.
Tue Dec 06 20:43:10 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
莫非是密碼文件的問題？
嘗試直接ftp主庫的密碼文件到備用庫
$ mv orapwdg orapwdg.bak
$ cp? /u4/ora11/app/orcl/product/11.2.0/dbhome_1/dbs/orapworcl .
$ mv orapworcl orapwdg
然后重新啟動(dòng)主庫和備庫，發(fā)現(xiàn)備庫居然開始應(yīng)用日志了
SQL> startup nomount
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select OPEN_MODE from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
看來就是密碼文件的問題啊，哈哈
日志是傳過去了，但是是否已經(jīng)apply到備庫了呢？
在備庫：
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
50 YES
51 YES
52 YES
53 YES
下面檢測(cè)下在主庫建立的表是否應(yīng)用到了備庫
SQL> select * from test;
no rows selected
發(fā)現(xiàn)表已經(jīng)有了
下面嘗試插入數(shù)據(jù)
在主庫：
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> !id
uid=1008(ora11) gid=110(dba) groups=110(dba)
SQL>
到備庫查詢：
SQL> select * from test;
TEL
----------
1
SQL> !id
uid=1010(oradg) gid=110(dba) groups=110(dba)
SQL>
發(fā)現(xiàn)已經(jīng)有了，做到了read only并且可以應(yīng)用日志！
總結(jié)
以上是生活随笔為你收集整理的oracle active data guard,Oracle Active DataGuard配置手册（一）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： android 活动传递数据,如何在An
- 下一篇： java 8 optional 类,Ja