linux下搭建datagurad(物理方式)
来源:互联网 发布:杨振宁 国籍 知乎 编辑:程序博客网 时间:2024/04/28 00:24
环境:
OS: Red HatEnterprise 4
DB: Oracle 10204
前期准备:
主库:创建虚拟机,并安装linux、oracle等软件。IP:192.168.139.3 HOST:prod
备库:copy一份主库的虚拟机(启动虚拟机时选择i copy it)。这样能保证两台虚拟机的oracle完全一致。
注意的问题:
1、由于主库和备库的的IP一样,需要修改备库的IP地址和host: IP:192.168.139.4 HOST:standby。可以通过applacations->system settings->net work修改(命令行也可以,这里就不介绍了)。
2、由于备库的mac和主库一样,导致主库和备库之间无法ping通,可以通过linux的操作界面applacations->system settings->net work ,先删除。
3、修改主库和备库的hosts:
[root@prod etc]$vi hosts;
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 prod localhost prod
192.168.139.3 prod
192.168.139.4 standby
上述完成后,就可以开始配置dataguard.
----------------------------------------------------------------------------------------------------------------------------------------------------
1 主库操作:
alterdatabase force logging;
--将主库设为归档模式
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
--创建初始化参数文件
SQL> create pfile from spfile;
--创建standby控制文件,在主库上执行
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS'/home/oracle/control01.ctl';
Database altered.
--创建密码文件
orapwd file=/home/oracle/product/10.2.0/dbs/orapwora10g password=123 entries=10;
--复制控制文件到备库
[oracle@prod ~]$ scp control01.ctl oracle@192.168.139.4:/home/oracle/oradata/ora10g/
然后再复制两个控制文件 control02.ctl control03.ctl
--复制日志文件到备库
[oracle@prod ora10g]$ scp *.log oracle@192.168.139.4:/home/oracle/oradata/ora10g/
--复制数据文件到备库
[oracle@prod ora10g]$ scp *.dbf oracle@192.168.139.4:/home/oracle/oradata/ora10g/
--复制密码文件到备库
[oracle@prod dbs]$ scp orapwora10g oracle@192.168.139.4:/home/oracle/product/10.2.0/dbs/
--复制initora10g.ora文件到备库
[oracle@prod dbs]$ scp initora10g.ora oracle@192.168.139.4:/home/oracle/product/10.2.0/dbs/
3 修改主库的初始化参数文件,增加以下内容
*.DB_UNIQUE_NAME='PRIMARY_1'
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'
*.log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_1'
*.log_archive_dest_2='SERVICE=STANDBY_1 LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=STANDBY_1
*.FAL_CLIENT=PRIMARY_1
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_MAX_PROCESSES=3
4 修改从库$ORACLE_HOME/dbs/initora10g.ora参数,增加以下内容
*.DB_UNIQUE_NAME='STANDBY_1'
*.log_archive_config='DG_CONFIG=(PRIMARY_1,STANDBY_1)'
*.log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY_1'
*.log_archive_dest_2='SERVICE=PRIMARY_1 LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY_1'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=PRIMARY_1
*.FAL_CLIENT=STANDBY_1
*.standby_file_management='AUTO'
*.LOG_ARCHIVE_MAX_PROCESSES=3
上述3、4点有个*.log_archive_dest_1='LOCATION=/home/oracle/archive/ ,所以主库和备库还需要创建目录archive,不然后面启动备库时会报错。
5 配置主库和备库上的 tnsnames.ora ,并测试连通性
PRIMARY_1=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.139.3)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=ora10g)
)
)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.139.4)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=ora10g)
)
)
6主库的 listen.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora10g)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(SID_NAME = ora10g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.139.3)(PORT = 1521))
)
)
6 备库的 listen.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ora10g)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(SID_NAME = ora10g)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.139.4)(PORT = 1521))
)
)
其中第五步和第六步红色部分需要注意,如果这里配置不正确,会报错:ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.
7 启动从库到mount状态
SQL>startup nomount pfile='/home/oracle/product/10.2.0/dbs/initora10g.ora';
SQL> ALTERDATABASE MOUNT STANDBY DATABASE;
--初始化log apply服务
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROMSESSION;
8 打开主库并测试archive log是否传送
SQL>startup;
--主库上执行
SQL> ALTER SYSTEM SWITCH LOGFILE;
--到备库主机上查看归档文件是否传来
[oracle@standby bdump]$ ls /home/oracle/archive
1_10_783823912.dbf 1_17_783823912.dbf 1_20_783823912.dbf 1_3_783823912.dbf 1_6_783823912.dbf 1_9_783823912.dbf
1_15_783823912.dbf 1_18_783823912.dbf 1_21_783823912.dbf 1_4_783823912.dbf 1_7_783823912.dbf
1_16_783823912.dbf 1_19_783823912.dbf 1_22_783823912.dbf 1_5_783823912.dbf 1_8_783823912.dbf
OK,环境搭建成功了。
注意Data Guard 启动顺序:
启动顺序:先standby ,后primary;
关闭顺序:先primary 后standby;
下面这段来自转载其他人的:
三. 一些其他操作
1. 首先查看当前的保护模式 ---primary数据库操作
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
2. 设置新的数据保护模式并重启数据库 --primary数据库操作
当保护模式更改顺序:
maximize protection ---> maximize availability ----> maximize performance
当在把dataguard的保护级别按这上面的顺序减低的时候, 不需要primary库在mount状态,否则primary 必须在mount 状态。
如:
SQL> alter database set standby database to maximize availability;
alter database set standby database to maximize availability
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
报错了,这是因为最大可用性需要先修改日志传送方式为lgwr同步方式,否则,数据库是无法open.
Maximum protection/AVAILABILITY模式必须满足以下条件
Redo Archival Process: LGWR
Network Tranmission mode: SYNC
Disk Write Option: AFFIRM
Standby Redo Logs: Yes
standby database type: Physical Only
SQL> alter system set log_archive_dest_2='service=orcl_st lgwr sync AFFIRM';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 79694068 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;
Database altered.
SQL> alter database open;
Database altered.
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
alter database set standby database to maximize performance;
提示:maximize后可跟{PROTECTION | AVAILABILITY | PERFORMANCE},分别对应最大保护,最高可用性及最高性能。
在最大保护模式下,直接关闭备库是不行的,如果在备库上关闭数据库,会有如下提示:
SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL>
在最大保护模式下,备库是不允许关闭的,此时首先关闭主库,然后备库就可以顺利关闭了。
注意: 主库的保护模式修改之后,备库的模式也会改变,和主库保持一致。
3. 查看日志归档情况
主库进行日志切换:
SQL>Alter system switch logfile;
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;
若不同步,
1) 看log日志, archive是否有丢失
2)可以在备库坐如下操作:
alter database recover managed standby database cancel;
alter database recover managed standby database disconnect from session;
四. 主备库切换
4.1 Switchover
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
1)确认主库和从库间网络连接通畅;
2)确认没有活动的会话连接在数据库中;
3)PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
4)确保STANDBY数据库处于ARCHIVELOG模式;
5)如果设置了REDO应用的延迟,那么将这个设置去掉;
6)确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
主库:
1. 查看switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
附: A:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了。
B.ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database finish;
或者Alter database recover managed standby database finish force;
Alter database recover managed standby database disconnect from session;
2 切换成备库
SQL>Alter database commit to switchover to physical standby with session shutdown;
或者
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
3 启动到mount和应用日志状态
SQL> SHUTDOWN IMMEDIATE
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
4. 查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
备库:
1.查看switchover状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY
附:若不是用此语句切换:ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
补充:若出现:ORA-16139: media recovery required
是因为没有执行:alter database recover managed standby database disconnect from session;
2. 切换成主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> shutdown immediate;
SQL> startup;
SQL> alter system switch logfile;
3. 查看数据库模式
SQL>select dest_name,status,database_mode,recovery_mode,protection_mode from v$archive_dest_status;
SQL>select status,database_mode from v$archive_dest_status;
验证同步:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
78
4.2. Failovers:
FAILOVER切换一般是PRIMARY数据库发生故障后的切换,这种情况是STANDBY数据库发挥其作用的情况。这种切换发生后,可能会造成数据的丢失。而且这个过程不是可逆的,DATA GUARD环境会被破坏。
由于PRIMARY数据库已经无法启动,所以FAILOVER切换所需的条件并不多,只要检查STANDBY是否运行在最大保护模式下,如果是的话,需要将其置为最大性能模式,否则切换到PRIMARY角色也无法启动。
1. 查看是否有日志GAP,没有应用的日志:
SQL> SELECT UNIQUE THREAD#, MAX(SEQUENCE#) OVER(PARTITION BY THREAD#) LAST FROM V$ARCHIVED_LOG;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如果有,则拷贝过来并且注册
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE '路径';
重复查看直到没有应用的日志:
2. 然后停止应用归档:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
3. 下面将STANDBY数据库切换为PRIMARY数据库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
或 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
Database altered.
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN; 或者 shutdown immediate+startup
Database altered.
检查数据库是否已经切换成功:
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
至此,FAILOVER切换完成。这个时候应该马上对新的PRIMARY数据库进行备份。
- linux下搭建datagurad(物理方式)
- 搭建逻辑datagurad
- oracle 12cR1 datagurad搭建文档
- 一台电脑上datagurad搭建过程
- Oracle DataGuard 物理Standby 搭建(下)
- 物理方式下建立oracle data guard
- Linux环境下搭建Jenkins三种方式
- Linux平台下物理DG部署
- linux 下得到磁盘物理扇区大小
- Linux下如何修改MySQL物理目录
- windows环境下搭建DB2 单物理节点 DPF 数据库
- Linux下Wiki搭建
- linux 下 SDL 搭建
- linux下搭建cvs
- linux下服务器搭建
- linux下环境搭建
- Linux下搭建Telnet
- crtmp----linux下搭建
- ids for this class must be manually assigned before calling save()
- IP数据报格式(网络层简介)
- SQL 2005 完全卸载
- POJ 2926 Requirements【最远曼哈顿距离】
- Android NDK学习与环境搭建
- linux下搭建datagurad(物理方式)
- HDU 4671 Backup Plan
- JDK 环境变量配置
- 学生信息管理系统之恍然大悟(2)
- Android不同分辨率的Drawable资源配置
- JAVA 排序算法
- 函数中分配内存的问题
- 详解Java解析XML的四种方法 (很详细,推荐咯)
- ASP.NET动态加载用户控件的实现方法