Oracle DG(Data Guard)创建

最新推荐文章于 2024-07-26 09:40:31 发布

cnqmkwvbv44393245

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量121

点赞数

文章标签：数据库网络开发工具

1.DG 进程名词解释.

RFS(reomte file services) write redo log or archive log to standby redo log or archive).

MRPn(managed recovery process) log apply to standby database.

2.DG平台环境.

主库与备库OS平台与Oracle版本相同.

OS: Linux RHEL5U5(32bit)

Oracle: 11.2.0.3.0(32bit)

Host hostname ip db_unique_name Net service name

主库 TESTDB1 192.168.160.131 jiedb01 jiedb01

备库 TESTDB1-sty 192.168.160.132 jiedb01dg jiedb01dg

Primary DB:

3.主库启用强制日志模式.

sys>alter database force logging.

4.启动归档模式.

sys>archive log list;

可以用以上命令查看归档模式是否开启,如果没有开启归档模式,DB需要启动到mount状态,用sql语句alter database archivelog开启.

5.主库创建密码文件,从而使OS验证的方式登陆.

orapwd file=/u01/product/oracle/dbs/orapwjiedb01 password=oracledba entries=5;

6.创建standby logfile.

在maximize performance可以省略,但是在maximize protection或maximize availability必须建立,如果dg后续会从maximize performance转化为maximize protection或maximize availability,建议创建standby logfile. 创建standby logfile时,不要与online logfile日志组号相同.查看当前主库online logfile创建了4组日志, 故standby logfile日志组要添加5组,standby logfiles日志组数公式 (online日志组数+1)*线程数(thread#), standby logfiles日志成员大小要与online logfiles成员大小相同,且日志成员尽量冗余.

sys>alter database add standby logfile group 5 ('/u01/product/oradata/jiedb01/redo05a.log','/data/jiedb01/redo05b.log') size 50M;

sys>alter database add standby logfile group 6 ('/u01/product/oradata/jiedb01/redo06a.log','/data/jiedb01/redo06b.log') size 50M;

sys>alter database add standby logfile group 7 ('/u01/product/oradata/jiedb01/redo07a.log','/data/jiedb01/redo07b.log') size 50M;

sys>alter database add standby logfile group 8 ('/u01/product/oradata/jiedb01/redo08a.log','/data/jiedb01/redo08b.log') size 50M;

sys>alter database add standby logfile group 9 ('/u01/product/oradata/jiedb01/redo09a.log','/data/jiedb01/redo09b.log') size 50M;

7.修改主库初始化参数,创建pfile.

sys>create pfile from spfile;

vim initjiedb01.ora(如下pfile中为添加的内容)

*.db_unique_name=jiedb01
*.log_archive_config='dg_config=(jiedb01,jiedb01dg)'
*.log_archive_dest_1='location=/data/jiedb01/arch valid_for=(all_logfiles,all_roles) db_unique_name=jiedb01'
*.log_archive_dest_2='service=jiedb01dg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=jiedb01dg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_client=jiedb01
*.fal_server=jiedb01dg
*.standby_file_management='AUTO'

sys>shutdown immediate

sys>startup nomount pfile='/u01/product/oracle/dbs/initjiedb01.ora';

sys>create spfile from pfile --根据修改后的pfile文件,创建新的spfile.因为db启动会优先选择spfile,为后续db启动选择正确的spfile.

----注: 此处我的主库可以停库. 如果db主库为7*24小时模式,那只能在用sql命令 alter system set xxx(参数) scope=spfile来修改spfile; 我的主库与备库data file与log file路径相同,故参数db_file_name_convert与log_file_name_convert可以省略, 如果主/备库data file与log file路径不相同,一定要设置两个参数值.

8.主库进行备库(RMAN备份)

rman target /

rman>backup database format='/data/backup/rman_backup/%d_%s.bak';

9.主库创建备库controlfile快照文件.

alter database create standby controlfile as '/tmp/dgcontrol01.ctl';

Standby DB:

10.创建备库环境变量与相关目录.

如果备库只安装oracle软件,未安装oracle库,就要创建ORACLE_BASE,ORACLE_HOME,udump,cdump,adump等目录.此处我的备库已经安装了oracle空库,以上目录已经创建.

11.备库创建口令文件.

因为我的主/备库, 环境变量ORACLE_SID相同为jiedb01,故直接copy主库口令文件到备库上使用即可(oracle 口令文件命令格式orapwORACLE_SID). 如果主/备库用户创建的密码文件不相同,备库上,用户创建时, 一定要保护主库与备库的口令文件密码相同.

12.主库与备库上,创建listener与配置tnsname.

创建监听器listener可以用命令netca,netmgr或直接修改$ORACLE_HOME/network/admin/listener.ora文件创建.

创建tname.ora可以用命令netmgr或$ORACLE_HOME/network/admin/tnsnames.ora文件创建.

注意之处,主库与备库创建后的tnsname相同,如下.

jiedb01 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.131)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jiedb01)
    )
)

LISTENER_JIEDB01 =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTDB1)(PORT = 1526))

jiedb01dg =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.160.132)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jiedb01dg)
    )
)

启动主库与备库的监听器,并在主库与备库上相互tnsping jiedb01, tnsping jiedb01dg网络名,查看主/备库能否相互tnsping通.

13.copy主库上产生的rman备份与备库快照controlfile到备库.

主库上.

cd /data/backup/rman_backup/;

scp JIEDB01_* 192.168.160.132:/data/backup/rman_backup/

scp /tmp/dgcontrol01.ctl 192.168.160.132:/tmp/

14.创建备库pfile初始化参数文件.

因为我的主/备库, 环境变量ORACLE_SID相同为jiedb01,故主/备库的initjiedb01.ora初始化参数文件名称相同.

从主库scp initjiedb01.ora文件到备库并修改参数值. 参数中dg db_unique_name主/备库的不相同, log_archive_dest_2中service指定的值与fal_client,fal_server为网络名.

scp initjiedb01.ora 192.168.160.132:/u01/product/oracle/dbs/ --主库上

vim initjiedb01.ora ---备库上, pfile添加如下参数值.

*.control_files='/u01/product/oradata/jiedb01/dgcontrol01.ctl','/u01/product/oradata/jiedb01/dgcontrol02.ctl','/data/jiedb01/dgcontrol03.ctl'

*.db_unique_name=jiedb01dg
*.log_archive_config='dg_config=(jiedb01,jiedb01dg)'
*.log_archive_dest_1='location=/data/jiedb01/arch valid_for=(all_logfiles,all_roles) db_unique_name=jiedb01dg'
*.log_archive_dest_2='service=jiedb01 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=jiedb01'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_client=jiedb01dg
*.fal_server=jiedb01
*.standby_file_management='AUTO'

copy 主库传过来的备库快照controlfile,到如上pfile初始化参数中,control_files设置的路径中.

cp /tmp/dgcontrol01.ctl /u01/product/oradata/jiedb01/dgcontrol01.ctl

cp /tmp/dgcontrol01.ctl /u01/product/oradata/jiedb01/dgcontrol02.ctl

cp /tmp/dgcontrol01.ctl /data/jiedb01/dgcontrol03.ctl

15.启动备库,并创建spfile.

sys>startup nomount pfile='/u01/product/oracle/dbs/initjiedb01.ora';

sys>create spfile from pfile;

sys>alter database mount standby database;

16.备库rman进行恢复.

rman target /

rman>restore database;

17.备库上进行redo logfile日志应用.

sys>alter database recover managed standby database disconnect from session;

sys> select process,pid,status from v$managed_standby; --如上sql备库产生mrp进程,从v$managed_standby视图中查询.

PROCESS PID STATUS
--------- ---------- ------------
ARCH 3896 CLOSING
ARCH 3898 CONNECTED
ARCH 3900 CONNECTED
ARCH 3902 CLOSING
RFS 3912 IDLE
RFS 3910 RECEIVING
RFS 3914 IDLE
RFS 3916 IDLE
MRP0 3918 WAIT_FOR_LOG

18. 主/备库日志传送及应用测试.

主库上.

