Centos 4.4下10G Dataguard配置 (下)

检查配置是否成功：

在主库rac1上创建一个新表

SQL> create table t as select * from dba_users;

Table created.

SQL>  alter system switch logfile;

System altered.

 在辅库rac3上检查

SQL> select username from t;

select username from t

                     *

ERROR at line 1:

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

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> select username from t;

 USERNAME

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

MGMT_VIEW

SYS

------

 21 rows selected.            成功

 SQL> create tablespace wyl datafile '/home/oracle/oradata/rac/wyl.dbf' size 200M;

Tablespace created.

SQL> alter system switch logfile;

   System altered.

SQL> select name from v$datafile;

NAME

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

/home/oracle/oradata/rac/wyl.dbf

Rac3

SQL> select name from v$datafile;

NAME

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

/home/oracle/oradata/rac/users01.dbf

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

*

ERROR at line 1:ORA-16136: Managed Standby Recovery not active

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

Database altered.

 SQL> select name from v$datafile;

 NAME

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

/home/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

 SQL> select name from v$datafile;

 NAME

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

/home/oracle/oradata/rac/users01.dbf

/home/oracle/oradata/rac/wyl.dbf

  9.主辅数据库的切换（停止主数据库,启用备用数据库）

修改rac1(原来做primary)的参数文件initrac.ora

增加

*.standby_archive_dest='/home/oracle/oradata/standbyarch'

*.fal_server='rac3'

*.fal_client='rac1'

*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")

*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archive","/home/oracle/oradata/archive")

*.STANDBY_FILE_MANAGEMENT='AUTO'

删除  log_archive_dest_2参数

修改rac3主机(原来做standby)的参数文件initrac.ora(先备份)

增加

*.log_archive_dest_2='service=primary mandatory reopen=60'

*.log_archive_dest_state_2='ENABLE'

删除

*.STANDBY_ARCHIVE_DEST=/home/oracle/oradata/archivelog

*.fal_server='standby'

*.fal_client='primary'

*.DB_FILE_NAME_CONVERT=("/home/oracle/oradata/primary","/home/oracle/oradata/oracle")

*.LOG_FILE_NAME_CONVERT=("/home/oracle/oradata/archivelog","/home/oracle/oradata/archivelog")

*.STANDBY_FILE_MANAGEMENT=AUTO

 在rac1主机上执行

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

Database altered.

察看rac1主机上的后台日志

 

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

在rac1主机上以备用模式（standby）启动

SQL> create spfile from pfile;

SQL> startup nomount;

ORACLE instance started.

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

SQL> show parameter standby_file_management;

 

NAME                  TYPE-------------------------------- ----------------------VALUE

standby_file_management         string

 SQL> alter database mount standby database;

Database altered.

在rac3主机上执行以下命令，切换为primary

[oracle@rac3]$ sqlplus "/ as sysdba"

 SQL> alter database commit to switchover to primary with session shutdown ;

Database altered.

SQL> shutdown immediate;

     ORA-01507: database not mounted

ORACLE instance shut down

SQL>create spfile from pfile;

SQL> startup;

        ……

Database mounted.

Database opened.

SQL> select SEQUENCE#,GROUP#,STATUS from v$log;

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

 

 SEQUENCE#     GROUP# STATUS

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

        72          1 INACTIVE

        73          2 INACTIVE

        74          3 CURRENT

 SQL> alter system switch logfile;

       System altered.

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

 

 SEQUENCE#     GROUP# STATUS

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

        75          1 CURRENT

        73          2 INACTIVE

        74          3 ACTIVE

在rac3主机上执行

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

在rac3主机上观察日志应用情况

 10.现在可做一个测试，在rac3主机上进行数据修改（rac3主机现在做primary）

  SQL> create table t as select * from dba_users;

Table created.

SQL> alter system switch logfile;     

System altered.

 

在从库上(rac1主机上)以read only打开数据库，执行查询

SQL> select username from t;

select username from t

 *

ERROR at line 1:

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

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> select username from t;

USERNAME

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

--------

WMSYS

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

switchover过程： 

12.把数据库再次切换回到主节点

先将rac3此时的initoracle.ora恢复为以前是standby时的参数。

在主节点（rac3主机上）

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

 SQL> create spfile from pfile;

File created.

 SQL> startup nomount;

ORACLE instance started.

SQL> alter database mount standby database;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

 在备用节点（在rac1主机上）

先将primary此时的initoracle.ora恢复为以前是primary时的参数。

SQL> alter database commit to switchover to primary;

Database altered.

SQL> alter database commit to switchover to primary;

      Database altered.

 SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> create spfile from pfile;

File created.

SQL> startup;

ORACLE instance started.

 Database mounted.

Database opened.