oracle DG搭建
安装环境
在主机1上安装数据库软件，并建监听和实例，在主机2上安装数据库软件，并建监听，但不建实例，dg搭建之后数据会同步过去。
数据库参数优化(也可在spfile文件统一修改)
注:scope=spfile表示更改spfile文件的值，重启数据库后才能生效
sga,pga配置
sga:（内存大小*80%）*80% = 128G * 80% * 80% = 82G；
pga:（内存大小*80%）*20% = 128G * 80% * 80% = 20G；
alter system set sga_max_size=82000m scope=spfile;
alter system set sga_target=82000m scope=spfile;
alter system set pga_aggregate_target=20000m scope=spfile;
JOB_QUEUE_PROCESSES参数
JOB_QUEUE_PROCESSES参数是job的进程个数，默认是0；
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=20;
共享池大小
Shared pool是SGA中最关键的内存片段,共享池主要由库缓存(共享SQL区和PL/SQL区)和数据字典缓存组成。其中库缓存的作用是存放频繁使用的sql，pl/sql代码以及执行计划。数据字段缓存用于缓存数据字典。在内存空间有限的容量下，数据库系统根据一定的算法决定何时释放共享池中的sql，pl/sql代码以及数据字典信息。所以shared_pool_size不能太小，否则执行效率会很低。
alter system set shared_pool_size=15000m scope = spfile;
数据库最大连接数
数据库的最大可支持的连接数，根据公司逻辑库的实际情况1500足够了
alter system set processes=1500 scope = spfile;
数据文件个数
db_files参数限制了数据库数据文件总的个数，datafiles数目达到db_files指定后数据库不能添加新的数据文件
alter system set db_files=1000 scope=spfile;
游标参数
OPEN_CURSORS 指定一个会话一次最多可以拥有的游标数，不够无法使用游标，过大不会增加系统开销
alter system set open_cursors=2000 scope=both;
alter system set session_cached_cursors=500 scope=spfile;
默认密码期限
Oracle数据库密码期限是180，把它改成无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
数据库连接超时时间
默认是60秒，60秒没连接，会报错，把它设置成0
LSNRCTL> set inbound_connect_timeout 0
LSNRCTL> set save_config_on_stop on 生效
关闭审计
oracle 11g推出了审计功能，这个功能会针对很多操作都产生审计文件.aud，日积月累下来这些文件也很多，考虑到实际情况，将审计功能关闭
alter system set audit_trail=none scope = spfile;
重启数据库
上述修改都需重启数据库生效
SQL>shutdown immediate
SQL>startup；
主数据库配置(192.168.8.173)
开启归档与强制LOGGING
Oracle dg模式需要保证主从库一致，需要传输archive log和redo log到从库，如果不是归档模式无法保证主从库的数据一致。
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> ALTER DATABASE FORCE LOGGING;
创建redo和standy redo日志
根据公司平台情况创建6个redo日志组，每个大小1G，创建standby日志组的个数是原日志组个数+1再与thread的积，size不能小于原日志文件的大小，所以这里创建7个standby redo。
redo log创建和删除
1：select * from v$log; 查看redo日志状态
2：alter system switch logfile; 将当前redo日志切到下一个执行
3：alter database add logfile group 6 ('/oracle/oradata/dbxxx/redo06.log') size 1024m reuse;
添加新的redo日志
4: alter database drop logfile group 1; 删除redo日志组（慎用）
standby redo log 的创建只是在logfile前加一个standby
1.alter database add standby logfile group 7('/oracle/oradata/dbxxx/standyredo01.log') size 1024m reuse;
创建主库密码文件
Data Guard 配置中所有数据库必须都拥有独立的密码文件,并且必须保证同一个 Data Guard 配置中所有数据库服务器的 SYS 用户拥有相同密码以保证redo 数据的顺利传输,因为 redo 传输服务通过认证的网络会话来传输redo 数据,而会话使用包含在密码文件中的SYS 用户密码来认证。
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=xxxx entries=1 force=y
SPFILE文件配置(128G内存数据库)
注：数据库启动时加载spfile文件，需对参数进行调整，以及主备配置
SQL> create pfile='/oracle/product/11.2/dbs/initdbxxx.ora' from spfile;
vi /oracle/product/11.2/dbs/initdbxxx.ora
dbxxx.__db_cache_size=66035122176
dbxxx.__java_pool_size=536870912
dbxxx.__large_pool_size=536870912
dbxxx.__oracle_base='/oracle'#ORACLE_BASE set from environment
dbxxx.__pga_aggregate_target=21474836480
dbxxx.__sga_target=83751862272
dbxxx.__shared_io_pool_size=0
dbxxx.__shared_pool_size=15569256448
dbxxx.__streams_pool_size=536870912
*.audit_file_dest='/oracle/admin/dbxxx/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/dbxxx/control01.ctl','/oracle/flash_recovery_area/dbxxx/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_name='dbxxx'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=20971520000
*.DB_UNIQUE_NAME='dbxxxpr' *.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbxxxXDB)'
*.FAL_CLIENT='dbxxxpr'
*.FAL_SERVER='dbxxxdg'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbxxxpr,dbxxxdg)'
*.log_archive_dest_1='location=/oracle/oradata/dbxxx/archivelog'
*.log_archive_dest_2='SERVICE=dbxxxdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbxxxdg'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=1000
*.pga_aggregate_target=20971520000
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=500
*.sessions=1655
*.sga_target=83751862272
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
SPFILE文件替换
spfiledbxxx.ora复制initdbxxx.ora
rm $ORACLE_HOME/dbs/spfiledbxxx.ora
SQL> create spfile from pfile='/oracle/product/11.2.0/dbs/initdbxxx.ora';
tns文件配置
vi $ORACLE_HOME/network/admin/tnsnames.ora ,配置如下：
dbxxxpr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbxxx)
)
)
dbxxxdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.174)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbxxx)
)
)
listener文件配置
vi $ORACLE_HOME/network/admin/tnsnames.ora ,配置如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server173)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle
SAVE_CONFIG_ON_STOP_LISTENER = ON
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
备数据库配置(192.168.8.174)
文件复制
将主数据库/oracle/product/10.2/network/admin下的网络配置文件（tnsnames.ora,listener.ora）,和/oracle/product/10.2/dbs下的密码文件orapwdbxxx和initdbxxx.ora拷贝scp到备数据库相同目录下，scp不行的话用sz下载，rz上传。
initdbxxx.ora参数配置
注：主备切换对应的名称正好相反，其他配置一样
修改initdbxxx.ora文件：
dbxxx.__db_cache_size=66035122176
dbxxx.__java_pool_size=536870912
dbxxx.__large_pool_size=536870912
dbxxx.__oracle_base='/oracle'#ORACLE_BASE set from environment
dbxxx.__pga_aggregate_target=21474836480
dbxxx.__sga_target=83751862272
dbxxx.__shared_io_pool_size=0
dbxxx.__shared_pool_size=15569256448
dbxxx.__streams_pool_size=536870912
*.audit_file_dest='/oracle/admin/dbxxx/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oracle/oradata/dbxxx/control01.ctl','/oracle/flash_recovery_area/dbxxx/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_name='dbxxx'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=20971520000
*.DB_UNIQUE_NAME='dbxxxdg'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbxxxXDB)'
*.FAL_CLIENT='dbxxxdg'
*.FAL_SERVER='dbxxxpr'
*.local_listener='for_db'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dbxxxpr,dbxxxdg)'
*.log_archive_dest_1='location=/oracle/oradata/dbxxx/archivelog'
*.log_archive_dest_2='SERVICE=dbxxxpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbxxxpr'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=1000
*.pga_aggregate_target=20971520000
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=500
*.sessions=1655
*.sga_target=83751862272
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'
创建相关目录
mkdir -p /oracle/fast_recovery_area/dbxxx
mkdir -p /oracle/admin/dbxxx
cd /oracle/admin/dbxxx
mkdir -p adump dpdump pfile
mkdir -p /oracle/oradata/dbxxx/
mkdir -p /oracle/oradata/dbxxx/archivelog
tns文件配置
修改/oracle/product/10.2/network/admin目录下的tnsnames.ora文件，配置如下：
dbxxxdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server174)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbxxx)
)
)
dbxxxpr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbxxx)
)
)
listener文件配置
vi $ORACLE_HOME/network/admin/tnsnames.ora ,配置如下：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbxxx)
(ORACLE_HOME = /oracle/product/10.2)
(SID_NAME = dbxxx)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server174)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /oracle
启动备库到nomount
SQL> conn /as sysdba
SQL> create spfile from pfile='/oracle/product/11.2.0/dbs/initgdqxt.ora';
SQL> startup nomount
备数据库还原
[[email protected] ~]$ rman target sys/****@dbxxxpr auxiliary sys/****@dbxxxdg
RMAN> duplicate target database for standby nofilenamecheck from active database;
切换到同步模式
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;