oel6.2 创建11g physical standby

目录

1 准备工作

1.1 primary打开force logging

1.2 Configure Redo Transport Authentication

   1.2.1 创建密码文件

1.3 配置Standby Redo Log

  1.3.1 Creating and Managing a Standby Redo Log

  1.3.2 Configuring Standby Redo Log Archival

     1.3.2.1 Enable Archiving

     1.3.2.2 Standby Redo Log Archival to a fast recovery area

     1.3.2.3 Standby Redo Log Archival to a Local FIle System Location

1.4 设置primary参数文件

  1.4.1 相关参数说明

1.5 确保数据库处于归档模式

1.6配置最大可用模式

1.7配置主库 TNS

2 创建物理standby

2.1 primary库创建备份

2.2 主库为standby数据库创建控制文件

2.3 standby创建对应目录

2.4 创建standby初始化参数文件
   2.4.1 从primary复制初始化参数文件

   2.4.2 修改standby初始化参数文件中的参数

2.5 配置standby数据库

   2.5.1 创建新的OracleService(windows环境下需要)

   2.5.2 创建密码文件

   2.5.3 创建redo log file(可选)

   2.5.4 创建TNS

   2.5.5 创建standby服务器端的初始化参数文件

   2.5.6 复制primary库的 encryption wallet到standby 库

2.6 启动standby

   2.6.1 Start the physical standby database

   2.6.2 Prepare the Standby Database to Receive Redo Data

   2.6.3 Create an Online Redo Log on the Standby Database

   2.6.4 复制primary数据库到standby 

   2.6.5 启动standby数据库   

   2.6.6 验证

2.7后续操作

 

• 1准备工作

    分别安装主库和备库的oracle软件，主库创建数据库库，备库只安装数据库软件。

说明：

HostNameIPdb_nameService_namedb_unique_nameremarkprimary192.168.1.201primaryprimaryprimaryPrimary DBstandby192.168.1.202primarystandbystandbyStandby DB

 

    不管物理standby还是逻辑standby，其初始创建都是要依赖primary数据库，因为这个准备工作中最重要的一部分，就是对primary数据库的配置。

• 1.1打开Force  Logging模式

SQL> select force_logging from v$database;

将primary数据库置为FORCE LOGGING模式。通过下列语句：

SQL> alter database force logging;

提示：关于FORCE LOGGING

   大家知道有一些DDL语句可以通过指定NOLOGGING子句的方式避免写redo log(目的是提高速度，某些时候确实有效)，指定数据库为FORCE LOGGING模式后，数据库将会记录除临时表空间或临时回滚段外所有的操作而忽略类似NOLOGGING之类的指定参数。如果在执行force logging时有nologging之类的语句在执行，则force logging会等待直到这类语句全部执行。FORCE LOGGING是做为固定参数保存在控制文件中，因此其不受重启之类操作的影响(只执行一次即可)，如果想取消，可以通过alter database no force logging语句关闭强制记录。

• 1.2 Configure Redo Transport Authentication

Data Guard uses Oracle Net sessions to transport redo data and control messages between the members of a Data Guard configuration. These redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file.

SSL is used to authenticate redo transport sessions between two databases if:

■ The databases are members of the same Oracle Internet Directory (OID) enterprise domain and it allows the use of current user database links

■ The  LOG_ARCHIVE_DEST_n , and  FAL_SERVER database initialization parameters that correspond to the databases use Orac le Net connect descriptors configured for SSL

■ Each database has an Oracle wallet or  supported hardware security module that contains a user certificate with a distinguished name (DN) that matches the DN in the OID entry for the database. If the SSL authentication requirements are not met, each member of a Data Guard configuration must be configured to use a remote login password file and every physical standby database in the configuration must have an up-to-date copy of the password file from the primary database.

• 1.2.1创建密码文件

     

    NOTE: 同一个Data Guard配置中所有数据库必须都拥有独立的密码文件，并且必须保证同一个Data Guard配置中所有数据库服务器的SYS用户拥有相同密码以保证redo数据的顺利传输，因为redo传输服务通过认证的网络会话来传输redo数据， 而会话使用包含在密码文件中的SYS用户密码来认证。

    

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwprimary password=oracle 

    如果已经存在, 就不用创建了。 

    缺省情况下，win下口令文件的格式是pwdsid.ora,unix下的格式是orapwSID(大小写敏感)

   NOTE: primary库看是否存在，如果不存在，则创建;standby库需要执行。

 SQL> host ls $ORACLE_HOME/dbs/

 orapwprimary  spfileprimary.ora

