前面我们有讲到Oracle 11G DataGuard的搭建,10G的搭建也是大同小异.不过Oracle 10G不支持Standby open
环境:
| 角色 | 主机名 | IP | 数据库版本 | 操作系统版本 |
| Primary | fdb1 | 192.168.10.8 | 10.2.0.1 | CentOS 5.11 x86_64 |
| Standby | fdb2 | 192.168.10.9 | 10.2.0.1 | CentOS 5.11 x86_64 |
在fdb1的/etc/hosts中加入(fdb1)
|
1
2
|
127.0.0.1 fdb1
192.168.10.9 fdb2
|
在fdb2的/etc/hosts中加入(fdb2)
|
1
2
|
127.0.0.1 fdb2
192.168.10.8 fdb1
|
创建必要的目录(fdb1,fdb2)
|
1
2
3
4
5
|
mkdir
-p
/opt/oracle/flash_recovery_area
mkdir
-p
/opt/oracle/admin/fengdb/
{a,b,c,u}dump
mkdir
/opt/oracle/oradata/fengdb
-p
mkdir
-p
/opt/oracle/dbackup
mkdir
-p
/opt/oracle/flash_recovery_area/fengdb/archivelog
|
查看当前的redo组(fdb1)
|
1
2
3
4
5
6
|
select
group
#,member from v$logfile;
//
增加standby日志组
alter database add standby logfile (
'/opt/oracle/oradata/fengdb/standby04.log'
) size 50m;
alter database add standby logfile (
'/opt/oracle/oradata/fengdb/standby05.log'
) size 50m;
alter database add standby logfile (
'/opt/oracle/oradata/fengdb/standby06.log'
) size 50m;
alter database add standby logfile (
'/opt/oracle/oradata/fengdb/standby07.log'
) size 50m;
|
创建原始参数文件用于备份(fdb1)
|
1
|
create pfile=
'/tmp/fengdb.pfile.ori'
from spfile;
|
修改相关参数用于DataGuard环境，注意此处与Oracle 11G不同(fdb1)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
alter system
set
db_unique_name=fdb1 scope=spfile;
alter system
set
log_archive_config=
'dg_config=(fdb1,fdb2)'
scope=spfile;
alter system
set
log_archive_dest_1=
'location=/opt/oracle/flash_recovery_area/fengdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=fdb1'
scope=spfile;
alter system
set
log_archive_dest_2=
'service=fdb2 async valid_for=(online_logfiles,primary_role) db_unique_name=fdb2'
scope=spfile;
alter system
set
log_archive_dest=
''
scope=spfile;
alter system
set
log_archive_dest_state_1=
enable
scope=spfile;
alter system
set
log_archive_dest_state_2=
enable
scope=spfile;
alter system
set
standby_file_management=auto scope=spfile;
alter system
set
fal_server=fdb2 scope=spfile;
alter system
set
fal_client=fdb1 scope=spfile;
alter system
set
db_file_name_convert=
'/opt/oracle/flash_recovery_area'
,
'/opt/oracle/flash_recovery_area'
scope=spfile;
alter system
set
log_file_name_convert=
'/opt/oracle/flash_recovery_area'
,
'/opt/oracle/flash_recovery_area'
scope=spfile;
|
| 注意：与Oracle 11G不同的地方有: alter system set log_archive_dest='' scope=spfile; 否则可能出现ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST 以及上面都是直接修改spfile的，不修改当前运行中的参数 scope=spfile |
执行上面的语句其实就是改了下面的一些参数
| *.db_unique_name='fdb1' *.log_archive_config='dg_config=(fdb1,fdb2)' *.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/fengdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=fdb1' *.log_archive_dest_2='service=fdb2 async valid_for=(online_logfiles,primary_role) db_unique_name=fdb2' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.standby_file_management='AUTO' *.fal_client='fdb1' *.fal_server='fdb2' *.db_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' *.log_file_name_convert='/opt/oracle/flash_recovery_area','/opt/oracle/flash_recovery_area' *.log_archive_dest='' |
重启数据库以使数据库生效(fdb1)
|
1
2
|
shutdown
immediate
startup
|
修改监听(fdb1)
vim $ORACLE_HOME/network/admin/tnsnames.ora
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
fdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fdb1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fdb1)
)
)
fdb2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fdb2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fdb2)
)
)
|
RMAN备份(fdb1)
|
1
2
3
4
5
6
7
8
9
10
|
rman target /
run{
allocate channel c1
type
disk;
backup
format
'/opt/oracle/dbackup/fengdb_%T_%s_%p'
database;
sql
'alter system archive log current'
;
backup
format
'/opt/oracle/dbackup/archive_log_%T_%s_%p'
archivelog all;
backup spfile
format
'/opt/oracle/dbackup/spfile_%u_%T.bak'
;
release channel c1;
}
copy current controlfile
for
standby to
'/opt/oracle/dbackup/standby.ctl'
;
|
将备份及监听文件和密码文件全部复制到fdb2上（fdb1）
scp -r /opt/oracle/dbackup/* fdb2:/opt/oracle/dbackup
scp -r $ORACLE_HOME/network/admin/* fdb2:$ORACLE_HOME/network/admin/
scp -r $ORACLE_HOME/dbs/* fdb2:$ORACLE_HOME/dbs/
在fdb2上执行如下对数据库进行恢复(fdb2)
RMAN> startup nomount;
RMAN> restore spfile to pfile '/tmp/fengdb.pfile' from '/opt/oracle/dbackup/spfile_rmrioont_20161019.bak';
RMAN> shutdown immediate;
然后修改/etc/fengdb.pfile成如下的红色部分
*.db_unique_name='fdb2'
*.fal_client='fdb2'
*.fal_server='fdb1'
*.log_archive_config='dg_config=(fdb2,fdb1)'
*.log_archive_dest_1='location=/opt/oracle/flash_recovery_area/fengdb/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=fdb2'
*.log_archive_dest_2='service=fdb1 async valid_for=(online_logfiles,primary_role) db_unique_name=fdb1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
备库复制控制文件(fdb2)
cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/fengdb/control01.ctl
cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/fengdb/control02.ctl
cp /opt/oracle/dbackup/standby.ctl /opt/oracle/oradata/fengdb/control03.ctl
启动至mount状态进行数据的恢复
|
1
2
|
RMAN> startup
mount
;
RMAN> restore database;
|
启动备库应用日志
|
1
|
SQL> alter database recover managed standby database using current logfile disconnect from session;
|
创建参数文件
|
1
|
SQL> create spfile from pfile=
'/tmp/fengdb.pfile'
;
|
| 注意:Oracle 10G与11G的区别: 10G备库只能启动到mount状态，而11G可以启动到open read only. |
检查日志是否同步(fdb1,fdb2)
|
1
|
select
sequence
#,applied from v$archived_log;
|
也可以试着切换下日志
|
1
2
3
|
//
切换日志
alter system switch logfile;
select
sequence
#,applied from v$archived_log;
|
在主库：(fdb1)
|
1
2
|
select
dest_name,status,error from
v
$archive_dest;
alter system
set
log_archive_dest_state_2=
enable
;
|
查询角色(fdb1,fdb2)
|
1
|
select
open_mode,database_role from
v
$database;
|
如果在备库上
SQL> select sequence#,applied from v$archived_log;
no rows selected
而且监听都是正常的，则有可能就是密码没复制过来
保持主库密码和备库密码一致
复制密码文件(fdb1)
|
1
|
scp
$ORACLE_HOME
/dbs/orapw
$ORACLE_SID fdb2:$ORACLE_HOME
/dbs/orapw
$ORACLE_SID
|
Oracle 10G主备切换
db1---primary/db2---standby ===》db2--primary/db1--standby
[oracle@fdb1 ~]$ lsnrctl stop
(fdb1)SQL> alter database commit to switchover to physical standby with session shutdown;
(fdb1)SQL> shutdown immediate;
(fdb1)SQL> startup mount;
(fdb1)SQL> alter database recover managed standby database using current logfile disconnect from session;
//在执行这条的时候，如果出现
ERROR at line 1:
ORA-01665: control file is not a standby control file
则是没有执行alter database commit to switchover to physical standby with session shutdown;
若出现ORA-38500: USING CURRENT LOGFILE option not available without stand
这种情况出现在主备切换之后，备再切换回主的情况下出现
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/fengdb/redo03.log
/opt/oracle/oradata/fengdbredo02.log
/opt/oracle/oradata/fengdb/redo01.log
增加standby flog即可
alter database add standby logfile ('/opt/oracle/oradata/fengdb/standby04.log') size 50m;
alter database add standby logfile ('/opt/oracle/oradata/fengdb/standby05.log') size 50m;
alter database add standby logfile ('/opt/oracle/oradata/fengdb/standby06.log') size 50m;
alter database add standby logfile ('/opt/oracle/oradata/fengdb/standby07.log') size 50m;
[oracle@fdb1 ~]$ lsnrctl start
(fdb2)SQL> alter database commit to switchover to primary;
| 注意： 若出现ORA-16139: media recovery required，执行如下语句: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SQL> alter database commit to switchover to primary; 如果出现,则可能是已打开了会话,加上with session shutdown强制关闭绘画 ERROR at line 1: ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected SQL> alter database commit to switchover to primary WITH SESSION SHUTDOWN; |
(fdb2)SQL> shutdown immediate;
(fdb2)SQL> startup;
本文转自 rong341233 51CTO博客，原文链接:http://blog.51cto.com/fengwan/1864165
