Oracle DG 实施文档-oracle_ren-ChinaUnix博客

博客访问： 660806
博文数量： 163
博客积分： 0
博客等级：民兵
技术积分： 1625
用户组：普通用户
注册时间： 2014-11-24 11:40

个人简介

资深Oracle数据库专家 OCM认证大师 10年数据库相关服务及开发经验各类数据库相关方案的编写，管理及实施数据中心数据库日常运维、大型项目割接、性能优化等方面有丰富的实战经验客户包括：电信，银行，保险，航空,国网，汽车，烟草等想要一起学习探讨数据安全技术的请加qq群 256041954

文章分类

全部博文（163）

数据安全（0）
Mongodb（0）
Data Wareho（0）
Mysql DB（1）
NoSql DB（3）
Oracle DB（5）
Trouble sho（8）
Oracle Gold（17）
Oracle Exad（1）
未分配的博文（128）

文章存档

2017年（2）

2016年（112）

2015年（38）

2014年（11）

我的朋友

一、主备库环境

	主库	备库
数据库版本	10.2.0.5	10.2.0.5
OS版本	SunOS 5.10	SunOS 5.10
数据库名	starboss	starboss
实例名	starboss1, starboss2	dg490
IP地址	192.168.1.149/24,192.168.1.152/24	192.168.1.245/24
db_unique_name	starboss	dg490
服务名	starboss	dg490
监听器端口	1521	1521
存储类型	ASM磁盘组，+BOSSDATA	文件系统

注意：若使用OMF，配置复杂，易出问题。

在开始前确保主库rac环境正常，备库只需要安装数据库软件即可，不需要建库.

[oracle@db01 ~]$ crs_stat -t -v

Name Type R/RA F/FT Target State Host

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

ora….boss.db application 0/0 0/1 ONLINE ONLINE db01

ora....s1.inst application 0/5 0/0 ONLINE ONLINE db01

ora....s2.inst application 0/5 0/0 ONLINE ONLINE db02

ora....SM1.asm application 0/5 0/0 ONLINE ONLINE db01

ora....01.lsnr application 0/5 0/0 ONLINE ONLINE db01

ora. db0rac1.gsd application 0/5 0/0 ONLINE ONLINE db01

ora. db01.ons application 0/3 0/0 ONLINE ONLINE db01

ora. db01.vip application 0/0 0/0 ONLINE ONLINE db01

ora....SM2.asm application 0/5 0/0 ONLINE ONLINE db02

ora....02.lsnr application 0/5 0/0 ONLINE ONLINE db02

ora. db02.gsd application 0/5 0/0 ONLINE ONLINE db02

ora. db02.ons application 0/3 0/0 ONLINE ONLINE db02

ora. db02.vip application 0/0 0/0 ONLINE ONLINE db02

二、配置主库环境

两个节点上需要同样进行配置。

1监听配置：

[oracle@db01 ~]$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER_DB01 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521)(IP = FIRST))

)

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

)

SID_LIST_LISTENER_DB01 =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /opt/ora10/product/10.2.0/Ora10Home)

(GLOBAL_DBNAME=starboss)

(SID_NAME = starboss1)

)

2名称解析配置：

[oracle@db01 ~]$ cat $ORACLE_HOME/admin/tnsnames.ora

LISTENERS_STARBOSS =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1521))

)

STARBOSS2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

(INSTANCE_NAME = starboss2)

)

STARBOSS1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

(INSTANCE_NAME = starboss1)

)

STARBOSS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

)

DG490 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg490)

(INSTANCE_NAME = dg490)

)

三、配置备库环境

1监听配置：

[oracle@dgserver ~]$ cat /opt/app/oracle/product/10.2.0/db1/network/admin/listener.ora

LISTENER_DG490 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521)

)

SID_LIST_LISTENER_DG490 =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)

(GLOBAL_DBNAME=starboss)

(SID_NAME = dg490)

)

2名称解析配置：

[oracle@dgserver ~]$ cat /opt/app/oracle/product/10.2.0/db1/network/admin/tnsnames.ora

LISTENER_DG490 =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521))

)

STARBOSS2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

(INSTANCE_NAME = starboss2)

)

STARBOSS1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

(INSTANCE_NAME = starboss1)

)

STARBOSS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db01-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = db02-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = starboss)

)