• 1.3配置Standby Redo Log

   对于最大保护和最高可用性模式，Standby数据库必须配置standby redo log，并且oracle推荐所有数据库都使用LGWR ASYNC模式传输，当然你现在可能还不知道LGWR ASYNC是什么问题，没关系，你很快就会知道了。

    Oracle 建议你在创建standby时就考虑standby redolog配置的问题。standby redologs与online redologs非常类似，应该说两者只是服务对象不同，其它参数属性甚至操作的命令格式几乎都一样，你在设计standby redologs的时候完全可以借鉴创建online redologs的思路，比如多个文件组啦，每组多个文件冗余之类的。除些之外呢，oracle提供了一些标准的建议如下：

1. 1.  确保standby redo log的文件大小与primary数据库online redo log文件大小相同。
   2. 创建适当的日志组

    一般而言，standby redo日志文件组数要比primary数据库的online redo日志文件组数至少多一个。推荐standby redo日志组数量基于primary数据库的线程数(这里的线程数可以理解为RAC结构中的RAC节点数)。

一个推荐的公式可以做参考：(每线程的日志组数+1)*最大线程数

    例如primary数据库有两个线程，每个线程分配两组日志，则standby日志组数建议为6组，使用这个公式可以降低primary数据库实例LGWR进程锁住的可能性。

提示：逻辑standby数据库有可能需要视工作量增加更多的standby redo log文件(或增加归档进程)，因为逻辑standby需要同时写online redo log文件。

Standby redo log的操作方式与online redo log几乎一模一样，只不过在创建或删除时需要多指定一个standby关键字，例如

• 1.3.1 Creating and Managing a Standby Redo Log

SQL> select count(1) from v$log;

  COUNT(1)

----------

         3

比online redo log多一组。

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/u01/product/oradata/primary/standby_redo01.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/u01/product/oradata/primary/standby_redo02.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/u01/product/oradata/primary/standby_redo03.log') size 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7 ('/u01/product/oradata/primary/standby_redo04.log') size 50M;

Database altered.

 

验证standby redo log文件组是否成功创建

 

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------

         4          0          0 YES UNASSIGNED

         5          0          0 YES UNASSIGNED

         6          0          0 YES UNASSIGNED

         7          0          0 YES UNASSIGNED

删除：

SQL> alter database drop standby logfile group 4;

另外，从可靠性方面考虑，建议在primary数据库也创建standby redologs，这样一旦发生切换，不会影响primary做为standby的正常运行。

• 1.3.2  Configuring Standby Redo Log Archival

• 1.3.2.1 Enable Archiving

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Current log sequence           5

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

设置standby log归档日志方式有以下两种，oracle推荐使用 fast recovery area.

•  1.3.2.2Standby Redo Log Archival to a fast recovery area

LOG_ARCHIVE_DEST_2 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

LOG_ARCHIVE_DEST_STATE_2=ENABLE

• 1.3.2.3 Standby Redo Log Archival to a Local FIle System Location

LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'

LOG_ARCHIVE_DEST_STATE_2=ENABLE

• 1.4 设置primary参数文件

alter system 

在primary主库修改spfile参数文件，参数配置如下：

Example:

DB_NAME=primary

DB_UNIQUE_NAME=primary

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

CONTROL_FILES='/u01/product/oradata/primary/control01.ctl, /u01/product/flash_recovery_area/primary/control02.ctl'

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST   VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 

  DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_2='SERVICE=STANDBY   LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)  DB_UNIQUE_NAME=standby'

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=30

FAL_SERVER=STANDBY

FAL_CLIENT=PRIMARY

#DB_FILE_NAME_CONVERT='primary','standby'

#LOG_FILE_NAME_CONVERT='/arch1/primary/','/arch1/standby/','/arch2/primary/','/arch2/standby/'

STANDBY_FILE_MANAGEMENT=AUTO

注意：

上面列举的这些参数仅只是对于primary/standby两角色可能会相关的参数，还有一些基础性参数比如*_dest,*_size等数据库相关的参数在具体配置时也需要根据实际情况做出适当修改。

 FAL_SERVER=STANDBY

