oracle dg之--构建physical standby
系统环境：
操作系统： redhat el4
oracle: oracle 10.2.0.1.0
650) this.width=650; src=http://www.68idc.cn/help/uploads/allimg/151111/121r54538-0.jpg title=dg2.png alt=wkiol1r28c2b7wxmaacowpabg-4498.jpg>
从以上图中所示：主库为bj（db_unique_name)，备库为sh。
案例分析：
本案例采用rman duplicate方式建立standby database。
一、配置主备库初始化参数
主库的初始化参数：initbj.ora
[oracle@ocmtest dbs]$ more initbj.ora *.audit_file_dest='/u01/app/oracle/admin/bj/adump'*.background_dump_dest='/u01/app/oracle/admin/bj/bdump'*.compatible='10.2.0.1.0'*.control_files='/u01/app/oracle/oradata/bj/control01.ctl','/u01/app/oracle/oradata/bj/control02.ctl','/u01/app/oracle/oradata/bj/control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/bj/cdump'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_name='bj'*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.dispatchers='(protocol=tcp) (service=bjxdb)'*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=68157440*.processes=150*.remote_login_passwordfile='exclusive'*.sga_target=205520896*.undo_management='auto'*.undo_tablespace='undotbs1'*.user_dump_dest='/u01/app/oracle/admin/bj/udump'
以下为dg配置参数:
db_unique_name=bjlog_archive_config='dg_config=(bj,sh)'log_archive_dest_1='location=/u01/disk1/arch/bj/ valid_for=(all_logfiles,all_roles) db_unique_name=bj' log_archive_dest_2='service=sh lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=sh' log_archive_dest_state_1=enablelog_archive_dest_state_2=enableremote_login_passwordfile=exclusivelog_archive_max_processes=3fal_server=shfal_client=bjdb_file_name_convert='/u01/app/oracle/oradata/sh/','/u01/app/oracle/oradata/bj/' log_file_name_convert='/u01/app/oracle/oradata/sh/','/u01/app/oracle/oradata/bj/' standby_file_management=auto
备库的初始化参数：initsh.ora
[oracle@ocmtest1 dbs]$ more initsh.ora *.audit_file_dest='/u01/app/oracle/admin/sh/adump'*.background_dump_dest='/u01/app/oracle/admin/sh/bdump'*.compatible='10.2.0.1.0'*.control_file_record_keep_time=7*.control_files='/u01/app/oracle/oradata/sh/control01.ctl','/u01/app/oracle/oradata/sh/control02.ctl','/u01/app/oracle/oradata/sh/control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/sh/cdump'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_file_name_convert='/u01/app/oracle/oradata/bj/','/u01/app/oracle/oradata/sh/'*.db_name='bj'*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.open_cursors=300*.pga_aggregate_target=68157440*.processes=150*.remote_login_passwordfile='exclusive'*.sga_target=205520896*.undo_management='auto'*.undo_tablespace='undotbs1'*.user_dump_dest='/u01/app/oracle/admin/sh/udump'
以下为dg配置参数:
*.db_unique_name='sh'*.fal_client='sh'*.fal_server='bj'*.job_queue_processes=10*.log_archive_config='dg_config=(bj,sh)'*.log_archive_dest_1='location=/u01/disk1/arch/sh/ valid_for=(all_logfiles,all_roles) db_unique_name=sh'*.log_archive_dest_2='service=bj lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=bj'*.log_archive_dest_state_1='enable'*.log_archive_dest_state_2='enable'*.log_archive_format='arch_%t_%s_%r.log'*.log_archive_max_processes=3*.log_file_name_convert='/u01/app/oracle/oradata/bj/','/u01/app/oracle/oradata/sh/'*.standby_file_management='auto'
二、在主库上建立standby 日志
对于默认的最大性能的保护方式，可以不用建立standby redo logfiles；但建立standby redo logfiles可以更好的保存redo log；standby redo日志组组数应等于或多于redo log，日志的大小应该一致。 sql> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/bj/redo04.log') size 50m; sql> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/bj/redo05.log.rdo') size 50m; sql> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/bj/redo06.log') size 50m;
三、在备库上建立相应目录：
[oracle@ocmtest1 dbs]$mkdir -p /u01/app/oracle/admin/sh/adump[oracle@ocmtest1 dbs]$mkdir -p /u01/app/oracle/admin/sh/udump[oracle@ocmtest1 dbs]$mkdir -p /u01/app/oracle/admin/sh/bdump[oracle@ocmtest1 dbs]$mkdir -p /u01/app/oracle/admin/sh/cdump[oracle@ocmtest1 dbs]$mkdir -p /u01/app/oracle/oradata/sh
四、在主库生成备库的controlfile和备份
1、数据库在mount 状态下06:57:41 sql> alter database create standby controlfile as '/home/oracle/std_control01.ctl';2、 将生成的控制文件，拷贝到备库相同的目录下[oracle@ocmtest1 ~]$ scp /home/oracle/std_control01.ctl 192.168.8.86:/home/oracle对主库进行备份[oracle@ocmtest bj]$ rman target /recovery manager: release 10.2.0.1.0 - production on wed aug 31 10:55:55 2011copyright (c) 1982, 2005, oracle. all rights reserved.connected to target database (not started)rman> run { 2> startup force mount; 3> backup database format='/u01/disk1/rman/bj/bj_%s.bak' plus archivelog; 4> alter database open; 5> }
五、在备库上duplicate database（备库启动到弄mount）
1）在备库上建立/u01/disk1/rman/bj目录 拷贝主库的备份到备库/u01/disk1/rman/bj目录（拷贝到相同的目录下）[oracle@ocmtest bj]$ scp /u01/disk1/rman/bj/bj_*.bak 192.168.8.86:/u01/disk1/rman/bj2）配置oracle network因为，备库启动到no mount 状态，拒绝用户远程联机，所以采用静态注册，如下所示备库listenter[oracle@ocmtest1 admin]$ cat listener.ora# listener.ora network configuration file: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora# generated by oracle configuration tools.sid_list_listener = (sid_list = (sid_desc = (sid_name = plsextproc) (oracle_home = /u01/app/oracle/product/10.2.0/db_1) (program = extproc) ) (sid_desc = (global_dbname = bj) (oracle_home = /u01/app/oracle/product/10.2.0/db_1) (sid_name = bj) ) )listener = (description_list = (description = (address = (protocol = tcp)(host = ocmtest1.51cto提醒您，请勿滥发广告!)(port = 1521)) (address = (protocol = ipc)(key = extproc0)) ) )如下所示：主库的tnsnames文件：[oracle@ocmtest1 admin]$ [oracle@ocmtest admin]$ cat tnsnames.ora # tnsnames.ora network configuration file: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# generated by oracle configuration tools.bj = (description = (address_list = (address = (protocol = tcp)(host = 192.168.8.84)(port = 1521)) ) (connect_data = (service_name = bj) ))sh = (description = (address_list = (address = (protocol = tcp)(host = 192.168.8.86)(port = 1521)) ) (connect_data = (service_name = sh) (ur=a) ；添加此项连接到静态注册的备库 ))测试连接备库[oracle@ocmtest bj]$ sqlplus sys/oracle@sh as sysdbasql*plus: release 10.2.0.1.0 - production on wed aug 31 11:19:32 2011copyright (c) 1982, 2005, oracle. all rights reserved.connected to:oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining optionssql> exit disconnected from oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options通过主库连接到备库，进行恢复（通过克隆方式建立备库）[oracle@ocmtest bj]$ rman target /recovery manager: release 10.2.0.1.0 - production on wed aug 31 11:19:36 2011copyright (c) 1982, 2005, oracle. all rights reserved.connected to target database: bj (dbid=1015723911)rman> connect auxiliary sys/oracle@sh; connected to auxiliary database: bj (not mounted)rman> duplicate target database for standby; starting duplicate db at 31-aug-11using target database control file instead of recovery catalogallocated channel: ora_aux_disk_1channel ora_aux_disk_1: sid=155 devtype=diskcontents of memory script:{ restore clone standby controlfile; sql clone 'alter database mount standby database';}executing memory scriptstarting restore at 31-aug-11using channel ora_aux_disk_1channel ora_aux_disk_1: restoring control filechannel ora_aux_disk_1: copied control file copyinput filename=/home/oracle/stand.ctloutput filename=/u01/app/oracle/oradata/sh/control01.ctloutput filename=/u01/app/oracle/oradata/sh/control02.ctloutput filename=/u01/app/oracle/oradata/sh/control03.ctlfinished restore at 31-aug-11sql statement: alter database mount standby databasereleased channel: ora_aux_disk_1contents of memory script:{ set newname for tempfile 1 to /u01/app/oracle/oradata/sh/temp01.dbf; switch clone tempfile all; set newname for datafile 1 to /u01/app/oracle/oradata/sh/system01.dbf; set newname for datafile 2 to /u01/app/oracle/oradata/sh/undotbs01.dbf; set newname for datafile 3 to /u01/app/oracle/oradata/sh/sysaux01.dbf; set newname for datafile 4 to /u01/app/oracle/oradata/sh/users01.dbf; set newname for datafile 5 to /u01/app/oracle/oradata/sh/example01.dbf; restore check readonly clone database ;}executing memory scriptexecuting command: set newnamerenamed temporary file 1 to /u01/app/oracle/oradata/sh/temp01.dbf in control fileexecuting command: set newnameexecuting command: set newnameexecuting command: set newnameexecuting command: set newnameexecuting command: set newnamestarting restore at 31-aug-11allocated channel: ora_aux_disk_1channel ora_aux_disk_1: sid=155 devtype=diskchannel ora_aux_disk_1: starting datafile backupset restorechannel ora_aux_disk_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/oradata/sh/system01.dbfrestoring datafile 00002 to /u01/app/oracle/oradata/sh/undotbs01.dbfrestoring datafile 00003 to /u01/app/oracle/oradata/sh/sysaux01.dbfrestoring datafile 00004 to /u01/app/oracle/oradata/sh/users01.dbfrestoring datafile 00005 to /u01/app/oracle/oradata/sh/example01.dbfchannel ora_aux_disk_1: reading from backup piece /u01/disk1/rman/bj/bj_2.bakchannel ora_aux_disk_1: restored backup piece 1piece handle=/u01/disk1/rman/bj/bj_2.bak tag=tag20110831t105953channel ora_aux_disk_1: restore complete, elapsed time: 00:00:48finished restore at 31-aug-11contents of memory script:{ switch clone datafile all;}executing memory scriptdatafile 1 switched to datafile copyinput datafile copy recid=10 stamp=760621486 filename=/u01/app/oracle/oradata/sh/system01.dbfdatafile 2 switched to datafile copyinput datafile copy recid=11 stamp=760621487 filename=/u01/app/oracle/oradata/sh/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy recid=12 stamp=760621487 filename=/u01/app/oracle/oradata/sh/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=13 stamp=760621487 filename=/u01/app/oracle/oradata/sh/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=14 stamp=760621487 filename=/u01/app/oracle/oradata/sh/example01.dbffinished duplicate db at 31-aug-11
---备库建立完毕，并启动到mount状态！
六、在备库上启动mrp进程
11:40:28 sql> select status from v$instance; status------------mounted11:45:40 sql> alter database recover managed standby database disconnect from session; database altered.告警日志：alter database recover managed standby database disconnect from sessionwed aug 31 11:46:00 2011attempt to start background managed standby recovery process (sh)mrp0 started with pid=22, os id=14159wed aug 31 11:46:01 2011mrp0: background managed standby recovery process started (sh)managed standby recovery not using real time applywed aug 31 11:46:06 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 1 of thread 1ora-00312: online log 1 thread 1: '/u01/app/oracle/oradata/sh/redo01.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3wed aug 31 11:46:06 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 1 of thread 1ora-00312: online log 1 thread 1: '/u01/app/oracle/oradata/sh/redo01.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3clearing online redo logfile 1 /u01/app/oracle/oradata/sh/redo01.logclearing online log 1 of thread 1 sequence number 23wed aug 31 11:46:06 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 1 of thread 1ora-00312: online log 1 thread 1: '/u01/app/oracle/oradata/sh/redo01.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3wed aug 31 11:46:07 2011completed: alter database recover managed standby database disconnect from sessionwed aug 31 11:46:08 2011clearing online redo logfile 1 completewed aug 31 11:46:08 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 2 of thread 1ora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sh/redo02.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3wed aug 31 11:46:08 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 2 of thread 1ora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sh/redo02.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3clearing online redo logfile 2 /u01/app/oracle/oradata/sh/redo02.logclearing online log 2 of thread 1 sequence number 21wed aug 31 11:46:08 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 2 of thread 1ora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/sh/redo02.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3clearing online redo logfile 2 completewed aug 31 11:46:09 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 3 of thread 1ora-00312: online log 3 thread 1: '/u01/app/oracle/oradata/sh/redo03.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3wed aug 31 11:46:09 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 3 of thread 1ora-00312: online log 3 thread 1: '/u01/app/oracle/oradata/sh/redo03.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3clearing online redo logfile 3 /u01/app/oracle/oradata/sh/redo03.logclearing online log 3 of thread 1 sequence number 22wed aug 31 11:46:09 2011errors in file /u01/app/oracle/admin/sh/bdump/sh_mrp0_14159.trc:ora-00313: open failed for members of log group 3 of thread 1ora-00312: online log 3 thread 1: '/u01/app/oracle/oradata/sh/redo03.log'ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3clearing online redo logfile 3 completemedia recovery waiting for thread 1 sequence 23wed aug 31 11:47:00 2011using standby_archive_dest parameter default value as /u01/disk1/arch/sh/redo shipping client connected as public-- connected user is validrfs[1]: assigned to rfs process 14219rfs[1]: identified database type as 'physical standby'wed aug 31 11:47:00 2011rfs logminer: client disabled from further notificationwed aug 31 11:47:01 2011redo shipping client connected as public-- connected user is validrfs[2]: assigned to rfs process 14221rfs[2]: identified database type as 'physical standby'wed aug 31 11:47:01 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_7_760458507.log'rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_8_760458507.log'wed aug 31 11:47:01 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_6_760458507.log'rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_10_760458507.log'wed aug 31 11:47:01 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_9_760458507.log'wed aug 31 11:47:02 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_11_760458507.log'wed aug 31 11:47:02 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_12_760458507.log'wed aug 31 11:47:02 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_13_760458507.log'wed aug 31 11:47:02 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_14_760458507.log'wed aug 31 11:47:02 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_15_760458507.log'wed aug 31 11:47:02 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_16_760458507.log'wed aug 31 11:47:03 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_17_760458507.log'wed aug 31 11:47:03 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_18_760458507.log'rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_19_760458507.log'wed aug 31 11:47:03 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_20_760458507.log'rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_22_760458507.log'wed aug 31 11:47:04 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_21_760458507.log'wed aug 31 11:47:04 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_24_760458507.log'rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_25_760458507.log'wed aug 31 11:47:05 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_23_760458507.log'wed aug 31 11:47:05 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_26_760458507.log'rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_27_760458507.log'wed aug 31 11:47:05 2011rfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_28_760458507.log'wed aug 31 11:47:05 2011rfs[2]: archived log: '/u01/disk1/arch/sh/arch_1_29_760458507.log'wed aug 31 11:47:05 2011media recovery log /u01/disk1/arch/sh/arch_1_23_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_24_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_25_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_26_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_27_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_28_760458507.logmedia recovery log /u01/disk1/arch/sh/arch_1_29_760458507.logmedia recovery waiting for thread 1 sequence 30
七、在主库上切换日志测试数据同步
rman> sql'alter system switch logfile'; sql statement: alter system switch logfile备库告警日志：rfs[1]: no standby redo logfiles createdrfs[1]: archived log: '/u01/disk1/arch/sh/arch_1_30_760458507.log'wed aug 31 11:48:06 2011redo shipping client connected as public-- connected user is validrfs[3]: assigned to rfs process 14234rfs[3]: identified database type as 'physical standby'primary database is in maximum performance modeprimary database is in maximum performance moderfs[3]: no standby redo logfiles createdwed aug 31 11:48:08 2011media recovery log /u01/disk1/arch/sh/arch_1_30_760458507.logmedia recovery waiting for thread 1 sequence 31 (in transit)---备库接收到归档日志，并对其做media recover。
八、查看数据库信息：
18:02:08 sys@ bj >select name,database_role,protection_mode from v$database;name database_role protection_mode--------- ---------------- --------------------bj primary maximum performance18:02:50 sys@ sh >select name,database_role,protection_mode from v$database;name database_role protection_mode--------- ---------------- --------------------bj physical standby maximum performance
----@此案例采用oracle 10g的环境，对于备库在recover状态下，只能启动到mount状态，如果启动到open环境，则会停止recover，失去了数据保护的功能；对于oracle 11g，oracle启用了新的特性，“active standby”，可以将备库启动到open状态，并且可以继续recover，从而增强了备库的应用范围和备库的功能。