oracle11g dg 配置（图文版）

有点懒，大家多见谅图片的位置就不一一添加了，工作比较忙，只给大家复制粘贴了下我的文档。

第三章 主库配置

3.1 创建目录并修改名称

//在主库创建所需的文件夹

mkdir /u01/app/oracle/arch

//修改名称

sqlplus / assysdba

show parametername

alter system set db_unique_name=orclscope=spfile;

！！！！如果默认是上图内容，则可不必执行下面2段sql语句！！！！

alter system set service_names=orclscope=spfile;

alter system set instance_name=orclscope=spfile;

3.2 配置日志归档模式

shutdownimmediate;

startup mount

//设置归档日志目录

alter system set log_arcHive_dest_1='location=/u01/app/oracle/arch';

//验证日志目录

archive log list

//开启归档模式

Alter databasearchivelog;

//开启强制记录日志模式

alter databaseforce logging;

验证

selectFORCE_LOGGING from v$database;

3.3 添加 standby redolog日志文件

alter database add standby logfile group 11 '/u01/app/oracle/oradata/orcl/std11.log'size 60M;

alter database add standby logfile group 12 '/u01/app/oracle/oradata/orcl/std12.log'size 60M;

alter database add standby logfile group 13 '/u01/app/oracle/oradata/orcl/std13.log'size 60M;

alter database add standby logfile group 14 '/u01/app/oracle/oradata/orcl/std14.log'size 60M;

//验证，加上系统默认的三条共7条

Select member,type from v$logfile;

3.4 开启闪回数据库

//开启闪回数据库（作用：当发生故障转移时，这个功能非常有用，它能让你将老的主库闪回到故障前，然后将其转换为备库。如果没有启用闪回功能，你就必须重建备库，意味着要再复制一次数据文件。除了这个好处，闪回还能在某些情况下让你避免从备份恢复数据）

//查看闪回数据库状态

show parameter db_recovery_file_dest

//更改路径和大小

可以修改位置：sql>alter system set db_recovery_file_dest='新路径';
       更改大小：sql>alter system set db_recovery_file_dest_size=400G;

//查看是否启用，默认是不开启的

select flashback_on from v$database;

alter database flashback on;

//验证

select flashback_on from v$database;

3.5 DG配置

// Data Guard 配置里的另外一个库的名字

alter system setlog_archive_config='DG_CONFIG=(orcl,orclstd)';

//配置归档日志位置（很重要）

alter system setlog_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

//配置重做日志到备份库（很重要）

alter system set log_archive_dest_2='SERVICE=orclstdASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd';

//设置日志格式

alter system setlog_archive_fORMat='arch_%r_%t_%s.arc' scope=spfile;

//设置FAL_SERVER（作用：这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志）

alter system set fal_server=orclstd;

alter system set fal_client=orcl;

//配置数据文件和日志文件目录

show parameter convert

alter system set db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'scope=spfile;

shutdown immediate

startup mount

//验证

show parameter convert

3.6 设置自动管理

//查看主库是否自动管理文件（作用：主库添加或删除数据文件时，备库也跟着添加删除数据文件）

show parameter standby

alter system set standby_file_management=auto;

//验证

show parameter standby

3.7 修改fal参数

alter system set fal_client=orcl;

alter system set fal_server=orclstd;

//验证

show parameter fal;

3.8 配置密码文件

//由于默认的密码不知道，所以可以删除或备份一份密码文件

cd /u01/app/oracle/product/11.2.0/db_1/dbs/

mv orapworcl orapworcl.bak

创建密码文件

orapwd file=orapworcl passWord=jinhetech force=y

3.9 配置监听

cd/u01/app/oracle/product/11.2.0/db_1/network/admin/

在监听文件里添加：

vim listener.ora

===========================================================

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = orcl)

     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

     (SID_NAME = orcl)

    )

 )