FAL_CLIENT=PRIMARY

这两个参数在standby库中需要修改。

• 1.4.1相关参数说明

参数说明DB_NAMEOn a primary database, specify the name used when the database was created. On a physical standby database, use the  DB_NAME of the primary database.  注意保持同一个Data Guard中所有数据库DB_NAME相同DB_UNIQUE_NAMESpecify a unique name for each database. This name stays with the database and does not change, even if the primary and standby databases reverse roles.LOG_ARCHIVE_CONFIGThe  DG_CONFIG attribute of this parameter must be explicitly set on each database in a Data Guard configuration to enable full Data Guard functionality.
Set  DG_CONFIG to a text string that contains the  DB_UNIQUE_NAME of each database in the configuration, with each  name in this list separated by a comma.罗列同一DG中所有的数据库DB_UNIQUE_NAME,比如 'DG_CONFIG=(primary,standby)'CONTROL_FILESSpecify the path name for the control files on the primary database.
It is recommended that a second copy of the control file is available so an instance can be easily restarted after copying the good control file to the location of the bad control file.LOG_ARCHIVE_DEST_nSpecify where the redo data is to be  archived on the primary and standby systems. In Example 3–1 :
■ LOG_ARCHIVE_DEST_1  archives redo data generated by the primary
database from the local online redo log files to the local archived redo log files in /arch1/primary/.
■ LOG_ARCHIVE_DEST_2  is valid only for the primary role. This destination transmits redo data to the remote physical standby destination  standby.
Note: If a fast recovery area was configured (with the DB_RECOVERY_FILE_DEST initialization parameter) and you have not explicitly configured a local archiving destination with the  LOCATION  attribute, Data Guard automatically uses the  LOG_ARCHIVE_DEST_1  initialization parameter (if it has not already been set) as the default destination for local archiving. Also, see Chapter 15  for complete LOG_ARCHIVE_DEST_n information.LOG_ARCHIVE_DEST_STATE_nSpecify ENABLE to allow redo transport services to transmit redo data to the specified destination.REMOTE_LOGIN_PASSWORDFILEThis parameter must be set to EXCLUSIVE or  SHARED if a remote login password file is used to authenticate administrative users or redo transport sessions.LOG_ARCHIVE_FORMATSpecify the format for the archived redo  log files using a thread (%t), sequence number (%s), and resetlogs ID (%r).LOG_ARCHIVE_MAX_PROCESSES =integerSpecify the maximum number (from 1 to 30) of archiver (ARC n) processes you want Oracle software to invoke initially. The default value is 4.FAL_SERVERSpecify the Oracle Net service name of the FAL server (typically this is the database running in the primary role). When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to
automatically send the missing log files.FAL是Fetch Archived Log的缩写 (指定primary库的 NET service name,相对的，比如primary库的应该指向standby库，standby库指向primary库）

FAL_CLIENTclient库的oralce Net service name,指接收日志的库。DB_FILE_NAME_CONVERT

Specify the path name and filename loca tion of the primary database datafiles followed by the standby loca tion. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is requ ired. Note that this parameter is used only to convert path names for physical standby databases. Multiple pairs of paths may be specified by this parameter.数据文件路径不同时，转换LOG_FILE_NAME_CONVERT

Specify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory
structure where the log files are located on the standby system is different from the primary system, then this parameter is required. Multiple pairs of paths may be specified by this parameter. primary和standby的online redo log的目录，如果一样，可以不用设置。STANDBY_FILE_MANAGEMENTSet to  AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.

 

• 1.5 确保数据库处于归档模式

SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

  

如果当前primary数据库并未处于归档模式，可通过下列命令将数据库置为归档模式：

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

 

•  1.6配置最大可用模式

   默认是最大性能模式

SQL> startup

ORACLE instance started

SQL> alter database set standby database to maximize availability;

Database altered.

SQL> exit

• 1.7配置主库 TNS

[oracle@localhost admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/product/db/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

    )

  )

STANDBY=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

    )

  )

   

• 2 创建物理standby
• 2.1primary库创建备份 

     本例由于采用duplicate active database，可以省略该步骤。
    [oracle@localhost admin]$ rman target /

RMAN> backup database plus archivelog;

 

