一、primary (例項名：jzh) standby(例項名：jyp)
IP地址：192.168.1.200 192.168.1.199
主機名：orcl.oracle.com orc2.oracle.com
二、主庫操作
1.開啟歸檔
SYS@jzh>alter database archivelog;
Database altered.
SYS@jzh>alter database force logging;
2.新增standby logfile
alter database add standby logfile group 4 ('/u01/oracle/oradata/jzh/redo04.log') size 50m;
alter database add standby logfile group 5 ('/u01/oracle/oradata/jzh/redo05.log') size 50m;
alter database add standby logfile group 6 ('/u01/oracle/oradata/jzh/redo06.log') size 50m;
alter database add standby logfile group 7 ('/u01/oracle/oradata/jzh/redo07.log') size 50m
3.修改listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = jzh)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
(SID_DESC =
(GLOBAL_DBNAME = jzh_dgmgrl)
(ORACLE_HOME = /u01/oracle/product/11.2.0/db_1)
(SID_NAME = jzh)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/oracle
4.修改tnsnames.ora
jzh =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jzh)
)
)
jyp =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyp)
)
)
5.修改初始化引數檔案
新增以下內容：
DB_NAME=jzh
DB_UNIQUE_NAME=jzh
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jzh,jyp)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_2=
'SERVICE=jyp ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jyp
FAL_CLIENT=jzh
DB_FILE_NAME_CONVERT='jyp','jzh'
LOG_FILE_NAME_CONVERT='jyp','jzh'
STANDBY_FILE_MANAGEMENT=AUTO
6.資料庫啟動至mount狀態,建立spfile
SYS@jzh>create spfile from pfile;
7.建立standby controlfile,開啟資料庫
SYS@jzh>alter database create standby controlfile as '/tmp/control01.ctl';
Database altered.
SYS@jzh>alter database open;
8.將密碼檔案，listener.ora，tnsnames.ora傳到備庫
scp: %ORACLE_HOME/dbs/: No such file or directory
[oracle@orcl dbs]$ scp orapwjzh 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
orapwjzh 100% 1536 1.5KB/s 00:00
9.控制檔案、初始化引數檔案傳到備庫
[oracle@orcl dbs]$ scp initjzh.ora 192.168.1.199:$ORACLE_HOME/dbs/
oracle@192.168.1.199's password:
initjzh.ora 100% 1468 1.4KB/s 00:00
三、備庫操作
1.修改初始化引數檔案
DB_NAME=jzh
DB_UNIQUE_NAME=jyp
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jyp,jzh)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=jyp'
LOG_ARCHIVE_DEST_2=
'SERVICE=jzh ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=jzh'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=jzh
FAL_CLIENT=jyp
2.解壓縮包
[oracle@orc2 oradata]$ tar -xvf jzh.tar
jzh/
jzh/redo07.log
jzh/system01.dbf
jzh/redo03.log
jzh/undotbs01.dbf
jzh/redo01.log
jzh/redo02.log
jzh/users01.dbf
jzh/redo04.log
jzh/redo05.log
jzh/redo06.log
jzh/control01.ctl
jzh/temp01.dbf
jzh/sysaux01.dbf
3.修改路徑
[oracle@orc2 oradata]$ mv jzh jyp
[oracle@orc2 oradata]$ ls
jyp jzh.tar
4.啟動備庫到mount狀態
SQL> startup mount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 490737024 bytes
Database Buffers 339738624 bytes
Redo Buffers 2396160 bytes
Database mounted.
5.應用日誌
SQL> recover managed standby database disconnect from session;
Media recovery complete.
6.日誌應用
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
13 YES
14 YES
15 YES
17 YES
16 YES
12 YES
18 YES
DATA GURAD至此搭建完成
四、配置dg_broker
兩邊例項設定dg_broker_start=true;
primary :
alter system set dg_broker_start=true;
standby :
alter system set dg_broker_start=true;
[oracle@orcl software]$ dgmgrl sys/oracle@jzh
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create CONFIGURATION macdb as primary database is jzh connect identifier is jzh;
Configuration "jzhdb" created with primary database "jzh"
DGMGRL> add database jyp as CONNECT IDENTIFIER IS jyp MAINTAINED AS PHYSICAL;
Database "jyp" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - jzhdb
Protection Mode: MaxPerformance
Databases:
jzh - Primary database
jyp - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to jyp
Performing switchover NOW, please wait...
New primary database "jyp" is opening...
Operation requires shutdown of instance "jzh" on database "jzh"
Shutting down instance "jzh"...
ORACLE instance shut down.
Operation requires startup of instance "jzh" on database "jzh"
Starting instance "jzh"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "jyp"
五、測試ADG
SYS@jzh>create table test as select * from dba_objects;
Table created.
SYS@jzh>select count(*) from test;

COUNT(*)
----------
74513
standby:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select count(*) from test;
COUNT(*)
----------
74513

11g ADG環境搭建

相關文章