oracle dataguard 11g centos6.7 搭建手稿

参考：Oracle-DataGurd-DG环境搭建中文图文手册Db-11g-10g-dba

oracle11g-dataguard-完全手册

oracle cluster 安装


unzip *。。1of2   *。。2of2
rm -rf ./*.zip
yum install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc- gcc-c++ libaio-devel libaio libgcc 


libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh ksh


vim /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
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 = 1048586


vim /etc/security/limits.conf
oracle           soft    nproc   2047
oracle           hard    nproc   16384
oracle           soft    nofile  1024
oracle           hard    nofile  65536
oracle           hard    stack   10240


cat /proc/slabinfo | grep kio


账户配置
groupadd -g 700 oinstall
groupadd -g 701 dba
useradd -g oinstall -G dba -u 700 oracle
echo 123456 | passwd --stdin oracle
id oracle
配置目录
mkdir  -p  /u01/oracle            # oracle根目录，-p 表示递归建立目录
mkdir -p  /u01/oradata                 # oracle数据文件存放目录
chown -R oracle:oinstall  /u01         
chmod -R 775  /u01








域名配置
vim /etc/hosts

192.168.1.30 prod.localdomain   prod

192.168.1.40standby.localdomain   standby

hostname prod.localdomain


vi /etc/pam.d/login    添加如下内容：
session   required     pam_limits.so
xhost +
access control disabled,clients can connect from any host     //出现以上文字表示x windows导出成功。


su - oracle
vim /home/oracle/.bash_profile//注意，bash文件下结尾不能有/，否则报（not start）错误
export ORACLE_BASE=/u01
export ORACLE_HOME=/u01/oracle/db_1
export ORACLE_SID=prod
export PATH=$ORACLE_HOME/bin:$PATH:$PATH/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/jlib:$ORACLE_HOME/network/lib
export LANG=en_US.gbk
export NLS_LANG=american_america.ZHS16GBK
export EDITOR=vi
source .bash_profile
mkdir /u01/logss
cd /oracle/database/
ls
xhost +      //root账户调用过以后，oracle账户才能调用
./runInstaller 


去掉I wish to receive security updates via My Oracle Surrport选项，点击Next。


选择Install database software only，点击Next。


Single instance database installation，点击Next。


English、Simplified Chinese、Traditional Chinese，加入到右边的Selected Languages中，然后点击Next。


按照实际情况，选择企业版本，


oracle的软件路径和基本路径


选择安装日志目录,


选择dba组


可以忽略，因为centos版本较高，所以11g check的时候不识别高版本lib包


finish


需要等待中，大概20分钟左右


点击OK，继续


然后点击close，结束


rm -rf /database


umount /mnt       //断开光盘  anyway，还是这么大，14.1G


--------------------关机，复制虚拟机，打开后重新设置网卡、主机名


开机，root下
/u01/logss/orainstRoot.sh
/u01/oracle/db_1/root.sh


xhost +
su - oracle
xhost +
netca  //监控本机
dbca
next、nest


General Purpus           //定制版Custom Database


输入前面确定的数据库名SID框，在.bash_profile中配置的


勾掉configure  ，next


Use the same ....  密码： 123456          //此密码在连接scott时使用


Use Database    //此前Use Common Location for All Database FilesDatabase File Location  此前创建/oracle/oracledata


勾掉   //此前额外勾选 Enable Archiving


勾选 sample


next，，character sets  勾选 choose from the list


next    //此前勾选Generate Database Creation Scripts，默认为 /oracle/app/oracle/admin/powerdes/scripts，


一路ok


完成安装，在点击exit前，点击password menagment，勾掉scott/bi/sh/oe/hr， ok  //解锁几个用户






测试部分
su - oracle
sqlplus "/ as sysdba"
create table z_test(id number,name varchar(20));
insert into z_test select 1,'a' from dual;
select * from z_test;
commit;
exit




------------------------------------------------------------------------设置部分
su - oracle //prod上，主库机上
sqlplus / as sysdba
select * from v$option where parameter = 'Oracle Data Guard';//guard功能是否可装
select log_mode,force_logging from v$database;       //查看log模式
shutdown immediate
startup mount
alter database archivelog;        //归档模式
alter database force logging;     //强制日志
select log_mode,force_logging from v$database;
archive log list    //日志模式目录等信息
!      //登陆oracle
mkdir /u01/oradata/prod/archivelog
exit   //退出登录
alter system set log_archive_dest_1='location=/u01/oradata/prod/archivelog'  //改变日志目录
archive log list  
alter database open;    //从mount状态打开
////////试验中提前设置备用日志
sql> alter database add standby logfile group  11 '/u01/oradata/standby/ar/prod11.log' size 50M;
sql> alter database add standby logfile group  12 '/u01/oradata/prod/prod12.log' size 50M;
sql> alter database add standby logfile group  13 '/u01/oradata/prod/prod13.log' size 50M;
sql> alter database add standby logfile group  14 '/u01/oradata/prod14.log' size 50M;


alter system set log_archive_dest_1='LOCATION=/u01/oradata/prod/archivelog valid_for=(all_logfiles,primary_role) db_unique_name=prod' scope=spfile;        


  //改变归档目录
alter system set log_archive_dest_2='SERVICE=standby lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=standby';      //重做日志到备库
alter system set FAL_SERVER='standby' //备库正好反过来
alter system set db_unique_name='prod'scope=spfile;
alter system set log_archive_config='dg_config=(prod,standby)';           //限制日志传输范围
alter system set db_file_name_convert='/u01/oradata/standby','/u01/oradata/prod' scope=spfile;
alter system set log_file_name_convert='/u01/oradata/standby','/u01/oradata/prod' scope=spfile;
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;


create pfile from spfile; //创建pfile，initprod.ora为pfile
alter database create standby controlfile as '/u01/oracle/standby.ctl';    //创建备库控制文件
scp orapwprod initprod.ora   standby:/u01/oracle/db_1/dbs      //密码、pfile到相同目录
scp /u01/oracle/standby.ctl  standby:/u01/oracle


vim .bash_profile //standby上，修改oracle_sid=standby
mv orapwprod orapwstandby       //standby上
mv initprod.ora initstandby.ora
vim initstandby.ora           //1,$s/\/prod\//\/standby\//g替换目录 并 修改或添加如下
*.log_archive_dest_1='location=/u01/oradata/standby/archive1og valid_for=(all_logfiles,primary_role) db_unique_name=standby'
*.log_archive_dest_2='SERVICE=prod lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=prod'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.db_unique_name='standby'
*.fal_server='prod' //Fetch Archived Log解决日志未能及时归档时定义获取服务器
*.db_file_name_convert='/u01/oradata/prod','/u01/oradata/standby'
*.log_file_name_convert='/u01/oradata/prod','/u01/oradata/standby'
*.log_archive_config='dg_config=(prod,standby)'
 
mkdir -p /u01/admin/standby/adump
mkdir -p /u01/admin/standby/bdump
mkdir -p /u01/admin/standby/cdump
mkdir -p /u01/admin/standby/udump
mkdir -p /u01/admin/standby/dpdump
mkdir -p /u01/oradata/standby/archivelog
netmgr //监控本机standby
netca //监听本地网络服务，prod
tnsping prod //tns的ping 主库机
env | grep ORA //查看oracle状态
sqlplus / as sysdba
create spfile from pfile='?/dbs/initstandby.ora'; //从ora文件导入系统设置
startup nomount //不挂载开启


netca //prod上，监听备库standby机
tnsping standby
sqlplus / as sysdba
alter system set standby_file_management=auto;
alter system set db_file_name_convert='/u01/oracle/standby','/u01/oracle/prod' scope=spfile;
alter system set log_file_name_convert='/u01/oracle/standby','/u01/oracle/prod' scope=spfile;
mkdir /u01/backupset
rman target / //登录到rman，备份数据库
run
{
allocate channel d1 type disk format '/u01/backupset/%U';
backup database;
release channel d1;
}
scp -r /u01/backupset 192.168.75.62:/u01 //复制到standby
rman target / auxiliary sys/123456@standby //123456为前边设置的密码
run //在prod上运行，完成standby数据库的恢复
{
allocate auxiliary channel d1 type disk;
duplicate target database for standby nofilenamecheck;
release channel d1;
}


select status from v$instance; //standby上
alter database add standby logfile('/u01/oradata/standby/standby01.log') size 50M;//实验中，提前创建，
alter database add standby logfile('/u01/oradata/standby/standby02.log') size 50M;
alter database add standby logfile('/u01/oradata/standby/standby03.log') size 50M;
alter database add standby logfile('/u01/oradata/standby/standby04.log') size 50M;


alter system switch logfile; //prod上


ls -l /u01/oradata/standby/archivelog //standby上
sqlplus / as sysdba
alter database recover managed standby database using current logfile disconnect from session;   //备库执行日志应用同步语句，开始运行物理DG
select sequence#, applied from v$archived_log where applied='YES' order by sequence#;   //yes 说明日志同步了


conn scott/tiger //prod上,初始值为tiger,重置密码为123456
create table t(i int);
insert into t values(1);
commit;
conn / as sysdba
alter system switch logfile;                     //改变日志传输通道1/2/3


alter database recover managed standby database cancel;//standby上，取消日志同步，
alter database open;
conn scott/123456
select * from t; //经过改变日志通道，取消日志同步后，数据写入数据库,看到刚才在主库上插入的表格及内容，则表示成功


conn / as sysdba //prod和standby上
select open_mode from v$database; //查看数据库状态


alter database recover managed standby database using current logfile disconnect from session;    //standby上
select open_mode from v$database;


注意:在测试数据同步的时候请不要用sys 用户，不然可能同步不了，这里测试都是用scott用户


重启dataguard顺序，先关闭主库：shutdown immediate  在关闭备库：取消日志同步，shutdown immediate；
  先开启备库：lsnrctl start; sql>startup  再开启主库：lsnrctl start; sql>startup


------------------------------------------------------------------结束
listener手动则添加
vim /u01/oracle/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
     (SID_LIST =
  (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /opt/oracle/11.2)
      (SID_NAME = orcl)
  )
     )
LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = primaryDB)(PORT = 1521))
     )
   )
vim tnsnames.ora
PROD =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.215.101)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = orcl)
     )
   )
 
 STANDBY =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.215.102)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = orcldg)
     )
   )


复制此两文件到standby， 修改listener.ora,,,,tnsnames.ora不需要修改




物理standby database的日常维护 (2016-01-25 18:45:36)
1.停止Standby
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;
 
2.切换到只读模式
-----由shutdown模式切换到只读模式-------
startup nomount;
alter database mount standby database;
alter database open read only;
-----由应用日志模式切换到只读模式-------
alter database recover managed standby database cancel; -- 取消日志应用
alter database open read only;
 
3.切换回管理恢复模式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session; -- 启动日志应用
alter database recover managed standby database using current logfile disconnect from session;
 
4.主库和备库之间角色切换
4.1 主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
 
4.2 从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup
alter system switch logfile;
 
5.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;
 
6.更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
 
7.取消自动恢复模式
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;
  


alter profile default limit password_life_time unlimited;




pdf上的bash_profile
export ORACLE_BASE=/u01
export ORACLE_SID=prod
export ORACLE_HOME=/u01/oracle/db_1
export ORACLE_TERM=xterm
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib