oracle主从配置.doc

主库：IP： 10.8.3.191主机名：primaryORACLE_SID=afcORACLE_BASE=/u01/oracleORACLE_HOME=/u01/oracle/10g备库：IP： 10.8.3.192主机名：standbyORACLE_SID=afcORACLE_BASE=/u01/oracleORACLE_HOME=/u01/oracle/10g准备工作：分别在主库和备库都安装上oracle软件，不装数据库。安装配置步骤：1、运行dbca创建数据库2、登陆到数据库上oracleprimary $ sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 11 20:31:09 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.SQL conn / as sysdba;Connected.SQL3、将主库设置为 FORCE LOGGING 模式SQL ALTER DATABASE FORCE LOGGING;Database altered. 4、创建一个密码文件，如果数据库是用dbca创建的则会在$ORACLE_HOME/dbs/下自动创建一个叫orapwdSID的一个密码文件。否则可以用 orapwd命令创建一个。5、检查数据库是否处于归档状态SQL archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/app/oracle/arch_logOldest online log sequence 1Next log sequence to archive 2Current log sequence 2如果不是处于归档状态则运行下面命令SQL SHUTDOWN IMMEDIATE;SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;6、创建备库的控制文件（创建后数据库不能做结构性的改变）SQL ALTER DATABASE CREATE STANDBY CONTROLFILE AS /u01/app/oracle/oradata/dgdemo/stdby.ctl;7、创建pfile以便于修改配置参数SQL CREATE PFILE FROM SPFILE;8、关掉数据库开始编辑参数SQL shutdown imdiate;9、$ vi /u01/oracle/10g/dbs/initafc.ora （重点处）添加下面内容DB_UNIQUE_NAME=primaryLOG_ARCHIVE_CONFIG=DG_CONFIG=(primary,standby)LOG_ARCHIVE_DEST_1=location=/u01/oracle/flash_rec overy_areavalid_for=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=primaryLOG_ARCHIVE_DEST_2=SERVICE=standby LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=standbyLOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=standbyFAL_CLIENT=primaryDB_FILE_NAME_CONVERT=standby,primarySTANDBY_FILE_MANAGEMENT=AUTO10、进入sqlplus创建spfile(NOMOUNT状态)SQL CREATE SPFILE from PFILE;11、创建tnsnas.ora和listener.ora，可以手动创建也可以用ca创建，无论哪种方法都必须保证能互相畅通。$ cd /u01/oracle/10g/work/admin/$ vi tnsnas.ora输入下面内容primary =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.3.191)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = afc)standby =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.3.192)(PORT = 1521)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = afc) $ vi listener.ora SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /u01/oracle/10g)(PROGRAM = extproc) LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)12、复制备份文件到备库$ cd /u01/app/oracle/$ scp -r flash_recovery_area/ admin/ oradata/ 10.8.3.192:/u01/oracle/$ cd /u01/oracle/10g/work/admin/$ scp listener.ora tnsnas.ora 10.8.3.192:/u01/oracle/10g/work/admin/$ cd /u01/oracle/10g/dbs/$ scp orapwdafc 10.8.3.192:/u01/oracle/10g/dbs/13、打开备库做以下操作$ cd /u01/oracle/oradata/afc$ rm -rf control0*$ cp stdby.ctl control01.ctl$ cp stdby.ctl control02.ctl$ cp stdby.ctl control03.ctl$ vi initafc.ora 修改备的参数文件如下（重点处）DB_UNIQUE_NAME=standbyLOG_ARCHIVE_CONFIG=DG_CONFIG=(primary,standby)LOG_ARCHIVE_DEST_1=location=/u01/oracle/flash_rec overy_areavalid_for=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=standbyLOG_ARCHIVE_DEST_2=SERVICE=primary LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=primaryLOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_MAX_PROCESSES=30FAL_SERVER=primaryFAL_CLIENT=standbyDB_FILE_NAME_CONVERT=standby,primarySTANDBY_FILE_MANAGEMENT=AUTO$ cd /u01/app/oracle/10.2.0/db_1/work/admin/$ vi listener.ora将primary修改为standby 13、在备库中进入sqlplus$ sqlplus /nologSQL conn / as sysdba;SQL CREATE SPFILE from PFILE=/u01/app/oracle/oradata/dgdemo/pfile;14、测试oracle 是否畅通首先启动监听服务（分别在主机和备机上进行）$ lsnrctl stop$ lsnrctl start分别在主机和备机上运行下面命令来测试$ tnsping primary$ tnsping standby15、启动主库（正常启动）$ sqlplus /nologSQL conn / as sysdba;SQL startup16、启动备库到mount状态，并打开redo Apply$ sqlplus /nologSQL conn / as sysdba;SQL startup mountSQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;17、在主库上做一次日志切换SQL ALTER SYSTEM SWITCH LOGFILE; 以下语句可以查看日志应用情况SQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SEQUENCE# APP- -2 YES3 YES如果出现上面情况则说明配置成功。 18、在主库建表然后在备库查询进行验证SQL create table haha(id integer,na char(10)SQL insert into haha values(1,xiao);SQL mit;SQL select * from haha;ID NAME- -1 xiao19、在主库上做个日志归档SQL ALTER SYSTEM ARCHIVE LOG CURRENT;20、在备库上取消掉redo applySQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL ALTER DATABASE OPEN;SQL select * from haha;ID NAME- -1 xiao测试成功24、再次切换为备库的redo apply状态SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;主库备库互相切换25、在主库上做如下sql语句SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;SQL shutdown imdiate;SQL startup nomount;SQL ALTER DATABASE MOUNT STANDBY DATABASE;SQL RECOVER MANAGED STANDBY DATABASE DISCONNECT26、在备库上做switchoverSQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;SQL shutdown imdiate;SQL startup27、分别在新的主库和备库上查看状态，验证主库备库是否切换正常。SQL select na,database_role from v$database;再次创建表在切换后的主库备库上验证28、在新的主库上创建列SQL insert into haha values(2,yang);SQL mit;SQL select * from haha; ID NAME- -1 xiao2 yangSQL ALTER SYSTEM ARCHIVE LOG CURRENT; 29、在新的备库上做下面操作以取消redo applySQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQL ALTER DATABASE OPEN;SQL select * from haha;ID NAME- -1 xiao2 yang30、恢复新备库为redo apply状态最后将数据库切换出去主备状态，如果相关同步都正常说明配置没问题可以用于生产了；31、主备切换时候遇到如下错误提示（主数据切换为备数据库后再切换回