oracle 10g dgmgrl主从搭建
时间:2014-11-06 05:11:24 作者:admin 标签: dg dgmgrl oracle standby 分类: Oracle
dgmrl oracle 10G dg配置 1、安装数据库版本，这里直接clone的方式进行安装
http://www.itopers.com/?p=722 2、进行一次全备，使用rman进行，如下提供下备份脚本：
#!/bin/bash
#
# oracle full backup
#
date="$(date "+%F")"
# backup dir
dir="/home/oracle/backup_stage/${date}"
if [ -d "$dir" ];then
for i in `seq 10`
do
if [ ! -d "${dir}_${i}" ];then
dir="${dir}_${i}"
break;
else
continue;
fi
done
fi
#create backup dir
[ ! -d $dir ] && mkdir $dir -p
chown -R oracle.oinstall $dir
# check user
if [ "$USER" != "oracle" ];then
echo "please execute this shell to oracle"
echo ""
exit 1
fi
# begin backup
rman target / log=$dir/incre_$date.log append <<!
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
set command id to "rman_$date";
backup incremental level 0 format '$dir/incre_0_%U' tag 'incre_0_${date}_bak' database;
release channel c1;
release channel c2;
release channel c3;
}
run {
allocate channel c4 device type disk;
allocate channel c5 device type disk;
backup archivelog all format '$dir/archive_0_%U' tag 'archivelog_$date' delete all input;
backup current controlfile for standby format '$dir/ctl_%U';
release channel c4;
release channel c5;
}
exit
!
# backup init.ora and controlfile
rm -rf /tmp/init.ora /tmp/otl.ctl
sqlplus /nolog<<!
conn / as sysdba;
create pfile='/tmp/init.ora' from spfile;
alter database backup controlfile to trace as '/tmp/otl.ctl';
exit;
!
# copy backup to backup dir
cp $ORACLE_HOME/dbs/orapw* /tmp/init.ora /tmp/otl.ctl $dir# backup end
# backup end
将备份拷到从库服务器上，我这里就是是nfs的方式，直接挂载就行。
3、修改下初始化文件，需要修改下db_unique_name，log_archivedest*，fal_server/fal_client参数
SQL> create spfile from pfile='/home/oracle/backup_stage/2014-11-04/init.ora'; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 1.0737E+10 bytes Fixed Size 2114144 bytes Variable Size 2298482080 bytes Database Buffers 8422162432 bytesRedo Buffers 14659584 bytes Redo Buffers 14659584 bytes
4、启到nomount后，进入rman进行恢复备份。
先恢复控制文件，然后启到mount状态
[oracle@statdb 2014-11-04]$ rman target / RMAN> restore controlfile from '/home/oracle/backup_stage/2014-11-04/ctl_2ppmpuql_1_1'; Starting restore at 2014-11-04 22:35:41 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output filename=/U01/app/oracle/oradata/nstatdb/control01.ctl output filename=/U01/app/oracle/oradata/nstatdb/control02.ctl output filename=/U01/app/oracle/oradata/nstatdb/control03.ctl Finished restore at 2014-11-04 22:35:46 RMAN> alter database mount; database mountedreleased channel: ORA_DISK_1 released channel: ORA_DISK_1
5、查看下备份。
RMAN>list backup;
6、恢复数据库
RMAN>restore database;
8、开始配置dg,将已经存在的configuration配置删除，remove configuration;配置好网络，tnsname,使用tnsping测试连通性。
在主从将dg_broker开启来
alter system set dg_broker_start =true;
9、进入dgmgrl管理，创建dg
[oracle@statnew backup_stage]$ dgmgrl /
DGMGRL> show configuration;
Error: ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>
DGMGRL> CREATE CONFIGURATION statdb_dg AS PRIMARY DATABASE IS nstatdb CONNECT IDENTIFIER IS nstatdb;
Configuration "statdb_dg" created with primary database "nstatdb"
DGMGRL> ADD DATABASE snstatdb2 AS CONNECT IDENTIFIER IS snstatdb2 MAINTAINED AS PHYSICAL;
Database "snstatdb2" added
DGMGRL> show configuration;
Configuration
Name: statdb_dg
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
nstatdb - Primary database
snstatdb2 - Physical standby database
Current status for "statdb_dg":
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration
Name: statdb_dg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
nstatdb - Primary database
snstatdb2 - Physical standby database
Current status for "statdb_dg":
SUCCESS
看到success已经创建成功。
附下其它查询常用查询语句：
查看数据库状态：
select db_unique_name,database_role,open_mode,switchover_status from v$database;
查看日志应用情况
select sequence#,applied from v$archived_log order by sequence# desc;
查询日志是否存在gap
select * from v$archive_gap;
手动应用日志
alter database register physical logfile '/home/oracle/backup_stage/arch/1_31026_742841399.dbf';
查看从服务器上日志应用情况
SQL> select process,pid,status,thread#,group#,sequence#,delay_mins from v$managed_standby; PROCESS PID STATUS THREAD# GROUP# SEQUENCE# DELAY_MINS --------- ---------- ------------ ---------- ---------------------------------------- ---------- ---------- ARCH 2045 CONNECTED 0 N/A 0 0 ARCH 2047 CONNECTED 0 N/A 0 0MRP0 2306 WAIT_FOR_GAP 1 N/A 31011 0 MRP0 2306 WAIT_FOR_GAP 1 N/A 31011 0
出现的问题
1、在主上添加dg后，dg_broker_config_file2配置文件没有同步，日志提示文件没有找到，后来将主从的dg_broker_start参数设置成false后，再设置成true，重新创建dg后正常
2、dg中show configuration;查看dg状态时，显示是success，无任务报错日志，但是没有同步日志。
Waiting for all non-current ORLs to be archived... Media Recovery Waiting for thread 1 sequence 31028 Thu Nov 06 11:22:10 CST 2014 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAYThu Nov 06 11:36:38 CST 2014 Thu Nov 06 11:36:38 CST 2014
在主要alter system switch logfile;也没有反应。
问题解决：
不知道为什么，之前是把从日志放到闪回里的，后面指定了一个目录，就可以了。
StandbyArchiveLocation = '/U01/app/oracle/admin/nstatdb/arch'
然后就能正常传输日志了，由于之前的日志没有传输过来，重新手动进行传
FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 31028-31068 DBID 1351766453 branch 742841399 FAL[client]: All defined FAL servers have been attempted. ------------------------------------------------------------- Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that is sufficiently large enough to maintain adequate log switch information to resolvearchivelog gaps. archivelog gaps.
生成应用日志语句。
[oracle@statnew arch]$ for i in `seq 31028 31068`
> do
> echo "alter database register physical logfile '/home/oracle/backup_stage/arch/1_${i}_742841399.dbf';" > done
> done