linux下oracle 11g dg环境搭建
项目 | 192.168.12.22（主） | 192.168.12.23（从） |
oracle sid | orcl | orcl |
db_unique_name | uniquepdg | uniquesdg |
tnsname | Tns_pdg | Tns_sdg |
一、主库操作
确认主库是否打开归档、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