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 环境中所有db的db_unique_name,redo 传输的需要用到

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

    配置db_unique_name，默认和db_name一致.

3.在$oracle_base\admin\db2\建立bdump，cdump，udump 目录,在primary 库上执行sql,通过修改primary的

    pfile文件，来生成standby的初始pfile。

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

      编辑initdb2.ora文件，把db1替换为db2(db_name参数保持不变，因为Data Guard环境中需要primary和standby数据的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. 配置primary，standby 的listener 和tnsnames ，确保tnsping可以ping通对方

   8.  H:\>oradim -startup -sid db2   启动服务，并利用pfile启动standby到mount状态，对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   该参数需要在所有primary和standby机器中设置)

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!!!

从standby的alert 日志中同样可以看到应用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 创建成功