dataguard_action.txt

文章中详细记录了dataguard搭建的全过程。

一、数据库主、备机dataguard配置
环境描述
主机：ip为192.168.1.10
备机：ip为192.168.1.11

0、主机数据库备份

1、备机安装数据库(实例名与主机相同)

2、备机关闭服务，修改备机注册表中HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1中ORA_ORCL_AUTOSTART=FALSE

3、主备机防火墙穿透设置
a、在注册表中HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1设置USE_SHARED_SOCKET = TRUE
b、防火墙中开放1521端口

4、主机oracle\product\10.2.0\oradata\orcl创建目录archive和目录standbyarchive

5、主机数据库强制日志和主机数据库的init文件
sqlplusw /nolog
SQL>conn / as sysdba;
SQL>alter database force logging;
SQL>create pfile from spfile;
SQL>shutdown immediate;

6、主机数据库修改为归档模式
SQL>startup mount
SQL>alter database archivelog;
SQL>archive log list;

7、为主数据库添加"备用联机日志文件"
SQL>alter database add standby logfile group 4 ('D:\oracle\product\10.2.0\oradata\orcl\std_redo04a.log','D:\oracle\product\10.2.0\oradata\orcl\std_redo04b.log') size 50m, group 5 ('D:\oracle\product\10.2.0\oradata\orcl\std_redo05a.log','D:\oracle\product\10.2.0\oradata\orcl\std_redo05b.log') size 50m, group 6 ('D:\oracle\product\10.2.0\oradata\orcl\std_redo06a.log','D:\oracle\product\10.2.0\oradata\orcl\std_redo06b.log') size 50m, group 7 ('D:\oracle\product\10.2.0\oradata\orcl\std_redo07a.log','D:\oracle\product\10.2.0\oradata\orcl\std_redo08b.log') size 50m;
SQL>shutdown immediate;

8、修改主机数据库的oracle\product\10.2.0\db_1\database\initOrcl.ora文件
修改initorcl.ora文件：
*.db_unique_name='dbprimary'
*.log_archive_config='DG_CONFIG=(dbprimary,dbstandby)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\orcl\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbprimary'
*.log_archive_dest_2='service=dbstandby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=dbstandby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.fal_server='dbstandby'
*.fal_client='dbprimary'
*.standby_archive_dest='D:\oracle\product\10.2.0\oradata\orcl\standbyarchive'
*.standby_file_management='auto'

9、导入主机数据库修改后的初始参数
SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\initorcl.ora';
SQL>create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initorcl.ora';
SQL>shutdown immediate;

10、主机复制D:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA到D:\oracle\product\10.2.0\db_1\dbs\

11、在主库创建备库的控制文件
SQL>startup mount
SQL>alter database create standby controlfile as 'd:\tools\CONTROLSTANDBY.CTL';
SQL>shutdown immediate;

12、备机复制主机以下数据
a、oracle\product\10.2.0\oradata\orcl下[数据文件、重做日志、控制文件、CONTROLSTANDBY.CTL]
b、oracle\product\10.2.0\db_1\database下[密码文件、初始参数文件]
c、oracle\product\10.2.0\admin\orcl下文件
d、如果引用flash_recovery_area还需同步oracle\product\10.2.0\flash_recovery_area\ORCL下文件)

13、修改从库的init文件
修改initorcl.ora文件：
*.control_files='D:\oracle\product\10.2.0\oradata\orcl\CONTROLSTANDBY.CTL'
*.db_unique_name='dbstandby'
*.log_archive_config='DG_CONFIG=(dbprimary,dbstandby)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\orcl\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbstandby'
*.log_archive_dest_2='service=dbprimary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=dbprimary'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.fal_server='dbstandby'
*.fal_client='dbprimary'
*.standby_archive_dest='D:\oracle\product\10.2.0\oradata\orcl\standbyarchive'
*.standby_file_management='auto'

14、启动备机服务，导入备机数据库修改后的初始参数
SQL>shutdown immediate;
SQL>startup pfile='D:\oracle\product\10.2.0\db_1\database\initorcl.ora';
SQL>create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\initorcl.ora';
SQL>shutdown immediate;

15、备机复制D:\oracle\product\10.2.0\db_1\database\SPFILEORCL.ORA到D:\oracle\product\10.2.0\db_1\dbs\

16、关闭主备机服务

17、修改主机oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
  )

18、修改备机oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
  )

19、修改主备机oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,添加：
dbprimary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

dbstandby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

20、启动从库到恢复模式
SQL>conn / as sysdba;
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;

21、主库测试
......
SQL>alter system switch logfile;

22、从库切换查看
SQL>alter database recover managed standby database cancel;
SQL>alter database open read only;
SQL>...

23、恢复备库到恢复模式
SQL>alter database recover managed standby database disconnect from session;

24、备机启动组添加数据库初始化批处理

25、主备机添加定时请贵档日志的任务

二、备机数据库切换为主库
    归档是否都拷贝到本地，和日志传输的方式，决定FAILOVER过程是否会丢失数据。
1)登陆STANDBY数据库,检查是否存在GAP
sqlplus "/ as sysdba"
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; 
检查是否存在GAP，如果存在拷贝相应的归档到STANDBY数据库，并注册，再次执行上面的查询，直到上面的查询不返回记录。

2)在PRIMARY数据库和其他的数据库中检查是否存在本地没有的归档文件，拷贝并注册这些文件：
SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG; 
　　THREAD# LAST 
　　---------- ---------- 
　　1 228
把所有的归档都拷贝到STANDBY站点，使用ALTER DATABASE REGISTER PHYSICAL LOGFILE语句进行注册，然后停止应用归档：

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
Database altered.

3)下面将STANDBY数据库切换为PRIMARY数据库：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 
　　Database altered. 
SQL> SELECT DATABASE_ROLE FROM V$DATABASE; 
　　DATABASE_ROLE 
　　---------------- 
　　PHYSICAL STANDBY 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 
　　Database altered. 
SQL> ALTER DATABASE OPEN; 
　　Database altered.

4)检查数据库是否已经切换成功：
SQL> SELECT DATABASE_ROLE FROM V$DATABASE; 
　　DATABASE_ROLE 
　　---------------- 
　　PRIMARY 

至此，FAILOVER切换完成。

5)对新的PRIMARY数据库进行备份。