建立ORACLE10G DATA GUARD--->Physical Standby
下面是我自己建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;
/*<------FAL_SERVER and FAL_CLIENT
FAL是Fetch Archive Log的简写，它是dataguard主备之间GAP的处理机制。
Primary上不会有GAP，所以fal_server和fal_client也是仅仅在standby上生效的參数，当然为了switch over的须要相同会在primary端进行预设置。
FAL參数定义的数据库名相同取自本地tnsnames.ora里配置的Oracle Net Service Name.
------->*/
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 from session parallel 4;
可提高media recover的速度，每次recover有4个并行度。这是10g的新特性
(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;
版权声明：本文博主原创文章，博客，未经同意不得转载。
