数据库

 首页 > 数据库 > MySql > 配置ORACLE 11G ADG

配置ORACLE 11G ADG

分享到：
【字体：大 中 小】
导读：
         摘要：以前装过10g的，没有做笔记，昨天使用duplicate方法装了个11g ADG，过程艰辛，记录下： 一、环境配置 主库IP地址:192.168.233.12824操作系统版本：rhel5.8 64bit数据库版本：11.2.0.1 64bit数据库sid名：orcl数据库名：orcl数据库db_u...

配置ORACLE 11G ADG

以前装过10g的，没有做笔记，昨天使用duplicate方法装了个11g ADG，过程艰辛，记录下：

 

一、环境配置

 

主库

IP地址:192.168.233.128/24

操作系统版本：rhel5.8 64bit

数据库版本：11.2.0.1 64bit

数据库sid名：orcl

数据库名：orcl

数据库db_unique_name:orcl1

主机名：pr

 

物理备库

IP地址:192.168.233.129/24

操作系统版本：rhel5.8 64bit

数据库版本：11.2.0.1 64bit

数据库sid名：orcl

数据库名：orcl

数据库db_unique_name:orcl2

主机名：st

 

二、修改主备库listener.ora，tnsnames.ora文件如下

[oracle@pr admin]$ more listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@pr admin]$ more tnsnames.ora
orcl1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.128)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1)
    )
  )

orcl2=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl2)
    )
  )

 

[oracle@st admin]$ more listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = st)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = orcl2)
 (ORACLE_HOME = /export/11g/product)
 (SID_NAME = orcl)
 )
 )


[oracle@st admin]$ more tnsnames.ora
orcl1=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.128)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl1)
    )
  )

orcl2=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl2)
    )
  )

 


 

三、修改主库、备库的pfile文件，红色部分为新增加的部分。

[oracle@pr dbs]$ more initorcl.ora
orcl.__db_cache_size=142606336
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/export/11g'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=167772160
orcl.__sga_target=251658240
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=92274688
orcl.__streams_pool_size=0
*.audit_file_dest='/export/11g/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/export/11g/oradata/orcl/control01.ctl','/export/11g/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/export/11g/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/export/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=417333248
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='orcl1'
*.fal_client='orcl1'
 *.fal_server='orcl2'
 *.log_archive_config='DG_CONFIG=(orcl1,orcl2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,primary_role) db_unique_name=orcl1'
 *.log_archive_dest_2='SERVICE=orcl2 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl2'
 *.remote_login_passwordfile='EXCLUSIVE'
 *.standby_file_management='AUTO'

 

[oracle@st dbs]$ more initorcl.ora
orcl.__db_cache_size=142606336
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/export/11g'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=167772160
orcl.__sga_target=251658240
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=92274688
orcl.__streams_pool_size=0
*.audit_file_dest='/export/11g/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/export/11g/oradata/orcl/control01.ctl','/export/11g/flash_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/export/11g/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/export/11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=417333248
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.fal_client='orcl2'
 *.fal_server='orcl1'
 *.log_archive_config='DG_CONFIG=(orcl1,orcl2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,primary_role) db_unique_name=orcl2'
 *.log_archive_dest_2='SERVICE=orcl1 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl1'
 *.remote_login_passwordfile='EXCLUSIVE'
 *.standby_file_management='AUTO'
 *.db_unique_name='orcl2'

 

四、在备库上创建相应的目录

如udump/,oradate/

 

