扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
你说的是oracle的dg技术吗？可以使用select open_mode，database_role from v$database语句查询数据库角色
公司主营业务：成都网站设计、网站制作、移动网站开发等业务。帮助企业客户真正实现互联网宣传，提高企业的竞争能力。创新互联是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化，感谢他们对我们的高要求，感谢他们从不同领域给我们带来的挑战，让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联推出金秀免费做网站回馈大家。
oracle的从库有三种角色，物理备库、逻辑备库、快照备库。
物理备库只能处于只读状态，无法执行写的操作
逻辑备库处于读写的状态，但是在备库写入的数据不会同步到主库的
快照备库处于读写状态，类似于给数据库创建一个还原点。
三者之间是可以互相转换的。
1、oracle双机热备叫做DataGuard简称DG，简单来说就一些步骤，每个步骤都有些概念需要了解。
2、10g和11g稍微有点区别，10g备库在热备状态不能open查询，11g支持ActiveDataguard备库可以在热备状态open并select数据：alter database open read only;。
3、DG多配置几次就会轻车熟路。
4、首先数据库必须开启归档模式，然后复制一份主库到备库，这个步骤11g也有更先进的命令，11g支持-Active database duplicate。
备份主库过程可以熟悉一下rman这个工具。
5、DG配置监听listener.ora和initSID.ora
在使用DATA GUARD 时，要求使用强制记录日志模式。日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)
ALTER DATABASE FORCE LOGGING;
6、启动备库
物理standby极少情况下可以以read-write模式打开，某些情况下可以以read-only模式打开，所以默认情况下，加载到mount状态即可。
SQL STARTUP MOUNT;
启动redo应用
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
启动实时应用
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
7、DG主库和备库之间角色切换
DATAGUARD包含三个服务（日志传输、日志应用、角色转换）
8、DATAGUARD的几种保护模式：最大保护maximum protection，最大可用maximum availability，最大性能maximum performance
例如在主库修改保护模式为maximize availability，并打开数据库
ALTER DATABASE SET STANDBY DATABASE TO maximize availability;
你这个属于主、备机切换
1. 察看主库状态
select switchover_status from v$database;
收集主库上的临时表空间的情况，原因是备库激活后临时文件可能丢失，需要手工建上去：
col file_name format a40
select file_name,tablespace_name,bytes/1024/1024 from dba_temp_files;
FILE_NAME TABLESPACE_NAME BYTES/1024/1024
---------------------------------------- -------------------- ---------------
/data/oradata/alihr/temp01.dbf TEMP 2048
2.切换主库到standby
alter database commit to switchover to physical standby;
或：
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
shutdown immediate;
startup nomount;
alter database mount standby database;
3.验证要被切换的standby是否接收到switch to 通知
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
以前的状态就是SESSIONS ACTIVE,现在就变为TO PRIMARY
4.切换物理standby到主用模式，检查redo log是否创建好
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
shutdown immediate;
startup;
5. standby数据库切换成主库后，检查是否需要、对临时表空间增加临时文件:
先检查临时文件是否丢失：
col file_name format a60
select file_name,tablespace_name from dba_temp_files;
把结果前面原主库上的临时文件进行对比，如有丢失则使用如下命令增加：
alter tablespace temp add tempfile '/data/oradata/alihr/temp02.dbf' size 2048M reuse;
正常情况下，如果db_file_name_convert参数设置正确的话，11g会自动建立temp file
6.在新的standby机器上
alter system set log_archive_dest_state_2='defer';
alter database recover managed standby database disconnect from session;
7.检查主备库中fal参数
fal_server服务名是在standby机器的tnsnames中，指向主库，fal_client是在主库上的tnsnames中，指向standby。
fal参数只在standby机器上生效，所以在standby机器上fal_server指向主库，fal_client机器指向备库.
而主库上的fal参数虽然不生效，但为了避免主库切换成standby时，我们还要修改fal参数，所以现在就设置好，
让fal_server指向现在的standby数据库，fal_client指向自己。
8.在新的主库
alter system set log_archive_dest_state_2='enable';
alter system archive log current;
然后到备库上检查备库机器上日志是否正常的传过来了。
9.检查和调整主备库两台机器中的crontab中的数据库备份脚本和删除归档脚本。
=================oracle11g的最大保护和最大可用模式下的切换==================
如果主库是意外宕机的,则直接把备库切换成主库：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
shutdown immediate;
connect / as sysdba;
startup mount;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
shutdown immediate;
connect / as sysdba;
startup mount;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
alter database open;
注意：上面操作中是手工的把数据库重新置成MAXIMIZE AVAILABILITY，否则数据库起来后是最大性能模式。
检查数据库的角色：
select database_role from v$database;
原有主库启动后，如果不是硬盘坏，主库上的数据还存在，则把主库转换成standby就可以了：
主库上：
startup mount;
recover automatic database;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shutdown immediate;
注意千万不要把主库打开了，否则会导致主库的SCN发生变化，无法转换成standby数据库了。
在把原先的主库转化为standby时，有时可能报如下错误：
SQL alter database recover managed standby database finish;
alter database recover managed standby database finish
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/opt/oracle/oradata/oratest/system01.dbf'
这里把到rman中：
list incarnation of database;
reset database to incarnation 1;
recover database;
reset database to incarnation 2;
=========================================================
failover在物理standby的切换
1.检查standby看是否使用了standby log
2.有standby log,执行下面的命令
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
2.1 没有standby log则不执行上面的
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;
3. 切换
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
3.1 如果上面3步骤失败,则
ALTER DATABASE ACTIVATE STANDBY DATABASE;
4. 重启db
shutdown immediate
startup
switchover的方法：
主库和物理standby的切换
1.察看主库状态
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
2.切换
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
3.原主库
shutdown immediate;
startup nomount
alter database mount standby database;
4.验证要被切换的standby是否接收到switch to 通知
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
增加online redo日志
5.切换物理standby到主
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
shutdown immediate;
startup;
6.在新的standby机器（也就是老的主库）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7.在新的主库
alter system archive log current;
========active standby database===========
当:
alter database activate standby database;
原来的主库只能通过闪回转化成standby database，但要求数据库的flashback打开。
在新主库上：
select to_char(standby_became_primary_scn) from v$database;
在旧主库上：
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN standby_became_primary_scn;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
select max(sequence#) from v$log_history where RESETLOGS_TIME=(select max(RESETLOGS_TIME) from v$log_history);
实例恢复：
recover managed standby database disconnect using current logfile;
=================lgwr设置====================================
alter system set log_archive_dest_3 = 'location=/disk3/arch/bopscha reopen=2 MAX_FAILURE=3';
alter system set log_archive_dest_state_3 = alternate;
alter system set log_archive_dest_1 = 'location=/disk2/arch/bopscha alternate=log_archive_dest_3 reopen=60 MAX_FAILURE=5' ;
*.log_archive_dest_2='SERVICE=DTMRT LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DTMRT';
alter database add standby logfile group 4 ('/oracle/u02/ORA10GDG/STANDBYRD01.LOG') size 200M;
select group#,thread#,sequence#,archived,status from v$standby_log;
alter database set standby database to maximize {availability | performance | protection};
select protection_mode from v$database;
ORA-19527:
LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/oract/','/u02/oracle/oradata/oract/'
alter system set log_archive_dest_2='SERVICE=bopsteststb lgwr sync affirm';
select frequency, duration from v$redo_dest_resp_histogram where dest_id=2 and frequency1;
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL ALTER DATABASE OPEN;
SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
alter database convert to snapshot standby;
alter database convert to physical standby;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
alter database commit to switchover to physical standby;
alter database activate standby database finish apply;
alter database recover managed standby database finish;
旧主库转换成standby的步骤：
在新主库上：
select to_char(standby_became_primary_scn) from v$database;
在旧主库上：
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO SCN standby_became_primary_scn;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
SQL select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
2.确认主库处于归档模式
SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/arch
Oldest online log sequence 154
Next log sequence to archive 156
Current log sequence 156
3.创建备库instance
windows平台利用oradim工具创建一个新的instance，unix/linux平台设置新的ORACLE_SID即可
4.准备好主备库的参数文件
主库：
orcl.__db_cache_size=184549376
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=88080384
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl','/u01/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u02/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/orcl/udump'
#################################
db_unique_name=node1
service_names=orcl
log_archive_config='dg_config=(node1,node2)'
log_archive_dest_2='service=dbstandby valid_for=(online_logfiles,primary_role) db_unique_name=node2'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=dbstandby
standby_file_management=AUTO
备库：
orcl.__db_cache_size=184549376
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=88080384
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl','/u01/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u02/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/orcl/udump'
#################################
db_unique_name=node2
service_names=orcl
log_archive_config='dg_config=(node1,node2)'
log_archive_dest_2='service=dbprimary valid_for=(online_logfiles,primary_role) db_unique_name=node1'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=dbprimary
fal_client=dbstandby
standby_file_management=AUTO
5.生成password file
c:/orapwd file=d:/oracle/ora92/DATABASE/PWDtest.ORA password=pass
或者直接将主库上的密码文件copy一份到备库上
6.配置网络
配置主备库的listener.ora,tnsnames.ora。修改完listener.ora后需要重启监听器。
主库：
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s1.gti.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora
dbprimary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
dbstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
备库：
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/oracle/product/10.2.0)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
tnsnames.ora
dbprimary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
dbstandby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
7.使用rman备份主库
[oracle@s1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 19:52:37 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1171867028)
RMAN backup full format='/u02/db_%U' database include current controlfile for standby;
...................
8.归档主库当前日志
SQL alter system archive log current;
System altered.
9.启动备库到nomount
sqlplus "/ as sysdba"
Connected to an idle instance.
SQL startup nomount
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
10.利用rman恢复备库
[oracle@s1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 19:53:21 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1171867028)
RMAN connect auxiliary sys/a@dbstandby
connected to auxiliary database: ORCL (DBID=1171867028, not mount)
RMAN duplicate target database for standby nofilenamecheck;
.............................
如果第8步没有归档当前日志，duplicate时可能出现错误：
RMAN-05507: standby controlfile checkpoint (710256) is more recent than duplicat
ion point in time (709530)
至此，备库创建成功。
11.将备库置于自动恢复状态
SQL conn / as sysdba
Connected.
SQLalter database recover managed standby database disconnect from session;
Media recovery complete.
12.switchover
物理STANDBY的SWITCHOVER切换会把当前的一个物理STANDBY切换为PRIMARY数据库，而PRIMARY数据库且变成物理STNADBY数据库。
一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意：
确认主库和从库间网络连接通畅；
确认没有活动的会话连接在数据库中；
PRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；
确保STANDBY数据库处于ARCHIVELOG模式；
如果设置了REDO应用的延迟，那么将这个设置去掉；
确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。
主库：
[oracle@s1 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 19:55:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL alter database commit to switchover to physical standby;
Database altered.
SQL shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
SQL alter database mount standby database;
Database altered.
SQL alter database recover managed standby database disconnect from session;
Database altered.
备库：
SQL alter database commit to switchover to primary;
Database altered.
SQL shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 96470608 bytes
Database Buffers 184549376 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL
答案来自;bs=asp.net%C1%AC%BD%D3sql%CA%FD%BE%DD%BF%E2sr=z=cl=3f=8tn=baiduwd=Oracle+create+databasect=0
主从数据库的建立一般基于以下三个方面考虑：
1、容灾：备库在异地，主库不存在了，备库可以立即接管，无须恢复时间
2、负载均衡：主库做增删改，备库做查询，这样很多查询业务不占用主库资源
3、数据集中和分发：此种模式主要用于数据从分公司集中到总公司，或从总公司分发到分公司，前提是公司需要同步的数据很少，另外各公司间业务系统不是同一家公司开发的
同步功能主要通过数据库同步软件实现的，象ORACLE的DATAGUARD、QUEST的SHAREPLEX、沃信科技的PAC、ORACLE的GOLDEN GATE、迪思杰的REALSYNC
但是建议可以用沃信科技的产品，因为只有他们一家产品是安装到主备之外的第三台机器上的，不站用主库资源，其他产品必须安装到主库机器上，大家试用起来很不方便
oracle DG 启动和关闭顺序
启动顺序：先启动备库，后启动主库
关闭顺序：先关闭主库，后关闭备库
1、正确打开备库和主库
备库:
SQL STARTUPMOUNT;
SQLALTERDATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
主库:
SQL STARTUPMOUNT;
SQL ALTERDATABASE ARCHIVELOG;
SQL ALTER DATABASEOPEN;
2、正确关闭顺序
主库
SQLSHUTDOWN IMMEDIATE;
备库:
SQL ALTER DATABASE RECOVER MANAGED STANDBYDATABASE CANCEL;
SQLSHUTDOWN IMMEDIATE;
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网