sys>alter system switch logfile; --日志切换,产生一个日志.

sys>select unique thread#,max(sequence#) over(partition by thread#) last_sequence# from v$archived_log; --查询主库每个线程最大日志序列号.

备库上.

sys>select unique thread#,max(sequence#) over(partition by thread#) last_sequence# from v$archived_log; --查看主库上新产生的日志,是否传送过来.

或备库上直接查看altert预警文件.

19. 主/备库切换.

19.1 switchover切换(计划切换或无损数据切换);

主库上.

sys> set long 100000
sys> set line 150
sys> col db_unqiue_name for a15
sys> select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;

NAME LOG_MODE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
--------- ------------ ------------- ---------------- ------------- ---------------- -------------------- ---------------
JIEDB01 ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY jiedb01

如上为切换前主库的状态. 如switchover_status为session_active时,需要用alter database commit to switchover to physical standby with session shutdown;

当switchover_status为not allowed时,官方文档说,主/备库切换会失败,我的备库switchover_status有一次实验为not allowed,但主/备库切换OK, 有一次实验主/备库switchover_status都为not allowed时,主/备库切换失败.此处要注意.

sys> alter database commit to switchover to physical standby;
sys> shutdown immediate;

sys>exit ---此处要退出,当前session处,重新用sqlplus / as sysdba登陆,如果不退出当前环境,直接startup nomount会报错,原因查待.

sqlplus / as sysdba

sys>startup nomount;
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size                  1348244 bytes
Variable Size             641731948 bytes
Database Buffers          201326592 bytes
Redo Buffers                5124096 bytes
sys> alter database mount standby database;

Database altered.

sys>alter database recover managed standby database disconnect from session;

sys>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;

NAME LOG_MODE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
--------- ------------ ---------- -------------------- -------------------- ---------------- ------------------ ---------------
JIEDB01 ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED jiedb01

备库上.

sys>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database;

NAME LOG_MODE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
-------- ---------- ----------- ------------ -------------------- ---------------- ----------------- ---------------
JIEDB01 ARCHIVELOG MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY TO PRIMARY jiedb01dg

sys> alter database commit to switchover to primary;

sys>alter database open;

sys>shutdown immediate;

sys>startup;

sys> select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME from v$database; ---原先备库做switchover切为主库后v$database参数.

NAME LOG_MODE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- --------- ------- ---------------- ----------------- ---------------- ----------------- ---------------

JIEDB01 ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY jiedb01dg

19.2. failover(非正常切换);

为主库宕机情况:

备库(192.168.160.132)

首先检查主库(假如可以查询)与备库的日志间隙

select * from v$archive_gap;

如上sql如主/备库产生间隙,备库上可以用如下sql语句,应用归档信息到数据字典中.

alter database register physical logfile ‘/data/jiedb01/arch/xxx.arc’;

检查主库与备库的sequence#

主库宕机不能查询时,可以查询alter预警文件或归档路径下的归档文件序列号,备库可以用如下sql查询.

select unique(thread#),max(sequence#) over(partition by thread#) from v$archived_log;

如下为备库运行的sql语句.

sys>alter database recover managed standby database cancel;或

sys >alter database recover managed standby database finish;

sys >alter database commit to switchover to primary;

sys >shutdown immediate;

sys >startup;

PS.主库与备库初始化参数文件中db_name相同(jiedb01).

小结: 1.主库与备库orapwjiedb01密码文件一定统一.主库与备库,如sys密码或orapwjiedb01密码文件不统一,alert会报Check that the primary and standby are using a password file......
2.备库最好建空库,datafile与logfile与主库相同,这样db_file_name_convert与log_file_name_convert两个参数就不需要设置.
3.备库建立空库后,备库建立的redo logfile与standby redo logfile要与主库建立的组数统一.
4.主库与备库中,tnsname.ora中service_name要设置正常.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28569596/viewspace-758877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28569596/viewspace-758877/

cnqmkwvbv44393245

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle DG(Data Guard)创建

1.DG进程名词解释. RFS(reomte file services)write redo log or archive log to standby redo log o...
复制链接

扫一扫