ITPub博客

首页 > Linux操作系统 > Linux操作系统 > standby简单配置

standby简单配置

原创 Linux操作系统 作者:atlantisholic 时间:2011-02-27 16:08:45 0 删除 编辑
主库设置:
1.设置standby的保护模式
 ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
SELECT PROTECTION_MODE FROM V$DATABASE;
PROTECTION:最大保护模式,数据实时同步。
AVAILABILITY :无数据丢失模式,默认行为(PROTECTION)模式,当网络出错时转换为(PERFORMANCE ),数据实时同步。
PERFORMANCE :最大性能模式,可能数据丢失。

 
2.设置强制归档模式,防止redo数据丢失。
ALTER DATABASE FORCE LOGGING;
 
3.将数据库置为归档模式。
ALTER DATABASE ARCHIVELOG;
 
4.为SYS用户创建一致的密码,主库和备库必须相同,用于传输日志。
ORAPWD FILE=...
 
5.在主库与备库添加standby log(在非双向切换的情况下,主库可以不设置),用于减少数据的丢失,在(AVAILABILITY ,PROTECTION)模式下,这些日志是必须的。
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 50M;
 
6.设置参数文件。
 
7.在主库与备库设置tnsnames,分别指向主备库,测试连接是否通畅。
 
8.设置归档日志的路径,查看归档路径的状态。
 
9.备份主库,使用rman备份,可参考如下。
创建备用控制文件:
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/../..' ;
RMAN> run{
2> allocate channel c1 device type disk format 'E:\rman\%U';
3> backup as compressed backupset full database include current controlfile for
standby plus archivelog;
4> }
 
10.将备份的文件拷贝到备机的相同目录下。
 
11.恢复备库。
 
手动恢复:
将备份的STANDBY CONTROLFILE,拷贝到备库参数文件指定的位置,
SQLPLUS "/AS SYSDBA"
--将数据库启动到MOUNT状态。
ALTER DATABASE MOUNT;
--连接备库
RMAN TARGET /
--从刚才RMAN备份的备用控制文件中恢复备用控制文件,也可以直接拷贝主库创建的备用控制文件。
RMAN> restore standby controlfile to 'E:\oracle\product\10.2.0\oradata\standby\control01
.ctl'  from 'E:\RMAN\10M5O4GV_1_1';
--RESTORE数据库文件
RMAN>restore database;
--使用命令恢复备库
SQL> (alter database) recover managed standby database using current logfile disconnect from session;
 
RMAN 自动恢复:
--启动到NOMOUNT状态下。
SQL>STARTUP NOMOUNT;
--连接主备库
RMAN TARGET SYS/INFOGRID@INFOGRID AUXILIARY /
--将数据文件复制到对应的位置(这个过程,如果备份不完整将发生找不到对应的备份文件的错误,数据文件、备用控制文件、归档日志文件)
RMAN>duplicate target database for standby dorecover;
--恢复备用数据库,进入同步状态。
SQL> (alter database) recover managed standby database using current logfile disconnect from session;

 
相关参数文件设置:
1.initprimary.ora
--如果设置双向切换,那么主备需要设置成相同的。
*.compatible='10.2.0.1.0'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.remote_login_passwordfile='EXCLUSIVE'
--9i以后才有的参数,可以自动同步数据文件,在主库上创建的数据文件可以自动在备库上创建。
*.STANDBY_FILE_MANAGEMENT='AUTO'
--标识数据库名称,主库与备库应该相同。
*.db_name='primary'
--标识主库与备库的区别,应该设置成不同的值。
*.DB_UNIQUE_NAME='primary'
--10gR2开始的参数,用于指定dataguard中所有的数据库的名字。
*.log_archive_config='DG_CONFIG=(primary,standby)'
--归档路径的设置,本地归档路径,VALID_FOR:该参数设置这个路径什么时候生效,设置了该参数,那么必须设置对应的DB_UNIQUE_NAME。
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
--远程归档路径设置,LGWR指定通过什么进程传输归档,SYNC指定网络传输模式,AFFIRM指定磁盘操作模式,VALID_FOR指定了传输哪些日志,以及作为什么角色时,这个路径生效。
*.log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
--自动日志同步,主库相对于备库位server,备库相对于主库是client,当前服务器处于主库状态。
*.FAL_CLIENT='primary'
*.FAL_SERVER='standby'
 
 
2.initstandby.ora
*.compatible='10.2.0.1.0'
*.db_name='primary'
*.db_unique_name='standby'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/standby/udump'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*.log_archive_dest_1='SERVICE=primary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
 
 
 
 
//=============================================================

