Oracle 11g Active DataGuard 搭建配置過程
一、環境介紹:
我在 ora11-node1 和 ora11-node2 兩臺Linux虛擬機器上首先分別安裝了一套資料庫軟體，
在 ora11-node1 主機上建立了名為zzdb的資料庫。
ora11-node1 10.0.0.31
ora11-node2 10.0.0.32
[oracle@ora11-node2 ~]$ uname -a
Linux ora11-node2 2.6.32-573.el6.x86_64 #1 SMP Thu Jul 23 15:44:03 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[oracle@ora11-node2 ~]$ cat /etc/redhat-release
CentOS release 6.7 (Final)
二、11g ADG部署：
1、primary端配置監聽
cat > $ORACLE_HOME/network/admin/listener.ora << EOF
# listener.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = zzdb)
(SID_NAME = zzdb )
(ORACLE_HOME = /oracle/product/11.2.0/db_1 )
)
)
ADR_BASE_LISTENER = /oracle/product
EOF
cat > $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
zzdb_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11-node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zzdb_dg)
)
)
zzdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora11-node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = zzdb)
)
)
EOF
2、修改primary端初始化引數檔案
startup mount;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive' scope=both;
alter database force logging;
alter database open;
alter system set log_archive_config = 'DG_CONFIG=(zzdb,zzdb_dg)' scope=both;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zzdb' scope=both;
alter system set log_archive_dest_2 = 'SERVICE=zzdb_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzdb_dg' scope=both;
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set fal_server=zzdb_dg scope=both;
alter system set fal_client=zzdb scope=both;
alter system set standby_file_management=AUTO scope=both;
alter system set db_file_name_convert='/oracle/product/oradata/zzdb','/oracle/product/oradata/zzdb' scope=spfile;
alter system set log_file_name_convert='/oracle/product/oradata/zzdb','/oracle/product/oradata/zzdb' scope=spfile;
3、在primary端pfile引數檔案和密碼檔案，並且複製到standby段相應位置
SQL> create pfile from spfile;
File created.
[oracle@ora11-node1 oracle]$ cd $ORACLE_HOME/dbs
[oracle@ora11-node1 dbs]$ scp initzzdb.ora orapwzzdb ora11-node2:$ORACLE_HOME/dbs
cd $ORACLE_BASE
[oracle@ora11-node1 product]$ scp -r admin/ diag/ ora11-node2:$ORACLE_BASE
4、修改standby端的監聽檔案及初始化引數檔案
--啟動監聽
[oracle@ora11-node2 dbs]$ lsnrctl start
--修改引數檔案
[oracle@ora11-node2 ~]$ sqlplus / as sysdba
SQL> startup nomount;
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initzzdb.ora';
SQL> shutdown abort;
SQL> startup nomount;
SQL>
alter system set db_unique_name=zzdb_dg scope=spfile;
alter system set log_archive_config='DG_CONFIG=(zzdb,zzdb_dg)' scope=both;
alter system set log_archive_dest_1 = 'LOCATION=/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=zzdb_dg' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=zzdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zzdb' scope=spfile;
alter system set fal_server=zzdb scope=spfile;
alter system set fal_client=zzdb_dg scope=spfile;
SQL> shutdown abort;
SQL> startup nomount;
5、在primary端透過Rman建立備庫,在 ora11-node1 上執行如下命令
#primary備份資料庫
mkdir -p /oracle/db_backup
rman target / <<!
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
backup database format '/oracle/db_backup/%d_%s_%p_%t.%T';
}
!
# primary生成備庫控制檔案
rman target / <<!
run {
allocate channel c1 device type disk format '/oracle/db_backup/CON_%U';
backup current controlfile for standby;
}
!
# cp備份檔案和控制檔案到standby
cd /oracle/db_backup
[oracle@ora11-node1 db_backup]$ scp * ora11-node2:/oracle/db_backup
#standby節點,建立目錄
mkdir -p /oracle/db_backup
mkdir -p /oracle/product/oradata/zzdb
[oracle@ora11-node2 db_backup]$ ll /oracle/db_backup
總用量 1213996
-rw-r----- 1 oracle oinstall 9797632 12月 29 10:23 CON_03qq19bv_1_1
-rw-r----- 1 oracle oinstall 1223499776 12月 29 10:24 ZZDB_1_1_899720408.20151229
-rw-r----- 1 oracle oinstall 9830400 12月 29 10:24 ZZDB_2_1_899720556.20151229
[oracle@ora11-node2 db_backup]$
#standby節點，恢復控制檔案
rman target / <<!
run {
restore controlfile from '/oracle/db_backup/CON_03qq19bv_1_1';
alter database mount;
}
!
#standby節點，透過rman進行恢復
rman target / <<!
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
restore database;
!
#至此已經恢復完成
6、在 primary 和 standby 端新增 standby日誌
SQL> alter database add standby logfile
group 4 ('/oracle/product/oradata/zzdb/zzdb_dgredo04.log') size 50m,
group 5 ('/oracle/product/oradata/zzdb/zzdb_dgredo05.log') size 50m,
group 6 ('/oracle/product/oradata/zzdb/zzdb_dgredo06.log') size 50m,
group 7 ('/oracle/product/oradata/zzdb/zzdb_dgredo07.log') size 50m;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
7、在standby端開啟實時日誌應用
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session;
---11g 的新功能，它允許物理備庫在應用日誌時處於只讀開啟狀態，執行以下步驟
startup mount
alter database open;
alter database recover managed standby database using current logfile disconnect;
三、測試ADG
8、執行日誌切換測試（在pri端切換歸檔，在節點二上檢查是否也發生了切換）
alter system switch logfile;
archive log list;
9、檢視standby啟動的DG程式
SQL> select process,client_process,sequence#,status from v$managed_standby;
10、檢視資料庫的保護模式：
#primary端檢視，我們可以看到資料庫的保護模式為最大效能
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
#standby 端檢視，也是一樣的。
11、檢視DG的日誌資訊
SQL> select * from v$dataguard_status;
12、在standby端， Open Read Only standby資料庫並且開啟實時日誌應用
SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> alter database recover managed standby database using current logfile disconnect from session;
13、解鎖scott使用者，新增資料，驗證資料是否能同步
#在primary端建立解鎖scott使用者並建立測試表t1，插入20000行資料
#primary 端操作如下內容
SQL> set line 100
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
SQL> alter user scott account unlock identified by tiger;
SQL> conn scott/tiger
SQL> show user
USER is "SCOTT"
SQL> create table t1 (id int primary key, birth char(8));
SQL>
declare
i int;
v_birth varchar2(8);
begin
delete t1;
commit;
for i in 1..20000
loop
insert into t1(id,birth)
values (i ,to_char(to_date('19850101','yyyymmdd')+i,'yyyymmdd'));
commit;
end loop;
end ;
/
#standby端查詢scott使用者是否解鎖，以及t1表是否建立並且插入了10000行資料：
SQL> conn scott/tiger;
SQL> select count(*) from t1;
#至此Oracle 11g ADG就已經配置完成了

ora11_node_dg（1）DG搭建過程

相關文章