Oracle dataguard 配置

Oracle dataguard 配置  系统 linux

说明：主服务器 ora10g01（192.168.1.252）

Standby服务器 orac10g02（192.168.1.253）

两台机器都安装相同版本的数据库

主库创建数据库

物理备库不创建数据库

1.       主库备库必须要是归档模式，主库和备库监听全部起来lsnrctl start

2.       Archive log list

3.       设置主库强制归档

Conn  /as  sysdba

startup

Alter database force logging;

4.       修改主库的tnsnames

加上

ORA10G01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora10g)

    )

  )

 

 

 

 

ORA10G02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora10g)

    )

  )

5. 修改备库的tnsnames

加上

ORA10G01 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora10g)

    )

  )

 

 

 

 

ORA10G02 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora10g)

    )

  )

 

6.生成备用库的控制文件

Alter database create standby controlfile as ‘/u01/app/oracle/standby.ctl’

7.修改主库初始化参数文件

create pfile from spfile

加上

log_archive_dest_1='LOCATION=/u01/app/oracle/archive'

log_archive_dest_state_1=enable

log_archive_dest_2='SERVICE=ora10g01'

log_archive_dest_state_2=enable

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

log_archive_max_processes=2

fal_server=ora10g01

fal_client=ora10g02

db_unique_name=ora10g01

 

8.shutdown immediate

 

9.vi /etc/hosts

加上

192.168.1.252           ora10g01

192.168.1.253           ora10g02

 

 

10.主库的数据文件，控制文件，日志文件拷到备库上

scp -r ora10g ora10g02 :/u01/app/oracle/oadata

scp-r /u01/app/oracle/standby.ctl  ora10g02 :/u01/app/oracle

scp –r initora10g.ora ora10g02 :/u01/oracle/oradata

11.要确保密码一样的也可以重建密码文件

Orapwd file= /u01/app/oralce/product/10.2.0/db_1/dbs/orapwora10g

12.备库初始化参数文件

加上

log_archive_dest_1='LOCATION=/u01/app/oracle/archive'

log_archive_dest_state_1=enable

log_archive_dest_2='SERVICE=ora10g02'

log_archive_dest_state_2=enable

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

log_archive_max_processes=2

fal_server=ora10g02

fal_client=ora10g01

db_unique_name=ora10g02

13.检查下归档的文件夹是否创建

14.删除备库的控制文件

Mv standby.ctl to control01.ctl

Cp control01.ctl control02.ctl

Cp control01.ctl control03.ctl

 

14.启动主库

Conn /as sysdba

Create spfile from pfile

Startup

Select name,database_role from v$database;

 

15.启动备库

Conn /as sysdba

Create pfile from pfile

Startup nomount

Alter database mount standby database;

Select name,database_role from v$database;

Alter database recover managed standby database disconnect from session;

16.可以建表测试

主库create table a (id1 number,id2 number);

Insert into a values(1,1);

Commit;

Alter system switch logfile;

Alter system switch logfile;

Alter system switch logfile;

 

备库  select  sequence#,first_name,next_time from v$archive_log orader by sequence#;

Select sequence#,applied from v$archived_log order by sequence#;

 

Alter database recover managed standby database cancel;

Alter database open read only;

Select  *　from  a;

Alter database recover managed standby database disconnect from session; 启动redo应用

17.主备库切换

更改主数据库为备用状态

Alter database commit to switchover to physical standby;

关闭主数据库

Shutdown immediate;

在备用模式启动主数据库

Startup nomount;

alter database mount standby database;

select name,open_mode,protection_mode,database_role from v$database;

alter database recover managed stanby database disconnect from session;

 

打开备用数据库

Conn / sysdba

Alter database commit to switchover primary;

Shutdown immediate;

Startup

Alter system switch logfile;

 

18.failover

在standby 上

Alter database recover managed standby database finish;

Alter database recover managed standby database skip standby logfile;

Alter database commit to switchover to primary;

Shutdown immediate;

Startup;

 

 

19. Alter database recover managed standby database cancel;

Alter database open read only;

如果直接切换到主库

alter database commit to switchover to primary;会报错

ORA-16139: media recovery required

必须执行

alter database recover managed stanby database disconnect from session;

alter database commit to switchover to primary;