dataguard之创建物理备库

dryu999

　　创建前的设置
　　1使用下面的语句将主库设置在force logging模式
　　alter database force logging;
　　2如果没有口令文件，创建一个口令文件。在dataguard中的每一个数据库都必须有一个口令文件。sys用户的密码在每个系统中必须是相同的用来日志数据的传输。
　　3配置一个standby redo log
　　4对最大保护模式和最大可用性模式需要standby redo log，对所有数据库建议使用lgwr async传输模式。dg可以恢复 应用更多的redo数据在standby redo log中，而不是仅仅在归档redo日志中。
　　执行下面的步骤来配置standby redo log
　　a 确保日志的尺寸和主库及备库的日志大小一致，standby redo log文件大小必须要和当前主库的在线redo log文件大小一致。比如，如果主数据库使用2个在线日志组，日志文件大小是200K,那么standby redo log大小也应该是200K
　　b 确定standby redo日志组的个数
　　standby redo 日志组最少应该比主库上的在线日志组多1个，建议的standb redo日志组的设置使用下面的公式来计算：
　　(maximum number of logfiles for each thread + 1) *maximum number of threads
　　使用这个等式减少主库的lgwr进程因为standby redo log不能再备库上分配导致的阻塞。比如主库上每个thread有2个日志文件，有2个thread，那么在备库上应该需要6个standby 日志文件组
　　c创建standby redo日志组，下面是2个例子
　　ALTER DATABASE ADD STANDBY LOGFILE THREAD 5 ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
　　ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
　　d 验证standby redo文件组被创建了，为了验证被正确的添加了，在主库上执行下日志切换，然后在备库上查看v$standby_log或v$logfile视图，例如
　　SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
5配置主库初始化参数，在主库上，你定义初始化参数来控制redo传输服务，当主库转换成备库的时候，你需要配置额外的参数来控制redo的接受和应用。
　　下面的例子显示了主库上的初始化参数，这个例子显示了主库位于芝加哥，物理备库位于boston的dg的配置，当芝加哥的库在主库角色或是备库角色的时候，这个配置都是有效的。
DatabaseDB_UNIQUE_NAMEOracle Net Service NamePrimarychicagochicagoPhysical standbybostonboston　　主库初始化参数
　　DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
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
　　下面是主库上的额外的standby 角色的初始化参数，这些参数在主库转成备库的时候生效
　　FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

　　下面是参数的解释
　　

Parameter

Recommended Setting

DB_NAME

Specify an 8-character name. Use the same name for all standby databases.

DB_UNIQUE_NAME

Specify a unique name for each database. This name stays with the database and does not change, even if theprimary and standby databases reverse roles.

LOG_ARCHIVE_CONFIG

Specify theDG_CONFIG attribute on this parameter to list theDB_UNIQUE_NAME of the primary and standby databases in the DataGuard configuration; this enables the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode. By default, theLOG_ARCHIVE_CONFIG parameter enables the database to send andreceive redo; after a role transition, you may need to specify these settings again using theSEND,NOSEND,RECEIVE, orNORECEIVE keywords.

CONTROL_FILES

Specify the path name for the control files on the primary database.Example 3-3 shows how to do this for two control files. 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 locationof the bad control file.

LOG_ARCHIVE_DEST_n

Specify where the redo data is to be archived on the primary and standby systems. InExample 3-3:

• LOG_ARCHIVE_DEST_1 archives redo data generated by the primary database from the localonline redo log files to the local archived redo log files in /arch1/chicago/.
  
• LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmitsredo data to the remote physical standby destination boston.
  

Note:If a flash recovery area was configured (with the DB_RECOVERY_FILE_DESTinitialization parameter) and you have not explicitly configured a local archiving destination with theLOCATION attribute, Data Guard automatically uses theLOG_ARCHIVE_DEST_10 initialization parameter as the default destinationfor local archiving. See Section 5.2.3 for more information. Also, seeChapter 14 for complete LOG_ARCHIVE_DEST_ninformation.

LOG_ARCHIVE_DEST_STATE_n

SpecifyENABLE to allow redo transport services to transmit redo datato the specified destination.

REMOTE_LOGIN_PASSWORDFILE

Set the same password forSYS on both the primary and standby databases. The recommendedsetting is either EXCLUSIVE orSHARED.

LOG_ARCHIVE_FORMAT

Specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogsID (%r). See Section 5.7.1 for another example.

LOG_ARCHIVE_MAX_PROCESSES=integer

Specify the maximum number (from 1 to 30) of archiver (ARCn)processes you want Oracle software to invoke initially. The default value is 4. SeeSection 5.3.1.2 for more information aboutARCnprocessing.

