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;