Oracle11g dataguard安装配置
图文版请下载附件：oracle10g迁移到11g并配置dataguard
| 角色 | Ip | Sid | Service_name |
| primary | 172.18.83.251 | nis | nis |
| standby | 172.18.83.252 | nis | nisdg |
创建用户和组
groupadd oinstall #创建用户组oinstall
groupadd dba #创建用户组dba
useradd -g oinstall -g dba -m oracle #创建用户oracle，并加入oinstall和dba组
passwd oracle #设置用户oracle的登录密码，根据提示输入两次密码
创建oracle安装目录
mkdir -p /oracle/oraInventory #oracle数据库配置目录
设置目录所有者
chown -R oracle:oinstall /oracle #设置目录所有者为oinstall用户组的oracle用户
chown -R oracle:oinstall /oracle/oraInventory
修改系统标识
vim /etc/redhat-release #编辑
把
CentOS Linux release 7.0.1406 (Core)
修改为
#CentOS Linux release 7.0.1406 (Core)
Red Hat Enterprise Linux 6
修改系统内核
vim /etc/sysctl.conf #编辑，在最后添加以下代码
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.file-max = 6815744 #设置最大打开文件数
fs.aio-max-nr = 1048576
kernel.shmall = 2097152 #共享内存的总量，8G内存设置：2097152*4k/1024/1024
kernel.shmmax = 2147483648 #最大共享内存的段大小
kernel.shmmni = 4096 #整个系统共享内存端的最大数
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500 #可使用的IPv4端口范围
net.core.rmem_default = 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.file-max = 6815744
fs.aio-max-nr = 1048576
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576
设置oracle用户限制，提高软件运行性能
vim /etc/security/limits.conf #在末尾添加以下代码
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
配置oracle用户环境变量
vim /home/oracle/.bash_profile #在最后添加以下代码
export ORACLE_BASE=/oracle #oracle数据库安装目录
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 #oracle数据库路径
export ORACLE_SID=nis #oracle启动数据库实例名
export ORACLE_UNQNAME=$ORACLE_SID
export PATH=$ORACLE_HOME/bin:/user/sbin:$PATH #添加系统环境变量
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH #添加系统环境变量
source /home/oracle/.bash_profile #使设置立刻生效
安装oracle数据库需要的软件包
yum install -y gcc* gcc-* gcc-c++-* glibc-devel-* glibc-headers-* compat-libstdc* libstdc* elfutils-libelf-devel* libaio-devel* sysstat* unixODBC-* pdksh-* libXp.i686 libXp-devel.i686 libXt.i686 libXt-devel.i686 libXtst.i686 libXtst-devel.i686 make.x86_64 gcc.x86_64 libaio.x86_64 glibc-devel.i686 libgcc.i686 glibc-devel.x86_64 compat-libstdc++-33 glibc* gcc* make* compat-db* libstdc* libXp* libXtst* compat-libstdc++*
--centos6.5需要安装
yum install binutils compat-libstdc++-33 compat-libstdc++-33.i686 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc.i686 glibc-common glibc-devel glibc-devel.i686 glibc-headers ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel make sysstat unixODBC unixODBC-devel -y
重启下系统 以oracle的身份登录系统
解压oracle下载的两个文件
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip
cd database
为防止乱码，运行
export LANG=en_US
运行oracle安装程序
./runInstaller
xhost +
access control disabled, clients can connect from any host
[root@localhost ~]# su - oracle
Last login: Wed Mar 29 10:15:50 CST 2017 on pts/1
[oracle@localhost ~]$ bash
[oracle@localhost ~]$ export DISPLAY=display_xwindow_ip:0.0
[oracle@localhost ~]$ csh
[oracle@localhost ~]$ setenv DISPLAY display_xwindow_ip:0.0
安装的时候直接用oracle用户登录,不要su – oracle
报错：Exception String: Error in invoking target 'install' of makefile '/data/product/11.2.0/db_1/ctx/lib/ins_ctx.mk'. See '/data/oraInventory/logs/installActions2017-04-13_01-08-11PM.log' for details.
yum -y install glibc-static.x86_64
yum -y install glibc-static.i686
修改/data/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk，将
ctxhx: $(CTXHXOBJ)
$(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK)
修改为：
ctxhx: $(CTXHXOBJ)
-static $(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK) /usr/lib64/stdc.a
点击Retry继续安装。接着又提示”Error in invoking target 'agent nmhs' of makefile '/data/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk.' ，解决方法：在makefile中添加链接libnnz11库的参数
修改/data/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk，将
$(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL) -lnnz11
点击Retry继续安装。
安装成功！
使用root用户执行以下命令后再点击ok
/oracle/oraInvertory/orainstRoot.sh
/oracle/product/11.2.0/dbhome_1/root.sh
更改字符集：
SQL> shutdown immediate
SQL> startup
SQL> alter session set sql_trace=true;
SQL> alter system enable restricted session;
SQL> show parameter job_queue_processes;
SQL> alter system set job_queue_processes=0;
SQL> alter system set aq_tm_processes=0;
SQL> alter database open;
SQL> alter database character set INTERNAL_USE AL32UTF8;
SQL> update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';
维护完以后需要
SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;
shutdown immediate;
startup;
/*第1步：创建临时表空间 */
create temporary tablespace iyunv_temp
tempfile '/data/oradata/nis/iyunv_temp/iyunv_temp.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local;
/*第2步：创建数据表空间 */
create tablespace b2b2table
logging
datafile '/data/oradata/nis//b2b/b2b2table.dbf'
size 50m
autoextend on
next 50m maxsize unlimited
extent management local
segment space management auto;
删除表空间
drop tablespace b2b2table;
drop tablespace b2b2table including contents and datafiles;
/*第3步：创建用户并指定表空间 */
create user iyunv identified by iyunv
default tablespace b2b2table
temporary tablespace iyunv_temp;
用户已存在：
alter user iyunv temporary tablespace iyunv_temp;
alter user iyunv default tablespace b2b2table;
/*第4步：给用户授予权限 */
grant connect,resource,dba to iyunv;
创建expdp
create directory dump_dir as '/data/temp/';
grant read,write on directory dump_dir to iyunv;
导出数据
expdp iyunv/iyunv@nis schemas=iyunv dumpfile=iyunv20170330.dmp directory=dump_dir;
数据导入
impdp iyunv/iyunv@nis directory=dump_dir dumpfile=iyunv20170330.dmp schemas=iyunv;
主备机都更改一下db_files
Shutdown immediate;
startup nomount;
alter system set db_files=500 scope=spfile;
shutdown immediate;
startup
主库：
配置为归档模式
startup mount
alter database archivelog;
查看是否配置成功
archive log list;
配置强制记录日志
alter database force logging;
检查状态(YES为强制)：
sql>select name,force_logging from v$database;
创建standby log files(备用日志文件）
alter database add standby logfile group 11 '/data/dg/standby11.log' size 50M;
alter database add standby logfile group 12 '/data/dg/standby12.log' size 50M;
alter database add standby logfile group 13 '/data/dg/standby13.log' size 50M;
alter database add standby logfile group 14 '/data/dg/standby14.log' size 50M;
show parameter REMOTE_LOGIN_PASSWORDFILE
设置db_unique_name:
show parameter db_unique_name;
alter system set db_unique_name=nisdg scope=spfile;
配置闪回区
查看是否启用，默认是不开启的
sql>select flashback_on from v$database;
开启：
sql>alter database flashback on;
alter system set db_recovery_file_dest='/data/fast_recovery_area';
更改大小：
sql>alter system set db_recovery_file_dest_size=100G;
配置监听
[oracle@dbzhu admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = nis)
(ORACLE_HOME = /data/product/11.2.0/db_1)
(SID_NAME = nis)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data
配置tnsnames
主
[oracle@dbzhu admin]$ cat listener.ora
# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = nis)
(ORACLE_HOME = /data/product/11.2.0/db_1)
(SID_NAME = nis)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data
[oracle@dbzhu admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nis)
)
)
NISDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbbak)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nisdg)
)
)
备机
[root@dbbak admin]# cat listener.ora
# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = nisdg)
(ORACLE_HOME = /data/product/11.2.0/db_1)
(SID_NAME = nis)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbbak)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /data
[root@dbbak admin]# cat tnsnames.ora
# tnsnames.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
NIS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nis)
)
)
NISDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbbak)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nisdg)
)
)
测试连通性
先关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
[oracle@dbzhu ~]$ tnsping nis
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-APR-2017 14:21:48
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/data/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nis)))
OK (0 msec)
[oracle@dbzhu ~]$ tnsping nisdg
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-APR-2017 14:21:52
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/data/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbbak)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nisdg)))
OK (10 msec)
重做日志传输配置
alter system set log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,primary_role) db_unique_name=nis' scope=spfile;
配置重做日志到备份库：
alter system set log_archive_dest_2='SERVICE=nisdg lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=nisdg ' scope=spfile;
配置FAL_SERVER
alter system set FAL_SERVER='nisdg';
Data Guard 配置里的另外一个库的名字
alter system set log_archive_config = 'dg_config=(nis,nisdg)';
在主库创建控制文件并复制到备机
alter database create standby controlfile as '/data/oradata/nis/standby_control01.ctl';
分别复制到备机并重命名
/data/oradata/nis/
control01.ctl
/data/flash_recovery_area/nis
control02.ctl
把主机的密码控制文件传输到备机
[oracle@dbzhu dbs]$ pwd
/data/product/11.2.0/db_1/dbs
scp orapwnis oracle@172.18.83.238:/data/product/11.2.0/db_1/dbs/
手工修改pfile
create pfile='/tmp/2.txt' from spfile;
shutdown immediate
修改pfile文件的内容
[oracle@dbzhu flash_recovery_area]$ cat /tmp/2.txt
nis.__db_cache_size=654311424
nis.__java_pool_size=16777216
nis.__large_pool_size=16777216
nis.__oracle_base='/data'#ORACLE_BASE set from environment
nis.__pga_aggregate_target=637534208
nis.__sga_target=956301312
nis.__shared_io_pool_size=0
nis.__shared_pool_size=251658240
nis.__streams_pool_size=0
*.audit_file_dest='/data/admin/nis/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/nis/control01.ctl','/data/flash_recovery_area/nis/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='nis'
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/data'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nisXDB)'
*.fal_server='nisdg'
*.log_archive_config='dg_config=(nis,nisdg)'
*.log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,all_roles) db_unique_name=nis'
*.log_archive_dest_2='SERVICE=nisdg lgwr sync valid_for=(online_logfile, all_roles) db_unique_name=nisdg '
*.memory_target=1582301184
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='nis'
*.archive_lag_target=1800
*.fal_client='nis'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.standby_file_management='auto'
*.db_file_name_convert='/data/flash_recovery_area',' /data/flash_recovery_area'
*.log_file_name_convert='/data/flash_recovery_area','/data/flash_recovery_area'
利用pfile文件创建spfile文件
create spfile from pfile='/tmp/2.txt';
startup
在备机操作：
[oracle@dbbak data]$ cat /tmp/2.txt
nis.__db_cache_size=654311424
nis.__java_pool_size=16777216
nis.__large_pool_size=16777216
nis.__oracle_base='/data'#ORACLE_BASE set from environment
nis.__pga_aggregate_target=637534208
nis.__sga_target=956301312
nis.__shared_io_pool_size=0
nis.__shared_pool_size=251658240
nis.__streams_pool_size=0
*.audit_file_dest='/data/admin/nis/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/nis/control01.ctl','/data/flash_recovery_area/nis/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='nis'
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.diagnostic_dest='/data'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nisXDB)'
*.fal_server='nis'
*.log_archive_config='dg_config=(nis,nisdg)'
*.log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,all_roles) db_unique_name=nisdg'
*.log_archive_dest_2='SERVICE=nis lgwr sync valid_for=(online_logfile,all_roles db_unique_name=nis '
*.memory_target=1582301184
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='nisdg'
*.archive_lag_target=1800
*.fal_client='nisdg'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.standby_file_management='auto'
*.db_file_name_convert='/data/flash_recovery_area',' /data/flash_recovery_area'
*.log_file_name_convert='/data/flash_recovery_area','/data/flash_recovery_area'
利用pfile文件创建spfile文件
shutdown immediate
create spfile from pfile='/tmp/2.txt';
startup
启动备库到standby管理模式
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
dataguard启动关闭顺序
<1> 监听 先启从库再起主库 #lsnrctl start
<2> 启动
先启从库：
sql>startup nomount
sql>alter database mount standby database;
sql>alter database recover managed standby database using current logfile disconnect from session;
再启主库
sql>startup
<3> 关闭：和开启正好相反
先关主库：
sql>shutdown immediate
再关从库：
sql>alter database recover managed standby database cancel;
sql>shutdown immediate;
删除某用户下所有表的数据
Select 'Truncate Table USER.' || Table_Name || ''
From Dba_Tables
Where Owner = 'IYUNV';
导入数据
impdp iyunv/iyunv@nis directory=dump_dir dumpfile=iyunv20170330.dmp schemas=iyunv table_exists_action=replace;
主库rman备份
rman target /
shutdown immediate
startup mount
run
{
allocate channel c1 device type disk format '/data/dg/datafile/full_%u_%s_%p';
backup database plus archivelog;
}
run
{
allocate channel c1 device type disk format '/data/dg/archive/control%u_%s_%p';
backup current controlfile for standby;
}
备份数据的时候如果出现归档日志被删除的情况可以先检查归档日志
change archivelog all crosscheck;
在备库恢复数据
恢复控制文件
shutdown immediate
startup nomount
restore controlfile from '/data/dg/archive/control1js1e03a_51_1';
备份启动到mount状态下，恢复数据文件
alter database mount;
run{
restore database;
recover database;
};
或者这样备份：
备份数据库(备份文件在闪恢复区)
[oracle@dbzhu dbs]$ rman target /
RMAN> backup database plus archivelog;
RMAN> backup current controlfile for standby;
恢复数据库：
恢复前查询一下db_files
show parameter db_files;
修改db_files
alter system set db_files=1000 scope=spfile;
Shutdown immediate
Startup
恢复是主机是startup状态，备机是startup nomount状态。
[oracle@dbbak ~]$ rman target sys/Founder123@nis auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
错误解决：
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oradata/phoneclient/system01.dbf'
解决方法：
SQL> RECOVER DATAFILE '/oracle/app/oradata/phoneclient/system01.dbf '
Media recovery complete.
SQL> recover tablespace system;
Media recovery complete.
SQL> RECOVER DATABASE;
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
问题：
ORACLE Instance nis - Archival Error
ORA-16014: log 12 sequence# 31 not archived, no available destinations
ORA-00312: online log 12 thread 1: '/data/dg/standby12.log'
Errors in file /data/diag/rdbms/nisdg/nis/trace/nis_arc1_100783.trc:
ORA-16014: log 12 sequence# 31 not archived, no available destinations
ORA-00312: online log 12 thread 1: '/data/dg/standby12.log'
Fri Apr 14 19:13:42 2017
Archiver process freed from errors. No longer stopped
解决方法
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=nisdg';
----生产库配置
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,primary_role) db_unique_name=nisdg;
----我的配置
更改序列的起始值。
现在的值13985285更改成13985371（13985371-13985285）
Alter Sequence HITDETAIL_SEQ Increment By 86;
Select HITDETAIL_SEQ.nextval FROM dual;
Alter Sequence HITDETAIL_SEQ Increment By 1;
