linux下oracle 11g dg环境搭建

一、主库操作

确认主库是否打开归档、force logging

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> SELECT NAME,LOG_MODE,FORCE_LOGGING FROM v$DATABASE;

修改主库参数

SQL> create pfile='/u01/pfile.ora' from spfile;

SQL> alter system set db_unique_name=uniquepdg scope=both;

SQL> alter system set log_archive_config='dg_config=(uniquepdg,uniquesdg)' scope=both;

SQL> alter system set log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg' scope=spfile;

SQL> alter system set log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg' scope=both;

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;  //默认即是exclusive

SQL> alter system set log_archive_max_processes=10 scope=both;

主库上有关standby角色的参数

SQL> alter system set fal_server=tns_sdg scope=both;

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

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

SQL> alter system set standby_file_management=auto scope=both;

从库传送pfile参数

scp /u01/pfile2.ora 192.168.12.31:$ORACLE_HOME/dbs/

从主库拷贝密码文件

$ cd $ORACLE_HOME/dbs

$ scp orapworcl 192.168.12.23:$ORACLE_HOME/dbs/orapworcl

二、从库操作

创建相应目录

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

$ mkdir -p /u01/oradata/orcl

$ mkdir -p /u01/app/oracle/fast_recovery_area

$ mkdir -p /u01/arch

$ mkdir -p /u01/backup

备库以spfile启动

SQL> startup nomount  pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> shutdown immediate;

SQL> startup nomount;

主库进行全备

rman target /

run {

allocate channel d0 type disk;

allocate channel d1 type disk;

backup format '/u01/backup02/full_t%t_s%s_p%p' database;

sql 'alter system archive log current';

backup format '/u01/backup02/arc_t%t_s%s_p%p' archivelog all;

release channel d0;

release channel d1;

}

创建备用控制文件

backup current controlfile for standby format '/u01/backup02/control01.ctl';

拷贝备份文件及备用控制文件到备库

$ cd /u01/backup

$ scp * 192.168.12.23:/u01/backup02

从库恢复控制文件

$ rman target /

RMAN> restore standby controlfile from '/u01/backup02/control01.ctl';

RMAN> alter database mount;

从备库还原数据文件

RMAN> restore database;

RMAN> recover database;----------------------------恢复完成后数据库处于mount状态

Connected.

SQL> select  open_mode  from  v$database;

OPEN_MODE

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

MOUNTED

主库：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata/orcl/sredo07.log' size 50M;

备库：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata2/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata2/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata2/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata2/orcl/sredo07.log' size 50M;

三、配置主备库监听及tns文件

主库监听文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

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

      (SID_NAME = orclpdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

备库监听文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

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

      (SID_NAME = orclsdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

主备库tns文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

TNS_PDG =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquepdg)

    )

  )

TNS_SDG =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquesdg)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

备库打开只读模式

SQL> alter database open;         

//10g无法启动到read only模式,只能启动到mount模式

备库启动实时应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

三、日常运维管理

DG环境的启动与关闭 

DG环境的关闭 

检查DG环境主备库的日志使用情况 

操作位置：主库&备库 

SQL> archive log list; 

主库与备库当前使用的日志编号相同

停主库的监听程序 

lsnrctl stop 

停备库的监听程序 

lsnrctl stop 

关闭主数据库 

SQL> shutdown immediate;

查看备库的开启模式 

SQL> select open_mode from v$database;  

如果发现当前数据库是read only with apply模式，则需要执行下面命令关闭归档日志应用程序，

如果发现是read only模式则直接关闭数据库即可。正常情况下备库应该时刻处于应用归档日志的模式。 

关闭备数据库的归档应用程序 

SQL> alter database recover managed standby database cancel; 

关闭备数据库 

SQL> shutdown immediate; 

这样，整个Data Guard环境就算是完整的关闭掉了...

DG环境的启动 

启动DG环境的主库 

sqlplus / as sysdba 

SQL> startup; 

SQL> select status from v$instance; 

启动主库的监听程序 

lsnrctl start 

启动DG环境的备库到mount或open状态 

sqlplus / as sysdba 

SQL> startup; 

SQL> startup mount; 

启动备库的监听程序 

lsnrctl start 

主库切换归档日志 

操作命令： 

SQL> alter system archive log current; 

查看备库是否有新应用过来的日志 

SQL> select sequence#,applied from v$archived_log; 

备库上开启归档日志应用进程 

SQL> alter database recover managed standby database disconnect from session; 

主库与备库验证当前redo log 

操作位置：主库&备库 

SQL> archive log list; 

如果此时发现主库与备库当前使用的redo日志的编号一致则说明重启的DG环境一切正常。 

这样，这个Data Guard环境就算是去正常的启动了... 

日常运维操作

 检查主备库是否存在GAP

主库检查current sequence#

SQL> select thread#,sequence#,status from v$log;

   THREAD#  SEQUENCE# STATUS

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

         1        847 INACTIVE

         1        848 INACTIVE

         1        849 CURRENT

主库检查LNS进程正在写的sequence#

SQL> select process,sequence#,status from v$managed_standby;

PROCESS    SEQUENCE# STATUS

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

ARCH              847 CLOSING

ARCH              848 CLOSING

LNS               849 WRITING

备库检查正在应用的sequence#

SQL> SELECT PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

PROCESS      THREAD#  SEQUENCE# STATUS

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

ARCH               1        848 CLOSING

ARCH               1        847 CLOSING

MRP0               1        849 APPLYING_LOG

RFS                0          0 IDLE

RFS                1        849 IDLE

检查是否存在GAP

SQL> SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;---由此可判断主备库无GAP，可进行正常switchover切换

set linesize 300

col DATABASE_ROLE  for 20

col DB_UNIQUE_NAME for a30 

col INSTANCE   for a30  

col OPEN_MODE  for a30

col PROTECTION_MODE  for a30

col PROTECTION_LEVEL   for a30

col SWITCHOVER_STATUS   for a30

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

配置文件内容说明

主库

orcl.__db_cache_size=264241152

orcl.__java_pool_size=4194304

orcl.__large_pool_size=71303168

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

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

orcl.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain='segment2'

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

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUEPDG'

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

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

*.fal_server='TNS_SDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg'

*.log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg'

*.log_archive_max_processes=10

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

从库配置文件

orcl.__db_cache_size=327155712

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

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

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

orcl.__streams_pool_size=0

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8192

*.db_domain='segment2'

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

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUESDG'

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

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

*.fal_server='TNS_PDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquesdg'

*.log_archive_dest_2='service=tns_pdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquepdg'

*.log_archive_max_processes=10

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

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

主库监听

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

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

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

从库监听

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

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

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle