oracle dataguard 搭建手册

oracle dataguard 搭建手册


一、准备工作
1、安装数据库软件：
主库和备库安装数据库软件：
步骤7：选择数据库的恢复区：勾选指定快速恢复选项和启用归档。


2、参数开启归档功能
sqlplus /nolog
conn /as sysdba


查看 快速回复区参数
show parameter db_recovery
设置 快速回复区参数
alter system set db_recovery_file_dest_size=4182M scope=spfile;
alter system set db_recovery_file_dest='D:\DATAREOV' scope=spfile;
检查日志模式
archive loglist;
开启归档模式
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
查看快速恢复区使用空间
select name,SPACE_LIMIT,SPACE_USED from v$recovery_file_dest;
修改归档日志路径，快速恢复区不要和归档处于同一目录下
alter system set log_archive_dest_1='location=d:\log\orcl';
--开启之后检查归档目录是否生成文件。




二、数据库配置




1、不同数据库名，不同SID。
主库：
IP:192.168.199.177
数据库名:orcl
数据库SID:orcl
DB_UNIQUE_NAME:orcl
数据库安装路径：D:\oracle\A
数据文件路径：D:\DATABASE
本地归档路径：D:\DATAREOV


listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = D:\oracle\A\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL)
    )
    
  )
tnsname.ora
ORCLBAK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLBAK)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


spfile.ora:
*.db_unique_name=orcl
*.log_archive_config='dg_config=(orcl,orclbak)' 
*.log_archive_dest_1='location=D:\DATAREOV\orcl valid_for=(all_logfiles,all_roles) db_unique_name=ORCL'
*.log_archive_dest_2='service=ORCLBAK async valid_for=(online_logfiles,primary_role) db_unique_name=ORCLBAK'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orclbak
*.fal_client=orcl
*.standby_file_management=auto


备库：
IP:192.168.199.179
数据库名:orclbak
数据库SID:orclbak
DB_UNIQUE_NAME:orclbak
数据库安装路径：D:\oracle
数据文件路径：D:\DATABASE
本地归档路径：D:\DATAREOV


listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLBAK)
      (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = ORCLBAK)
    )
    
  )
tnsname.ora
ORCLBAK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLBAK)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


spfile.ora:
*.db_unique_name=orclbak
*.log_archive_config='dg_config=(orcl,orclbak)' 
*.log_archive_dest_1='location=D:\DATAREOV\orclbak valid_for=(all_logfiles,all_roles) db_unique_name=ORCLBAK'
*.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=ORCL'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.fal_client=orclbak
*.standby_file_management=auto


2、同数据库名，同SID。
主库：
IP:192.168.199.177
数据库名:orcl
数据库SID:orcl
DB_UNIQUE_NAME:orcl
数据库安装路径：D:\oracle\A
数据文件路径：D:\DATABASE
本地归档路径：D:\DATAREOV


listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = D:\oracle\A\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL)
    )
    
  )


tnsname.ora
ORCLBAK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


spfile.ora:
*.db_unique_name=orcl
*.log_archive_config='dg_config=(orcl,orclbak)' 
*.log_archive_dest_1='location=D:\DATAREOV\orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl'
*.log_archive_dest_2='service=ORCLBAK async valid_for=(online_logfiles,primary_role) db_unique_name=orclbak'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orclbak
*.fal_client=orcl
*.standby_file_management=auto








备库：
IP:192.168.199.179
数据库名:orcl
数据库SID:orcl
DB_UNIQUE_NAME:orclbak
数据库安装路径：D:\oracle
数据文件路径：D:\DATABASE
本地归档路径：D:\DATAREOV


listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
    )
  )


tnsname.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.177)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )


ORCLBAK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.179)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )




spfile.ora:
*.db_unique_name=orclbak
*.log_archive_config='dg_config=(orcl,orclbak)' 
*.log_archive_dest_1='location=D:\log\orcl valid_for=(all_logfiles,all_roles) db_unique_name=orclbak'
*.log_archive_dest_2='service=ORCL async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.fal_server=orcl
*.fal_client=orclbak
*.standby_file_management=auto




二、主库处理：
1.调整数据库模式
--检查数据库模式
select force_logging from v$database;
--修改模式
alter database force logging; 


2.设置standby日志文件
--增加standby redo log
select group#,member from v$logfile;
--检查日志文件大小
select group#,bytes/(1024*1024) from v$log; 
--增加日志组
alter database add standby logfile group 4 'D:\DATABASE\ORCL\orclbak_redo04.log' size 50m;
alter database add standby logfile group 5 'D:\DATABASE\ORCL\orclbak_redo05.log' size 50m;
alter database add standby logfile group 6 'D:\DATABASE\ORCL\orclbak_redo06.log' size 50m;
alter database add standby logfile group 7 'D:\DATABASE\ORCL\orclbak_redo07.log' size 50m;
--检查创建结果
select group#,member from v$logfile where type='STANDBY';


3.修改主库pfile文件
--创建pfile文件
create pfile='d:\1.ora' from spfile;
--在1.ora中增加,并保存。按照配置选择spfile.ora参数添加




4.复制密码文件到备库相同路径并修改为备库文件名。
D:\oracle\A\product\11.2.0\dbhome_1\database\INITorcl.ORA


5.创建备库的控制文件，要求数据库是OPEN状态。
--检查控制文件
select * from v$controlfile;
--创建备库使用的控制文件，将CONTROL01.CTL复制CONTROL02.CTL和CONTROL03.CTL一起拷贝到备库去。
alter database create standby controlfile as  'd:\CONTROL01.CTL';


6.关闭数据库，拷贝文件到备库。
shutdown immdediate;




二、备库处理：
1.根据主库数据库摸板建立备库数据库。


2.拷贝主库*.dbf、redo*.log、std*.log和密码文件到备库相应文件位置，并覆盖原有文件（原有文件可做好备份）。


3.修改备库pfile文件
--创建pfile文件
create pfile='d:\1.ora' from spfile;
--在1.ora中增加,并保存。按照配置选择spfile.ora参数添加


4.启动备库
--启动到nomount状态
startup pfile='D:\1.ora' nomount;
--启动到mount状态
alter database mount orclbak database;
--开启备库监听
alter database recover managed standby database disconnect from session;






三、启动主备库
1.启动顺序：先启动备库，再启动主库。关闭顺序：先关主库，再关备库。
1.启动备库不可读，可同步。
--主库启动,可以更新spfile中的参数，之后默认使用新参数。
startup pfile='D:\1.ora';
--备库启动，此启动方式备库不能以只读方式访问。
startup pfile='D:\1.ora' nomount;
alter database mount standby database;
--备库检查RFS程序是否启动
select process,pid,status,client_process from v$managed_standby;
--备库启动REDO APPLY
alter database recover managed standby database disconnect from session;
--备库检查程序启动情况，PROCESS:MRP0,STATUS：WAIT_FOR_LOG,CLIENT_P:N/A。
select process,pid,status,client_process from v$managed_standby;


2.启动备库可读，可同步。
--主库启动,可以更新spfile中的参数，之后默认使用新参数。
startup pfile='D:\1.ora';
--备库启动，启动后以只读方式访问
startup pfile='D:\1.ora' nomount;
alter database mount standby database;
alter database open read only;
select process,pid,status,client_process from v$managed_standby;
alter database recover managed standby database disconnect from session;
select process,pid,status,client_process from v$managed_standby;
--检查数据库的状态,READ ONLY WITH APPLY 只读模式打开可以应用重做日志，同步会需要一定时间。只有处于READ ONLY WITH APPLY和MOUNTED状态才能同步。
select open_mode,database_role from v$database;




四、切换主备库
1、检查主库的切换状态。PRIMARY TO STANDBY没有用户连接可以切换，SESSIONS ACTIVE
select database_role,switchover_status from v$database;
2、在当前主库上初始化切换到备库，备库会自动接收转为主库
alter database commit to switchover to physical standby with session shutdown wait;
3、将切换后成为主库执行语句不执行外部操作。
shutdown immediate;
startup nomunt;
4、检查主库和备库的切换状态
select database_role,switchover_status from v$database;
5、在新的备库启动日志应用
alter database recover managed standby database disconnect from session;
6、检查程序启动情况，PROCESS:MRP0,STATUS：WAIT_FOR_LOG,CLIENT_P:N/A。
select process,pid,status,client_process from v$managed_standby;
7、检查备库的切换状态
select database_role,switchover_status from v$database;
8、切换备库到主库
alter database commit to switchover to primary with session shutdown wait;
9、打开主库
alter database open;
10、检查主库的切换状态
select database_role,switchover_status from v$database;
11、进行检测日志等是否切换成功。


五、相关命令
--查看pfile启动路径
show parameter spfile
show parameter pfile
--使用pfile文件更新spfile中的参数,请备份好原有pfile文件
create spfile from pfile='D:\1.ora';


--手动切换日志,触发提交日志到备库
alter system switch logfile;


--检查主备库查询当前日志序列号
select sequence# from v$log;
--查看已经归档日志情况
select name,sequence#,thread# from v$archived_log;
--检查表空间
select name from v$datafile;
--检查日志状态
archive log  list;
--检查主库备库切换状态
--主库：PRIMARY TO STANDBY
--备库：PHYSICAL STANDBY NOT ALLOWED
select database_role,switchover_status from v$database;
--检查数据库的状态,READ ONLY WITH APPLY 只读模式打开可以应用重做日志，同步会需要一定时间。只有处于READ ONLY WITH APPLY和MOUNTED状态才能同步。
select open_mode,database_role from v$database;
--启动备库应用日志传送模式
alter database recover managed standby database disconnect from session;
--启动备库应用日志传送模式，并使备库立即应用归档日志
alter database recover managed standby database using current logfile disconnect from session;
相关语句：
--取消备库监听
alter database recover managed standby database cancel;
--查看数据库保护模式
select protection_mode from v$database;
--更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;