1 准备工作
|
Host IP
|
DB_NAME
|
DB_UNIQUE_NAME
|
Archive_Dest
|
|
|
主库
|
172.20.20.150
|
orcl
|
pdunq
|
/data/oracle/archivelog/
|
|
备库
|
172.20.20.151
|
orcl
|
pdunq_dg
|
/data/oracle/archivelog/
|
保护模式：默认最大性能模式(MAXIMUM PERFORMANCE)
在primary搭建数据库软件，建立lsnrctl监听，采用dbca搭建实例，在standby上搭建数据库软件，建立监听，但是不需要采用dbca建立实例。
2 数据库安装
主机名修改
#/etc/sysconfig/network
#/etc/hosts
解决系统依赖关系
yum -y install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc* ksh glibc-common glibc-devel gcc gcc-c++ libaio-devel libaio libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh numactl-devel glibc-headers
创建用户
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
配置系统环境变量（source /home/oracle/.bash_profile)
#vi /home/oracle/.bash_profile
export ORACLE_BASE=/data/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
export ORACLE_SID=orcl
export ORACLE_OWNER=oracle
export ORACLE_TERM=vt100
export NLS_LANG=”AMERICAN_AMERICA.AL32UTF8″
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
PATH=$PATH:/usr/sbin; export PATH
PATH=$PATH:/usr/bin; export PATH
ORA_NLS33=$ORACLE_HOME/nls/admin/data
修改系统内核参数（sysctl -p）
#vi /etc/sysctl
kernel.sem = 100
fs.file-max =
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default =
net.core.wmem_default =
net.core.rmem_max =
net.core.wmem_max =
fs.aio-max-nr =
net.ipv4.tcp_fin_timeout = 10
kernel.threads-max =
vi /etc/security/limits.conf
oracle soft nproc
oracle hard nofile 65536
* soft memlock
* hard memlock
* soft nofile
* hard nofile
两种安装方式
-新建dbca文件静默安装
-图形界面xmanager远程
yum groupinstall “X Window System” “Desktop” -y
DISPLAY=10.255.1.6:0.0; export DISPLAY
./data/soft/database/runInstaller
下一步提示安装
数据库建库（dbca）—–>备库不需要
建立监听（netca）
具体参考链接 http://www.jb51.net/article/53769.htm
————————————————————
3 dataguard配置
主库打开Forced Logging 模式
强制归档 alter database force logging;
添加standby文件
alter database add standby logfile group 4 (‘/home/oradata/powerdes/redo_dg_021.log’) size 20M;
alter database add standby logfile group 5 (‘/home/oradata/powerdes/redo_dg_022.log’) size 20M;
alter database add standby logfile group 6 (‘/home/oradata/powerdes/redo_dg_023.log’) size 20M;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
select * from v$logfile order by 1;
准备参数文件，生成pfile
SQl>create pfile from spfile;
File created.
SQL>shutdown immediate
修改pfile,先备份文件
cp /data/oracle/product/11.2/db_1/dbs/initorcl.ora /data/oracle/product/11.2/db_1/dbs/initorcl.orabak
—>主要修改如下红色地方
orcl.__db_cache_size=
orcl.__java_pool_size=
orcl.__large_pool_size=
orcl.__oracle_base=’/data/oracle’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=
orcl.__sga_target=
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=
orcl.__streams_pool_size=0
*.audit_file_dest=’/data/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/data/oracle/oradata/orcl/control01.ctl’,’/data/oracle/flash_recovery_area/orcl/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’/data/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=
*.db_unique_name=’pdunq’
*.diagnostic_dest=’/data/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.FAL_CLIENT=’pdunq’
*.FAL_SERVER=’pdunq_dg’
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(pdunq_dg,pdunq)’
*.LOG_ARCHIVE_DEST_1=’LOCATION=/data/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdunq’
*.LOG_ARCHIVE_DEST_2=’service=pdunq_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.memory_max_target=
*.memory_target=
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1′
生成spfile，配置生效
create spfile from pfile;
启动
SQL>startup
修改监听文件
vi /data/oracle/product/11.2/db_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = KM1818-databasebak)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/data/oracle/product/11.2/db_1)
(SID_NAME = orcl)
)
)
修改tns配置文件
vi /data/oracle/product/11.2/db_1/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KM1818-databasebak)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
pdunq
=
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(
HOST = 172.20.20.150
)(PORT = 1521))
HOST = 172.20.20.150
)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
pdunq_dg
=
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(
HOST = 172.20.20.151
)(PORT = 1521))
HOST = 172.20.20.151
)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
监听服务重启
lsnrctl stop/start/status
primary上配置最大可用模式：
SQL>startup
SQL>alter database set standby database to maximize availability;
创建主库的密码文件，没有则建立。可以OS验证的方式登录
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5
备份数据库
backup database plus archivelog;
backup current controlfile for standby;
exit;
备份结束后会在闪回区产生备份文件（
/data/oracle/flash_recovery_area/ORCL）
/data/oracle/flash_recovery_area/ORCL）
主库上建立备库控制文件（控制文件通常需要有多份，手工将文件复制几份）
$ sqlplus ‘/as sysdba’
SQL> alter database create standby controlfile as ‘/data/oracle/stdby_control01.ctl’;
cp sdtby_control01.ctl stdby_control02.ctl
拷贝文件到备机上（包括密码文件；控制文件；监听文件；rman备份文件）
scp -r ./* 172.20.20.151:/data/oracle/flash_recovery_area/
scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/dbs/
scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/network/admin/
scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/dbs/orapworcl
—————————————————————————————
备机上操作
listener.ora tnsnames.ora(配置如下)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(
HOST = i-rzc0r96p
)(PORT = 1521))
HOST = i-rzc0r96p
)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/data/oracle/product/11.2/db_1)
(SID_NAME = orcl)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = i-rzc0r96p)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
pdunq =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.20.150)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
pdunq_dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.20.151)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
修改参数文件
orcl.__db_cache_size=
orcl.__java_pool_size=
orcl.__large_pool_size=
orcl.__oracle_base=’/data/oracle’#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=
orcl.__sga_target=
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=
orcl.__streams_pool_size=0
*.audit_file_dest=’/data/oracle/admin/orcl/adump’
*.audit_trail=
‘db’ （如果备库将来要打开成只读模式，需要确认audit_trail参数不是含db，应该设成os或none）
‘db’ （如果备库将来要打开成只读模式，需要确认audit_trail参数不是含db，应该设成os或none）
*.compatible=’11.2.0.0.0′
*.control_files=’/data/oracle/stdby_control01.ctl’,’/data/oracle/stdby_control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’/data/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=
*.DB_UNIQUE_NAME=’pdunq_dg’
*.diagnostic_dest=’/data/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.FAL_CLIENT=’pdunq’
*.FAL_SERVER=’pdunq_dg’
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(pdunq_dg,pdunq)’
*.log_archive_dest_1=’location=/data/oracle/archivelog’
*.LOG_ARCHIVE_DEST_2=’service=pdunq_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.memory_max_target=
*.memory_target=
*.open_cursors=300
*.processes=3000
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1′
监听重启，查看状态
重启备库生效
create spfile from pfile;
启动物理备库
SQL> startup nomount
SQL> alter database mount standby database;
备库做rman恢复
$ rman target / (要求主备库rman备份文件的存放路径和文件名一致)
RMAN> restore database;
RMAN> restore archivelog all;
介质恢复后，rman 自动将standby 数据库打开到mount 状态。
启动redo 应用
SQL> alter database recover managed standby database disconnect from session;
————————————————————————————–
查看同步情况
首先连接到primary 数据库
SQL> show parameter instance_name;
NAME TYPE VALUE
———————————— ———– ——————————
instance_name string jssweb
SQL> alter system switch logfile;
系统已更改。
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
22
连接到standby 数据库
SQL> show parameter instance_name;
NAME TYPE VALUE
———————————— ———– ——————————
instance_name string jsspdg
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
22
常用维护SQL命令
启动Data Guard 后，查看同步情况：：
SQL>
select error from v$archive_dest;
select error from v$archive_dest;
用SQL 查看了一下同步正常：
SQL>
select sequence#,applied from v$archived_log;
select sequence#,applied from v$archived_log;
查看备库接收、应用redo数据的过程
select message from v$dataguard_status；
主库归档：
SQL>ALTER SYSTEM SWITCH LOGFILE; –对单实例数据库或RAC中的当前实例执行日志切换
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT; –对数据库中的所有实例执行日志切换
V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息
SELECT* FROM V$STANDBY_LOG;
备库启动归档日志应用
alter database recover managed standby database disconnect from session;
备库停止归档日志应用
alter database recover managed standby database cancel;
查看备库是否和主库同步，查询V$archive_dest_status视图
select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;
监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少
select* from v$archive_gap;
查看当前主机的运行状态
select switchover_status,database_role,protection_mode from v$database
备库端查看RFS(RemoteFile Service)接收日志情况和MRP应用日志同步主数据库的情况(Physical Standby Database Only) 记录当前备库的一些进程情况和进程ID
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/228667.html原文链接：https://javaforall.net
