oracle10g dataguard亲手实验笔记

1、基本配置

   主机名：db10g(aix7.1)、p550b(aix7.1)

   IP地址：db10g：192.168.29.46     SID：orcl

           p550b：192.168.29.159    SID：orcl

2、主库启用FORCE LOGGING

   SQL>ALTER DATABASE FORCE LOGGING;

   SQL>select FORCE_LOGGING from v$database;

    

   启用归档模式

   SQL> startup mount

   SQL> alter database archivelog;

   SQL> alter database open;

    

3、关闭数据库，生成数据库备份文件

   $tar -cvf oradata.tar oradata

4、启动数据库，生成备份控制文件

   SQL> alter database create standby controlfile as '/oradata/stdcotrl.ctl';

5、从主库创建pfile文件

   SQL> create pfile from spfile; 

   在/oracle/db10g/dbs/目录下生成pfile文件：initorcl.ora

6、登陆备库节点,ftp获得数据库文件、备用控制文件及参数文件

   oradata.tar、stdcotrl.ctl、initorcl.ora、密码文件orapworcl 

   解压数据文件

   $ tar -xvf oradata.tar

7、主库及备库都添加standby redo log

   SQL>alter database add standby logfile group 4  size 50m;

   SQL>alter database add standby logfile group 5  size 50m;

   SQL>alter database add standby logfile group 6  size 50m;

   SQL>alter database add standby logfile group 7  size 50m;

   查看日志情况

   sql>select * from v$logfile;

8、备库上创建必要的目录

   $ mkdir -p /oracle/admin/orcl/adump

   $ ls

   $ mkdir bdump cdump udump pfile

9、主库pfile文件initorcl.ora增加以下内容

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

*.db_unique_name='primary'

*.service_names='primary'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/archive 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'

*.log_archive_format='%t_%s_%r.arc'

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.fal_server='standby'

*.fal_client='primary'

*.DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'

*.LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'

*.standby_file_management=auto

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

10、主库listener.ora修改

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

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/db10g)

      (PROGRAM = extproc)

    )

   (SID_DESC =

     (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /oracle/db10g)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

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

增加的内容为：

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

++++++   (SID_DESC =

++++++     (GLOBAL_DBNAME = orcl)

++++++      (ORACLE_HOME = /oracle/db10g)

++++++      (SID_NAME = orcl)

++++++    )

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

5、主库修改tnsnames.ora

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

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

primary =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

standby =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

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

11、备库修改pfile文件，增加以下内容

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

*.control_files='/oradata/orcl/stdcotrl.ctl'

*.db_unique_name='standby'

*.service_names='standby'

*.log_archive_config='dg_config=(primary,standby)'

*.log_archive_dest_1='location=/archive 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'

*.log_archive_format='%t_%s_%r.arc'

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.fal_server='primary'

*.fal_client='standby'

*.DB_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'

*.LOG_FILE_NAME_CONVERT='/oradata/orcl','/oradata/orcl'

*.standby_file_management=auto

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

12、备库listener.ora文件

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

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /oracle/db)

      (PROGRAM = extproc)

    )

   (SID_DESC =

     (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /oracle/db)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

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

13、备库tnsnames.ora文件

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

orcl =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

primary =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

standby =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

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

14、启动备用数据库

   SQL> startup nomount pfile='/oracle/db10g/dbs/initorcl.ora';

   SQL> alter database mount standby database;

   SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

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

   

15、进行数据修改测试

   测试主库产生的归档日志是否能正常传送到归档日志

   在主机上

   SQL>archive log list;

   SQL>alter system archive log current;

   SQL>archive log list;

   

   主库进行日志切换 :

   SQL>Alter system switch logfile;

   

   然后分别查看主库和备库的 /archive 目录下是否产生了同样的归档日志文件。

   select max(sequence#) from v$archived_log;

   select max(sequence#) from v$log_history;

   select group#,sequence#,archived,status from v$log;

   select name,sequence#,applied from v$archived_log;

   select sequence#,applied from v$archived_log;

   

   若不同步，可以在备库 做 如下操作：

   SQL>alter database recover managed standby database cancel;

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

   

   

16、在主库上建表测试

   SQL> create table system.t1 as select * from dba_users;

        insert into system.t1 select * from dba_users connect by rownum <10000； 

   SQL> create table system.t2 as select * from dba_users connect by rownum <100000;

        create table system.t3 as select * from dba_users connect by rownum <130000;

        create table system.t4 as select * from dba_users connect by rownum <140000;

        create table system.t5 as select * from dba_users connect by rownum <150000;

        

   SQL> select count(*) from system.t1;

   SQL> alter system switch logfile;

   

   计算时间：SQL> select 100000/35 from dual;

   

   在备库上以read only打开数据库，执行查询

   SQL> select username from system.t1;

       select username from system.t1

       *

       ERROR at line 1:

       ORA-01219: database not open: queries allowed on fixed tables/views only

       

   SQL> alter database recover managed standby database cancel;      

   SQL> ALTER DATABASE OPEN READ ONLY;   

   SQL> select count(*) from system.t1;

         COUNT(*)

        ----------

        100020

    

    备库回到日志传送模式  

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

    SQL> select status from v$instance;

    STATUS

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

    MOUNTED  

    

17、日志传送状态监控

  

   1）主库 察看当前日志状况

       SQL> select sequence#,status from v$log;

   2）备库 察看 RFS(Remote File Service) 接收日志情况和 MRP应用日志情况

       SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK# FROM V$MANAGED_STANDBY;

   3）察看备库是否和主库同步

       SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

   4）察看备库已经归档的 redo

       SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

   5）察看备库已经应用的 redo

       SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;      

         

18、主库正常切换

   1）在主库端检验数据库可切换状态

      SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

      SWITCHOVER_STATUS

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

      SESSIONS ACTIVE

      

      SWITCHOVER_STATUS:TO STANDBY 表示可以正常切换 .

      如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE, 表示当前有会话处于 ACTIVE状态

      

   2）开始主库正常切换

      如果 SWITCHOVER_STATUS 的值为 TO STANDBY 则 :

       SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

      如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE 则 :

       SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

      成功运行这个命令后，主库被修改为备库

      

   3）重启先前的主库

      SQL> SHUTDOWN IMMEDIATE;

      SQL> STARTUP MOUNT;

      

   4）在备库验证可切换状态

      SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

      SWITCHOVER_STATUS

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

      TO_PRIMARY

      1 row selected

      

   5）将目标备库转换为主库

      如果 SWITCHOVER_STATUS 的值为 TO STANDBY 则 :

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

      

      如果 SWITCHOVER_STATUS 的值为 SESSIONS ACTIVE 则 :

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

      

      成功运行这个命令后，备库被修改为主库

   

   6）重启目标备库

      SQL> SHUTDOWN IMMEDIATE;

      SQL> STARTUP;

      

   7）将先前主库启动日志传送进程

      SQL> alter database recover managed standby database disconnect;

   8）总结 : 这样主库的一次正常切换完成 . 切换后的状态 , 原先的主库变为备库 , 原先的备库变为主库 .