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