五、将备库启动到nomount状态，然后连接主库进行duplicate操作

  1. [oracle@dg2 ~]$ lsnrctl start 
  2. [oracle@dg2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapworclpassword=oracle entries=5
  3. [oracle@dg2 ~]$ sqlplus /nolog
  4. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 22 13:36:53 2012 
  5. Copyright (c) 1982, 2011, Oracle.  All rights reserved. 
  7. SQL> conn /as sysdba 
  8. Connected to an idle instance. 
  9. SQL> create spfile frompfile='/export/11g/product/dbs/initorcl.ora'; 
  10. File created. 
  12. SQL> startup nomount 
  13. ORACLE instance started. 
  15. Total System Global Area  417546240 bytes 
  16. Fixed Size                  2228944 bytes 
  17. Variable Size             285216048 bytes 
  18. Database Buffers          121634816 bytes 
  19. Redo Buffers                8466432 bytes

 

[oracle@st dbs]rman targetsys/oracle@orcl1 auxiliarysys/oracle@orcl2

RMAN> duplicate target database for standby from activedatabase nofilenamecheck;

 

Starting Duplicate Db at 10-NOV-14

using target databasecontrol file instead of recovery catalog

allocated channel:ORA_AUX_DISK_1

channel ORA_AUX_DISK_1:SID=20 device type=DISK

 

contents of MemoryScript:

{

  backup as copy reuse

  targetfile '/export/11g/product/dbs/orapworcl' auxiliary format

 '/export/11g/product/dbs/orapworcl'   ;

}

executing Memory Script

 

Starting backup at 10-NOV-14

allocated channel:ORA_DISK_1

channel ORA_DISK_1:SID=43 device type=DISK

Finished backup at10-NOV-14

 

contents of MemoryScript:

{

  backup as copy current controlfile forstandby auxiliary format '/export/11g/oradata/orcl/control01.ctl';

  restore clone controlfile to '/export/11g/flash_recovery_area/orcl/control02.ctl' from

 '/export/11g/oradata/orcl/control01.ctl';

}

executing Memory Script

 

Starting backup at 10-NOV-14

using channel ORA_DISK_1

channel ORA_DISK_1:starting datafile copy

copying standby controlfile

output filename=/export/11g/product/dbs/snapcf_orcl.f tag=TAG20141110T094600 RECID=5STAMP=863257560

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:03

Finished backup at10-NOV-14

 

Starting restore at 10-NOV-14

using channelORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1:copied control file copy

Finished restore at10-NOV-14

 

contents of MemoryScript:

{

  sql clone 'alter database mount standbydatabase';

}

executing Memory Script

 

sql statement: alterdatabase mount standby database

 

contents of MemoryScript:

{

  set newname for tempfile  1 to

 "/export/11g/oradata/orcl/temp01.dbf";

  switch clone tempfile all;

  set newname for datafile  1 to

 "/export/11g/oradata/orcl/system01.dbf";

  set newname for datafile  2 to

 "/export/11g/oradata/orcl/sysaux01.dbf";

  set newname for datafile  3 to

 "/export/11g/oradata/orcl/undotbs01.dbf";

  set newname for datafile  4 to

 "/export/11g/oradata/orcl/users01.dbf";

  set newname for datafile  5 to

 "/export/11g/oradata/orcl/example01.dbf";

  backup as copy reuse

  datafile 1 auxiliary format

 "/export/11g/oradata/orcl/system01.dbf"   datafile

 2 auxiliary format

 "/export/11g/oradata/orcl/sysaux01.dbf"   datafile

 3 auxiliary format

 "/export/11g/oradata/orcl/undotbs01.dbf"   datafile

 4 auxiliary format

 "/export/11g/oradata/orcl/users01.dbf"   datafile

 5 auxiliary format

 "/export/11g/oradata/orcl/example01.dbf"   ;

  sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SETNEWNAME

 

renamed tempfile 1 to/export/11g/oradata/orcl/temp01.dbf in control file

 

executing command: SETNEWNAME

 

executing command: SETNEWNAME

 

executing command: SETNEWNAME

 

executing command: SETNEWNAME

 

executing command: SETNEWNAME

 

Starting backup at 10-NOV-14

using channel ORA_DISK_1

channel ORA_DISK_1:starting datafile copy

input datafile filenumber=00001 name=/export/11g/oradata/orcl/system01.dbf

output filename=/export/11g/oradata/orcl/system01.dbf tag=TAG20141110T094610

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:56

channel ORA_DISK_1: startingdatafile copy

input datafile filenumber=00002 name=/export/11g/oradata/orcl/sysaux01.dbf

output filename=/export/11g/oradata/orcl/sysaux01.dbf tag=TAG20141110T094610

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1:starting datafile copy

input datafile filenumber=00005 name=/export/11g/oradata/orcl/example01.dbf

output filename=/export/11g/oradata/orcl/example01.dbf tag=TAG20141110T094610

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_1:starting datafile copy

input datafile filenumber=00003 name=/export/11g/oradata/orcl/undotbs01.dbf

output filename=/export/11g/oradata/orcl/undotbs01.dbf tag=TAG20141110T094610

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1:starting datafile copy

input datafile filenumber=00004 name=/export/11g/oradata/orcl/users01.dbf

output filename=/export/11g/oradata/orcl/users01.dbf tag=TAG20141110T094610

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:07

Finished backup at10-NOV-14

 

sql statement: altersystem archive log current

 

contents of MemoryScript:

{

  switch clone datafile all;

}

executing Memory Script

 

datafile 1 switched todatafile copy

input datafile copyRECID=5 STAMP=863257693 file name=/export/11g/oradata/orcl/system01.dbf

datafile 2 switched todatafile copy

input datafile copyRECID=6 STAMP=863257694 file name=/export/11g/oradata/orcl/sysaux01.dbf

datafile 3 switched todatafile copy

input datafile copyRECID=7 STAMP=863257694 file name=/export/11g/oradata/orcl/undotbs01.dbf

datafile 4 switched todatafile copy

input datafile copyRECID=8 STAMP=863257694 file name=/export/11g/oradata/orcl/users01.dbf

datafile 5 switched todatafile copy

input datafile copyRECID=9 STAMP=863257694 file name=/export/11g/oradata/orcl/example01.dbf

Finished Duplicate Db at10-NOV-14

 

RMAN> exit

 

 

Recovery Managercomplete.

 

至此备库创建完成。

 

期间遇到很多的问题，现在总结如下：
1、执行[oracle@st dbs]rman target sys/oracle@orcl1 auxiliarysys/oracle@orcl2时候，提示不能连接到orcl1，但是能ping同pr，原来是防火墙没有开通过

2、tnsname.ora文件中service_name 与连接对端的监听中的service_name相对应。

3、备库中的配置了静态监听，具体原因参见：http://blog.itpub.net/23135684/viewspace-692707

4、执行duplicate target database for standby from active database nofilenamecheck;运行过程中出现了以下错误，是因为缺少文中的第四步，没有在备库上创建相应的目录。

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 10-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

……省略

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/export/11g/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/export/11g/flash_recovery_area/orcl/control02.ctl' from
 '/export/11g/oradata/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 10-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2014 09:24:29
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/10/2014 09:24:29
ORA-17628: Oracle error 19505 returned by remote Oracle server

 

5、执行duplicate target database for standby from active database nofilenamecheck;，运行过程中出现了以下错误，是因为没有使用nofilenamecheck。

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 10-NOV-14
using channel ORA_AUX_DISK_1

…… …… 省略

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2014 09:33:14
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /export/11g/oradata/orcl/example01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /export/11g/oradata/orcl/users01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /export/11g/oradata/orcl/undotbs01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /export/11g/oradata/orcl/sysaux01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /export/11g/oradata/orcl/system01.dbf conflicts with a file used by the target database

 

 

配置ORACLE 11G ADG
分享到：
mysql存储过程及常用函数
mysql存储过程及常用函数一.函数 1.数学函数 CEIL()进一取整 SELECT CEIL(1.2);2 FLOOR()舍一取整 SELECT FLOOR(1.9);9 MOD取余数（取模） SELECT MOD(3,8);3--3对8取模 POWER()幂运算 SELECT POWER(3,2);9 ROUND()四舍五入 SELECT ROUND(123.456,2);123.46 TRUNCTATE数字截取 SELECT TRUNCATE(123.456,2);123.45 ABS()取绝...
多实例下percona-xtrbackup使用（2014-11...
多实例下percona-xtrbackup使用（2014-11-10） 背景说明         percona-xtrabackup是由percona公司开发的备份工具，主要有两个工具，一个是xtrabackup，另一个是innobackupex。其中中innobackupex是对xtrabackup封装，是一个perl脚本。本文操作相对比较简单，通过innobackupex将3306实例的数据进行备份，再恢复到330...
  •         php迷，一个php技术的分享社区，专属您自己的技术摘抄本、收藏夹。
  • 在这里……

Tags

网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱  冀ICP备18013926号-2