11g DG使用duplicate from active database 创建 物理 standby database

原创 于 2014-06-13 11:36:28 发布 · 1.8k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍Oracle数据库物理备库的搭建过程，包括强制日志记录的开启、重定向认证配置、日志文件的添加与管理等关键步骤，并提供了解决常见错误的方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.1 Enable ForcedLogging
  select FORCE_LOGGING from v$database;
  alter database force logging;

1.2 Configure Redotransport Authentication
  orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SIDpassword=redhat entries=10 force=yes

1.3 Configure thePrimary Database to Receive Redo data

  ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/primary/slog1.rdo') SIZE 50M;
  ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/primary/slog2.rdo') SIZE 50M;
  ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/primary/slog3.rdo') SIZE 50M;
  ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/primary/slog4.rdo') SIZE 50M;
  select * from V$STANDBY_LOG

1.4 创建主库归档目录

[root@primary ~]# mkdir -p /archivelog/primary/
[root@primary ~]# chown -R oracle:oinstall /archivelog/primary/

 

1.5 设置主库初始化参数

  DB_NAME=primary
  DB_UNIQUE_NAME=primary
  LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
  LOG_ARCHIVE_DEST_1='LOCATION=/archivelog/primary/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)       

DB_UNIQUE_NAME=primary'
  LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=standby'
  LOG_ARCHIVE_DEST_STATE_1=ENABLE
  LOG_ARCHIVE_DEST_STATE_2=ENABLE 
  LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
  FAL_SERVER=standby
  STANDBY_FILE_MANAGEMENT=AUTO

1.6 启用主库归档模式

  shutdown immediate;
  startup mount;
  alter database archivelog;
  alter database open;

1.7：创建listener.ora和tnsnames.ora

 [oracle@primary dbs]$cd /u01/oracle/product/11g/network/admin/
 
 [oracle@primaryadmin]$ vi listener.ora
SID_LIST_LISTENER=  
  (SID_LIST =
     (SID_DESC =
      (GLOBAL_DBNAME = primary)
      (ORACLE_HOME = /u01/app/oracle/product/11g)
      (SID_NAME = primary)
     )
   )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
ADR_BASE_LISTENER =/u01/app/oracle

[oracle@primaryadmin]$ vitnsnames.ora
 
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.50)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = primary)
    )
  )
 
STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.51)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVICE_NAME = standby)
    )
  )

 

备库准备：

1：创建好linstener 和 tnsnames

2：在备库上创建相应的目录并复制相应的文件到备库
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/admin/primary/adump
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/admin/primary/dpdump
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/admin/primary/pfile
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/oradata/primary
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/fast_recovery_area/primary
 
 [oracle@standbyoracle]$ mkdir -p $ORACLE_BASE/diag/rdbms/primary/primary

3：创建 pfile 填写dbname

DB_NAME=primary
  DB_UNIQUE_NAME=standby
  LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
  LOG_ARCHIVE_DEST_1='LOCATION=/archivelog/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)       

DB_UNIQUE_NAME=standby'
  LOG_ARCHIVE_DEST_2='SERVICE=primary ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=primary'
  LOG_ARCHIVE_DEST_STATE_1=ENABLE
  LOG_ARCHIVE_DEST_STATE_2=ENABLE 
  LOG_ARCHIVE_FORMAT=s%t_%s_%r.arc
  FAL_SERVER=primary
  STANDBY_FILE_MANAGEMENT=AUTO

4：创建密码文件或者从主库复制过来改下名字

5：启动备用数据库到nomount状态

 

开始用RMAN恢复

rman target sys/redhat@primary auxiliarysys/redhat@standby

duplicate target database for standby from active database dorecover nofilenamecheck;

此时有可能会报如下错误

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 06/13/2014 01:44:17
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 06/13/2014 01:44:17
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby 

database
ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: '/u01/app/oracle/product/11g/dbs/cntrlstandby.dbf'


解决方法是修改备库参数文件

compatible='11.2.0.4.0'

然后重新duplicate

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

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

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11g/dbs/orapwprimary' auxiliary format 
 '/u01/app/oracle/product/11g/dbs/orapwstandby'   ;
}
executing Memory Script

