189 8069 5689
项目
企业建站必须是能够以充分展现企业形象为主要目的，是企业文化与产品对外扩展宣传的重要窗口，一个合格的网站不仅仅能为公司带来巨大的互联网上的收集和信息发布平台，创新互联面向各种领域：柔性防护网等成都网站设计、全网整合营销推广解决方案、网站设计等建站排名服务。 | 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
