DATAGUARD、broker安装维护

主库db_unique_name: primary

备库db_unique_name: standby

配置DATAGUARD:

####主库上操作：###########

alter system set log_archive_dest_1= 'location=/u01/app/dg/l_log/ valid_for=(all_logfiles,all_roles) db_unique_name=primary' scope=spfile;

alter system set log_archive_dest_2= 'service=standby valid_for=(online_logfiles,primary_role) db_unique_name=standby lgwr sync affirm' scope=spfile;

alter database archivelog;(mount下)

ALTER DATABASE FORCE LOGGING;

alter system set db_unique_name=primary scope=spfile;

alter system set log_archive_config='DG_CONFIG=(primary ,standby)' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;

ALTER SYSTEM SET FAL_SERVER ='standby';

ALTER SYSTEM SET FAL_client ='primary';

alter system set standby_file_management=auto;

alter system set standby_archive_dest='/u01/app/dg/s_log/' scope=spfile;

–standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。

alter database add standby logfile '/u01/app/dg/y_log/s1.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s2.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s3.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s4.log' size 50m;

alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/','/u01/app/dg/oradata' scope=spfile;

alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/','/u01/app/dg/oralog' scope=spfile;

alter database create standby controlfile as '/u01/app/dg/s_con/stancontrol.ctl';

create pfile from spfile;

scp initprimary.ora 192.168.75.62:/u01/oracle/db_1/dbs

scp /u01/app/dg/s_con/stancontrol.ctl 192.168.75.62:/u01/oracle

####备库上操作：##########

orapwd file=$ORACLE_HOME/dbs/orapwrac password=oracle entries=30;

mv initprimary.ora initstandby.ora

vi initstandby.ora

create spfile from pfile='/u01/app/dg/rman/stan.ora';

alter system set control_files ='/u01/app/dg/s_con/stancontrol.ctl' scope=spfile;

switch database to copy;

alter system set db_unique_name=standby scope=spfile;

–启动db接受或发送redo data，包括所有库的db_unique_name

alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=spfile;

alter system set standby_file_management=auto;

alter system set service_names='standby' scope=both;

ALTER SYSTEM SET FAL_SERVER ='primary';

ALTER SYSTEM SET FAL_CLIENT ='standby';

–standby redolog的组数参考公式：(online redolog组数 + 1) * 数据库线程数；单机线程数为1，RAC一般为2。

alter database add standby logfile '/u01/app/dg/y_log/s21.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s22.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s23.log' size 50m;

alter database add standby logfile '/u01/app/dg/y_log/s24.log' size 50m;

alter system set log_archive_dest_1= 'location=/u01/app/dg/l_log/ valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=spfile;

alter system set log_archive_dest_2= 'service=primary valid_for=(online_logfiles,primary_role) db_unique_name=primary lgwr sync affirm';

####启动ADG##########

SQL> alter database recover managed standby database disconnect from session;

ACTIVE DATA GURAD

开启一般模式：

SQL> alter database recover managed standby database cancel;

SQL> alter database open;

SQL> alter database recover managed standby database disconnect;

如果开启时日志实时应用的话：

Real-time Apply

alter database recover managed standby database cancel;

alter database recover managed standby database using current logfile disconnect from session;

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

——————– ——————–

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

alter database set standby database to maximize performance;

提示：maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分别对应最大保护，最高可用性及最高性能。

验证主库

SQL> Select dest_name,status,error from v$archive_dest;

##############rman duplicate到备库####################

rman target /

run

{

allocate channel d1 type disk format '/u01/backupset/%U';

allocate channel d2 type disk format '/u01/backupset/%U';

backup database;

release channel d1;

release channel d2;

}

exit

scp -r /u01/backupset 192.168.143.158:/u01/

在primary 上运行

rman target / auxiliary sys/oracle@standby

run

{

allocate auxiliary channel d1 type disk;

duplicate target database for standby nofilenamecheck;

release channel d1;

}

####################检查：######################################

查看当前主机的运行状态

select switchover_status,database_role,protection_mode from v$database;

显示备库相关进程的当前状态信息：

SQL> select process,client_process,sequence#,status from v$managed_standby;

查看备库接收、应用redo数据的过程

select message from v$dataguard_status;

查看被日志是否被应用

select sequence#, applied from v$archived_log where applied='YES' order by sequence#;

查看数据库状态

select open_mode,database_role,db_unique_name from v$database;

监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少

select * from v$archive_gap;

查看数据库角色(查看是主库还是备库)

select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

1、在生产库停止Data Guard操作：

SQL> show parameter log_archive_dest

SQL> alter system set log_archive_dest_state_2=defer;

2、在生产库开启Data Guard操作：

SQL> alter system set log_archive_dest_state_2=enable;

##############注意Data Guard 启动顺序：##############################

启动顺序：先standby ,后primary;

关闭顺序：先primary 后standby;

一.启用备用数据库

1.将备库将实例启动到mount 状态：

startup nomount;

2.启动到备用库模式