LISTENER =

 (DESCRIPTION_LIST =

    (DESCRIPTION=

      (ADDRESS =(PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS =(PROTOCOL = tcp)(HOST = compute02)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

===========================================================

重启监听，重启数据库.

验证：

lsnrctl status

//配置远程监听，添加一下内容

vim tnsnames.ora

===========================================================

ORCL =

  (DESCRIPTION =

    (ADDRESS =(PROTOCOL = TCP)(HOST = compute02)(PORT = 1521))

   (CONNECT_DATA =

      (SERVER =DEDICATED)

     (SERVICE_NAME = orcl)

    )

  )

ORCLSTD =

 (DESCRIPTION =

   (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521))

   (CONNECT_DATA =

     (SERVER = DEDICATED)

     (SERVICE_NAME = orclstd)

    )

  )

===========================================================

验证

tnsping compute02

3.10 设置参数文件

//生成修改主库的pfile用于备库

create pfile='/tmp/init.ora' from spfile;

//生成自己的pfile配置文件

create pfile from spfile;

//分别修改主备库配置

//修改下面两个文件配置，手动添加红色字段

vim /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

vim /tmp/init.ora

===========================================================

orcl.__db_cache_size=7784628224

orcl.__java_pool_size=268435456

orcl.__large_pool_size=268435456

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE setfrom environment

orcl.__pga_aggregate_target=11005853696

orcl.__sga_target=16106127360

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=7516192768

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='ORCL'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCL'

*.fal_server='ORCLSTD'

*.log_archive_config='DG_CONFIG=(orcl,orclstd)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_2='SERVICE=orclstd ASYNC LGWRVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstd'

*.log_archive_format='arch_%r_%t_%s.arc'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcl/'

*.memory_target=26902265856

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

===========================================================

3.11 创建控制文件

alter database create standby controlfile as '/tmp/controlstb.ctl';

3.12 传输文件

！！！！！！按顺序执行！！！！！

//传输密码文件到备库

cd /u01/app/oracle/product/11.2.0/db_1/dbs/

scp orapworcl oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/

//拷贝监听文件到备库

cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

scp listener.ora tnsnames.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/network/admin/

//拷贝参数文件到备库

scp /tmp/init.ora oracle@172.16.0.1:/u01/app/oracle/product/11.2.0/db_1/dbs/

//拷贝数据文件到备库

scp /u01/app/oracle/oradata/orcl/* oracle@172.16.0.1:/u01/app/oracle/oradata/orcl/

//拷贝控制文件到备库

scp /tmp/controlstb.ctloracle@172.16.0.1:/u01/app/oracle/oradata/orcl/control01.ctl

scp /tmp/controlstb.ctl oracle@172.16.0.1:/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

第四章 备库配置

4.1 创建所需的文件夹

//在备库创建所需的文件夹

mkdir -p/u01/app/oracle/oradata/orcl/

mkdir -p/u01/app/oracle/arch/

mkdir -p /u01/app/oracle/flash_recovery_area/orcl

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/admin/orcl/dpdump

chown -R oracle:oinstall/u01/app

chmod 6751/u01/app

4.2 修改参数文件

cd/u01/app/oracle/product/11.2.0/db_1/dbs/

vim init.ora

//修改init.ora为如下内容：

===========================================================

*.db_unique_name='orclstd'

fal_server='orcl'

fal_client='orclstd'

log_archive_dest_1='location=/u01/app/oracle/archVALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclstd'

log_archive_dest_2='service=orcl valid_for=(online_logfiles,primary_role)db_unique_name=orcl'

========================================================

4.3 配置监听

cd /u01/app/oracle/product/11.2.0/db_1/network/admin/

vim listener.ora

========================================================

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orclstd)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orclstd)

    )

 )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = controller)(PORT = 1521))

    )

  )

===========================================================

//启动监听

lsnrctl start

//验证

tnsping orcl

tnsping orclstd

第五章 日常维护

5.1 启动方式（先启动备库在启动主库）

//备库操作

shut immediate

startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora';

alter databasemount standby database;

alter databaseopen read only;

select open_modefrom v$database;

alter databaserecover managed standby database using current logfile disconnect from session;

select open_modefrom v$database;

archive log list;

//生成自己的pfile配置文件

create pfile from spfile;

//主库操作

shut immediate

startup

//主库最终监听状态

//备库最终监听状态