Oracle 11G dataguard单机部署（Windows--＞Linux）

Floating warm sun

已于 2023-03-15 13:43:18 修改

阅读量2.6k

点赞数 1

文章标签：数据库 dba oracle

于 2022-04-02 17:31:17 首次发布

本文链接：https://blog.csdn.net/loveLAxin/article/details/123924974

版权

dataguard部署环境如下：

主库	备库
IP： 192.168.88.222	IP：192.168.88.223
OS：Windows	OS：Linux
instance_name：windb	instance_name：windg
data_file_directory：D:\ORADATA\WINDB	data_file_directory：/oradatadg/windg

1. 主端设置force logging

select name,log_mode,force_logging from gv$database;

返回NO表示未开启，需要执行以下命令开启：
alter database force logging;

2. 主端要在归档模式，不是则开启归档

shutdown immediate
startup mount

将数据库更改为归档模式：
alter database archivelog;

再次查看是否开启成功：
archive log list;

3. 主端配置参数

DG设置：

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.88.222)(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.88.223)(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 v$archived_log where RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;

备：

select max(sequence#),thread# from v$archived_log where  applied='YES' and RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$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#;

至此，Windows–＞Linux dataguard搭建完成。