环境描述:
主库:
IP: 10.8.3.191
主机名:primary
ORACLE_SID=afc
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/10g
备库:
IP: 10.8.3.192
主机名:standby
ORACLE_SID=afc
ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/10g
准备工作:分别在主库和备库都安装上oracle软件,不装数据库。
安装配置步骤:
1、运行dbca创建数据库
2、登陆到数据库上
[oracle@primary ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 11 20:31:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL>
3、将主库设置为 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 Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch_log
Oldest online log sequence 1
Next log sequence to archive 2
Current 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 immediate;
9、$ vi /u01/oracle/10g/dbs/initafc.ora (重点处)
添加下面内容
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='location=/u01/oracle/flash_recovery_area
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
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=standby
FAL_CLIENT=primary
DB_FILE_NAME_CONVERT='standby','primary'
STANDBY_FILE_MANAGEMENT=AUTO
10、进入sqlplus创建spfile(NOMOUNT状态)
SQL> CREATE SPFILE from PFILE;
11、创建tnsnames.ora和listener.ora,可以手动创建也可以用netca创建,无论哪种方法都必须保证能互相畅通。
$ cd /u01/oracle/10g/network/admin/
$ vi tnsnames.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/network/admin/
$ scp listener.ora tnsnames.ora 10.8.3.192:/u01/oracle/10g/network/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=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='location=/u01/oracle/flash_recovery_area
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'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=primary
FAL_CLIENT=standby
DB_FILE_NAME_CONVERT='standby','primary'
STANDBY_FILE_MANAGEMENT=AUTO
$ cd /u01/app/oracle/10.2.0/db_1/network/admin/
$ vi listener.ora
将primary修改为standby
13、在备库中进入sqlplus
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> CREATE SPFILE from PFILE='/u01/app/oracle/oradata/dgdemo/pfile';
14、测试oracle net是否畅通
首先启动监听服务(分别在主机和备机上进行)
$ lsnrctl stop
$ lsnrctl start
分别在主机和备机上运行下面命令来测试
$ tnsping primary
$ tnsping standby
15、启动主库(正常启动)
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup
16、启动备库到mount状态,并打开redo Apply
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup mount
SQL> 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 YES
3 YES
如果出现上面情况则说明配置成功。
18、在主库建表然后在备库查询进行验证
SQL> create table haha(id integer,name char(10))
SQL> insert into haha values(1,'xiao');
SQL> commit;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
19、在主库上做个日志归档
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
20、在备库上取消掉redo apply
SQL> 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 immediate;
SQL> startup nomount;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT
26、在备库上做switchover
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> shutdown immediate;
SQL> startup
27、分别在新的主库和备库上查看状态,验证主库备库是否切换正常。
SQL> select name,database_role from v$database;
再次创建表在切换后的主库备库上验证
28、在新的主库上创建列
SQL> insert into haha values(2,'yang');
SQL> commit;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
2 yang
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
29、在新的备库上做下面操作以取消redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
2 yang
30、恢复新备库为redo apply状态
最后将数据库切换出去主备状态,如果相关同步都正常说明配置没问题可以用于生产了;
31、主备切换时候遇到如下错误提示(主数据切换为备数据库后再切换回主系统时出现错误),解决
如下:
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
mount模式下执行alter database recover managed standby database finish
SQL> alter database commit to switchover to primary;
Database altered.