DG490 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg490)

(INSTANCE_NAME = dg490)

)

四、配置主库初始化参数

配置DG主参数：

SQL>alter database force logging;

SQL>alter system set log_file_name_convert='/dataguard/','+BOSSDATA/' scope=spfile;

SQL>alter system set db_file_name_convert='/dataguard/','+BOSSDATA/' scope=spfile;

SQL>alter system set fal_client='starboss';

SQL>alter system set fal_server='dg490';

SQL>alter system set log_archive_config='DG_CONFIG=(starboss,dg490)';

SQL>alter system set log_archive_dest_1='LOCATION=+bossdata/Archive_log/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=starboss';

SQL> alter system set log_archive_dest_state_1= enable;

SQL>alter system set log_archive_dest_2='SERVICE=dg490 ARCH SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg490';

SQL> alter system set log_archive_dest_state_2=enable;

SQL> alter system set log_archive_max_processes=3;

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

SQL> alter system set standby_file_management='auto';

SQL> alter system set archive_lag_target=1800;

五、备份主库

rman target / nocatalog log=/rmanbackup/rmanlog << eof

run{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

backup maxsetsize 10g database tag tjbossdb20120907 format '/rmanbackup/bossdb20120907%d%U.dbf.%T';

}

exit

eof

六、创建备库实例

1主、备库创建密码文件:

(注意密码文件命名格式)

ORAPWD　FILE=$ORACLE_HOME/dbs/orapwsid　PASSWORD =＜PASSWORD＞

2使用主库的pfile修改相关参数用于启动备库实例：

SQL> create pfile='/home/oracle/initdg490.ora' from spfile;

File created.

修改后如下(需要创建相应的目录，名称为小写，尤其要注意目录空间是否够用)：

dg490.__db_cache_size=4689818624

dg490.__java_pool_size=16777216

dg490.__large_pool_size=16777216

dg490.__shared_pool_size=2315255808

dg490.__streams_pool_size=0

*.audit_file_dest='/opt/app/oracle/product/admin/starboss/adump'

*.background_dump_dest='/opt/app/oracle/product/admin/starboss/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/opt/oradata/dg490/control01.ctl'

*.core_dump_dest='/opt/app/oracle/product/admin/starboss/cdump'

*.cursor_sharing='similar'

*.db_2k_cache_size=400m

*.db_block_size=8192

*.db_create_file_dest=''

*.db_domain=''

*.db_file_multiblock_read_count=32

*.db_file_name_convert='+BOSSDATA/','/dataguard/'

*.db_name='starboss'

*.db_unique_name='dg490'

*.db_writer_processes=4

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg490XDB)'

*.fal_client='dg490'

*.fal_server='starboss'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(starboss,dg490)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=/dataguard/archive_log/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg490'

*.log_archive_dest_2='SERVICE=starboss ARCH SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=starboss'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='ARC%S_%R.%T'

*.log_file_name_convert='+BOSSDATA/ ','/dataguard/'

*.open_cursors=1000

*.optimizer_mode='all_rows'

*.pga_aggregate_target=2424649216

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=10480M

*.standby_file_management='auto'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/app/oracle/product/admin/starboss/udump'

七、复制备份

利用操作系统命令scp，或者ftp bin方式复制rman全备份到备库。

也可利用移动挂载阵列来实现：

#mount -F nfs -o rw 192.168.1.120:/dataguard1/oraclermanbackup /rmanbackup

八、还原备库

1在主库上创建备库控制文件

SQL> alter database create standby controlfile as '/opt/oracle/control01.ctl';

scp 控制文件control01.ctl到备库相应的位置，注意和pfile中路径一致。

2启动备库到nomount状态

SQL> conn /as sysdba

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

3将备库置于mount standby状态

SQL> alter database mount standby database;

Database altered.

4利用rman恢复出备库

oracle@linux:~> rman target /

RMAN> restore database;

5将备库置于自动恢复状态

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

Database altered.

如果是Oracle11g ADG 启动时需要:

alter database open read only;

alter database recover managed standby database using current logfile disconnect from session;

九、验证

1在主库上执行检查

SQL> conn /as sysdba

Connected.

SQL> select dest_name,status,error from v$archive_dest;

DEST_NAME STATUS ERROR

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 VALID

LOG_ARCHIVE_DEST_3 INACTIVE

