oracle11g 搭建DG

oracle11g 搭建DG
一.主库上的操作
1.创建密码文件
orapwd file=orapwtest password=oracle force=y
2.创建pfile
create spfile from pfile=’/tmp/inittest.ora’;
3.创建standby日志
alter database add standby logfile group 4 (’/data/oracle/oradata/orcl/redo9.log’) size 50M;
alter database add standby logfile group 5 (’/data/oracle/oradata/orcl/redo10.log’) size 50M;
alter database add standby logfile group 6 (’/data/oracle/oradata/orcl/redo11.log’) size 50M;
4.修改pfile
test.__db_cache_size=276824064
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__oracle_base=’/data/oracle’#ORACLE_BASE set from environment
test.__pga_aggregate_target=385875968
test.__sga_target=507510784
test.__shared_io_pool_size=0
test.__shared_pool_size=209715200
test._streams_pool_size=0
*.audit_file_dest=’/data/oracle/admin/test/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=’/data/oracle/oradata/test/control01.ctl’,’/data/oracle/flash_recovery_area/test/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘test’
*.db_recovery_file_dest=’/data/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/data/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’
*.memory_target=890241024
*.nls_language=‘AMERICAN’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.service_names=‘TEST’,‘TESTPR’
*.undo_tablespace=‘UNDOTBS1’

*.db_unique_name= ‘testpr’
*.fal_client=‘testpr’
*.fal_server=‘testdg’
*.standby_file_management=‘AUTO’
*.log_archive_config=‘DG_CONFIG=(testpr,testdg)’
*.log_archive_dest_1=‘location=/data/oracle/oradata/test/archivelog’
*.log_archive_dest_2=‘SERVICE=testdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg’
*.log_archive_dest_state_1=‘ENABLE’
.log_archive_dest_state_2=‘ENABLE’

红色字体部分是新加的
5.将主库关闭，从修改后的配置文件启动
6.将密码文件、pfile考别到备库对应目录下
7.对主库进行备份
backup database format=’/data/oracle/oradata/test/archivelog/full%u%p%s.dbf’ include current controlfile for standby;
8.配置tns文件
TESTPR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testpr)
)
)

TESTDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdg)
)
)
使用tnsping 看能不能ping通
二、配置备库
1.修改pfile文件
test.__db_cache_size=276824064
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__oracle_base=’/data/oracle’#ORACLE_BASE set from environment
test.__pga_aggregate_target=385875968
test.__sga_target=507510784
test.__shared_io_pool_size=0
test.__shared_pool_size=209715200
test.__streams_pool_size=0
*.audit_file_dest=’/data/oracle/admin/test/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.0.0’
*.control_files=’/data/oracle/oradata/test/control01.ctl’,’/data/oracle/flash_recovery_area/test/control02.ctl’
*.db_block_size=8192
*.db_domain=’’
*.db_name=‘test’
*.db_recovery_file_dest=’/data/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/data/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’
*.memory_target=890241024
*.nls_language=‘AMERICAN’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.service_names=‘TEST’,‘TESTDG’
*.undo_tablespace=‘UNDOTBS1’

*.db_unique_name= ‘testdg’
*.fal_client=‘testdg’
*.fal_server=‘testpr’
*.standby_file_management=‘AUTO’
*.log_archive_config=‘DG_CONFIG=(testpr,testdg)’
*.log_archive_dest_1=‘location=/data/oracle/oradata/test/archivelog’
*.log_archive_dest_2=‘SERVICE=testpr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testpr’
*.log_archive_dest_state_1=‘ENABLE’
*.log_archive_dest_state_2=‘ENABLE’

注意红色字体部分和主库的不同
2.使用pfile创建spfile，并启动备库到nomount阶段
[oracle@orcldg dbs]$ export ORACLE_SID=test
[oracle@orcldg dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile=’/tmp/inittest.ora’;
SQL> startup nomount;
3.根据配置文件在备库上创建相应的目录
mkdir /data/oracle/admin/test/adump
mkdir /data/oracle/oradata/test/
mkdir /data/oracle/flash_recovery_area/test/
mkdir /data/oracle/oradata/test/archivelog
4.配置tns文件跟主库相同，可将主库上的文件拷贝到备库
TESTPR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testpr)
)
)

TESTDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdg)
)
)
测试能否tnsping通对方
5.检查主库和备库密码文件是否一致
strings orapworcl
主库日志传不到从库，很大可能是网络问题或者主从密码不一样。
cd $ORACLE_HOME/dbs
strings orapwtest
6.将主库备件文件拷贝到备库相应目录下

scp /data/oracle/oradata/test/archivelog/full_0* 192.168.1.7:/data/oracle/oradata/test/archivelog/

7.对拷贝过来备份进行恢复操作
[oracle@orcldg dbs]$ rman target sys/oracle@testpr auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
8.同步完后将备库应用日志
sqlplus / as sysdba
archive log list;
alter database recover managed standby database disconnect from session;
9.在主库上切换日志，查看备库是否同步
sqlplus / as sysdba
alter system switch logfile;
archive log list;

10.备库上取消日志应用，并启动到open状态
alter database recover managed standby database cancel;
alter database open;
11.查看当前备库库mode是否为read only
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

---

PHYSICAL STANDBY READ ONLY
12.备库启用日志应用，并查看数据库mode
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

---

PHYSICAL STANDBY READ ONLY WITH APPLY

参考：1.使用Duplicate target database命令恢复线上oracle datagard备库 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26230597/viewspace-1174294/
2.Oracle11g+CentOS6+DG安装记录 https://www.2cto.com/database/201412/365145.html
3.Oracle 11gR2 DG部署（RMAN方式） https://blog.csdn.net/onlyshenmin/article/details/81069263