Oracle -＞ DG主备配置

欧皇小德子

已于 2022-02-16 16:36:59 修改

阅读量4.6k

点赞数 2

分类专栏： Oracle 文章标签： oracle 数据库服务器

于 2021-12-03 16:09:47 首次发布

本文链接：https://blog.csdn.net/rod0320/article/details/121631609

版权

Oracle 专栏收录该内容

3 篇文章 1 订阅

订阅专栏

文章目录

1. 介绍主库是归档模式
2.安装前准备工作
3. 主库配置
4. 从库配置
5. DG操作
6. 完成
7. 测试
8. 设置开机启动

1. 介绍主库是归档模式

Oracle中有2种日志，一种称为 Redo Log（重做日志），另一种叫做Archive Log（归档日志）

Redo Log（重做日志）
- oracle对数据库的记录,当数据库有增删改,新建表索引等变化时,会对记录成日志写入到一个文件中。
- 理论上来说,就是按顺序记录了这些SQL语句,只要我们重新按顺序执行一遍这些记录的语句就可以还原数据库。
Archive Log（归档日志）
- Redo Log（重做日志）毕竟是写入到一个文件中的,这个文件可以指定容量大小,当这个文件超过预定的值,就会重新开始写入,就会发生覆盖。
- 所以有了归档日志,oracle开启了归档模式后,一个重做日志文件写满后,会将重做日志备份到归档日志,这样就可以有办法执行恢复了(也只是用来恢复,正常用不着)。

DG实现原理就是从主库获取数据到从库，在主库发生异常的时候，从库接管主库，完成身份的变化。

dg (主)负责传输主数据库 redo data(重做日志) 到从数据库
从数据库通过应用接收到的 redo data (重做日志) 保持与主数据库的事务一致。
需要保证主从库一致，需要传输archive log和redo log到从库，如果不是归档模式无法保证主从库的数据一致(没有归档,覆盖日志会导致数据缺失)。

2.安装前准备工作

准备两台已安装oracle的服务器一台

主机名:oracleone(主)     库名:oracl 	实例名SID:orcl
主机名:oracletwo(备) 	无库+无实例+有监听程序

修改主备库主机名

vi /etc/hostname

[root@oracleone ~]# cat /etc/hostname
oracleone
[root@oracletwo ~]# cat /etc/hostname
oracletwo

分别设置IP映射关系

vi /etc/hosts

[root@oracleone ~]#  cat /etc/hosts
127.0.0.1 oracleone  localhost 
::1 	  oracleone  localhost
8.8.8.5 oracleone
8.8.8.6 oracletwo

[root@oracletwo ~]# cat /etc/hosts
127.0.0.1  oracletwo localhost
::1        oracletwo localhost
8.8.8.5 oracleone
8.8.8.6 oracletwo

init 6#重启主备库

备库删除实例(备库不需要实例)

su - oracle
dbshut#停掉oracle
lsnrctl stop
查看备库实例
find $ORACLE_BASE/ -name $ORACLE_SID

[oracle@oracletwo ~]$ find $ORACLE_BASE/ -name $ORACLE_SID
/home/oracle/diag/rdbms/orcl
/home/oracle/diag/rdbms/orcltwo/orcl
/home/oracle/cfgtoollogs/dbca/orcl
/home/oracle/admin/orcl
/home/oracle/fast_recovery_area/orcl
/home/oracle/oradata/orcl

删除
dbca -silent -deleteDatabase -sourcedb orcl

3. 主库配置

主机设置归档模式

su - oracle
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  822579200 bytes
Fixed Size		    2257720 bytes
Variable Size		  281021640 bytes
Database Buffers	  532676608 bytes
Redo Buffers		    6623232 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

判断DG是否已经安装

SQL> select name, log_mode from v$database;
SQL> select * from v$option where parameter = 'Oracle Data Guard';

SQL> select name, log_mode from v$database;
NAME	  LOG_MODE
--------- ------------
ORCLONE   ARCHIVELOG

SQL> select * from v$option where parameter = 'Oracle Data Guard';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Oracle Data Guard
TRUE

为true就是正常

强制记录日志：

SQL> alter database force logging;

检查状态(yes为强制)：

SQL> select name,force_logging from v$database;

查询redo log files文件大小（默认50M,3个）

select group#,bytes/1024/1024 as M from v$log;

SQL> select group#,bytes/1024/1024 as M from v$log;

    GROUP#	    M
---------- ----------
	 1	   50
	 2	   50
	 3	   50

创建4个standby logfile:

su - root
mkdir /home/oracle/oradata/orcl
chmod -R 777 /home/oracle/oradata/orcl
su - oracle
sqlplus / as sysdba
SQL> alter database add standby logfile group 11 '/home/oracle/oradata/orcl/standby11.log' size 50M;
SQL> alter database add standby logfile group 12 '/home/oracle/oradata/orcl/standby12.log' size 50M;
SQL> alter database add standby logfile group 13 '/home/oracle/oradata/orcl/standby13.log' size 50M;
SQL> alter database add standby logfile group 14 '/home/oracle/oradata/orcl/standby14.log' size 50M;

密码文件

一般数据库默认就有密码文件，存放在$ORACLE_HOME/dbs/orapw+SID
如果没有:
- orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle;

在这里插入图片描述

密码文件远程传输到从库(保证主从密码文件一致)

su - oracle
cd $ORACLE_HOME/dbs
手敲(orapw+sid)
- 必须用oracle用户不能用root,不能用连接工具复制粘贴
- scp orapworcl oracl@oracletwo:$ORACLE_HOME/dbs/orapworcl

[oracle@oracleone dbs]$ scp orapworcl oracl@oracletwo:/home/oracle/11.2.0/dbs/orapworcl
oracl@oracletwo's password: 
orapworcl   100% 1536   815.2KB/s   00:00

配置监听

vim $ORACLE_HOME/network/admin/listener.ora

# 新加
SID_LIST_LISTENER=
  (SID_LIST =
   (SID_DESC =
       (GLOBAL_DBNAME = orcl)
       (ORACLE_HOME = /home/oracle/11.2.0)
       (SID_NAME = orcl)
   )
  )
# 旧
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracleone)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle

vim $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleone)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
# 新加(ORCLTWO:自定义名称)
ORCLTWO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletwo)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

把监听文件复制到备库–建议自己手敲

cd $ORACLE_HOME/network/admin/
scp tnsnames.ora oracle@oracletwo:$ORACLE_HOME/network/admin/
scp listener.ora oracle@oracletwo:$ORACLE_HOME/network/admin/

测试监听

lsnrctl stop
lsnrctl start
lsnrctl status
tnsping orcl#通过
tnsping orcltwo#未通过(备库还未配置)

开放端口,关闭防火墙

su - root
开放1521端口
firewall-cmd --add-port=1521/tcp --permanent
firewall-cmd --reload
systemctl stop firewalld

参数设置

su - oracle
sqlplus / as sysdba
SQL> create pfile from spfile;
- $ORACLE_HOME/dbs/init+sid.ora #文件位置

在这里插入图片描述

SQL>exit
cd $ORACLE_HOME/dbs

[oracle@oracleone dbs]$ ls
init.ora  initorclone.ora   orapworclone  spfileorclone.ora

手敲(init+sid)
vim initorcl.ora
添加部分配置 initorcl.ora

orcl.__db_cache_size=419430400
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/home/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=452984832
orcl.__sga_target=654311424
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/oradata/orcl/control01.ctl','/home/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'#注意主备库要一样
*.db_unique_name='orclone'#注意主备库要不一样
*.db_recovery_file_dest='/home/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orclone'#注意
*.fal_server='orcltwo'#注意
*.log_archive_config='dg_config=(orclone,orcltwo)'#注意
*.log_archive_dest_1='LOCATION=/home/oracle/oradata/orcl/archive valid_for=(all_logfiles,all_roles) db_unique_name=orclone'#注意
*.log_archive_dest_2='SERVICE=orcltwo lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orcltwo'#注意
*.log_archive_format='orcl_%t_%s_%r.dbf'#注意
*.memory_target=1098907648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'#注意
*.standby_file_management='AUTO'#注意
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_state_1=enable#注意
*.log_archive_dest_state_2=enable#注意

sqlplus / as sysdba
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> exit

远程发给从库

cd $ORACLE_HOME/dbs
手敲(init+sid)
scp initorcl.ora oracle@oracletwo:$ORACLE_HOME/dbs/initorcl.ora

4. 从库配置

开放端口,关闭防火墙

su - root
开放1521端口
firewall-cmd --add-port=1521/tcp --permanent
firewall-cmd --reload
systemctl stop firewalld
chmod -R 777 $ORACLE_HOME/network/admin

配置监听

su - oracle
vim $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=
  (SID_LIST =
   (SID_DESC =
       (GLOBAL_DBNAME = orcl)
       (ORACLE_HOME = /home/oracle/11.2.0)
       (SID_NAME = orcl)
   )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracletwo)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /home/oracle

