DATAGUARD搭建步骤及遇到的问题

【【Data Guard for Oracle 11.2.3】】*****************************************************************************************************************************
【生产环境部署Phisycal Standby】
前提：
*安装操作系统并优化
*配置存储磁盘或RAID并划分LAN
*划分网络并配置双网卡绑定



一、部署Oracle
主库服务器部署Oracle软件，监听，Oracle数据库
备库服务器部署Oracle软件，监听


二、配置参数文件
配置Primary参数
alter system set db_unique_name='meitao_1' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(meitao_1,meitao_2)' scope=spfile;
alter system set log_archive_dest_2='SERVICE=meitao_2 LGWR SYNC AFFIRM REOPEN=1800 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=meitao_2' scope=both
alter system set log_archive_dest_state_2='defer' scope=spfile;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
alter system set fal_client='meitao_1' scope=spfile;
alter system set fal_server='meitao_2' scope=spfile;
alter system set standby_file_management='AUTO' scope=spfile;


shutdown immediate
startup mount
alter database archivelog;
alter database force logging;
alter database open;




添加Standby Redolog
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/app/oracle/oradata/meitao/redo11a.log','/u01/app/oracle/oradata/meitao/redo11b.log') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/u01/app/oracle/oradata/meitao/redo12a.log','/u01/app/oracle/oradata/meitao/redo12b.log') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/u01/app/oracle/oradata/meitao/redo13a.log','/u01/app/oracle/oradata/meitao/redo13b.log') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/u01/app/oracle/oradata/meitao/redo14a.log','/u01/app/oracle/oradata/meitao/redo14b.log') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 ('/u01/app/oracle/oradata/meitao/redo15a.log','/u01/app/oracle/oradata/meitao/redo15b.log') SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 ('/u01/app/oracle/oradata/meitao/redo16a.log','/u01/app/oracle/oradata/meitao/redo16b.log') SIZE 500M;




配置Standby参数
#vi init.ora
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/meitao/control01.ctl','/u01/app/oracle/fast_recovery_area/meitao/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='meitao'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='meitao_2'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='meitao_2'
*.fal_server='meitao_1'
*.log_archive_config='DG_CONFIG=(meitao_1,meitao_2)'
*.log_archive_dest_2='SERVICE=meitao_1 LGWR SYNC AFFIRM reopen=15 net_timeout=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=meitao_1'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1600126976
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/dsdfdb','/u01/app/oracle/oradata/dsdfdb'
*.log_file_name_convert='/u01/app/oracle/oradata/dsdfdb','/u01/app/oracle/oradata/dsdfdb'


--中续处理
mkdir -p /u01/app/oracle/oradata/meitao
mkdir -p /u01/app/oracle/fast_recovery_area/meitao
orapwd file=orapwmeitao password=m123456 entries=2
sqlplus / as sysdba
startup nomount pfile='/home/oracle/init.ora';
create spfile from pfile='/home/oracle/init.ora';
shutdown immediate
startup nomount






二、配置Oracle网络
==LISTENER==
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = MEITAO)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = MEITAO)
    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
    )
  )


ADR_BASE_LISTENER = /u01/app/oracle




==TNSNAMES==
names.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


MEITAO_1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = meitao_1)
    )
  )


MEITAO_2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = meitao_2)
      (UR=A)
    )
  )






三、连接目标库和辅助库，利用活动数据库复制搭建Standby
rman target sys/m123456@meitao_1 auxiliary sys/m123456@meitao_2
run
{
duplicate target database 
for standby
from active database
nofilenamecheck;
}
注释：如果在RMAN恢复时不指定 nofilenamecheck 参数，则在数据文件相同文件名恢复时会出现RMAN-05501错误，当主库，备库的数据库文件目录是一样的时候，必须使用 nofilenamecheck参数告诉rman主库和被创建的备份库拥有一样的文件目录和文件名。




启动恢复
主库
alter system set log_archive_dest_state_2=enable scope=both;
备库
alter database recover managed standby database disconnect from session;


取消备库的自动恢复
alter database recover managed standby database cancel;
启动到只读状态
alter database open read only;
在“READ ONLY”状态下进一步启动备库的恢复
alter database recover managed standby database using current logfile disconnect;






四、保护模式修改
一般创建完备库，默认的保护模式是最大性能
SQL> select database_role,protection_mode,protection_level from v$database;


DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


切换到最高可用模式
SQL> alter database set standby database to maximize availability;


Database altered.


SQL> select database_role,protection_mode,protection_level from v$database;


DATABASE_ROLE PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY








#################################################################################
1.中间open时的问题：
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/dsdfdb/system01.dbf'


解决：
RMAN> recover database;   
Starting recover at 23-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
starting media recovery
unable to find archived log
archived log thread=1 sequence=0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/23/2017 01:53:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 1087899


查看alert日志
ORA-16016: archived log for thread 1 sequence# 23 unavailable
将主库的所有归档日志全部拷贝到备库相应目录
RMAN> catalog start with '/u01/app/oracle/arch';
RMAN> recover database;
Starting recover at 23-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
starting media recovery
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/arch/1_23_952625699.dbf
archived log file name=/u01/app/oracle/arch/1_23_952625699.dbf thread=1 sequence=23
unable to find archived log
archived log thread=1 sequence=24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/23/2017 01:56:04
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 24 and starting SCN of 1087965


SQL> alter database open read only;
Database altered.