FAL_SERVER

Specify the Oracle Net service name of the FAL server (typically this is the database running in the primaryrole). 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. SeeSection 5.8.

FAL_CLIENT

Specify the Oracle Net service name of the Chicago database. The FAL server (Boston) copies missing archivedredo log files to the Chicago standby database. See Section 5.8.

DB_FILE_NAME_CONVERT

Specify the path name and filename location of the primary database data files followed by the standby location.This parameter converts the path names of the primary database data files to the standby data file path names. If the standby database is on the same system as the primary database or if the directory structure where the data files are located on the standbysite is different from the primary site, then this parameter is required. 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 parameterconverts 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 isdifferent from the primary system, then this parameter is required. Multiple pairs of paths may be specified by this parameter.

STANDBY_FILE_MANAGEMENT

Set toAUTO so when data files are added to or dropped from the primarydatabase, corresponding changes are made automatically to the standby database.

　　

6启用归档，使用下面的语句将主库设置在归档模式，并启用自动归档
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
开始创建一个物理备库
1创建主库的数据文件的一个备份
2为备库创建一个控制文件
如果在备份的过程中关闭了主库，那么将主库启动到mount模式，使用下面的命令创建备库的控制文件
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
创建完后，启动主库
3为备库准备初始化参数文件
a拷贝主库的参数文件到备库
b在备库上设置初始化参数
下面标红的是与主库不同的参数
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT=
'/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston
不同的参数如下：db_unique_name control_files db_file_name_convert log_file_name_convert log_archive_dest_1 log_archive_dest_2 fal_server fal_client
下面是不同参数的解释

Parameter

Recommended Setting

DB_UNIQUE_NAME

Specify a unique name for this database. This name stays with the database and does not change even if the primaryand standby databases reverse roles.

CONTROL_FILES

Specify the path name for the control files on the standby database.Example 3-5 shows how to do this for two control files. 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 locationof the bad control file.

DB_FILE_NAME_CONVERT

Specify the path name and filename location of the primary database data files followed by the standby location.This parameter converts the path names of the primary database data files to the standby data file path names. If the standby database is on the same system as the primary database or if the directory structure where the data files are located on the standbysite is different from the primary site, then this parameter is required.

LOG_FILE_NAME_CONVERT

Specify the location of the primary database online redo log files followed by the standby location. This parameterconverts 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 isdifferent from the primary system, then this parameter is required.

LOG_ARCHIVE_DEST_n

Specify where the redo data is to be archived. InExample 3-5:

• LOG_ARCHIVE_DEST_1 archives redo data received from the primary database to archivedredo log files in /arch1/boston/.
  
• LOG_ARCHIVE_DEST_2 is currently ignored because this destination is valid only forthe primary role. If a switchover occurs and this instance becomes the primary database, then it will transmit redo data to the remote Chicago destination.
  

Note:If a flash recovery area was configured (with the DB_RECOVERY_FILE_DESTinitialization parameter) and you have not explicitly configured a local archiving destination with theLOCATION attribute, Data Guard automatically uses theLOG_ARCHIVE_DEST_10 initialization parameter as the default destinationfor local archiving. See Section 5.2.3 for more information. Also, seeChapter 14 for complete information aboutLOG_ARCHIVE_DEST_n.

FAL_SERVER

Specify the Oracle Net service name of the FAL server (typically this is the database running in the primaryrole). When the Boston database is running in the standby role, it uses the Chicago database as the FAL server from which to fetch (request) missing archived redo log files if Chicago is unable to automatically send the missing log files. SeeSection 5.8.

FAL_CLIENT

Specify the Oracle Net service name of the Boston database. The FAL server (Chicago) copies missing archivedredo log files to the Boston standby database. See Section 5.8.

4拷贝主库上的文件到备库上
数据文件的备份文件，备库控制文件，初始化参数文件
5设置备库的环境
创建口令文件，设置net service，创建spfile
6启动物理备库
a使用下面的命令来启动备库
startup mount;
b开始应用redo日志
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
disconnect from session选项使redo apply在后台会话中运行
c对物理备库测试归档选项
只有在发生了日志切换的时候，redo数据的传输才会发生，在主库上执行alter system switch logfile来马上强制执行日志切换。
7验证物理备库正常运行
a验证存在的归档redo日志文件，在备库上查询v$archived_log视图
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
b在主库上执行日志切换
alter system switch logfile;
c在备库上验证新的redo数据被归档了，再次查询v$archived_log视图
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
d验证redo数据被应用了
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;