[大数据]Oracle 11G dataguard单机部署（Windows--＞Linux）

dataguard部署环境如下：

1. 主端设置force logging
   select name,log_mode,force_logging from gv$database;
   显示NO未开启，需要开启：
   alter database force logging;
   显示yes开启成功

2. 主端要在归档模式，不是则开启归档
   shutdown immediate
   startup mount
   将数据库更改为归档模式：
   alter database archivelog;
   再次查看是否开启成功：
   archive log list;

3. 主端配置参数
   ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=‘DG_CONFIG=(WINDB,WINDG)’;

   主库归档目的地：
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=‘LOCATION=D:\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=WINDB’;

   传输到备端归档目的地：
   ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=‘SERVICE=WINDG LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=WINDG’;

   允许redo传输数据到目的地，默认enable：
   alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
   alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

   设置备库文件自动管理：
   ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

   设置FAL_SERVER为备库名：（主备库切换的时候会用到这个参数）
   ALTER SYSTEM SET FAL_SERVER=‘WINDG’;

   以下参数修改需要重启数据库，如果主库不能重启备库必须设置：
   alter system set DB_FILE_NAME_CONVERT = ‘D:\ORADATA\WINDB’,’/oradatadg/windg/’ scope=spfile;
   alter system set LOG_FILE_NAME_CONVERT = ‘D:\ORADATA\WINDB’,’/oradatadg/windg/’ scope=spfile;
   注意：对方路径在前，自己路径在后。
   DB_FILE_NAME_CONVERT参数的作用是转换主库和备库的数据文件路径。
   LOG_FILE_NAME_CONVERT参数的作用是转换主库和备库的redo日志文件的路径。

4. 主端创建standby redolog
   对于11g此步骤可以在主端备份数据库之前创建standby redolog，在备端执行alter database mount后会在备端自动创建standby redolog。
   查看当前redo日志组：
   select GROUP#,MEMBER from v$logfile;

   创建standby redolog：
   standby日志的大小至少必须与redo日志一样大，standby日志的数量至少比当前redo日志多一个日志组。
   alter database add standby logfile thread 1 group 4 (‘D:\ORADATA\WINDB\standby_redo04.log’) size 200M;
   alter database add standby logfile thread 1 group 5 (‘D:\ORADATA\WINDB\standby_redo05.log’) size 200M;
   alter database add standby logfile thread 1 group 6 (‘D:\ORADATA\WINDB\standby_redo06.log’) size 200M;
   alter database add standby logfile thread 1 group 7 (‘D:\ORADATA\WINDB\standby_redo07.log’) size 200M;

5. 主端配置listener.ora和tnsnames.ora文件：
   D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
   SID_LIST_LISTENER =
   (SID_LIST =
   (SID_DESC =
   (GLOBAL_DBNAME = orcl)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home)
   (SID_NAME = orcl)
   )
   )

   LISTENER =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.128)(PORT = 1521))
   )

   D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
   WINDG =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 备库的ip )(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = orcl) (UR=A)
   )
   )

   (UR=A)作用当备端数据库nomount,mount或者restricted时，动态监听显示状态为BLOCKED时，主端可通过配置UR=A进行连接。

6. 备端配置listener.ora和tnsnames.ora文件：
   vim $ORACLE_HOME/network/admin/listener.ora
   SID_LIST_LISTENER =
   (SID_LIST =
   (SID_DESC =
   (GLOBAL_DBNAME = windg)
   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home)
   (SID_NAME = windg)
   )
   )

   LISTENER =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.130)(PORT = 1521))
   )

   vim $ORACLE_HOME/network/admin/tnsnames.ora
   WINDB =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 主库ip )(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = windb)
   )
   )

   主备端配置完监听需重启：
   lsnrctl stop
   lsnrctl start
   主备端测试监听配置：
   tnsping WINDB
   tnsping WINDG

7. 主端生成pfile
   create pfile=‘D:\backup\windb.ora’ from spfile;

8. 备份数据库
   run {
   allocate channel c1 type disk;
   allocate channel c2 type disk;
   allocate channel c3 type disk;
   backup incremental level 0 format ‘D:\backup\windb_full_%U’ database;
   backup format ‘D:\backup\windb_full_stanctf_%U’ current controlfile for standby;
   release channel c1;
   release channel c2;
   release channel c3;
   }

9. 把主端的参数文件、密码文件和备份文件传输到备库

10. 备库修改参数文件
    *.audit_file_dest=’/u01/app/oracle/admin/windg/adump’
    *.control_files=’/oradatadg/windg/control01.ctl’,’/oradatadg/windg/control02.ctl’
    *.db_file_name_convert=‘D:\ORADATA\WINDB’,’/oradatadg/windg/’
    *.log_file_name_convert=‘D:\ORADATA\WINDB’,’/oradatadg/windg/’
    *.diagnostic_dest=’/u01/app/oracle’
    *.fal_server=‘WINDB’
    *.db_unique_name=‘WINDG’
    *.log_archive_dest_1=‘LOCATION=/arch/windg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=WINDG’
    *.log_archive_dest_2=‘SERVICE=WINDB LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=WINDB’

11. 备端创建所需路径
    mkdir -p /u01/app/oracle/admin/windg/adump
    mkdir -p /arch/windg
    mkdir -p /oradatadg/windg

12. 备端恢复数据库
    生成spfile，再启动到nomount：
    create spfile from pfile=’/tmp/dgbak/windb.ora’;
    startup nomount;

    测试主备间的连通性：
    主端测试：
    connect sys/admin@windg AS SYSDBA
    备端测试：
    connect sys/admin@windb AS SYSDBA

    恢复控制文件：
    restore standby controlfile from ‘/tmp/dgbak/WINDB_FULL_STANCTF_05VRRQB3_1_1’;

    恢复数据文件：
    alter database mount;
    catalog start with ‘/tmp/dgbak/’;
    run {
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    allocate channel d3 type disk;
    restore database;
    recover database;
    release channel d1;
    release channel d2;
    release channel d3;
    }

13. 备端同步主库的归档日志
    alter database recover managed standby database using current logfile disconnect from session;
    SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS FROM V$MANAGED_STANDBY;

    关闭介质恢复：
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    只读模式打开数据库：
    ALTER DATABASE OPEN READ ONLY;

14. 查询同步
    主：
    select max(sequence#),thread# from vKaTeX parse error: Expected 'EOF', got '#' at position 36: …ESETLOGS_CHANGE#? = (SELECT RESE…DATABASE_INCARNATION WHERE STATUS = ‘CURRENT’) GROUP BY THREAD#;

    备：
    select max(sequence#),thread# from vKaTeX parse error: Expected 'EOF', got '#' at position 55: …ESETLOGS_CHANGE#? = (SELECT RESE…DATABASE_INCARNATION WHERE STATUS = ‘CURRENT’) GROUP BY THREAD#;

    查看同步是否存在错误：
    select error from v$archive_dest where target=‘STANDBY’;

    查看备端归档是否成功应用：
    SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;