ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Create physical standby database

Create physical standby database

原创 Linux操作系统 作者:janeting 时间:2009-04-03 16:02:09 0 删除 编辑
Create physical standby database

 数据库环境Oracle10gR2.操作系统为Redhat4.5

1.      设置主库listener,tnsnames

Listener:

LISTENER =

  (DESCRIPTION =

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

  )

Tnsnames:

QHDTEST =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = qhdtest)

      (SERVER = DEDICATED)

    )

  )

 

 

TESTSB =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testsb)

      (SERVER = DEDICATED)

    )

  )

2.      设置备库listener,tnsnames

Listener:

LISTENER =

  (DESCRIPTION =

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

  )

 

 

Tnsnames:

TESTSB =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = testsb)

      (SERVER = DEDICATED

    )

  )

 

 

QHDTEST =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = qhdtest)

    )

  )

 

3.      使用tnsping命令检查连通性能

主库端:

[oracle@qhdtest admin]$ tnsping qhdtest

OK (10 msec)

[oracle@qhdtest admin]$ tnsping testsb

OK (0 msec)

备库端:

[oracle@qhdbsb admin]$ tnsping testsb

OK (0 msec)

[oracle@qhdbsb admin]$ tnsping qhdtest

OK (0 msec)

在一台库上使用sqlplus登陆其它库,进一步验证listener配置的正确性.

4. 在主库端mount模式设置主库为归档模式,并查看.

Alter database archivelog;

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive/qhdtest/

Oldest online log sequence     133

Next log sequence to archive   135

Current log sequence           135

5.      在主库open状态下设置主库为force logging 模式

Alter database force logging

SQL> select force_logging from v$database;

FOR

---

YES

Force logging 是做为固定参数保存在控制文件中,因此不受重启操作的影响(只执行一次即可),如果想取消,可以通过alter database no force logging 语句关闭。

Oracle文档参考信息:

FORCE LOGGING
Use this clause to put the database into FORCE LOGGING mode. Oracle will log all changes in the database except for changes in temporary tablespaces and temporary segments. This setting takes precedence over and is independent of any NOLOGGING or FORCE LOGGING settings you specify for individual tablespaces and any NOLOGGING settings you specify for individual database objects.

FORCE LOGGING mode is persistent across instances of the database. That is, if you shut down and restart the database, the database is still in FORCE LOGGING mode. However, if you re-create the control file, Oracle will take the database out of FORCE LOGGING mode unless you specify FORCE LOGGING in the CREATE CONTROLFILE statement.

6.      在主数据库上创建口令文件

orapwd file=$ORACLE_HOME/dbs/orapwqhdtest password=system entries=5

7. 在备库上创建口令文件

orapwd file=$ORACLE_HOME/dbs/orapwtestsb password=system entries=5

在dataguard配置中,每台DB必须使用口令文件.而且每台DB上的sys口令文件内容密码必须相同,才可以确保备库和主库连接的正确性.

注: Linux和Windows中口令文件的格式:

Linux文件名为:orapw$ORACLE_SID

Windows文件名为:orapwd$ORACLE_SID.ora

8.在备库上创建相应目录

[oracle@qhdbsb u10]$ mkdir -p /u10/archive/testsb

[oracle@qhdbsb u10]$ mkdir -p /u02/oradata/testsb

[oracle@qhdbsb u10]$ mkdir -p /u01/app/oracle/admin/testsb/udump

[oracle@qhdbsb u10]$ mkdir -p /u01/app/oracle/admin/testsb/cdump

[oracle@qhdbsb u10]$ mkdir -p /u01/app/oracle/admin/testsb/bdump

[oracle@qhdbsb u10]$ mkdir -p /u01/app/oracle/admin/testsb/adump

[oracle@qhdbsb u10]$ mkdir -p /u01/app/oracle/flash_recovery_area

9.修改主库参数

Db_unique_name=qhdtest

Log_archive_config=’dg_config=(qhdtest,testsb)’

