1. 首页
  2. IT资讯

10GR2下创建物理standby STEP BY STEP

这篇文章主要介绍如何在10g下创建物理standby。服务器信息:主库: IP:10.2.98.10 SID:Primary备库: IP:10.2.98.11 SID:Primary OS平台都是基于linux,数据库版本是10.2.0.3具体步骤如下:

1、在主数据库执行force loggingSQL> alter database force logging;Database altered.2、在主库创建密码文件(如果已经存在则略过此步骤)3、在主库创建standby redo log在主库上建立standby log,大小与主库联机日志大小一样,组数至少大1(这个是可选,是为了角色切换方便)SQL> alter database add standby logfile group 4 ‘/u01/oracle/oradata/primary/standbyredo04.log’ size 50m;Database altered.SQL> alter database add standby logfile group 5 ‘/u01/oracle/oradata/primary/standbyredo5.log’ size 50m;Database altered.SQL> alter database add standby logfile group 6 ‘/u01/oracle/oradata/primary/standbyredo6.log’ size 50m;Database altered.SQL> alter database add standby logfile group 7 ‘/u01/oracle/oradata/primary/standbyredo7.log’ size 50m;Database altered.4、设置主库初始化参数SQL> create pfile from spfile;File created.然后编辑生成的pfile,主要修改的地方如下:DB_UNIQUE_NAME=primaryLOG_ARCHIVE_CONFIG=’DG_CONFIG=(primary,standby)’LOG_ARCHIVE_DEST_1=’LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary’LOG_ARCHIVE_DEST_2=’SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby’LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arcLOG_ARCHIVE_MAX_PROCESSES=4COMPATIBLE = 10.2.0.3#以下参数是为了角色切换设置FAL_CLIENT = primaryFAL_SERVER = standbySTANDBY_FILE_MANAGEMENT =AUTOlog_file_name_convert=’/u01/oracle/oradata/primary/’,’/u01/oracle/oradata/primary/’5、设置归档模式SQL> startup mount pfile=?/dbs/initprimary.oraTotal System Global Area 167772160 bytesFixed Size 1260696 bytesVariable Size 125829992 bytesDatabase Buffers 37748736 bytesRedo Buffers 2932736 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> create spfile from pfile;File created.6、在主库用RMAN做一个全备[oracle@primary ~]$ rman target /Recovery Manager: Release 10.2.0.3.0 – Production on Sun Jun 17 02:50:40 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: PRIMARY (DBID=1462491904)RMAN> backup database format=’/u01/backup/%U_%s.bak’;Starting backup at 17-JUN-07using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=118 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/oracle/oradata/primary/system01.dbfinput datafile fno=00002 name=/u01/oracle/oradata/primary/undotbs01.dbfinput datafile fno=00003 name=/u01/oracle/oradata/primary/sysaux01.dbfinput datafile fno=00004 name=/u01/oracle/oradata/primary/users01.dbfchannel ORA_DISK_1: starting piece 1 at 17-JUN-07channel ORA_DISK_1: finished piece 1 at 17-JUN-07piece handle=/u01/backup/01ikfgkh_1_1_1.bak tag=TAG20070617T025056 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:35channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 17-JUN-07channel ORA_DISK_1: finished piece 1 at 17-JUN-07piece handle=/u01/backup/02ikfglk_1_1_2.bak tag=TAG20070617T025056 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04Finished backup at 17-JUN-07RMAN> sql “Alter System Archive Log Current”;sql statement: Alter System Archive Log CurrentRMAN> Backup filesperset 10 ArchiveLog all format=’/u01/backup/%U_%s.bak’;Starting backup at 17-JUN-07current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=22 recid=1 stamp=625459450input archive log thread=1 sequence=23 recid=2 stamp=625459916input archive log thread=1 sequence=24 recid=3 stamp=625459929channel ORA_DISK_1: starting piece 1 at 17-JUN-07channel ORA_DISK_1: finished piece 1 at 17-JUN-07piece handle=/u01/backup/03ikfgmr_1_1_3.bak tag=TAG20070617T025210 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-JUN-07把备份文件传到备库中[oracle@primary backup]$ pwd/u01/backup[oracle@primary backup]$ ls01ikfgkh_1_1_1.bak 02ikfglk_1_1_2.bak 03ikfgmr_1_1_3.bak[oracle@primary backup]$ pwd /u01/backup[oracle@primary backup]$ scp * 10.2.98.11:`pwd`oracle@10.2.98.11’s password: 01ikfgkh_1_1_1.bak 100% 298MB 3.0MB/s 01:39 02ikfglk_1_1_2.bak 100% 6976KB 2.3MB/s 00:03 03ikfgmr_1_1_3.bak 100% 178KB 178.0KB/s 00:00 也可以用热备的方法备份,把数据文件和归档传到备库中7、在主库创建备用服务器控制文件SQL> alter database create standby controlfile as ‘/u01/backup/standby.ctl’;Database altered.拷贝到备库,并复制多份–主库[oracle@primary backup]$ scp standby.ctl 10.2.98.11:/u01/oracle/oradata/primaryoracle@10.2.98.11’s password: standby.ctl 100% 6896KB 3.4MB/s 00:02 –备库[oracle@standby backup]$ mkdir -p /u01/oracle/oradata/primary[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control01.ctl [oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control02.ctl[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control03.ctl 8、设置备库参数文件从主库传送pfile到备库中。[oracle@primary dbs]$ scp initprimary.ora 10.2.98.11:`pwd`oracle@10.2.98.11’s password: initprimary.ora 100% 1528 1.5KB/s 00:00 修改如下参数:*.DB_UNIQUE_NAME=’standby’*.FAL_CLIENT=’standby’*.FAL_SERVER=’primary’*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(primary,standby)’*.LOG_ARCHIVE_DEST_1=’LOCATION=/u01/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby’*.LOG_ARCHIVE_DEST_2=’SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary’*.STANDBY_FILE_MANAGEMENT =AUTO*.control_files=’/u01/oracle/oradata/primary/control01.ctl’,’/u01/oracle/oradata/primary/control02.ctl’,’/u01/oracle/oradata/primary/control03.ctl’*.COMPATIBLE = 10.2.0.3*.log_file_name_convert=’/u01/oracle/oradata/primary/’,’/u01/oracle/oradata/primary/’9、在备库创建密码文件[oracle@standby dbs]$ orapwd file=orapwprimary password=suk entries=10在备库上创建目录:[oracle@standby dbs]$ mkdir -p /u01/oracle/admin/primary/{adump,bdump,cdump,udump}10、在备库端还原数据库[oracle@standby dbs]$ sqlplus “/as sysdba”SQL*Plus: Release 10.2.0.3.0 – Production on Sun Jun 24 00:19:36 2007Copyright (c) 1982, 2006, Oracle. All Rights Reserved.Connected to an idle instance.SQL> startup nomount pfile=?/dbs/initprimary.oraORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1260696 bytesVariable Size 75498344 bytesDatabase Buffers 88080384 bytesRedo Buffers 2932736 bytesSQL> alter database mount standby database;Database altered.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – ProductionWith the Partitioning and Data Mining options[oracle@standby dbs]$ $ORACLE_HOME/bin/rman target /Recovery Manager: Release 10.2.0.3.0 – Production on Sun Jun 24 00:20:10 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: PRIMARY (DBID=1463363807, not open)RMAN> restore database;Starting restore at 24-JUN-07using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=153 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/oracle/oradata/primary/system01.dbfrestoring datafile 00002 to /u01/oracle/oradata/primary/undotbs01.dbfrestoring datafile 00003 to /u01/oracle/oradata/primary/sysaux01.dbfrestoring datafile 00004 to /u01/oracle/oradata/primary/users01.dbfchannel ORA_DISK_1: reading from backup piece /u01/backup/01il1ila_1_1_1.bakchannel ORA_DISK_1: restored backup piece 1piece handle=/u01/backup/01il1ila_1_1_1.bak tag=TAG20070623T231553channel ORA_DISK_1: restore complete, elapsed time: 00:00:36Finished restore at 24-JUN-07RMAN> restore archivelog all;Starting restore at 24-JUN-07using channel ORA_DISK_1channel ORA_DISK_1: starting archive log restore to default destinationchannel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=10channel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=11channel ORA_DISK_1: restoring archive logarchive log thread=1 sequence=12channel ORA_DISK_1: reading from backup piece /u01/backup/03il1inl_1_1_3.bakchannel ORA_DISK_1: restored backup piece 1piece handle=/u01/backup/03il1inl_1_1_3.bak tag=TAG20070623T231708channel ORA_DISK_1: restore complete, elapsed time: 00:00:06Finished restore at 24-JUN-0711、分别在主库和备库配置监听并启动在主库,listener.ora配置如下:[oracle@primary admin]$ more listener.ora # listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1) (SID_NAME = primary) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) )在备库,listener.ora配置如下:[oracle@primary admin]$ more listener.ora # listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = primary) (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1) (SID_NAME = primary) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) ) 12、在主库和备库分别配置tnsnames在主库和备库的tnsnames.ora都做以下配置:[oracle@primary admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.10)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primary) ) )STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.11)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primary) ) )13、备库端创建spfile在备库执行:SQL> create spfile from pfile;File created.13、启动备库 startup mount14、在备库建立standby redo log和online redo log 在备库上建立standby log,大小与主库联机日志大小一样,组数至少大1;(如果是最大性能保护模式,可以不添加standby log,但是建议添加上,避免损失更多数据) 在备库中不需要建立联机日志,它会自动创建与主库一样的日志的。首先在主库切换一次日志,使备库中记录的原主库的standby redo log信息被清除:主库执行:SQL> alter system switch logfile;稍等一会,等在主库中查询v$standby_log没有原来主库standby log的信息返回时,再在备库中添加standby redo log。如果备库正在处于恢复状态,先停止恢复,否则添加standby log会报错:ERROR at line 1:ORA-02095: specified initialization parameter cannot be modified–取消恢复状态SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.SQL> alter database add standby logfile group 4 ‘/u01/oracle/oradata/primary/standbyredo4.log’ size 50m;Database altered.SQL> alter database add standby logfile group 5 ‘/u01/oracle/oradata/primary/standbyredo5.log’ size 50m;Database altered.SQL> alter database add standby logfile group 6 ‘/u01/oracle/oradata/primary/standbyredo6.log’ size 50m;Database altered.SQL> alter database add standby logfile group 7 ‘/u01/oracle/oradata/primary/standbyredo7.log’ size 50m;Database altered.15、启动redo applySQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.16、检查物理standby情况 1)在备库检查当前的archivelogSQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME———- ——————- ——————- 22 2007-06-17 02:42:12 2007-06-17 02:44:05 23 2007-06-17 02:44:05 2007-06-17 02:51:56 24 2007-06-17 02:51:56 2007-06-17 02:52:09 2)在主库新建一个表,插入数据,然后切换日志SQL> create table test(id int);Table created.SQL> insert into test values(1);1 row created.SQL> commit;Commit complete.SQL> alter system switch logfile;System altered. 3)再次检查备库的archivelogSQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# FIRST_TIME NEXT_TIME———- ——————- ——————- 22 2007-06-17 02:42:12 2007-06-17 02:44:05 23 2007-06-17 02:44:05 2007-06-17 02:51:56 24 2007-06-17 02:51:56 2007-06-17 02:52:09 25 2007-06-17 02:52:09 2007-06-17 04:16:23 26 2007-06-17 04:16:23 2007-06-17 04:19:16 27 2007-06-17 04:19:16 2007-06-17 04:21:59 此时检查alret文件,可以看到类似于下面的信息: Sun Jun 24 16:36:32 2007Primary database is in MAXIMUM PERFORMANCE modeRFS[2]: Successfully opened standby log 4: ‘/u01/oracle/oradata/primary/standbyredo04.log’Sun Jun 24 16:36:33 2007Media Recovery Log /u01/archivelog/1_15_626106231.dbf这表示应用归档成功。 4)在备库查询数据SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Database altered.SQL> ALTER DATABASE OPEN;Database altered.SQL> select * from test; ID———- 1可以,数据已经正常同步。至此,最大性能保护模式下的DG配置完成。

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195707.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code