测试监听

lsnrctl stop
lsnrctl start
lsnrctl status
tnsping orcl#通过
tnsping orcltwo#通过

配置控制文件

su - root
chmod -R 777 $ORACLE_HOME/dbs
su - oracle
cd $ORACLE_HOME/dbs
手敲(init+sid)
vim initorcl.ora

从库 initorcl.ora

orcl.__db_cache_size=419430400
orcl.__java_pool_size=16777216
orcl.__large_pool_size=33554432
orcl.__oracle_base='/home/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=452984832
orcl.__sga_target=654311424
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=167772160
orcl.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/oradata/orcl/control01.ctl','/home/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'#注意主备一致
*.db_recovery_file_dest='/home/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='orcltwo'#注意主备不一致
*.diagnostic_dest='/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcltwo'#注意
*.fal_server='orclone'#注意
*.log_archive_config='dg_config=(orclone,orcltwo)'#注意
*.log_archive_dest_1='LOCATION=/home/oracle/oradata/orcl/archive valid_for=(all_logfiles,primary_role) db_unique_name=orcltwo'#注意
*.log_archive_dest_2='SERVICE=orclone lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=orclone'#注意
*.log_archive_format='orcl_%t_%s_%r.dbf'#注意
*.memory_target=1098907648
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_state_1=enable#注意
*.log_archive_dest_state_2=enable#注意

在备库创建文件目录

su - oracle
mkdir -p /home/oracle/admin/orcl/adump
mkdir -p /home/oracle/admin/orcl/dbdump
mkdir -p /home/oracle/admin/orcl/pfile
mkdir -p /home/oracle/oradata/orcl
mkdir -p /home/oracle/fast_recovery_area/orcl
mkdir -p /home/oracle/oradata/orcl/archive

更新备库控制文件

sqlplus / as sysdba
SQL> shutdown immediate;
SQL> create spfile from pfile;

主库备库初步均配置完成

5. DG操作

启动备库+监听(先启)

lsnrctl stop
lsnrctl start
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;#参数文件启动

启动主库+监听(后启)

lsnrctl stop
lsnrctl start
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;#正常启动

利用RMAN在备库上恢复主库：

cd /home/oracle/admin/orcl
保证密码文件是相同的(都是oracle用户的)
rman target sys/sys@orcl auxiliary sys/sys@orcltwo
RAMN> duplicate target database for standby from active database nofilenamecheck;
RMAN> exit
恢复主库完成

另一种恢复主库办法:
将主库oracle目录下的admin,cfgtollogs,diag,flash_recover_area,oradata
目录拷贝到备用库的相同路径，备库已经有的文件和文件夹直接覆盖。

在这里插入图片描述

在备用服务器上启动日志传送服务

sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;
SQL> recover managed standby database cancel;

重启备库,设置可读不可写

SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;

6. 完成

7. 测试

主库 - sqlplus sys/sys as sysdba#也可以继续使用Linux连接

创建用户test:

SQL > create user test identified by test;

赋予权限:

SQL > grant connect,resource,dba to test;

登陆test - sqlplus test/test#也可以继续使用Linux连接

SQL> create table TEST(ID int, NAME varchar(20));
SQL> insert into TEST values(1, '张三');
SQL> select * from TEST;

在这里插入图片描述

备库 - sqlplus test/test#也可以继续使用Linux连接

SQL> select * from TEST;

8. 设置开机启动

主库

su - root
vim /etc/init.d/oracle
添加如下脚本：


#!/bin/sh
#chkconfig: 2345 20 80   
#以下请根据安装oracle时的具体情况进行修改
ORA_HOME=/home/oracle/11.2.0
ORA_OWNER=oracle
LOGFILE=/var/log/oracle.log
echo "#################################" >> ${LOGFILE}
date +"### %T %a %D: Run Oracle" >> ${LOGFILE}
if [ ! -f ${ORA_HOME}/bin/dbstart ] || [ ! -f ${ORA_HOME}/bin/dbshut ]; then
    echo "Error: Missing the script file ${ORA_HOME}/bin/dbstart or ${ORA_HOME}/bin/dbshut!" >> ${LOGFILE}
    echo "#################################" >> ${LOGFILE}
    exit
