查看文章 |
10GR2下创建物理standby STEP BY STEP
2007-06-24 16:45
这篇文章主要介绍如何在10g下创建物理standby。 服务器信息： 主库： IP:10.2.98.10 SID:Primary 备库： IP:10.2.98.11 SID:Primary OS平台都是基于linux，数据库版本是10.2.0.3 具体步骤如下： 1、在主数据库执行force logging SQL> 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=primary LOG_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=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=4 COMPATIBLE = 10.2.0.3 #以下参数是为了角色切换设置 FAL_CLIENT = primary FAL_SERVER = standby STANDBY_FILE_MANAGEMENT =AUTO log_file_name_convert='/u01/oracle/oradata/primary/','/u01/oracle/oradata/primary/' 5、设置归档模式 SQL> startup mount pfile=?/dbs/initprimary.ora Total System Global Area 167772160 bytes Fixed Size 1260696 bytes Variable Size 125829992 bytes Database Buffers 37748736 bytes Redo Buffers 2932736 bytes Database 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 2007 Copyright (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-07 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=118 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=/u01/oracle/oradata/primary/system01.dbf input datafile fno=00002 name=/u01/oracle/oradata/primary/undotbs01.dbf input datafile fno=00003 name=/u01/oracle/oradata/primary/sysaux01.dbf input datafile fno=00004 name=/u01/oracle/oradata/primary/users01.dbf channel ORA_DISK_1: starting piece 1 at 17-JUN-07 channel ORA_DISK_1: finished piece 1 at 17-JUN-07 piece handle=/u01/backup/01ikfgkh_1_1_1.bak tag=TAG20070617T025056 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 17-JUN-07 channel ORA_DISK_1: finished piece 1 at 17-JUN-07 piece handle=/u01/backup/02ikfglk_1_1_2.bak tag=TAG20070617T025056 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 17-JUN-07 RMAN> sql "Alter System Archive Log Current"; sql statement: Alter System Archive Log Current RMAN> Backup filesperset 10 ArchiveLog all format='/u01/backup/%U_%s.bak'; Starting backup at 17-JUN-07 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=22 recid=1 stamp=625459450 input archive log thread=1 sequence=23 recid=2 stamp=625459916 input archive log thread=1 sequence=24 recid=3 stamp=625459929 channel ORA_DISK_1: starting piece 1 at 17-JUN-07 channel ORA_DISK_1: finished piece 1 at 17-JUN-07 piece handle=/u01/backup/03ikfgmr_1_1_3.bak tag=TAG20070617T025210 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 17-JUN-07 把备份文件传到备库中 [oracle@primary backup]$ pwd /u01/backup [oracle@primary backup]$ ls 01ikfgkh_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/primary oracle@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 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup nomount pfile=?/dbs/initprimary.ora ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1260696 bytes Variable Size 75498344 bytes Database Buffers 88080384 bytes Redo Buffers 2932736 bytes SQL> alter database mount standby database; Database altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With 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 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: PRIMARY (DBID=1463363807, not open) RMAN> restore database; Starting restore at 24-JUN-07 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=153 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oracle/oradata/primary/system01.dbf restoring datafile 00002 to /u01/oracle/oradata/primary/undotbs01.dbf restoring datafile 00003 to /u01/oracle/oradata/primary/sysaux01.dbf restoring datafile 00004 to /u01/oracle/oradata/primary/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/01il1ila_1_1_1.bak channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/backup/01il1ila_1_1_1.bak tag=TAG20070623T231553 channel ORA_DISK_1: restore complete, elapsed time: 00:00:36 Finished restore at 24-JUN-07 RMAN> restore archivelog all; Starting restore at 24-JUN-07 using channel ORA_DISK_1 channel ORA_DISK_1: starting archive log restore to default destination channel ORA_DISK_1: restoring archive log archive log thread=1 sequence=10 channel ORA_DISK_1: restoring archive log archive log thread=1 sequence=11 channel ORA_DISK_1: restoring archive log archive log thread=1 sequence=12 channel ORA_DISK_1: reading from backup piece /u01/backup/03il1inl_1_1_3.bak channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/backup/03il1inl_1_1_3.bak tag=TAG20070623T231708 channel ORA_DISK_1: restore complete, elapsed time: 00:00:06 Finished restore at 24-JUN-07 11、分别在主库和备库配置监听并启动 在主库，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 mount 14、在备库建立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 apply SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. 16、检查物理standby情况 1）在备库检查当前的archivelog SQL> 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）再次检查备库的archivelog SQL> 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 2007 Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log' Sun Jun 24 16:36:33 2007 Media 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配置完成。 |
最近读者：