LOG_ARCHIVE_DEST_4 INACTIVE

LOG_ARCHIVE_DEST_5 INACTIVE

LOG_ARCHIVE_DEST_6 INACTIVE

LOG_ARCHIVE_DEST_7 INACTIVE

LOG_ARCHIVE_DEST_8 INACTIVE

LOG_ARCHIVE_DEST_9 INACTIVE

LOG_ARCHIVE_DEST_10 INACTIVE

10 rows selected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select thread#,sequence# from v$log_history;

SEQUENCE#

----------

...

303

2在备库上执行检查

SQL> conn / as sysdba

Connected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select sequence# from v$log_history;

SEQUENCE#

----------

...

303

SQL> select thread#,sequence#,applied from v$archived_log;

SEQUENCE# APPLIE

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

295 YES

296 YES

297 YES

298 YES

299 YES

300 YES

301 YES

302 YES

303 YES

SQL> select process,status from v$managed_standby;

PROCESS STATUS

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

ARCH CLOSING

RFS IDLE

MRP0 WAIT_FOR_LOG

十、主备切换

注意：非紧急和必要时候，不得在生产库上执行切换。

1. Switchover

1.1 检查主库状态

在当前的主库端查询V$DATABASE的SWITCHOVER_STATUS列，确认是否能够执行switchover：

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO STANDBY

1 row selected

1.2 主库执行switchover

在主库端将主库转换成备库角色：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

1.3 重新挂载原主库

关闭并重启挂载原主库：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

1.4 再次检查原主库状态

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO_PRIMARY

1 row selected

1.5 备库执行switchover

在备库端将备库转换成主库角色：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

1.6 完成角色转换

如果备库尚未打开为只读模式，则打开备库为读写模式：

SQL> ALTER DATABASE OPEN;

如果备库已经打开为只读模式，则关闭并重新打开为读写模式：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

1.7新的备库端重启日志应用服务

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

1.8 发送日志

在新的主库端发送日志：

SQL> ALTER SYSTEM SWITCH LOGFILE;

2. Failover

在执行failover之后，原主库失效，不能继续直接参与DataGuard当前环境当中。

2.1识别并解决归档缺隙

查询V$ARCHIVE_GAP识别备库上是否缺少归档日志：

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

1 90 92

上例说明缺少线程1的顺序为90,91,92的三个归档日志。拷贝这些归档日志到备库中并注册：

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

2.2 重复第一步解决所有缺隙

第一步的查询仅显示最高缺隙信息，在解决那些之后，重复第一步直到不再返回任何行。

2.3 拷贝任何其他丢失的归档日志

查询V$ARCHIVED_LOG得到每个线程的归档日志的最高序列号：

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)

2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST

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

1 100

拷贝主库端的任何大于该序列号的归档日志到备库端并注册：

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

查询V$ARCHIVE_GAP确认没有引入新的缺隙。

注意:如果在前三步中，不能够解决归档日志的缺隙，在failover时将会丢失缺隙日志部分的数据。

2.4 备库端执行failover

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

2.5 转换备库角色到主库

一旦ALTER DATABASE RECOVER MANAGED STANDBY DATABASE...FINISH FORCE执行成功，则将备库切换到主库角色：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2.6 完成转换

如果备库尚未打开为只读模式，则打开备库为读写模式：

SQL> ALTER DATABASE OPEN;

如果备库已经打开为只读模式，则关闭并重新打开为读写模式：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

2.7 备份新主库

新主库打开后，应立即执行一次全备份，作为以后还原的基础。

2.8 恢复原主库

在failover后,原主库不再能够直接参与到当前的DataGuard环境当中，可通过重建数据库和DataGuard配置的方式将原主库配置到当前DataGuard环境中。

阅读(3436) | 评论(0) | 转发(0) |

上一篇：Oracle11g ADG配置

下一篇：oracle11g ADG官方文档

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6

一、 主备库环境

二、 配置主库环境

三、 配置备库环境

四、 配置主库初始化参数

五、 备份主库

六、 创建备库实例

七、 复制备份

八、 还原备库

九、 验证

十、 主备切换

一、主备库环境

二、配置主库环境

三、配置备库环境

四、配置主库初始化参数

五、备份主库

六、创建备库实例

七、复制备份

八、还原备库

九、验证

十、主备切换