Starting backup at 13-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
Finished backup at 13-JUN-14

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  

'/u01/app/oracle/product/11g/dbs/cntrlstandby.dbf';
}
executing Memory Script

Starting backup at 13-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11g/dbs/snapcf_primary.f tag=TAG20140613T014925 

RECID=6 STAMP=850096165
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-JUN-14

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

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/primary/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/primary/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/primary/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/primary/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/primary/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/primary/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/primary/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/primary/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/primary/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/primary/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 13-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/primary/system01.dbf
output file name=/u01/app/oracle/oradata/primary/system01.dbf tag=TAG20140613T014934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/primary/sysaux01.dbf
output file name=/u01/app/oracle/oradata/primary/sysaux01.dbf tag=TAG20140613T014934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/primary/undotbs01.dbf
output file name=/u01/app/oracle/oradata/primary/undotbs01.dbf tag=TAG20140613T014934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/primary/users01.dbf
output file name=/u01/app/oracle/oradata/primary/users01.dbf tag=TAG20140613T014934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-JUN-14

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/archivelog/primary/1_21_849845139.arc" auxiliary format 
 "/archivelog/standby/s1_21_849845139.arc"   archivelog like 
 "/archivelog/primary/1_22_849845139.arc" auxiliary format 
 "/archivelog/standby/s1_22_849845139.arc"   ;
   catalog clone archivelog  "/archivelog/standby/s1_21_849845139.arc";
   catalog clone archivelog  "/archivelog/standby/s1_22_849845139.arc";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 13-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=21 RECID=8 STAMP=850096175
output file name=/archivelog/standby/s1_21_849845139.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=22 RECID=9 STAMP=850096353
output file name=/archivelog/standby/s1_22_849845139.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 13-JUN-14

cataloged archived log
archived log file name=/archivelog/standby/s1_21_849845139.arc RECID=1 STAMP=850096352

cataloged archived log
archived log file name=/archivelog/standby/s1_22_849845139.arc RECID=2 STAMP=850096352

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=850096352 file 

name=/u01/app/oracle/oradata/primary/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=850096352 file 

name=/u01/app/oracle/oradata/primary/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=850096352 file 

name=/u01/app/oracle/oradata/primary/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=850096352 file 

name=/u01/app/oracle/oradata/primary/users01.dbf

contents of Memory Script:
{
   set until scn  331258;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 13-JUN-14
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 21 is already on disk as file 

/archivelog/standby/s1_21_849845139.arc
archived log for thread 1 with sequence 22 is already on disk as file 

/archivelog/standby/s1_22_849845139.arc
archived log file name=/archivelog/standby/s1_21_849845139.arc thread=1 sequence=21
archived log file name=/archivelog/standby/s1_22_849845139.arc thread=1 sequence=22
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-JUN-14
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/primary/redo01.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/primary/redo02.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/primary/redo03.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 0: '/u01/app/oracle/oradata/primary/slog1.rdo'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 0: '/u01/app/oracle/oradata/primary/slog2.rdo'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 6 thread 0: '/u01/app/oracle/oradata/primary/slog3.rdo'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/primary/slog4.rdo'

RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 13-JUN-14

 

上面有很多警告关于日志文件的 当你到备库的oradata目录下发现 没有日志文件生成：

有两种解决方式：

1：在duplicate之前 主库和备库的参数文件里添加 

LOG_FILE_NAME_CONVERT=/u01/app/oracle/oradata/primary,/u01/app/oracle/oradata/primary

大家看到了 就算目录一样也要添加。。

 

2：在备库上操作重建备库的online redo log

SQL>create spfile from pfile;  
File created.  
SQL>shutdown immediate 
SQL>startup

alter system set standby_file_management=manual;

alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/primary' scope=spfile;
shutdown immediate; 
startup; 

alter database clear logfile group 1;

alter database clear logfile group 2;

alter database clear logfile group 3;

alter system reset LOG_FILE_NAME_CONVERT;

shutdown immediate; 
startup;

alter system set standby_file_management=auto;

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值