备份完成后会在闪回区生成备份文件

• 2.2主库为standby数据库创建控制文件

RMAN> backup current controlfile for standby;

Note: You cannot use a single control file for both the primary and  standby databases.

• 2.3standby创建对应目录

           

     包括dump文件目录，闪回区，数据文件目录，可以通过 show parameter dest命令查看

[oracle@localhost product]$ mkdir -p  /u01/product/admin/primary/adump/

[oracle@localhost product]$ mkdir -p  /u01/product/admin/primary/backup

[oracle@localhost product]$ mkdir -p  /u01/product/admin/primary/dpdump

[oracle@localhost product]$ mkdir -p  /u01/product/admin/primary/pfile

[oracle@localhost product]$ mkdir -p /u01/product/flash_recovery_area/primary

[oracle@localhost product]$ mkdir -p /u01/product/oradata/primary

[oracle@standby oradata]$mkdir /u01/product/oradata/primary/backup/

• 2.4创建standby初始化参数文件
• 2.4.1从primary复制初始化参数文件

   [oracle@localhost oradata]$ scp /tmp/initstandby.ora 192.168.1.202:$ORACLE_HOME/dbs

oracle@192.168.1.202's password:
initstandby.ora                                                100% 1399     1.4KB/s   00:00  

• 2.4.2修改standby初始化参数文件中的参数

根据实际情况修改吧，注意primary和standby不同角色的属性配置，注意文件路径。

[oracle@localhost admin]$ vi  $ORACLE_HOME/dbs/initstandby.ora

compatible='11.2.0.0.0'
control_files='/u01/product/oradata/primary/control01.ctl','/u01/product/flash_recovery_area/primary/control02.ctl'
db_name='primary'

db_unique_name=standby

db_recovery_file_dest='/u01/product/flash_recovery_area'
fal_client='STANDBY'
fal_server='PRIMARY'
log_archive_config='DG_CONFIG=(primary,standby)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=standby'
log_archive_dest_2='SERVICE=PRIMARY    LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_max_processes=30
remote_login_passwordfile='EXCLUSIVE' 

• 2.5 配置standby数据库

• 2.5.1 创建新的OracleService(windows环境下需要)

WINNT> oradim –NEW –SID boston –STARTMODE manual

• 2.5.2 创建密码文件

     注意保持密码与primary数据库一致。

    

     直接从primary复制密码文件。

[oracle@localhost trace]$ scp /u01/product/db/dbs/orapwprimary  192.168.1.202:/u01/product/db/dbs/

oracle@192.168.1.202's password:

orapwprimary                            100% 1536     1.5KB/s   00:00

•  2.5.3创建redo log file(可选)

  如果不是采用RMAN的duplicate方式，而是直接复制数据文件的方式，才需要执行这步操作。建议执行。

  如果主库没有添加redo log file，可以先用copy 过来的初始化文件将数据库启动到mount 状态。在创建个spfile，最后添加redo log。

SQL> startup mount pfile='$ORACLE_HOME/dbs/initstandby.ora'

ORACLE instance started.

SQL> create spfile from pfile=' $ORACLE_HOME/dbs/initstandby.ora';

添加新的online redo logs.

SQL>ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/product/oradata/primary/redo01.log') size 50M;

SQL>ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/product/oradata/primary/redo02.log') size 50M;

SQL>ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/product/oradata/primary/redo03.log') size 50M;

添加一个新的Standby Redologs组（注意组号不要与当前存在的Online Redo logs组重复，并为该组指定一个成员：

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/product/oradata/primary/standby_redo01.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/product/oradata/primary/standby_redo02.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/product/oradata/primary/standby_redo03.log') size 50M; 

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/product/oradata/primary/standby_redo04.log') size 50M; 

 

提示，由于从Primary数据库复制文件时并没复制Online Redo logs，因此物理Standby数据库在第一次启动REDO应用时，会在Alert文件中报Online Redo Logfile文件不存在，没关系，物理Standby会自动重建这批文件，同时你也不用担心会丢失数据，Online Redo logs中的数据会以归档文件的形式从Primary端接收。

• 2.5.3 配置监听并启动

[oracle@localhost admin]$ vi listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
 (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/product/db)
      (SID_NAME = primary)
    )
 )
ADR_BASE_LISTENER = /u01/product
 

