ORACLE DG 搭建及日常维护手册

发表于 分类于 ，

[TOC]

ORACLE DG 搭建、切换及日常维护手册

ORACLE DG 数据库信息

数据库版本 11.2.0.4

从库 是 10.0.6.6 主库 是 192.0.0.9

角色 IP db_unique_name 存储
主库 192.0.0.9 liupsdb 文件系统
DG 10.0.6.6 liupsdbdg 文件系统

配置归档模式

主库进行如下操作进行 force logging和归档配置

配置 force logging 模式

1
ALTER DATABASE FORCE LOGGING;

配置归档模式

1
2
3
4
SQL>shutdown immediate 
SQL>startup mount
SQL>alter database archivelog; 
SQL>alter database open;

检查归档模式

1
2
3
SQL> select LOG_MODE, FORCE_LOGGING from v$database;

LOG_MODE FOR ------------ --- ARCHIVELOG YES

配置静态监听及tns

DG 上配置静态配置 /data/app/oracle/product/11.2.0.4/dbhome_1

1
vi /data/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

新增 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = liupsdb) (ORACLE_HOME = /data/app/oracle/product/11.2.0.4/dbhome_1) (SID_NAME = liupsdb) ) )

重启监听

1
2
lsnrctl stop 
lsnrctl start

配置 tns

主从都修改 tnsnames.ora，新增如下信息

1
vi /data/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

主库 新增

1
liupsdbdg = (description = (address = (protocol = tcp)(host = 10.0.6.6)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb) ) ) liupsdb = (description = (address = (protocol = tcp)(host = 192.0.0.9)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb) ) )

DG 新增

1
iupsdbdg = (description = (address = (protocol = tcp)(host = 10.0.6.6)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb) ) ) liupsdb = (description = (address = (protocol = tcp)(host = 10.0.0.47)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb) ) )

测试 tns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[oracle@ecsdbdg1 admin]$ tnsping liupsdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2019 22:17:43

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files: /data/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (description = (address = (protocol = tcp)(host = 192.0.0.9)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb))) OK (20 msec) [oracle@ecsdbdg1 admin]$ tnsping liupsdbdg

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 04-MAR-2019 22:17:51

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files: /data/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (description = (address = (protocol = tcp)(host = 10.0.6.6)(port = 1521)) (connect_data = (server = dedicated) (service_name = liupsdb))) OK (0 msec)

新增 standby log file

主库上进行 standby log file 操作

1
2
3
4
alter database add standby logfile thread 1 group 21 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo01.log') size 1073741824; alter database add standby logfile thread 1 group 22 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo02.log') size 1073741824; 
alter database add standby logfile thread 1 group 23 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo03.log') size 1073741824; alter database add standby logfile thread 1 group 24 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo04.log') size 1073741824; 
alter database add standby logfile thread 1 group 25 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo05.log') size 1073741824; alter database add standby logfile thread 1 group 26 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo06.log') size 1073741824; 
alter database add standby logfile thread 1 group 27 ('/data1/oradata/liupsdbDG/onlinelog/sty_redo07.log') size 1073741824;

主库修改数据库参数

1
2
3
4
5
6
alter system set log_archive_config='dg_config=(liupsdb,liupsdbdg)'; 
alter system set log_archive_dest_1='location=/data1/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=liupsdbdg'; 
alter system set log_archive_dest_2='SERVICE=liupsdbdg lgwr async valid_for=(ONLINE_LOGFILES,primary_role) db_unique_name=liupsdb'; 
alter system set standby_file_management='AUTO'; 
alter system set fal_server=liupsdbdg; 
alter system set log_archive_dest_state_2=enable;

复制密码文件到备库

1
2
cd $ORACLE_HOME/dbs 
scp scp orapwliupsdb 10.0.6.6:/data/app/oracle/product/11.2.0.4/dbhome_1/

备库启动数据库实例

备库创建审计日志目录

1
mkdir -p /data/app/oracle/admin/liupsdb/adump

备库启动数据库实例到 nomount 状态

1
cd $ORACLE_HOME/dbs touch initliupsdb.ora echo "db_name=liupsdb">initliupsdb.ora 
1
sqlplus sys/hvcrda90n@liupsdb as sysdba sqlplus sys/hvcrda90n@liupsdbdg as sysdba

rman duplicate 搭建DG从库

rman 登录数据库

oracle 用户执行 rman 命令，然后使用如下脚本进行DG搭建。

cat r.sh

1
connect target sys/pass@liupsdb connect auxiliary sys/pass@liupsdbdg run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate auxiliary channel s1 type disk; allocate auxiliary channel s2 type disk; allocate auxiliary channel s3 type disk; allocate auxiliary channel s4 type disk; duplicate target database for standby from active database nofilenamecheck dorecover spfile parameter_value_convert 'liupsdb','liupsdb','liupsdbdg','liupsdb' set db_unique_name='liupsdb' set cluster_database='false' set fal_server='liupsdb' set remote_listener='' set local_listener='' set standby_file_management='AUTO' set db_create_file_dest='' set db_create_online_log_dest_1='' set log_archive_config='dg_config=(liupsdb,liupsdbdg)' set log_archive_dest_1='location=/data1/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=liupsdb' set log_archive_dest_2='SERVICE=liupsdb lgwr async VALID_FOr=(ONLINE_LOGFILES,primary_role) DB_UNIQUE_NAME=liupsdbdg' set log_archive_dest_state_2='enable' ; sql channel c1 "alter system archive log current"; sql channel s1 "alter database open"; sql channel s1 "alter database recover managed standby database using current logfile disconnect"; }

查看DG数据同步详情

查看脚本为 /home/oracle/dginfo.sh，内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#!/usr/bin/env sh
sqlplus / as sysdba <<EOF
set lines 123
set pages 200
col  CTIME format a20
col  NAME format a20
col  VALUE format a20
col  DATUM_TIME format a20
select open_mode, DATABASE_ROLE from v\$database;
SELECT  TO_NUMBER( SUBSTR ( (SUBSTR (VALUE, 5)), 0, 2) * 3600 + SUBSTR ( (SUBSTR (VALUE, 5)), 4, 2) * 60 + SUBSTR ( (SUBSTR (VALUE, 5)), 7, 2)) dgbehind, TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss'
) CTIME, NAME, VALUE,DATUM_TIME FROM V\$DATAGUARD_STATS WHERE NAME ='apply lag';
select process,block#,blocks ,status ,sequence# from v\$managed_standby;
exit
EOF

DG日常维护手册

以下所有操作如果没有特别说明使用 oracle 用户执行

1、启动监听

1
lsnrctl start

2、查看监听状态

1
lsnrctl status

3、关闭监听

1
lsnrctl stop

4、启动数据库

s
1
2
sqlplus / as sysdba 
startup

5、关闭数据库

1
2
sqlplus / as sysdba 
shutdown immediate

6、启动数据同步进程

```sql
sqlplus / as sysdba
alter database recover managed standby database using current logfile disconnect;