1. 介绍主库是归档模式
Oracle中有2种日志,一种称为 Redo Log(重做日志),另一种叫做Archive Log(归档日志)
-
Redo Log(重做日志)
- oracle对数据库的记录,当数据库有
增删改,新建表索引
等变化时,会对记录成日志写入到一个文件中。 - 理论上来说,就是按顺序记录了这些SQL语句,只要我们重新按顺序执行一遍这些记录的语句就可以还原数据库。
- oracle对数据库的记录,当数据库有
-
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
#停掉oraclelsnrctl 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
#重启
告辞