alter database mount standby database ; （也可以在此步后以只读模式打开数据库）

3.开始redo apply

alter database recover managed standby database disconnect from session;

alter database recover managed standby database parallel 32 disconnect from session;

4.如果要打开数据库，Standby只读模式打开

–停止redo apply

alter database recover managed standby database cancel;

–只读模式打开

alter database open read only ;（在此可以继续恢复备用库应用日志 Alter DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;）

）

5.验证Standby redo 是否应用

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

############## 主备库切换 ##############################

Step 1

主库：

查看switchover 状态

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

TO STANDBY

备库：

1.查看switchover状态

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

附：若不是用此语句切换：ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown

补充：若出现：ORA-16139: media recovery required

是因为没有执行：alter database recover managed standby database disconnect from session;

查看是否有日志GAP,没有应用的日志：

SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;

如果有，则拷贝过来并且注册

ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径';

重复查看直到没有应用的日志：

Step 2 开始把物理主库改变为物理备库（原主库执行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

或

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;

注：如果有活动的session可以使用此选项，否则转换会遇到ORA-01093错误，也可以杀掉活动会话或等活动会话后进行转换

Step 3 关闭并重启主库（原主库执行）

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 4 验证备库是否能执行角色转换到主库（原备库执行）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 5 开始把物理备库转换成物理主库（原备库执行）

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

–如果报ORA-16139: media recovery required，可能是由于未应用日志引起，可先执行

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Step 6 打开备库，然后关闭重启.（原备库执行）

SQL> ALTER DATABASE OPEN;

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Step 7 验证是否转换成功（原备库执行）

SQL> ALTER SYSTEM SWITCH LOGFILE;

Setp 8 应用归档日志（原主库上执行）

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

##################################配置 dg_broker#####################

1、配置listener.ora文件和tnsnames.ora文件，添加GLOBAL_DBNAME参数，重启监听器，所有的节点都需要配置

GLOBAL_DBNAME=primary_DGMGRL格式为GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain,因为实际环境中db_domain为空，以下所有涉及到service_name的就用这个GLOBAL_DBNAME了。

for example：

——primary database：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

  (SID_NAME = primary)

  (GLOBAL_DBNAME= primary_DGMGRL.localdomain)

  (ORACLE_HOME = /u01/oracle/db_1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

)

—–standby database：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

  (SID_NAME = standby)

  (GLOBAL_DBNAME= standby_DGMGRL.localdomain)

  (ORACLE_HOME = /u01/oracle/db_1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

)

–配置tnsnames.ora文件

primary =

(DESCRIPTION =

 (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.157)(PORT = 1521))

 (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = primary_DGMGRL.localdomain)

)

STANDBY =

(DESCRIPTION =

 (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.158)(PORT = 1521))

 (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = standby_DGMGRL.localdomain)

)

2、设置LOCAL_LISTENER

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.157)(PORT = 1521))' scope=spfile;

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.143.158)(PORT = 1521))' scope=spfile;

3，修改dg_broker_config_file参数。如果是在RAC环境中，这个把这个文件把到共享的存储上面，如果有ASM可以放到ASM中。

主库：

这里跟官方给的名字有一点不一样htz1.dat这里官方要求是drc.dat。

alter system set dg_broker_config_file1='/u01/oradata/primary/drcprimary1.dat';

alter system set dg_broker_config_file2='/u01/oradata/primary/drcprimary2.dat';

备库：

alter system set dg_broker_config_file1='/u01/oradata/standby/drcstandby1.dat';

alter system set dg_broker_config_file2='/u01/oradata/standby/drcstandby2.dat';

4.启用BROKER：

在两个数据库上面都执行：

alter system set dg_broker_start=true scope=both;

5.BROKER管理：

在主备库其中任意一台做下面的操作就可以了，另它安装了client的客户端也是可以的.

进入管理

[oracle@11g admin]$ dgmgrl sys/oracle@primary

help是帮忙命令

DGMGRL> help create

CREATE CONFIGURATION AS PRIMARY DATABASE IS CONNECT IDENTIFIER IS ;

开始配置管理dg######

创建一个配置文件，这里一定要记得是连接到当前的主数据库

DGMGRL> create configuration primary as primary database is primary connect identifier is primary;

启用配置文件

DGMGRL> enable configuration;

Enabled.

增加备库到配置文件中

DGMGRL> help add

ADD DATABASE [AS CONNECT IDENTIFIER IS ] [MAINTAINED AS {PHYSICAL|LOGICAL}];

DGMGRL> add database standby as connect identifier is standby;

Database "standby" added

启用备库

DGMGRL> enable database standby;

Enabled.

查看配置文件

DGMGRL> show configuration

查看某个数据库的配置消息，其中很多参数我们都可以在sqlplus修改的。

DGMGRL> show database verbose primary;

静态监听中的GLOBAL_NAME是DB_UNIQUE_NAME，所以这里我们要修改

DGMGRL> show database verbose standby;

