欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle 10g dgmgrl主从搭建

程序员文章站 2024-02-18 20:13:52
...

dgmrl oracle 10G ?dg配置 1、安装数据库版本,这里直接clone的方式进行安装 http://www.itopers.com/?p=722 2、进行一次全备,使用rman进行,如下提供下备份脚本: #!/bin/bash## oracle full backup#date="$(date "+%F")"# backup dirdir="/home/oracle/ba

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 

将备份拷到从库服务器上,我这里就是是nfs的方式,直接挂载就行。

3、修改下初始化文件,需要修改下db_unique_name,log_archive_dest_*,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 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 mounted
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          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  NODELAY
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 resolve
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