oracle11g 搭建DG

爱被打了一巴掌 2023-06-13 11:25 23阅读 0赞

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