主库准备工作
Host IP DB_NAME DB_UNIQUE_NAME Net Service Name(网络服务名)
主库192.168.10.253
STARSMS PRIMARY primary
备库192.168.10.240
STARSMS STANDBY standby
保护模式:默认最大性能模式,由于尼日利亚网络状况不是很好采用【最高可用性】
SQL > alter database set standby database
to maximize protection; --最大保护
SQL > alter database set standby database
to maximize availability; --最高可用性
SQL > alter database set standby database
to maximize performance; --最高性能
DataGuard 共有三种数据保护模式:
最大保护(Maximum protection):
这种模式能够确保绝无数据丢失。要实现这一步当然是有代价的,它要求所有的事务在提交前其redo不仅被写入到本地的online redo log,还要同时提交到standby数据库的standby redo log,并确认redo数据至少在一个standby数据库可用(如果有多个的话),然后才会在primary数据库上提交。如果出现了什么故障导致standby数据库不可用的话,primary数据库会被shutdown。
最高性能(Maximum performance):
这种模式提供在不影响primary数据库性能前提下最高级别的数据保护策略。事务可以随时提交,当前primary数据库的redo数据也需要至少写入一个standby数据库,不过这种写入可以是不同步的。如果网络条件理想的话,这种模式能够提供类似最高可用性的数据保护而仅对primary数据库有轻微的性能影响。
最高可用性(Maximum availability):采用模式
这种模式提供在不影响primary数据库可用前提下最高级别的数据保护策略。其实现方式与最大保护模式类似,也是要求所有事务在提交前必须保障redo数据至少在一个standby数据库可用,不过与之不同的是,如果出现故障导入无法同时写入standby数据库redo log,primary数据库并不会shutdown,而是自动转为最高性能模式,等standby数据库恢复正常之后,它又会再自动转换成最高可用性模式。
最大保护及最高可用性需要至少一个standby数据库redo数据被同步写入。三种模式都需要指定LOG_ARCHIVE_DEST_n初始化参数。
注意DataGuard启动顺序:
启动顺序: 先standby ,后primary;
关闭顺序: 先primary ,后standby;
1.1、检查数据库是否支持Data Guard(企业版才支持),是否归档模式,Enable force logging
$ sqlplus '/as sysdba'
SQL> select * from v$option where
parameter = 'Managed Standby';
确认主库处于归档模式
SQL> archive log list (先检查是否归档模式,不是则修改)
startup mount
alter database archivelog;
alter database open;
将primary数据库置为FORCE LOGGING模式
SQL> alter database force logging; (强制产生日志)
1.2、如果主库没有密码文件则建立密码文件,从而可以OS验证的方式登陆
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID
password=system entries=5
1.3、配置standby redolog(最佳性能模式可以忽略,如果将来变成备库且要转为其它两种模式则要建立)
SQL> alter database add standby logfile
group 5 ('/Starol_redolog/StarbossRelog51.ora',
'/Starol_redolog/StarbossRelog52.ora',
'/Starol_redolog/StarbossRelog53.ora') size 50m,
group 6 ('/Starol_redolog/StarbossRelog61.ora',
'/Starol_redolog/StarbossRelog62.ora',
'/Starol_redolog/StarbossRelog63.ora') size 50m,
group 7 ('/Starol_redolog/StarbossRelog71.ora',
'/Starol_redolog/StarbossRelog72.ora',
'/Starol_redolog/StarbossRelog73.ora') size 50m,
group 8 ('/Starol_redolog/StarbossRelog81.ora',
'/Starol_redolog/StarbossRelog82.ora',
'/Starol_redolog/StarbossRelog83.ora') size 50m,
group 9 ('/Starol_redolog/StarbossRelog91.ora',
'/Starol_redolog/StarbossRelog92.ora',
'/Starol_redolog/StarbossRelog93.ora') size 50m;
standby redolog的组数参考公式:(online redolog组数+ 1) *数据库线程数;单机线程数为1,RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
1.4、设置主库初始化参数
$ sqlplus '/as sysdba'
SQL>create pfile='/opt/ora10/product/10.2.0/dbs/initstarsms.ora'
from spfile; (备份参数文件)
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
scope=both;
(启动db接受或发送redo data,包括所有库的db_unique_name)
SQL>alter system set LOG_ARCHIVE_DEST_1='LOCATION=
/Starhis_archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
scope=both; ---(主库归档目的地)
---(当该库充当主库角色时,设置物理备库redo data的传输目的地)
SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=5
scope=both;
---(最大ARCn进程数)
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE
scope=both;
---(允许redo传输服务传输数据到目的地,默认是enable)
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE
scope=both; (同上)
--以下是主库切换为备库,充当备库角色时的一些参数设置,如果不打算做数据库切换就不用设置了
SQL> alter system set FAL_SERVER=db_standby
scope=both;
----(配置网络服务名,假如转换为备库角色时,从这里获取丢失的归档文件)
SQL> alter system set FAL_CLIENT=db_primary
scope=both;
----(配置网络服务名,fal_server拷贝丢失的归档文件到这里)
SQL> alter system set
DB_FILE_NAME_CONVERT='STANDBY','PRIMARY' scope=both;
----(前为切换后的主库路径,后为切换后的备库路径,如果主备库目录结构完全一样,则无需设定)
SQL> alter system set
LOG_FILE_NAME_CONVERT='STANDBY','PRIMARY' scope=both;
---(同上,这两个名字转换参数是主备库的路径映射关系,可能会是路径全名,看情况而定)
SQL> alter system set STANDBY_FILE_MANAGEMENT=auto
scope=both;
--- (auto后当主库的datafiles增删时备库也同样自动操作,且会把日志传送到备库standby_archive_dest参数指定的目录下,确保该目录存在,如果你的存储采用文件系统没有问题,但是如果采用了裸设备,你就必须将该参数设置为manual)
SQL> alter system set STANDBY_ARCHIVE_DEST='LOCATION=/Starhis_archive ' scope=both;
--- (一般和LOG_ARCHIVE_DEST_1的位置一样,如果备库采用ARCH传输方式,那么主库会把归档日志传到该目录下)
有了以上参数设置,则无论该库充当主库角色还是备库角色都无需再修改了。
然后重启数据库:
SQL> shutdown immediate
SQL> startup;
1.5、备份主库数据文件
可以利用每天的备份,所以此步骤可以省略
关闭应用服务器,停止监听,开始rman备份:
$ lsnrctl stop
$ rman target /
RMAN> backup full database format
'/backup/backup_%T_%s_%p.bak';
#RMAN> sql "alter system archive log
current";
#RMAN> backup archive log all
format='/backup/arch_%T_%s_%p.bak';
1.6、在主库上建立备库控制文件(控制文件通常需要有多份,手工将文件复制几份)
$ sqlplus '/as sysdba'
SQL> alter database create standby
controlfile as '/opt/stdby_control01.ctl';
$ cd /backup/
$ cp sdtby_control01.ctl stdby_control02.ctl
$ cp sdtby_control01.ctl stdby_control03.ctl
1.7、为备库准备init参数
$ sqlplus '/as sysdba'
SQL> create pfile = '/backup/initstarsms.ora'
from spfile;
$ cd /backup/
$ vi initstarsms.ora
注意主备库不同角色的属性配置,注意文件路径等,注意db_name要和主库一致,主要是以下参数:
control_files='/orahome/oradata/stdby_control01.ctl','/orahome/oradata/stdby_control02.ctl','/orahome/oradata/stdby_control03.ctl'
db_unique_name='STANDBY'
log_archive_config='DG_CONFIG=(STANDBY,PRIMARY)'
log_archive_dest_1='LOCATION=/Starhis_archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
log_archive_dest_2='SERVICE=db_primary LGWR
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
--
fal_server='DB_ STANDBY'
fal_client='DB_PRIMARY'
db_file_name_convert='PRIMARY','STANDBY' ---如果主库和备份库目录结构一样,不用设置
log_file_name_convert='PRIMARY',STANDBY' ---如果主库和备份库目录结构一样,不用设置
standby_archive_dest='LOCATION=/Starhis_archive '
另外,如果备库将来要打开成只读模式,需要确认audit_trail参数不是含db,应该设成os或none。
1.8、拷贝上面生成的文件backup_%T.bak、stdby_control01/02/03.ctl、initSTANDBY.ora到备库所在主机
注意rman备份的文件在主备库主机上目录要一致。
1.9、建立主库监听和主备库的网络服务名(必须是dedicated的),并启动监听
$ netca
(是图形界面,或者手工从别的库把listener.ora和tnsnames.ora拷过来修改也行)
$ lsnrctl start
$ tnsping db_primary
$ tnsping db_standby (此时tnsping还不通物理备库)
tnsping对方的时候,有可能linux防火墙限制了,会提示TNS-12560:
TNS:协议适配器错误。
临时禁用防火墙方法:
# service iptables stop
永久禁用防火墙方法:
# chkconfig --list iptables
# chkconfig --level 345 iptables off
2、建立备库
2.1、设置环境变量并建立备库一些必需目录
$ mkdir -p
$ORACLE_BASE/admin/$ORACLE_SID/adump
$ mkdir -p
$ORACLE_BASE/admin/$ORACLE_SID/bdump
$ mkdir -p
$ORACLE_BASE/admin/$ORACLE_SID/cdump
$ mkdir -p
$ORACLE_BASE/admin/$ORACLE_SID/udump
2.2、在备库主机上生成密码文件,且sys密码和主库得一致
$ orapwd
file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5
2.3、在备库上建立监听,和主备库网络服务名(必须是dedicated的),并启动监听
$ netca
(是图形界面,或者手工从别的库把listener.ora和tnsnames.ora拷过来修改也行)
$ lsnrctl start
$ tnsping db_PRIMARY
$ tnsping db_STANDBY
2.4、在备库上建立spfile
$ sqlplus '/as sysdba'
SQL> create spfile from pfile;
如果pfile没有放到$ORACLE_HOME/dbs/下,而是放在别的位置:
SQL> create spfile from pfile='/backup/initSTANDBY.ora';
2.5、启动物理备库
SQL> startup nomount
SQL> alter database mount standby
database;
2.6、备库做rman恢复
$ rman target / (要求主备库rman备份文件的存放路径和文件名一致)
RMAN> restore database;
##RMAN> restore archivelog all;
介质恢复后,rman自动将standby数据库打开到mount状态。
2.7、配置standby redolog(最佳性能模式可以忽略,如果要转为其它两种模式则要建立)
SQL> alter database add standby logfile
group 5 ('/Starol_redolog/stdby_redo05.log') size 50m,
group 6 ('/Starol_redolog/stdby_redo06.log') size 50m,
group 7 ('/Starol_redolog/stdby_redo07.log') size 50m,
group 8 ('/Starol_redolog/ stdby_redo08.log') size 50m;
group 9 ('/Starol_redolog/ stdby_redo09.log')
size 50m;
standby redolog的组数参考公式:(online redolog组数+ 1) *数据库线程数;单机线程数为1,RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
2.8、在备库上,启动redo apply
SQL> alter database recover managed
standby database disconnect from session;
到此物理备库创建完毕
3、主备库各参数文件内容
3.1、主备库listener.ora一样,如果有不一样也是host不一样
----------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
----------------------------------------
3.2、主备库tnsnames.ora一样,如果有不一样也是host和port不一样
----------------------------------------
DB_PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.253)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = starsms)
)
)
DB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.240)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = starsms)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
----------------------------------------
3.3、init$ORACLE_SID.ora
主库initPRIMARY.ora:
----------------------------------------
PRIMARY.__db_cache_size=226492416
PRIMARY.__java_pool_size=4194304
PRIMARY.__large_pool_size=4194304
PRIMARY.__shared_pool_size=96468992
PRIMARY.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PRIMARY/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db','extended'
*.background_dump_dest='/u01/app/oracle/admin/PRIMARY/bdump'
*.control_files='/orahome/oradata/control1.ctl','/orahome/oradata/control2.ctl','/orahome/oradata/control3.ctl'
*.core_dump_dest='/u01/app/oracle/admin/PRIMARY/cdump'
*.db_block_size=8192
*.db_domain='LK'
*.db_file_name_convert='STANDBY','PRIMARY'
*.db_name='ORCLDB'
*.db_unique_name='PRIMARY'
*.fal_client='DB_PRIMARY'
*.fal_server='DB_STANDBY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=/orahome/arch1/PRIMARY
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_dest_2='SERVICE=db_standby LGWR
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.log_archive_max_processes=5
*.log_file_name_convert='STANDBY','PRIMARY'
*.open_cursors=1500
*.processes=500
*.sga_max_size=320M
*.sga_target=320M
*.standby_archive_dest='LOCATION=/orahome/arch1/PRIMARY'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/PRIMARY/udump'
----------------------------------------
备库initSTANDBY.ora:
----------------------------------------
STANDBY.__db_cache_size=226492416
STANDBY.__java_pool_size=4194304
STANDBY.__large_pool_size=4194304
STANDBY.__shared_pool_size=96468992
STANDBY.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/STANDBY/adump'
*.audit_sys_operations=TRUE
*.audit_trail='os'
*.background_dump_dest='/u01/app/oracle/admin/STANDBY/bdump'
*.control_files='/orahome/oradata/stdby_control01.ctl','/orahome/oradata/stdby_control02.ctl','/orahome/oradata/stdby_control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/STANDBY/cdump'
*.db_block_size=8192
*.db_domain='LK'
*.db_file_name_convert='PRIMARY','STANDBY'
*.db_name='ORCLDB'
*.db_unique_name='STANDBY'
*.fal_client='DB_STANDBY'
*.fal_server='DB_PRIMARY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(STANDBY,PRIMARY)'
*.log_archive_dest_1='LOCATION=/orahome/arch1/STANDBY
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_2='SERVICE=db_PRIMARY LGWR
ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_max_processes=5
*.log_file_name_convert='PRIMARY','STANDBY'
*.open_cursors=1500
*.processes=500
*.sga_max_size=320M
*.sga_target=320M
*.standby_archive_dest='LOCATION=/orahome/arch1/STANDBY'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/STANDBY/udump'
----------------------------------------
4、主库归档测试
主库归档前:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /orahome/arch1/PRIMARY
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
此时备库:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /orahome/arch1/STANDBY
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 8
主库归档后:
SQL> ALTER SYSTEM SWITCH LOGFILE; --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM ARCHIVE LOG
CURRENT; --对数据库中的所有实例执行日志切换
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /orahome/arch1/PRIMARY
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
此时备库:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /orahome/arch1/STANDBY
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 9
5、修改主库DataGuard保护模式
5.1、LGWR传送日志的配置方法:
表中描述了不同保护模式下LOG_ARCHIVE_DEST_n参数应该设置的属性:
5.2、修改主库DataGuard保护模式
SQL > select
name,db_unique_name,protection_mode from v$database; 查看当前保护模式
5.3、主库修改初始化参数 (主库db_primary操作)
SQL> alter system set
log_archive_dest_2='SERVICE=db_STANDBY OPTIONAL LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora10gdg';
System altered.
5.4、主库通过命令行修改数据保护模式,重启主数据库 (主库db_primary操作)
SQL > startup mount
SQL> alter database set standby database
to maximize availability;
Database altered.
切换主库保护模式的语法:
alter database set standby database to maximize
{ protection | availability | performance }
附:下面列出不同数据保护模式的修改方法
SQL > alter database set standby database
to maximize protection; --最大保护
SQL > alter database set standby database
to maximize availability; --最高可用性
SQL > alter database set standby database
to maximize performance; --最高性能
5.5、重启主库 (主库操作)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 104857600 bytes
Fixed Size 1266056 bytes
Variable Size 79695480 bytes
Database Buffers 20971520 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
5.6、查看主库保护模式是否变更成功 (主库操作)
SQL> select
protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
5.7、修改备库的log_archive_dest_2初始化参数方便数据库角色切换 (备库操作)
SQL> alter system set log_archive_dest_2='SERVICE=db_primary
OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ora10g';
System altered.
5.8、查看备库数据保护模式 (备库操作)
SQL> select
protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
5.9、验证一下“最高可用性”切换成果
1).备库关闭前主库的状态:
SQL> select protection_mode,protection_level
from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
2).备库关闭后主库的状态(注意这里的变化,主库的PROTECTION_LEVEL标示为RESYNCHRONIZATION状态):
sys@ora10g> select protection_mode,protection_level
from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY RESYNCHRONIZATION
3).备库恢复数据恢复后,主库的状态:
sys@ora10g> select
protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
5.9打开备用库恢复进程
recover managed standby database disconnect
from session;
执行上面这条语句,备用库会在主库日志文件切换归档后,使用归档文件恢复数据库。
recover managed standby database using
current logfile disconnect from session;
这条语句与上面不同的是,备用日志文件切换,生成归档日志前,先恢复数据库。
recover managed standby database finish;
这条语句在做切换时,尽量多的保护数据。从备用日志文件中恢复数据
recover managed standby database cancel; 取消备用库自动恢复
可以在使用上面两条语句时,查看v$managed_standby的不同。
SQL> recover managed standby database
disconnect;
Media recovery complete.
SQL> select process,status from
v$managed_standby;
查询当前库的角色和保护模式:
SQL> select
database_role,db_unique_name,open_mode,protection_mode,
protection_level,switchover_status,
supplemental_log_data_pk,supplemental_log_data_ui
from v$database;
6、DataGuard关启状态
启用备用数据库
SQL > startup nomount;
SQL >alter database mount standby
database;
SQL >alter database recover managed
standby database disconnect from session;
关闭备用数据库
SQL >alter database recover managed
standby database cancel;
SQL >shutdown immediate;
从关闭状态打开
SQL >startup nomount;
SQL >alter database mount standby
database;
SQL >alter database open read only;
从正在恢复状态只读打开
SQL >alter database recover managed standby
database cancel;
SQL >alter database open read only;
切换回到恢复状态
SQL >alter database
recover managed standby database disconnect from session;
7、主备数据库切换
7.1、正常切换:
主服务器
SQL >select switchover_status from
v$database; ---查看状态
SQL >alter database commit to switchover
to physical standby with session shutdown;
SQL >alter database commit to switchover
to physical standby;
SQL >shutdown immediate
SQL >startup nomount;
SQL >alter database mount standby
database;
SQL >alter database recover managed
standby database disconnect from session;
备用服务器
SQL > alter database commit to switchover
to primary;
SQL >shutdown immediate; ---对应于sun cluster采用scsetup来重启
SQL >startup
7.2、非正常切换:(即主服务器当机的情况)启动failover
备服务器
SQL >alter database recover managed standby database finish;
SQL >alter database commit to switchover
to primary;
SQL >shutdown immediate;
SQL >startup;
8、常用维护SQL
添加几个常用命令
备库启动归档日志应用
alter database recover managed standby
database disconnect from session;
备库停止归档日志应用
alter database recover managed standby
database cancel;
查询归档日志是否被应用,查询V$archived_log视图的applied列
select sequence#,dest_id,first_time,next_time
,applied from v$archived_log;
查看备库是否和主库同步,查询V$archive_dest_status视图
select
archived_thread#,archived_seq#,applied_thread#,applied_seq# from
v$archive_dest_status;
监控日志传送状态,V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少
select * from v$archive_gap;
查看当前主机的运行状态
select
switchover_status,database_role,protection_mode from v$database
查看备库接收、应用redo数据的过程
select message from v$dataguard_status
备库端查看RFS(Remote File Service)接收日志情况和MRP应用日志同步主数据库的情况(Physical Standby Database
Only)记录当前备库的一些进程情况和进程ID
select
process,status,thread#,sequence#,block#,blocks from v$managed_standby;
V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息
SELECT * FROM V$STANDBY_LOG;
启动Data Guard后, 查看同步情况::
SQL> select error from v$archive_dest;
用SQL查看了一下同步正常:
SQL> select sequence#,applied from
v$archived_log;
主库归档:
SQL> ALTER SYSTEM SWITCH
LOGFILE; --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM
ARCHIVE LOG CURRENT; --对数据库中的所有实例执行日志切换
在备库上,验证一下传过来的归档文件:
SQL> SELECT SEQUENCE#, FIRST_TIME,
NEXT_TIME, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在主库上,查询待转换standby库的归档文件是否连接:
SQL> SELECT THREAD#, LOW_SEQUENCE#,
HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
如过上面查到存在不连续的归档,那查找sequence对应的归档文件:
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE
THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;
查询主备库已归档文件最大序号是否相同:
SQL> select distinct
thread#,max(sequence#) over(partition by thread#) a from v$archived_log;
在备库上,显示备库相关进程的当前状态信息:
SQL> select
process,client_process,sequence#,status from v$managed_standby;
显示归档文件路径配置信息及redo apply情况:
SQL> select
dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name
from v$archive_dest_status where
status='VALID';
检查应用模式(是否启用了实时应用):
如果打开了实时应用,则recovery_mode会显示为:MANAGED
REAL TIME APPLY。
SQL> select
dest_id,DEST_NAME,STATUS,TYPE,DATABASE_MODE, recovery_mode from
v$archive_dest_status;
显示那些被自动触发写入alert.log或服务器trace文件的事件:
通常是在你不便访问到服务器查询alert.log时,可以临时访问本视图查看一些与dataguard相关的信息。
SQL> select * from v$dataguard_status;