log_archive_dest_1=’location=/archive/qhdtest/ valid_for=(all_logfiles,all_roles) db_unique_name=qhdtest’

log_archive_dest_2=’service=testsb lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=testsb’

动态修改:

SQL> alter system set db_unique_name=qhdtest scope=spfile;

SQL> alter system set log_archive_config='dg_config=(qhdtest,testsb)';

SQL> alter system set log_archive_dest_1='location=/archive/qhdtest valid_for=(all_logfiles,all _roles) db_unique_name=qhdtest';

SQL> alter system set log_archive_dest_2='service=testsb lgwr async valid_for=(online_logfiles, primary_role) db_unique_name=testsb';

一般默认下log_archive_dest_state_n是enable的,故无需更改.

主库切换为备角色时需要的参数:

fal_client=qhdtest

fal_server=testsb

db_file_name_convert=’testsb’,’qhdtest’

log_file_name_convert=’/u01/oradata/testsb/, /u01/oradata/qhdtest/, /u02/oradata/testsb/,

/u02/oradata/qhdtest/’

standby_file_management=auto

 

 

10.创建备库的pfile

生成主库pfile文件,在主库执行以下命令:

create pfile from spfile;

把主库pfile复制到备库上添加并修改pfile内容如下

*.audit_file_dest='/u01/app/oracle/admin/testsb/adump'

*.background_dump_dest='/u01/app/oracle/admin/testsb/bdump'

*.control_files='/u02/oradata/testsb/control01.ctl','/u02/oradata/testsb/control02.ctl','/u02/oradata/testsb/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/testsb/cdump'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.user_dump_dest='/u01/app/oracle/admin/testsb/udump'

以上参数根据建立目录时的路径自行修改.

添加以下备库参数:

 

fal_server=qhdtest

fal_client=testsb

standby_file_management=auto

db_file_name_convert='qhdtest','testsb'

log_file_name_convert=’/u01/oradata/qhdtest/, /u01/oradata/testsb/, /u02/oradata/qhdtest/,

/u02/oradata/testsb/’

备库切换为主库时需要添加的参数:

*.db_unique_name='testsb'

*.log_archive_config='dg_config=(qhdtest,testsb)'

*.log_archive_dest_1='location=/u10/archive/testsb valid_for=(all_logfiles,all_roles) db_unique_name=testsb'

*.log_archive_dest_2='SERVICE=qhdtest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=qhdtest'

log_archive_dest_state_n参数默认下都是enable的.

11.备份主库

Rman>backup database;

备份后在主库打开nfs服务:或者使用scp传输

vi /etc/exports

/u09/      *(rw,no_root_squash)

启动服务  service nfs start

把备份好的rman文件传到备库对应目录.

12.创建备库controlfile

在主库执行如下命令:

alter database create standby controlfile as '/u01/app/control01.ctl';

把生成后的standby controlfile 复制到备援机上pfile指定的位置.

13.恢复备援数据库

把备库启动到mount下执行

Rman>restore database;

恢复完成后查看主库和备库有没有log差异

SQL> select sequence# from v$log_history;
如果有差距,要手动把差的archived log file复制到备库,并手动恢复:
SQL>alter database recover automatic standby database
如果没有差距,就可以直接进入managed recovery mode:
Sql>alter database recover managed standby database disconnect from session;

备库成功创建,查看相关视图:v$managed_standby ,v$archived_log;

到这里就算是创建成功了.

此时归档方式为:

SQL> col dest_name for a30

SQL> select archiver,dest_name from v$archive_dest;

 

ARCHIVER   DEST_NAME

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

ARCH       LOG_ARCHIVE_DEST_1

ARCH       LOG_ARCHIVE_DEST_2

ARCH       LOG_ARCHIVE_DEST_3

ARCH       LOG_ARCHIVE_DEST_4

ARCH       LOG_ARCHIVE_DEST_5

ARCH       LOG_ARCHIVE_DEST_6

ARCH       LOG_ARCHIVE_DEST_7

ARCH       LOG_ARCHIVE_DEST_8