修改StaticConnectIdentifier参数，记得两个数据库都要修改。broker里面的连接的service_name是_DGMGRL

DGMGRL> edit database primary set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.143.157)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL.localdomain)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))';

DGMGRL>edit database standby set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.143.158)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=standby_DGMGRL.localdomain)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))';

SWITCHOVER######

开始切换

DGMGRL> switchover to standby;

查看状态

DGMGRL> show configuration;

查询数据库状态：

set line 300

select DB_UNIQUE_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,PROTECTION_MODE,SWITCHOVER_STATUS,DATAGUARD_BROKER from v$database;

FAILOVER######

1.修改保护模式：

[oracle@test admin]$ dgmgrl sys/1111111

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL> show configuration;

2.修改日志传递方式

DGMGRL> edit database primary set property logxptmode=sync;

DGMGRL> edit database standby set property logxptmode=sync;

DGMGRL> show database primary logxptmode

LogXptMode = 'sync'

DGMGRL> show database standby logxptmode

LogXptMode = 'sync

配置数据库的flashback

主库上面

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

配置flashback

startup mount;

alter database flashback on;

alter database open;

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON

——————– —————- ———— ——————

READ WRITE PRIMARY ARCHIVELOG YES

flashback的配置目录与大小

SQL> show parameter recovery

NAME TYPE VALUE

———————————— ———– ——————————

db_recovery_file_dest string /u01/app/oracle/fast_recovery_

                                             area

db_recovery_file_dest_size big integer 4032M

备库上面

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON

——————– —————- ———— ——————

READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG NO

SQL> recover managed standby database cancel;

Media recovery complete.

startup mount;

alter database flashback on;

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select open_mode,database_role,log_mode,flashback_on from v$database;

OPEN_MODE DATABASE_ROLE LOG_MODE FLASHBACK_ON

——————– —————- ———— ——————

READ ONLY WITH APPLY PHYSICAL STANDBY ARCHIVELOG YES

4 配置observer服务器

5 配置tnsnames.ora文件。

6 启动observer进程

[oracle@test admin]$ dgmgrl sys/111111 "start observer"

7 配置fast-start failover

DGMGRL> edit database primary set property FastStartFailoverTarget=standby;

DGMGRL> edit database standby set property FastStartFailoverTarget=primary ;

8 启用fast-start failover

DGMGRL> enable fast_start failover

测试FAST-START FAILOVER是否生效

原主库直接shutdown abort;

SQL> shutdown abort;

SQL> select open_mode ,database_role from v$database;

手动启动原主库

DGMGRL> show configuration;

至此broker配置操作完毕。

#################################问题处理汇总##############################################

##处理ORA-01153:问题##########

Action: Complete or cancel the other media recovery session or RMAN backup

SQL> recover managed standby database cancel;

SQL> recover automatic standby database;

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;

####处理ORA-16143:问题##########

ORA-16143: RFS connections not allowed during or after terminal recovery

Thu Dec 13 07:40:47 2012

原因：在备库上曾经执行了：

alter database recover managed standby database finish ；

——-导致RFS不能再被启动

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法

在主库重建standby control file

先在备库查看一下控制文件名称，等会创建完后直接覆盖过去：

主库创建standby controlfile：

SQL> alter database create standbycontrolfile as '/u01/control01.ctl';

Database altered.

copy到备库的目录，在覆盖原来的控制文件：

–先关闭备库：

SQL> shutdown immediate

–copy并覆盖：

mv control01.ctlcontrol01.ctl.bak

scp 192.168.1.20:/u01/control01.ctl 192.168.1.30:/u01/app/oracle/oradata/dave/

mv control02.ctlcontrol02.ctl.bak

cp control01.ctl/u01/app/oracle/fast_recovery_area/dave/control02.ctl

2.在正常拉起备库

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database recover managedstandby database disconnect from session;

####处理ORA-16789:问题##########

broker ORA-16789: standby redo logs not configured

解决方法：在主库添加standby logfile

alter database add standby logfile ('/u01/oradata/primary/standby01.log') size 50m;

alter database add standby logfile ('/u01/oradata/primary/standby02.log') size 50m;

alter database add standby logfile ('/u01/oradata/primary/standby03.log') size 50m;

alter database add standby logfile ('/u01/oradata/primary/standby04.log') size 50m;

###failover会出现两种情况：##########

第一种：（有可能将原来宕机的主库reinstated为备库）

Error: ORA-16661: the standby database needs to be reinstated

Reinstate the database using the DGMGRL REINSTATE DATABASE command

步骤：

Restart the database to the mounted state

Connect to the new primary database

Use DGMGRL to reinstate the database

DGMGRL> REINSTATE DATABASE db_unique_name;

如果以上步骤失败，则需要重新搭建dg。

第二种：

ORA-16795: the broker detects that database re-creation is required

没有办法只有重建dg。

##### 主库被破坏#######

快速把备库切换

alter system set log_archive_dest_state_2=defer;

alter database activate standby database;

alter database open;