Oracle11g 创建物理备库

 

环境配置

配置	primary database	standby database
IP address	192.168.1.200	192.168.1.201
host name	ghsjdb1	ghsjdb2
oracle sid	ghsjdb	ghsjdb
unique name	ghsjdbprimary	ghsjdbstandby
tnsnames	beijing	shanghai
version	11.2.0.3	11.2.0.3

 

1.Prerare the primary database

 1.1 Enable FORCE LOGGING at the database level

 SQL> ALTER DATABASE FORCE LOGGING;

 

 1.2 Create a password file if required

如果修改了sys用户的密码，替换备库的密码文件

 alter user sys  identified by oracle;

 orapwd file=orapwghsjdb.ora password=oracle force=y ignorecase=y

 主库创建密码文件然后复制到备库

 1.3 Create standby redo logs

 Note that a logical standby database uses standby redo logs (SRLs) for redo received from the primary database, and also writes to online redo logs (ORLs) as it applies changes to the standby database. Thus, logical standby databases often require additional ARCn processes to simultaneously archive SRLs and ORLs. Additionally, because archiving of ORLs takes precedence over archiving of SRLs, a greater number of SRLs may be needed on a logical standby during periods of very  high workload.

(注意逻辑备库使用standby redo logs 接收主库的redo,同时当应用更改的时候也写online redo logs，因此逻辑备库常常需要额外的ARCn 进程同时的归档SRLs和ORLs  另外因为归档ORLs的优先级比SRLs高，因此一个大SRLs是需要的在一个高负载的逻辑备库)

 Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数，例如
 rac 2台机器 每台3组 则 standy 需8组，并且创建时要为每个实例建4组

 alter database add standby logfile  group 4 ('/boot/u01/oracle/oradata/ghsjdb/redo04.log') size 50m;
 alter database add standby logfile  group 5 ('/boot/u01/oracle/oradata/ghsjdb/redo05.log') size 50m;
 alter database add standby logfile  group 6 ('/boot/u01/oracle/oradata/ghsjdb/redo06.log') size 50m;
 alter database add standby logfile  group 7 ('/boot/u01/oracle/oradata/ghsjdb/redo07.log') size 50m;

 

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE ORDER BY GROUP#;

GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /boot/u01/oracle/oradata/ghsjdb/redo01.log
2 ONLINE /boot/u01/oracle/oradata/ghsjdb/redo02.log
3 ONLINE /boot/u01/oracle/oradata/ghsjdb/redo03.log
4 STANDBY /boot/u01/oracle/oradata/ghsjdb/redo04.log
5 STANDBY /boot/u01/oracle/oradata/ghsjdb/redo05.log
6 STANDBY /boot/u01/oracle/oradata/ghsjdb/redo06.log
7 STANDBY /boot/u01/oracle/oradata/ghsjdb/redo07.log

7 rows selected.

 

 1.4 Set initilization parameters

 RAC主库:（注意使用ASM的时候，不要改变db_unique_name参数，否则之后创建的asm文件就会放入至新的db_unique_name目录下面，导致DB_FILE_NAME_CONVERT失效。）
 #add below parameter for standy database

 *.DB_UNIQUE_NAME=ghsjdbprimary
 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ghsjdbprimary,ghsjdbstandby)'
 *.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ghsjdbprimary'
 *.LOG_ARCHIVE_DEST_2='SERVICE=shanghai LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ghsjdbstandby'

 

 #switchover fetch archive log server

 *.FAL_SERVER=shanghai
 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
 *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 *.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
 *.LOG_ARCHIVE_MAX_PROCESSES=10
 *.STANDBY_FILE_MANAGEMENT=AUTO
 *.DB_FILE_NAME_CONVERT='/boot/u01/oracle/oradata/ghsjdb','/boot/u01/oracle/oradata/ghsjdb'
 *.LOG_FILE_NAME_CONVERT='/boot/u01/oracle/oradata/ghsjdb','/boot/u01/oracle/oradata/ghsjdb'

 1.5 Enable archiving

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

 

2.Set parameters on the physical standby database.

SQL> create pfile='/home/oracle/initstandby.ora' from spfile;

File created.

修改如下参数：

 *.DB_UNIQUE_NAME=ghsjdbstandby
 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ghsjdbprimary,ghsjdbstandby)'
 *.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ghsjdbstandby'
 *.LOG_ARCHIVE_DEST_2='SERVICE=beijing LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ghsjdbprimary'

 *.FAL_SERVER=beijing
 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
 *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
 *.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
 *.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
 *.LOG_ARCHIVE_MAX_PROCESSES=10
 *.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='/boot/u01/oracle/oradata/ghsjdb','/boot/u01/oracle/oradata/ghsjdb'
*.LOG_FILE_NAME_CONVERT='/boot/u01/oracle/oradata/ghsjdb','/boot/u01/oracle/oradata/ghsjdb'

 

3.Configure Oracle Net Services.

修改listener.ora ，配置静态监听

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
       (GLOBAL_DBNAME = ghsjdb)
       (ORACLE_HOME = /boot/u01/oracle/product/11.0.2/db_1)
       (SID_NAME = ghsjdb)
     )
      (SID_DESC =
       (GLOBAL_DBNAME = PLSExtProc)
       (ORACLE_HOME = /boot/u01/oracle/product/11.0.2/db_1)
       (SID_NAME = PLSExtProc)
     )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ghsjdb1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /boot/u01/oracle

修改tnsnames.ora

 

BEIJING =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ghsjdb1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GHSJDB)
    )
  )

SHANGHAI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ghsjdb2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = GHSJDB)
    )
  )

 

4.Start standby database instance.

将初始化参数文件拷贝到备库 

scp initstandby.oraghsjdb2:/home/oracle/rmanback/

根据pfile创建spfile

SQL> create spfile from pfile='initstandby.ora';

File created.

 

5.Create a Backup Copy of the Primary Database Datafiles

rman target /

backup database format '/home/oracle/rmanback/level0_%d_%s_%p_%u.bak';
backup archivelog all format '/home/oracle/rmanback/level0archive_%d_%s_%p_%u.bak';
backup device type disk format '/home/oracle/rmanback/standby_%U.ctl'current controlfile for standby;

 

将备份的文件拷贝到备库

scplevel0archive_GHSJDB_3_1_03pn28mm.bak level0_GHSJDB_1_1_01pn28ig.bak level0_GHSJDB_2_1_02pn28lr.bak standby_04pn28n4_1_1.ctl ghsjdb2:/home/oracle/rmanback

 

 

6.Execute the DUPLICATE TARRGET DATABASE FOR STANDBY NOFILENAMECHECK

在主库执行

 

[oracle@ghsjdb1 ~]$ rman target / auxiliarysys/oracle@shanghai

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Nov 8 01:06:09 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: GHSJDB (DBID=1474410688, not open)
connected to auxiliary database: GHSJDB (not mounted)

RMAN>

duplicate target database for standby nofilenamecheck ;

 

7.Start the transport and application of redo

 

SQL>  SELECT SEQUENCE#,REGISTRAR,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# REGISTR APPLIED
---------- ------- ---------
         5 RFS     YES
         6 RFS     YES

SQL>  SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         5 07-NOV-14 08-NOV-14
         6 08-NOV-14 08-NOV-14

 

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;
 
alter database recover managed standby database using current logfile disconnect from session;

 

本文乃原创文章，请勿转载。如须转载请详细标明转载出处