ARCH       LOG_ARCHIVE_DEST_9

ARCH       LOG_ARCHIVE_DEST_10

可以看到我们虽然在主库上的log_archive_dest_2参数设置了日志传递方式为lgwr,但是

从以上查询中看到还是使用的arch的方式传递日志.因為沒有創建standby redo log.

12.创建备库的standby redo log

对于Oracle而言,如果要使用最大可用和最大保护模式,则必须建立standby redo log ,Oracle推荐使用lgwr async传输日志. 建立的standby redo log的大小一定要和主库的redo log大小相一致,Standby redo log 组数要比主库的online redo 日志文件组数至少多一组,Oracle有一个公式做参考(每线程的日志组数+1)*最大线程数 (这里的线程可以理解为rac中的节点)

依此公式可以防止主库的lgwr进程锁住的可能性.

在备库上添加standby logfile

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database add standby logfile group 5 ('/u02/oradata/testsb/redo05a.log','/u03/oradata/testsb/redo5b.log') size 51200k;

Database altered.

SQL> alter database add standby logfile group 6 ('/u02/oradata/testsb/redo06a.log','/u03/oradata/testsb/redo6b.log') size 51200k;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

 

查看当前日志传输方式:

SQL> select archiver,dest_name from v$archive_dest;

 

ARCHIVER   DEST_NAME

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

ARCH       LOG_ARCHIVE_DEST_1

LGWR       LOG_ARCHIVE_DEST_2

ARCH       LOG_ARCHIVE_DEST_3

ARCH       LOG_ARCHIVE_DEST_4

ARCH       LOG_ARCHIVE_DEST_5

ARCH       LOG_ARCHIVE_DEST_6

ARCH       LOG_ARCHIVE_DEST_7

ARCH       LOG_ARCHIVE_DEST_8

ARCH       LOG_ARCHIVE_DEST_9

ARCH       LOG_ARCHIVE_DEST_10

查看alert文件时发现这样一句: Managed Standby Recovery not using Real Time Apply

备库没有实时应用主库的日志.

我们使用如下命令使备库可以实时应用日志:

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

Alert日志显示: Managed Standby Recovery starting Real Time Apply

 

附录:

 

查看当前数据库保护模式:

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

 

DATABASE_ROLE    PROTECTION_LEVEL     PROTECTION_MODE

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

PRIMARY      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

查看当前日志传输方式:

SQL> select archiver,dest_name from v$archive_dest;

 

ARCHIVER   DEST_NAME

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

ARCH       LOG_ARCHIVE_DEST_1

LGWR       LOG_ARCHIVE_DEST_2

ARCH       LOG_ARCHIVE_DEST_3

ARCH       LOG_ARCHIVE_DEST_4

ARCH       LOG_ARCHIVE_DEST_5

ARCH       LOG_ARCHIVE_DEST_6

ARCH       LOG_ARCHIVE_DEST_7

ARCH       LOG_ARCHIVE_DEST_8

ARCH       LOG_ARCHIVE_DEST_9

ARCH       LOG_ARCHIVE_DEST_10

查看当前备库的进程:

SQL> select process,status from v$managed_standby;

 

PROCESS   STATUS

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

ARCH      CLOSING

ARCH      CLOSING

MRP0      APPLYING_LOG

RFS       IDLE

RFS       IDLE

RFS       IDLE

注:此语句可看到对应的client端进程执行状态:

select process,status,client_process from v$managed_standby;

切换到maximize availability 模式:

先关闭主库, SQL> shutdown immediate;

启动到mount模式, SQL> startup mount;

执行如下命令进行切换:

SQL> alter database set standby database to maximize availability;

打开数据库: SQL> alter database open;

执行检查:

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

 

PROTECTION_MODE        PROTECTION_LEVEL     DATABASE_ROLE

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

MAXIMUM  AVAILABILITY    RESYNCHRONIZATION    PRIMARY

切换成功!

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

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

注册时间:2008-09-12

  • 博文量
    22
  • 访问量
    49532