oracle dataguard 实验(单数据库环境+物理standby)

本次oracle dataguard

环境：

操作系统：windows 2003 server

数据库：oracle 10g 10.2.0.1

ORACLE_HOME：D:/oracle/product/10.2.0/db_1

archive_dest：D:/archivelog

rman_dest：d:/rman_backup

机器：1台

主库名称：learn

备库名称：learndg

实验步骤：修改好tnsnames、listener、pfile文件，通过rman的duplicate还原备库

 

一、主库上操作：

alter database force logging;

alter database archivelog;

添加standby日志组，比本身的redo日志组多出一组（有一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数 线程数可以理解为rac结构中的rac节点数）

alter database add standby logfile group 4 ('D:/oracle/product/10.2.0/oradata/learn/STANDBYRDO01.LOG') szie 50M;

create pfile='d:/oracle/init_learn.ora' from spfile;
复制D:/oracle/product/10.2.0/db_1/database目录下的PWDlearn.ora为PWDlearndg.ora

 

修改create出来的pfile修改后如下,红字部分为修改的地方（里面没有的则添加）

learn.__db_cache_size=276824064
learn.__java_pool_size=4194304
learn.__large_pool_size=4194304
learn.__shared_pool_size=109051904
learn.__streams_pool_size=0
*.audit_file_dest='d:/oracle/product/10.2.0/admin/learn/adump'
*.background_dump_dest='d:/oracle/product/10.2.0/admin/learn/bdump'
*.compatible='10.2.0.1.0'
*.control_files='d:/oracle/product/10.2.0/oradata/learn//control01.ctl','d:/oracle/product/10.2.0/oradata/learn//control02.ctl','d:/oracle/product/10.2.0/oradata/learn//control03.ctl'
*.core_dump_dest='d:/oracle/product/10.2.0/admin/learn/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='D:/oracle/product/10.2.0/oradata/learndg','D:/oracle/product/10.2.0/oradata/learn'
*.db_name='learn'
*.db_recovery_file_dest='d:/oracle/product/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=536870912
*.DB_UNIQUE_NAME='learn'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=learnXDB)'
*.FAL_CLIENT='learn'
*.FAL_SERVER='learndg'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(learn,learndg)'
*.log_archive_dest_1='LOCATION=D:/archive_log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=learn'
*.LOG_ARCHIVE_DEST_2='SERVICE=learndg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=learndg'
*.LOG_FILE_NAME_CONVERT='D:/oracle/product/10.2.0/oradata/learndg','D:/oracle/product/10.2.0/oradata/learn'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=134217728
*.processes=50
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=60
*.sga_max_size=402653184
*.sga_target=402653184
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:/oracle/product/10.2.0/admin/learn/udump'

 

修改完成后

shutdown immediate

create spfile from pfile='d:/oracle/init_learn.ora';

startup

 

二、备库操作

复制刚才的'd:/oracle/init_learn.ora'为'd:/oracle/init_learndg.ora'

 

修改init_learndg.ora文件如下，红字部分为修改的地方

learn.__db_cache_size=276824064
learn.__java_pool_size=4194304
learn.__large_pool_size=4194304
learn.__shared_pool_size=109051904
learn.__streams_pool_size=0
*.audit_file_dest='d:/oracle/product/10.2.0/admin/learndg/adump'
*.background_dump_dest='d:/oracle/product/10.2.0/admin/learndg/bdump'
*.compatible='10.2.0.1.0'
*.control_files='d:/oracle/product/10.2.0/oradata/learndg//standbyctl01.ctl','d:/oracle/product/10.2.0/oradata/learndg//standbyctl02.ctl','d:/oracle/product/10.2.0/oradata/learndg//standbyctl03.ctl'
*.core_dump_dest='d:/oracle/product/10.2.0/admin/learndg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='D:/oracle/product/10.2.0/oradata/learn','D:/oracle/product/10.2.0/oradata/learndg'
*.LOG_FILE_NAME_CONVERT='D:/oracle/product/10.2.0/oradata/learn','D:/oracle/product/10.2.0/oradata/learndg'
*.db_name='learn'
*.db_recovery_file_dest='d:/oracle/product/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=536870912
*.DB_UNIQUE_NAME='learndg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=learnXDB)'
*.FAL_CLIENT='learndg'
*.FAL_SERVER='learn'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(learn,learndg)'
*.log_archive_dest_1='LOCATION=D:/archive_log/learndg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=learndg'
*.LOG_ARCHIVE_DEST_2='SERVICE=learn LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=learn'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=134217728
*.processes=50
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.sessions=60
*.sga_max_size=402653184
*.sga_target=402653184
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:/oracle/product/10.2.0/admin/learndg/udump'

在D:/oracle/product/10.2.0/admin目录下建立learndg文件夹，在learndg文件夹中建立adump bdump cdump dpump pfile udump文件夹

 

CMD下执行

oradim -new -sid LEARNDG

set ORACLE_SID=learndg

sqlplus /@learndg as sysdba

create spfile from pfile='d:/oracle/init_learn.ora';

startup nomount

quit

 

三、修改tnsnames、listener文件

 

tnsnames文件增加服务名learn和learndg，修改后如下

# tnsnames.ora Network Configuration File: d:/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LEARNDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.53.26)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = learndg)
    )
  )

LEARN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.53.26)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = learn)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

 

修改listener.ora文件，修改好的listener.ora如下

# listener.ora Network Configuration File: d:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
     (GLOBAL_NAME = LEARNDG)
     (ORACLE_HOME = d:/oracle/product/10.2.0/db_1)
     (SID_NAME = learndg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IT0001)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

 

修改完成后

CMD命令下

lsnrctl stop

lsnrctl start

 

四、通过rman备份数据库，并使用duplicate同步出备库

rman nocatalog target /

run{

allocate channel d1 type disk format 'd:/rman_backup/%U';

allocate channel d2 type disk format 'd:/rman_backup/%U';

backup database skip inaccessible filesperset 10 plus archivelog filesperset 20 include current controlfile for standby delete all input;

release channel d1;

release channel d2;

}

 

备份完成后

connect auxiliary /@learndg

run{

duplicate target database for standby dorecover

db_file_name_convert=('D:/oracle/product/10.2.0/oradata/learn','D:/oracle/product/10.2.0/oradata/learndg');

}

 

五、rman恢复完成后

在备库上执行 alter database recover managed standby database disconnect from session;

 

在主库上执行 alter system switch logfile;

 

在主库和备库上执行

select max(sequence#) from v$archived_log;来验证是否同步成功

 

总结：整个实验过程历时4天，中途遇到如下问题，导致花费很多时间

1.监听问题、连接备库时提示

   ORA-12154: TNS: 无法解析指定的连接标识符，原因是learndg由于无法启动注册监听，所以需要配置静态监听。

 

2.rman的duplicate问题，最初不知道duplicate for standby命令，直接通过duplicate命令同步备库，结果导致主库生成的standby controlfile和数据库文件不一致（数据库ID不同）

 

3.rman duplicate时在执行内存脚本这一步卡住，然后提示

   RMAN-06136: 来自辅助数据库的 ORACLE 错误: ORA-01013: 用户请求取消当前的操作，原因是做duplicate时备库上还有用户登入，退出登入后错误解决

 

4.rman duplicate时提示

   RMAN-03002: failure of Duplicate Db command at 12/14/2007 17:07:13
   RMAN-03015: error occurred in stored script Memory Script
   RMAN-06026: some targets not found - aborting restore
   RMAN-06024: no backup or copy of the controlfile found to restore

原因是我之前的脚本是

run{

allocate channel d1 type disk format 'd:/rman_backup/%U';

allocate channel d2 type disk format 'd:/rman_backup/%U';

backup database skip inaccessible filesperset 10 plus archivelog filesperset 20 delete all input;

backup current controlfile for standby;

release channel d1;

release channel d2;

}

在自己的电脑上没问题，公司电脑上出现这个错

将脚本修改为如下后，问题解决

run{

allocate channel d1 type disk format 'd:/rman_backup/%U';

allocate channel d2 type disk format 'd:/rman_backup/%U';

backup database skip inaccessible filesperset 10 plus archivelog filesperset 20 include current controlfile for standby delete all input;

release channel d1;

release channel d2;

}