1.      物理DataGuard创建

对应章节

相关步骤

1.1

主库Enable force logging 强制写日志,启用数据库归档

1.2

在主库创建密码文件(dataguard使用sys角色传输redo

1.3

在主库创建备用日志standby log(供switchoverfailover使用)

1.4

设置主库的参数文件initSID.ora,设置主库的tnsnames.ora

1.5

使用 RMAN创建主库的数据文件备份

1.6

在主库创建一份备用控制文件

1.7

复制主库的参数文件,并修改成备用库的参数文件,设置备库的tnsnames.ora

1.8

把主库的备份数据文件,控制文件,归档日志与参数文件拷贝到备用机

1.9

创建备库的密码文件,创建对应的文件夹

1.10

使用备用参数文件将数据库启动到nomount状态

1.11

从备份中恢复控制文件—>数据文件

1.12

在备库创建备用日志standby log,提供更高级的保护,减少数据丢失

1.13

将数据库启动到恢复模式

1.14

测试主库归档路径的可用性。

1.15

查看主库的protection_mode和备库的protection_mode

1.16

测试主库与备库的可切换性(switchover

 

1.1   主库Enable force logging 强制写日志

Alter database force logging;

Alter database archivelog;

 

1.2  主库创建密码文件(dataguard使用sys角色传输redo

a.  主库密码文件的密码要与备库密码文件的密码相同

b.  注意密码文件的命名,在linux下大小写敏感(ORAPWSID.ORA,Windows下(PWDSID.ORA,否则归档可能报没有权限的错误。

c.  Orapwd file=.. password=.. entries=.. force=y

 

1.3  在主库创建备用日志standby log(供switchoverfailover使用)

a.  作为备库:在最大保护模式、最大可用保护模式与read-time applyLGWR ASYNC)时,都需要此文件,前两种模式不发生数据丢失,第三种能将丢失量减小到最少,否则将通过archivelog传输来应用日志,可能丢失较多的数据。

b.  作为主库:这个文件可以不用,但当switchover或者failover时,主库变为备库,这时需要这个文件来完成数据同步。

c.  注意备用日志的大小必须与主库的联机日志大小相同,而且应该比主库的联机日志多一组,建议备用日志组数量为:(maximum number of logfiles for each thread + 1) * maximum number of threads

d.  Alter database add standby logfile group 4(’..’,’..’);

 

 

 

 

1.4  设置主库的参数文件initSID.ora,设置主库的tnsnames.ora

a.  主库参数文件设置:

设置主库参数文件时,应该考虑同时设置主库为standby role时的参数,这样在切换时能够做最少的改动。

主库(primary role)

DB_NAME=primary

DB_UNIQUE_NAME=primary

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

CONTROL_FILES='/arch1/primary/control1.ctl','/arch2/standby/control2.ctl'

LOG_ARCHIVE_DEST_1='LOCATION=/arch1/primary/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC

  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

主库(standby role

      FAL_SERVER=standby
         FAL_CLIENT=primary
         DB_FILE_NAME_CONVERT='standby','primary'
         LOG_FILE_NAME_CONVERT=
         '/arch1/standby/','/arch1/primary/','/arch2/standby/','/arch2/primary/' 
         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 the primary and standby databases reverse roles.

LOG_ARCHIVE_CONFIG

Specify the DG_CONFIG attribute on this parameter to list the DB_UNIQUE_NAME of the primary and standby databases in the Data Guard 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, the LOG_ARCHIVE_CONFIG parameter enables the database to send and receive redo; after a role transition, you may need to specify these settings again using the SEND, NOSEND, RECEIVE, or NORECEIVE keywords.

CONTROL_FILES

Specify the path name for the control files on the primary database. 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 location of the bad control file.

LOG_ARCHIVE_DEST_n

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

  • 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/chicago/.
  • LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination boston.

Note: If a flash 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_10 initialization parameter as the default destination for local archiving. See Section 5.2.3 for more information. Also, see Chapter 14 for complete LOG_ARCHIVE_DEST_n information.

LOG_ARCHIVE_DEST_STATE_n

Specify ENABLE to allow redo transport services to transmit redo data to the specified destination.

REMOTE_LOGIN_PASSWORDFILE

Set the same password for SYS on both the primary and standby databases. The recommended setting is either EXCLUSIVE or SHARED.

LOG_ARCHIVE_FORMAT

Specify the format for the archived redo log files using a thread (%t), sequence number (%s), and resetlogs ID (%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. See Section 5.3.1.2 for more information about ARCn processing.

FAL_SERVER

Specify 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. See Section 5.8.

FAL_CLIENT

Specify the Oracle Net service name of the Chicago database. The FAL server (Boston) copies missing archived redo 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 datafiles followed by the standby location. 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 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 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.

STANDBY_FILE_MANAGEMENT

Set 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  使用 RMAN创建主库的数据文件备份

Rman target /

Run{

Allocate channel c1 device type disk format ‘D:\rman\’;

Backup as compressed backupset database include current standby controlfile for standby plus archivelog;

}

1.6  在主库创建一份备用控制文件

Alter database create standby controlfile as ‘D:\rman\’;

 

1.7  复制主库的参数文件,并修改成备用库的参数文件,设置备库的tnsnames.ora

Create pfile=’D:\rman\initstandby.ora’ from spfile;

备库参数文件:

DB_NAME=primary
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
CONTROL_FILES='/arch1/standby/control1.ctl', '/arch2/standby/control2.ctl'
DB_FILE_NAME_CONVERT='primary','standby'
LOG_FILE_NAME_CONVERT='/arch1/primary/','/arch1/standby/','/arch2/primary/','/arch2/standby/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=primary
FAL_CLIENT=standby

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 primary and 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 location of the bad control file.

DB_FILE_NAME_CONVERT

Specify the path name and filename location of the primary database datafiles followed by the standby location. 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 required.

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.

LOG_ARCHIVE_DEST_n

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

·         LOG_ARCHIVE_DEST_1 archives redo data received from the primary database to archived redo log files in /arch1/boston/.

·         LOG_ARCHIVE_DEST_2 is currently ignored because this destination is valid only for the 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_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_10 initialization parameter as the default destination for local archiving. See Section 5.2.3 for more information. Also, see Chapter 14 for complete information about LOG_ARCHIVE_DEST_n.

FAL_SERVER

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

FAL_CLIENT

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

 

1.8  把主库的备份数据文件,控制文件,归档日志与参数文件拷贝到备用机

a.  将主库的rman备份文件(数据文件备份与日志备份)拷贝到备库,并且路径必须和主库的备份路径一样,因为rman的备份路径是存储在控制文件中的,暂时无法改。

b.  将参数文件拷贝到备库对应的目录下database或者dbs目录下。

 

1.9  创建备库的密码文件,创建对应的文件夹

a.  创建密码文件,备库密码应该与主库相同,否则无法通过验证,传输redo.

b.  创建参数文件中对应的目录,adumpbdumpcdumpudump,创建数据文件所在文件夹primary(恢复时可能会用到,没有可能会出错)

 

1.10              使用备用参数文件将数据库启动到nomount状态

Startup nomount pfile=..

 

1.11     从备份中恢复控制文件—>数据文件

a.  手工恢复控制文件

Restore standby controlfile from ..

Alter database mount;

Restore database;

b.  使用rman自动恢复

Rman target sys/password@primary auxiliary /

Duplicate target database for standby dorecover (nofilenamecheck);

当备库文件名和目录与主库文件名和目录完全相同时,需要使用nofilenamecheck,否则将报错(文件冲突)

1.12              在备库创建备用日志standby log,提供更高级的保护,减少数据丢失

Alter database add standby logfile group 4(‘..’) size 50M;

Alter database add standby logfile group 5(‘..’) size 50M;

 

1.13              将数据库启动到恢复模式

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

 

1.14              测试主库归档路径的可用性。

a.  在主库使用日志切换命令,并且在备库查看日志是否接收到

Alter system switch logfile;

然后查看备库日志状态。

b.  直接在主库查看归档日志路径的状态

Select * from v$archive_dest_status;

 

 

 

1.15              查看主库的protection_mode和备库的protection_mode

Select t.DATABASE_ROLE,t.SWITCHOVER_STATUS,t.PROTECTION_MODE,t.PROTECTION_LEVEL from v$database t

查看数据库角色状态,切换状态,保护模式。

 

1.16              测试主库与备库的可切换性(switchover

a.  主库切换为备库,在正常状态下应该先将主库切换为备库,除非主库实例失败,出现failover状态。

alter database commit to switchover to physical standby with session shutdown;

shutdown immediate

startup mount;

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

b.  将备库切换为主库

alter database commit to switchover to primary with session shutdown;

alter database open;

c.  再将切换后的备库与主库切换回来,到此,物理standby配置成功

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23071790/viewspace-688049/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-08-30

  • 博文量
    130
  • 访问量
    632407