ORACLE DG 搭建及日常维护手册

蓝皮书Comments are off for this post.

[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 | 文件系统 |

“`
echo $ORACLE_HOME
/data/app/oracle/product/11.2.0.4/dbhome_1
echo $ORACLE_HOME
/data/app/oracle/product/11.2.0.4/dbhome_1
telnet 10.0.6.6 1521
telnet 192.0.0.9 1521
dbca -silent -deletedatabase -sourcedb liupsdb -sysdbausername sys
“`

 

## 配置归档模式

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

### 配置 force logging 模式

“`shell
ALTER DATABASE FORCE LOGGING;
“`

### 配置归档模式

“`shell
shutdown immediate
startup mount
alter database archivelog;
alter database open;
“`

### 检查归档模式

“`shell
SQL> select LOG_MODE, FORCE_LOGGING from v$database;

LOG_MODE FOR
———— —
ARCHIVELOG YES

SQL>
“`

## 配置静态监听及tns

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

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

 

“`shell
新增
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)
)
)
“`

### 重启监听

“`shell
lsnrctl stop
lsnrctl start
“`

### 配置 tns

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

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

pri

“`shell
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

“`
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 = 10.0.0.47)(port = 1521))
(connect_data =
(server = dedicated)
(service_name = liupsdb)
)
)
“`

 

### 测试 tns

“`shell
[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)
“`

## 新增 redo log file 和 standby log file

主库上进行 redo log file 和 standby log file 操作

“`sql
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;

alter database add logfile group 1 (‘/data1/oradata/’) size 1073741824;
“`

## 主库修改数据库参数

“`sql
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;
“`

## 复制密码文件到备库

“`shell
cd $ORACLE_HOME/dbs
scp scp orapwliupsdb 10.0.6.6:/data/app/oracle/product/11.2.0.4/dbhome_1/
“`

## 备库启动数据库实例

备库创建审计日志目录

“`shell
mkdir -p /data/app/oracle/admin/liupsdb/adump
“`

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

“`shell
cd $ORACLE_HOME/dbs
touch initliupsdb.ora
echo “db_name=liupsdb”>initliupsdb.ora
“`

“`
sqlplus sys/hvcrda90n@liupsdb as sysdba
sqlplus sys/hvcrda90n@liupsdbdg as sysdba
“`

“`
mkdir -p /data/app/oracle/oradata/liupsdbDG/datafile/
mkdir -p /data/app/oracle/oradata/liupsdb/
mkdir -p /data1/app/oracle/oradata/liupsdb/datafile/
mkdir -p /data1/oradata/liupsdbDG/datafile/
mkdir -p /data/app/oracle
mkdir -p /data/app/oracle/admin/liupsdb/adump
mkdir -p /data/app/oracle/oradata/liupsdb/
mkdir -p /data1/oradata
mkdir -p /data/app/oracle/oradata/liupsdb
mkdir -p /data/app/oracle/oradata/liupsdb
mkdir -p /data/app/oracle
mkdir -p /data1/arch
mkdir -p /data/app/oracle/oradata/liupsdb
“`

## rman duplicate 搭建DG从库

rman 登录数据库

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

“`
rman
“`

cat r.sh

“`shell

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，内容如下

“`shell
#!/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
show parameter service_name
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、启动监听

“`shell
lsnrctl start
“`

### 2、查看监听状态

“`shell
lsnrctl status
“`

### 3、关闭监听

“`shell
lsnrctl stop
“`

### 4、启动数据库

“`sql
sqlplus / as sysdba
SQL> startup
“`

### 5、关闭数据库

“`
sqlplus / as sysdba
SQL> shutdown immediate
“`

### 6、启动数据同步进程

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

d