% lsnrctl stop

% lsnrctl start

或者

$lsnrctl reload

• 2.5.4 创建TNS

    standby的tnsnames.ora， 增加对应的Net Service Name。

  [oracle@localhost admin]$ vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/product/db/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PRIMARY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

    )

  )

STANDBY=

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = primary)

    )

  )

   

         注意关闭防火墙，测试

[oracle@localhost admin]$ tnsping PRIMARY

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-JUL-2013 01:46:25

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))

OK (10 msec)

[oracle@localhost admin]$ tnsping STANDBY

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-JUL-2013 01:45:33

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

/u01/product/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (P

ROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primary)))

OK (40 msec)

• 2.5.5 创建standby服务器端的初始化参数文件

       SQL> create spfile from pfile='/u01/product/db/dbs/initstandby.ora';

       #pfile是传过来的文件。

• 2.5.6 复制primary库的 encryption wallet到standby 库

If the primary database has a database encryption wallet, copy it to the standby  database system and configure the standby database to use this wallet。

Note: The database encryption wallet must be copied from the primary database system to each standby database system whenever the master encryption key is updated.

Encrypted data in a standby database cannot be accessed unless the standby database is configured to point to a database encryption wallet or hardware security module  that contains the current master encryption key from the primary database.

• 2.6 启动standby

• 2.6.1 Start the physical standby database

 

     SQL> STARTUP NOMOUNT;

• 2.6.2Prepare the Standby Database to Receive Redo Data

       归档日志的目录由于使用FRA在2.3步骤,已经创建。

• 2.6.3 Create an Online Redo Log on the Standby Database

Although this step is optional,Oracle recommends that an online redo log be created when a standby database is created. By foll owing this best practice, a standby database will be ready to quickly transition to the primary database role.

The size and number of redo log groups in the online redo log of a standby database should be chosen so that the standby databa se performs well if it transitions to the primary role.

因为第一次启用REDO日志时，会自动创建online redo logfile.

• 2.6.4 复制primary数据库到standby   

 RMAN duplicate active database 不需要复制备份集,不过仅适用于11g，在源端或目标端，standby数据库启动到nomount状态，

      $ rman target sys/oracle@PRIMARY auxiliary sys/oracle@STANDBY

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

Starting Duplicate Db at 27-JUL-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=129 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/product/db/dbs/orapwprimary' auxiliary format

'/u01/product/db/dbs/orapwprimary'   ;

}

executing Memory Script

Starting backup at 27-JUL-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=143 device type=DISK

Finished backup at 27-JUL-13

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/product/oradata/primary/control01.ctl';

   restore clone controlfile to  '/u01/product/flash_recovery_area/primary/control02.ctl' from

'/u01/product/oradata/primary/control01.ctl';

}

executing Memory Script

Starting backup at 27-JUL-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/product/admin/primary/backup/snapcf_primary.f tag=TAG20130727T084507 RECID=1 STAMP=821868308

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

Finished backup at 27-JUL-13

Starting restore at 27-JUL-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 27-JUL-13

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/product/oradata/primary/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

"/u01/product/oradata/primary/system01.dbf";

   set newname for datafile  2 to

"/u01/product/oradata/primary/sysaux01.dbf";

   set newname for datafile  3 to

"/u01/product/oradata/primary/undotbs01.dbf";

   set newname for datafile  4 to

"/u01/product/oradata/primary/users01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

"/u01/product/oradata/primary/system01.dbf"   datafile

2 auxiliary format

"/u01/product/oradata/primary/sysaux01.dbf"   datafile

3 auxiliary format

"/u01/product/oradata/primary/undotbs01.dbf"   datafile

4 auxiliary format

"/u01/product/oradata/primary/users01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/product/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 27-JUL-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/product/oradata/primary/system01.dbf

output file name=/u01/product/oradata/primary/system01.dbf tag=TAG20130727T084516

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/product/oradata/primary/sysaux01.dbf

output file name=/u01/product/oradata/primary/sysaux01.dbf tag=TAG20130727T084516

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/product/oradata/primary/undotbs01.dbf

output file name=/u01/product/oradata/primary/undotbs01.dbf tag=TAG20130727T084516

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/product/oradata/primary/users01.dbf

output file name=/u01/product/oradata/primary/users01.dbf tag=TAG20130727T084516

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

