ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DataGuard:Physical Standby Create Sample

DataGuard:Physical Standby Create Sample

原创 Linux操作系统 作者:oracle_kai 时间:2009-05-14 12:15:22 0 删除 编辑

DataGuard:Physical Standby Create Sample

 

环境:winxp

            oracle10.2.0.1

            db_name=db1

            primary db_unique_name='db1'

              standby db_unique_name='db2'

 

primary database  配置

 

1.配置primary数据库为force logging 归档模式

    SQL> startup mount

    SQL> alter database archivelog;

    SQL> archive log start

    SQL>alter system set log_archive_dest_1='location=c:\arch\db1\primary   valid_for=(online_logfiles,all_roles)';

    SQL> alter system set log_archive_format='DB1_ARC%S_%R.%T' SCOPE=SPFILE;

    SQL> ALTER DATABASE FORCE LOGGING;

    SQL> SHUTDOWN IMMEDIATE

    SQL> select LOG_MODE,DB_UNIQUE_NAME,FORCE_LOGGING from v$database;

 

              LOG_MODE     DB_UNIQUE_NAME                 FOR

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

              ARCHIVELOG   db1                            YES

 

 2.primary 库需要编辑和增加的参数

 

 配置 log_archive_dest_2 参数,用于指定如何传输redo standby,并确认log_archive_dest_state_x是否为enable,如不是,改为enable

 

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

 

配置log_archive_config,用于指定Data Guard 环境中所有dbdb_unique_name,redo 传输的需要用到

    SQL> alter system set log_archive_config='dg_config=(db1,db2)';

 

    配置db_unique_name,默认和db_name一致.

 

 

3.$oracle_base\admin\db2\建立bdumpcdumpudump 目录,primary 库上执行sql,通过修改primary

    pfile文件,来生成standby的初始pfile

 

      SQL>  CREATE PFILE='C:\INITDB2.ORA' FROM SPFILE;

 

      编辑initdb2.ora文件,把db1替换为db2(db_name参数保持不变,因为Data Guard环境中需要primarystandby数据的db_name都一样),并增加下面的参数

*.fal_server='db1'

*.fal_client='db2'    *.db_file_name_convert='C:\oracle\product\10.2.0\oradata\db2','C:\oracle\product\10.2.0\oradata\db1' *.log_file_name_convert='C:\oracle\product\10.2.0\oradata\db2','C:\oracle\product\10.2.0\oradata\db1'

*.standby_file_management='auto'

*.log_archive_dest_2='location=c:\arch\db2\standby valid_for=(standby_logfiles,standby_roles)'

*.log_archive_dest_state_2='enable'

*.db_unique_name='db2'

 

  3.   产生standby 控制文件,primary 库上执行

         SQL> alter database create standby controlfile as 'C:\oracle\product\10.2.0\oradata\db2\CONTROL01.CTL',

         并按前面参数文件定义的control_files ,拷贝相应份数的控制文件(用操作系统命令)

 

  4.   建立standby密码文件

         H:\>orapwd file=C:\oracle\product\10.2.0\db_1\database\pwddb2.ora password=password

         建立standby的密码文件,密码和primary保持一致,以便于做redo 同步的验证

 

   5.  建立oracle服务,windwos环境下需要此步骤,unix中不需要做

        H:\>oradim -new -sid db2 -startmode manual

 

   6.   拷贝primary数据库文件到standby数据库对应目录,primary 库上执行sql

 

          SQL> select  NAME from v$datafile;

           SQL>select member from v$logfile;

          SQL> select name from v$tempfile;

 

          shutdown primary,copy 上面列出的这些文件到standby 数据库的oradata目录下

 

   7. 配置primarystandby listener tnsnames ,确保tnsping可以ping通对方

    

   8.  H:\>oradim -startup -sid db2   启动服务,并利用pfile启动standbymount状态,对standby同样设置归档及force logging

 

   9.  应用redo 服务,此时报错,日志信息如下

        SQL>alter database recover managed standby database disconnect from session

 

        alter log 错误

       Errors in file c:\oracle\product\10.2.0\admin\db2\bdump\db2_dbw0_2504.trc:

        ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

        ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\SYSTEM01.DBF'

        ORA-27086: unable to lock file - already in use

        OSD-00002: 附加错误信息

        O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.

 

 看似控制文件读取的datafile的路径有问题,查看在standby参数文件中的参数设置

*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\db2','C:\oracle\product\10.2.0\oradata\db1'

*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\db2','C:\oracle\product\10.2.0\oradata\db1'

 

修改下面的参数

*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\db1','C:\oracle\product\10.2.0\oradata\db2'

*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\db1','C:\oracle\product\10.2.0\oradata\db2'

 

问题解决由此可以得知db_file_name_convert/log_file_name_convert  primary 路径名/文件名 , standby 路径名/文件名位置不可颠倒,且只需要在standby端设置即可,oracle在定位standby文件的时候自动应用上面的参数去重命名文件

 

错误 ORA-16004 ,初始的standby只有在接收到primary redo,并应用后才能open

SQL>  alter database recover managed standby database cancel;

      Database altered.

SQL> alter database open read only;

     alter database open read only

     ERROR at line 1:

     ORA-16004: backup database requires recovery

     ORA-01152: file 1 was not restored from a sufficiently old backup

     ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\SYSTEM01.DBF'

 

10.  primary  switch logfile  standby 并没有接收到redo,此时primary alert log错误提示

 

        Errors in file c:\oracle\product\10.2.0\admin\db1\bdump\db1_arc0_2740.trc:

        ORA-16047: DGID mismatch between destination setting and standby

        Wed Jan 07 11:45:48 2009

        PING[ARC0]: Heartbeat failed to connect to standby 'db2'. Error is 16047.

 

解决办法:standby log_archive_config  没有设定,把它设定和primary一致即可(log_archive_config   该参数需要在所有primarystandby机器中设置)

 

11.测试

 

primary 库执行

SQL> select name from v$datafile;

 

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\EXAMPLE01.DBF

 

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB1\test.dbf' size 10m;

SQL> create user test identified by test default tablespace test;

SQL> grant connect ,resource to test;

SQL> connect test/test

SQL> create table a as select 'My Data Guard!!!' col1  from dual;

SQL> connect /as sysdba

SQL> alter system switch logfile;

 

standby 执行

 

取消redo应用

 

SQL>  alter database recover managed standby database cancel;

只读打开standby

SQL>  alter database open read only;

查询datafile文件,增加了C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\TEST.DBF

SQL> select name from v$datafile;

 

NAME

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

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\EXAMPLE01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\TEST.DBF

 

6 rows selected.

 

查询test 用户下的表a

SQL> select * from test.a;

 

COL1

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

My Data Guard!!!

 

standbyalert 日志中同样可以看到应用archive log recover的信息,截录如下:

RFS[4]: Archived Log: 'C:\ARCH\DB2\STANDBY\DB2_ARC00009_0675443184.001'

Wed Jan 07 12:16:35 2009

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[5]: Assigned to RFS process 2124

RFS[5]: Identified database type as 'physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

RFS[5]: No standby redo logfiles created

Wed Jan 07 12:16:38 2009

Media Recovery Log C:\ARCH\DB2\STANDBY\DB2_ARC00009_0675443184.001

Recovery created file C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\TEST.DBF

Successfully added datafile 6 to media recovery

Datafile #6: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\TEST.DBF'

 

physical standby 创建成功

 

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

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

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    174530