第一次 Oracle 单实例DG搭建（个人记录以纪念，文章中错误很多，来者三思）

教程参阅 http://blog.csdn.net/shiyu1157758655/article/details/72878249

测试环境:

 

	主机1（主库）	主机2（备库）
操作系统	Centos6.5 64位	Rhel 5.8 64位
主机名	sannetocp	gyl
IP	192.168.152.133	192.168.152.88
数据库软件版本	oracle 11.2.0.4	oracle 11.2.0.4
ORACLE_BASE	/u01/app/oracle	/u01/app/oracle
ORACLE_HOME	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/db
ORACLE_SID	ocp
闪回区	开启
归档	开启

 

 

 

 

 

1.1更改为强制日志切换

在DataGuard环境中,为了减少故障时数据损失,我们可以设置ARCHIVE_LAG_TARGET参数,强制进行日志切换。

SYS@ocp> selectlog_mode,force_logging from v$database;

 

LOG_MODE     FOR

------------ ---

ARCHIVELOG   NO

 

SYS@ocp> ALTERDATABASE FORCE LOGGING;

 

Database altered.

 

SYS@ocp> selectlog_mode,force_logging from v$database;

 

LOG_MODE     FOR

------------ ---

ARCHIVELOG   YES

 

 

1.2 创建备库日志文件

首先查询主库的

SYS@ocp> selectgroup#,bytes/1024/1024 from v$log;

 

    GROUP# BYTES/1024/1024

-------------------------

 1                50

 2                50

 3                50

 

SYS@ocp> selectmember from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/database/ocp/redo03.log

/database/ocp/redo02.log

/database/ocp/redo01.log

 

创建standby日志组的个数是原日志组个数+1再与thread的积，size不能小于原日志文件的大小。

alter database addstandby logfile '/database/ocp/standby01.log' size 50m;

alter database addstandby logfile '/database/ocp/standby02.log' size 50m;

alter database addstandby logfile '/database/ocp/standby03.log' size 50m;

alter database addstandby logfile '/database/ocp/standby04.log' size 50m;

 

具体步骤：

SYS@ocp> alterdatabase add standby logfile '/database/ocp/standby01.log' size 50m;

 

Database altered.

 

SYS@ocp> alterdatabase add standby logfile '/database/ocp/standby02.log' size 50m;

 

Database altered.

 

SYS@ocp> alterdatabase add standby logfile '/database/ocp/standby03.log' size 50m;

 

Database altered.

 

SYS@ocp> alterdatabase add standby logfile '/database/ocp/standby04.log' size 50m;

 

Database altered.

 

SYS@ocp> selectmember from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/database/ocp/redo03.log

/database/ocp/redo02.log

/database/ocp/redo01.log

/database/ocp/standby01.log

/database/ocp/standby02.log

/database/ocp/standby03.log

/database/ocp/standby04.log

 

7 rows selected.

 

1.3 确认已经开启数据库闪回

SYS@ocp> selectlog_mode,open_mode,flashback_on from v$database;

 

LOG_MODE    OPEN_MODE                  FLASHBACK_ON

-------------------------------- ------------------

ARCHIVELOG   READ WRITE          NO

 

 

1.4修改参数文件

SYS@ocp>alter system set log_archive_config='DG_CONFIG=(ocp,oca)';

 

System altered.

 

SYS@ocp>altersystem set log_archive_dest_2='SERVICE=ocaVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oca' scope=spfile;

 

 

SYS@ocp> altersystem set log_archive_dest_state_1='enable';

 

System altered.

 

SYS@ocp> altersystem set log_archive_dest_state_2='enable';

 

System altered.

 

SYS@ocp>altersystem set db_file_name_convert='/database/ocp','/database/ocp' scope=spfile;

 

System altered.

 

SYS@ocp> altersystem set log_file_name_convert='/database/ocp','/database/ocp'scope=spfile;

 

System altered.

 

SYS@ocp>alter system set fal_server='ocp';

 

System altered.

 

SYS@ocp>alter system set fal_client='oca';

 

System altered.

 

SYS@ocp> altersystem set standby_file_management='AUTO';

 

System altered.

 

 

由于上述生效需要重启，所以重启数据库

SYS@ocp> shutdownimmediate

Database closed.

Database dismounted.

ORACLE instance shutdown.

SYS@ocp>

SYS@ocp>

SYS@ocp> startup

ORACLE instancestarted.

 

Total System GlobalArea 1235959808 bytes

FixedSize                    2252784 bytes

VariableSize                  419430416 bytes

DatabaseBuffers          805306368 bytes

RedoBuffers                    8970240 bytes

Database mounted.

Database opened.

 

 

修改监听文件

oracle@sannetocp ~]$vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

 

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

 

 

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = ocp)

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

     (SID_NAME = ocp)

    )

   )

 

 

修改TNS配置文件：

[root@sannetocporacle]# vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora