下面是我自己搭建的Physical Standby，按照我下面的步骤一步一步做，肯定能搭建成功的，下面的搭建步骤可以作为一个Data Guard的搭建操作手册。
HA和DG的区别：HA:可以做到IP自动切换 DG:做不到IP自动切换
主库和备库和RAC的接点个数没有关系,(生产库多少个节点,备库就是多少个节点),Oracle必须是企业版(Enterprise Edition)才可以做DG
DG级练 A->B->C(ABC可以相互切换)
主备库配置环境：
ORACLE VERSIOIN: Release 10.2.0.1.0
LINUX VERSION: Linux vmoel5u4.oracle.com 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux
192.168.92.100(primary)
hostname:vmoel5u4.oracle.com
192.168.92.200(standby)
hostname:even.oracle.com
主库和备库和RAC的接点个数没有关系,(生产点接点,备库是多接点)什么情况下用:
Oracle必须是企业版(Enterprise Edition)才可以做DG
DG级练 A->B->C(ABC可以相互切换)
以下是primary database configuration:
主库上创建相应的目录为下面所用：
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/archivelog/orcl
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
[oracle@vmoel5u4 ~]$ mkdir -p /u01/app/oracle/archivelog/orclsby
把数据库启动到mount状态，开数据库归档
alter database archivvlog
alter system set DB_UNIQUE_NAME = ORCL SCOPE=SPFILE;
一定要确保/u01/app/oracle/archivelog/orcl路径存在
alter system set log_archive_format = ‘%t_%s_%r.arc‘ scope=spfile;
alter system set log_archive_dest_1 = ‘LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl‘;
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_max_processes = 4;
打开闪回:一定要确保/u01/app/oracle/flash_recovery_area路径存在
alter system set db_recovery_file_dest = ‘/u01/app/oracle/flash_recovery_area‘ scope=spfile;
alter system set db_recovery_file_dest_size = 4G;
alter system set db_flashback_retention_target = 1440;
shutdown immediate
startup mount
alter database flashback on;
alter database open;
**执行下下面(一定要加)
alter database force logging;
在主库添加standby log file(当然备库同样等会也要加standby log file)
---日志大小一定要大于等于online redo主库的大小
---standby redo log的组数一定要大于或等于online redo log
alter database add standby logfile group 4 (‘/u01/app/oracle/oradata/orcl/redosby01.log‘) size 50M reuse;
alter database add standby logfile group 5 (‘/u01/app/oracle/oradata/orcl/redosby02.log‘) size 50M reuse;
alter database add standby logfile group 6 (‘/u01/app/oracle/oradata/orcl/redosby03.log‘) size 50M reuse;
alter database add standby logfile group 7 (‘/u01/app/oracle/oradata/orcl/redosby04.log‘) size 50M reuse;
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 STALE ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 STALE ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
4 STANDBY /u01/app/oracle/oradata/orcl/redosby01.log NO
5 STANDBY /u01/app/oracle/oradata/orcl/redosby02.log NO
6 STANDBY /u01/app/oracle/oradata/orcl/redosby03.log NO
7 STANDBY /u01/app/oracle/oradata/orcl/redosby04.log NO
7 rows selected.
***primary listener.ora
[oracle@vmoel5u4 admin]$ vi listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=vmoel5u4.oracle.com)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcl.oracle.com)
)
(SID_DESC =
(SID_NAME = orcldg)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcldg.oracle.com)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
***primary tnsnames
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmoel5u4.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
***standby listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=even.oracle.com)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcl.oracle.com)
)
(SID_DESC =
(SID_NAME = orcldg)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcldg.oracle.com)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
***standby tnsnames
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
ORCLPRI =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmoel5u4.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)
***Primary DB
alter system set dg_broker_start = false;(false表示不用broker)
alter system set remote_login_passwordfile = exclusive scope=spfile;
alter system set instance_name = orcl scope=spfile;
alter system set db_unique_name = ORCL scope=spfile;
alter system set service_names = ‘orcl.oracle.com‘;
alter system set log_archive_config = ‘DG_CONFIG=(orcl,orcldg)‘;(一定要配置)
alter system set log_archive_dest_1 = ‘LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl‘;
alter system set log_archive_dest_2 = ‘SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2
REOPEN=300 NOMAX_FAILURE‘;
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = defer;
alter system set archive_lag_target = 1800;
alter system set log_archive_max_processes = 4;
alter system set standby_archive_dest = ‘/u01/app/oracle/archivelog/orclsby‘; ---确保路径存在哦
alter system set standby_file_management = auto;
alter system reset db_create_file_dest scope=spfile sid=‘*‘; --如果db_create_file_dest为空的话，回提示ORA-32010: cannot find entry to delete in SPFILE，可以ignore
alter system set fal_server = orcldg;(永远指向别人)
alter system set fal_client = orcl;(永远指向自己)
create pfile from spfile;
SQL> shutdown immediate
***shutdown immediate 后拷贝如下几个文件(控制文件,归档,临时,闪回文件不拷贝)(考试的时候千万不要拷贝到别人的电脑上去了)
/*拷贝之前一定要到备库上去把相应的路径目录给创建好，否则在后面的操作中会提示目录不存在的*/
下面是在备库standby创建目录：
[oracle@even ~]$ mkdir -p /u01/app/oracle/oradata/orcl
[oracle@even ~]$ mkdir -p /u01/app/oracle/admin/orcl/{a,b,c,u,dp}dump;
[oracle@even ~]$ mkdir -p /u01/app/oracle/archivelog/orcl
[oracle@even ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
下面是从主库上拷贝文件到备库上：
scp /u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora even.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
scp /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl even.oracle.com:/u01/app/oracle/product/10.2.0/db_1/dbs/
scp /u01/app/oracle/oradata/orcl/*.dbf even.oracle.com:/u01/app/oracle/oradata/orcl/
scp /u01/app/oracle/oradata/orcl/*.log even.oracle.com:/u01/app/oracle/oradata/orcl/
拷贝完成后千万不要立即启动数据库(因为控制文件没有拷贝,所以pfile,主备库的pfile会不一致)
startup mount(千万不要startup,否则重新拷贝上面的内容)
alter database create standby controlfile as ‘/u01/app/oracle/oradata/orcl/control_sby.ctl‘ reuse;(千万不要写错了control_sby.ctl reuse)
alter database open;
(从主库拷贝primary的控制文件到standby)
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control01.ctl
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control02.ctl
scp /u01/app/oracle/oradata/orcl/control_sby.ctl even.oracle.com:/u01/app/oracle/oradata/orcl/control03.ctl
***备库(此时数据库是没有打开的)
cd $ORACLE_HOME/dbs
vi initorcl.ora(修改一些参数)
*.db_unique_name=‘ORCLDG‘
*.fal_client=‘ORCL‘
*.fal_server=‘ORCLPRI‘
*.log_archive_config=‘DG_CONFIG=(orcl,orcldg)‘
*.log_archive_dest_1=‘LOCATION=/u01/app/oracle/archivelog/orcl VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg‘
*.log_archive_dest_state_1=‘ENABLE‘
*.log_archive_dest_state_2=‘ENABLE‘
create spfile from pfile;
startup mount;
(standby )
SQL> alter database recover managed standby database disconnect; /*alter database recover managed standby database cancel; (这个是关掉)*/
Database altered.
在主备库上都打开跟踪文件：
tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
(以下5条命令在primary)
alter system set log_archive_dest_state_2 = enable;
(切换的此时是redo的数目加1)
alter system archive log current;
alter system archive log current;
alter system archive log current;
alter system archive log current;
(此时在standby库的跟踪文件一定要看到Media Recovery Log 才算成功,类似如下：)
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_5_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_6_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_7_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_8_847844205.arc
Media Recovery Log /u01/app/oracle/archivelog/orcl/1_9_847844205.arc
Media Recovery Waiting for thread 1 sequence 10
(下面一条在primary和standby执行,查看)
select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
/*下面是在备库查看的信息*/
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
/*下面是在主库查看的信息*/
SQL> set linesize 200
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY TO STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
/*下面主库测试dataguard数据库传输*/
drop table hr.t1 purge;
create table hr.t1 (id number);
insert into hr.t1 values (1);
commit;
alter system archive log current;
/*下面是在standby备库上操作的sql statement*/
alter database recover managed standby database cancel; /*让备库置于不redo apply的状态*/
alter database open read only; /*让备库置于read only 可以查询的状态*/
select * from hr.t1;(如果查询到数据就成功)
SQL> select * from hr.t1;
ID
----------
1
/*standby备库上，让备库处于redo apply的状态*/
alter database recover managed standby database disconnect;
(primary)(修改成MAXIMUM PROTECTION)
SQL> alter system set log_archive_dest_2=‘SERVICE=orcldg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg NODELAY MAX_CONNECTIONS=2
REOPEN=300 NOMAX_FAILURE‘;
(standby)
SQL> alter system set log_archive_dest_2=‘SERVICE=orclpri LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl NODELAY MAX_CONNECTIONS=2
REOPEN=300 NOMAX_FAILURE‘;
(以上两条千万不能写错了，否则主库会suspend)
/*下面是设置standby 在maximun protection 下*/
(primary)
shutdown immediate
startup mount
alter database set standby to maximize protection;
alter database open;
(primary & standby都看以下是否是maximun protection)
select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
/*下面是在主库查看的信息*/
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
READ WRITE PRIMARY TO STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION
/*下面是在备库查看的信息*/
SQL> set linesize 200
SQL> select open_mode, database_role, switchover_status, protection_mode, protection_level from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_MODE PROTECTION_LEVEL
---------- ---------------- -------------------- -------------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED MAXIMUM PROTECTION MAXIMUM PROTECTION
(把备库network disconnect)
在主库
insert into hr.t1 values (2);
commit;
此时主库被SUSPEND了.
把standby network connect后,主库就commit successfully.
所以这是maximun protection的缺点.
switchover between primary and standby
(primary:
shutdown immediate
startup restrict;(一定要是这种模式,不允许有连的,因为考试的时候grid control 的sysman是连着的)
select switchover_status from v$database;(switchover_status 是to standby 状态就可以切换了)
alter database commit to switchover to physical standby;
shutdown immediate
startup mount;(startup 是起不来的,因为是2个备库)
)
(standby:)
SQL> select switchover_status from v$database; /*如果是to primary就正常了,否则就问题大了*/
SWITCHOVER_STATUS
--------------------
TO PRIMARY
alter database commit to switchover to primary;
shutdown immediate;(因为控制文件主备格式不一样,必须shutdown)
(在原主库上即vmoel5u4.oracle.com主机上,此时它是备库了)
sql>alter database recover managed standby database disconnect; )
(在原备库上即even.oracle.com主机上,此时它是主库了)
sql>startup
到目前位置切换成功
可以在测试下数据是否可以在主备之间顺利传输。
再切回去一样的步骤.注意主备库不用搞乱了.
switchover between primary and standby
(primary:在even.oracle.com
shutdown immediate
startup restrict;(一定要是这种模式,不允许有连的,因为考试的时候grid control 的sysman是连着的)
select switchover_status from v$database;(switchover_status 是to standby 状态就可以切换了)
alter database commit to switchover to physical standby;
shutdown immediate
startup mount;(startup 是起不来的,因为是2个备库)
)
(standby: 在vmoel5u4.oracle.com)
SQL> select switchover_status from v$database; /*如果是to primary就正常了,否则就问题大了*/
SWITCHOVER_STATUS
--------------------
TO PRIMARY
alter database commit to switchover to primary;
shutdown immediate;(因为控制文件主备格式不一样,必须shutdown)
(在原主库上即even.oracle.com主机上,此时它是备库了)
sql>alter database recover managed standby database disconnect; )
(在原备库上即vmoel5u4.oracle.com主机上,此时它是主库了)
sql>startup
到此为止，我们有顺利的切回来了！
注意点：
先关主库,再关备库,备库先要cancel(alter database recover managed standby database cancel;)再shutdown.
开的时候是反的.备库先要cancel(alter database recover managed standby database disconnect;)
如何清掉DG：
primary:
alter database set standby database to maximize performance;
select protection_mode, protection_level from v$database;
alter system set log_archive_dest_state_2=defer;
shutdown immediate;
startup;
standby
rman: target /
rman:shutdown immediate
sql:startup mount restrict
rman:drop database;