fi
start(){
    echo "###开启 Database..."
    su - ${ORA_OWNER} -c "${ORA_HOME}/bin/dbstart ${ORA_HOME}"
    echo "###Done."
    echo "###Run database control..."
    su - ${ORA_OWNER} -c "${ORA_HOME}/bin/emctl start dbconsole"
    echo "###Done."
}
stop(){
    echo "###Stop database control..."
    su - ${ORA_OWNER} -c "${ORA_HOME}/bin/emctl stop dbconsole"
    echo "###Done."
    echo "###停止 Database..."
    su - ${ORA_OWNER} -c "${ORA_HOME}/bin/dbshut ${ORA_HOME}"
    echo "###Done."
}
case "$1" in
    'start')
        start
    ;;
    'stop')
        stop
    ;;
    'restart')
        stop
        start
    ;;
    *)
    echo "请使用参数stop/start/restart"
    ;;
esac
date +"### %T %a %D: Finished." >> ${LOGFILE}
echo "#################################" >> ${LOGFILE}
echo ""

chmod 777 /etc/init.d/oracle
chkconfig --add oracle
chkconfig oracle on

至此可使用如下命令对oracle的启动或关闭进行管理

service oracle stop #关闭
service oracle start #启动
service oracle restart #重启

备库

su - root
vim /etc/init.d/oracle
添加如下脚本：


#!/bin/bash
#chkconfig: 2345 98 01
#description: Oracle database dataguard server
#Starts the oracle database dataguard server
#
# processname: oracle
# Source function library.
. /etc/init.d/functions

ORACLE_SID=orcl; export ORACLE_SID
ORACLE_BASE=/home/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/11.2.0.4
export ORACLE_HOME
#NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
#ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
#export NLS_LANG ORA_NLS33
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME_LISTNER=$ORACLE_HOME
PATH=$HOME/bin:$ORACLE_HOME/bin:/opt/bin:/bin:/usr/bin:usr/ccs/bin:/usr/ucb:/etc:$PATH
export PATH
 
 
RETVAL=0
 
start() {
# Check if oracle is already running
if [ ! -f /var/lock/subsys/oracle ]; then
prog="listener"
echo -n $"Starting $prog: "
su - oracle -c "lsnrctl start" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Starting $prog: "
su - oracle -c "sqlplus /nolog" << EOF >>/var/log/oracle.log
connect / as sysdba
startup nomount;
alter database mount standby database;
alter database open read only;   
alter database recover managed standby database using current logfile disconnect from session;
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v\$database;
exit
EOF

RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && touch /var/lock/subsys/oracle || RETVAL=1
fi
return $RETVAL
}
 
stop() {
prog="listener"
echo -n $"Stopping $prog: "
su - oracle -c "lsnrctl stop" >> /var/log/oracle.log
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
prog="oracle dataguard"
echo -n $"Stopping $prog: "
su - oracle -c "sqlplus /nolog" << EOF >> /var/log/oracle.log
connect / as sysdba
recover managed standby database cancel;
shutdown immediate;
exit
EOF
RETVAL=$?
[ $RETVAL -eq 0 ] && success || failed
echo
[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/oracle
return $RETVAL
}

restart() {
stop
start
}
reload() {
restart
}
status_oracle() {
su - oracle -c "lsnrctl status"
su - oracle -c "sqlplus /nolog" << EOF
connect / as sysdba
select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v\$database;
select process,status from v\$managed_standby;
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME from v\$dataguard_stats where NAME like '%lag';
exit
EOF
}

case "$1" in
start)
start
;;
stop)
stop
;;
reload|restart)
restart
;;
status)
status_oracle
;;
*)
echo $"请使用参数stop/start/restart"
exit 1
;;
esac
exit $?
exit $RETVAL

chmod 777 /etc/init.d/oracle
chkconfig --add oracle
chkconfig oracle on

至此可使用如下命令对oracle的启动或关闭进行管理

service oracle stop #关闭
service oracle start #启动
service oracle restart #重启

告辞

欧皇小德子

关注

2
点赞
踩
29

收藏

觉得还不错? 一键收藏
0
评论
Oracle -＞ DG主备配置

文章目录介绍主库是归档模式安装前准备工作主库配置从库配置利用RMAN在备库上恢复主库：启动备库到nomount：介绍主库是归档模式Oracle中有2种日志，一种称为 Redo Log（重做日志），另一种叫做Archive Log（归档日志）Redo Log（重做日志）oracle对数据库的记录,当数据库有增删改,新建表索引等变化时,会对记录成日志写入到一个文件中。理论上来说,就是按顺序记录了这些SQL语句,只要我们重新按顺序执行一遍这些记录的语句就可以还原数据库。Archive
复制链接

扫一扫

专栏目录