DG_安装一
ORACLE DG 的搭建（方法一）
说明：
备库生成原理：
1 安装数据库软件 ( 和主库目录一致 )
2 主库通过 rman 进行一次全备，并发送到备库
3 备库通过 rman 恢复成和主库一样的数据库
4 更改备库相应的配置（主备库 sid 不同）
主机
IP: 192.168.1.181
主机名 : chen01
OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)
oracle: 11.2.0.1.0
SID: orcl
备机
IP ： 192.168.1.183
主机名 : chen03
OS: Red Hat Enterprise Linux Server release 6.4 (Santiago)
oracle: 11.2.0.1.0
SID: db01
一：主机 , 备机分别配置 hosts 文件
192.168.1.181
[root@chen01 ~]# vim /etc/hosts
192.168.1.181 chen01
192.168.1.183 chen03
192.168.1.183
[root@chen03 ~]# vim /etc/hosts
192.168.1.183 chen03
192.168.1.181 chen01
二：主机修改为归档模式，强制写日志
192.168.1.181
SQL> startup mount
SQL> alter database archivelog;
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
三：主机创建密码文件
192.168.1.181
[oracle@chen01 ~]$ cd $ORACLE_HOME/network/admin
[oracle@chen01 admin]$ orapwd file=orapworcl password=oracle
四：主机配置 standby redolog （最佳性能模式可以忽略，如果将来变成备库且要转为其它两种模式则要建立）
192.168.1.181
standby redolog 的组数参考公式： (online redolog 组数 + 1) * 数据库线程数；单机线程数为 1 ， RAC 一般为 2 。
standby redolog 的组成员数和大小也尽量和 online redolog 一样。
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
SQL> select group#,members,bytes/1024/1024||'M' M from v$log;
GROUP# MEMBERS M
---------- ---------- -----------------------------------------
1 1 50M
2 1 50M
3 1 50M
SQL> alter database add standby logfile
2 group 4 ('/u01/app/oracle/oradata/orcl/stdby_redo04.log') size 50M,
3 group 5 ('/u01/app/oracle/oradata/orcl/stdby_redo05.log') size 50M,
4 group 6 ('/u01/app/oracle/oradata/orcl/stdby_redo06.log') size 50M;
Database altered.
SQL> select * from v$logfile order by group#;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
4 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo04.log NO
5 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo05.log NO
6 STANDBY /u01/app/oracle/oradata/orcl/stdby_redo06.log NO
6 rows selected.
SQL> select group#,members,bytes/1024/1024||'M' M from v$log;
GROUP# MEMBERS M
---------- ---------- -----------------------------------------
1 1 50M
2 1 50M
3 1 50M
SQL> select group#,bytes/1004/1024||'M' M from v$standby_log;
GROUP# M
---------- -----------------------------------------
4 50.9960159362549800796812749003984063745M
5 50.9960159362549800796812749003984063745M
6 50.9960159362549800796812749003984063745M
五：设置主库初始化参数
192.168.1.181
1 启动 db 接受或发送 redo data ，包括所有库的 db_unique_name
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL> alter system set log_archive_config='dg_config=(orcl,db01)' scope=spfile;
System altered.
2 主库归档目的地
[oracle@chen01 ~]$ mkdir arch_orcl
[oracle@chen01 ~]$ mkdir arch_db01
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch_orcl valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2='service=db_db01 LGWR ASYNC valid_for=(online_logfiles,primary_roles) db_unique_name=db01' scope=spfile;
System altered.
( 当该库充当主库角色时，设置物理备库 redo data 的传输目的地 )
3 最大 ARCn 进程数
SQL> alter system set log_archive_max_processes=5 scope=spfile;
System altered.
4
SQL> alter system set log_archive_dest_state_1=ENABLE scope=spfile;
System altered.
SQL> alter system set log_archive_dest_state_2=ENABLE scope=spfile;
System altered.
5 exclusive or shared ，所有库 sys 密码要一致，默认是 exclusive
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
以下
是主库切换为备库，充当备库角色时的一些参数设置，如果不打算做数据库切换就不用设置了
6 配置网络服务名，假如转换为备库角色时，从这里获取丢失的归档文件
SQL> alter system set fal_server=db_db01 scope=spfile;
System altered.
SQL> alter system set fal_client=db_orcl scope=spfile;
System altered.
（配置网络服务名， fal_server 拷贝丢失的归档文件到这里）
7 前为切换后的主库路径，后为切换后的备库路径，如果主备库目录结构完全一样，则无需设定
SQL> alter system set db_file_name_convert='db01','orcl' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='db01','orcl' scope=spfile;
System altered.
同上，这两个名字转换参数是主备库的路径映射关系，可能会是路径全名，看情况而定
8
SQL> alter system set standby_file_management=auto scope=spfile;
System altered.
（ auto 后当主库的 datafiles 增删时备库也同样自动操作，且会把日志传送到备库 standby_archive_dest 参数指定的目录下，
确保该目录存在，如果你的存储采用文件系统没有问题，但是如果采用了裸设备，你就必须将该参数设置为 manual ）
9 一般和 LOG_ARCHIVE_DEST_1 的位置一样，如果备库采用 ARCH 传输方式，那么主库会把归档日志传到该目录下
SQL> alter system set standby_archive_dest='location=/home/oracle/arch_orcl' scope=spfile;
System altered.
有了以上参数设置，则无论该库充当主库角色还是备库角色都无需再修改了。
11 重启数据库，使参数生效
192.168.1.181
SQL> shutdown immediate;
SQL> startup
六 备份主库数据文件 ( 可以冷备，热备 rman 备等 )
192.168.1.181
[oracle@chen01 ~]$ mkdir backup
[oracle@chen01 ~]$ lsnrctl stop
[oracle@chen01 ~]$ rman target /
RMAN> backup full database format '/home/oracle/backup/backup_%T_%s_%p.bak';
RMAN> sql "alter system archive log current";
RMAN> backup archivelog all format='/home/oracle/backup/arch_%T_%s_%p.bak';
七 主库上建立备库的控制文件（控制文件通常需要有多份，手工将文件复制几份）
192.168.1.181
SQL> alter database create standby controlfile as '/home/oracle/backup/stdby_control01.ctl';
Database altered.
[oracle@chen01 backup]$ cp stdby_control01.ctl stdby_control02.ctl
八 为备库准备 init 参数文件
备注：主库和备库的db_name 必须相同，为orcl
主库和备库的db_unique_name 必须不同，分别为orcl 和db01
192.168.1.181
1
SQL> create pfile='/home/oracle/backup/initdb01.ora' from spfile;
File created.
2
[oracle@chen01 backup]$ unset LANG
[oracle@chen01 backup]$ vim initdb01.ora
*.audit_file_dest='/u01/app/oracle/admin/db01/adump'
*.control_files='/u01/app/oracle/oradata/db01/stdby_control01.ctl','/u01/app/ora
cle/flash_recovery_area/db01/stdby_control02.ctl'
*.db_name='orcl'
*.db_unique_name=’db01’
*.log_archive_config='dg_config=(db01,orcl)'
*.log_archive_dest_1='location=/home/oracle/arch_db01 valid_for=(all_logfiles,al
l_roles) db_unique_name=db01'
*.log_archive_dest_2='service=db_orcl LGWR ASYNC valid_for=(online_logfiles,prim
ary_roles) db_unique_name=orcl'
*.fal_client='DB_DB01'
*.fal_server='DB_ORCL'
*.log_file_name_convert='orcl','db01'
*.db_file_name_convert='orcl','db01'
*.standby_archive_dest='location=/home/oracle/arch_db01'
另外，如果备库将来要打开成只读模式，需要确认 audit_trail 参数不是含 db ，应该设成 os 或 none 。
九 拷贝上面生成的文件 backup_%T.bak 、 stdby_control01/02/03.ctl 、 initdb01.ora 到备库所在主机
192.168.1.181
[oracle@chen01 ~]$ scp backup/* 192.168.1.183:/home/oracle/backup/
[oracle@chen01 backup]$ scp initdb01.ora 192.168.1.183:$ORACLE_HOME/dbs/
十 建立备库的数据库软件，目录及环境变量
192.168.1.183
[oracle@chen03 ~]$ /database/runInstaller
删除数据库orcl,db01
新建数据库，只创建数据库软件
[oracle@chen03 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@chen03 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0
[oracle@chen03 ~]$ export ORACLE_SID=db01
[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
[oracle@chen03 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
[oracle@chen03 ~]$ mkdir -p /u01/app/oracle/oradata/$ORACLE_SID
[oracle@chen03 ~]$ cp backup/* /u01/app/oracle/oradata/db01/
[oracle@chen03 ~]$ cp backup/stdby_control02.ctl /u01/app/oracle/flash_recovery_area/db01/
十一 建立主库监听和主备库的网络服务名（必须是 dedicated 的），并启动监听
192.168.1.181 192.168.1.183
[root@chen01 ~]# xhost +
[root@chen01 ~]# su - oracle
[oracle@chen01 ~]$ netmgr
删除监听 -- 增加监听
删除服务 -- 新建服务 网络服务名： db_orcl(192.168.1.181),db_db01(192.168.1.183)
关闭防火墙
iptables -F
sentenforce 0
确保主机和备机可以互通
[oracle@chen01 ~]$ tnsping db_orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 19:56:18
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.181)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@chen01 ~]$ tnsping db_db01
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 14-NOV-2014 19:56:21
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.183)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db01)))
OK (10 msec)
十二 配置并启动备库
192.168.1.183
[oracle@chen03 dbs]$ orapwd file=orapwdb01 password=oracle -- 和主机密码必须相同
SQL> create spfile from pfile;
File created.
SQL> startup nomount
SQL> alter database mount standby database;
Database altered.
[oracle@chen03 dbs]$ rman target /
RMAN> restore database; --- 主库，备库目录一致才能保证恢复成功
RMAN> restore archivelog all;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
db01 MOUNTED
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
db01 MOUNTED
十三 检查主库备库是否配置成功
192.168.1.181
SQL> select dest_name,status from v$archive_dest_status;
DEST_NAME STATUS
------------------------- ---------
LOG_ARCHIVE_DEST_1 VALID ------1 和 2 必须确保都是 VALID
LOG_ARCHIVE_DEST_2 VALID
如何不是 VALID 可能的原因有：
1 防火墙 iptables 和 selinux 没有关
2 监听没有启动或配置不合理
3 参数配置不合理
192.168.1.181
SQL> col dest_name for a25
SQL> select dest_name,status from v$archive_dest_status;
DEST_NAME STATUS
------------------------- ---------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
十四 在备库上 , 启动 redo apply --- --- 启动管理恢复进程
SQL> alter database recover managed standby database disconnect from session;
Database altered.
十五 主库归档测试
主库：
192.168.1.181
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch_orcl
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
备库
192.168.1.183
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch_db01
Oldest online log sequence 11
Next log sequence to archive 0
Current log sequence 28
主库归档后
192.168.1.181
SQL> alter system switch logfile;-- 对单实例数据库或 RAC 中的当前实例执行日志切换
System altered.
SQL> alter system archive log current; -- 对数据库中的所有实例执行日志切换
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch_orcl
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
此时备库
192.168.1.183
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch_db01
Oldest online log sequence 28
Next log sequence to archive 0
Current log sequence 30
===============================================================================
启动备库失败及解决办法
192.168.1.183
[oracle@chen03 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 15 01:52:25 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
ERROR:
ORA-01075:
[oracle@chen03 dbs]$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}'
[oracle@chen03 dbs]$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}'
851986
884755
917524
[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm
resource(s) deleted
[oracle@chen03 dbs]$ ipcs -m | grep oracle | awk '{print $2}'
[oracle@chen03 dbs]$ ps -ef | grep ora_
oracle 9425 9125 0 02:02 pts/1 00:00:00 grep ora_
http://blog.csdn.net/wyzxg/article/details/7280223
SQL> create pfile from spfile;
File created.
SQL> alter database mount;
alter database mount
*
? 1 ?????:
ORA-00205: ?????????, ??????, ???????
SQL> alter session set nls_language=american;
Session altered.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
===============================================================================
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29785807/viewspace-1571772/ ，如需转载，请注明出处，否则将追究法律责任。