Finished backup at 27-JUL-13

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=821868415 file name=/u01/product/oradata/primary/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=821868415 file name=/u01/product/oradata/primary/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=821868415 file name=/u01/product/oradata/primary/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=821868415 file name=/u01/product/oradata/primary/users01.dbf

Finished Duplicate Db at 27-JUL-13

   这种方式自动复制datafile,standby control file到目标端，并且复制过程中主库还可以正常运行。

    

      注：如果这一步RMAN失败之后，需要先关掉数据库，然后删除spfile，并将库启动到nomount状态再重新开始用RMAN进行Duplicate Database。

• 2.6.5 启动standby数据库

SQL> shutdown immediate;

SQL> startup nomount;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

启动实时应用

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

      提示：disconnect from session子句并非必须，该子句用于指定启动完应用后自动退出到命令操作符前，如果不指定的话，当前   session就会一直停留处理redo应用，如果想做其它操作，就只能新建一个连接。

    如果是不是通过RMAN复制数据库，而是直接copy数据文件，则需要启动到mount状态，然后

• 2.6.6 验证

    首先查看primary:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     27

Next log sequence to archive   29

Current log sequence           29

 standby:

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     26

Next log sequence to archive   0

Current log sequence           29

Current log sequence相同。

• 2.6.6.1  standby database Identify the existing archived redo log files.

   

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

  2  from  V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

        23 27-JUL-13 27-JUL-13

        24 27-JUL-13 27-JUL-13

        25 27-JUL-13 27-JUL-13

        26 27-JUL-13 27-JUL-13

        27 27-JUL-13 27-JUL-13

        28 27-JUL-13 27-JUL-13

6 rows selected.

• 2.6.6.2 primary database Force a log switch to archive the current online redo log file.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

• 2.6.6.3 Verify the new redo data was archived on the standby database.

SQL> /

SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

        23 27-JUL-13 27-JUL-13

        24 27-JUL-13 27-JUL-13

        25 27-JUL-13 27-JUL-13

        26 27-JUL-13 27-JUL-13

        27 27-JUL-13 27-JUL-13

        28 27-JUL-13 27-JUL-13

        29 27-JUL-13 27-JUL-13

7 rows selected.

• 2.6.6.4 Verify that received redo has been applied

QL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG

  2   ORDER BY SEQUENCE#;

SEQUENCE# APPLIED

---------- ---------

        23 YES

        24 YES

        25 YES

        26 YES

        27 YES

        28 YES

        29 IN-MEMORY

7 rows selected.

Note:

The value of the APPLIED column for the most recently  received log file will be either IN-MEMORY or  YES  if that log file has  been applied.

• 2.6.6.5查看保护模式

SQL> select database_role,protection_mode,protection_level,switchover_status from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NOT ALLOWED

• 2.7后续操作
• 2.7.1 Upgrade the data protection mode

The Data Guard configuration is initia lly set up in the maximum performance mode (the default).

-- Maximum Availability.SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY  AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';System altered.SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;Database altered.SQL> select database_role,protection_mode,protection_level,switchover_status from v$database;DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS---------------- -------------------- -------------------- --------------------PRIMARY          MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY TO STANDBY 

-- Maximum Performance.SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';System altered.SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;Database altered.

SQL> select database_role,protection_mode,protection_level,switchover_status from v$database;DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS---------------- -------------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED

-- Maximum Protection.ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';

SHUTDOWN IMMEDIATE;STARTUP MOUNT;ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;ALTER DATABASE OPEN;

SQL> select database_role,protection_mode,protection_level,switchover_status from v$database;DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS---------------- -------------------- -------------------- --------------------PHYSICAL STANDBY MAXIMUM PROTECTION   MAXIMUM PROTECTION   NOT ALLOWED

Note that the data protection mode can be set to MAXIMUM PROTECTION on an open database only if the current data protection mode is MAXIMUM AVAILABILITY and if there is at least one synchronized standby database

• 2.7.2 Enable Flashback Database

Flashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than trad itional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both.

如果primary不开flashback database功能，failover操作后，需要重建primary库。建议primary和standby都开flashback.

• 2.7.3切换到只读模式

SQL> alter database recover managed standby database cancel;

  SQL> alter database open read only;

• 2.7.4切换到同步模式

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount standby database;

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