rm -rf ./*.zip
yum -y install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 gcc gcc-c++ glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libaio libaio.i686 libaio-devel libaio-devel.i686 ibXext ibXext.i686 libX11 libX11.i686 libxcb libxcb.i686 libXi libXi.i686 make sysstat vim /etc/sysctl.conf //追加 fs.aio-max-nr = 1048576 fs.file-max = 6815744 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 oracle soft stack 10240 cat /proc/slabinfo | grep kio 账户配置 i=701; for group in oinstall dba backupdba oper dgdba kmdba; do groupadd -g $i $group; i=`expr $i + 1` ; done useradd -g oinstall -G dba,oper,backupdba,dgdba,kmdba -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.20 prod.localdomain prod 192.168.1.30 standby.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 //出现以上文字表示导出成功。 su - oracle vim /home/oracle/.bash_profile //注意，bash文件下结尾不能有/，否则报（not start）错误 export TMP=/tmp export TMPDIR=$TMP export ORACLE_BASE=/u01 export ORACLE_HOME=/u01/oracle/db_1 export ORACLE_SID=prod export ORACLE_UNQNAME=prod export ORACLE_HOSTNAME=prod.localdomain 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 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，结束 root下执行 /u01/logss/orainstRoot.sh /u01/oracle/db_1/root.sh rm -rf /database umount /mnt //断开光盘 anyway，还是这么大，14.1G --------------------关机，复制虚拟机，打开后重新设置网卡、主机名 开机， xhost + su - oracle xhost + netca //监控本机 dbca 选择create database advanced mode general purpose 输入数据库名prod，SID名prod，在.bash_profile中配置的，勾选create as ，PDB名pdbprod next Use the same .... 密码： 123456 //此密码在连接sysbackup时使用 Use Common Location for All Database Files ，选择目录/u01/oracledata next, 在第三选项卡character sets 勾选 choose from the list next 一路ok 完成安装，在点击exit前，点击password menagment，勾掉sysbackup,sysdg,syskm， 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状态打开 alter database add standby logfile '/u01/oradata/prod/standby01.log' size 50M; //添加备用日志 alter database add standby logfile '/u01/oradata/prod/standby02.log' size 50M; alter database add standby logfile '/u01/oradata/prod/standby03.log' size 50M; alter database add standby logfile '/u01/oradata/prod/standby04.log' size 50M; alter system set log_archive_dest_1='LOCATION=/u01/oradata/prod/archivelog valid_for=(all_logfiles,all_roles) 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' //Fetch Archived Log传输日志的服务器 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; alter system set standby_file_management=auto; //备库日志处理管理方式 create pfile from spfile; //创建pfile，即：initprod.ora 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 su - oracle //standby上 vim .bash_profile //修改prod为standby mv orapwprod orapwstandby //目录/u01/oracle/db_1/dbs下 mv initprod.ora initstandby.ora vim initstandby.ora //1,$s/\/prod\//\/standby\//g替换目录/prod/的部分为/standby/ 并 修改或添加如下 *.log_archive_dest_1='location=/u01/oradata/standby/archive1og valid_for=(all_logfiles,all_roles) 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 //standby上 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 mkdir -p /u01/oradata/standby/pdbseed mkdir /u01/oradata/standby/pdbprod/ mkdir -p /u01/fast_recovery_area/ listener手动则添加 //prod上，监听本地文件 vim /u01/oracle/db_1/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = prod) (ORACLE_HOME = /u01/oracle/db_1) (SID_NAME = prod) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain)(PORT = 1521)) ) vim /u01/oracle/db_1/network/admin/tnsnames.ora //监听网络服务文件 STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE = DEDICATED) (SERVICE_NAME = standby) ) ) PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE = DEDICATED) (SERVICE_NAME = prod) ) ) PDBPROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE = DEDICATED) (SERVICE_NAME = pdbprod) )
)
PDBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVICE = DEDICATED) (SERVICE_NAME = pdbprod) )
)
LISTENER_PROD = (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain)(PORT = 1521)) //由于安装时依靠listener_prod名称，需要添加，备库上HOST = standby.localdomain
lsnrctl stop //监听关闭，用户oracle下命令
lsnrctl start //开启 scp listener.ora tnsnames.ora standby:/u01/oracle/db_1/network/admin/ vim /u01/oracle/db_1/network/admin/listener.ora //standby上，替换prod为standby lsnrctl stop //监听关闭 lsnrctl start //开启 tnsping prod //tns的ping 主库机 env | grep ORA //查看oracle状态 sqlplus / as sysdba create spfile from pfile='?/dbs/initstandby.ora'; //从ora文件导入系统设置 startup nomount //不挂载开启 alter user sysbackup identified by 123456 account unlock; //prod上，解锁重置过期密码 alter user sysdg identified by 123456 account unlock; scp orapwprod standby:/u01/oracle/db_1/dbs/orapwstandby //用户oracle下命令，重置覆盖备库的密码文件 tnsping standby //测试监听备库 rman //用户oracle下命令 connect target "sysbackup/123456@prod as sysbackup" //prod(pid=**)为成功连接 connect auxiliary "sysbackup/123456@standby as sysbackup" //(not mounted)为成功连接 duplicate target database for standby from active database nofilenamecheck dorecover; //相关参考文件为@/home/oracle/dup.rman，同样奇怪为什么屡次失败，而提前设置好参数后，不带参数命令就可以完成。 rman错误处理： //list backup summary; //delete expired backup; //crosscheck archivelog all; ora错误处理 //如果出现ora错误，比如：ora-16698，则用户oracle下命令 //oerr ora 16698 ,根据提示理解问题 show parameter standby //主备上都查看，默认值manual则数据库下命令alter system set standby_file_management='AUTO' select name,open_mode,database_role,flashback_on,switchover_status from v$database; select status from v$instance; //standby上 alter system switch logfile; //prod上 ls -l /u01/oradata/standby/archivelog //standby上，如果没有则数据库下命令alter system set log_archive_dest_1='LOCATION=/u01/oradata/standby/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=standby' scope=spfile; 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 说明日志同步了 如果发现日志不能同步，尝试： alter system switch logfile; //prod上，改变日志传输通道1/2/3 alter database recover managed standby database cancel; //standby上，取消日志同步， shutdown immediate startup alter database recover managed standby database using current logfile disconnect from session; 重启dataguard顺序，先关闭主库：shutdown immediate 在关闭备库：取消日志同步，shutdown immediate； 先开启备库：lsnrctl start; sql>startup 再开启主库：lsnrctl start; sql>startup 构建测试数据库 //prod上 sqlplus / as sys select con_id,dbid,name,open_mode from v$pdbs //查看pdb数据库,主库下的可插拔数据库，用来建立dgmgrl服务 alter pluggable database pdbprod open; alter session set container=pdbprod; //设置当前会话为数据库pdbprod下 select name from v$datafile; //查看pdbprd下的文件 create tablespace luocs datafile '/u01/oradata/prod/pdbprod/luocs01.dbf' size 100M autoextend on maxsize 500M; //创建pdbprod下的表空间 create user luocs identified by 123456 default tablespace luocs; //创建表空间的用户 alter user luocs quota unlimited on luocs; grant connect,resource to luocs; //授权 sqlplus luocs/123456@pdbprod //用户oracle下命令，连接数据库pdbprod create table t1 (id number, name char(8)); insert into t1 values(1,'luocs'); commit; select * from t1; 以相同方式登录备库，查看表是否同步 alter profile default limit password_life_time unlimited;
配置broker alter system set dg_broker_start=true; //开启broker，数据库下命令，先主后备 alter pluggable database pdbprod open; //打开pdbprod，主、备 alter system set log_archive_dest_2=''; //主、备 dgmgrl sys/123456 //prod上，用户oracle下命令 create configuration prod as primary database is prod connect identifier is prod; //创建pdbprod下的dgmgrl主库 add database standby as connect identifier is standby maintained as physical; //添加pdbprod下的dgmgrl备库，不用切换 enable configuration; //先主后备,备库同样需要打开dgmgrl使用命令 show database verbose prod edit database prod set property LogXptMode='sync'; //设置sync同步模式 edit database standby set property LogXptMode='sync'; show configuration verbose sqlplus luocs/123456@pdbprod //用户oracle下命令，尝试登录 sqlplus luocs/123456@pdbstandby //注意，备库有可能因为未能同步而登录不上 edit configuration set protection mode as MaxAvailability; //dgmgrl下命令，设置为最大高可用模式 show configuration; //查看 edit configuration set protection mode as MaxProtection; //设置为最大保护模式 show configuration; //查看 edit configuration set protection mode as MaxAvailability; //改回来 show configuration; //查看 switchover to standby //我用的是prod上，dgmgrl下命令，手动切换主库备库，简单的一条命令完成切换。另外，我的lsnrctl配置据说有问题，导致主库切换成备库后 未能启动，而备库切换成主库后则正常，问题仍在寻找答案中 select name,open_mode,database_role,flashback_on,switchover_status from v$database; //主、备上，确认 测试是否能同步 failover配置 show fast_start failover //dgmgrl下命令，查看是否开启，默认没有 shutdown immediate //主、备 startup mount alter database flashback on; alter database open; alter pluggable database pdbprod open; select process,status from v$managed_standby; enable fast_start failover; //dgmgrl下，主、备 show configuration verbose; start observer //standby上，dgmgrl下， shutdown abort //prod上，观察standby上的observer，30秒后备库切换为主库。 select name,open_mode,database_role,flashback_on,switchover_status from v$database; //主、备上查看确认， startup //prod上，继续观察standby上的observer，30秒后，prod切换成备库。 select name,open_mode,database_role,flashback_on,switchover_status from v$database; //主、备上查看确认，
注释：此文参考
oracle 12c 安装 Oracle Database 12c 部署Data Guard(ADG、LDG)并实现Switchover和Failover(Rman Duplicate和Broker) Oracle-DataGurd-DG环境搭建中文图文手册Db-11g-10g-dba 三篇文章，第二篇文章完全可以， 但rman恢复错误，丢掉参数则完成，只是备库archive log list却总是没有消息队列，而切换为主库后，则出现。 lsnrctl在failover时主库没有启动 ip地址漂移问题 另外，所以文章都有漏洞和错误，配置人员需要根据情况和手头资料，自己来判断。 最后，所有的资料都将过期，我们的目标是未来的挑战。 第二篇文章的rman命令文件： DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET "db_unique_name"="standby" SET CONTROL_FILES='/u01/oradata/standby/control01.ctl' SET LOG_ARCHIVE_DEST_2="service=pl ASYNC REGISTER VALID_FOR=(online_logfile,primary_role)" SET MEMORY_TARGET="800M" SET AUDIT_FILE_DEST='/u01/admin/standby/adump' SET FAL_SERVER="prod" SET DB_FILE_NAME_CONVERT='/u01/oradata/prod','/u01/oradata/standby' SET LOG_FILE_NAME_CONVERT='/u01/oradata/prod','/u01/oradata/standby' SET standby_file_management='AUTO';